Optimizing Oracle Optimizer (2009년)
특징과 용도
- Query Transformation 단계에 대한 정보를 기록한다
- Query Transformation이 실패한 원인을 정확하게 기록해 준다
Evnet 10053에서 확인 할 수 있는 옵티마이져 동작
- (Hard Parse(혹은 Optimization)가 발생할 때만 수행된다.)
1단계 : Query Block을 출력
2단계 : Parameter 정보를 출력
- Bug Fix Control정보를 출력한다.
- (Bug Fix Control에 따라 옵티마이져의 안정성또는 비정상적인 동작을 하는 경우 제어하기 위함)
- 사 용 법 : Alter Session Set "_FIX_CONTROL" = '3746511:on','4519016:off'
- 적용여부 : V$SYSTEM_FIX_CONTROL, V$SESSION_FIX_CONTROL
- OPT_PARAM Hint에 의해 변경된 Parameter 정보를 출력한다.
- opt_param('_optimizer_push_pred_cost_based','false')
3단계 : QT(Query Transformation), CBQT(Cost Based Query Transformation) 수행
- QT(Query Transformation) : Cost 계산이 불필요한 간단한 Transformation이 수행된다.
- CBQT(Cost Based Query Transformation) : Cost를 감안해서 Transformation이 수행된다.
- Optimization단계에서 실제 Transformation이 이루어진다는 의미 (Transformation단계와 Optimization단계의 융합)
- CBQT의 수행제어방법
- alter session set "_optimizer_cost_based_transformation" = off;
- alter session set "_optimizer_push_pred_cost_based" = false;
- select /*+ opt_param('_optimizer_push_pred_cost_based','false') */ ...
4단계 : Statistics 정보를 추출하고, Optimization을위한 기본적인 정보를 계산
- Optimization의 기본단위는 Query Block
- CBO는 Query Level에서 Optimization을 수행하고 그 결과를 상위 Query Block에서 사용하는 방식을 사용
5단계 : Statistics정보 추출을 기준으로 Single Table Access 방식을 판단
- Dynamic_Sampling Hint가 사용된 경우 Dynamic Sampling을 수행된다.
6단계 : join순서와 join 종류를 결정
Dump file d:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_4620.trc
Wed Feb 18 17:40:26 2009
ORACLE V10.2.0.3.0 - Production vsnsta=0
vsnsql=14 vsnxtr=3
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
Windows NT Version V6.0 Service Pack 1
CPU : 2 - type 586, 2 Physical Cores
Process Affinity : 0x00000000
Memory (Avail/Total): Ph:892M/3045M, Ph+PgF:3339M/6292M, VA:1269M/2047M
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 16
Windows thread id: 4620, image: ORACLE.EXE (SHAD)
*** 2009-02-18 17:40:26.985
*** ACTION NAME:() 2009-02-18 17:40:26.982
*** MODULE NAME:(SQL*Plus) 2009-02-18 17:40:26.982
*** SERVICE NAME:(SYS$USERS) 2009-02-18 17:40:26.982
*** SESSION ID:(133.132) 2009-02-18 17:40:26.982
-- SQL에서 제공된 쿼리블럭을 정의
Registered qb: MAIN_QUERY 0x8306808 (HINT MAIN_QUERY)
signature (): qb_name=MAIN_QUERY nbfros=1 flg=0
fro(0): flg=4 objn=55296 hint_alias="A"@"MAIN_QUERY"
Registered qb: SUB_QUERY 0x8305dec (HINT SUB_QUERY)
signature (): qb_name=SUB_QUERY nbfros=1 flg=0
fro(0): flg=4 objn=55330 hint_alias="B"@"SUB_QUERY"
**************************
Predicate Move-Around (PM)
**************************
PM: Considering predicate move-around in MAIN_QUERY (#0).
PM: Checking validity of predicate move-around in MAIN_QUERY (#0).
CBQT: Validity checks passed for cjtzqad54bcmy.
apadrv-start: call(in-use=656, alloc=0), compile(in-use=39648, alloc=0)
******************************************
Current SQL statement for this session:
select /*+ gather_plan_statistics qb_name(main_query)*/
a.col1, a.col2
from t_plan a
where (a.col1, a.col2) in (select /*+ parallel(b,8) qb_name(sub_query) */
b.col1, b.col2
from t_plan2 b
where col1 = :ag_bind
and col2 <= 100)
*******************************************
Legend
The following abbreviations are used by optimizer trace.
CBQT - cost-based query transformation
JPPD - join predicate push-down
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
ST - star transformation
qb - query block
LB - leaf blocks
DK - distinct keys
LB/K - average number of leaf blocks per key
DB/K - average number of data blocks per key
CLUF - clustering factor
NDV - number of distinct values
Resp - response cost
Card - cardinality
Resc - resource cost
NL - nested loops (join)
SM - sort merge (join)
HA - hash (join)
CPUCSPEED - CPU Speed
IOTFRSPEED - I/O transfer speed
IOSEEKTIM - I/O seek time
SREADTIM - average single block read time
MREADTIM - average multiblock read time
MBRC - average multiblock read count
MAXTHR - maximum I/O system throughput
SLAVETHR - average slave I/O throughput
dmeth - distribution method
1: no partitioning required
2: value partitioned
4: right is random (round-robin)
512: left is random (round-robin)
8: broadcast right and partition left
16: broadcast left and partition right
32: partition left using partitioning of right
64: partition right using partitioning of left
128: use hash partitioning dimension
256: use range partitioning dimension
2048: use list partitioning dimension
1024: run the join in serial
0: invalid distribution method
sel - selectivity
ptn - partition
-- 바인드변수의 값을 확인한다.
*******************************************
Peeked values of the binds in SQL statement
*******************************************
kkscoacd
Bind#0
oacdty=01 mxl=32(05) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000000 frm=01 csi=846 siz=32 off=0
kxsbbbfp=08417b34 bln=32 avl=05 flg=05
value="Many2"
-- 2단계 파라미터정보 출력
***************************************
PARAMETERS USED BY THE OPTIMIZER
********************************
-- 2단계 변경된 파라미터 정보출력
*************************************
PARAMETERS WITH ALTERED VALUES
******************************
optimizer_mode = choose
-- 2단계 Bug Fix Control정보출력
*********************************
Bug Fix Control Environment
***************************
fix 4611850 = enabled
fix 4663804 = enabled
fix 4663698 = enabled
fix 4545833 = enabled
fix 3499674 = disabled
...
fix 4134994 = enabled
fix 5104624 = enabled
fix 4908162 = enabled
fix 5015557 = enabled
fix 5240607 = enabled
2단계 Default 파라미터 정보 출력
*************************************
PARAMETERS WITH DEFAULT VALUES
******************************
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 = 10.2.0.3
_optimizer_search_limit = 5
cpu_count = 2
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
...
_partition_view_enabled = true
_always_star_transformation = false
_query_rewrite_or_error = false
_hash_join_enabled = true
cursor_sharing = exact
_b_tree_bitmap_plans = true
star_transformation_enabled = false
_optimizer_cost_model = choose
_new_sort_cost_estimate = true
_complex_view_merging = true
_unnest_subquery = true
_eliminate_common_subexpr = true
_pred_move_around = true
_convert_set_to_join = false
_push_join_predicate = true
_push_join_union_view = true
_fast_full_scan_enabled = true
_optim_enhance_nnull_detection = true
_parallel_broadcast_enabled = true
_px_broadcast_fudge_factor = 100
_ordered_nested_loop = true
_no_or_expansion = false
optimizer_index_cost_adj = 100
optimizer_index_caching = 0
_system_index_caching = 0
_disable_datalayer_sampling = false
query_rewrite_enabled = true
query_rewrite_integrity = enforced
_query_cost_rewrite = true
...
optimizer_dynamic_sampling = 2
_pre_rewrite_push_pred = true
_optimizer_new_join_card_computation = true
_union_rewrite_for_gs = yes_gset_mvs
_generalized_pruning_enabled = true
_optim_adjust_for_part_skews = true
_force_datefold_trunc = false
statistics_level = typical
_optimizer_system_stats_usage = true
skip_unusable_indexes = true
_remove_aggr_subquery = true
_optimizer_push_down_distinct = 0
_dml_monitoring_enabled = true
_optimizer_undo_changes = false
_predicate_elimination_enabled = true
_nested_loop_fudge = 100
_project_view_columns = true
_local_communication_costing_enabled = true
_local_communication_ratio = 50
_query_rewrite_vop_cleanup = true
...
_optimizer_correct_sq_selectivity = true
_disable_function_based_index = false
_optimizer_join_order_control = 3
_optimizer_cartesian_enabled = true
_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
_bloom_filter_enabled = true
_update_bji_ipdml_enabled = 0
...
_optimizer_outer_to_anti_enabled = true
_selfjoin_mv_duplicates = true
_dimension_skip_null = true
_force_rewrite_enable = false
_optimizer_star_tran_in_with_clause = true
_optimizer_complex_pred_selectivity = true
_optimizer_connect_by_cost_based = true
_gby_hash_aggregation_enabled = true
_globalindex_pnum_filter_enabled = true
_fix_control_key = 0
_optimizer_skip_scan_guess = false
_enable_row_shipping = false
_row_shipping_threshold = 80
_row_shipping_explain = false
_optimizer_rownum_bind_default = 10
_first_k_rows_dynamic_proration = true
_optimizer_native_full_outer_join = off
*********************************
Bug Fix Control Environment
***************************
fix 4611850 = enabled
fix 4663804 = enabled
fix 4663698 = enabled
...
fix 4969880 = disabled
fix 4711525 = enabled
fix 4717546 = enabled
fix 4904838 = enabled
fix 5005866 = enabled
fix 4600710 = enabled
fix 5129233 = enabled
fix 5195882 = enabled
fix 5084239 = enabled
fix 4595987 = enabled
fix 4134994 = enabled
fix 5104624 = enabled
fix 4908162 = enabled
fix 5015557 = enabled
fix 5240607 = enabled
***************************************
PARAMETERS IN OPT_PARAM HINT
****************************
***************************************
Column Usage Monitoring is ON: tracking level = 1
***************************************
-- 3단계 QT단계실행
********************************
COST-BASED QUERY TRANSFORMATIONS
********************************
FPD: Considering simple filter push (pre rewrite) in MAIN_QUERY (#0)
FPD: Current where clause predicates in MAIN_QUERY (#0) :
("A"."COL1","A"."COL2")=ANY (SELECT /*+ QB_NAME ("SUB_QUERY") PARALLEL ("B",8) */ "B"."COL1","B"."COL2" FROM "T_PLAN2" "B")
Registered qb: MAIN_QUERY 0x830410c (COPY MAIN_QUERY)
signature(): NULL
Registered qb: SUB_QUERY 0x8301500 (COPY SUB_QUERY)
signature(): NULL
-- 3단계 CBQT단계실행
*****************************
Cost-Based Subquery Unnesting
*****************************
SU: No subqueries to consider in query block SUB_QUERY (#2).
SU: Considering subquery unnesting in query block MAIN_QUERY (#1)
SU: Performing unnesting that does not require costing.
SU: Considering subquery unnest on MAIN_QUERY (#1).
SU: Checking validity of unnesting subquery SUB_QUERY (#2)
SU: Passed validity checks.
SU: Transforming ANY subquery to a join.
Registered qb: SEL$DBE3B336 0x830410c (SUBQUERY UNNEST MAIN_QUERY; SUB_QUERY)
signature (): qb_name=SEL$DBE3B336 nbfros=2 flg=0
fro(0): flg=0 objn=55296 hint_alias="A"@"MAIN_QUERY"
fro(1): flg=0 objn=55330 hint_alias="B"@"SUB_QUERY"
*******************************
Cost-Based Complex View Merging
*******************************
CVM: Finding query blocks in SEL$DBE3B336 (#1) that are valid to merge.
SU: Transforming ANY subquery to a join.
*************************
Set-Join Conversion (SJC)
*************************
SJC: Considering set-join conversion in SEL$DBE3B336 (#1).
Query block (08306808) before join elimination:
SQL:
Query block (08306808) unchanged
**************************
Predicate Move-Around (PM)
**************************
PM: Considering predicate move-around in SEL$DBE3B336 (#1).
PM: Checking validity of predicate move-around in SEL$DBE3B336 (#1).
PM: PM bypassed: Outer query contains no views.
JPPD: Applying transformation directives
JPPD: Checking validity of push-down in query block SEL$DBE3B336 (#1)
JPPD: No view found to push predicate into.
FPD: Considering simple filter push in SEL$DBE3B336 (#1)
FPD: Current where clause predicates in SEL$DBE3B336 (#1) :
"A"."COL1"="B"."COL1" AND "A"."COL2"="B"."COL2" AND "B"."COL1"=:B1 AND "B"."COL2"<=100
kkogcp: try to generate transitive predicate from check constraints for SEL$DBE3B336 (#1)
predicates with check contraints: "A"."COL1"="B"."COL1" AND "A"."COL2"="B"."COL2" AND "B"."COL1"=:B1 AND "B"."COL2"<=100 AND "A"."COL1"=:B2 AND "A"."COL2"<=100
after transitive predicate generation: "A"."COL1"="B"."COL1" AND "A"."COL2"="B"."COL2" AND "B"."COL1"=:B1 AND "B"."COL2"<=100 AND "A"."COL1"=:B2 AND "A"."COL2"<=100
finally: "A"."COL1"="B"."COL1" AND "A"."COL2"="B"."COL2" AND "B"."COL1"=:B1 AND "B"."COL2"<=100 AND "A"."COL1"=:B2 AND "A"."COL2"<=100
FPD: Following transitive predicates are generated in SEL$DBE3B336 (#1) :
"A"."COL1"=:B1 AND "A"."COL2"<=100
kkoqbc-start
: call(in-use=1124, alloc=0), compile(in-use=49448, alloc=0)
****************
QUERY BLOCK TEXT
****************
select /*+ gather_plan_statistics qb_name(main_query)*/
a.col1, a.col2
from t_plan a
where (a.col1, a.col2) in (select /*+ parallel(b,8) qb_name(sub_query) */
b.col1, b.col2
from t_plan2 b
where col1 = :ag_bind
and col2 <= 100)
-- 4단계 쿼리블록 정보계산
*********************
QUERY BLOCK SIGNATURE
*********************
qb name was generated
signature (optimizer): qb_name=SEL$DBE3B336 nbfros=2 flg=0
fro(0): flg=0 objn=55296 hint_alias="A"@"MAIN_QUERY"
fro(1): flg=0 objn=55330 hint_alias="B"@"SUB_QUERY"
-- 4단계 시스템통계정보조사
*****************************
SYSTEM STATISTICS INFORMATION
*****************************
Using NOWORKLOAD Stats
CPUSPEED: 1280 millions instruction/sec
IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
IOSEEKTIM: 17 milliseconds (default is 10)
-- 4단계 오브젝트(딕셔너리)통계정보조사
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: T_PLAN Alias: A
#Rows: 10000 #Blks: 43 AvgRowLen: 9.00
Column (#1): COL1(VARCHAR2)
AvgLen: 6.00 NDV: 1 Nulls: 0 Density: 5.0000e-005
Histogram: Freq #Bkts: 1 UncompBkts: 10000 EndPtVals: 1
Column (#2): COL2(NUMBER)
AvgLen: 4.00 NDV: 10000 Nulls: 0 Density: 1.0000e-004 Min: 1 Max: 10000
Index Stats::
Index: T_PLAN_IDX Col#: 1
LVLS: 1 #LB: 31 #DK: 1 LB/K: 31.00 DB/K: 21.00 CLUF: 21.00
***********************
Table Stats::
Table: T_PLAN2 Alias: B Partition [0]
#Rows: 2999 #Blks: 13 AvgRowLen: 9.00
#Rows: 2999 #Blks: 13 AvgRowLen: 9.00
-- 5단계 single table access방식을 판단
***************************************
SINGLE TABLE ACCESS PATH
Column (#1): COL1(VARCHAR2) Part#: 0
AvgLen: 6.00 NDV: 1 Nulls: 0 Density: 1.6672e-004
Histogram: Freq #Bkts: 1 UncompBkts: 2999 EndPtVals: 1
Column (#1): COL1(VARCHAR2)
AvgLen: 6.00 NDV: 1 Nulls: 0 Density: 1.6672e-004
Histogram: Freq #Bkts: 1 UncompBkts: 2999 EndPtVals: 1
Column (#2): COL2(NUMBER) Part#: 0
AvgLen: 4.00 NDV: 2999 Nulls: 0 Density: 3.3344e-004 Min: 1 Max: 2999
Column (#2): COL2(NUMBER)
AvgLen: 4.00 NDV: 2999 Nulls: 0 Density: 3.3344e-004 Min: 1 Max: 2999
Table: T_PLAN2 Alias: B
Card: Original: 2999 Rounded: 1 Computed: 0.02 Non Adjusted: 0.02
Access Path: TableScan
Cost: 3.03 Resp: 2.00 Degree: 0
Cost_io: 3.00 Cost_cpu: 659805
Resp_io: 2.00 Resp_cpu: 91640
Best:: AccessPath: TableScan
Cost: 2.00 Degree: 8 Resp: 2.00 Card: 0.02 Bytes: 0
***************************************
SINGLE TABLE ACCESS PATH
Table: T_PLAN Alias: A
Card: Original: 10000 Rounded: 1 Computed: 0.01 Non Adjusted: 0.01
Access Path: TableScan
Cost: 6.10 Resp: 6.10 Degree: 0
Cost_io: 6.00 Cost_cpu: 2506247
Resp_io: 6.00 Resp_cpu: 2506247
Access Path: index (AllEqRange)
Index: T_PLAN_IDX
resc_io: 2.00 resc_cpu: 15488
ix_sel: 5.0000e-005 ix_sel_with_filters: 5.0000e-005
Cost: 2.00 Resp: 2.00 Degree: 1
Best:: AccessPath: IndexRange Index: T_PLAN_IDX
Cost: 2.00 Degree: 1 Resp: 2.00 Card: 0.01 Bytes: 0
Multi-column join key card: 10000 #cols: 2 table: T_PLAN
Multi-column join key card: 9999 #cols: 2 table: T_PLAN2
-- 6단계 조인순서와 join종류를 결정
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
Permutations for Starting Table :0
***********************
Join order[1]: T_PLAN[A]#0 T_PLAN2[B]#1
***************
Now joining: T_PLAN2[B]#1
***************
NL Join
Outer table: Card: 0.01 Cost: 2.00 Resp: 2.00 Degree: 1 Bytes: 9
Inner table: T_PLAN2 Alias: B
Access Path: TableScan
NL Join: Cost: 5.03 Resp: 4.00 Degree: 100
Cost_io: 5.00 Cost_cpu: 675293
Resp_io: 4.00 Resp_cpu: 107127
Best NL cost: 4.00
resc: 5.03 resc_io: 5.00 resc_cpu: 675293
resp: 4.00 resp_io: 4.00 resp_cpu: 107127
Using multi-column join key sanity check for table T_PLAN
Revised join sel:1.0000e-004 = 1 * (1/10000.00) * (1/1)
Semi Join Card: 0.00 = outer (0.01) * sel (1.0000e-004)
Join Card - Rounded: 1 Computed: 0.00
SM Join
Outer table:
resc: 2.00 card 0.01 bytes: 9 deg: 1 resp: 2.00
Inner table: T_PLAN2 Alias: B
resc: 3.03 card: 0.02 bytes: 9 deg: 8 resp: 2.00
using dmeth: 2 #groups: 1
SORT resource Sort statistics
Sort width: 231 Area size: 202752 Max Area size: 40684544
Degree: 1
Blocks to Sort: 1 Row size: 20 Total Rows: 1
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 24708786
Total Temp space used: 0
SORT resource Sort statistics
Sort width: 231 Area size: 202752 Max Area size: 40684544
Degree: 1
Blocks to Sort: 1 Row size: 20 Total Rows: 1
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 24708786
Total Temp space used: 0
SORT response Sort statistics
Sort width: 71 Area size: 2542797 Max Area size: 12713984
Degree: 8
Blocks to Sort: 1 Row size: 20 Total Rows: 1
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 27454207
SM join: Resc: 7.03 Resp: 6.12 [multiMatchCost=0.00]
SM cost: 6.12
resc: 7.03 resc_io: 5.00 resc_cpu: 50092866
resp: 6.12 resp_io: 4.00 resp_cpu: 52270121
HA Join
Outer table:
resc: 2.00 card 0.01 bytes: 9 deg: 1 resp: 2.00
Inner table: T_PLAN2 Alias: B
resc: 3.03 card: 0.02 bytes: 9 deg: 8 resp: 2.00
using dmeth: 2 #groups: 1
Cost per ptn: 0.50 #ptns: 8
hash_area: 0 (max=0) buildfrag: 1 probefrag: 1 passes: 1
Hash join: Resc: 9.03 Resp: 4.50 [multiMatchCost=0.00]
HA cost: 4.50
resc: 9.03 resc_io: 5.00 resc_cpu: 99512438
resp: 4.50 resp_io: 4.00 resp_cpu: 12461771
Best:: JoinMethod: HashSemi
Cost: 4.50 Degree: 8 Resp: 4.50 Card: 0.00 Bytes: 18
***********************
Best so far: Table#: 0 cost: 2.0006 card: 0.0050 bytes: 9
Table#: 1 cost: 4.5043 card: 0.0000 bytes: 18
***********************
Join order[2]: T_PLAN2[B]#1 T_PLAN[A]#0
SORT resource Sort statistics
Sort width: 231 Area size: 202752 Max Area size: 40684544
Degree: 1
Blocks to Sort: 1 Row size: 20 Total Rows: 1
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 24708786
Total Temp space used: 0
SORT response Sort statistics
Sort width: 71 Area size: 2542797 Max Area size: 12713984
Degree: 8
Blocks to Sort: 1 Row size: 20 Total Rows: 1
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 27454207
***************
Now joining: T_PLAN[A]#0
***************
NL Join
Outer table: Card: 0.02 Cost: 3.11 Resp: 3.11 Degree: 8 Bytes: 9
Inner table: T_PLAN Alias: A
Access Path: TableScan
NL Join: Cost: 10.13 Resp: 3.96 Degree: 100
Cost_io: 9.00 Cost_cpu: 27874838
Resp_io: 2.83 Resp_cpu: 27893937
kkofmx: index filter:"A"."COL1"="B"."COL1" AND "A"."COL2"="B"."COL2" AND "B"."COL1"=:B1 AND "A"."COL1"=:B2 AND "A"."COL2"<=100 AND "B"."COL2"<=100
Access Path: index (AllEqJoinGuess)
Index: T_PLAN_IDX
resc_io: 32.00 resc_cpu: 229131
ix_sel: 5.0000e-005 ix_sel_with_filters: 5.0000e-005
NL Join: Cost: 36.04 Resp: 7.56 Degree: 8
Cost_io: 35.00 Cost_cpu: 25597722
Resp_io: 6.44 Resp_cpu: 27577670
Best NL cost: 3.96
resc: 10.13 resc_io: 9.00 resc_cpu: 27874838
resp: 3.96 resp_io: 2.83 resp_cpu: 27893937
Using multi-column join key sanity check for table T_PLAN
Revised join sel:1.0000e-004 = 1 * (1/10000.00) * (1/1)
Join Card: 0.00 = outer (0.02) * inner (0.01) * sel (1.0000e-004)
Join Card - Rounded: 1 Computed: 0.00
SM Join
Outer table:
resc: 4.03 card 0.02 bytes: 9 deg: 8 resp: 3.11
Inner table: T_PLAN Alias: A
resc: 2.00 card: 0.01 bytes: 9 deg: 1 resp: 2.00
using dmeth: 2 #groups: 1
SORT resource Sort statistics
Sort width: 231 Area size: 202752 Max Area size: 40684544
Degree: 1
Blocks to Sort: 1 Row size: 20 Total Rows: 1
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 24708786
Total Temp space used: 0
SORT response Sort statistics
Sort width: 71 Area size: 2542797 Max Area size: 12713984
Degree: 8
Blocks to Sort: 1 Row size: 20 Total Rows: 1
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 27454207
SORT resource Sort statistics
Sort width: 231 Area size: 202752 Max Area size: 40684544
Degree: 1
Blocks to Sort: 1 Row size: 20 Total Rows: 1
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 24708786
Total Temp space used: 0
SM join: Resc: 8.03 Resp: 7.23 [multiMatchCost=0.00]
SM cost: 7.23
resc: 8.03 resc_io: 5.00 resc_cpu: 74801652
resp: 7.23 resp_io: 4.00 resp_cpu: 79724328
HA Join
Outer table:
resc: 4.03 card 0.02 bytes: 9 deg: 8 resp: 3.11
Inner table: T_PLAN Alias: A
resc: 2.00 card: 0.01 bytes: 9 deg: 1 resp: 2.00
using dmeth: 2 #groups: 1
Cost per ptn: 0.50 #ptns: 8
hash_area: 0 (max=0) buildfrag: 1 probefrag: 1 passes: 1
Hash join: Resc: 10.03 Resp: 5.62 [multiMatchCost=0.00]
HA cost: 5.62
resc: 10.03 resc_io: 5.00 resc_cpu: 124221225
resp: 5.62 resp_io: 4.00 resp_cpu: 39915978
Plan cardinality mismatch: best card= 0.00000050005 curr card= 0.00000000834
Join order aborted: cost > best plan cost
***********************
(newjo-stop-1) k:0, spcnt:0, perm:2, maxperm:2000
*********************************
Number of join permutations tried: 2
*********************************
-- Enumerating distribution methods for #Hash Join:
---- cost VALUE = 0.00
Outer table:
resc: 2.00 card 0.01 bytes: 9 deg: 1 resp: 2.00
Inner table: T_PLAN2 Alias: B
resc: 3.03 card: 0.02 bytes: 9 deg: 8 resp: 2.00
using dmeth: 2 #groups: 1
Cost per ptn: 0.50 #ptns: 8
hash_area: 0 (max=0) buildfrag: 1 probefrag: 1 passes: 1
Hash join: Resc: 9.03 Resp: 4.50 [multiMatchCost=0.00]
---- cost(Hash Join) = 4.50 (w/o dist), 4.50 (w/ dist)
kkoBloomFilter: join between A.T_PLAN (left) and B.T_PLAN2 (right) -> ndv:0 (1) reduction factor:1.000000 (limit:0.500000) one of the DFO is serial
(newjo-save) [0 1 ]
Final - All Rows Plan: Best join order: 1
Cost: 4.5043 Degree: 8 Card: 1.0000 Bytes: 18
Resc: 9.0274 Resc_io: 5.0000 Resc_cpu: 99512438
Resp: 4.5043 Resp_io: 4.0000 Resc_cpu: 12461771
kkoipt: Query block SEL$DBE3B336 (#1)
kkoqbc-end
: call(in-use=44492, alloc=0), compile(in-use=54336, alloc=0)
apadrv-end: call(in-use=44492, alloc=0), compile(in-use=54924, alloc=0)
sql_id=cjtzqad54bcmy.
Current SQL statement for this session:
select /*+ gather_plan_statistics qb_name(main_query)*/
a.col1, a.col2
from t_plan a
where (a.col1, a.col2) in (select /*+ parallel(b,8) qb_name(sub_query) */
b.col1, b.col2
from t_plan2 b
where col1 = :ag_bind
and col2 <= 100)
============
Plan Table
============
-------------------------------------------------------+-----------------------------------+-------------------------+---------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time | TQ |IN-OUT|PQ Distrib | Pstart| Pstop |
-------------------------------------------------------+-----------------------------------+-------------------------+---------------+
| 0 | SELECT STATEMENT | | | | 5 | | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | 1 | 18 | 5 | 00:00:01 |:Q1002| P->S |QC (RANDOM)| | |
| 3 | HASH JOIN SEMI BUFFERED | | 1 | 18 | 5 | 00:00:01 |:Q1002| PCWP | | | |
| 4 | BUFFER SORT | | | | | |:Q1002| PCWC | | | |
| 5 | PX RECEIVE | | 1 | 9 | 2 | 00:00:01 |:Q1002| PCWP | | | |
| 6 | PX SEND HASH | :TQ10000 | 1 | 9 | 2 | 00:00:01 | | S->P |HASH | | |
| 7 | TABLE ACCESS BY INDEX ROWID | T_PLAN | 1 | 9 | 2 | 00:00:01 | | | | | |
| 8 | INDEX RANGE SCAN | T_PLAN_IDX| 1 | | 1 | 00:00:01 | | | | | |
| 9 | PX RECEIVE | | 1 | 9 | 2 | 00:00:01 |:Q1002| PCWP | | | |
| 10 | PX SEND HASH | :TQ10001 | 1 | 9 | 2
"데이터베이스 스터디모임" 에서 2009년에 "OPTIMIZING ORACLE OPTIMIZER
" 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/3855
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.