直方图

最新推荐文章于 2020-12-20 02:51:36 发布

cla37092

最新推荐文章于 2020-12-20 02:51:36 发布

阅读量95

点赞数

文章标签：数据库

DBMS_STATS 包对段表的分析有三个层次：

表自身的分析

这包括表中的行数，数据块数，行长等信息。

列的分析

包括列值的重复数，列上的空值，数据在列上的分布情况。

索引的分析

包括索引叶块的数量，索引的深度，索引的聚合因子等。

下面讨论的直方图，单指的是第二项的最后一种，数据在列上的分布情况。

当Oracle 做直方图分析时，会将要分析的列上的数据分成很多数量相同的部分，每一

部分称作一个bucke，这样CBO 就可以非常容易的知道这个列上的数值的分布情况，

这种数据的分布将作为一个非常重要的因素纳入到执行成本的计算当中。

对于数据分布非常倾斜的表，做直方图分析是非常有用的。

直方图对于CBO 有时候非常重要，特别是对于有字段数据非常倾斜的表，做直方图分

析尤为重要，比如下面的例子：

Last login: Fri Aug 31 22:09:41 2012 from 10.8.0.249

[root@localhost ~]# su - oracle

[oracle@localhost ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on 星期六 9月 1 08:34:11 2012

SQL> conn change/change

Connected.

SQL> create table change(a number,b number);

Table created.

SQL> begin

2 for i in 1..10000 loop

3 insert into change values(i,i);

4 end loop;

5 commit;

6 end;

7 /

PL/SQL procedure successfully completed.

SQL>

SQL> update change set b=5 where b between 6 and 9995;

9990 rows updated.

SQL> commit;

Commit complete.

SQL>

SQL> select b,count(*) from change group by b;

B COUNT(*)

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

1 1

9997 1

9999 1

10000 1

2 1

5 9991

4 1

9998 1

3 1

9996 1

10 rows selected.

这样表中有10000条数据，对于b列，有10个不同的值，等于5的值有9991个，此时无论是选择b=1或者b=5的数据都必须要进行全表扫描，因为表上没有索引，也即是没有别的访问路径。

SQL> set autotrace traceonly

SQL>

SQL> select * from change where b=1;

Execution Plan

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

Plan hash value: 3349315267

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

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

| 0 | SELECT STATEMENT | | 1 | 26 | 7 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| CHANGE | 1 | 26 | 7 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - filter("B"=1)

Note

-----

- dynamic sampling used for this statement (level=2)

Statistics

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

28 recursive calls

0 db block gets

50 consistent gets

0 physical reads

0 redo size

593 bytes sent via SQL*Net to client

524 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

SQL> set autotrace traceonly

SQL> create index i_change_b on change(b);

Index created.

SQL> exec dbms_stats.gather_table_stats(user,'CHANGE',cascade=>true);

PL/SQL procedure successfully completed.

SQL> set autotrace off

SQL>

SQL> col TABLE_NAME for a20

SQL> col COLUMN_NAME for a20

SQL> select table_Name,column_name,endpoint_number,endpoint_value from user_histograms where table_name='CHANGE';

TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE

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

CHANGE B 1 1

CHANGE B 2 2

CHANGE B 3 3

CHANGE B 4 4

CHANGE B 9995 5

CHANGE B 9996 9996

CHANGE B 9997 9997

CHANGE B 9998 9998

CHANGE B 9999 9999

CHANGE B 10000 10000

CHANGE A 0 1

TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE

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

CHANGE A 1 10000

12 rows selected.

直方图中的ENDPOINT_VALUE表示列值，ENDPOINT_NUMBER表示累积的行数。比如ENDPOINT_VALUE=2，ENDPOINT_NUMBER=2，因为ENDPOINT_NUMBER是个累积值，

实际上2的 ENDPOINT_NUMBER应该是2减去上一个值的ENDPOINT_NUMBER，也即是2-1=1。同理，5的 ENDPOINT_NUMBER=9995-4=9991。

SQL> set autotrace traceonly explain;

SQL> select * from change where b=1;

Execution Plan

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

Plan hash value: 1994370661

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

----------

Time |

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

----------

| 0 | SELECT STATEMENT | | 1 | 7 | 2 (0)|

00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| CHANGE | 1 | 7 | 2 (0)|

00:00:01 |

|* 2 | INDEX RANGE SCAN | I_CHANGE_B | 1 | | 1 (0)|

00:00:01 |

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

----------

Predicate Information (identified by operation id):

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

2 - access("B"=1)

SQL> set linesize 1000

SQL> /

Execution Plan

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

Plan hash value: 1994370661

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

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

| 0 | SELECT STATEMENT | | 1 | 7 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| CHANGE | 1 | 7 | 2 (0)| 00:00:01 |

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

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

Predicate Information (identified by operation id):

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

2 - access("B"=1)

从这里我们可以看到,CBO 根据直方图的信息知道id=1 的记录只有一行，所以它选择了索

引，这个选择非常正确:

SQL>

SQL> select * from change where b=5;

Execution Plan

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

Plan hash value: 3349315267

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

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

| 0 | SELECT STATEMENT | | 9991 | 69937 | 7 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| CHANGE | 9991 | 69937 | 7 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - filter("B"=5)

从这里我们可以看到,CBO 根据直方图的信息知道id=5 的记录有9991行，所以它选择了全表扫描，这个选择非常正确。通过对列做直方图分析，CBO 可以非常完美的制定出一个最有的执行计

划来。

下面让我们将直方图信息删除，但保留表和索引的分析信息：

SQL> exec dbms_stats.delete_column_stats(user,'change','b');

PL/SQL procedure successfully completed.

SQL> select table_Name,column_name,endpoint_number,endpoint_value from user_histograms where table_name='CHANGE';

TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE

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

CHANGE A 0 1

CHANGE A 1 10000-------------------B列上的信息已经被删除

SQL> select num_rows,avg_row_len,blocks,last_analyzed from user_tables where table_name='CHANGE';

NUM_ROWS AVG_ROW_LEN BLOCKS LAST_ANALYZE

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

10000 7 20 01-9月 -12

SQL> select blevel,leaf_blocks,distinct_keys,last_analyzed from user_indexes where table_name='CHANGE';

BLEVEL LEAF_BLOCKS DISTINCT_KEYS LAST_ANALYZE

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

1 20 10 01-9月 -12

索引和表的信息仍然存在，并且索引中甚至可以找到重复的数值只有10 个，但是CBO 却无

法得到这两个重复的数值的分布情况，所以依然没有办法选出一个正确的执行计划：

SQL> set autotrace traceonly

SQL> select * from change where b=1;

Execution Plan

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

Plan hash value: 1994370661

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

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

| 0 | SELECT STATEMENT | | 100 | 700 | 3 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| CHANGE | 100 | 700 | 3 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | I_CHANGE_B | 40 | | 2 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("B"=1)

Statistics

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

0 recursive calls

0 db block gets

4 consistent gets

0 physical reads

0 redo size

597 bytes sent via SQL*Net to client

524 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

SQL>

SQL> select * from change where b=5;

9991 rows selected.

Execution Plan

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

Plan hash value: 1994370661

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

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

| 0 | SELECT STATEMENT | | 100 | 700 | 3 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| CHANGE | 100 | 700 | 3 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | I_CHANGE_B | 40 | | 2 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("B"=5)

Statistics

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

0 recursive calls

0 db block gets

689 consistent gets

0 physical reads

0 redo size

212190 bytes sent via SQL*Net to client

7850 bytes received via SQL*Net from client

668 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

9991 rows processed

CBO 在b=1 时，估算出返回的结果是100行，比较全部表的记录数10000 行（这个

信息可以从表的分析数据中得到user_tables），CBO 认为选择索引是合适的

同样，在ib=5 时，CBO 依然估算出的返回值是100 条，在这种情况下，CBO 没有

办法得到数据的具体分布情况，所以作出的错误的判断。

SQL> analyze table change compute statistics for columns b size 8;

Table analyzed.

SQL> set autotrace off

SQL> select table_Name,column_name,endpoint_number,endpoint_value from user_histograms where table_name='CHANGE';

TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE

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

CHANGE B 7 5

CHANGE B 8 10000

CHANGE B 0 1

CHANGE A 0 1

CHANGE A 1 10000

SQL> analyze table change compute statistics for columns b size 7;

Table analyzed.

SQL> select table_Name,column_name,endpoint_number,endpoint_value from user_histograms where table_name='CHANGE';

TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE

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

CHANGE B 0 1

CHANGE B 6 5

CHANGE B 7 10000

CHANGE A 0 1

CHANGE A 1 10000

ENDPOINT_NUMBER是实际的bucket编号，ENDPOINT_VALUE是根据列值决定的该bucket的endpoint值。上面的输出中，bucket 0存放着b列的低值，为了节省空间没有显示出1-6号的bucket。但是我们能够理解，bucket[1-7]里存放着的endpoint=5，而 bucket8里存放endpoint=10000。因此，实际上bucket0里包含了1-5之间的所有值，而bucket8里包含了5-10000之间的所有值，在本例中也就是9996-10000这5个数值。

综上所述，假如数据是均衡的，没有必要使用直方图。如果使用唯一值数量来创建直方图，Oracle为每个值创建一个bucket；但是假如实际的生产系统中，不能够为每一个唯一值分配一个bucket时，Oracle采用合适的算法尽可能将值平均分布到每个bucket中，剩余的值放入到最后的bucket。

对于数据分布均匀的列，直方图没意义，对应列中数据分布比较倾斜的列（不均匀），直方图就非常有用。CBO就可以估计大概的数据分布，计算查询的选择率和基数也更精确。

最后提示一点：如果要删除直方图统计信息，那么只需指定size 1即可：

SQL> exec dbms_stats.gather_table_stats(user,'CHANGE',cascade =>false,method_opt => 'for all columns size 1');

PL/SQL procedure successfully completed.

SQL> select table_Name,column_name,endpoint_number,endpoint_value from user_histograms where table_name='CHANGE';

TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE

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

CHANGE A 0 1

CHANGE B 0 1

CHANGE A 1 10000

CHANGE B 1 10000