1 10053 이벤트[편집]

1.1 10053 이벤트 개요[편집]

Cost Base Optimizer (이하, CBO)의 과정을 Trace 파일에 직접 출력한 것
Query Transformation이 점점 복잡해지면서 low level의 troble shooting tool의 필요성이 제기되어 만들어짐
이를 통해 query 수행 시작부터 결과를 보여주기까지의 과정을 알 수 있다.
즉, 옵티마이저용 디버그 트레이스 이벤트, 10053 이벤트 트레이스 파일은 실행계획 평가와 관련한 카디널리티 및 비용을 계산하기 위해 옵티마이저가 수행하는 많은 일들을 보여준다. 하지만 트레이스 파일의 정보는 완전하지 않다.
10053 Events는 CBO 문제를 추적하는 가장 강력한 Tool.
1. 하지만 더 쉽고 좋은 방법이 많기 때문에 자주 사용되지는 않는다.
2. Explain Plan 명령, GATHER_PLAN_STATISTICS Hint, DBMS_XPLAN Package등을 이용하면 CBO 문제의 핵심인 실행계획 이상 현상을 상당 부분 추적할 수 있다.
10053 Event의 위대한 점은 CBO가 수행하는 일련의 작업을 시간 순으로 추적할 수 있다는 것.
10053 Event의 출력 결과가 제공하는 정보는
1. CBO 가 참초하는 기본 환경 값들
2. Optimizer와 관련된 Parameter 및 Bug Fix Patch 적용 여부 등을 확인
3. Query Transformation 과정
  - 복잡한 SQL 문장이 어떻게 Transformation 되는지?
  - 왜 Transformation에 실패하는지에 대한 풍부한 정보를 제공.
4. Query Optimization 과정
5. System Statistics와 Object Statistics를 참조하는 일련의 과정과 Access Type의 결정, Join Type을 결정하는 일련의 과정 출력.

1.2 10053 Event Trace[편집]

1.2.1 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';

위의 두 문장은 정확히 같은 일을 하지만 마지막 옵션을 사용하게 되면 보통 트레이스 파일의 앞부분에 나타나는옵티마이저 파라미터 목록이 제외되어 약간 짧은 트레이스 파일이 만들어 진다.

1.2.2 트레이스를 중단시키는 방법[편집]

SQL> alter session set events '10053 trace name context off';

10g에서 수행하는 것이 아니라면, PL/SQL 블록에 포함된 쿼리문에 대해서는 10053 트레이스 기능이 작동하지 않는다.
10gR2 트레이스 파일은 이전 버전의 것과는 다른데, 섹션의 순서가 다르고 여러 단서나 설명이 추가 되었으며 시스템통계가 기록되고 조건절 및 개요와 함께 최종 실행계획이 포함된다.

1.3 10053 이벤트 분석[편집]

10053 이벤트 분석을 쉽게 하려면 순서를 알아야 한다

1.3.1 10053 이벤트 분석 순서[편집]

1.3.1.1 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

1.3.1.2 쿼리블럭 정보와 수행된 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)
*******************************************

1.3.1.3 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

 

이하 생략....

1.3.1.4 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 힌트를 사용한 경우 여기에 나타난다.
  ****************************

 

이하 생략...

1.3.1.5 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) 

이하 생략...

1.3.1.6 Bind Peeking 수행[편집]

