玉面飞龙的BLOG

When are partition statistics beneficial?

September 22nd, 2008 | Categories: Boring | Tags: Oracle, Partition, Statistics

CBO 在分析如下的SQL语句的时候，使用的是global table statistics还是partition level statistics哪？


SQL> select  *

  2  from binzhang

  3  where user_id=1234 and some_date >= sysdate-7 and

  4        action in ( 5, 0) and (partition_key >= 8 AND partition_key<= 9);

And we said,

partition level statistics are used when the query plan would have explicit partition
START and STOP keys — and the START=STOP.

当然啦，_optim_peek_user_binds是个例外。

观察10053 事件得到结论。

先看partition_key >= 8 AND partition_key<= 9的SQL,


SQL> alter session set events '10053 trace name context forever,level 12';

Session altered.
SQL> select  *

  2  from binzhang

  3  where user_id=1234 and some_date >= sysdate-7 and

  4        action in ( 5, 0) and (partition_key >= 8 AND partition_key<= 9);

----------------------------------------------------------+-----------------------------------+---------------+ | Id | Operation | Name | Rows | Bytes | Cost | Time | Pstart| Pstop | ----------------------------------------------------------+-----------------------------------+---------------+ | 0 | SELECT STATEMENT | | | | 2 | | | | | 1 | PARTITION RANGE ITERATOR | | 1 | 126 | 2 | 00:00:01 | 8 | 9 | | 2 | TABLE ACCESS BY LOCAL INDEX ROWID | BINZHANG | 1 | 126 | 2 | 00:00:01 | 8 | 9 | | 3 | INDEX RANGE SCAN | BINZHANG_IDX| 1 | | 2 | 00:00:01 | 8 | 9 | ----------------------------------------------------------+-----------------------------------+--------------
注意下面的注释: (Using composite stats)和(making adjustments for partition skews)
*************************************** BASE STATISTICAL INFORMATION *********************** Table Stats:: Table: BINZHANG Alias: BINZHANG (Using composite stats) (making adjustments for partition skews) ORIGINAL VALUES:: #Rows: 1000000 #Blks: 10000 AvgRowLen: 0.00 PARTITIONS:: PRUNED: 2 ANALYZED: 2 UNANALYZED: 0 #Rows: 1000000 #Blks: 262 AvgRowLen: 0.00 Index Stats:: Index: BINZHANG_IDX Col#: 1 2 4 6 USING COMPOSITE STATS LVLS: 1 #LB: 0 #DK: 0 LB/K: 0.00 DB/K: 0.00 CLUF: 0.00 *************************************** SINGLE TABLE ACCESS PATH Column (#1): USER_ID(NUMBER) AvgLen: 22.00 NDV: 0 Nulls: 0 Density: 0.0000e+00 Min: 395459 Max: 806372689 Column (#4): FIRE_DATE(DATE) AvgLen: 7.00 NDV: 0 Nulls: 0 Density: 0.0000e+00 Min: 2454680 Max: 2454680 Using prorated density: 5.0000e-07 of col #4 as selectivity of out-of-range value pred Column (#5): RULE_ACTION(NUMBER) AvgLen: 22.00 NDV: 0 Nulls: 0 Density: 0.0000e+00 Min: -1 Max: 100 Column (#6): PARTITION_KEY(NUMBER) AvgLen: 22.00 NDV: 0 Nulls: 0 Density: 0.0000e+00 Min: 8 Max: 8 Table: BINZHANG Alias: BINZHANG Card: Original: 1000000 Rounded: 1 Computed: 0.00 Non Adjusted: 0.00 Access Path: TableScan Cost: 60.25 Resp: 60.25 Degree: 0 Cost_io: 59.00 Cost_cpu: 9726160 Resp_io: 59.00 Resp_cpu: 9726160 kkofmx: index filter:"BINZHANG"."FIRE_DATE">=SYSDATE@!-7 AND ("BINZHANG"."RULE_ACTION"=35 OR "BINZHANG"."RULE_ACTION"=40) AND "BINZHANG"."PARTITION_KEY">=8 AND "BINZHANG"."PARTITION_KEY"<=9 kkofmx: index filter:"BINZHANG"."PARTITION_KEY">=8 AND “BINZHANG”.”PARTITION_KEY”<=9 kkofmx: index filter:"BINZHANG"."PARTITION_KEY"<=9 Using prorated density: 5.0000e-07 of col #4 as selectivity of out-of-range value pred Using prorated density: 5.0000e-07 of col #4 as selectivity of out-of-range value pred Access Path: index (skip-scan) SS sel: 5.0000e-13 ANDV (#skips): 1 SS io: 0.00 vs. index scan io: 0.00 Skip Scan rejected Access Path: index (RangeScan) Index: BINZHANG_IDX resc_io: 2.00 resc_cpu: 14443 ix_sel: 1.0000e-06 ix_sel_with_filters: 5.0000e-13 Cost: 2.00 Resp: 2.00 Degree: 1 Best:: AccessPath: IndexRange Index: BINZHANG_IDX Cost: 2.00 Degree: 1 Resp: 2.00 Card: 0.00 Bytes: 0 *************************************** OPTIMIZER STATISTICS AND COMPUTATIONS *************************************** GENERAL PLANS ***************************************
虽然使用table level statistics, 10053输出中也表示CBO会基于某个分区间数据分布对统计信息作些调整。(making adjustments for partition skews)

