Oracle数据迁移后由列的直方图统计信息引起的执行计划异常

(一)问题背景

在使用impdp进行数据导入的时候,往往在导入表和索引的统计信息的时候,速度非常慢,因此我在使用impdp进行导入时,会使用exclude=table_statistics排除表的统计信息,从而加快导入速度,之后再手动收集统计信息。

Oracle数据迁移后由列的直方图统计信息引起的执行计划异常

图.impdp导入数据的时导入统计信息速度非常慢

导入语句如下:

impdp user/password directory=DUMPDIR dumpfile=TEST01.dmp logfile=TEST01.log remap_schema=TEST_USER:TEST_USER123 exclude=table_statistics

手动收集统计信息语句如下:

EXEC dbms_stats.gather_table_stats(ownname => 'LIJIAMAN',tabname => 'TEST01');

最近使用以上方法将数据还原到测试环境后,发现与生产环境执行计划存在偏差,本来应该走全表扫描的,却走了索引范围扫描。经过确认,是由于列的直方图统计信息未收集引发的执行计划偏差。


(二)列的直方图统计信息

什么是列的直方图统计信息呢?在Oracle数据库中,Oracle默认列上的值是在最小值与最大值之间均分布的,当在计算cardinatity时,会以均匀分布的方式计算,但是在实际生活中某些场景下数据并非均匀分布。举个列子,某公司有员工10000人,表A的列COL1记录员工的绩效(分别是:A、B、C、D,A最好,D最差),那么可能A占了15%,B占了60,C占了20%,D占了5%。很明显在该场景下数据并非均匀分布,假如以均匀分布的方式去统计员工的绩效,可能会导致执行计划失准。

当列的数据分布不均匀的时候,就需要统计列上的数据分布情况,从而走出正确的执行计划,列的直方图统计信息就是记录列上的数据分布情况的。


(三)异常模拟

STEP1:创建测试表test01

create table test01
(id number,
name varchar2(10)
);
create index idx_test01_id on test01(id);

向test01中插入测试数据

begin
insert into test01 values(1,'a'); for i in 1..10 loop
insert into test01 values(2,'b');
end loop; for i in 1..100 loop
insert into test01 values(3,'c');
end loop; for i in 1..1000 loop
insert into test01 values(4,'d');
end loop; commit;
end;

查看数据分布情况:

SQL> SELECT ID,NAME,COUNT(*) FROM test01 GROUP BY ID,NAME ORDER BY COUNT(*);

ID          NAME       COUNT(*)
---------- ---------- ----------
1 a 1
2 b 10
3 c 100
4 d 1000

STEP2:收集统计信息,因为上面查询过id列,故在收集统计信息的时候,会收集直方图的统计信息

EXEC dbms_stats.gather_table_stats(ownname => 'LIJIAMAN',tabname => 'TEST01');

查看是否已经收集了直方图信息,发现id列上已经收集

SQL> SELECT a.OWNER,a.TABLE_NAME,a.COLUMN_NAME,a.LOW_VALUE,a.HIGH_VALUE,a.NUM_BUCKETS,a.HISTOGRAM
2 FROM dba_tab_columns a
3 WHERE a.OWNER = 'LIJIAMAN' AND a.TABLE_NAME = 'TEST01'; OWNER TABLE_NAME COLUMN_NAME LOW_VALUE HIGH_VALUE NUM_BUCKETS HISTOGRAM
--------- ----------- ------------ ------------ ------------ ----------- ---------------
LIJIAMAN TEST01 ID C102 C105 4 FREQUENCY
LIJIAMAN TEST01 NAME 61 64 1 NONE

查看直方图,已经将id列的4个值放入了4个bucket中:

SQL> SELECT * FROM dba_tab_histograms a WHERE a.OWNER = 'LIJIAMAN' AND a.TABLE_NAME = 'TEST01';

OWNER        TABLE_NAME    COLUMN_NAME    ENDPOINT_NUMBER    ENDPOINT_VALUE ENDPOINT_ACTUAL_VALUE
----------- ------------ ------------- --------------- -------------- ----------------------
LIJIAMAN TEST01 ID 1 1
LIJIAMAN TEST01 ID 11 2
LIJIAMAN TEST01 ID 111 3
LIJIAMAN TEST01 ID 1111 4
LIJIAMAN TEST01 NAME 0 5.036527952778
LIJIAMAN TEST01 NAME 1 5.192296858534

STEP3:查看id=1和id=4的执行计划,当id=1时,走索引范围扫描,当id=4时,走全表扫描

id列存在直方图统计信息,当id=1时,走索引范围扫描 id列存在直方图统计信息,当id=4时,走全表扫描
SELECT * FROM test01 WHERE ID=1

 Plan Hash Value  : 1151852672 

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 2 | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | TEST01 | 1 | 5 | 2 | 00:00:01 |
| * 2 | INDEX RANGE SCAN | IDX_TEST01_ID | 1 | | 1 | 00:00:01 |
---------------------------------------------------------------------------------------- Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("ID"=1)
SELECT * FROM test01 WHERE ID=4

 Plan Hash Value  : 262542483 

-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 5000 | 3 | 00:00:01 |
| * 1 | TABLE ACCESS FULL | TEST01 | 1000 | 5000 | 3 | 00:00:01 |
----------------------------------------------------------------------- Predicate Information (identified by operation id):
------------------------------------------
* 1 - filter("ID"=4)

STEP4:接下来模拟数据迁移,排除统计信息

导出表test01

expdp lijiaman/lijiaman directory=DUMPDIR tables=LIJIAMAN.TEST01 dumpfile =test01.dmp

删除原来的表:

