옵티마이저 설정을 기본값으로 되돌린 뒤 소스가 없는 점포 판매 조회 화면만 첫 줄이 늦게 뜬다

중급PLSQL유통635 / 700

상황

지난달 정기 점검에서 본사 유통 DB의 옵티마이저 설정을 기본값(ALL_ROWS)으로 되돌렸다(OPT-041의 절차). 반복 SQL은 미리 베이스라인으로 올려 두어 대부분 그대로였는데, 2009년에 만든 PowerBuilder 「점포 판매 조회」 화면 하나가 느려졌다. 한 달에 며칠만 쓰여서 베이스라인을 올릴 때 커서 캐시에 없었다. 이 화면은 소스가 남아 있지 않아 SQL을 고칠 수 없다. 결과를 20행씩 보여 주고, 사용자는 대부분 첫 화면만 보고 닫는다. 이 DB에는 Tuning Pack이 없다.

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록 · CF10_PK (SLE_NO)(SLE_NO는 날짜로 시작해 판매 순서와 같다) · 매장 6곳(매장당 약 83,000건)
CF11 판매상품: 1,096,708행 · 8,204블록 · CF11_PK (SLE_NO, ITM_NO) 높이 3 · 판매 한 건에 평균 2.2줄
화면 SQL의 SQL_ID는 7w2q9cj1sd0m4이다(V$SQL에서 찾았다).

원본 SQL

sql

-- PowerBuilder 화면이 보내는 SQL (소스 없음 · 고칠 수 없다)
SELECT a.SLE_NO, a.SLE_DT, a.SLE_HMS, b.ITM_NO, b.SLE_QTY
  FROM CF10 a, CF11 b
 WHERE a.SHP_NO = :1
   AND b.SLE_NO = a.SLE_NO
 ORDER BY a.SLE_NO DESC

실행계획 학습용 예시

xplan

[기본값으로 되돌린 뒤 — 첫 화면 20행을 받고 닫았을 때]
----------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads | Used-Mem |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |        |      1 |        |     20 |00:00:00.93 |   12787 |  8120 |          |
|   1 |  SORT ORDER BY      |        |      1 |    182K|     20 |00:00:00.93 |   12787 |  8120 |   11M (0)|
|*  2 |   HASH JOIN         |        |      1 |    182K|    182K|00:00:00.71 |   12787 |  8120 |   13M (0)|
|*  3 |    TABLE ACCESS FULL| CF10   |      1 |  83333 |  83312 |00:00:00.08 |    4583 |     0 |          |
|   4 |    TABLE ACCESS FULL| CF11   |      1 |   1096K|   1096K|00:00:00.42 |    8204 |  8120 |          |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("B"."SLE_NO"="A"."SLE_NO")
   3 - filter("A"."SHP_NO"=:1)

[되돌리기 전 — FIRST_ROWS 설정 · AWR 의 같은 SQL_ID]  CF10_PK 를 거꾸로 읽는 NL · 20행 · 42블록 · 0.01초

문제

설정을 되돌린 뒤 이 화면의 첫 화면이 늦어진 이유를 실행계획으로 설명하시오.
SQL 글자와 인스턴스 설정을 바꾸지 않고 이 SQL에만 최적화 목표를 알려 주시오. 적용됐는지 확인하는 방법도 쓰시오.
다른 방법(SQL 프로파일, 로그온 트리거, SQL plan baseline)과 견줘 그 방법을 고른 이유를 쓰시오.

개선 SQL원본 SQL을 고쳐 쓰세요. 인덱스 DDL과 힌트도 쓸 수 있습니다.

개선 사유무엇이 문제였고 왜 이렇게 고쳤는지 쓰세요.0자

모범답안

모범 답안 SQL

sql

-- 글자는 그대로 두고 이 SQL_ID 의 문장에만 힌트 하나를 덧붙인다 (튜닝 팩 기능이 아니다)
DECLARE
  v VARCHAR2(128);
BEGIN
  v := DBMS_SQLDIAG.CREATE_SQL_PATCH(
         sql_id      => '7w2q9cj1sd0m4',
         hint_text   => 'FIRST_ROWS(20)',
         name        => 'PATCH_PB_SLE_LIST',
         description => 'PB 점포 판매 조회 · 소스 없음 · 첫 화면 20행 · OPT-041 되돌림 뒤');
END;
/

-- 확인 : 다음 실행의 Note 와 Buffers
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('7w2q9cj1sd0m4', NULL, 'ALLSTATS LAST'));
SELECT name, status, created FROM DBA_SQL_PATCHES;

개선 후 실행계획 학습용 예시

