다른 명령
10053 이벤트
10053 이벤트 개요
- Cost Base Optimizer (이하, CBO)의 과정을 Trace 파일에 직접 출력한 것
- Query Transformation이 점점 복잡해지면서 low level의 troble shooting tool의 필요성이 제기되어 만들어짐
- 이를 통해 query 수행 시작부터 결과를 보여주기까지의 과정을 알 수 있다.
- 즉, 옵티마이저용 디버그 트레이스 이벤트, 10053 이벤트 트레이스 파일은 실행계획 평가와 관련한 카디널리티 및 비용을 계산하기 위해 옵티마이저가 수행하는 많은 일들을 보여준다. 하지만 트레이스 파일의 정보는 완전하지 않다.
- 10053 Events는 CBO 문제를 추적하는 가장 강력한 Tool.
- 하지만 더 쉽고 좋은 방법이 많기 때문에 자주 사용되지는 않는다.
- Explain Plan 명령, GATHER_PLAN_STATISTICS Hint, DBMS_XPLAN Package등을 이용하면 CBO 문제의 핵심인 실행계획 이상 현상을 상당 부분 추적할 수 있다.
- 10053 Event의 위대한 점은 CBO가 수행하는 일련의 작업을 시간 순으로 추적할 수 있다는 것.
- 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 Trace
10053 Event를 발생시키는 방법
SQL> alter session set events '10053 trace name context forever'; SQL> alter session set events '10053 trace name context forever, level 1'; SQL> alter session set events '10053 trace name context forever, level 2';
- 위의 두 문장은 정확히 같은 일을 하지만 마지막 옵션을 사용하게 되면 보통 트레이스 파일의 앞부분에 나타나는옵티마이저 파라미터 목록이 제외되어 약간 짧은 트레이스 파일이 만들어 진다.
트레이스를 중단시키는 방법
SQL> alter session set events '10053 trace name context off';
- 10g에서 수행하는 것이 아니라면, PL/SQL 블록에 포함된 쿼리문에 대해서는 10053 트레이스 기능이 작동하지 않는다.
- 10gR2 트레이스 파일은 이전 버전의 것과는 다른데, 섹션의 순서가 다르고 여러 단서나 설명이 추가 되었으며 시스템통계가 기록되고 조건절 및 개요와 함께 최종 실행계획이 포함된다.
10053 이벤트 분석
- 10053 이벤트 분석을 쉽게 하려면 순서를 알아야 한다
10053 이벤트 분석 순서
DBMS 정보, OS 정보, 하드웨어 정보 그리고 SQL을 실행한 Client의 정보 출력
Trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_32591.trc Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1 System name: Linux Node name: localhost.localdomain Release: 2.6.18-194.el5 Version: #1 SMP Tue Mar 16 21:52:43 EDT 2010 Machine: i686 Instance name: orcl Redo thread mounted by this instance: 1 Oracle process number: 36 Unix process pid: 32591, image: oracle@localhost.localdomain (TNS V1-V3) *** 2011-09-02 03:17:33.670 *** SESSION ID:(47.961) 2011-09-02 03:17:33.670 *** CLIENT ID:() 2011-09-02 03:17:33.670 *** SERVICE NAME:(SYS$USERS) 2011-09-02 03:17:33.670 *** MODULE NAME:(sqlplus@localhost.localdomain (TNS V1-V3)) 2011-09-02 03:17:33.670 *** ACTION NAME:() 2011-09-02 03:17:33.670
쿼리블럭 정보와 수행된 SQL을 Parser로부터 받아서 출력
- Trace File은 Query Block 정보를 생성하는 것에서부터 시작한다.
- SQL 문장에 대해 Parse Tree를 생성하는 과정에서 기본적인 Query Block 정보가 생성된다.
- 여기서 사용된 Query Block 명이 Plan Statistics에서 보이는 Query Block Name 정보와 일치한다는 것에 주목하자.
- Query Transformation에 의해 Query Block 정보가 바뀌는 경우에는 최종 Query Block 개수와 이름이 변경될 수 있다.
- 쿼리 블록(쉽게 말하면 가시적인 서브쿼리 또는 인라인 뷰)은 자신의 명칭을 부여 받을 수 있다.
- 어떤 블록에 qb_name 힌트를 사용해 명시적으로 명칭을 부여할 수 있고, global 힌트에서 그 것을 오브젝트 별칭으로
- 참조할 수 있다. 만일 그런 식으로 이름을 직접 부여하지 않으면 오라클은 각 블록에 대해 시스템이 자동으로 부여한 이름을 사용한다.
- 만일 머지 될 수 없는 서브쿼리나 뷰를 포함하는 복잡한 쿼리인 경우 10053 트레이스 파일은 완전히 구별된 여러 섹션을 가질 수 있다.
- (메인 쿼리와 머지할 수 없는 쿼리 블록 각각 한 섹션, 그리고 머지할 수 없는 부분이 조인되는 곳에서 한 섹션)
- QUERY BLOCK SIGNATURE 부분에서는 쿼리 블록에 나타나는 테이블, 또는 더 정확히는 별칭을 열거함으로써 쿼리 블록에 나타나는 쿼리가 어떻게 여러 부분으로 나뉘는 지 알 수 있게 한다.
Registered qb: SEL$1 0x723a04 (PARSER) --------------------- QUERY BLOCK SIGNATURE --------------------- signature (): qb_name=SEL$1 nbfros=1 flg=0 // 책에서의 예제대로라면 쿼리블럭명이 MAIN이어야 한다. fro(0): flg=4 objn=73928 hint_alias="D"@"SEL$1" // from 절에 Alias가 'D'인 테이블이 있다. Registered qb: SEL$2 0x721978 (PARSER) --------------------- QUERY BLOCK SIGNATURE --------------------- signature (): qb_name=SEL$2 nbfros=1 flg=0 fro(0): flg=4 objn=73933 hint_alias="E"@"SEL$2" // from 절에 Alias가 'E'인 테이블이 있다. SPM: statement not found in SMB ************************** Automatic degree of parallelism (ADOP) ************************** Automatic degree of parallelism is disabled: Parameter. PM: Considering predicate move-around in query block SEL$1 (#0) ************************** Predicate Move-Around (PM) ************************** OPTIMIZER INFORMATION ****************************************** ----- Current SQL Statement for this session (sql_id=cdc70ry0ahu7k) ----- select --+ QB_NAME_MAIN -- department_id, department_name, manager_id, location_id from hr.departments d where exists (select --+ QB_NAME_SUB -- null from hr.employees e where e.department_id=d.department_id) *******************************************
10053 Event에서 사용될 용어를 출력
Legend The following abbreviations are used by optimizer trace. CBQT - cost-based query transformation JPPD - join predicate push-down OJPPD - old-style (non-cost-based) JPPD FPD - filter push-down PM - predicate move-around CVM - complex view merging SPJ - select-project-join SJC - set join conversion SU - subquery unnesting OBYE - order by elimination OST - old style star transformation ST - new (cbqt) star transformation CNT - count(col) to count(*) transformation JE - Join Elimination JF - join factorization SLP - select list pruning DP - distinct placement qb - query block 이하 생략....
Optimizer에 관련된 성능 파라미터를 출력하고 연이어 Bug Fix Cotrol 정보를 출력
- Query Block이 부여되고 나면 Optimizer는 Parameter 정보와 Bug Fix Control 정보를 출력한다.
- 변경된 Parameter 값과 기본 Parameter 값이 순서대로 출력된다.
- 그리고 Parameter 정보와 함께 Bug Fix Control의 활성화/비활성화 여부가 출력된다.
- 마지막으로는 OPT_PARAM Hint에 의해 변경된 Optimizer Parameter 정보를 출력한다.
- OPT_PARAM Hint는 SQL 문장 레벨에서 Optimizer Parameter 값을 제어하기 위한 목적으로 고안되었으며 Oracle 10gR2 부터 지원된다.
- 사용법은 다음과 같다.
--+ opt_param('_optimizer_push_pred_cost_based', 'false') --
- 옵티마이저와 관련된 파라미터의 현재 설정 값을 열거한다.
*************************************** PARAMETERS USED BY THE OPTIMIZER // 이게 바로 Title!! ******************************** ************************************* PARAMETERS WITH ALTERED VALUES // 여기서 변경된 파라미터가 출력된다. ****************************** Compilation Environment Dump _smm_min_size = 143 KB _smm_max_size = 28672 KB _smm_px_max_size = 71680 KB Bug Fix Control Environment // 만약 optimizer_mode를 다른 값으로 (예를 들어, first_rows_1) 변경했다면 이 곳에서 변경된 파라미터가 출력되었을 것. ************************************* PARAMETERS WITH DEFAULT VALUES // 파라미터의 디폴트 값이 출력 되었다. ****************************** Compilation Environment Dump optimizer_mode_hinted = false optimizer_features_hinted = 0.0.0 parallel_execution_enabled = true parallel_query_forced_dop = 0 parallel_dml_forced_dop = 0 parallel_ddl_forced_degree = 0 parallel_ddl_forced_instances = 0 _query_rewrite_fudge = 90 optimizer_features_enable = 11.2.0.1 _optimizer_search_limit = 5 cpu_count = 1 active_instance_count = 1 parallel_threads_per_cpu = 2 hash_area_size = 131072 bitmap_merge_area_size = 1048576 sort_area_size = 65536 sort_area_retained_size = 0 _sort_elimination_cost_ratio = 0 _optimizer_block_size = 8192 _sort_multiblock_read_count = 2 _hash_multiblock_io_count = 0 _db_file_optimizer_read_count = 8 _optimizer_max_permutations = 2000 ...중간 생략... Bug Fix Control Environment fix 3834770 = 1 fix 3746511 = enabled fix 4519016 = enabled ...중간 생략... *************************************** PARAMETERS IN OPT_PARAM HINT // Opt_param 힌트를 사용한 경우 여기에 나타난다. **************************** 이하 생략...
Heuristic Query Transformation 과정이 출력된다.
- 이제 본격적인 Optimization이 수행되며, 그 첫 단계로 Query Transformation이 수행된다.
- Query Transformation은 QT(Query Transformation)와 CBQT(Cost Based Query Transformation)의 두 단계로 구분된다.
- QT 단계에서는 Cost 계산이 불필요한 간단한 Transformation을 수행한다. QT 단계가 끝나면 CBQT가 수행된다.
- CBQT는 Cost를 감안해서 Transformation을 수행해야 하는데, 이 것을 달리 해석하면 Transformation 단계에서 실제 Transformation이 일어나지 않고 Cost가 계산되는 Optimization 단계에서 Transformation이 이루어진다는 것을 의미한다.
- CBQT의 등장과 함께 Transformation 단계와 Optimization 단계의 융합이 이루어진 것이다.
- SQL 문장 레벨에서 CBQT를 Disable 하려면 조금 전에 보여 준 예시대로 OPT_PARAM Hint를 사용하면 된다.
Considering Query Transformations on query block SEL$1 (#0) ************************** Query transformations (QT) ************************** JF: Checking validity of join factorization for query block SEL$2 (#0) JF: Bypassed: not a UNION or UNION-ALL query block. ST: not valid since star transformation parameter is FALSE TE: Checking validity of table expansion for query block SEL$2 (#0) TE: Bypassed: No partitioned table in query block. CBQT: Validity checks passed for cdc70ry0ahu7k. Common Subexpression elimination (CSE) ************************* CSE: Considering common sub-expression elimination in query block SEL$2 (#0) ************************* Common Subexpression elimination (CSE) ************************* CSE: CSE not performed on query block SEL$2 (#0). CSE: CSE not performed on query block SEL$1 (#0). OBYE: Considering Order-by Elimination from view SEL$1 (#0) *************************** Order-by elimination (OBYE) *************************** OBYE: OBYE bypassed: no order by to eliminate. query block SEL$1 (#0) unchanged Considering Query Transformations on query block SEL$1 (#0) 이하 생략...
Bind Peeking 수행
- 바인드 변수를 사용한 쿼리는 먼저 파싱과 최적화가 이루어진 후에 바인드 변수에 바인딩이 이루어진다는 사실이다.
- 이 말은 곧 최적화가 이루어지는 시점에는 변수로 제공되는 컬럼은 바인딩값에 대한 통계정보를 사용할 수 없다는 것을 의미한다.
- 그렇다면 어쩔 수 없이 값들이 균일하게 분포되어 있다는 가정을 세운 후에 최적화를 수행할 수 밖에 없다.
- 그러므로 만약 분포도가 균일하지 못한 컬럼에 바인드 변수를 사용하게되면 최악의 실행계획이 생성될 수도 있다.
- 우리는 파싱의 부하를 줄이기 위해 바인드 변수를 사용하는 것이 좋다는 것을 알고 있다.
- 그러나 실전에서는 데이터의 분포가 균일하지 않은 경우가 많기 때문에 억지로 동적 SQL을 사용해야 할 때가 종종 있었다.
- 이러한 한계를 극복하기 위해 새롭게 제공되기 시작한 것이 'PEEKING'이라는 기능이다.
- 이 단어를 사전에서 찾아보면 '몰래 엿보다'라는 뜻을 가지고 있다.
- 이 기능은 바인드 변수를 사용한 쿼리가 처음 실행될 때 옵티마이저는 사용자가 지정한 바인드 변수의 값을 '살짝 커닝' 함으로써 조건절의 컬럼값이 상수값으로 제공될 대와 마찬가지로 선택도를 확인하여 최적화를 수행하도록 한다.
- 전체를 평균적인 분포도로 보는 것이나 최초의 단 한가지 경우만으로 전체를 대신할 수 있다는 것이나 논리적인 확률은 그다지 다르다고 할 수는 없다.
- 그러나 이들은 분명히 다른 결과를 가져올 수 있다. 가령, '성별' 컬럼에 'M'이 99%, 'F'가 1% 들어있고, 이 조건만 가진 쿼리가 있다고 가정해 보자.
- 평균적인 접근 방법으로는 이 컬럼은 좀처럼 인덱스를 사용하지 않을 것이다.
- 그러나 만약 피킹이 적용되었다면 'M'이 첫 번째일 때는 전체테이블스캔, 'F'일 때는 인덱스 스캔이 선택되었을 것이다.
- 이처럼 두 가지 방법은 다른 실행계획을 가져올 수 있다.
******************************************* Peeked values of the binds in SQL statement ******************************************* ------ Bind Info ------------------------ // 책에 나온 예제대로라면 바인드 정보가 출력되겠지만, 본인은 바인드 변수를 넣지 않았으므로 패스.
Cost Based Query Transformation 과정 출력
- Statistics 정보를 추출하고, Optimization을 위한 기본적인 정보들을 계산한다.
- Optimization의 기본 단위는 SQL 문장 전체가 아니라 Query Block이다.
- CBO는 Query Block 레벨에서 Optimization을 수행하고 그 결과를 다시 상위 Query Block에서 사용하는 방식을 사용한다.
CBQT: Considering cost-based transformation on query block SEL$1 (#0) ******************************** COST-BASED QUERY TRANSFORMATIONS ******************************** FPD: Considering simple filter push (pre rewrite) in query block SEL$2 (#0) FPD: Current where clause predicates "E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID" FPD: Considering simple filter push (pre rewrite) in query block SEL$1 (#0) FPD: Current where clause predicates EXISTS (SELECT NULL FROM "HR"."EMPLOYEES" "E") OBYE: Considering Order-by Elimination from view SEL$1 (#0) ...중간 생략... ***************************** Cost-Based Subquery Unnesting // 여기서 서브쿼리가 조인으로 변경되는 작업이 수행되며, 이것이 Subquery Unnesting! ***************************** SU: Unnesting query blocks in query block SEL$1 (#1) that are valid to unnest. Subquery Unnesting on query block SEL$1 (#1)SU: Performing unnesting that does not require costing. SU: Considering subquery unnest on query block SEL$1 (#1). SU: Checking validity of unnesting subquery SEL$2 (#2) SU: Passed validity checks. SU: Transforming EXISTS subquery to a join. // 서브쿼리가 조인으로 바뀐 것을 알 수 있음 Registered qb: SEL$5DA710D3 0x760dd8 (SUBQUERY UNNEST SEL$1; SEL$2) // 쿼리블럭이 생성됨 --------------------- QUERY BLOCK SIGNATURE // 새로 생성된 쿼리블럭의 구조를 보여준다. --------------------- signature (): qb_name=SEL$5DA710D3 nbfros=2 flg=0 fro(0): flg=0 objn=73928 hint_alias="D"@"SEL$1" fro(1): flg=0 objn=73933 hint_alias="E"@"SEL$2" ******************************* Cost-Based Complex View Merging ******************************* ...중간 생략... ************************* Set-Join Conversion (SJC) ************************* SJC: not performed JE: Considering Join Elimination on query block SEL$5DA710D3 (#1) ************************* Join Elimination (JE) ************************* SQL:******* UNPARSED QUERY IS ******* SELECT "D"."DEPARTMENT_ID" "DEPARTMENT_ID","D"."DEPARTMENT_NAME" "DEPARTMENT_NAME","D"."MANAGER_ID" "MANAGER_ID","D"."LOCATION_ID" "LOCATION_ID" FROM "HR"."EMPLOYEES" "E","HR"."DEPARTMENTS" "D" WHERE "E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID" JE: cfro: EMPLOYEES objn:73928 col#:11 dfro:DEPARTMENTS dcol#:11 JE: cfro: EMPLOYEES objn:73928 col#:11 dfro:DEPARTMENTS dcol#:11 SQL:******* UNPARSED QUERY IS ******* SELECT "D"."DEPARTMENT_ID" "DEPARTMENT_ID","D"."DEPARTMENT_NAME" "DEPARTMENT_NAME","D"."MANAGER_ID" "MANAGER_ID","D"."LOCATION_ID" "LOCATION_ID" FROM "HR"."EMPLOYEES" "E","HR"."DEPARTMENTS" "D" WHERE "E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID" Query block SEL$5DA710D3 (#1) unchanged PM: Considering predicate move-around in query block SEL$5DA710D3 (#1) ************************** Predicate Move-Around (PM) ************************** PM: PM bypassed: Outer query contains no views. PM: PM bypassed: Outer query contains no views. ...중간 생략... ************************************ Cost-based predicate pushdown (JPPD) ************************************ ...중간 생략... --------------------- QUERY BLOCK SIGNATURE // 쿼리블럭 SEL$5DA710D3가 생성되었고 쿼리블럭 MAIN과 SUB가 통합되었다. --------------------- signature (optimizer): qb_name=SEL$5DA710D3 nbfros=2 flg=0 fro(0): flg=0 objn=73928 hint_alias="D"@"SEL$1" fro(1): flg=0 objn=73933 hint_alias="E"@"SEL$2"
시스템 통계정보와 테이블과 인덱스의 통계정보 출력
- CBO는 System Statistics를 가장 먼저 조사한다.
- System Statistics는 SYS.SYS_AUX$ Table에 저장되어 있다.
- 그 후 Dictionary View를 통해 Object Statistics 정보를 추출한다.
// 이제부터 physical optimizer 과정이 진행된다. ----------------------------- SYSTEM STATISTICS INFORMATION ----------------------------- Using NOWORKLOAD Stats CPUSPEEDNW: 2696 millions instructions/sec (default is 100) IOTFRSPEED: 4096 bytes per millisecond (default is 4096) IOSEEKTIM: 10 milliseconds (default is 10) MBRC: -1 blocks (default is 8) *************************************** BASE STATISTICAL INFORMATION *********************** Table Stats:: // DEPARTMENTS의 통계정보를 추출한다. Table: DEPARTMENTS Alias: D #Rows: 27 #Blks: 5 AvgRowLen: 21.00 Index Stats:: Index: DEPT_ID_PK Col#: 1 LVLS: 0 #LB: 1 #DK: 27 LB/K: 1.00 DB/K: 1.00 CLUF: 1.00 Index: DEPT_LOCATION_IX Col#: 4 LVLS: 0 #LB: 1 #DK: 7 LB/K: 1.00 DB/K: 1.00 CLUF: 1.00 *********************** Table Stats:: Table: EMPLOYEES Alias: E // EMPLOUEE의 통계정보를 출력한다. #Rows: 107 #Blks: 5 AvgRowLen: 69.00 Index Stats:: Index: EMP_DEPARTMENT_IX Col#: 11 LVLS: 0 #LB: 1 #DK: 11 LB/K: 1.00 DB/K: 1.00 CLUF: 7.00 Index: EMP_EMAIL_UK Col#: 4 LVLS: 0 #LB: 1 #DK: 107 LB/K: 1.00 DB/K: 1.00 CLUF: 19.00 Index: EMP_EMP_ID_PK Col#: 1 LVLS: 0 #LB: 1 #DK: 107 LB/K: 1.00 DB/K: 1.00 CLUF: 2.00 Index: EMP_JOB_IX Col#: 7 LVLS: 0 #LB: 1 #DK: 19 LB/K: 1.00 DB/K: 1.00 CLUF: 8.00 Index: EMP_MANAGER_IX Col#: 10 LVLS: 0 #LB: 1 #DK: 18 LB/K: 1.00 DB/K: 1.00 CLUF: 7.00 Index: EMP_NAME_IX Col#: 3 2 LVLS: 0 #LB: 1 #DK: 107 LB/K: 1.00 DB/K: 1.00 CLUF: 15.00
테이블 단위로 최적의 Access path와 Cost를 출력
- Statistics에서 추출한 정보는 Single Table Access 방식을 판단하는 기준으로 사용된다.
- Single Table Access의 Cost를 계산하는 작업은 앞서 언급한 것처럼 특정 Query Block 단위로 이루어진다.
- Query Block 내에 속한 Table과 조건에 대해 최적의 Access 방식을 산출한다.
Access path analysis for EMPLOYEES // EMPLOYEE 테이블의 Access path를 최적화 한다. *************************************** SINGLE TABLE ACCESS PATH Single Table Cardinality Estimation for EMPLOYEESE Table: EMPLOYEES Alias: E Card: Original: 107.000000 Rounded: 107 Computed: 107.00 Non Adjusted: 107.00 Access Path: TableScan Cost: 3.00 Resp: 3.00 Degree: 0 Cost_io: 3.00 Cost_cpu: 73057 Resp_io: 3.00 Resp_cpu: 73057 Access Path: index (index (FFS)) Index: EMP_DEPARTMENT_IX resc_io: 2.00 resc_cpu: 19841 ix_sel: 0.000000 ix_sel_with_filters: 1.000000 Access Path: index (FFS) Cost: 2.00 Resp: 2.00 Degree: 1 Cost_io: 2.00 Cost_cpu: 19841 Resp_io: 2.00 Resp_cpu: 19841 Access Path: index (FullScan) Index: EMP_DEPARTMENT_IX resc_io: 1.00 resc_cpu: 28321 ix_sel: 1.000000 ix_sel_with_filters: 1.000000 Cost: 1.00 Resp: 1.00 Degree: 1 Best:: AccessPath: IndexRange Index: EMP_DEPARTMENT_IX Cost: 1.00 Degree: 1 Resp: 1.00 Card: 107.00 Bytes: 0 Access path analysis for DEPARTMENTS // DEPARTMENT 테이블의 Access path를 최적화 한다. *************************************** SINGLE TABLE ACCESS PATH Single Table Cardinality Estimation for DEPARTMENTSD Table: DEPARTMENTS Alias: D Card: Original: 27.000000 Rounded: 27 Computed: 27.00 Non Adjusted: 27.00 Access Path: TableScan Cost: 3.00 Resp: 3.00 Degree: 0 Cost_io: 3.00 Cost_cpu: 41277 Resp_io: 3.00 Resp_cpu: 41277 Best:: AccessPath: TableScan // 최적의 Access Path는 full table scan 이다. Cost: 3.00 Degree: 1 Resp: 3.00 Card: 27.00 Bytes: 0 ***************************************
최적의 조인방법과 조인순서를 정한다
- 이제 가장 중요한 단계로 Join 순서와 Join 종류를 결정하게 된다.
- Join에 참여하는 Table들에 대해 Joing 순서 별로 어떤 Join 방식이 가장 최적의 Cost를 갖는지 계산하게 된다.
OPTIMIZER STATISTICS AND COMPUTATIONS *************************************** GENERAL PLANS *************************************** Considering cardinality-based initial join order. Permutations for Starting Table :0 Join order1: DEPARTMENTSD#0 EMPLOYEESE#1 *************** Now joining: EMPLOYEESE#1 *************** NL Join ...이하 생략
SQL dump와 Explain Plan Dump를 수행하여 SQL이 수행되었던 당시의 SQL과 실행계획 출력
Starting SQL statement dump // SQL dump가 수행된다. user_id=0 user_name=SYS module=sqlplus@localhost.localdomain (TNS V1-V3) action= sql_id=cdc70ry0ahu7k plan_hash_value=-1689275523 problem_type=3 ----- Current SQL Statement for this session (sql_id=cdc70ry0ahu7k) ----- select --+ QB_NAME_MAIN -- department_id, department_name, manager_id, location_id from hr.departments d where exists (select --+ QB_NAME_SUB -- null from hr.employees e where e.department_id=d.department_id) sql_text_length=209 sql=select --+ QB_NAME_MAIN -- department_id, department_name, manager_id, location_id from hr.departments d where exists (select --+ QB_NAME_SUB -- null from hr.employees e where e.department_id=d.department_id) ----- Explain Plan Dump ----- // Explain Plan Dump가 수행된다. ----- Plan Table ----- ============ Plan Table ============ -----------------------------------------------+-----------------------------------+ | Id | Operation | Name | Rows | Bytes | Cost | Time | -----------------------------------------------+-----------------------------------+ | 0 | SELECT STATEMENT | | | | 3 | | | 1 | NESTED LOOPS SEMI | | 10 | 240 | 3 | 00:00:01 | | 2 | TABLE ACCESS FULL | DEPARTMENTS | 27 | 567 | 3 | 00:00:01 | | 3 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX| 41 | 123 | 0 | | -----------------------------------------------+-----------------------------------+ Predicate Information: ---------------------- 3 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID") Content of other_xml column =========================== db_version : 11.2.0.1 parse_schema : SYS plan_hash : 2605691773 plan_hash_2 : 2663028966 Outline Data: --+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('11.2.0.1') DB_VERSION('11.2.0.1') ALL_ROWS OUTLINE_LEAF(@"SEL$5DA710D3") UNNEST(@"SEL$2") OUTLINE(@"SEL$1") OUTLINE(@"SEL$2") FULL(@"SEL$5DA710D3" "D"@"SEL$1") INDEX(@"SEL$5DA710D3" "E"@"SEL$2" ("EMPLOYEES"."DEPARTMENT_ID")) LEADING(@"SEL$5DA710D3" "D"@"SEL$1" "E"@"SEL$2") USE_NL(@"SEL$5DA710D3" "E"@"SEL$2") END_OUTLINE_DATA --
마지막으로 Optimizer state dump를 수행하여 SQL이 수행되었던 당시의 옵티마이져 관련 파라미터 정보, Bug Fix Control 등을 출력
Optimizer state dump: Compilation Environment Dump optimizer_mode_hinted = false optimizer_features_hinted = 0.0.0 parallel_execution_enabled = true parallel_query_forced_dop = 0 parallel_dml_forced_dop = 0 parallel_ddl_forced_degree = 0 ...중간 생략... Bug Fix Control Environment // Bug Fix Control 정보가 출력된다. fix 3834770 = 1 fix 3746511 = enabled fix 4519016 = enabled fix 3118776 = enabled fix 4488689 = enabled ...중간 생략... Query Block Registry: SEL$2 0x721978 (PARSER) SEL$5DA710D3 0x723a04 (SUBQUERY UNNEST SEL$1; SEL$2) FINAL SEL$1 0x723a04 (PARSER) SEL$5DA710D3 0x723a04 (SUBQUERY UNNEST SEL$1; SEL$2) FINAL : call(in-use=27212, alloc=65488), compile(in-use=132504, alloc=191564), execution(in-use=6332, alloc=8136) End of Optimizer State Dump Dumping Hints // Hint dump를 수행하여 최종 적용된 힌트 정보를 출력한다. ============= ====================== END SQL Statement Dump ======================
- 참고문서 : 비용기반의 오라클 원리
새로 쓴 대용량 데이터베이스 솔루션 1 Optimizing Oracle Optimizer