10046 event 트레이스
DB CAFE
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
1. 10046 Event
10046 Event는 SQL Tuning에 있어서 필수적인 Tool이다. 왜 Oracle10g에서도 Extended SQL Trace는 여전히 중요한 Tool인가? Plan Statistics가 제공하지 못하는 중요한 정보를 제공해주기 때문이다.
- 모든 SQL 문장의 Bind 변수 값을 추적할 수 있는 유일한 방법(Level 4) - 모든 SQL 문장의 대기 현상(Wait)을 추적할 수 있는 유일한 방법(Level 8)
Oracle10g의 Bind Capture 기능이 소개되면서 V$SQL_BIND_CAPTURE View를 통해 Bind 변수를 추적할 수 있는 방법이 생겼다. 하지만 Bind Capture는 본질적인 한계를 가지고 있다.
- 15분 간격으로 Capture를 수행. Capture주기는 _CURSOR_BIND_CAPTURE_INTERVAL Parameter값에 의해 결정.
(기본값 : 900(초)) 이 값을 줄임으로써 보다 공격적으로 Capture를 수행할 수 있다. 약간의 성능 저하를 감수해야 한다. 아무리 Capture 주기를 줄인 다고 해도 모든 SQL 문장의 Bind 변수를 Capture 하는 것은 불가능하다.
- 하나의 SQL Cursor에 대해 정해진 메모리 크기만큼만 Bind Capture를 수행. 메모리 크기의 한계는
_CURSOR_BIND_CAPTURE_AREA_SIZE Parameter 값에 의해 결정.(기본값 : 2000(Byte))
많은 수의 Bind 변수를 사용하는 경우 V$SQL_BIND_CAPTURE View에 일부 Bind 변수 값이 누락되는 현상이 발생할 수 있는데 이런 제약 조건 때문이다.
- Oracle은 Select 문장과 Bind 변수가 Where 절에서 사용되었을 경우에만 Bind Capture를 수행. - V$SQL_BIND_CAPTURE View는 가장 “최근에” Capture된 Bind 변수 값을 보여준다.(Extended SQL Trace와 같은 역할은 불가)
Oracle은 매우 많은 수의 View들을 통해 대기 현상을 Monitoring 할 수 있다. V$SESSION, V$SESSION_WAIT, V$SESSION_EVENT, V$SYSTEM_EVENT, V$ACTIVE_SESSION_HISTORY DBA_HIST_ACTIVE_SESS_HISTORY, V$EVENT_HISTOGRAM View 등이 대기 현상과 관련된 중요한 정보들을 제공한다.
- 그 어떤 View도 10046 SQL Trace가 제공하는 것과 같은 포괄적인 추적 정보를 제공하지는 않는다.
2. 자동화에 대한 아이디어
10046 Event를 이용해 SQL Trace 수행을 자동화하는 방법이다. Connect … @trace on 10046 8 -- execute query select * from … where … @trace_off @trace_file @tkprof trc.out ed trc.out trace_on.sql과 trace_off.sql Script는 Event 을 활성화 / 비활성화하는 역할. -- trace on.sql alter session set events ‘&1 trace name context forever, level &2’; -- trace off.sql alter session set events ’&1 trace name context off’; 자동화의 핵심은 trace_file의 이름을 얻는데 있다. -- @name : trace file -- @author : dion cho -- @description : get trace file name column trace file name new value trace file select -- 아래 부분은 환경에 맞게 적절히 수정 d.value ||’/’||p.value||’_ora_’||s.spid||’.trc’ as trace_file_name from (select value from v$parameter where name = ’instance_name’) p, (select value from v$parameter where name = ‘user_dump_dest’) d, select spid from v$process where addr = (select paddr from v$session where sid = (select sid from v$mystat where rownum = 1))) s ; prompt &trace_file
SQL*Plus의 column 명령을 이용해서 Trace File의 이름을 얻어내는 기법을 유심히 보자. 잘 응용하면 많은 작업들을 SQL*Plus 내에서 자동화시킬 수 있다. Host Command로 tkprof를 호출하면 Formatting된 Report를 얻을 수 있다. -- tkprof.sql Ho tkprof &trace_file &1
3. 10053 Event
10053 Event은 CBO 문제를 추적하는 가장 강력한 Tool이다. 하지만 자주 사용되지는 않는 이유는 무엇보다도 더 쉽고 좋은 방법이 많기 때문이다.
- EXPLAIN PLAN 명령, GATHER_PLAN_STATISTICS Hint, DBMS_XPLAN Package 등을 이용 (CBO 문제의 핵심인 실행 계획 이상 현상을 상당 부분 추적) - CBO가 Optimization 단계에서 수행하는 일은 결국 통계 정보를 읽어서 최적의 Cost를 계산하는 일에 불과하다. 따라서 통계 정보를 정확하게 이해하고 있다면 CBO가 하는 일을 해할 수 있다. ALL_TAB_STATISTICS, ALL_IND_STATISTICS, ALL_TAB_COL_STATISTICS, ALL_PART_COL_STATISTICS, ALL_SUBPART_COL_STATISTICS, ALL_TAB_HISTOGRAMS, ALL_PART_HISTOGRAMS, ALL_SUBPART_HISTOGRAMS Dictionary View를 조회하면 통계 정보를 확인 할 수 있다.
하지만 Oracle CBO가 점점 세련되어지고 복잡해지면서 EXPLAIN PLAN이나 DBMS_XPLAN Package만으로는 원하는 정보를 충분히 얻기가 점점 어려워지고 있다.(10053 Event 필요성이 증가) 10053 Event의 출력 결과가 제공하는 정보 - CBO가 참조하는 기본 환경 값을 알 수 있다. Optimizer와 관련된 Parameter/Bug Fix Patch 등을 확인 - Query Transformation 과정을 알 수 있 다. 복잡한 SQL 문장이 어떻게 Transformation 되는지
왜 Transformation에 실패하는지에 대한 정보를 제공.
- Query Optimization 과정을 알 수 있다. System Statistics와 Object Statistics를 참조하는 일련의 과정과
Access Type의 결정, Join Type을 결정하는 일련의 과정이 출력
4. 기본적인 사용법
10053 Event의 사용 방법을 보자. alter session set events ‘10053 trace name context forever, level 1’; <Query> alter session set events ’10053 trace name context off’;
출력 결과는 User Dump Directory에 Trace File 형태로 기록되며 내용은 Version마다 상이하다. 최신 버전의 Trace File은 다음과 같은 내용을 기록한다.
- Query Block 생성 - Optimizer Parameter, Bug Fix Control 정보 - Query Transformation 과정 - Statistics 정보 - Single Table Access 방식 선택 - Join 순서와 Join방식 선택
각 단계별로 어떤 내용이 기록되는지 예제를 통해서 확인하자. 우선 필요한 Object 들을 생성
test_script32.sql
Query를 수행하고, Plan Statistics를 조회
test_script33.sql
PLAN_TABLE_OUTPUT
SQL_ID 07fq9jjq9p0qs, child number 0
select /*+ gather_plan_statistics cost_based */ t1.name, v1.name2 , v1.name2 from t_pred1 t1, v_pred v1 where t1.n = 1 and t1.id = v1.id2_1(+)
Plan hash value: 3337020919
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
| 0 | SELECT STATEMENT | | 1 | | | 45 (100)| | 20 |00:00:00.09 | 173 | | | | |* 1 | HASH JOIN OUTER | | 1 | 20 | 880 | 45 (5)|00:00:01| 20 |00:00:00.09 | 173 | 1133K| 1133K| 961K (0)| |* 2 | TABLE ACCESS FULL | T_PRED1 | 1 | 20 | 280 | 3 (0)|00:00:01| 20 |00:00:00.01 | 3 | | | | | 3 | VIEW | V_PRED | 1 | 20000 | 585K| 42 (5)|00:00:01| 20000 |00:00:00.05 | 170 | | | | |* 4 | HASH JOIN | | 1 | 20000 | 800K| 42 (5)|00:00:01| 20000 |00:00:00.05 | 170 | 1647K| 1647K| 2249K (0)| | 5 | INDEX FAST FULL SCAN | T_PRED3_IDX| 1 | 20000 | 195K| 14 (0)|00:00:01| 20000 |00:00:00.01 | 61 | | | | | 6 | TABLE ACCESS FULL | T_PRED2 | 1 | 20000 | 605K| 26 (0)|00:00:01| 20000 |00:00:00.01 | 109 | | | |
Query Block Name / Object Alias (identified by operation id):
1 - SEL$1 2 - SEL$1 / T1@SEL$1 3 - SEL$2 / V1@SEL$1 4 - SEL$2 5 - SEL$2 / T3@SEL$2 6 - SEL$2 / T2@SEL$2
Predicate Information (identified by operation id):
1 - access("T1"."ID"="V1"."ID2_1")
2 - filter("T1"."N"=1)
4 - access("T2"."ID"="T3"."ID" AND "T2"."ID2"="T3"."ID2")
Column Projection Information (identified by operation id):
1 - (#keys=1) "T1"."NAME"[VARCHAR2,30], "V1"."NAME2"[VARCHAR2,30] 2 - "T1"."ID"[NUMBER,22], "T1"."NAME"[VARCHAR2,30] 3 - "V1"."ID2_1"[NUMBER,22], "V1"."NAME2"[VARCHAR2,30] 4 - (#keys=2) "T2"."ID"[NUMBER,22], "T2"."NAME"[VARCHAR2,30] 5 - "T3"."ID"[NUMBER,22], "T3"."ID2"[NUMBER,22] 6 - "T2"."ID"[NUMBER,22], "T2"."ID2"[NUMBER,22], "T2"."NAME"[VARCHAR2,30]
실행 계획에서 유의해서 봐야 할 정보는 Predicate 정보이다. 정보는 다음과 같이 해석할 수 있다.
- 2번 단계(t1.n = 1)에서 Table Full Scan을 통해 조건을 만족하는 Row를 Fetch - 4번 단계(v_pred.t2.id = t3.id and t2.id2 = t3.id2)에서 Hash Join을 통해 조건을 만족하는 Row를 Fetch - 1번 단계(t1.id = v1.id2_1)에서 Hash Outer Join을 통해 2번과 4번 단계의 Data들을 Join
정상적인 결과로 보이는 위의 실행 계획의 문제점은 Join Predicate Pushing이 발생하지 않았다는 것이다. t1.n = 1의 조건을 만족하는 Data에 대해서 Join 조건(Join Predicate)을 View v_pred 안으로 밀어 넣을 수(Pushing)있다면 일량을 줄일 수 있다.
Predicate 정보의 변화에 주목하자. Join Predicate Pushing이라는 이름의 Query Transformation 기법으로 인해 t2.id = t1.id라는 기존에 존재하지 않던 Join 조건이 추가되었고 실행 계획이 크게 변한다. 10053 Event를 이용해 왜 Query Transformation, 즉 Join Predicate Pushing이 발생하지 않았는지를 추적하는 것이다.
단계 1 : Trace File은 Query Block정보를 생성하는 것에서부터 시작. SQL 문장에 대해 Parse Tree를 생성하는 과정에서 기본적인 Query Block 정보가 생성. 사용된 Query Block명이 Plan Statistics에서 보이는 Query Block Name 정보와 일치한다. Query Transformation에 의해 Query Block정보가 바뀌는 경우에는 최종 Query Block 개수와 이름이 변경될 수 있다. (Trace 파일 일부분을 발췌해봤다.)
Registered qb: SEL$1 0x2f2feae0 (PARSER)
signature (): qb_name=SEL$1 nbfros=2 flg=0 fro(0): flg=4 objn=66336 hint_alias="T1"@"SEL$1" fro(1): flg=5 objn=66341 hint_alias="V1"@"SEL$1"
Registered qb: SEL$2 0x2f2f61b0 (PARSER)
signature (): qb_name=SEL$2 nbfros=2 flg=0 fro(0): flg=4 objn=66337 hint_alias="T2"@"SEL$2" fro(1): flg=4 objn=66338 hint_alias="T3"@"SEL$2"
단계 2 : Parse Tree를 Query Block이 부여되고 나면 Optimizer는 Parameter 정보와 Bug Fix Control 정보를 출력한다. 변경된 Parameter값과 기본 Parameter값이 순서대로 출력.
단계2 결과 참조.txt
Bug Fix Control 정보의 출력은 Oracle 10.2.0.2부터 출력. Bug Fix Control은 FIX CONTROL Parameter를 통해 수행. 이 Parameter 역시 Oracle10.2.0.2부터 지원되며 사용법은 다음과 같다.
ALTER SESSION SET "_FIX_CONTROL" = ’3746511:on’,’4519016:off’ ;
"Bug#3746511에 해당하는 Fix Control을 적용하라. 하지만 Bug#4519016에 해당하는 Fix Control은 적용하지 말라". Bug Fix Control의 적용 여부에 따라 Optimizer의 안정성이 깨어지거나 비정상적인 동작을 하는 경우를 세밀하게 제어하기 위해 지원되는 기능이다. Bug Fix Control의 적용 여부는 V$SYSTEM_FIX_CONTROL View / V$SESSION_FIX_CONTROL View를 통해서도 조회 가능하다.
OPT_PARAM Hint에 의해 변경된 Optimizer Parameter 정보를 출력. ........
_optimizer_starplan_enabled = true _extended_pruning_enabled = true _optimizer_push_pred_cost_based = true _sql_model_unfold_forloops = run_time _enable_dml_lock_escalation = false
.........
SQL 문장 레벨에서 Optimizer Parameter 값을 제어하기 위한 목적으로 고안되었으며 Oracle10g R2부터 지원.
select /*+ opt_param('_optimizer_push_pred_cost_based','false') */ t1.name, v1.name2, v1.name2 from t_pred1 t1, v_pred v1 where t2.n = 1 and t1.id = v1.id2_1(+);
단계 3 : 이제 본격적인 Optimization이 수행. 첫 단계로 Query Transformation 수행. Query Transformation은 QT(Query Transformation)와 CBQT(Cost Based Query Transformation)의 두 단계로 구분되며, QT 단계에서는 Cost 계산이 불필요한 간단한 Transformation을 수행.
단계3 결과 참조_1.txt
Oracle Version에 따라 Transformation 기법이 추가되거나 개선되기 때문에 이 정보는
Version 에 따라 크게 다를 수 있다. QT 단계가 끝나면 CBQT 가 수행된다.
단계3 결과 참조_2.txt
CBQT는 Cost를 감안해서 Transformation을 수행해야 한다. 이것은 Transformation 단계에서 실제
Transformation이 일어나지 않고 Cost가 계산되는 Optimization 단계에서 Transformation이 이루어진다는
것을 의미 한다. CBQT의 등장과 함께 Transformation 단계와 Optimization 단계의 융합(Fusion)이
이루어진 것이다. 예제와 같이 Optimization이 이루어지는 도중에 실제 Transformation을 수행한다.
(첨부파일 내용은 책의 내용을 복사한 것이다.)
단계3 결과 참조_3.txt
첨부파일의 마지막 줄 "JPPD: Will not use JPPD from query block SEL$1 (#1)" Message에 주목하자.
이 Message는 CBQT에 의해 JPPD(Join Predicate Push Down)이 이루어진 경우의 Cost가 더 높기
때문에 JPPD를 수행하지 않을 것임을 의미한다. 이 정보는 10053 Event에서만 얻을 수 있다.
10053 Event를 수행해야 할 가장 중요한 이유 중 하나가 Transformation (CBQT 포함)
Troubleshooting이 될 것이다.
CBQT의 수행 여부는 _OPTIMIZER_COST_BASED_TRANSFORMATION Parameter /
_OPTIMIZER_PUSH_PRED_COST_BASED Parameter값에 의해 결정된다. CBQT를 Disable 시키려면
Parameter 값을 변경해 주면 된다.
-- disable CBQT
alter session set "_optimizer_cost_based_transformation" = off;
-- disable cost based join predicate pushing alter session set "_optimizer_push_pred_cost_based" = false;
SQL 문장 레벨에서 CBQT를 Disable 하려면 OPT PARAM Hint를 사용하면 된다.
select /*+ opt_param(‘_optimizer_push_pred_cost_based’,’false’) */
t1.name, v1.name2, v1.name2
from t_pred1 t1, v_pred v1
where t1.n = 1 and t1.id = v1.id2_1(+);
단계 4: Statistics 정보를 추출하고, Optimization을 위한 기본적인 정보들을 계산한다. Optimization의 기본 단위는
SQL 문장 전체가 아니라 Query Block이다. CBO는 Query Block 레벨에서 Optimization을 수행하고 그 결과를
다시 상위 Query Block에서 사용하는 방식을 사용한다. 예제는 Optimization 과정이 Query Block 들의 단위로
나누어 기록된다.
(첨부파일 내용 역시 책의 내용을 복사한 것이다.)
단계4 결과 참조_1.txt
System Statistics는 SYS.SYS_AUX$ Table에 저장되어 있다.
Select * from sys.sys_aux$;
(직접 실행은 해봤지만 테이블이 존재하지 않는다는 메세지가 출력 됬다. 직접 조회해보기 바란다.)
그 후 Dictionary View를 통해 Object Statistics 정보를 추출한다. Table에 대해서는 Row수, Block수, 평균 Row 크기
등의 정보를 추출한다. Index에 대해서는 Level(Depth), Distinct Key의 개수, Leaf Block수, Clustering Factor 등의 정보를 추출한다.
단계4 결과 참조_2.txt
단계 5: Statistics에서 추출한 정보는 Single Table Access 방식을 판단하는 기준으로 사용된다.
Single Table Access의 Cost를 계산하는 작업은 특정 Query Block 단위로 이루어진다. Query Block 내에 속한
Table과 Predicate(조건)에 대해 최적의 Access 방식을 산출. Access 방식은 Table Full Scan, Index Unique / Range Scan,
Index Full Scan, Index Fast Full Scan, Index Skip Scan, Index Join, Index Combination에서 선택.
단계5 결과 참조_1.txt
Table t_pred2에 대한 최적의 Access 방식은 Table Full Scan이며 Cost는 29.55이다. 반면, Table t_pred3에
대한 최적의 Access 방식은 Index t_pred3_idx를 Index Fast Full Scan으로 읽는 것이며, Cost는 15.33이다.
단계5 결과 참조_2.txt
특정 Table에 대해 Parallel 작업이 수행되는 경우 다음 정보가 출력 된다. Serial Cost(Cost):3,
Parallel Cost(Resp):2로 계산된다. 따라서 Parallel Execution이 선택될 것이다.
select /*+ parallel(t1 4) */
t1.name, v1.name2, v1.name2
from t_pred1 t1, v_pred v1
where t1.n = 1 and t1.id = v1.id2_1(+);
SINGLE TABLE ACCESS PATH
Column (#2) : N(NUMBER)
AvgLen : 3.00 NDV : 5 Nulls : 0 Density:0.2 Min : 1 Max : 5
Table : T_PRED1 Alias : T1
Card : Original : 100 Rounded : 20 Computed : 20.00 Non Adjusted : 20.00
Access Path : TableScan
Cost : 2.00 Degree : 4 Resp : 2.00 Card : 20.00 Bytes : 0
통계 정보가 없거나 DYNAMIC_SAMPLING Hint가 사용되는 경우에는 Dynamic Sampling이 수행 된다.
Dynamic Sampling은 특정 Table에 대해 주어진 Block 수(32)만큼 Sampling을 수행. Table에 대해 사용된
Predicate를 확인해서 예상 Row수를 계산. 복잡한 Predicate에 대해 좀 더 정확한 예측이 가능.
Dynamic Sampling이 사용된 경우에는 Single Table Access 정보에 내용이 추가로 출력.
select /*+ dynamic_sampling(t1 4) */
t1.name, v1.name2, v1.name2
from t_pred1 t1, v_pred v1
where t1.n = 1 and t1.id = v1.id2_1(+);
단계5 결과 참조_3.txt
Dynamic Sampling은 Query Block이 아닌 Table Level에서 이루어진다.(Dynamic Sampling의 근본적인 한계)
단계 6: 가장 중요한 단계로 Join 순서와 Join 종류를 결정. Join에 참여하는 Table들에 대해 Join 순서 별로 어떤 Join
방식이 가장 최적의 Cost를 갖는지 계산하게 된다.
단계6 결과 참조_1.txt
첫 번째 Join 순서로 {T2 -> T3}가 시도되며 NL Join, Sort Merge Join, Hash Join 중
Hash Join 이 최적의 Cost(45.97)를 지닌다. 두 번째 Join 순서인 {T3 ->T2}에 대해서도 같은 순서로 Cost를 계산하며,
동일한 Cost인 45.97이 계산되었기 때문에 첫 번째 Join 순서인 {T2 -> T3}+Hash Join이 최종 선택.
Join order[2] : T PRED3[T3]#1 T PRED2[T2]#0
Now joining : T PRED2[T2]#0
NL Join
outer table : Card : 20000.00 Cost : 15.33 Resp : 15.33 Degree : 1 Bytes : 10
Access path analysis for T_PRED2
Inner table : T_PRED2 Alias : T2
Access Path : TableScan
NL Join : Cost : 560929.09 Resp : 560929.09 Degree : 1
Cost_io : 550016.00 Cost_cpu : 91669966801
Resp_io : 550016.00 Resp_cpu : 91669966801
Access Path : index (AllEqJoinGuess)
Index : T_PRED2_IDX
resc_io : 2.00 resc_cpu : 15483
Trace 내용 참고.txt
(이하 내용이 너무 많아 별도의 첨부파일을 등록했다 참고 바란다.)
Query 유형과 무관하게 CBO는 항상 6 단계에 따라 최적의 Cost를 갖는 실행 계획을 선택하게 된다.
DBMS_XPLAN Package의 결과를 볼 때 위의 과정을 그려본다면 실행 계획을 이해하는데 도움이 될 수 있다.
10053 Event는 Query에 대해 Hard Parse(혹은 Optimization)가 발생할 때만 수행된다. Query가 수행될 때마다
활성화되는 10046 Event와 확실히 구별된다. 간혹 특정 SQL 문장만 Hard Parse를 유발하고 싶은 경우가 생긴다.
이것은 Oracle 10.2.0.4부터는 가능하다. DBM5-SHARED_POOL.PURGE Procedure가 이 기능을 제공한다.
DBMS_SHARED_POOL.PURGE Procedure의 사용법은 다음과 같다.
(자세한 사용법은 http://wiki.ex-em.com/index.php/DBMS_SHARED_POOL.PURGE을 참고하자).
--특정 SQL Cursor를 Purge
select address, hash value
from v$sqlarea
where sql_text like ’....’;
ADDRESS HASH VALUE
---------------
2F4651EC 3900782439
exec sys.dbms_shared_pool.purge(‘2F4651EC.3900782439’,’C’);
--특정 Procedure를 purge 할 때
exec sys.dbms_shared_pool.purge(‘USER.TEST_PROC’,’P’);
| Examples |
예제를 통해 10053 Event 의 유용함과 Trace File 해석의 중요성을 확인 하자.
사악한 ROWNUM
첫 번째 예제는 ROWNUM에 의한 실행 계획 변화 문제를 추적하는 것이다. Object를 생성.
test_script34.sql
아래 두 SQL 문장의 유일한 차이는 Inline View의 특정 단계에서의 ROWNUM 연산자의 사용 여부이다.
test_script35.sql
(Rownum을 사용하지 않은 경우의 결과)
PLAN_TABLE_OUTPUT
Plan hash value: 1743147828
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 1| 41 | 8 (25)|00:00:01| | 1 | SORT AGGREGATE | | 1| 41 | | | | 2 | VIEW | | 1| 41 | 8 (25)|00:00:01| | 3 | HASH GROUP BY | | 1| 91 | 8 (25)|00:00:01| |* 4 | HASH JOIN | | 1| 91 | 7 (15)|00:00:01| | 5 | MERGE JOIN CARTESIAN | | 1| 65 | 4 (0)|00:00:01| |* 6 | TABLE ACCESS FULL | T1 | 1| 39 | 2 (0)|00:00:01| | 7 | BUFFER SORT | | 1| 26 | 2 (0)|00:00:01| | 8 | TABLE ACCESS FULL | T3 | 1| 26 | 2 (0)|00:00:01| | 9 | TABLE ACCESS FULL | T2 | 1| 26 | 2 (0)|00:00:01|
Predicate Information (identified by operation id):
4 - access("T1"."C1"="T2"."C1" AND "T2"."C1"="T3"."C1")
6 - filter("T1"."C2"=1)
Note
- dynamic sampling used for this statement test_script36.sql
(Rownum을 사용한 경우의 결과)
PLAN_TABLE_OUTPUT
Plan hash value: 472191378
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 1 | 41 | 8 (25)| 00:00:01| | 1 | SORT AGGREGATE | | 1 | 41 | | | | 2 | VIEW | | 1 | 41 | 8 (25)| 00:00:01| | 3 | VIEW | | 1 | 52 | 8 (25)| 00:00:01| | 4 | VIEW | | 1 | 52 | 8 (25)| 00:00:01| | 5 | VIEW | | 1 | 52 | 8 (25)| 00:00:01| | 6 | HASH GROUP BY | | 1 | 91 | 8 (25)| 00:00:01| |* 7 | HASH JOIN | | 1 | 91 | 7 (15)| 00:00:01| | 8 | MERGE JOIN CARTESIAN | | 1 | 65 | 4 (0)| 00:00:01| |* 9 | TABLE ACCESS FULL | T1 | 1 | 39 | 2 (0)| 00:00:01| | 10 | BUFFER SORT | | 1 | 26 | 2 (0)| 00:00:01| | 11 | TABLE ACCESS FULL | T3 | 1 | 26 | 2 (0)| 00:00:01| | 12 | TABLE ACCESS FULL | T2 | 1 | 26 | 2 (0)| 00:00:01|
Predicate Information (identified by operation id):
7 - access("T1"."C1"="T2"."C1" AND "T2"."C1"="T3"."C1")
9 - filter("T1"."C2"=1)
Note
- dynamic sampling used for this statement
위 결과는 책의 결과와는 조금 다르다.
실행 계획의 가장 큰 차이는 어디에 있는가? Predicate 정보에서 ROWNUM이 사용되지 않은 첫 번째 경우
"6 - filter("T1"."C2"=1)" 조건이 사용되었다. 반면 ROWNUM이 사용된 두 번째 경우에는 "filter(" PUSH_KEY" = 1)"
조건이 사용되었다. 이것은 무엇을 의미하는가?
- ROWNUM이 사용되지 않은 경우 : push_key = 1 조건이 View 안으로 Push(실제 조건인 t1.c2 = 1 조건으로 변환
- ROWNUM이 사용된 경우 : push_key = 1 조건이 View 안으로 Push 되지못하고 View가 다 처리된 후 마지막에 적용
ROWNUM의 사용으로 인해 push_key = 1 조건이 View 안으로 Push 되지 않은 경우가 성능에 더 불리할 것이라는 것은
충분히 예측 가능할 것이다. 왜 이런 현상이 생겼는가? 이런 문제를 이미 경험한 사례가 있는 독자라면 ROWNUM을 사용함으로써
Query Transformation이 이루어지지 않았다는 것을 짐작할 수 있다. 10053 Event를 이용하면 CBO가 어떤 판단을 했는지(사실)
확인 할 수 있다. 아래는 두 번째 경우, ROWNUM이 사용된 경우에 Query Transformation에 어떤 영향을 미치는지를
10053 Event의 출력 결과를 발췌한 것이다.
Cost-Based Complex View Merging.txt
(책의 결과랑 다르므로 직접 확인해보자.)
우선 ROWNUM과 ORDER BY에 의해 CVM(Complex View Merging)이 이루어지지 않았다.
Predicate Move- Around (PM)
PM : Considering predicate move- around in SEL$1 (#1).
PM: Checking validity of predicate move-around in SEL$1(#1)
PM : PM bypassed : Reference to ROWNUM
PM : Passed validity checks
결정적으로 ROWNUM으로 인해 PM(Predicate Move-Around)가 실패했음을 알 수 있다.
PM이 성공적으로 이루어졌다면 push_key = 1 조건이 View 안으로 들어가서 보다 효율적인
실행 계획이 수립되었을 것이다. 하지만 예제를 보면 ROWNUM을 포함한 View에 대해서는
Predicate Pushing이 불가능하다.
Parallel or Not?
PARALLEL Hint를 사용함에도 불구하고 Parallel Query가 수행되지 않는 경우가 있다. 예제를 보자.
test_script37.sql
Primary Key를 갖는 Table이다. Parallel Query를 (기대하며) 수행한다.
test_script38.sql
PLAN_TABLE_OUTPUT
Plan hash value: 1018460547
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 1 | 6 (0) | 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | INDEX FAST FULL SCAN| T1_PK | 10000 | 6 (0) | 00:00:01 |
하지만 기대와 달리 Parallel Query가 수행되지 않고 Index Fast Full Scan이 선택되었다.
Oracle이 PARALLEL Hint를 무시했나? 아니다. Oracle은 Hint를 절대 무시하지 않는다. 만일 Hint를
명시했는데도 동작하지 않았다면 Oracle이 Hint가 동작할 수 없는 상황이거나 Hint로 명시한 것보다
더 좋은 어떤 것을 발견했다는 의미이다. 10053 Event에 그 해답이 있다. 아래에 해당 Query에 대한
10053 Trace 결과가 있다.
--- Trace File 생성 후 결과 확인 ---
이 결과에서 다음과 같은 정보를 얻을 수 있다.
- Table Full Scan에 대한 Serial Cost(361.18), Parallel Cost(100.33) 이다.
- Index Fast Full Scan의 Cost(6.16)이다.
즉, PARALLEL Hint를 고려해서 Parallel Cost를 계산해 봤지만, Index Fast Full Scan의 Cost보다 훨씬 낮기 때문에
Index Fast Full Scan이 선택된 것이다. 이것이 PARALLEL Hint의 기본적인 동작 방식이다. Parallel Execution을
일방적으로 선택하는 것이 아니라 Parallel Cost를 낮춤으로써 선택 될 확률을 높이는 방식이다.
Oracle10g에서 Parallel Cost의 계산 공식은 다음과 같다.
Paralell Cost = (Serial Cost) / (Degree * 0.9)
재미있는 것은 다음과 같이 Primary Key를 삭제하고 Unique Index를 사용하면 Parallel Query가 선택 된다.
test_script39.sql
PLAN_TABLE_OUTPUT
Plan hash value: 3110199320
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
| 0 | SELECT STATEMENT | | 1 | 88 (0)|00:00:02| | | | | 1 | SORT AGGREGATE | | 1 | | | | | | | 2 | PX COORDINATOR | | | | | | | | | 3 | PX SEND QC (RANDOM) |:TQ10000| 1 | | | Q1,00 | P->S | QC (RAND) | | 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | | | 5 | PX BLOCK ITERATOR | | 10000 | 88 (0)|00:00:02| Q1,00 | PCWC | | | 6 | TABLE ACCESS FULL | T1 | 10000 | 88 (0)|00:00:02| Q1,00 | PCWP | |
Primary Key와 Unique Index사이에 어떤 차이점이 있는지 10053 Event를 통해 확인해 보자.
--- Trace File 생성 후 결과 확인 ---
놀랍게도 Index Fast Full Scan은 Cost계산에서 전혀 고려되지 않고 있다. 왜 그런가?
Primary Key와 Unique Index의 가장 큰 차이점에 답이 있다. Primary Key는 NOT NULL 조건을
암묵적으로 사용한다. Unique Index는 Column에 NULL값이 있다는 가정이 담겨 있다.
Unique Index는 "select count(*) from t1"과 같은Query에서는 사용될 수 없다. NULL 값이 누락되서
전체 Row수를 얻을 수 없기 때문에 Cost 계산 대상에서 아예 제외되어 버리는 것이다. NOT NULL 조건이
부여되면 다시 Index Fast Full Scan이 선택되는 것을 확인 할 수 있다.
test_script40.sql
PLAN_TABLE_OUTPUT
Plan hash value: 3675732849
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 1 | 6 (0) | 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | INDEX FAST FULL SCAN| T1_N1 | 10000 | 6 (0) | 00:00:01 |
[출처] Chap02.CBO Tools Part.02 (ProDBA) |작성자 디비