Thanks Jonathan.
It's standard emp/dept tables of 10g demo.
And I have analyzed all of them.
The following is my step:
SYS AS SYSDBA on 24-MAY-04 >connect scott/tiger
Connected.
SCOTT on 24-MAY-04 >exec dbms_stats.gather_schema_stats('SCOTT')
PL/SQL procedure successfully completed.
SCOTT on 24-MAY-04 >select table_name,num_rows from user_tables;
TABLE_NAME NUM_ROWS
------------------------------ ----------
DEPT 4
EMP 14
BONUS 0
SALGRADE 5
SCOTT on 24-MAY-04 >connect / as sysdba
Connected.
SYS AS SYSDBA on 24-MAY-04 >shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS AS SYSDBA on 24-MAY-04 >startup
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 1301704 bytes
Variable Size 274473784 bytes
Database Buffers 37748736 bytes
Redo Buffers 1048576 bytes
Database mounted.
Database opened.
SYS AS SYSDBA on 24-MAY-04 >connect scott/tiger
Connected.
SCOTT on 24-MAY-04 >alter session set events '10053 trace name
context forever,level 1';
Session altered.
SCOTT on 24-MAY-04 >explain plan for select ename,dname from emp
,dept where emp.deptno = dept.deptno;
Explained.
And every time I got the same result.
I'll try to post trace file here.
/opt/oracle/soft/eygle_ora_9040.trc
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit
Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /opt/oracle/product/10.0.1
System name: SunOS
Node name: billing-center.hurray.com.cn
Release: 5.8
Version: Generic_108528-23
Machine: sun4u
Instance name: eygle
Redo thread mounted by this instance: 1
Oracle process number: 12
Unix process pid: 9040, image: oracle_at_billing-center.hurray.com.cn
(TNS V1-V3)
- 2004-05-24 10:16:50.990
- ACTION NAME:() 2004-05-24 10:16:50.988
- MODULE NAME:(SQL*Plus) 2004-05-24 10:16:50.988
- SERVICE NAME:(SYS$USERS) 2004-05-24 10:16:50.988
- SESSION ID:(162.5) 2004-05-24 10:16:50.988
PARAMETERS USED BY THE OPTIMIZER
PARAMETERS WITH ALTERED VALUES
db_file_multiblock_read_count = 16
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
_optimizer_search_limit = 5
cpu_count = 4
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
_optimizer_max_permutations = 2000
pga_aggregate_target = 24576 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 = 128 KB
_smm_max_size = 1228 KB
_smm_px_max_size = 7371 KB
_cpu_to_io = 0
_optimizer_undo_cost_change = 10.1.0
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
Column Usage Monitoring is ON: tracking level = 1
QUERY BLOCK SIGNATURE
qb name was generated
signature (optimizer): qb_name=SEL$1 nbfros=2 flg=0
fro(0): flg=0 objn=17806 hint_alias="DEPT"@"SEL$1"
fro(1): flg=0 objn=17808 hint_alias="EMP"@"SEL$1"
BASE STATISTICAL INFORMATION
Table stats Table: DEPT Alias: DEPT
TOTAL :: CDN: 4 NBLKS: 5 AVG_ROW_LEN: 20
COLUMN: DEPTNO(NUMBER) Col#: 1 Table: DEPT Alias: DEPT
Size: 3 NDV: 4 Nulls: 0 Density: 2.5000e-01 Min: 10 Max: 40
No Histogram: #BKT: 1
(1 uncompressed buckets and 2 endpoint values)
Index stats
Index: PK_DEPT COL#: 1
TOTAL :: LVLS: 0 #LB: 1 #DK: 4 LB/K: 1 DB/K: 1 CLUF: 1
Table stats Table: EMP Alias: EMP
TOTAL :: CDN: 14 NBLKS: 5 AVG_ROW_LEN: 37
COLUMN: DEPTNO(NUMBER) Col#: 8 Table: EMP Alias: EMP
Size: 3 NDV: 3 Nulls: 0 Density: 3.5714e-02
Frequency Histogram: #BKT: 3
(14 uncompressed buckets and 3 endpoint values)
Index stats
Index: PK_EMP COL#: 1
TOTAL :: LVLS: 0 #LB: 1 #DK: 14 LB/K: 1 DB/K: 1 CLUF: 1
_OPTIMIZER_PERCENT_PARALLEL = 0
SINGLE TABLE ACCESS PATH
TABLE: EMP Alias: EMP
Original Card: 14 Rounded Card: 14 Computed Card: 14.00
Access Path: table-scan Resc: 3 Resp: 3
BEST_CST: 3.02 PATH: 2 Degree: 1
SINGLE TABLE ACCESS PATH
TABLE: DEPT Alias: DEPT
Original Card: 4 Rounded Card: 4 Computed Card: 4.00
Access Path: table-scan Resc: 3 Resp: 3
BEST_CST: 3.02 PATH: 2 Degree: 1
OPTIMIZER STATISTICS AND COMPUTATIONS
GENERAL PLANS
Join order[1]: DEPT[DEPT]#0 EMP[EMP]#1
Now joining: EMP[EMP]#1 *******
NL Join
Outer table: cost: 3 cdn: 4 rcz: 13 resp: 3
Inner table: EMP Alias: EMP
Access Path: table-scan Resc: 2
Join: Resc: 9 Resp: 9
Best NL cost: 9 resp: 9
Join cardinality: 14 = outer (4) * inner (14) * sel (2.5000e-01)
[flag=0]
SM Join
Outer table:
resc: 3 cdn: 4 rcz: 13 deg: 1 resp: 3
Inner table: EMP Alias: EMP
resc: 3 cdn: 14 rcz: 9 deg: 1 resp: 3
using join:1 distribution:2 #groups:1
SORT resource Sort statistics
Sort width: 5 Area size: 131072 Max Area size:
1257472
Degree: 1
Blocks to Sort: 1 Row size: 25 Total Rows:
4
Initial runs: 1 Merge passes: 0 IO Cost / pass:
0
Total IO sort cost: 0 Total CPU sort cost: 1835540
Total Temp space used: 0
SORT resource Sort statistics
Sort width: 5 Area size: 131072 Max Area size:
1257472
Degree: 1
Blocks to Sort: 1 Row size: 20 Total Rows:
14
Initial runs: 1 Merge passes: 0 IO Cost / pass:
0
Total IO sort cost: 0 Total CPU sort cost: 1837581
Total Temp space used: 0
Merge join Cost: 8 Resp: 8
SM Join (with index on outer)
Access Path: index (no start/stop keys)
Index: PK_DEPT
rsc_cpu: 15563 rsc_io: 2
ix_sel: 1.0000e+00 ix_sel_with_filters: 1.0000e+00
Outer table:
resc: 2 cdn: 4 rcz: 13 deg: 1 resp: 2
Inner table: EMP Alias: EMP
resc: 3 cdn: 14 rcz: 9 deg: 1 resp: 3
using join:1 distribution:2 #groups:1
SORT resource Sort statistics
Sort width: 5 Area size: 131072 Max Area size:
1257472
Degree: 1
Blocks to Sort: 1 Row size: 20 Total Rows:
14
Initial runs: 1 Merge passes: 0 IO Cost / pass:
0
Total IO sort cost: 0 Total CPU sort cost: 1837581
Total Temp space used: 0
Merge join Cost: 6 Resp: 6
HA Join
Outer table:
resc: 3 cdn: 4 rcz: 13 deg: 1 resp: 3
Inner table: EMP Alias: EMP
resc: 3 cdn: 14 rcz: 9 deg: 1 resp: 3
using join:8 distribution:2 #groups:1
Hash join one ptn Resc: 1 Deg: 1
hash_area: 32 (max=307) buildfrag: 1 probefrag: 1 ppasses: 1
Hash join Resc: 7 Resp: 7
Join result: cost: 6 cdn: 14 rcz: 22
Best so far: TABLE#: 0 CST: 2 CDN: 4 BYTES:
52
Best so far: TABLE#: 1 CST: 6 CDN: 14 BYTES:
308
Join order[2]: EMP[EMP]#1 DEPT[DEPT]#0
Now joining: DEPT[DEPT]#0 *******
NL Join
Outer table: cost: 3 cdn: 14 rcz: 9 resp: 3
Inner table: DEPT Alias: DEPT
Access Path: table-scan Resc: 1
Join: Resc: 20 Resp: 20
Access Path: index (unique)
Index: PK_DEPT
rsc_cpu: 14613 rsc_io: 1
ix_sel: 2.5000e-01 ix_sel_with_filters: 2.5000e-01
NL Join: resc: 17 resp: 17
Access Path: index (eq-unique)
Index: PK_DEPT
rsc_cpu: 14813 rsc_io: 1
ix_sel: 0.0000e+00 ix_sel_with_filters: 0.0000e+00
NL Join: resc: 17 resp: 17
Best NL cost: 17 resp: 17
Join cardinality: 14 = outer (14) * inner (4) * sel (2.5000e-01)
[flag=0]
SM Join
Outer table:
resc: 3 cdn: 14 rcz: 9 deg: 1 resp: 3
Inner table: DEPT Alias: DEPT
resc: 3 cdn: 4 rcz: 13 deg: 1 resp: 3
using join:1 distribution:2 #groups:1
SORT resource Sort statistics
Sort width: 5 Area size: 131072 Max Area size:
1257472
Degree: 1
Blocks to Sort: 1 Row size: 20 Total Rows:
14
Initial runs: 1 Merge passes: 0 IO Cost / pass:
0
Total IO sort cost: 0 Total CPU sort cost: 1837581
Total Temp space used: 0
SORT resource Sort statistics
Sort width: 5 Area size: 131072 Max Area size:
1257472
Degree: 1
Blocks to Sort: 1 Row size: 25 Total Rows:
4
Initial runs: 1 Merge passes: 0 IO Cost / pass:
0
Total IO sort cost: 0 Total CPU sort cost: 1835540
Total Temp space used: 0
Merge join Cost: 8 Resp: 8
HA Join
Outer table:
resc: 3 cdn: 14 rcz: 9 deg: 1 resp: 3
Inner table: DEPT Alias: DEPT
resc: 3 cdn: 4 rcz: 13 deg: 1 resp: 3
using join:8 distribution:2 #groups:1
Hash join one ptn Resc: 1 Deg: 1
hash_area: 32 (max=307) buildfrag: 1 probefrag: 1 ppasses: 1
Hash join Resc: 7 Resp: 7
(newjo-stop-1) k:0, spcnt:0, perm:2, maxperm:2000
(newjo-save) [0 1 ]
Final - All Rows Plan:
JOIN ORDER: 1
CST: 6 CDN: 14 RSC: 6 RSP: 6 BYTES: 308
IO-RSC: 5 IO-RSP: 5 CPU-RSC: 1890571 CPU-RSP: 1890571
QUERY
explain plan for select ename,dname from emp ,dept where emp.deptno =
dept.deptno
Plan Table
-
| Operation | Name | Rows | Bytes
| Cost | Time | TQ |IN-OUT| PQ Distrib |Pstart| Pstop
|
-
| SELECT STATEMENT | | |
| 6 | | | | | |
|
| MERGE JOIN | | 14 | 308
| 6 | 00:00:01 | | | | |
|
| TABLE ACCESS BY INDEX ROWID | DEPT | 4 | 52
| 2 | 00:00:01 | | | | |
|
| INDEX FULL SCAN | PK_DEPT | 4 |
| 1 | 00:00:01 | | | | |
|
| SORT JOIN | | 14 | 126
| 4 | 00:00:01 | | | | |
|
| TABLE ACCESS FULL | EMP | 14 | 126
| 3 | 00:00:01 | | | | |
|
-
And the cpu_cost and io_cost of plan_table is :
SCOTT on 24-MAY-04 >select
OPERATION,object_name,cost,CARDINALITY,CPU_COST,IO_COST from
plan_table;
OPERATION OBJECT_NAM COST CARDINALITY
CPU_COST IO_COST
------------------------------ ---------- ---------- -----------
---------- ----------
SELECT STATEMENT 6 14
1890571 5
MERGE JOIN 6 14
1890571 5
TABLE ACCESS DEPT 2 4
15563 2
INDEX PK_DEPT 1 4
7921 1
SORT 4 14
1875008 3
TABLE ACCESS EMP 3 14
37427 3
6 rows selected.
Thank you very much.
Received on Sun May 23 2004 - 21:31:34 CDT
