ITPub博客

首页 > 数据库 > Oracle > dbms_stats.gather_table_stats中 granularity的实验

dbms_stats.gather_table_stats中 granularity的实验

原创 Oracle 作者:buptdream 时间:2014-08-06 15:56:55 0 删除 编辑
在OLAP 或者数据仓库环境中,经常有这样的事情,新创建一个分区,将批量的数据(通
常是很大量的数据)加载到分区中,然后对分区作分析,然后做报表或者数据挖掘;在理想
的情况下,对表的全局,分区都做分析,这样才能得到最充足的数据;但是通常来讲这样的
表都非常大,如果每增加一个分区都需要做一次全局分析的话,势必消耗极大的系统资源;
但是如果只对新加入的分区进行分析而不做全局分析,Oracle 在全局范围内的信息就会不
准确,比如这个表有多少条数据?这个信息就是不准确的,很可能导致CBO 产生错误的执行
计划,下面的例子将说明这个问题:
[oracle@localhost ~]$ clear

[oracle@localhost ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on 星期六 9月 1 10:17:42 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

SQL> conn change/change
Connected.
SQL>  create table t(id int) partition by range(id)
  2   (
  3   partition p1 values less than(5),
  4   partition p2 values less than(10),
  5   partition p3 values less than(15)
  6   );

Table created.

SQL> insert into t values(1);

1 row created.

SQL> insert into t values(6);

1 row created.

SQL> insert into t values(11);

1 row created.

SQL> commit;

Commit complete.

SQL> create index ind_t on t(id) local;

Index created.

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

PL/SQL procedure successfully completed.
先创建一个分区表T,他包含3 个分区p1,p2 和P3,每个分区插入一条记录,在
表上创建一个本地索引,然后对表进行分析:


参数在默认情况下,DBMS_STATS 包会对表级(全局),分区
级(对应参数partition)都会进行分析:
SQL> select num_rows,avg_row_len,last_analyzed from user_tables where table_name='T';

  NUM_ROWS AVG_ROW_LEN LAST_ANALYZE
---------- ----------- ------------
         3           3 01-9月 -12

SQL> select partition_name,num_rows,avg_row_len,last_analyzed from user_tab_partitions where
  2  table_name='T';

PARTITION_NAME
--------------------------------------------------------------------------------
  NUM_ROWS AVG_ROW_LEN LAST_ANALYZE
---------- ----------- ------------
P1
         1           3 01-9月 -12

P2
         1           3 01-9月 -12

P3
         1           3 01-9月 -12


SQL> col PARTITION_NAME for a20
SQL> /

PARTITION_NAME         NUM_ROWS AVG_ROW_LEN LAST_ANALYZE
-------------------- ---------- ----------- ------------
P1                            1           3 01-9月 -12
P2                            1           3 01-9月 -12
P3                            1           3 01-9月 -12

CASCADE 设置为TRUE,相应的索引的全局和分区级别也都会被分析:
SQL> select BLevel,leaf_blocks,last_analyzed from user_indexes where index_name='IND_T';

    BLEVEL LEAF_BLOCKS LAST_ANALYZE
---------- ----------- ------------
         0           3 01-9月 -12



SQL>  select partition_name,BLevel,leaf_blocks,last_analyzed from user_ind_partitions where index_name='IND_T';

PARTITION_NAME           BLEVEL LEAF_BLOCKS LAST_ANALYZE
-------------------- ---------- ----------- ------------
P1                            0           1 01-9月 -12
P2                            0           1 01-9月 -12
P3                            0           1 01-9月 -12

向表中加入一个新的分区,并给这个新的分区载入10000 条数据,并且让这个分
区的数据分布严重倾斜:
SQL> alter table t add partition pm values less than(maxvalue);

Table altered.

SQL>  begin for i in 1..10000 loop
  2   insert into t values(16);
  3   end loop;
  4   end;
  5  /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> update t set id=10000 where id=16 and rownum=1;

1 row updated.

SQL> commit;

Commit complete.

SQL> select count(*) from t;

  COUNT(*)
----------
     10003

SQL> select * from t partition(p1);

        ID
----------
         1

SQL> select * from t partition(p2);

        ID
----------
         6

SQL> select * from t partition(p3);

        ID
----------
        11

SQL> select id,count(*) from t partition(pm) group by id;

        ID   COUNT(*)
---------- ----------
     10000          1
        16       9999

上面的数据查出了每个分区的记录数,注意PM 分区有9999 条记录的值为16,1 条记录的值
为10000。
SQL>  select partition_name, num_rows,avg_row_len,last_analyzed from user_tab_partitions where table_name='T';

PARTITION_NAME         NUM_ROWS AVG_ROW_LEN LAST_ANALYZE
-------------------- ---------- ----------- ------------
P1                            1           3 01-9月 -12
P2                            1           3 01-9月 -12
P3                            1           3 01-9月 -12
PM

SQL> select num_rows,avg_row_len,last_analyzed from user_tables where table_name
  2  ='T';

  NUM_ROWS AVG_ROW_LEN LAST_ANALYZE
---------- ----------- ------------
         3           3 01-9月 -12
我们看到,这时候新的分区PM 没有被分析,而表的全局信息显示,这个表中的数据仍然只
有3 条,让我们来执行一条SQL,看看CBO 的判断:
SQL> set linesize 1000
SQL> set autotrace traceonly
SQL> select * from t where id=16;

9999 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 657481967

------------------------------------------------------------------------------------------------
| Id  | Operation              | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |       |     1 |     3 |     1   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|       |     1 |     3 |     1   (0)| 00:00:01 |     4 |     4 |
|*  2 |   INDEX RANGE SCAN     | IND_T |     1 |     3 |     1   (0)| 00:00:01 |     4 |     4 |
------------------------------------------------------------------------------------------------

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

   2 - access("ID"=16)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        713  consistent gets
          0  physical reads
       1496  redo size
     172445  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)
       9999  rows processed
