原贴发表在ITPUB
http://www.itpub.net/581612.html
试验环境:
Linux AS4 + 10.2.0.1.0 - 64bit Production
为了模拟数据库对象占用多个block,故设置较高pctfree.
SQL> create table irs pctfree 80 tablespace users as
2 select rownum id,a.* from all_objects a;
Table created.
SQL> alter table irs add constraint irs_id_pk primary key(id);
Table altered.
SQL> create index irs_owner_idx on irs(owner) tablespace indx pctfree 80;
Index created.
SQL> show parameter optimizer_index
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
获取表和索引的统计信息
SQL> analyze table irs compute statistics for table for all indexes;
Table analyzed.
获取列的histogram信息
SQL> analyze table irs compute statistics for all indexed columns;
Table analyzed.
[@more@]SQL> select owner,count(*) from irs
2 group by owner;
OWNER COUNT(*)
------------------------------ ----------
MDSYS 859
DMSYS 189
TSMSYS 2
CTXSYS 338
OLAPSYS 718
OUTLN 7
PUBLIC 20079
EXFSYS 279
SCOTT 6
SYSTEM 425
DBSNMP 46
OWNER COUNT(*)
------------------------------ ----------
LIYONG 2
ORDPLUGINS 10
ORDSYS 1669
SYSMAN 1291
XDB 346
CWT 310
PERFSTAT 139
SI_INFORMTN_SCHEMA 8
SYS 23134
WMSYS 232
21 rows selected.
SQL> select NUM_ROWS,BLOCKS,EMPTY_BLOCKS from dba_tables
2 where table_name='IRS';
NUM_ROWS BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
50089 3423 33
SQL> select BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS,CLUSTERING_FACTOR from dba_indexes
2 where INDEX_NAME='IRS_OWNER_IDX';
BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR
---------- ----------- ------------- -----------------
2 570 21 5890
SQL> select DENSITY,NUM_DISTINCT,HISTOGRAM from dba_tab_columns
2 where OWNER='LIYONG'
3 and TABLE_NAME='IRS'
4 and COLUMN_NAME='OWNER';
DENSITY NUM_DISTINCT HISTOGRAM
---------- ------------ ---------------
9.9822E-06 21 FREQUENCY
SQL> alter session set events '10053 trace name context forever ,level 2';
Session altered.
SQL> select * from irs where owner='SYSMAN';
1291 rows selected.
SQL> alter session set events '10053 trace name context off';
10053内容截取
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: IRS Alias: IRS
#Rows: 50089 #Blks: 3423 AvgRowLen: 102.00
Index Stats::
Index: IRS_ID_PK Col#: 1
LVLS: 1 #LB: 104 #DK: 50089 LB/K: 1.00 DB/K: 1.00 CLUF: 3361.00
Index: IRS_OWNER_IDX Col#: 2
LVLS: 2 #LB: 570 #DK: 21 LB/K: 27.00 DB/K: 280.00 CLUF: 5890.00
***************************************
SINGLE TABLE ACCESS PATH
Column (#2): OWNER(VARCHAR2)
AvgLen: 5.00 NDV: 21 Nulls: 0 Density: 9.9822e-06
这里看到OWNER列的Density: 9.9822e-06
Histogram: Freq #Bkts: 21 UncompBkts: 50089 EndPtVals: 21
Table: IRS Alias: IRS
Card: Original: 50089 Rounded: 1291 Computed: 1291.00 Non Adjusted: 1291.00 --这里可以看到CBO根据列的histogram信息统计出Card集的相关信息,
SQL> select 1291/50089 from dual;
1291/50089
----------
.025774122
这样可以准确算出effective index selectivity为0.025774122
Access Path: TableScan --全表扫描的代价为750
Cost: 754.17 Resp: 754.17 Degree: 0
Cost_io: 750.00 Cost_cpu: 35706109
Resp_io: 750.00 Resp_cpu: 35706109
Access Path: index (AllEqRange)
Index: IRS_OWNER_IDX
resc_io: 169.00 resc_cpu: 1991033
ix_sel: 0.025774 ix_sel_with_filters: 0.025774
Cost: 169.23 Resp: 169.23 Degree: 1
Best:: AccessPath: IndexRange Index: IRS_OWNER_IDX
Cost: 169.23 Degree: 1 Resp: 169.23 Card: 1291.00 Bytes: 0
根据索引扫描成本计算公式
cost = (cost(INDEX RANGE SCAN)+cost(TABLE ACCESS BY INDEX ROWID)) * optimizer_index_cost_adj%
= blevel +ceiling(leaf_blocks * effective index selectivity) + -- cost(INDEX RANGE SCAN)
ceiling(clustering_factor * effective table selectivity) --cost(TABLE ACCESS BY INDEX ROWID)
= 2 + ceil(570*0.025774) + ceil(5890*0.025774)
= 2 + 15 (17) -- cost(INDEX RANGE SCAN)
+ 152 --cost(TABLE ACCESS BY INDEX ROWID)
= 169
----------------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 169 | |
| 1 | TABLE ACCESS BY INDEX ROWID | IRS | 1291 | 129K | 169 | 00:00:03 |
| 2 | INDEX RANGE SCAN | IRS_OWNER_IDX| 1291 | | 17 | 00:00:01 |
----------------------------------------------------+-----------------------------------+
解释一下整个sql执行过程：
1 首先根据谓词 table_name='IRS'做INDEX RANGE SCAN，找到1291个Index Entrys,整个这个过程的代价为17.
2 Oracle根据Index Entrys中Rowid扫描原表中的blocks,获取到1291条记录. 整个TABLE ACCESS BY INDEX ROWID的代价为169-17=152.
3 TABLE ACCESS BY INDEX ROWID过程中,表的blocks被Server process读入到SGA的DB Cache中,读入的数据量近似的等于129K.(Oracle读取一行记录会读整个block;命中率等因素)
SQL> select AVG_ROW_LEN,BLOCKS from dba_tables
2 where table_name='IRS';
AVG_ROW_LEN BLOCKS
----------- ----------
102 3423
SQL> select 102*1291/1024 from dual;
102*1291/1024
-------------
128.595703
关于索引的索引聚簇因子CLUF算法下次再作探讨.
索引扫描成本还和另一个参数有很大关系.先前eygle老大已经发表过专门的帖子.
http://www.eygle.com/sql/OPTIMIZER_INDEX_COST_ADJ.htm
我这里拿来主义.
SQL> alter session set optimizer_index_cost_adj=40;
Session altered.
SQL> set autotrace traceonly
SQL> set linesize 150
SQL> select * from irs where owner='SYSMAN';
1291 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4071038474
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1291 | 128K| 68 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| IRS | 1291 | 128K| 68 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IRS_OWNER_IDX | 1291 | | 7 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
可以看到 Cost=68=ceil(169*0.4)
附:
在没有列的histogram情况下的成本计算,我们看到偏差很大.CBO无法准确获取到Card集的信息，所以Oracle只能估算.
10053内容截取
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: IRS Alias: IRS
#Rows: 50089 #Blks: 3423 AvgRowLen: 102.00
Index Stats::
Index: IRS_ID_PK Col#: 1
LVLS: 1 #LB: 104 #DK: 50089 LB/K: 1.00 DB/K: 1.00 CLUF: 3361.00
Index: IRS_OWNER_IDX Col#: 2
LVLS: 2 #LB: 570 (索引LEAF_BLOCKS个数) #DK: 21 (索引distinct key个数) LB/K: 27.00 DB/K: 280.00 CLUF: 5890.00 (索引聚簇因子,稍后会有详细介绍)
***************************************
SINGLE TABLE ACCESS PATH
Column (#2): OWNER(VARCHAR2) NO STATISTICS (using defaults)
AvgLen: 30.00 NDV: 1565 Nulls: 0 Density: 6.3886e-04
Table: IRS Alias: IRS
Card: Original: 50089 Rounded: 501 Computed: 500.89 Non Adjusted: 500.89
Access Path: TableScan
Cost: 754.15 Resp: 754.15 Degree: 0
Cost_io: 750.00 Cost_cpu: 35516509
Resp_io: 750.00 Resp_cpu: 35516509
Access Path: index (AllEqGuess) -- 我们注意这里索引的访问路径为index (AllEqGuess),我猜想是由于没有OWNER列的Histogram,Oracle无法计算OWNER列的selectivity造成的
Index: IRS_OWNER_IDX
resc_io: 53.00 resc_cpu: 500046
ix_sel: 0.004 ix_sel_with_filters: 0.004
Cost: 53.06 Resp: 53.06 Degree: 1
Best:: AccessPath: IndexRange Index: IRS_OWNER_IDX
Cost: 53.06 Degree: 1 Resp: 53.06 Card: 500.89 Bytes: 0
根据10053 Oracle评估出:
cost(TableScan)=750
cost(index)=53.06
所以选择索引扫描为执行计划.
----------------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 53 | |
| 1 | TABLE ACCESS BY INDEX ROWID | IRS | 501 | 50K | 53 | 00:00:01 |
| 2 | INDEX RANGE SCAN | IRS_OWNER_IDX| 200 | | 29 | 00:00:01 |
----------------------------------------------------+-----------------------------------+
根据索引扫描成本计算公式
cost = (cost(INDEX RANGE SCAN)+cost(TABLE ACCESS BY INDEX ROWID)) * optimizer_index_cost_adj%
= blevel +ceiling(leaf_blocks * effective index selectivity) + -- cost(INDEX RANGE SCAN)
ceiling(clustering_factor * effective table selectivity) --cost(TABLE ACCESS BY INDEX ROWID)
= 2 + ceil(570*0.004) + ceil(5890*0.004)
= 2 + 3 + 24
= 29
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/76065/viewspace-846417/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/76065/viewspace-846417/
3623
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
