在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/，如需转载，请注明出处，否则将追究法律责任。
上一篇：
直方图
下一篇：
并行和非并行在不通场景中的性能差异
请登录后发表评论
登录
全部评论