CBO 根据表的信息估算的返回结果级只有1 条。这个结果严重偏离了实际情
况,我们知道实际上ID=16 的值有9999 条,但是因为我们没有对新增加的表进行分析,所
以导致CBO 出现了错误的判断。
那么如果我们只对分区级别进行分析,而不做表级的分析,那么会如
何呢?
SQL> set autotrace off
SQL> exec dbms_stats.gather_table_stats(user,'t',partname=>'pm',granularity=>'partition');

PL/SQL procedure successfully completed.

SQL> select partition_name, num_rows,avg_row_len,last_analyzed from user_tab_partitions where
  2  table_name='T';

PARTITION_NAME         NUM_ROWS AVG_ROW_LEN LAST_ANALYZE
-------------------- ---------- ----------- ------------
P1                            1           3 01-9月 -12
P2                            1           3 01-9月 -12
P3                            1           3 01-9月 -12
PM                        10000           3 01-9月 -12

我们将granularity 设置为partition,这样,ORACLE 只会在partition 级别上做分析,表
级(BLOBAL)将不会作分析:
SQL> select num_rows,avg_row_len,last_analyzed from user_tables where table_name='T';

  NUM_ROWS AVG_ROW_LEN LAST_ANALYZE
---------- ----------- ------------
         3           3 01-9月 -12
我们看到PM 分区的信息已经被更新,显示为10000 条,而在表级信息里,显示的T 表仍然
只有3 条记录

SQL> alter table t add object_name varchar2(10);

Table altered.


SQL> update t set object_name='AAAA';

10003 rows updated.

SQL> commit
  2  ;

Commit complete.

SQL> create index ind2_t on t(object_name);

Index created.

SQL> set autotrace traceonly
SQL> select * from t where object_name='AAAA';

10003 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 4057582461

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |        |     1 |     3 |     5   (0)| 00:00:01 |       |       |
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| T      |     1 |     3 |     5   (0)| 00:00:01 | ROWID | ROWID |
|*  2 |   INDEX RANGE SCAN                 | IND2_T |    40 |       |    28   (0)| 00:00:01 |       |       |
-------------------------------------------------------------------------------------------------------------

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

   2 - access("OBJECT_NAME"='AAAA')


Statistics
----------------------------------------------------------
         24  recursive calls
          0  db block gets
       8822  consistent gets
         28  physical reads
          0  redo size
     277692  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)
      10003  rows processed
  CBO 估算出结果集为1,所以选择了索引,因此,即使已经对分区进行了分析,而没有
同步更新表级(全局)统计信息,依然可能导致CBO 选择执行计划错误。
我们来完成全局的信息统计,再来看CBO 是如何选择执行计划的?
SQL> exec dbms_stats.gather_table_stats(user,'t',granularity=>'GLOBAL');

PL/SQL procedure successfully completed.


SQL> set autotrace off
SQL> select partition_name, num_rows,avg_row_len,last_analyzed from user_tab_partitions where  table_name='T';

PARTITION_NAME         NUM_ROWS AVG_ROW_LEN LAST_ANALYZE
-------------------- ---------- ----------- ------------
P1                            1           3 01-9月 -12
P2                            1           3 01-9月 -12
P3                            1           3 01-9月 -12
PM                        10000           3 01-9月 -12
分析完成后,表的行数在统计信息里已经更新为10000 条了,说表全局统计信息已
经更新。再看刚才SQL 的执行计划:
SQL> set autotrace traceonly
SQL> select * from t where object_name='AAAA';

10003 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3557914527

--------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      | 10003 | 80024 |    17   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE ALL|      | 10003 | 80024 |    17   (0)| 00:00:01 |     1 |     4 |
|*  2 |   TABLE ACCESS FULL | T    | 10003 | 80024 |    17   (0)| 00:00:01 |     1 |     4 |
--------------------------------------------------------------------------------------------

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

   2 - filter("OBJECT_NAME"='AAAA')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        731  consistent gets
          0  physical reads
          0  redo size
     172557  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)
      10003  rows processed