xplan

---------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |      1 |        |     20 |00:00:00.01 |      42 |
|   1 |  NESTED LOOPS                |         |      1 |     20 |     20 |00:00:00.01 |      42 |
|   2 |   NESTED LOOPS               |         |      1 |     20 |     20 |00:00:00.01 |      33 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| CF10   |      1 |      9 |      9 |00:00:00.01 |       6 |
|   4 |     INDEX FULL SCAN DESCENDING| CF10_PK|      1 |     54 |     54 |00:00:00.01 |       3 |
|*  5 |    INDEX RANGE SCAN          | CF11_PK |      9 |      2 |     20 |00:00:00.01 |      27 |
|   6 |   TABLE ACCESS BY INDEX ROWID| CF11    |     20 |      2 |     20 |00:00:00.01 |       9 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("A"."SHP_NO"=:1)
   5 - access("B"."SLE_NO"="A"."SLE_NO")

Note
-----
   - SQL patch "PATCH_PB_SLE_LIST" used for this statement

모범 답안 서술

설정을 되돌린 뒤 옵티마이저는 ALL_ROWS, 곧 결과 전체를 끝까지 받는 비용으로 계획을 고른다. 매장의 판매 83,312건(3번 줄)과 판매상품 109만 줄(4번 줄)을 통째로 읽어 해시 조인하고, 18만 2천 줄을 SORT ORDER BY로 정렬했다. 해시 조인과 정렬은 입력을 다 받아야 첫 행을 내므로, 사용자가 보는 20행을 띄우기 전에 이 일을 모두 끝내야 했다(Buffers 12,787, 0.93초). 되돌리기 전에는 FIRST_ROWS 설정 덕에 CF10_PK를 거꾸로 읽는 NL로 20행에서 멈췄다(42블록). SQL 글자와 인스턴스 설정은 바꿀 수 없으므로, 이 문장에만 최적화 목표를 덧붙인다. DBMS_SQLDIAG.CREATE_SQL_PATCH로 SQL_ID 7w2q9cj1sd0m4에 FIRST_ROWS(20) 힌트를 붙인다. SQL 패치는 글자를 그대로 둔 채 그 문장을 파싱할 때 힌트를 더해 준다. 다음 실행의 계획 Note에 SQL patch "PATCH_PB_SLE_LIST" used가 뜨고, CF10_PK INDEX FULL SCAN DESCENDING과 NL로 20행에서 멈추면 적용된 것이다. Buffers는 12,787 → 42다. DBA_SQL_PATCHES에도 남는다. 다른 방법과 견주면 이렇다. SQL 프로파일도 힌트를 붙일 수 있지만 튜닝 팩 기능이라 이 DB에서는 쓸 수 없다. 로그온 트리거로 이 프로그램(MODULE)의 세션을 FIRST_ROWS_20으로 바꾸면 그 프로그램의 모든 SQL이 함께 바뀌어, 같은 화면의 집계나 출력이 반대로 느려질 수 있다. SQL plan baseline도 쓸 수 있지만, 그러려면 같은 글자를 FIRST_ROWS_20 세션에서 한 번 돌려 자식 커서를 만든 뒤 그 계획을 올려야 한다(OPT-037의 방법). 원하는 것이 계획 하나가 아니라 "앞의 20행"이라는 목표 하나이므로, 힌트 한 줄을 붙이는 SQL 패치가 가장 작고 뜻이 분명하다.

해설

패치와 베이스라인이 함께 있으면 베이스라인이 마지막 관문이다. 패치로 만든 계획도 받아들인 계획이 아니면 쓰이지 않는다(OPT-040). 이 SQL에는 베이스라인이 없다.
패치는 글자에 묶인다. 화면을 다시 만들어 SQL이 한 글자라도 바뀌면 패치는 붙지 않는다. 그래서 패치 대장에 화면 이름과 SQL_ID, 붙인 이유와 지울 조건(화면 재개발)을 적는다.
함정: hint_text에 테이블 별칭을 쓰는 힌트(INDEX(a CF10_PK) 같은)를 넣으면 쿼리 블록 이름까지 맞아야 먹는다. FIRST_ROWS(n)처럼 문장 전체에 거는 힌트는 그런 걱정이 없다. 넣은 뒤에는 Note와 계획으로 꼭 확인해야 한다.
되돌린 뒤 한 달 동안은 AWR의 SQL 이력에서 계획이 바뀐 SQL을 찾아 이런 화면이 더 있는지 본다. 한 달에 며칠만 도는 SQL은 베이스라인 캡처 목록에서 빠지기 쉽다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황