14. 10053 트레이스 파일
2009.12.28 01:21
CHAPTER 14. 10053 트레이스 파일
10053 트레이스 활성화
alter session set events '10053 trace name context forever';
alter session set events '10053 trace name context forever,level 1';
alter session set events '10053 trace name context forever,level 2'; <- 옵티마이져 파라미터 목록 제외
트레이스 중단
alter session set events '10053 trace name context off';
쿼리문
? 예제 - (big_10053.sql)
- 굵은 글자는 primary key, 모든 테이블은 기본키에 대한 인덱스가 생성됨
- greatgrandparent : 1,000개 튜플
|
Name |
Type |
값 |
비고 |
|
ID |
NUMBER |
1..1000 |
rownum |
|
SMALL_NUM_GGP |
NUMBER |
0,0,0,0,0,1,1,1, .. 각 5개씩 |
trunc((rownum-1)/5) |
|
SMALL_VC_GGP |
VARCHAR2(20) |
1..1000 |
rownum |
|
PADDING_GGP |
VARCHAR2(3200) |
1..1000 |
rownum |
- grandparent : 1,000개 튜플
(ID_GGP는 grandgrandparent(ID)를 참조하는 외래키)
|
Name |
Type |
값 |
비고 |
|
ID_GGP |
NUMBER |
2..1000, 0, 1 |
1+mod(rownum,1000) |
|
ID |
NUMBER |
1..1000 |
rownum |
|
SMALL_NUM_GP |
NUMBER |
0,0,0,0,0,1,1,1, .. 각 5개씩 |
trunc((rownum-1)/5) |
|
SMALL_VC_GP |
VARCHAR2(20) |
1..10000 |
rownum |
|
PADDING_GP |
VARCHAR2(3200) |
- parent : 10,000개 튜플
(ID_GGP,ID_GP는 grandparent(ID_GGP,ID)를 참조하는 외래키)
|
Name |
Type |
값 |
비고 |
|
ID_GGP |
NUMBER |
(2,..1000,1) 10번 반복 |
|
|
ID_GP |
NUMBER |
(1,..2000) 5번 반복 |
trunc((rownum-1)/5) |
|
ID |
NUMBER |
0..10,000 |
rownum |
|
SMALL_NUM_P |
NUMBER |
0 5번,1 5번,..., 1999 5번 |
|
|
SMALL_VC_P |
VARCHAR2(20) |
0..10,000 |
rownum |
|
PADDING_P |
VARCHAR2(3200) |
0..10,000 |
rownum |
- child : 40,000개 튜플
(ID_GGP,ID_GP,ID_P는 parent(ID_GGP,ID_GP,ID)를 참조하는 외래키)
|
Name |
Type |
값 |
비고 |
|
ID_GGP |
NUMBER |
(2,..1000,1) 40번 반복 |
|
|
ID_GP |
NUMBER |
(1,..2000) 20번 반복 |
|
|
ID_P |
NUMBER |
0..10,000 4번 반복 |
|
|
ID |
NUMBER |
1..40000 |
rownum |
|
SMALL_NUM_C |
NUMBER |
0 4번,1 5번,..., 9999 4번 |
|
|
SMALL_VC_C |
VARCHAR2(20) |
0..10,000 |
rownum |
|
PADDING_C |
VARCHAR2(3200) |
0..10,000 |
rownum |
- 질의문
select
count(ggp.small_vc_ggp),
count(gp.small_vc_gp),
count(p.small_vc_p),
count(c.small_vc_c)
from
greatgrandparent ggp,
grandparent gp,
parent p,
child c
where ggp.small_num_ggp between 100 and 150
/* */
and gp.id_ggp = ggp.id
and gp.small_num_gp between 110 and 130
/* */
and p.id_gp = gp.id
and p.id_ggp = gp.id_ggp
and p.small_num_p between 110 and 130
/* */
and c.id_p = p.id
and c.id_gp = p.id_gp
and c.id_ggp = p.id_ggp
and c.small_num_c between 200 and 215
;
실행계획
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 96 | 361 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | 96 | | |
| 2 | NESTED LOOPS | | 1 | 96 | 361 (1)| 00:00:04 |
| 3 | NESTED LOOPS | | 1 | 77 | 360 (1)| 00:00:04 |
| 4 | NESTED LOOPS | | 6 | 300 | 348 (1)| 00:00:04 |
|* 5 | TABLE ACCESS FULL | GRANDPARENT | 110 | 2530 | 128 (1)| 00:00:02 |
|* 6 | TABLE ACCESS BY INDEX ROWID| PARENT | 1 | 27 | 2 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | P_PK | 1 | | 1 (0)| 00:00:01 |
|* 8 | TABLE ACCESS BY INDEX ROWID | CHILD | 1 | 27 | 2 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | C_PK | 1 | | 1 (0)| 00:00:01 |
|* 10 | TABLE ACCESS BY INDEX ROWID | GREATGRANDPARENT | 1 | 19 | 1 (0)| 00:00:01 |
|* 11 | INDEX UNIQUE SCAN | GGP_PK | 1 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter("GP"."SMALL_NUM_GP"<=130 AND "GP"."SMALL_NUM_GP">=110)
6 - filter("P"."SMALL_NUM_P"<=130 AND "P"."SMALL_NUM_P">=110)
7 - access("P"."ID_GGP"="GP"."ID_GGP" AND "P"."ID_GP"="GP"."ID")
8 - filter("C"."SMALL_NUM_C"<=215 AND "C"."SMALL_NUM_C">=200)
9 - access("C"."ID_GGP"="P"."ID_GGP" AND "C"."ID_GP"="P"."ID_GP" AND "C"."ID_P"="P"."ID")
10 - filter("GGP"."SMALL_NUM_GGP">=100 AND "GGP"."SMALL_NUM_GGP"<=150)
11 - access("GP"."ID_GGP"="GGP"."ID")
환경
begin
dbms_stats.set_system_stats('MBRC',8);
dbms_stats.set_system_stats('MREADTIM',20);
dbms_stats.set_system_stats('SREADTIM',10);
dbms_stats.set_system_stats('CPUSPEED',500);
end;
/
트레이스 파일 /이창헌
10053트레이스 파일 출력의 첫 번째 섹션
- 옵티마이저가 바인드 변수 peeking의 결과로 실제 사용한 값을 열거
- 바인드 변수의 명칭 대신 위치만 나옴
실제 테스트 결과
첫 번째 섹션에서 바인드 변수 peeking정보 없음 ??
제일 마지막 섹션 query구문에서 바인드 변수를 확인 가능
테스트 쿼리
select * from child
where id_ggp between :v_id_start and :v_id_end
and small_num_c like :v_snc || '%'
v_id_start 2 입력
v_id_end = 10 입력
v_snc = 0 입력
10053트레이스 파일 마지막 섹션
QUERY
select * from child
where id_ggp between 2 and 10
and small_num_c like 0 || '%'
QUERY
교제의 ch_14_10053_10.trc
(실제 테스트환경 트레이스 파일은 첨부)
*******************************************
Peeked values of the binds in SQL statement
*******************************************
파라미터 설정값 /이창헌
두 번째 섹션
-옵티마이저와 관련된 파라미터의 현재 설정 값을 열거
10g:
세션에서 변경된 것
*************************************
PARAMETERS WITH ALTERED VALUES
******************************
디폴트 값
*************************************
PARAMETERS WITH DEFAULT VALUES
******************************
***************************************
PARAMETERS USED BY THE OPTIMIZER
********************************
*************************************
PARAMETERS WITH ALTERED VALUES
******************************
*************************************
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.1.0.4
_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
_sort_elimination_cost_ratio = 0
_optimizer_block_size = 8192
_sort_multiblock_read_count = 2
_hash_multiblock_io_count = 0
db_file_multiblock_read_count = 8
_optimizer_max_permutations = 2000
pga_aggregate_target = 204800 KB
_pga_max_size = 204800 KB
_sort_space_for_write_buffers = 1
_query_rewrite_maxdisjunct = 257
_smm_auto_min_io_size = 56 KB
_smm_auto_max_io_size = 248 KB
_smm_min_size = 204 KB
_smm_max_size = 10240 KB
_smm_px_max_size = 61440 KB
_cpu_to_io = 0
_optimizer_undo_cost_change = 10.1.0.4
parallel_query_mode = enabled
parallel_dml_mode = disabled
parallel_ddl_mode = enabled
optimizer_mode = all_rows
sqlstat_enabled = false
_optimizer_percent_parallel = 101
_always_anti_join = choose
_always_semi_join = choose
_optimizer_mode_force = true
_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
_query_rewrite_2 = true
_query_rewrite_1 = true
_query_rewrite_expression = true
_query_rewrite_jgmigrate = true
_query_rewrite_fpc = true
_query_rewrite_drj = true
_full_pwise_join_enabled = true
_partial_pwise_join_enabled = true
_left_nested_loops_random = true
_improved_row_length_enabled = true
_index_join_enabled = true
_enable_type_dep_selectivity = true
_improved_outerjoin_card = true
_optimizer_adjust_for_nulls = true
_optimizer_degree = 0
_use_column_stats_for_function = true
_subquery_pruning_enabled = true
_subquery_pruning_mv_enabled = false
_or_expand_nvl_predicate = true
_like_with_bind_as_equality = false
_table_scan_cost_plus_one = true
_cost_equality_semi_join = true
_default_non_equality_sel_check = true
_new_initial_join_orders = true
_oneside_colstat_for_equijoins = true
_optim_peek_user_binds = true
_minimal_stats_aggregation = true
_force_temptables_for_gsets = false
workarea_size_policy = auto
_smm_auto_cost_enabled = true
_gs_anti_semi_join_allowed = true
_optim_new_default_join_sel = 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
_slave_mapping_enabled = true
_optimizer_cost_based_transformation = linear
_optimizer_mjc_enabled = true
_right_outer_hash_enable = true
_spr_push_pred_refspr = true
_optimizer_cache_stats = false
_optimizer_cbqt_factor = 50
_optimizer_squ_bottomup = true
_fic_area_size = 131072
_optimizer_skip_scan_enabled = true
_optimizer_cost_filter_pred = false
_optimizer_sortmerge_join_enabled = true
_optimizer_join_sel_sanity_check = true
_mmv_query_rewrite_enabled = false
_bt_mmv_query_rewrite_enabled = true
_add_stale_mv_to_dependency_list = true
_distinct_view_unnesting = false
_optimizer_dim_subq_join_sel = true
_optimizer_disable_strans_sanity_checks = 0
_optimizer_compute_index_stats = true
_push_join_union_view2 = true
_optimizer_ignore_hints = false
_optimizer_random_plan = 0
_query_rewrite_setopgrw_enable = true
_optimizer_correct_sq_selectivity = true
_disable_function_based_index = false
_optimizer_join_order_control = 3
_optimizer_push_pred_cost_based = true
flashback_table_rpi = non_fbt
_optimizer_cbqt_no_size_restriction = true
***************************************
Column Usage Monitoring is ON: tracking level = 1
***************************************
쿼리 블록 /박우창
QUERY BLOCK SIGNATURE
*********************
qb name was generated
signature (optimizer): qb_name=SEL$1 nbfros=4 flg=0
fro(0): flg=0 objn=52067 hint_alias="C"@"SEL$1"
fro(1): flg=0 objn=52064 hint_alias="GGP"@"SEL$1"
fro(2): flg=0 objn=52065 hint_alias="GP"@"SEL$1"
fro(3): flg=0 objn=52066 hint_alias="P"@"SEL$1"
***************************************
통계정보 /박우창
BASE STATISTICAL INFORMATION
***********************
Table stats Table: CHILD Alias: C
TOTAL :: CDN: 40000 NBLKS: 10000 AVG_ROW_LEN: 1632
COLUMN: ID_P(NUMBER) Col#: 3 Table: CHILD Alias: C
Size: 4 NDV: 10000 Nulls: 0 Density: 1.0000e-004 Min: 1 Max: 10000
COLUMN: ID_GP(NUMBER) Col#: 2 Table: CHILD Alias: C
Size: 4 NDV: 2000 Nulls: 0 Density: 5.0000e-004 Min: 1 Max: 2000
COLUMN: ID_GGP(NUMBER) Col#: 1 Table: CHILD Alias: C
Size: 4 NDV: 1000 Nulls: 0 Density: 1.0000e-003 Min: 1 Max: 1000
Index stats
Index: C_PK COL#: 1 2 3 4
TOTAL :: LVLS: 1 #LB: 108 #DK: 40000 LB/K: 1 DB/K: 1 CLUF: 40000
***********************
Table stats Table: PARENT Alias: P
TOTAL :: CDN: 10000 NBLKS: 2500 AVG_ROW_LEN: 1627
COLUMN: ID_GP(NUMBER) Col#: 2 Table: PARENT Alias: P
Size: 4 NDV: 2000 Nulls: 0 Density: 5.0000e-004 Min: 1 Max: 2000
COLUMN: ID_GGP(NUMBER) Col#: 1 Table: PARENT Alias: P
Size: 4 NDV: 1000 Nulls: 0 Density: 1.0000e-003 Min: 1 Max: 1000
COLUMN: ID(NUMBER) Col#: 3 Table: PARENT Alias: P
Size: 4 NDV: 10000 Nulls: 0 Density: 1.0000e-004 Min: 1 Max: 10000
COLUMN: ID_GP(NUMBER) Col#: 2 Table: PARENT Alias: P
Size: 4 NDV: 2000 Nulls: 0 Density: 5.0000e-004 Min: 1 Max: 2000
COLUMN: ID_GGP(NUMBER) Col#: 1 Table: PARENT Alias: P
Size: 4 NDV: 1000 Nulls: 0 Density: 1.0000e-003 Min: 1 Max: 1000
Index stats
Index: P_PK COL#: 1 2 3
TOTAL :: LVLS: 1 #LB: 24 #DK: 10000 LB/K: 1 DB/K: 1 CLUF: 10000
***********************
Table stats Table: GRANDPARENT Alias: GP
TOTAL :: CDN: 2000 NBLKS: 500 AVG_ROW_LEN: 1623
COLUMN: ID_GGP(NUMBER) Col#: 1 Table: GRANDPARENT Alias: GP
Size: 4 NDV: 1000 Nulls: 0 Density: 1.0000e-003 Min: 1 Max: 1000
COLUMN: ID(NUMBER) Col#: 2 Table: GRANDPARENT Alias: GP
Size: 4 NDV: 2000 Nulls: 0 Density: 5.0000e-004 Min: 1 Max: 2000
COLUMN: ID_GGP(NUMBER) Col#: 1 Table: GRANDPARENT Alias: GP
Size: 4 NDV: 1000 Nulls: 0 Density: 1.0000e-003 Min: 1 Max: 1000
Index stats
Index: GP_PK COL#: 1 2
TOTAL :: LVLS: 1 #LB: 6 #DK: 2000 LB/K: 1 DB/K: 1 CLUF: 2000
***********************
Table stats Table: GREATGRANDPARENT Alias: GGP
TOTAL :: CDN: 1000 NBLKS: 250 AVG_ROW_LEN: 1619
COLUMN: ID(NUMBER) Col#: 1 Table: GREATGRANDPARENT Alias: GGP
Size: 4 NDV: 1000 Nulls: 0 Density: 1.0000e-003 Min: 1 Max: 1000
Index stats
Index: GGP_PK COL#: 1
TOTAL :: LVLS: 1 #LB: 2 #DK: 1000 LB/K: 1 DB/K: 1 CLUF: 250
_OPTIMIZER_PERCENT_PARALLEL = 0
***************************************
단일 테이블 /남송휘
단일 테이블 액세스 경로와 관련
각 테이블에 대해 개별적으로 획득하게 될 로우 개수와 그것을 얻기 위해 가장 싼 비용을 계산
조건절 'small_num_ggp between 100 and 150'
Number of distinct values (NDV) = 200
Number of nulls (NULLS) = 0
Density (DENS) = 0.005 (1/200)
Low value (Min) = 0
High values (Max) = 199
'between' 범위 조건에 대한 선택도 공식
선택도= (val2-val1) / (high-low) +2 / num_distinct =
50/199+2/200= 0.261256
1000개의 로우를 곱한후 반올림 261
BEST_CST(best cost)는 64.41
SINGLE TABLE ACCESS PATH
COLUMN: SMALL_NUM_(NUMBER) Col#: 2 Table: GREATGRANDPARENT Alias: GGP
Size: 4 NDV: 200 Nulls: 0 Density: 5.0000e-003 Min: 0 Max: 199
TABLE: GREATGRANDPARENT Alias: GGP
Original Card: 1000 Rounded: 261 Computed: 261.26 Non Adjusted: 261.26
Access Path: table-scan Resc: 64 Resp: 64
BEST_CST: 64.41 PATH: 2 Degree: 1
***************************************
SINGLE TABLE ACCESS PATH
COLUMN: SMALL_NUM_(NUMBER) Col#: 3 Table: GRANDPARENT Alias: GP
Size: 4 NDV: 400 Nulls: 0 Density: 2.5000e-003 Min: 0 Max: 399
TABLE: GRANDPARENT Alias: GP
Original Card: 2000 Rounded: 110 Computed: 110.25 Non Adjusted: 110.25
Access Path: table-scan Resc: 128 Resp: 128
BEST_CST: 127.82 PATH: 2 Degree: 1
***************************************
SINGLE TABLE ACCESS PATH
COLUMN: SMALL_NUM_(NUMBER) Col#: 4 Table: PARENT Alias: P
Size: 4 NDV: 2000 Nulls: 0 Density: 5.0000e-004 Min: 0 Max: 1999
TABLE: PARENT Alias: P
Original Card: 10000 Rounded: 110 Computed: 110.05 Non Adjusted: 110.05
Access Path: table-scan Resc: 631 Resp: 631
BEST_CST: 631.09 PATH: 2 Degree: 1
***************************************
SINGLE TABLE ACCESS PATH
COLUMN: SMALL_NUM_(NUMBER) Col#: 5 Table: CHILD Alias: C
Size: 4 NDV: 10000 Nulls: 0 Density: 1.0000e-004 Min: 0 Max: 9999
TABLE: CHILD Alias: C
Original Card: 40000 Rounded: 68 Computed: 68.01 Non Adjusted: 68.01
Access Path: table-scan Resc: 2517 Resp: 2517
BEST_CST: 2517.49 PATH: 2 Degree: 1
Sanity Checks /남송휘
두 테이블간 혹은 그이상의 조인 조건절에서 어떤 테이블의 선택도를 사용할지 결정하기 위해 옵티마이져는 각 조건절을 분리해서 다루고 10g는 조인의 한족 선택도만을 취함
- 조인 컬럼이 결합 인덱스 컬럼 모두를 포함: user_indexes 뷰의 distinct_keys 컬럼 값을 적정한 조인선택도로 간주
- 적절한 인덱스가 없으면: 두 테이블중 한쪽의 조인 컬럼 선택도를 모두 곱한 값이용
Table: CHILD Concatenated index card: 10000.000000
Table: PARENT Concatenated index card: 2000.000000
Table: CHILD Multi-column join key card: 40000.000000
Table: PARENT Multi-column join key card: 10000.000000
Table: PARENT Multi-column join key card: 10000.000000
Table: GRANDPARENT Multi-column join key card: 2000.000000
General Plans /남송휘
greatgrandparent 테이블로 시작
ggp-gp-p-c,ggp_gp-c-p, ggp-p-gp-c,ggp-p-c-gp,ggp-c-gp-p,ggp-c-p-gp 의형태로
grandparent , parent, child 순으로 각각 6개씩 진행
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***********************
Join order[1] /이태경
Join order[1]: CHILD[C]#0 PARENT[P]#1 GRANDPARENT[GP]#2 GREATGRANDPARENT[GGP]#3
Now joining: PARENT[P]#1 *******
NL Join
Outer table: cost: 2517 cdn: 68 rcz: 27 resp: 2517
Inner table: PARENT Alias: P
Access Path: table-scan Resc: 629
Join: Resc: 45302 Resp: 45302
Access Path: index (unique)
Index: P_PK
rsc_cpu: 15620 rsc_io: 1
ix_sel: 1.0000e-004 ix_sel_with_filters: 1.0000e-004
NL Join: resc: 2586 resp: 2586
Access Path: index (eq-unique)
Index: P_PK
rsc_cpu: 15820 rsc_io: 1
ix_sel: 0.0000e+000 ix_sel_with_filters: 0.0000e+000
NL Join: resc: 2586 resp: 2586
Best NL cost: 2586 resp: 2586
Using concatenated index cardinality for table PARENT
Revised join selectivity: 1.0000e-004 = 7.9445e-007 * (1/10000) * (1/7.9445e-007)
Join Card: 0.75 = outer (68.01) * inner (110.05) * sel (1.0000e-004)
SM Join
Outer table:
resc: 2517 cdn: 68 rcz: 27 deg: 1 resp: 2517
Inner table: PARENT Alias: P
resc: 631 cdn: 110 rcz: 27 deg: 1 resp: 631
using join:1 distribution:2 #groups:1
SORT resource Sort statistics
Sort width: 58 Area size: 208896 Max Area size: 10485760
Degree: 1
Blocks to Sort: 1 Row size: 40 Total Rows: 68
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 5018650
Total Temp space used: 0
SORT resource Sort statistics
Sort width: 58 Area size: 208896 Max Area size: 10485760
Degree: 1
Blocks to Sort: 1 Row size: 40 Total Rows: 110
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 5033608
Total Temp space used: 0
Merge join Cost: 3151 Resp: 3151
HA Join
Outer table:
resc: 2517 cdn: 68 rcz: 27 deg: 1 resp: 2517
Inner table: PARENT Alias: P
resc: 631 cdn: 110 rcz: 27 deg: 1 resp: 631
using join:8 distribution:2 #groups:1
Hash join one ptn Resc: 1 Deg: 1
hash_area: 124 (max=2560) buildfrag: 1 probefrag: 1 ppasses: 1
Hash join Resc: 3149 Resp: 3149
Join result: cost: 2586 cdn: 1 rcz: 54
Now joining: GRANDPARENT[GP]#2 *******
NL Join
Outer table: cost: 2586 cdn: 1 rcz: 54 resp: 2586
Inner table: GRANDPARENT Alias: GP
Access Path: table-scan Resc: 128
Join: Resc: 2714 Resp: 2714
Access Path: index (unique)
Index: GP_PK
rsc_cpu: 15589 rsc_io: 1
ix_sel: 5.0000e-004 ix_sel_with_filters: 5.0000e-004
NL Join: resc: 2587 resp: 2587
Access Path: index (eq-unique)
Index: GP_PK
rsc_cpu: 15789 rsc_io: 1
ix_sel: 0.0000e+000 ix_sel_with_filters: 0.0000e+000
NL Join: resc: 2587 resp: 2587
Best NL cost: 2587 resp: 2587
Using concatenated index cardinality for table GRANDPARENT
Revised join selectivity: 5.0000e-004 = 8.3417e-005 * (1/2000) * (1/8.3417e-005)
Join Card: 0.04 = outer (0.75) * inner (110.25) * sel (5.0000e-004)
SM Join
Outer table:
resc: 2586 cdn: 1 rcz: 54 deg: 1 resp: 2586
Inner table: GRANDPARENT Alias: GP
resc: 128 cdn: 110 rcz: 23 deg: 1 resp: 128
using join:1 distribution:2 #groups:1
SORT resource Sort statistics
Sort width: 58 Area size: 208896 Max Area size: 10485760
Degree: 1
Blocks to Sort: 1 Row size: 70 Total Rows: 1
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 5000000
Total Temp space used: 0
SORT resource Sort statistics
Sort width: 58 Area size: 208896 Max Area size: 10485760
Degree: 1
Blocks to Sort: 1 Row size: 36 Total Rows: 110
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 5033608
Total Temp space used: 0
Merge join Cost: 2716 Resp: 2716
HA Join
Outer table:
resc: 2586 cdn: 1 rcz: 54 deg: 1 resp: 2586
Inner table: GRANDPARENT Alias: GP
resc: 128 cdn: 110 rcz: 23 deg: 1 resp: 128
using join:8 distribution:2 #groups:1
Hash join one ptn Resc: 1 Deg: 1
hash_area: 124 (max=2560) buildfrag: 1 probefrag: 1 ppasses: 1
Hash join Resc: 2714 Resp: 2714
Join result: cost: 2587 cdn: 1 rcz: 77
Now joining: GREATGRANDPARENT[GGP]#3 *******
NL Join
Outer table: cost: 2587 cdn: 1 rcz: 77 resp: 2587
Inner table: GREATGRANDPARENT Alias: GGP
Access Path: table-scan Resc: 64
Join: Resc: 2651 Resp: 2651
Access Path: index (unique)
Index: GGP_PK
rsc_cpu: 15558 rsc_io: 1
ix_sel: 1.0000e-003 ix_sel_with_filters: 1.0000e-003
NL Join: resc: 2588 resp: 2588
Access Path: index (eq-unique)
Index: GGP_PK
rsc_cpu: 15758 rsc_io: 1
ix_sel: 0.0000e+000 ix_sel_with_filters: 0.0000e+000
NL Join: resc: 2588 resp: 2588
Best NL cost: 2588 resp: 2588
Join Card: 0.04 = outer (0.04) * inner (261.26) * sel (3.8168e-003)
SM Join
Outer table:
resc: 2587 cdn: 1 rcz: 77 deg: 1 resp: 2587
Inner table: GREATGRANDPARENT Alias: GGP
resc: 64 cdn: 261 rcz: 19 deg: 1 resp: 64
using join:1 distribution:2 #groups:1
SORT resource Sort statistics
Sort width: 58 Area size: 208896 Max Area size: 10485760
Degree: 1
Blocks to Sort: 1 Row size: 95 Total Rows: 1
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 5000000
Total Temp space used: 0
SORT resource Sort statistics
Sort width: 58 Area size: 208896 Max Area size: 10485760
Degree: 1
Blocks to Sort: 1 Row size: 31 Total Rows: 261
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 5094402
Total Temp space used: 0
Merge join Cost: 2653 Resp: 2653
HA Join
Outer table:
resc: 2587 cdn: 1 rcz: 77 deg: 1 resp: 2587
Inner table: GREATGRANDPARENT Alias: GGP
resc: 64 cdn: 261 rcz: 19 deg: 1 resp: 64
using join:8 distribution:2 #groups:1
Hash join one ptn Resc: 1 Deg: 1
hash_area: 124 (max=2560) buildfrag: 1 probefrag: 1 ppasses: 1
Hash join Resc: 2652 Resp: 2652
Join result: cost: 2588 cdn: 1 rcz: 96
Best so far: TABLE#: 0 CST: 2517 CDN: 68 BYTES: 1836
Best so far: TABLE#: 1 CST: 2586 CDN: 1 BYTES: 54
Best so far: TABLE#: 2 CST: 2587 CDN: 1 BYTES: 77
Best so far: TABLE#: 3 CST: 2588 CDN: 1 BYTES: 96
***********************
Join order[2] /이태경
Join order[2]: CHILD[C]#0 PARENT[P]#1 GREATGRANDPARENT[GGP]#3 GRANDPARENT[GP]#2
Now joining: GREATGRANDPARENT[GGP]#3 *******
NL Join
Outer table: cost: 2586 cdn: 1 rcz: 54 resp: 2586
Inner table: GREATGRANDPARENT Alias: GGP
Access Path: table-scan Resc: 64
Join: Resc: 2650 Resp: 2650
Best NL cost: 2650 resp: 2650
Join Card: 195.53 = outer (0.75) * inner (261.26) * sel (1.0000e+000)
***********************
Join order[3] /이태경
Join order[3]: CHILD[C]#0 GRANDPARENT[GP]#2 PARENT[P]#1 GREATGRANDPARENT[GGP]#3
Now joining: GRANDPARENT[GP]#2 *******
NL Join
Outer table: cost: 2517 cdn: 68 rcz: 27 resp: 2517
Inner table: GRANDPARENT Alias: GP
Access Path: table-scan Resc: 126
Join: Resc: 11074 Resp: 11074
Best NL cost: 11074 resp: 11074
Join Card: 7497.70 = outer (68.01) * inner (110.25) * sel (1.0000e+000)
***********************
Join order[4] /이태경
Join order[4]: CHILD[C]#0 GREATGRANDPARENT[GGP]#3 PARENT[P]#1 GRANDPARENT[GP]#2
Now joining: GREATGRANDPARENT[GGP]#3 *******
NL Join
Outer table: cost: 2517 cdn: 68 rcz: 27 resp: 2517
Inner table: GREATGRANDPARENT Alias: GGP
Access Path: table-scan Resc: 63
Join: Resc: 6796 Resp: 6796
Best NL cost: 6796 resp: 6796
Join Card: 17766.99 = outer (68.01) * inner (261.26) * sel (1.0000e+000)
***********************
Join order[5] /위충환
: PARENT, CHILD, GRANDPARENT, GREATGRNADPARENT 조인순서로 끝까지 계산처리
Join order[5]: PARENT[P]#1 CHILD[C]#0 GRANDPARENT[GP]#2 GREATGRANDPARENT[GGP]#3
Now joining: CHILD[C]#0 *******
NL Join
Outer table: cost: 631 cdn: 110 rcz: 27 resp: 631
Inner table: CHILD Alias: C
Access Path: table-scan Resc: 2517
Join: Resc: 277488 Resp: 277488
Access Path: index (scan)
Index: C_PK
rsc_cpu: 15543 rsc_io: 2
ix_sel: 5.0000e-011 ix_sel_with_filters: 5.0000e-011
NL Join: resc: 851 resp: 851
Best NL cost: 851 resp: 851
Using concatenated index cardinality for table PARENT
Revised join selectivity: 1.0000e-004 = 7.9445e-007 * (1/10000) * (1/7.9445e-007)
Join Card: 0.75 = outer (110.05) * inner (68.01) * sel (1.0000e-004)
SM Join
Outer table:
resc: 631 cdn: 110 rcz: 27 deg: 1 resp: 631
Inner table: CHILD Alias: C
resc: 2517 cdn: 68 rcz: 27 deg: 1 resp: 2517
using join:1 distribution:2 #groups:1
SORT resource Sort statistics
Sort width: 58 Area size: 208896 Max Area size: 10485760
Degree: 1
Blocks to Sort: 1 Row size: 40 Total Rows: 110
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 5033608
Total Temp space used: 0
SORT resource Sort statistics
Sort width: 58 Area size: 208896 Max Area size: 10485760
Degree: 1
Blocks to Sort: 1 Row size: 40 Total Rows: 68
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 5018650
Total Temp space used: 0
Merge join Cost: 3151 Resp: 3151
HA Join
Outer table:
resc: 631 cdn: 110 rcz: 27 deg: 1 resp: 631
Inner table: CHILD Alias: C
resc: 2517 cdn: 68 rcz: 27 deg: 1 resp: 2517
using join:8 distribution:2 #groups:1
Hash join one ptn Resc: 1 Deg: 1
hash_area: 124 (max=2560) buildfrag: 1 probefrag: 1 ppasses: 1
Hash join Resc: 3149 Resp: 3149
Join result: cost: 851 cdn: 1 rcz: 54
☞ PARENT, CHILD 처음 두 테이블에 대한 조인 비용 : 851
Now joining: GRANDPARENT[GP]#2 *******
NL Join
Outer table: cost: 851 cdn: 1 rcz: 54 resp: 851
Inner table: GRANDPARENT Alias: GP
Access Path: table-scan Resc: 128
Join: Resc: 979 Resp: 979
Access Path: index (unique)
Index: GP_PK
rsc_cpu: 15589 rsc_io: 1
ix_sel: 5.0000e-004 ix_sel_with_filters: 5.0000e-004
NL Join: resc: 852 resp: 852
Access Path: index (eq-unique)
Index: GP_PK
rsc_cpu: 15789 rsc_io: 1
ix_sel: 0.0000e+000 ix_sel_with_filters: 0.0000e+000
NL Join: resc: 852 resp: 852
Best NL cost: 852 resp: 852
Using concatenated index cardinality for table GRANDPARENT
Revised join selectivity: 5.0000e-004 = 8.3417e-005 * (1/2000) * (1/8.3417e-005)
Join Card: 0.04 = outer (0.75) * inner (110.25) * sel (5.0000e-004)
SM Join
Outer table:
resc: 851 cdn: 1 rcz: 54 deg: 1 resp: 851
Inner table: GRANDPARENT Alias: GP
resc: 128 cdn: 110 rcz: 23 deg: 1 resp: 128
using join:1 distribution:2 #groups:1
SORT resource Sort statistics
Sort width: 58 Area size: 208896 Max Area size: 10485760
Degree: 1
Blocks to Sort: 1 Row size: 70 Total Rows: 1
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 5000000
Total Temp space used: 0
SORT resource Sort statistics
Sort width: 58 Area size: 208896 Max Area size: 10485760
Degree: 1
Blocks to Sort: 1 Row size: 36 Total Rows: 110
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 5033608
Total Temp space used: 0
Merge join Cost: 981 Resp: 981
HA Join
Outer table:
resc: 851 cdn: 1 rcz: 54 deg: 1 resp: 851
Inner table: GRANDPARENT Alias: GP
resc: 128 cdn: 110 rcz: 23 deg: 1 resp: 128
using join:8 distribution:2 #groups:1
Hash join one ptn Resc: 1 Deg: 1
hash_area: 124 (max=2560) buildfrag: 1 probefrag: 1 ppasses: 1
Hash join Resc: 980 Resp: 980
Join result: cost: 852 cdn: 1 rcz: 77
☞ PARENT, CHILD, GRANDPARENT 처음 세 테이블에 대한 조인 비용 : 852
Now joining: GREATGRANDPARENT[GGP]#3 *******
NL Join
Outer table: cost: 852 cdn: 1 rcz: 77 resp: 852
Inner table: GREATGRANDPARENT Alias: GGP
Access Path: table-scan Resc: 64
Join: Resc: 917 Resp: 917
Access Path: index (unique)
Index: GGP_PK
rsc_cpu: 15558 rsc_io: 1
ix_sel: 1.0000e-003 ix_sel_with_filters: 1.0000e-003
NL Join: resc: 853 resp: 853
Access Path: index (eq-unique)
Index: GGP_PK
rsc_cpu: 15758 rsc_io: 1
ix_sel: 0.0000e+000 ix_sel_with_filters: 0.0000e+000
NL Join: resc: 853 resp: 853
Best NL cost: 853 resp: 853
Join Card: 0.04 = outer (0.04) * inner (261.26) * sel (3.8168e-003)
SM Join
Outer table:
resc: 852 cdn: 1 rcz: 77 deg: 1 resp: 852
Inner table: GREATGRANDPARENT Alias: GGP
resc: 64 cdn: 261 rcz: 19 deg: 1 resp: 64
using join:1 distribution:2 #groups:1
SORT resource Sort statistics
Sort width: 58 Area size: 208896 Max Area size: 10485760
Degree: 1
Blocks to Sort: 1 Row size: 95 Total Rows: 1
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 5000000
Total Temp space used: 0
SORT resource Sort statistics
Sort width: 58 Area size: 208896 Max Area size: 10485760
Degree: 1
Blocks to Sort: 1 Row size: 31 Total Rows: 261
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 5094402
Total Temp space used: 0
Merge join Cost: 919 Resp: 919
HA Join
Outer table:
resc: 852 cdn: 1 rcz: 77 deg: 1 resp: 852
Inner table: GREATGRANDPARENT Alias: GGP
resc: 64 cdn: 261 rcz: 19 deg: 1 resp: 64
using join:8 distribution:2 #groups:1
Hash join one ptn Resc: 1 Deg: 1
hash_area: 124 (max=2560) buildfrag: 1 probefrag: 1 ppasses: 1
Hash join Resc: 917 Resp: 917
Join result: cost: 853 cdn: 1 rcz: 96
☞ Join Order[5] (PARENT, CHILD, GRANDPARENT, GREATGRNADPARENT 순의 네 테이블) 조인 비용 : 853
Best so far: TABLE#: 1 CST: 631 CDN: 110 BYTES: 2970
Best so far: TABLE#: 0 CST: 851 CDN: 1 BYTES: 54
Best so far: TABLE#: 2 CST: 852 CDN: 1 BYTES: 77
Best so far: TABLE#: 3 CST: 853 CDN: 1 BYTES: 96
***********************
Join order[6] /위충환
: 조인 순서를 PARENT, CHILD,
GREATGRNADPARENT, GRANDPARENT 변경
한 테스트로 카테지안 조인이 아니지만,
테이블 간 관계를 보지 못하고 오라클은 해당 조인 폐기
Join order[6]: PARENT[P]#1 CHILD[C]#0 GREATGRANDPARENT[GGP]#3 GRANDPARENT[GP]#2
Now joining: GREATGRANDPARENT[GGP]#3 *******
NL Join
Outer table: cost: 851 cdn: 1 rcz: 54 resp: 851
Inner table: GREATGRANDPARENT Alias: GGP
Access Path: table-scan Resc: 64
Join: Resc: 916 Resp: 916
Best NL cost: 916 resp: 916
Join Card: 195.53 = outer (0.75) * inner (261.26) * sel (1.0000e+000)
***********************
Join order[7] /위충환
: PARENT,
GRANDPARENT, CHILD,
GREATGRNADPARENT 조인순서로 끝까지 계산처리, 이번 결과 비용이 best so far
Join order[7]: PARENT[P]#1 GRANDPARENT[GP]#2 CHILD[C]#0 GREATGRANDPARENT[GGP]#3
Now joining: GRANDPARENT[GP]#2 *******
NL Join
Outer table: cost: 631 cdn: 110 rcz: 27 resp: 631
Inner table: GRANDPARENT Alias: GP
Access Path: table-scan Resc: 126
Join: Resc: 14473 Resp: 14473
Access Path: index (unique)
Index: GP_PK
rsc_cpu: 15589 rsc_io: 1
ix_sel: 5.0000e-004 ix_sel_with_filters: 5.0000e-004
NL Join: resc: 741 resp: 741
Access Path: index (eq-unique)
Index: GP_PK
rsc_cpu: 15789 rsc_io: 1
ix_sel: 0.0000e+000 ix_sel_with_filters: 0.0000e+000
NL Join: resc: 741 resp: 741
Best NL cost: 741 resp: 741
Using concatenated index cardinality for table GRANDPARENT
Revised join selectivity: 5.0000e-004 = 8.3417e-005 * (1/2000) * (1/8.3417e-005)
Join Card: 6.07 = outer (110.05) * inner (110.25) * sel (5.0000e-004)
SM Join
Outer table:
resc: 631 cdn: 110 rcz: 27 deg: 1 resp: 631
Inner table: GRANDPARENT Alias: GP
resc: 128 cdn: 110 rcz: 23 deg: 1 resp: 128
using join:1 distribution:2 #groups:1
SORT resource Sort statistics
Sort width: 58 Area size: 208896 Max Area size: 10485760
Degree: 1
Blocks to Sort: 1 Row size: 40 Total Rows: 110
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 5033608
Total Temp space used: 0
SORT resource Sort statistics
Sort width: 58 Area size: 208896 Max Area size: 10485760
Degree: 1
Blocks to Sort: 1 Row size: 36 Total Rows: 110
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 5033608
Total Temp space used: 0
Merge join Cost: 761 Resp: 761
HA Join
Outer table:
resc: 631 cdn: 110 rcz: 27 deg: 1 resp: 631
Inner table: GRANDPARENT Alias: GP
resc: 128 cdn: 110 rcz: 23 deg: 1 resp: 128
using join:8 distribution:2 #groups:1
Hash join one ptn Resc: 1 Deg: 1
hash_area: 124 (max=2560) buildfrag: 1 probefrag: 1 ppasses: 1
Hash join Resc: 759 Resp: 759
Join result: cost: 741 cdn: 6 rcz: 50
☞ PARENT, GRANDPARENT 처음 두 테이블에 대한 조인 비용 : 741
Now joining: CHILD[C]#0 *******
NL Join
Outer table: cost: 741 cdn: 6 rcz: 50 resp: 741
Inner table: CHILD Alias: C
Access Path: table-scan Resc: 2517
Join: Resc: 15844 Resp: 15844
Access Path: index (scan)
Index: C_PK
rsc_cpu: 15543 rsc_io: 2
ix_sel: 5.0000e-011 ix_sel_with_filters: 5.0000e-011
NL Join: resc: 753 resp: 753
Best NL cost: 753 resp: 753
Using concatenated index cardinality for table PARENT
Revised join selectivity: 1.0000e-004 = 7.9445e-007 * (1/10000) * (1/7.9445e-007)
Join Card: 0.04 = outer (6.07) * inner (68.01) * sel (1.0000e-004)
SM Join
Outer table:
resc: 741 cdn: 6 rcz: 50 deg: 1 resp: 741
Inner table: CHILD Alias: C
resc: 2517 cdn: 68 rcz: 27 deg: 1 resp: 2517
using join:1 distribution:2 #groups:1
SORT resource Sort statistics
Sort width: 58 Area size: 208896 Max Area size: 10485760
Degree: 1
Blocks to Sort: 1 Row size: 65 Total Rows: 6
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 5000699
Total Temp space used: 0
SORT resource Sort statistics
Sort width: 58 Area size: 208896 Max Area size: 10485760
Degree: 1
Blocks to Sort: 1 Row size: 40 Total Rows: 68
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 5018650
Total Temp space used: 0
Merge join Cost: 3261 Resp: 3261
HA Join
Outer table:
resc: 741 cdn: 6 rcz: 50 deg: 1 resp: 741
Inner table: CHILD Alias: C
resc: 2517 cdn: 68 rcz: 27 deg: 1 resp: 2517
using join:8 distribution:2 #groups:1
Hash join one ptn Resc: 1 Deg: 1
hash_area: 124 (max=2560) buildfrag: 1 probefrag: 1 ppasses: 1
Hash join Resc: 3259 Resp: 3259
Join result: cost: 753 cdn: 1 rcz: 77
☞ PARENT, GRANDPARENT, CHILD 처음 세 테이블에 대한 조인 비용 : 753
Now joining: GREATGRANDPARENT[GGP]#3 *******
NL Join
Outer table: cost: 753 cdn: 1 rcz: 77 resp: 753
Inner table: GREATGRANDPARENT Alias: GGP
Access Path: table-scan Resc: 64
Join: Resc: 818 Resp: 818
Access Path: index (unique)
Index: GGP_PK
rsc_cpu: 15558 rsc_io: 1
ix_sel: 1.0000e-003 ix_sel_with_filters: 1.0000e-003
NL Join: resc: 754 resp: 754
Access Path: index (eq-unique)
Index: GGP_PK
rsc_cpu: 15758 rsc_io: 1
ix_sel: 0.0000e+000 ix_sel_with_filters: 0.0000e+000
NL Join: resc: 754 resp: 754
Best NL cost: 754 resp: 754
Join Card: 0.04 = outer (0.04) * inner (261.26) * sel (3.8168e-003)
SM Join
Outer table:
resc: 753 cdn: 1 rcz: 77 deg: 1 resp: 753
Inner table: GREATGRANDPARENT Alias: GGP
resc: 64 cdn: 261 rcz: 19 deg: 1 resp: 64
using join:1 distribution:2 #groups:1
SORT resource Sort statistics
Sort width: 58 Area size: 208896 Max Area size: 10485760
Degree: 1
Blocks to Sort: 1 Row size: 95 Total Rows: 1
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 5000000
Total Temp space used: 0
SORT resource Sort statistics
Sort width: 58 Area size: 208896 Max Area size: 10485760
Degree: 1
Blocks to Sort: 1 Row size: 31 Total Rows: 261
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 5094402
Total Temp space used: 0
Merge join Cost: 820 Resp: 820
HA Join
Outer table:
resc: 753 cdn: 1 rcz: 77 deg: 1 resp: 753
Inner table: GREATGRANDPARENT Alias: GGP
resc: 64 cdn: 261 rcz: 19 deg: 1 resp: 64
using join:8 distribution:2 #groups:1
Hash join one ptn Resc: 1 Deg: 1
hash_area: 124 (max=2560) buildfrag: 1 probefrag: 1 ppasses: 1
Hash join Resc: 818 Resp: 818
Join result: cost: 754 cdn: 1 rcz: 96
☞ Join Order[7] (PARENT, GRANDPARENT, CHILD, GREATGRNADPARENT 순의 네 테이블) 조인 비용 : 754
Best so far: TABLE#: 1 CST: 631 CDN: 110 BYTES: 2970
Best so far: TABLE#: 2 CST: 741 CDN: 6 BYTES: 300
Best so far: TABLE#: 0 CST: 753 CDN: 1 BYTES: 77
Best so far: TABLE#: 3 CST: 754 CDN: 1 BYTES: 96
***********************
Join order[8] /박우창
Join order[8]: PARENT[P]#1 GRANDPARENT[GP]#2 GREATGRANDPARENT[GGP]#3 CHILD[C]#0
Now joining: GREATGRANDPARENT[GGP]#3 *******
NL Join
Outer table: cost: 741 cdn: 6 rcz: 50 resp: 741
Inner table: GREATGRANDPARENT Alias: GGP
Access Path: table-scan Resc: 63
Join: Resc: 1121 Resp: 1121
Access Path: index (unique)
Index: GGP_PK
rsc_cpu: 15558 rsc_io: 1
ix_sel: 1.0000e-003 ix_sel_with_filters: 1.0000e-003
NL Join: resc: 747 resp: 747
Access Path: index (eq-unique)
Index: GGP_PK
rsc_cpu: 15758 rsc_io: 1
ix_sel: 0.0000e+000 ix_sel_with_filters: 0.0000e+000
NL Join: resc: 747 resp: 747
Best NL cost: 747 resp: 747
Join Card: 6.05 = outer (6.07) * inner (261.26) * sel (3.8168e-003)
SM Join
Outer table:
resc: 741 cdn: 6 rcz: 50 deg: 1 resp: 741
Inner table: GREATGRANDPARENT Alias: GGP
resc: 64 cdn: 261 rcz: 19 deg: 1 resp: 64
using join:1 distribution:2 #groups:1
SORT resource Sort statistics
Sort width: 58 Area size: 208896 Max Area size: 10485760
Degree: 1
Blocks to Sort: 1 Row size: 65 Total Rows: 6
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 5000699
Total Temp space used: 0
SORT resource Sort statistics
Sort width: 58 Area size: 208896 Max Area size: 10485760
Degree: 1
Blocks to Sort: 1 Row size: 31 Total Rows: 261
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 5094402
Total Temp space used: 0
Merge join Cost: 808 Resp: 808
HA Join
Outer table:
resc: 741 cdn: 6 rcz: 50 deg: 1 resp: 741
Inner table: GREATGRANDPARENT Alias: GGP
resc: 64 cdn: 261 rcz: 19 deg: 1 resp: 64
using join:8 distribution:2 #groups:1
Hash join one ptn Resc: 1 Deg: 1
hash_area: 124 (max=2560) buildfrag: 1 probefrag: 1 ppasses: 1
Hash join Resc: 806 Resp: 806
Join result: cost: 747 cdn: 6 rcz: 69
Now joining: CHILD[C]#0 *******
NL Join
Outer table: cost: 747 cdn: 6 rcz: 69 resp: 747
Inner table: CHILD Alias: C
Access Path: table-scan Resc: 2517
Join: Resc: 15850 Resp: 15850
Access Path: index (scan)
Index: C_PK
rsc_cpu: 15543 rsc_io: 2
ix_sel: 5.0000e-011 ix_sel_with_filters: 5.0000e-011
NL Join: resc: 759 resp: 759
Best NL cost: 759 resp: 759
Using concatenated index cardinality for table PARENT
Revised join selectivity: 1.0000e-004 = 7.9445e-007 * (1/10000) * (1/7.9445e-007)
Join Card: 0.04 = outer (6.05) * inner (68.01) * sel (1.0000e-004)
Join cardinality for NL: 0.04, outer: 6.05, inner: 68.01, sel: 1.0000e-004
SM Join
Outer table:
resc: 747 cdn: 6 rcz: 69 deg: 1 resp: 747
Inner table: CHILD Alias: C
resc: 2517 cdn: 68 rcz: 27 deg: 1 resp: 2517
using join:1 distribution:2 #groups:1
SORT resource Sort statistics
Sort width: 58 Area size: 208896 Max Area size: 10485760
Degree: 1
Blocks to Sort: 1 Row size: 86 Total Rows: 6
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 5000699
Total Temp space used: 0
SORT resource Sort statistics
Sort width: 58 Area size: 208896 Max Area size: 10485760
Degree: 1
Blocks to Sort: 1 Row size: 40 Total Rows: 68
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 5018650
Total Temp space used: 0
Merge join Cost: 3267 Resp: 3267
HA Join
Outer table:
resc: 747 cdn: 6 rcz: 69 deg: 1 resp: 747
Inner table: CHILD Alias: C
resc: 2517 cdn: 68 rcz: 27 deg: 1 resp: 2517
using join:8 distribution:2 #groups:1
Hash join one ptn Resc: 1 Deg: 1
hash_area: 124 (max=2560) buildfrag: 1 probefrag: 1 ppasses: 1
Hash join Resc: 3265 Resp: 3265
***********************
Join order[9] /박우창
Join order[9]: PARENT[P]#1 GREATGRANDPARENT[GGP]#3 CHILD[C]#0 GRANDPARENT[GP]#2
Now joining: GREATGRANDPARENT[GGP]#3 *******
NL Join
Outer table: cost: 631 cdn: 110 rcz: 27 resp: 631
Inner table: GREATGRANDPARENT Alias: GGP
Access Path: table-scan Resc: 63
Join: Resc: 7553 Resp: 7553
Best NL cost: 7553 resp: 7553
Join Card: 28751.26 = outer (110.05) * inner (261.26) * sel (1.0000e+000)
***********************
Join order[10] /박우창
Join order[10]: GRANDPARENT[GP]#2 CHILD[C]#0 PARENT[P]#1 GREATGRANDPARENT[GGP]#3
Now joining: CHILD[C]#0 *******
NL Join
Outer table: cost: 128 cdn: 110 rcz: 23 resp: 128
Inner table: CHILD Alias: C
Access Path: table-scan Resc: 2517
Join: Resc: 276985 Resp: 276985
Best NL cost: 276985 resp: 276985
Join Card: 7497.70 = outer (110.25) * inner (68.01) * sel (1.0000e+000)
***********************
Join order[11] /박우창
Join order[11]: GRANDPARENT[GP]#2 PARENT[P]#1 CHILD[C]#0 GREATGRANDPARENT[GGP]#3
Now joining: PARENT[P]#1 *******
NL Join
Outer table: cost: 128 cdn: 110 rcz: 23 resp: 128
Inner table: PARENT Alias: P
Access Path: table-scan Resc: 629
Join: Resc: 69338 Resp: 69338
Access Path: index (scan)
Index: P_PK
rsc_cpu: 15523 rsc_io: 2
ix_sel: 5.0000e-007 ix_sel_with_filters: 5.0000e-007
NL Join: resc: 348 resp: 348
Best NL cost: 348 resp: 348
Using concatenated index cardinality for table GRANDPARENT
Revised join selectivity: 5.0000e-004 = 8.3417e-005 * (1/2000) * (1/8.3417e-005)
Join Card: 6.07 = outer (110.25) * inner (110.05) * sel (5.0000e-004)
SM Join
Outer table:
resc: 128 cdn: 110 rcz: 23 deg: 1 resp: 128
Inner table: PARENT Alias: P
resc: 631 cdn: 110 rcz: 27 deg: 1 resp: 631
using join:1 distribution:2 #groups:1
SORT resource Sort statistics
Sort width: 58 Area size: 208896 Max Area size: 10485760
Degree: 1
Blocks to Sort: 1 Row size: 36 Total Rows: 110
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 5033608
Total Temp space used: 0
SORT resource Sort statistics
Sort width: 58 Area size: 208896 Max Area size: 10485760
Degree: 1
Blocks to Sort: 1 Row size: 40 Total Rows: 110
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 5033608
Total Temp space used: 0
Merge join Cost: 761 Resp: 761
HA Join
Outer table:
resc: 128 cdn: 110 rcz: 23 deg: 1 resp: 128
Inner table: PARENT Alias: P
resc: 631 cdn: 110 rcz: 27 deg: 1 resp: 631
using join:8 distribution:2 #groups:1
Hash join one ptn Resc: 1 Deg: 1
hash_area: 124 (max=2560) buildfrag: 1 probefrag: 1 ppasses: 1
Hash join Resc: 759 Resp: 759
Join result: cost: 348 cdn: 6 rcz: 50
Now joining: CHILD[C]#0 *******
NL Join
Outer table: cost: 348 cdn: 6 rcz: 50 resp: 348
Inner table: CHILD Alias: C
Access Path: table-scan Resc: 2517
Join: Resc: 15450 Resp: 15450
Access Path: index (scan)
Index: C_PK
rsc_cpu: 15543 rsc_io: 2
ix_sel: 5.0000e-011 ix_sel_with_filters: 5.0000e-011
NL Join: resc: 360 resp: 360
Best NL cost: 360 resp: 360
Using concatenated index cardinality for table PARENT
Revised join selectivity: 1.0000e-004 = 7.9445e-007 * (1/10000) * (1/7.9445e-007)
Join Card: 0.04 = outer (6.07) * inner (68.01) * sel (1.0000e-004)
SM Join
Outer table:
resc: 348 cdn: 6 rcz: 50 deg: 1 resp: 348
Inner table: CHILD Alias: C
resc: 2517 cdn: 68 rcz: 27 deg: 1 resp: 2517
using join:1 distribution:2 #groups:1
SORT resource Sort statistics
Sort width: 58 Area size: 208896 Max Area size: 10485760
Degree: 1
Blocks to Sort: 1 Row size: 65 Total Rows: 6
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 5000699
Total Temp space used: 0
SORT resource Sort statistics
Sort width: 58 Area size: 208896 Max Area size: 10485760
Degree: 1
Blocks to Sort: 1 Row size: 40 Total Rows: 68
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 5018650
Total Temp space used: 0
Merge join Cost: 2868 Resp: 2868
HA Join
Outer table:
resc: 348 cdn: 6 rcz: 50 deg: 1 resp: 348
Inner table: CHILD Alias: C
resc: 2517 cdn: 68 rcz: 27 deg: 1 resp: 2517
using join:8 distribution:2 #groups:1
Hash join one ptn Resc: 1 Deg: 1
hash_area: 124 (max=2560) buildfrag: 1 probefrag: 1 ppasses: 1
Hash join Resc: 2866 Resp: 2866
Join result: cost: 360 cdn: 1 rcz: 77
Now joining: GREATGRANDPARENT[GGP]#3 *******
NL Join
Outer table: cost: 360 cdn: 1 rcz: 77 resp: 360
Inner table: GREATGRANDPARENT Alias: GGP
Access Path: table-scan Resc: 64
Join: Resc: 425 Resp: 425
Access Path: index (unique)
Index: GGP_PK
rsc_cpu: 15558 rsc_io: 1
ix_sel: 1.0000e-003 ix_sel_with_filters: 1.0000e-003
NL Join: resc: 361 resp: 361
Access Path: index (eq-unique)
Index: GGP_PK
rsc_cpu: 15758 rsc_io: 1
ix_sel: 0.0000e+000 ix_sel_with_filters: 0.0000e+000
NL Join: resc: 361 resp: 361
Best NL cost: 361 resp: 361
Join Card: 0.04 = outer (0.04) * inner (261.26) * sel (3.8168e-003)
SM Join
Outer table:
resc: 360 cdn: 1 rcz: 77 deg: 1 resp: 360
Inner table: GREATGRANDPARENT Alias: GGP
resc: 64 cdn: 261 rcz: 19 deg: 1 resp: 64
using join:1 distribution:2 #groups:1
SORT resource Sort statistics
Sort width: 58 Area size: 208896 Max Area size: 10485760
Degree: 1
Blocks to Sort: 1 Row size: 95 Total Rows: 1
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 5000000
Total Temp space used: 0
SORT resource Sort statistics
Sort width: 58 Area size: 208896 Max Area size: 10485760
Degree: 1
Blocks to Sort: 1 Row size: 31 Total Rows: 261
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 5094402
Total Temp space used: 0
Merge join Cost: 427 Resp: 427
HA Join
Outer table:
resc: 360 cdn: 1 rcz: 77 deg: 1 resp: 360
Inner table: GREATGRANDPARENT Alias: GGP
resc: 64 cdn: 261 rcz: 19 deg: 1 resp: 64
using join:8 distribution:2 #groups:1
Hash join one ptn Resc: 1 Deg: 1
hash_area: 124 (max=2560) buildfrag: 1 probefrag: 1 ppasses: 1
Hash join Resc: 425 Resp: 425
Join result: cost: 361 cdn: 1 rcz: 96
Best so far: TABLE#: 2 CST: 128 CDN: 110 BYTES: 2530
Best so far: TABLE#: 1 CST: 348 CDN: 6 BYTES: 300
Best so far: TABLE#: 0 CST: 360 CDN: 1 BYTES: 77
Best so far: TABLE#: 3 CST: 361 CDN: 1 BYTES: 96
***********************
Join order[12] /남송휘
join order[11]로부터 부분적인 결과를 사용하여 시간이 절감됨
세번째 greatgrandparent 조인후 중단 현재까지의 최고 비용을 초과
Join order[12]: GRANDPARENT[GP]#2 PARENT[P]#1 GREATGRANDPARENT[GGP]#3 CHILD[C]#0
Now joining: GREATGRANDPARENT[GGP]#3 *******
NL Join
Outer table: cost: 348 cdn: 6 rcz: 50 resp: 348
Inner table: GREATGRANDPARENT Alias: GGP
Access Path: table-scan Resc: 63
Join: Resc: 728 Resp: 728
Access Path: index (unique)
Index: GGP_PK
rsc_cpu: 15558 rsc_io: 1
ix_sel: 1.0000e-003 ix_sel_with_filters: 1.0000e-003
NL Join: resc: 354 resp: 354
Access Path: index (eq-unique)
Index: GGP_PK
rsc_cpu: 15758 rsc_io: 1
ix_sel: 0.0000e+000 ix_sel_with_filters: 0.0000e+000
NL Join: resc: 354 resp: 354
Best NL cost: 354 resp: 354
Join Card: 6.05 = outer (6.07) * inner (261.26) * sel (3.8168e-003)
SM Join
Outer table:
resc: 348 cdn: 6 rcz: 50 deg: 1 resp: 348
Inner table: GREATGRANDPARENT Alias: GGP
resc: 64 cdn: 261 rcz: 19 deg: 1 resp: 64
using join:1 distribution:2 #groups:1
SORT resource Sort statistics
Sort width: 58 Area size: 208896 Max Area size: 10485760
Degree: 1
Blocks to Sort: 1 Row size: 65 Total Rows: 6
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 5000699
Total Temp space used: 0
SORT resource Sort statistics
Sort width: 58 Area size: 208896 Max Area size: 10485760
Degree: 1
Blocks to Sort: 1 Row size: 31 Total Rows: 261
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 5094402
Total Temp space used: 0
Merge join Cost: 415 Resp: 415
HA Join
Outer table:
resc: 348 cdn: 6 rcz: 50 deg: 1 resp: 348
Inner table: GREATGRANDPARENT Alias: GGP
resc: 64 cdn: 261 rcz: 19 deg: 1 resp: 64
using join:8 distribution:2 #groups:1
Hash join one ptn Resc: 1 Deg: 1
hash_area: 124 (max=2560) buildfrag: 1 probefrag: 1 ppasses: 1
Hash join Resc: 413 Resp: 413
Join result: cost: 354 cdn: 6 rcz: 69
Now joining: CHILD[C]#0 *******
NL Join
Outer table: cost: 354 cdn: 6 rcz: 69 resp: 354
Inner table: CHILD Alias: C
Access Path: table-scan Resc: 2517
Join: Resc: 15456 Resp: 15456
Access Path: index (scan)
Index: C_PK
rsc_cpu: 15543 rsc_io: 2
ix_sel: 5.0000e-011 ix_sel_with_filters: 5.0000e-011
NL Join: resc: 366 resp: 366
Best NL cost: 366 resp: 366
Using concatenated index cardinality for table PARENT
Revised join selectivity: 1.0000e-004 = 7.9445e-007 * (1/10000) * (1/7.9445e-007)
Join Card: 0.04 = outer (6.05) * inner (68.01) * sel (1.0000e-004)
Join cardinality for NL: 0.04, outer: 6.05, inner: 68.01, sel: 1.0000e-004
SM Join
Outer table:
resc: 354 cdn: 6 rcz: 69 deg: 1 resp: 354
Inner table: CHILD Alias: C
resc: 2517 cdn: 68 rcz: 27 deg: 1 resp: 2517
using join:1 distribution:2 #groups:1
SORT resource Sort statistics
Sort width: 58 Area size: 208896 Max Area size: 10485760
Degree: 1
Blocks to Sort: 1 Row size: 86 Total Rows: 6
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 5000699
Total Temp space used: 0
SORT resource Sort statistics
Sort width: 58 Area size: 208896 Max Area size: 10485760
Degree: 1
Blocks to Sort: 1 Row size: 40 Total Rows: 68
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 5018650
Total Temp space used: 0
Merge join Cost: 2874 Resp: 2874
HA Join
Outer table:
resc: 354 cdn: 6 rcz: 69 deg: 1 resp: 354
Inner table: CHILD Alias: C
resc: 2517 cdn: 68 rcz: 27 deg: 1 resp: 2517
using join:8 distribution:2 #groups:1
Hash join one ptn Resc: 1 Deg: 1
hash_area: 124 (max=2560) buildfrag: 1 probefrag: 1 ppasses: 1
Hash join Resc: 2872 Resp: 2872
***********************
Join order[13] /남송휘
세번째 테이블과 조인으로 중단, CHILD 테이블과의 표면적인 카디젼 조인이 문제
Join order[13]: GRANDPARENT[GP]#2 GREATGRANDPARENT[GGP]#3 CHILD[C]#0 PARENT[P]#1
Now joining: GREATGRANDPARENT[GGP]#3 *******
NL Join
Outer table: cost: 128 cdn: 110 rcz: 23 resp: 128
Inner table: GREATGRANDPARENT Alias: GGP
Access Path: table-scan Resc: 63
Join: Resc: 7049 Resp: 7049
Access Path: index (unique)
Index: GGP_PK
rsc_cpu: 15558 rsc_io: 1
ix_sel: 1.0000e-003 ix_sel_with_filters: 1.0000e-003
NL Join: resc: 238 resp: 238
Access Path: index (eq-unique)
Index: GGP_PK
rsc_cpu: 15758 rsc_io: 1
ix_sel: 0.0000e+000 ix_sel_with_filters: 0.0000e+000
NL Join: resc: 238 resp: 238
Best NL cost: 238 resp: 238
Join Card: 109.94 = outer (110.25) * inner (261.26) * sel (3.8168e-003)
Join cardinality for NL: 109.94, outer: 110.25, inner: 261.26, sel: 3.8168e-003
SM Join
Outer table:
resc: 128 cdn: 110 rcz: 23 deg: 1 resp: 128
Inner table: GREATGRANDPARENT Alias: GGP
resc: 64 cdn: 261 rcz: 19 deg: 1 resp: 64
using join:1 distribution:2 #groups:1
SORT resource Sort statistics
Sort width: 58 Area size: 208896 Max Area size: 10485760
Degree: 1
Blocks to Sort: 1 Row size: 36 Total Rows: 110
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 5033608
Total Temp space used: 0
SORT resource Sort statistics
Sort width: 58 Area size: 208896 Max Area size: 10485760
Degree: 1
Blocks to Sort: 1 Row size: 31 Total Rows: 261
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 5094402
Total Temp space used: 0
Merge join Cost: 194 Resp: 194
HA Join
Outer table:
resc: 128 cdn: 110 rcz: 23 deg: 1 resp: 128
Inner table: GREATGRANDPARENT Alias: GGP
resc: 64 cdn: 261 rcz: 19 deg: 1 resp: 64
using join:8 distribution:2 #groups:1
Hash join one ptn Resc: 1 Deg: 1
hash_area: 124 (max=2560) buildfrag: 1 probefrag: 1 ppasses: 1
Hash join Resc: 193 Resp: 193
Join result: cost: 193 cdn: 110 rcz: 42
Now joining: CHILD[C]#0 *******
NL Join
Outer table: cost: 193 cdn: 110 rcz: 42 resp: 193
Inner table: CHILD Alias: C
Access Path: table-scan Resc: 2517
Join: Resc: 277050 Resp: 277050
Best NL cost: 277050 resp: 277050
Join Card: 7476.42 = outer (109.94) * inner (68.01) * sel (1.0000e+000)
***********************
Join order[14] /남송휘
join order[13]의 부분결과 재사용
세번재 PARENT 테이블과 가능한 모든 조인을 시도한후 중단
Join order[14]: GRANDPARENT[GP]#2 GREATGRANDPARENT[GGP]#3 PARENT[P]#1 CHILD[C]#0
Now joining: PARENT[P]#1 *******
NL Join
Outer table: cost: 193 cdn: 110 rcz: 42 resp: 193
Inner table: PARENT Alias: P
Access Path: table-scan Resc: 629
Join: Resc: 69403 Resp: 69403
Access Path: index (scan)
Index: P_PK
rsc_cpu: 15523 rsc_io: 2
ix_sel: 5.0000e-007 ix_sel_with_filters: 5.0000e-007
NL Join: resc: 413 resp: 413
Best NL cost: 413 resp: 413
Using concatenated index cardinality for table GRANDPARENT
Revised join selectivity: 5.0000e-004 = 8.3417e-005 * (1/2000) * (1/8.3417e-005)
Join Card: 6.05 = outer (109.94) * inner (110.05) * sel (5.0000e-004)
SM Join
Outer table:
resc: 193 cdn: 110 rcz: 42 deg: 1 resp: 193
Inner table: PARENT Alias: P
resc: 631 cdn: 110 rcz: 27 deg: 1 resp: 631
using join:1 distribution:2 #groups:1
SORT resource Sort statistics
Sort width: 58 Area size: 208896 Max Area size: 10485760
Degree: 1
Blocks to Sort: 1 Row size: 57 Total Rows: 110
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 5033608
Total Temp space used: 0
SORT resource Sort statistics
Sort width: 58 Area size: 208896 Max Area size: 10485760
Degree: 1
Blocks to Sort: 1 Row size: 40 Total Rows: 110
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 5033608
Total Temp space used: 0
Merge join Cost: 826 Resp: 826
HA Join
Outer table:
resc: 193 cdn: 110 rcz: 42 deg: 1 resp: 193
Inner table: PARENT Alias: P
resc: 631 cdn: 110 rcz: 27 deg: 1 resp: 631
using join:8 distribution:2 #groups:1
Hash join one ptn Resc: 1 Deg: 1
hash_area: 124 (max=2560) buildfrag: 1 probefrag: 1 ppasses: 1
Hash join Resc: 824 Resp: 824
***********************
Join order[15] /남송휘
GREATGRANDPARENT 테이블 조인이 선두에 나옴
두번째가 child여서 카티젼조인으로 중단 나머지 고려하지 않음
Join order[15]: GREATGRANDPARENT[GGP]#3 CHILD[C]#0 PARENT[P]#1 GRANDPARENT[GP]#2
Now joining: CHILD[C]#0 *******
NL Join
Outer table: cost: 64 cdn: 261 rcz: 19 resp: 64
Inner table: CHILD Alias: C
Access Path: table-scan Resc: 2517
Join: Resc: 656970 Resp: 656970
Best NL cost: 656970 resp: 656970
Join Card: 17766.99 = outer (261.26) * inner (68.01) * sel (1.0000e+000)
***********************
Join order[16] /이창헌
첫 번째 두 테이블 간에 카테지안 조인이 발생하므로 더 이상의 조인 비용 계산 생략
Join order[16]: GREATGRANDPARENT[GGP]#3 PARENT[P]#1 CHILD[C]#0 GRANDPARENT[GP]#2
Now joining: PARENT[P]#1 *******
NL Join
Outer table: cost: 64 cdn: 261 rcz: 19 resp: 64
Inner table: PARENT Alias: P
Access Path: table-scan Resc: 629
Join: Resc: 164281 Resp: 164281
Best NL cost: 164281 resp: 164281
Join Card: 28751.26 = outer (261.26) * inner (110.05) * sel (1.0000e+000)
***********************
Join order[17] /이창헌
primary key 인덱스를 사용하면 정렬하는 것을 회피할 수 있다. 이 경우 더 많은 데이터를 얻고 그것의 일부를 버려야 하는데, 이런 식의 추가적인 작업이 회피한 정렬의 비용 보다 비싼 것으로 판명
Join order[17]: GREATGRANDPARENT[GGP]#3 GRANDPARENT[GP]#2 CHILD[C]#0 PARENT[P]#1
Now joining: GRANDPARENT[GP]#2 *******
NL Join
Outer table: cost: 64 cdn: 261 rcz: 19 resp: 64
Inner table: GRANDPARENT Alias: GP
Access Path: table-scan Resc: 126
Join: Resc: 32906 Resp: 32906
Access Path: index (scan)
Index: GP_PK
rsc_cpu: 23207 rsc_io: 3
ix_sel: 1.0000e-003 ix_sel_with_filters: 1.0000e-003
NL Join: resc: 849 resp: 849
Best NL cost: 849 resp: 849
Join Card: 109.94 = outer (261.26) * inner (110.25) * sel (3.8168e-003)
Join cardinality for NL: 109.94, outer: 261.26, inner: 110.25, sel: 3.8168e-003
카테지안 조인 결과이기 때문에 멈춥다
SM Join
Outer table:
resc: 64 cdn: 261 rcz: 19 deg: 1 resp: 64
Inner table: GRANDPARENT Alias: GP
resc: 128 cdn: 110 rcz: 23 deg: 1 resp: 128
using join:1 distribution:2 #groups:1
SORT resource Sort statistics
Sort width: 58 Area size: 208896 Max Area size: 10485760
Degree: 1
Blocks to Sort: 1 Row size: 31 Total Rows: 261
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 5094402
Total Temp space used: 0
SORT resource Sort statistics
Sort width: 58 Area size: 208896 Max Area size: 10485760
Degree: 1
Blocks to Sort: 1 Row size: 36 Total Rows: 110
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 5033608
Total Temp space used: 0
Merge join Cost: 194 Resp: 194
정렬을 회피하기 위해 primary key인덱스를 사용하는 경우이다. 첫 번째 데이터 집합은 조인 전에 미리 정렬되었으므로 SORT resource로 표시된 섹션은 한번만 나타난다.. -> 인덱스를 사용하지 않는 것보다 더 비싼 것으로 판명나게 되고 따라서 쓸모없는 노력이 된다.
SM Join (with index on outer)
Access Path: index (no start/stop keys)
Index: GGP_PK
rsc_cpu: 2266849 rsc_io: 253
ix_sel: 1.0000e+000 ix_sel_with_filters: 1.0000e+000
Outer table:
resc: 253 cdn: 261 rcz: 19 deg: 1 resp: 253
Inner table: GRANDPARENT Alias: GP
resc: 128 cdn: 110 rcz: 23 deg: 1 resp: 128
using join:1 distribution:2 #groups:1
SORT resource Sort statistics
Sort width: 58 Area size: 208896 Max Area size: 10485760
Degree: 1
Blocks to Sort: 1 Row size: 36 Total Rows: 110
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 5033608
Total Temp space used: 0
Merge join Cost: 382 Resp: 382
HA Join
Outer table:
resc: 64 cdn: 261 rcz: 19 deg: 1 resp: 64
Inner table: GRANDPARENT Alias: GP
resc: 128 cdn: 110 rcz: 23 deg: 1 resp: 128
using join:8 distribution:2 #groups:1
Hash join one ptn Resc: 1 Deg: 1
hash_area: 124 (max=2560) buildfrag: 1 probefrag: 1 ppasses: 1
Hash join Resc: 193 Resp: 193
Join result: cost: 193 cdn: 110 rcz: 42
Now joining: CHILD[C]#0 *******
NL Join
Outer table: cost: 193 cdn: 110 rcz: 42 resp: 193
Inner table: CHILD Alias: C
Access Path: table-scan Resc: 2517
Join: Resc: 277050 Resp: 277050
Best NL cost: 277050 resp: 277050
Join Card: 7476.42 = outer (109.94) * inner (68.01) * sel (1.0000e+000)
***********************
Join order[18] /이창헌
기존 조인에 대한 부분 결과를 재 사용하며, 세 번째 테이블에서 중단한다.
Join order[18]: GREATGRANDPARENT[GGP]#3 GRANDPARENT[GP]#2 PARENT[P]#1 CHILD[C]#0
Now joining: PARENT[P]#1 *******
NL Join
Outer table: cost: 193 cdn: 110 rcz: 42 resp: 193
Inner table: PARENT Alias: P
Access Path: table-scan Resc: 629
Join: Resc: 69403 Resp: 69403
Access Path: index (scan)
Index: P_PK
rsc_cpu: 15523 rsc_io: 2
ix_sel: 5.0000e-007 ix_sel_with_filters: 5.0000e-007
NL Join: resc: 413 resp: 413
Best NL cost: 413 resp: 413
Using concatenated index cardinality for table GRANDPARENT
Revised join selectivity: 5.0000e-004 = 8.3417e-005 * (1/2000) * (1/8.3417e-005)
Join Card: 6.05 = outer (109.94) * inner (110.05) * sel (5.0000e-004)
SM Join
Outer table:
resc: 193 cdn: 110 rcz: 42 deg: 1 resp: 193
Inner table: PARENT Alias: P
resc: 631 cdn: 110 rcz: 27 deg: 1 resp: 631
using join:1 distribution:2 #groups:1
SORT resource Sort statistics
Sort width: 58 Area size: 208896 Max Area size: 10485760
Degree: 1
Blocks to Sort: 1 Row size: 57 Total Rows: 110
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 5033608
Total Temp space used: 0
SORT resource Sort statistics
Sort width: 58 Area size: 208896 Max Area size: 10485760
Degree: 1
Blocks to Sort: 1 Row size: 40 Total Rows: 110
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 5033608
Total Temp space used: 0
Merge join Cost: 826 Resp: 826
HA Join
Outer table:
resc: 193 cdn: 110 rcz: 42 deg: 1 resp: 193
Inner table: PARENT Alias: P
resc: 631 cdn: 110 rcz: 27 deg: 1 resp: 631
using join:8 distribution:2 #groups:1
Hash join one ptn Resc: 1 Deg: 1
hash_area: 124 (max=2560) buildfrag: 1 probefrag: 1 ppasses: 1
Hash join Resc: 824 Resp: 824
(newjo-stop-1) k:0, spcnt:0, perm:18, maxperm:2000
(newjo-save) [2 1 0 3 ]
Final - All Rows Plan:
JOIN ORDER: 11
CST: 361 CDN: 1 RSC: 361 RSP: 361 BYTES: 96
IO-RSC: 360 IO-RSP: 360 CPU-RSC: 5892137 CPU-RSP: 5892137
조인 평가 요약
테스트 스크립트
댓글 0
| 번호 | 제목 | 글쓴이 | 날짜 | 조회 수 |
|---|---|---|---|---|
| 17 | 진행기록 | 운영자 | 2010.04.10 | 7747 |
| 16 |
Front Page
 | 운영자 | 2009.12.20 | 130233 |
