ITPub博客

首页 > 数据库 > Oracle > cbo心得(选择率,基数,直方图)(三)

cbo心得(选择率,基数,直方图)(三)

原创 Oracle 作者:sunwgneuqsoft 时间:2008-03-05 11:06:50 0 删除 编辑

1, 存在表和索引上的统计信息,没有直方图

这是最一般的情况,定期对表和索引进行分析,但是并不分析直方图信息。大部分时候,这种统计信息收集策略都会很好的工作。我们这的大部分指得是,列上数据分布很平均的情况下。为了更好的演示,我需要创建一个存在倾斜列的表[@more@]

SQL> drop table sunwg purge;

表已删除。

SQL> create table sunwg (id number);

表已创建。

SQL> create index ind_sunwg on sunwg(id);

索引已创建。

SQL> insert into sunwg select 1 from dba_objects where rownum<2001;

已创建2000行。

SQL> insert into sunwg select 1+ rownum from dba_objects where rownum<2001;

已创建2000行。

SQL> commit;

提交完成。

SQL> analyze table sunwg compute statistics for table for all indexes for columns id size 1;

表已分析。

这样我们就有了一个有倾斜列的表SUNWG,在表中id = 1的记录有2000条,占了全部记录的50%。查询下表和索引的统计信息,看看我们都告诉了CBO什么信息。

SQL> select NUM_ROWS,

2 BLOCKS,

3 EMPTY_BLOCKS,

4 AVG_SPACE,

5 CHAIN_CNT,

6 AVG_ROW_LEN

7 from user_tables

8 where table_name = 'SUNWG';

NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN

---------- ---------- ------------ ---------- ---------- -----------

4000 13 3 4687 0 6

SQL> select BLEVEL,

2 LEAF_BLOCKS,

3 DISTINCT_KEYS,

4 AVG_LEAF_BLOCKS_PER_KEY,

5 AVG_DATA_BLOCKS_PER_KEY,

6 CLUSTERING_FACTOR

7 from user_indexes

8 where index_name = 'IND_SUNWG';

BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY

---------- ----------- ------------- -----------------------

AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR

----------------------- -----------------

1 8 2001 1

1 7

SQL> select NUM_DISTINCT,

2 LOW_VALUE,

3 HIGH_VALUE,

4 DENSITY,

5 NUM_NULLS,

6 NUM_BUCKETS,

7 HISTOGRAM

8 from user_tab_columns

9 where table_name ='SUNWG'

10 and column_name = 'ID';

NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY NUM_NULLS NUM_BUCKETS HISTOGRAM

----------- --------- --------- ------ -------- ----------- --------

2001 C102 C21502 .00049975 0 1 NONE

关于LOW_VALUEHIGH_VALUE要特别说明下,在user_tab_columns里面的这两个值是采用RAW数据类型来存储的。我们想要得到number类型的值需要调用dbms_stats中的过程convert_raw_value。下面是个写好函数raw_to_number

function raw_to_number (in_raw raw)

return number

as

ft FLOAT(126);

begin

dbms_stats.convert_raw_value(in_raw,ft);

return ft;

end;

SQL> select * from sunwg where id = 50;

执行计划

----------------------------------------------------------

Plan hash value: 3109917279

---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

---------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 2 | 6 | 1 (0)| 00:00:01 |

|* 1 | INDEX RANGE SCAN| IND_SUNWG | 2 | 6 | 1 (0)| 00:00:01 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - access("ID"=50)

统计信息

----------------------------------------------------------

0 recursive calls

0 db block gets

3 consistent gets

0 physical reads

0 redo size

402 bytes sent via SQL*Net to client

385 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

上面这条SQLCBO选择的通过索引范围扫描来执行。CBO为什么会做出这样的决定呢?我们看一下计划里面有一列是Rows,这列代表着ORACLE估计的结果集合的大小。前面这个例子中ORACLE估计ID = 50的结果集大概是2条。实际上呢,ID = 50的记录只有一条。先不管这点差异,在总量是4000条记录的表中查询几条记录用索引扫描会更加的高效。因此,CBO选择了索引扫描而不采用全表扫描。

接下来在说这个Rows = 2是怎么产生的。在列的统计信息中一个字段是NUM_DISTINCT,它说明该列不同值的个数。由于不存在直方图信息,ORACLE无法知道列上数据的真实分布情况,所以ORACLE假设数据在列ID上是平均分配的。所以ID = 50的记录数就应该是:

4000 * 1/2001 = 1.99

SQL> select * from sunwg where id = 1;

已选择2000行。

执行计划

----------------------------------------------------------

Plan hash value: 3109917279

---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

---------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 2 | 6 | 1 (0)| 00:00:01 |

|* 1 | INDEX RANGE SCAN| IND_SUNWG | 2 | 6 | 1 (0)| 00:00:01 |

---------------------------------------------------------------------------Predicate Information (identified by operation id):

---------------------------------------------------

1 - access("ID"=1)

统计信息

----------------------------------------------------------

1 recursive calls

0 db block gets

138 consistent gets

0 physical reads

0 redo size

27319 bytes sent via SQL*Net to client

1848 bytes received via SQL*Net from client

135 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

2000 rows processed

上面的这个例子中ORACLE关于结果集的估计是差得比较多了,实际上ID = 1的记录有2000条,可ORACLE的估计值只有2条。差距这么大的就可能导致CBO选择错误的执行计划,用低效的索引扫描来代替更加高效的全表扫描。直方图信息就是用来解决这种问题的,在数据倾斜的情况下给ORACLE更多的信息,使ORACLE可以意识到倾斜的存在。

我们上边仅仅对ID = 1这样的最简单的情况进行了测试,平时常用的谓词还有>,>=,<等等,下表列出了常用的一些谓词的情况

Where 条件

Oracle估计记录数

表中实际记录数

Oracle估算公式(猜想)

ID = 1

2

2000

4000 * 1/2001

ID > 1

3998

2000

4000 * 2001 – 1/2001

ID >= 1

4000

4000

4000*2001 – 1/2001+2

ID = 110

2

0

1<= X<= 4000 * 1/2001

ID + 1 > 1

200

4000

4000* 5%

ID + 1 >= 1

200

4000

4000* 5%

ID + 1 > 1 AND ID + 1 > 1

200

4000

4000* 5%

ID > 1 AND ID < 50

96

48

4000 *( 5%* 5%) - 2 - 2

ID >= 1 AND ID <= 50

100

2049

4000 *( 5%* 5%)

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8394333/viewspace-1000432/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
  • 博文量
    56
  • 访问量
    758689