바인드 변수를 사용한 쿼리는 먼저 파싱과 최적화가 이루어진 후에 바인드 변수에 바인딩이 이루어진다는 사실이다.
이 말은 곧 최적화가 이루어지는 시점에는 변수로 제공되는 컬럼은 바인딩값에 대한 통계정보를 사용할 수 없다는 것을 의미한다.
그렇다면 어쩔 수 없이 값들이 균일하게 분포되어 있다는 가정을 세운 후에 최적화를 수행할 수 밖에 없다.
그러므로 만약 분포도가 균일하지 못한 컬럼에 바인드 변수를 사용하게되면 최악의 실행계획이 생성될 수도 있다.
우리는 파싱의 부하를 줄이기 위해 바인드 변수를 사용하는 것이 좋다는 것을 알고 있다.
그러나 실전에서는 데이터의 분포가 균일하지 않은 경우가 많기 때문에 억지로 동적 SQL을 사용해야 할 때가 종종 있었다.
이러한 한계를 극복하기 위해 새롭게 제공되기 시작한 것이 'PEEKING'이라는 기능이다.
이 단어를 사전에서 찾아보면 '몰래 엿보다'라는 뜻을 가지고 있다.
이 기능은 바인드 변수를 사용한 쿼리가 처음 실행될 때 옵티마이저는 사용자가 지정한 바인드 변수의 값을 '살짝 커닝' 함으로써 조건절의 컬럼값이 상수값으로 제공될 대와 마찬가지로 선택도를 확인하여 최적화를 수행하도록 한다.
전체를 평균적인 분포도로 보는 것이나 최초의 단 한가지 경우만으로 전체를 대신할 수 있다는 것이나 논리적인 확률은 그다지 다르다고 할 수는 없다.
그러나 이들은 분명히 다른 결과를 가져올 수 있다. 가령, '성별' 컬럼에 'M'이 99%, 'F'가 1% 들어있고, 이 조건만 가진 쿼리가 있다고 가정해 보자.
평균적인 접근 방법으로는 이 컬럼은 좀처럼 인덱스를 사용하지 않을 것이다.
그러나 만약 피킹이 적용되었다면 'M'이 첫 번째일 때는 전체테이블스캔, 'F'일 때는 인덱스 스캔이 선택되었을 것이다.
이처럼 두 가지 방법은 다른 실행계획을 가져올 수 있다.

*******************************************
Peeked values of the binds in SQL statement
*******************************************

------ Bind Info ------------------------     // 책에 나온 예제대로라면 바인드 정보가 출력되겠지만, 본인은 바인드 변수를 넣지 않았으므로 패스.

1.3.1.7 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"

1.3.1.8 시스템 통계정보와 테이블과 인덱스의 통계정보 출력[편집]

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

1.3.1.9 테이블 단위로 최적의 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

***************************************

1.3.1.10 최적의 조인방법과 조인순서를 정한다[편집]

이제 가장 중요한 단계로 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

 

...이하 생략

1.3.1.11 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
  --

1.3.1.12 마지막으로 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

10053 event

DB CAFE

목차

1 10053 이벤트[편집]

1.1 10053 이벤트 개요[편집]

1.2 10053 Event Trace[편집]

1.2.1 10053 Event를 발생시키는 방법[편집]

1.2.2 트레이스를 중단시키는 방법[편집]

1.3 10053 이벤트 분석[편집]

1.3.1 10053 이벤트 분석 순서[편집]

1.3.1.1 DBMS 정보, OS 정보, 하드웨어 정보 그리고 SQL을 실행한 Client의 정보 출력[편집]

1.3.1.2 쿼리블럭 정보와 수행된 SQL을 Parser로부터 받아서 출력[편집]

1.3.1.3 10053 Event에서 사용될 용어를 출력[편집]

1.3.1.4 Optimizer에 관련된 성능 파라미터를 출력하고 연이어 Bug Fix Cotrol 정보를 출력[편집]

1.3.1.5 Heuristic Query Transformation 과정이 출력된다.[편집]

1.3.1.6 Bind Peeking 수행[편집]

1.3.1.7 Cost Based Query Transformation 과정 출력[편집]

1.3.1.8 시스템 통계정보와 테이블과 인덱스의 통계정보 출력[편집]

1.3.1.9 테이블 단위로 최적의 Access path와 Cost를 출력[편집]

1.3.1.10 최적의 조인방법과 조인순서를 정한다[편집]

1.3.1.11 SQL dump와 Explain Plan Dump를 수행하여 SQL이 수행되었던 당시의 SQL과 실행계획 출력[편집]

1.3.1.12 마지막으로 Optimizer state dump를 수행하여 SQL이 수행되었던 당시의 옵티마이져 관련 파라미터 정보, Bug Fix Control 등을 출력[편집]