SQL> drop table test01;
Table dropped

再次导入表,排除统计信息:

impdp lijiaman/lijiaman directory=DUMPDIR dumpfile =test01.dmp exclude=table_statistics

查看表的统计信息,不存在统计信息:

SQL> SELECT   a.OWNER,a.TABLE_NAME,a.COLUMN_NAME,a.LOW_VALUE,a.HIGH_VALUE,a.NUM_BUCKETS,a.HISTOGRAM
2 FROM dba_tab_columns a
3 WHERE a.OWNER = 'LIJIAMAN' AND a.TABLE_NAME = 'TEST01'; OWNER TABLE_NAME COLUMN_NAME LOW_VALUE HIGH_VALUE NUM_BUCKETS HISTOGRAM
-------------- --------------- --------------- ------------ ------------ ----------- ---------------
LIJIAMAN TEST01 ID NONE
LIJIAMAN TEST01 NAME NONE

STEP5:手动收集统计信息

EXEC dbms_stats.gather_table_stats(ownname => 'LIJIAMAN',tabname => 'TEST01');

发现统计信息已经收集,但是不存在直方图的统计信息

SQL> SELECT   a.OWNER,a.TABLE_NAME,a.COLUMN_NAME,a.LOW_VALUE,a.HIGH_VALUE,a.NUM_BUCKETS,a.HISTOGRAM
2 FROM dba_tab_columns a
3 WHERE a.OWNER = 'LIJIAMAN' AND a.TABLE_NAME = 'TEST01'; OWNER TABLE_NAME COLUMN_NAME LOW_VALUE HIGH_VALUE NUM_BUCKETS HISTOGRAM
--------- ----------- ----------- ----------- ----------- ----------- ---------------
LIJIAMAN TEST01 ID C102 C105 1 NONE
LIJIAMAN TEST01 NAME 61 64 1 NONE

STEP6:再次查看id=1和id=4的执行计划,当id=1或id=4时,都走索引范围扫描

id列未收集直方图统计信息,当id=1时,走索引范围扫描 id列未收集直方图统计信息,当id=4时,走索引范围扫描
SELECT * FROM test01 WHERE ID=1
Plan Hash Value : 1151852672 ----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 278 | 1390 | 2 | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | TEST01 | 278 | 1390 | 2 | 00:00:01 |
| * 2 | INDEX RANGE SCAN | IDX_TEST01_ID | 278 | | 1 | 00:00:01 |
---------------------------------------------------------------------------------------- Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("ID"=1)
SELECT * FROM test01 WHERE ID=4

 Plan Hash Value  : 1151852672 

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 278 | 1390 | 2 | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | TEST01 | 278 | 1390 | 2 | 00:00:01 |
| * 2 | INDEX RANGE SCAN | IDX_TEST01_ID | 278 | | 1 | 00:00:01 |
---------------------------------------------------------------------------------------- Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("ID"=4)

STEP7:再次收集统计信息,因为使用过了id列作为查询条件,故再次收集统计信息时,会收集id列的直方图信息:

EXEC dbms_stats.gather_table_stats(ownname => 'LIJIAMAN',tabname => 'TEST01');

可以看到,此时已经收集了id列的直方图统计信息:

SQL> SELECT   a.OWNER,a.TABLE_NAME,a.COLUMN_NAME,a.LOW_VALUE,a.HIGH_VALUE,a.NUM_BUCKETS,a.HISTOGRAM
2 FROM dba_tab_columns a
3 WHERE a.OWNER = 'LIJIAMAN' AND a.TABLE_NAME = 'TEST01'; OWNER TABLE_NAME COLUMN_NAME LOW_VALUE HIGH_VALUE NUM_BUCKETS HISTOGRAM
------------------------------ ------------------------------ ------------------------------ ------------- ------------- ----------- ---------------
LIJIAMAN TEST01 ID C102 C105 4 FREQUENCY
LIJIAMAN TEST01 NAME 61 64 1 NONE

执行计划已经按照我们想要的方式走:

id列重新收集直方图统计信息,当id=1时,走索引范围扫描 id列重新收集直方图统计信息,当id=4时,走全表扫描
SELECT * FROM test01 WHERE ID=1

 Plan Hash Value  : 1151852672 

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 2 | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | TEST01 | 1 | 5 | 2 | 00:00:01 |
| * 2 | INDEX RANGE SCAN | IDX_TEST01_ID | 1 | | 1 | 00:00:01 |
---------------------------------------------------------------------------------------- Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("ID"=1)
SELECT * FROM test01 WHERE ID=4

 Plan Hash Value  : 262542483 

-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 5000 | 3 | 00:00:01 |
| * 1 | TABLE ACCESS FULL | TEST01 | 1000 | 5000 | 3 | 00:00:01 |
----------------------------------------------------------------------- Predicate Information (identified by operation id):
------------------------------------------
* 1 - filter("ID"=4)

(四)总结

在使用expdp/impdp进行导出/导入数据的时,统计信息是非常重要的,对于大部分统计信息,我们可以在导入结束之后收集获得。但是对于列的直方图统计信息,Oracle默认收集的方式是auto,即Oracle会根据用户对列的使用情况进行判断是否收集直方图统计信息,然而数据刚迁移完成,在表还未使用的情况下收集统计信息,往往收集不到列的直方图信息,这就造成了执行计划异常,这种情况通常在下一次收集统计信息之后会有所改变。

参考文档:

DBMS_STATS With METHOD_OPT =>'..SIZE auto' May Not Collect Histograms (Doc ID 557594.1)

上一篇:Java基础——常用类(Date、File)以及包装类


下一篇:第13章 Java常用类