|
|
71번째 줄: |
71번째 줄: |
| | | |
| ---------- | | ---------- |
− | = 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을 결정하는 일련의 과정이 출력
| |
− |
| |
− | == 10053 Event의 사용 방법 ==
| |
− | <source lang=sql>
| |
− | alter session set events ‘10053 trace name context forever, level 1’;
| |
− | ... <쿼리 실행> ...
| |
− | alter session set events ’10053 trace name context off’;
| |
− | </source>
| |
− | * 출력 결과는 User Dump Directory에 Trace File 형태로 기록되며 내용은 Version마다 상이하다.
| |
− | * 최신 버전의 Trace File은 다음과 같은 내용을 기록한다.
| |
− | ** Query Block 생성
| |
− | ** Optimizer Parameter, Bug Fix Control 정보
| |
− | ** Query Transformation 과정
| |
− | ** Statistics 정보
| |
− | ** Single Table Access 방식 선택
| |
− | ** Join 순서와 Join방식 선택
| |
− |
| |
− | === 10053 Event 테스트 예제 ===
| |
− | * 각 단계별로 어떤 내용이 기록되는지 예제를 통해서 확인하자. 우선 필요한 Object 들을 생성
| |
− | <source lang=shell>
| |
− | test_script32.sql
| |
− | </source>
| |
− | * Query를 수행하고, Plan Statistics를 조회
| |
− | <source lang=shell>
| |
− | test_script33.sql
| |
− | </source>
| |
− | ----
| |
− | <source lang=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]
| |
− | </source>
| |
− |
| |
− | * 실행 계획에서 유의해서 봐야 할 정보는 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 파일 일부분을 발췌)
| |
− | <source lang=shell>
| |
− | 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"
| |
− | </source>
| |
− |
| |
− | ==== [단계 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부터 지원되며 사용법은 다음과 같다.
| |
− | <source lang=shell>
| |
− | ALTER SESSION SET "_FIX_CONTROL" = '3746511:on','4519016:off' ;
| |
− | </source>
| |
− | * "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 정보를 출력.
| |
− | <source lang=shell>
| |
− | ........
| |
− | _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
| |
− | .........
| |
− | </source>
| |
− |
| |
− | * SQL 문장 레벨에서 Optimizer Parameter 값을 제어하기 위한 목적으로 고안되었으며 Oracle10g R2부터 지원.
| |
− | <source lang=sql>
| |
− | 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(+);
| |
− | </source>
| |
− |
| |
− | ==== [단계 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 값을 변경해 주면 된다.
| |
− | <source lang=sql>
| |
− | -- 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;
| |
− | </source>
| |
− |
| |
− | * SQL 문장 레벨에서 CBQT를 Disable 하려면 OPT PARAM Hint를 사용하면 된다.
| |
− | <source lang=sql>
| |
− | 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(+);
| |
− | </source>
| |
− |
| |
− | ==== [단계 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에 저장되어 있다.
| |
− | <source lang=sql>
| |
− | select *
| |
− | from sys.sys_aux$;
| |
− | </source>
| |
− | : (직접 실행은 해봤지만 테이블이 존재하지 않는다는 메세지가 출력 됬다. 직접 조회해보기 바란다.)
| |
− | * 그 후 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이 선택될 것이다.
| |
− |
| |
− | <source lang=sql>
| |
− | 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(+);
| |
− | </source>
| |
− |
| |
− | <source lang=shell>
| |
− | 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
| |
− | </source>
| |
− |
| |
− | * 통계 정보가 없거나 DYNAMIC_SAMPLING Hint가 사용되는 경우에는 Dynamic Sampling이 수행 된다.
| |
− | * Dynamic Sampling은 특정 Table에 대해 주어진 Block 수(32)만큼 Sampling을 수행. Table에 대해 사용된Predicate를 확인해서 예상 Row수를 계산.
| |
− | * 복잡한 Predicate에 대해 좀 더 정확한 예측이 가능.
| |
− | * Dynamic Sampling이 사용된 경우에는 Single Table Access 정보에 내용이 추가로 출력.
| |
− | <source lang=sql>
| |
− | 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(+);
| |
− | </source>
| |
− | : 단계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이 최종 선택.
| |
− | <source lang=sql>
| |
− | 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
| |
− | </source>
| |
− | : 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
| |
− | <source lang=sql>
| |
− | select address, hash value
| |
− | from v$sqlarea
| |
− | where sql_text like '....' ;
| |
− |
| |
− | ------------------------------------
| |
− | ADDRESS HASH VALUE
| |
− | ----------- ---------------
| |
− | 2F4651EC 3900782439
| |
− | </source>
| |
− | <source lang=sql>
| |
− | exec sys.dbms_shared_pool.purge(‘2F4651EC.3900782439’,’C’);
| |
− | </source>
| |
− |
| |
− | * 특정 Procedure를 purge 할 때
| |
− | <source lang=sql>
| |
− | exec sys.dbms_shared_pool.purge(‘USER.TEST_PROC’,’P’);
| |
− | </source>
| |
− |
| |
− | | Examples |
| |
− |
| |
− | * 예제를 통해 10053 Event 의 유용함과 Trace File 해석의 중요성을 확인 하자.
| |
− | * 사악한 ROWNUM
| |
− | ** 첫 번째 예제는 ROWNUM에 의한 실행 계획 변화 문제를 추적하는 것이다. Object를 생성.
| |
− | :** test_script34.sql
| |
− | * 아래 두 SQL 문장의 유일한 차이는 Inline View의 특정 단계에서의 ROWNUM 연산자의 사용 여부이다.
| |
− |
| |
− | <source lang=shell>
| |
− | test_script35.sql
| |
− | </source>
| |
− |
| |
− | * Rownum을 사용하지 않은 경우의 결과
| |
− |
| |
− | <source lang=sql>
| |
− | 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
| |
− | </source>
| |
− |
| |
− | <source lang=shell>
| |
− | test_script36.sql
| |
− | </source>
| |
− |
| |
− | * Rownum을 사용한 경우의 결과
| |
− |
| |
− | <source lang=sql>
| |
− | 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
| |
− | </source>
| |
− |
| |
− | 위 결과는 책의 결과와는 조금 다르다.
| |
− |
| |
− | 실행 계획의 가장 큰 차이는 어디에 있는가?
| |
− |
| |
− | 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가 수행되지 않는 경우가 있다. 예제를 보자.
| |
− |
| |
− |
| |
− | <source lang=shell>
| |
− | test_script37.sql
| |
− | </source>
| |
− |
| |
− | * Primary Key를 갖는 Table이다. Parallel Query를 (기대하며) 수행한다.
| |
− |
| |
− | <source lang=shell>
| |
− | test_script38.sql
| |
− | </source>
| |
− | <source lang=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 |
| |
− | -----------------------------------------------------------------------
| |
− | </source>
| |
− |
| |
− | 하지만 기대와 달리 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가 선택 된다.
| |
− |
| |
− |
| |
− | <source lang=shell>
| |
− | test_script39.sql
| |
− | </source>
| |
− | <source lang=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 | |
| |
− | --------------------------------------------------------------------------------------------------------
| |
− | </source>
| |
− |
| |
− | 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이 선택되는 것을 확인 할 수 있다.
| |
− |
| |
− |
| |
− | <source lang=shell>
| |
− | test_script40.sql
| |
− | </source>
| |
− | <source lang=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 |
| |
− | -----------------------------------------------------------------------
| |
− | </source>
| |
− | [출처] Chap02.CBO Tools Part.02 (ProDBA) |작성자 디비
| |
− | [[category:oracle]]
| |