| 15 | 비용기반의 오라클 원리 첫 모임 [4] | 운영자 | 2010.01.03 | 5894 |
| 14 | 1. 비용(COST)이란? | ZoneWorker2 | 2009.12.28 | 18069 |
| 13 | 2. 테이블 스캔 | balto | 2009.12.28 | 13286 |
| 12 | 3. 단일 테이블 선택도 | ZoneWorker2 | 2009.12.28 | 8462 |
| 11 |
4. 단순 B-tree 액세스
| 휘휘 | 2009.12.28 | 8945 |
| 10 | 5. 클러스터링 팩터 | balto | 2009.12.28 | 14830 |
| 9 | 6. 선택도 이슈 | ZoneWorker2 | 2009.12.28 | 7688 |
| 8 | 7. 히스토그램 | ZoneWorker2 | 2009.12.28 | 10160 |
| 7 | 8. 비트맵 인덱스 | ZoneWorker2 | 2009.12.28 | 8088 |
| 6 | 9. 쿼리 변환 | 헌쓰 | 2009.12.28 | 15181 |
| 5 | 10. 조인 카디널리티 | 휘휘 | 2009.12.28 | 8894 |
| 4 | 11. NL(Nested Loops) 조인 | 휘휘 | 2009.12.28 | 11925 |
| 3 |
12. 해시 조인
| 헌쓰 | 2009.12.28 | 17078 |
| 2 | 13. 정렬과 머지 조인 | 실천하자 | 2009.12.28 | 16217 |
| » |
14. 10053 트레이스 파일
| 휘휘 | 2009.12.28 | 10340 |