可见CBO在确定执行计划的时候，会可能的寻找一些可利用的信息进行执行计划成本分析，包括global table statistics和partition skews的情况。

下面看PSTART=PSTOP=partition_number

SQL> alter session set events '10053 trace name context forever,level 12'; Session altered.


SQL> select  item_id, process, priority_id, site_id,

  2  TO_CHAR(fire_date, 'YYYY-MM-DD HH24:MI:SS'), ad_id, rule_id

  3  from binzhang

  4  where user_id =342234 and fire_date >= sysdate-7 and

  5    rule_action in (35, 40) and (partition_key  = 8);

----------------------------------------------------------+-----------------------------------+---------------+ | Id | Operation | Name | Rows | Bytes | Cost | Time | Pstart| Pstop | ----------------------------------------------------------+-----------------------------------+---------------+ | 0 | SELECT STATEMENT | | | | 1 | | | | | 1 | PARTITION RANGE SINGLE | | 1 | 126 | 1 | 00:00:01 | 8 | 8 | | 2 | TABLE ACCESS BY LOCAL INDEX ROWID | BINZHANG | 1 | 126 | 1 | 00:00:01 | 8 | 8 | | 3 | INDEX RANGE SCAN | BINZHANG_IDX| 1 | | 1 | 00:00:01 | 8 | 8 | ----------------------------------------------------------+-----------------------------------+---------------+
如下，明确表示CBO是基于 Partition 7来决定计算成本的。
*************************************** BASE STATISTICAL INFORMATION *********************** Table Stats:: Table: BINZHANG Alias: BINZHANG Partition [7] #Rows: 0 #Blks: 261 AvgRowLen: 0.00 #Rows: 0 #Blks: 261 AvgRowLen: 0.00 Index Stats:: Index: BINZHANG_IDX Col#: 1 2 4 6 PARTITION [7] LVLS: 1 #LB: 0 #DK: 0 LB/K: 0.00 DB/K: 0.00 CLUF: 0.00 LVLS: 1 #LB: 0 #DK: 0 LB/K: 0.00 DB/K: 0.00 CLUF: 0.00 *************************************** SINGLE TABLE ACCESS PATH Column (#1): USER_ID(NUMBER) Part#: 7 AvgLen: 22.00 NDV: 0 Nulls: 0 Density: 0.0000e+00 Min: 0 Max: 0 Column (#1): USER_ID(NUMBER) AvgLen: 22.00 NDV: 0 Nulls: 0 Density: 0.0000e+00 Min: 0 Max: 0 Column (#4): FIRE_DATE(DATE) Part#: 7 AvgLen: 7.00 NDV: 0 Nulls: 0 Density: 0.0000e+00 Min: 0 Max: 0 Column (#4): FIRE_DATE(DATE) AvgLen: 7.00 NDV: 0 Nulls: 0 Density: 0.0000e+00 Min: 0 Max: 0 Column (#5): RULE_ACTION(NUMBER) Part#: 7 AvgLen: 22.00 NDV: 0 Nulls: 0 Density: 0.0000e+00 Min: 0 Max: 0 Column (#5): RULE_ACTION(NUMBER) AvgLen: 22.00 NDV: 0 Nulls: 0 Density: 0.0000e+00 Min: 0 Max: 0 Column (#6): PARTITION_KEY(NUMBER) Part#: 7 AvgLen: 22.00 NDV: 0 Nulls: 0 Density: 0.0000e+00 Min: 0 Max: 0 Column (#6): PARTITION_KEY(NUMBER) AvgLen: 22.00 NDV: 0 Nulls: 0 Density: 0.0000e+00 Min: 0 Max: 0 Table: BINZHANG Alias: BINZHANG Card: Original: 0 Rounded: 1 Computed: 0.00 Non Adjusted: 0.00 Access Path: TableScan Cost: 59.24 Resp: 59.24 Degree: 0 Cost_io: 59.00 Cost_cpu: 1858696 Resp_io: 59.00 Resp_cpu: 1858696 kkofmx: index filter:"BINZHANG"."FIRE_DATE">=SYSDATE@!-7 AND ("BINZHANG"."RULE_ACTION"=35 OR "BINZHANG"."RULE_ACTION"=40) AND "BINZHANG"."PARTITION_KEY"=8 kkofmx: index filter:"BINZHANG"."PARTITION_KEY"=8 Access Path: index (skip-scan) SS sel: 0.0000e+00 ANDV (#skips): 1 SS io: 0.00 vs. index scan io: 0.00 Skip Scan rejected Access Path: index (RangeScan) Index: BINZHANG_IDX resc_io: 1.00 resc_cpu: 7321 ix_sel: 0.0000e+00 ix_sel_with_filters: 0.0000e+00 Cost: 1.00 Resp: 1.00 Degree: 1 Best:: AccessPath: IndexRange Index: BINZHANG_IDX Cost: 1.00 Degree: 1 Resp: 1.00 Card: 0.00 Bytes: 0 *************************************** OPTIMIZER STATISTICS AND COMPUTATIONS *************************************** GENERAL PLANS ***************************************
这就说明了，当Oracle能够知道要访问某一个partition (PSTART=PSTOP)的时候，要确保这个partition上的统计信息精确。