这时候CBO 估算出返回的结果级为10003 行,所以毫不犹豫的选择了全表扫描。
我们看到,即使在分区级别的信息有了,如果表的全局信息没有更新,依然会导致CBO 做出
错误的执行计划。
那么如果我们只对全局信息做分析,而不对分区做分析,对CBO 的影响又将是如何的
呢?
用下面的方式将PM 分区的统计信息恢复为载入数据之前的状态:
SQL> delete from t where id>15;

10000 rows deleted.


Execution Plan
----------------------------------------------------------
Plan hash value: 1929529978

------------------------------------------------------------------------------------------------
| Id  | Operation               | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT        |      | 10000 | 30000 |     7   (0)| 00:00:01 |       |       |
|   1 |  DELETE                 | T    |       |       |            |          |       |       |
|   2 |   PARTITION RANGE SINGLE|      | 10000 | 30000 |     7   (0)| 00:00:01 |     4 |     4 |
|*  3 |    TABLE ACCESS FULL    | T    | 10000 | 30000 |     7   (0)| 00:00:01 |     4 |     4 |
------------------------------------------------------------------------------------------------

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

   3 - filter("ID">15)


Statistics
----------------------------------------------------------
         24  recursive calls
      54851  db block gets
         48  consistent gets
          0  physical reads
    7668236  redo size
        850  bytes sent via SQL*Net to client
        782  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      10000  rows processed

SQL> set autotrace off
SQL> commit;

Commit complete.

SQL> delete from t where id>15;

0 rows deleted.

SQL> exec dbms_stats.gather_table_stats(user,'t');

PL/SQL procedure successfully completed.

SQL> select partition_name, num_rows,avg_row_len,last_analyzed from user_tab_partitions where
  2  table_name='T';

PARTITION_NAME         NUM_ROWS AVG_ROW_LEN LAST_ANALYZE
-------------------- ---------- ----------- ------------
P1                            1           8 01-9月 -12
P2                            1           8 01-9月 -12
P3                            1           8 01-9月 -12
PM                            0           0 01-9月 -12

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

  NUM_ROWS AVG_ROW_LEN LAST_ANALYZE
---------- ----------- ------------
         3           8 01-9月 -12
SQL> select count(*) from t;

  COUNT(*)
----------
         3


SQL> alter table t drop column object_name;

Table altered.

SQL>  begin
  2   for i in 1..10000 loop
  3   insert into t values(16);
  4   end loop;
  5   end;
  6  /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> update t set id=10000 where id=16 and rownum=1;

1 row updated.

SQL> commit;

Commit complete.


对表做全局分析,不对新的分区分析

SQL>  exec dbms_stats.gather_table_stats(user,'t',granularity=>'global',method_opt=>'for all columns size 1');

PL/SQL procedure successfully completed.

设置method_opt=>'for all columns size 1'是要Oracle 不对列做直方图分
析,因为现在我们只要全局统计信息。
SQL> select num_rows,avg_row_len,last_analyzed from user_tables where table_name='T';

  NUM_ROWS AVG_ROW_LEN LAST_ANALYZE
---------- ----------- ------------
     10003           3 01-9月 -12

SQL>  select partition_name, num_rows,avg_row_len,last_analyzed from user_tab_partitions where  table_name='T';

PARTITION_NAME         NUM_ROWS AVG_ROW_LEN LAST_ANALYZE
-------------------- ---------- ----------- ------------
P1                            1           8 01-9月 -12
P2                            1           8 01-9月 -12
P3                            1           8 01-9月 -12
PM                            0           0 01-9月 -12
全局分析信息更新了,而分区的分析信息没有更新。
SQL> set autotrace traceonly exp;
SQL> select * from t where id=16;

Execution Plan
----------------------------------------------------------
Plan hash value: 657481967

------------------------------------------------------------------------------------------------
| Id  | Operation              | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |       |     1 |    13 |     1   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|       |     1 |    13 |     1   (0)| 00:00:01 |     4 |     4 |
|*  2 |   INDEX RANGE SCAN     | IND_T |     1 |    13 |     1   (0)| 00:00:01 |     4 |     4 |
------------------------------------------------------------------------------------------------

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

   2 - access("ID"=16)

尽管CBO 知道T 表有10003 条数据,因为没有对PM 分区做分析(包括直方图),无法知
道ID=16 的数据有多少,所以对返回结果集做出了错误的估计,认为结果集为1 条,选择了
使用索引。
我们从上面的例子中看到,当一些新的数据进入到表中,如何对这些新的数据进行分析
(即如何设置Granularity 的值),是一个非常重要的问题;上面的例子只是说明了,如果
有一些信息没有分析到,就可能导致CBO 做出错误的执行计划。

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

上一篇: 直方图
请登录后发表评论 登录
全部评论

注册时间:2008-12-04

  • 博文量
    25
  • 访问量
    335697