Oracle 1053事件 主要看是成本如何计算的?
Apress.Cost.Based.Oracle.Fundamentals. 看中文翻译版看的很吃力. 当初不知10053事件,看完该书花费了很长的时间!
10053事件
1 创建测试表 create table zengmuansha_temp01 as select * from dba_objects ;
2 在PL/SQL工具的COMMAND窗口 或者SQLPLUS
3 获得会话id:SELECTSYS_CONTEXT ('USERENV', 'SID') sid FROM DUAL;
4 设置跟踪文件标识: altersession set tracefile_identifier='kk_flag';
5 alter session set events '10053 trace name context forever, level 1';
6 declare
typetype_owner istableofzengfankun_temp01.owner%typeindexbybinary_integer;
typetype_object_name istableofzengfankun_temp01.object_name%typeindexbybinary_integer;
typetype_object_id istableofzengfankun_temp01.object_id%typeindexbybinary_integer;
typetype_object_type istableofzengfankun_temp01.object_type%typeindexbybinary_integer;
typetype_last_ddl_time istableofzengfankun_temp01.last_ddl_time%typeindexbybinary_integer;
l_ary_owner type_owner;
l_ary_object_name type_object_name;
l_ary_object_id type_object_id;
l_ary_object_type type_object_type;
l_ary_last_ddl_time type_last_ddl_time;
cursorcur_object is
selectowner,object_name,object_id,object_type,last_ddl_time
from zengfankun_temp01
order by owner,object_name,object_id,object_type,last_ddl_time;
begin
open cur_object;
loop
fetch cur_object bulkcollectinto
l_ary_owner,
l_ary_object_name,
l_ary_object_id,
l_ary_object_type,
l_ary_last_ddl_time
limit100;
exitwhen cur_object%notfoundor cur_object%notfoundisnull;
endloop;
end;
/
7 altersession set events '10053 trace name context off';
8 获得跟踪文件名称和路径
SelectRtrim(c.Value, '/') || Decode(e.Plat_Id, 1, '\', '/') ||
d.Instance_Name || '_ora_' || Ltrim(To_Char(a.Spid))|| '.trc' Trace_File
From V$process a,
V$session b,
V$parameter c,
V$instance d,
(SelectCase
When t.Banner Like'%Windows%'Then
1
When t.Banner Like'%Linux%'Then
2
When t.Banner Like'%AIX%'Then
3
When t.Banner Like'%Solaris%'Then
4
End Plat_Id
From V$version t
Where t.BANNER Like'%TNS%') e
Where a.Addr = b.Paddr
And b.Sid = &P_SID
And c.Name = 'user_dump_dest';
9 跟踪文件内容
/home/oracle/app/oracle/admin/oss2/udump/oss2_ora_13859_kk_flag.trc
OracleDatabase 10g EnterpriseEdition Release 10.2.0.3.0 - 64bit Production
Withthe Partitioning, OLAP and Data Mining options
ORACLE_HOME= /home/oracle/app/oracle/product/10.2.0/db_1
Systemname: Linux
Nodename: db101
Release: 2.6.18-164.el5
Version: #1 SMP Tue Aug 18 15:51:48 EDT 2009
Machine: x86_64
Instancename: oss2
Redothread mounted by this instance: 1
Oracleprocess number: 58
Unixprocess pid: 13859, image: oracleoss2@db101
***2012-08-14 09:29:04.559
***ACTION NAME:(Command Window - New) 2012-08-14 09:29:04.559
*** MODULENAME:(PL/SQL Developer) 2012-08-14 09:29:04.559
***SERVICE NAME:(oss2) 2012-08-14 09:29:04.559
***SESSION ID:(1598.1946) 2012-08-14 09:29:04.559
Registeredqb: MISC$1 0x287158f8(PARSER)
signature(): NULL
**************************
PredicateMove-Around (PM)
**************************
PM:Considering predicate move-around in MISC$1 (#0).
PM: Checking validity of predicate move-aroundin MISC$1 (#0).
CBQT:Validity checks failed for 5q23guhp5t335.
CVM:Considering view merge in query block MISC$1 (#0)
CBQT:Validity checks failed for 5q23guhp5t335.
***************
SubqueryUnnest
***************
SU:Considering subquery unnesting in query block MISC$1 (#0)
*************************
Set-JoinConversion (SJC)
*************************
SJC:Considering set-join conversion in MISC$1 (#0).
**************************
PredicateMove-Around (PM)
**************************
PM:Considering predicate move-around in MISC$1 (#0).
PM: Checking validity of predicate move-aroundin MISC$1 (#0).
PM: PM bypassed: Outer query contains noviews.
Registeredqb: SEL$1 0x28712428 (PARSER)
signature (): qb_name=SEL$1 nbfros=1 flg=0
fro(0): flg=4 objn=440475hint_alias="ZENGFANKUN_TEMP01"@"SEL$1"
**************************
PredicateMove-Around (PM)
**************************
PM:Considering predicate move-around in SEL$1 (#0).
PM: Checking validity of predicate move-aroundin SEL$1 (#0).
CBQT:Validity checks failed for 7nvfsndyywp50.
CVM:Considering view merge in query block SEL$1 (#0)
CBQT:Validity checks failed for 7nvfsndyywp50.
***************
SubqueryUnnest
***************
SU:Considering subquery unnesting in query block SEL$1 (#0)
*************************
Set-JoinConversion (SJC)
*************************
SJC:Considering set-join conversion in SEL$1 (#0).
**************************
PredicateMove-Around (PM)
**************************
PM:Considering predicate move-around in SEL$1 (#0).
PM: Checking validity of predicate move-aroundin SEL$1 (#0).
PM: PM bypassed: Outer query contains no views.
FPD:Considering simple filter push in SEL$1 (#0)
FPD: Current where clause predicates in SEL$1(#0) :
apadrv-start: call(in-use=600,alloc=16344), compile(in-use=40192, alloc=41240)
kkoqbc-start
: call(in-use=600, alloc=16344), compile(in-use=41040,alloc=41240)
******************************************
CurrentSQL statement for this session:
SELECTOWNER,OBJECT_NAME,OBJECT_ID,OBJECT_TYPE,LAST_DDL_TIME FROM ZENGFANKUN_TEMP01ORDER BY OWNER,OBJECT_NAME,OBJECT_TYPE,LAST_DDL_TIME
-----PL/SQL Call Stack -----
object line object
handle number name
0x74772a68 15 anonymous block
0x74772a68 19 anonymous block
*******************************************
Legend
Thefollowing 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 ofright
64: partition right using partitioning ofleft
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
*******************************************
Peekedvalues of the binds in SQL statement
*******************************************
***************************************
PARAMETERSUSED BY THE OPTIMIZER
********************************
*************************************
PARAMETERS WITH ALTERED VALUES
******************************
_pga_max_size = 2097140 KB
*********************************
Bug Fix Control Environment
***************************
fix 4611850 = enabled
fix 4908162 = enabled
fix 5015557 = enabled
*************************************
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 = 8
active_instance_count = 1
parallel_threads_per_cpu = 2
hash_area_size = 131072
bitmap_merge_area_size = 1048576
sort_area_size = 65536
sort_area_retained_size = 0
_sort_elimination_cost_ratio = 0
_optimizer_block_size = 8192
_sort_multiblock_read_count = 2
_hash_multiblock_io_count = 0
_db_file_optimizer_read_count = 16
_optimizer_max_permutations = 2000
pga_aggregate_target = 10485760 KB
_query_rewrite_maxdisjunct = 257
_smm_auto_min_io_size = 56 KB
_smm_auto_max_io_size = 248 KB
_smm_min_size = 1024 KB
_smm_max_size = 1048570 KB
_smm_px_max_size = 5242880 KB
_cpu_to_io = 0
_optimizer_undo_cost_change = 10.2.0.3
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 = true
_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_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_extended_cursor_sharing = udo
_dm_max_shared_pool_pct = 1
_optimizer_cost_hjsmj_multimatch = true
_optimizer_transitivity_retain = true
_px_pwg_enabled = true
optimizer_secure_view_merging = true
_optimizer_join_elimination_enabled = true
flashback_table_rpi = non_fbt
_optimizer_cbqt_no_size_restriction = true
_optimizer_enhanced_filter_push = true
_optimizer_filter_pred_pullup = true
_rowsrc_trace_level = 0
_simple_view_merging = true
_optimizer_rownum_pred_based_fkr = true
_optimizer_better_inlist_costing = all
_optimizer_self_induced_cache_cost = false
_optimizer_min_cache_blocks = 10
_optimizer_or_expansion = depth
_optimizer_order_by_elimination_enabled =true
_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
***************************************
PARAMETERS IN OPT_PARAM HINT
****************************
***************************************
ColumnUsage Monitoring is ON: tracking level = 1
***************************************
****************
QUERYBLOCK TEXT
****************
SELECTOWNER,OBJECT_NAME,OBJECT_ID,OBJECT_TYPE,LAST_DDL_TIME FROM ZENGFANKUN_TEMP01
*********************
QUERYBLOCK SIGNATURE
*********************
qbname was generated
signature(optimizer): qb_name=SEL$1 nbfros=1 flg=0
fro(0): flg=0 objn=440475 hint_alias="ZENGFANKUN_TEMP01"@"SEL$1"
*****************************
SYSTEMSTATISTICS INFORMATION
*****************************
Using WORKLOAD Stats
CPUSPEED: 1042 millions instructions/sec
SREADTIM: 21 milliseconds
MREADTIM: 45 millisecons
MBRC: 12.000000 blocks
MAXTHR: 54754304 bytes/sec
SLAVETHR: -1 bytes/sec
***************************************
BASESTATISTICAL INFORMATION
***********************
TableStats::
Table: ZENGFANKUN_TEMP01 Alias: ZENGFANKUN_TEMP01
#Rows: 126826 #Blks: 2099 AvgRowLen: 114.00
***************************************
SINGLETABLE ACCESS PATH
Table: ZENGFANKUN_TEMP01 Alias: ZENGFANKUN_TEMP01
Card: Original: 126826 Rounded: 126826 Computed: 126826.00 Non Adjusted: 126826.00
Access Path: TableScan
Cost: 381.36 Resp: 381.36 Degree: 0
Cost_io: 379.00 Cost_cpu: 51727443
Resp_io: 379.00 Resp_cpu: 51727443
Best:: AccessPath: TableScan
Cost: 381.36 Degree: 1 Resp: 381.36 Card: 126826.00 Bytes: 0
***************************************
OPTIMIZERSTATISTICS AND COMPUTATIONS
***************************************
GENERALPLANS
***************************************
Consideringcardinality-based initial join order.
Permutationsfor Starting Table :0
***********************
Joinorder[1]: ZENGFANKUN_TEMP01[ZENGFANKUN_TEMP01]#0
ORDERBY sort
SORT resource Sort statistics
Sort width: 6142 Area size: 1048576 Max Area size: 1073735680
Degree: 1
Blocks to Sort: 1056 Row size: 68 Total Rows: 126826
Initial runs: 2 Merge passes: 1 IO Cost / pass: 460
Total IO sort cost: 1516 Total CPU sort cost: 144803056
Total Temp space used: 17327000
***********************
Bestso far: Table#: 0 cost: 1903.9617 card: 126826.0000 bytes: 6594952
(newjo-stop-1)k:0, spcnt:0, perm:1, maxperm:80000
*********************************
Numberof join permutations tried: 1
*********************************
SORT resource Sort statistics
Sort width: 6142 Area size: 1048576 Max Area size: 1073735680
Degree: 1
Blocks to Sort: 1056 Row size: 68 Total Rows: 126826
Initial runs: 2 Merge passes: 1 IO Cost / pass: 460
Total IO sort cost: 1516 Total CPU sort cost: 144803056
Total Temp space used: 17327000
Final- All Rows Plan: Best join order: 1
Cost: 1903.9617 Degree: 1 Card: 126826.0000 Bytes: 6594952
Resc: 1903.9617 Resc_io: 1895.0000 Resc_cpu: 196530498
Resp: 1903.9617 Resp_io: 1895.0000 Resc_cpu: 196530498
kkoipt:Query block SEL$1 (#0)
*******UNPARSED QUERY IS *******
SELECT"ZENGFANKUN_TEMP01"."OWNER""OWNER","ZENGFANKUN_TEMP01"."OBJECT_NAME""OBJECT_NAME","ZENGFANKUN_TEMP01"."OBJECT_ID""OBJECT_ID","ZENGFANKUN_TEMP01"."OBJECT_TYPE""OBJECT_TYPE","ZENGFANKUN_TEMP01"."LAST_DDL_TIME""LAST_DDL_TIME"
FROM"OSS03"."ZENGFANKUN_TEMP01" "ZENGFANKUN_TEMP01"
ORDERBY"ZENGFANKUN_TEMP01"."OWNER","ZENGFANKUN_TEMP01"."OBJECT_NAME","ZENGFANKUN_TEMP01"."OBJECT_TYPE","ZENGFANKUN_TEMP01"."LAST_DDL_TIME"
kkoqbc-end
: call(in-use=26184, alloc=49080),compile(in-use=41544, alloc=45384)
apadrv-end:call(in-use=26184, alloc=49080), compile(in-use=42392, alloc=45384)
sql_id=7nvfsndyywp50.
CurrentSQL statement for this session:
SELECTOWNER,OBJECT_NAME,OBJECT_ID,OBJECT_TYPE,LAST_DDL_TIME FROM ZENGFANKUN_TEMP01ORDER BY OWNER,OBJECT_NAME,OBJECT_TYPE,LAST_DDL_TIME
-----PL/SQL Call Stack -----
object line object
handle number name
0x74772a68 15 anonymous block
0x74772a68 19 anonymous block
============
PlanTable
============
-----------------------------------------------+-----------------------------------+
|Id | Operation | Name | Rows | Bytes | Cost | Time |
-----------------------------------------------+-----------------------------------+
|0 | SELECT STATEMENT | | | | 1904 | |
|1 | SORT ORDER BY | | 124K | 6440K | 1904 | 00:00:41 |
|2 | TABLE ACCESS FULL | ZENGFANKUN_TEMP01| 124K | 6440K | 381 | 00:00:09 |
-----------------------------------------------+-----------------------------------+
PredicateInformation:
----------------------
Contentof other_xml column
===========================
db_version : 10.2.0.3
parse_schema : OSS03
plan_hash : 3659189292
Outline Data:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1""ZENGFANKUN_TEMP01"@"SEL$1")
END_OUTLINE_DATA
*/
Optimizerenvironment:
optimizer_mode_hinted = false
optimizer_features_hinted = 0.0.0
…………………………………………………………
_optimizer_native_full_outer_join = off
*********************************
Bug Fix Control Environment
***************************
fix 4611850 = enabled
fix 5015557 = enabled
QueryBlock Registry:
*********************
SEL$10x28712428 (PARSER) [FINAL]
Optimizer State Dump: call(in-use=28536,alloc=49080), compile(in-use=65344, alloc=115056)
10 如何设置10053 事件设置本session的10053
开启：
Alter session set events’10053 trace namecontext forever[,level {1/2}]’;
关闭：
Alter session set events’10053 trace namecontext off’;
设置其他session 的10053
开启：
SYS.DBMS_SYSTEM.SET_EV (, , 10053, {1|2}, '''')
关闭：
SYS.DBMS_SYSTEM.SET_EV (, , 10053,0, '''')
跟其他跟踪事件不同，10053 提供了两个跟踪级别，但是级别2 的跟踪信息比级别1 少
11记载了所有影响成本计算的参数
***************************************
PARAMETERS USEDBYTHE OPTIMIZER
********************************
12基本统计信息:
***************************************
BASE STATISTICAL INFORMATION
***********************
表
Trace label dba_tables column
CDN NUM_ROWS 表记录数
NBLKS BLOCKS 高水位以下的block 数
TABLE_SCAN_CST 全表扫描的I/O 成本
AVG_ROW_LEN AVG_ROW_LEN 平均行长
索引
Trace label dba_indexes column
Index#, col# 索引号及表列号
LVLS BLEVEL BTREE 索引高度
#LB LEAF_BLOCKS 索引叶块数
#DK DISTINCT_KEYS 不重复索引关键字
LB/K AVG_LEAF_BLOCKS_PER_KEY 叶块/关键字
DB/K AVG_DATA_BLOCKS_PER_KEY 数据块/关键字
CLUF CLUSTERING_FACTOR 索引聚合因子
基本表访问成本：这里开始CBO 将会计算单表访问的成本
单表访问路径
SINGLETABLEACCESS
PATH..................................................................1
Column: ENAME Col#:2Table: EMP Alias:
EMP.....................................................................2
NDV:42NULLS:0DENS:2.3810e-002...........................................3
TABLE: EMP ORIG CDN:7213CMPTD CDN:
172.......................................................................4
Access path: tsc Resc:6Resp:
6........................................................................5
Access path:index
(equal).................................................................6
INDEX#:23575TABLE:
EMP .....................................................................7
CST:39IXSEL:0.0000e+000TBSEL:
2.3810e-002..............................................................8
BEST_CST:6.00PATH:2Degree:
1.......................................................................9
我们看一下上面是什么意思。首先CBO 列出了ename 列的统计信息(第2，3 行),这些
统计信息来自dba_tab_columns。
列的统计信息和dba_tab_columns 中对应的列名如下
Trace label dba_tablescolumn
NDV NUM_DISTINCT 列的不重复值数
NULLS NUM_NULLS 列的空行数
DENS DENSITY 列密度，没有直方图的情况下=1/NDV
LO LOW_VALUE 列的最小值 (只对数字列)
HI HIGH_VALUE 列的最大值(只对数字列)
第4 行出现了表的行数ORIG CDN 和计算过的行数 CMPTD CDN (computed
cardinality). 计算公式如下，
CMPTD CDN = ORIG CDN * FF
在这里 FF 表示过滤因子(FilterFactor)。我们稍后再来看FF 是什么及如何计算的。
第5 行表示了全表扫描的成本。 这里的成本是62, 是由NBLKS 和
db_file_multi_block_read_count 初始化参数计算出来的。.
第6-8 行是索引访问的成本。
第9 行是总结了以上信息并选出了最优的访问路径为全表扫描，成本为6。
表扫描成本
让我们来看一下全表扫描成本(tsc)是如何计算的 这里有其他两个大表的基本统计信息。
TOTAL :: CDN: 115630 NBLKS: 4339TABLE_SCAN_CST: 265 AVG_ROW_LEN: 272
TOTAL :: CDN: 454503 NBLKS: 8975 TABLE_SCAN_CST:548 AVG_ROW_LEN: 151
你可能曾经看到过全表扫描成本=访问的块数目/db_file_multi_block_read_count.
看起来这个等式很有意义因为oracle在做全表扫描时每个I/O请求将会读取db_file_multi_block_read_count个块。
但是，我们计算以上统计信息得到
NBLKS / TABLE_SCAN_CST = 4339 / 265 = 16.373 ≠db_file_multi_block_read_count
(这里的值是32，可以看前面参数)
另外一个表为NBLKS / TABLE_SCAN_CST = 8975 / 548 =16.377
全表扫描成本和db_file_multi_block_read_count
CBO 将会根据NBLKS 和db_file_multiblock_read_count 来估计全表扫描成本，但是
db_file_multiblock_read_count 通常会被打上折扣。实际上我们可以认为等式会是
TABLE_SCAN_CST = NBLKS / k
我们来看一下k 和db_file_multiblock_read_count究竟有什么规律可寻。
我们来做一个实验，使用不同的db_file_multiblock_read_count 值4, 6,8,12,16, 24,32 来测试。
过滤因子(FF)
为了理解索引访问成本我们需要了解一下过滤因子。 过滤因子是一个介于0 和1 之
间的数字，反映了记录的可选择性。如果一个列有10 种不同的值，我们需要查询等
于其中某一个值的记录时，如果这10 种值平均分 布的话，你将得到1/10 的行数。
如果没有直方图，过滤因子为FF = 1/NDV = density
再来看一下过滤因子和查询条件的关系
不使用绑定变量的情况:
predicate Filter factor
c1=value1/c1.num_distinct4
c1likevalue1/c1.num_distinct
c1>value (Hi-value)/(Hi-Lo)
c1>=value (Hi-value)/(Hi-Lo)+
c1<value (value-Lo)/(Hi-Lo)
c1<=value (value-Lo)/(Hi-Lo)+
c1betweenval1andval2 (val2 val1)/(Hi-Lo)+
使用绑定变量的情况(8i):
predicate Filter factor
col1=:b1 col1.density
col1 {like|>|>=|<|<=} :b1{5.0000e-02|col1.col1 between:b1and:b25.0000e-02*5.0000e-
包含and 和or 的情况:
predicate Filter factor
predicate1andpredicate2FF1*FF2
predicate1orpredicate2FF1+FF2 FF1*FF2
索引访问成本
现在我们知道了聚合因子的概念，我们再来看一看索引访问的成本
SINGLETABLEACCESS
PATH.............................................................1
Column: ENAME Col#:2Table: EMP Alias:
EMP...............................................................2
NDV:42NULLS:0DENS:2.3810e-002.....................................3
TABLE: EMP ORIG CDN:7213CMPTD CDN:
172...............................................................4
Access path: tsc Resc:6Resp:
6.................................................................5
Access path:index
(equal)..........................................................6
INDEX#:23575TABLE:
EMP ..............................................................7
CST:39IXSEL:0.0000e+000TBSEL:
2.3810e-002.......................................................8
BEST_CST:6.00PATH:2Degree:
1.................................................................9
我们来看6-8 行，这里表示了索引访问的成本。第6 行表示这里采取索引equal 的方
法来访问，再来回忆一下索引的基本统计信息
INDEX#: 23575 COL#: 2
TOTAL :: LVLS: 1 #LB: 48 #DK: 42 LB/K: 1 DB/K: 36 CLUF: 1534
根据索引成本计算公式
blevel + FF*leaf_blocks +FF*clustering_factor
1 + 2.3810e-002-2*48 + 2.3810e-002-2*1534 =1 + 1.1429 + 36.5245 = 38.6674
这里的FF 就等于TBSEL=DENS=2.3810e-002,由于我们的查询条件为ename = :b1 所
以得出FF 为ENAME 列的DENS， 其实索引访问方式的成本计算公式
. Unique scan blevel+1
. Fast full scan leaf_blocks / k ( k =1.6765x0.6581 )
. Index-only blevel + FF*leaf_blocks
让我们用别的例子证明一下索引成本计算，语句为
select…fromtbl a
wherea.col#1=:b1
anda.col#12=:b2
anda.col#8=:b3
索引和列的基本统计数据如下
INDEX# COL#LVLS #LB #DK LB/K DB/K CLUF
841727,1113100665001221469200
84181,12,7219000747001151176500
84193,1,4,22310004970012118000
157551,12,8112600188001301890275
84161,2,33,4,5,622580018903001183900
Col#:1NDV:10NULLS:0DENS:1.0000e-001-1
Col#:12NDV:8NULLS:0DENS:1.2500e-001
Col#:8NDV:33NULLS:0DENS:3.0303e-001
Access path:index
(scan)....................................1
INDEX#:8418CST:14947IXSEL:1.2500e-002TBSEL:
1.2500e-002...............................2
Access path:index
(equal) ...................................