当Oracle能够知道要访问某些partition的(PSTART != PSTOP)时候,不但table level statistics要精确，partition level的skews相关的统计信息也不能遗漏，否则执行计划可能很糟糕。

当Oracle不能够知道要访问哪些partition的时候(PSTART=KEY1 PSTOP=KEY2)，只要依赖table level statistics就可以了。

当Oracle无法利用partition elimination的时候，CBO 仅依赖table level statistics.

当然使用bind variable的要注意bind peeking.

如上测试是在10.2.0.3。可见下面的说法有点过时了。9i我还没有10053过，不知道是否有(making adjustments for partition skews)这个异像。

partition level statistics are used when the query plan would have explicit partition
START and STOP keys — and the START=STOP.

如果DBA当市长 Cost of unlink empty index block

木匠
September 23rd, 2008 at 01:44

Quote | #1

学习了, 有一些 Optimizer 的问题, 以后共同探讨.

你还在继续研究 Oracle 11g TEXT 吗?
yumianfeilong
September 23rd, 2008 at 08:08

Quote | #2

没有再看text相关的文档了，公司也不用这个oracle技术

When are partition statistics beneficial?

相关文章:

广而告之

Categories

Recent Posts

Recent Comments

Blogroll

Alumni&Fellows

经济人

Daily Click

Favorites

DouBan

广而告之

DouBan

广而告之

玉面飞龙的BLOG

When are partition statistics beneficial?

相关文章:

广而告之

Categories

Recent Posts

Tag Cloud

Recent Comments

Blogroll

Alumni&Fellows

经济人

Daily Click

Favorites

DouBan 广而告之

DouBan

广而告之

DouBan

广而告之