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

最新推荐文章于 2024-09-14 19:55:29 发布

cizhe8562

最新推荐文章于 2024-09-14 19:55:29 发布

阅读量114

点赞数

文章标签：数据库

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_VALUE和HIGH_VALUE要特别说明下，在user_tab_columns里面的这两个值是采用RAW数据类型来存储的。我们想要得到number类型的值需要调用dbms_stats中的过程convert_raw_value。下面是个写好函数raw_to_number。

function raw_to_number (in_raw raw)

return number

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

上面这条SQL，CBO选择的通过索引范围扫描来执行。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%)