在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/,如需转载,请注明出处,否则将追究法律责任。