Oracle数据迁移后由列的直方图统计信息引起的执行计划异常-搜云库技术团队

IDEA2023.1.3破解,IDEA破解,IDEA 2023.1破解,最新IDEA激活码

（一）问题背景

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

[![72_1.png][72_1.png]][72_1.png_72_1.png]

图.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时，走全表扫描

“`

an style="color: rgb(0, 0, 255);">Plan Hash Value : 1151852672

n style="color: rgb(0, 128, 128);">–————————————————————————————–
n style="color: rgb(128, 128, 128);">| Id | Operation | Name | Rows | Bytes | Cost | Time |
n style="color: rgb(0, 128, 128);">–————————————————————————————–
n style="color: rgb(128, 128, 128);">| 0 | SELECT STATEMENT | | 1 | 5 | 2 | 00:00:01 |
n style="color: rgb(128, 128, 128);">| 1 | TABLE ACCESS BY INDEX ROWID | TEST01 | 1 | 5 | 2 | 00:00:01 |
n style="color: rgb(128, 128, 128);">| * 2 | INDEX RANGE SCAN | IDX_TEST01_ID | 1 | | 1 | 00:00:01 |
n style="color: rgb(0, 128, 128);">–————————————————————————————–
n style="color: rgb(0, 0, 0);">
icate Information (identified by operation id):
an>–—————————————-
“`

“`

an style="color: rgb(0, 0, 255);">Plan Hash Value : 262542483

n style="color: rgb(0, 128, 128);">–———————————————————————
an style="color: rgb(128, 128, 128);">| Id | Operation | Name | Rows | Bytes | Cost | Time |
n style="color: rgb(0, 128, 128);">–———————————————————————
an style="color: rgb(128, 128, 128);">| 0 | SELECT STATEMENT | | 1000 | 5000 | 3 | 00:00:01 |
an style="color: rgb(128, 128, 128);">| * 1 | TABLE ACCESS FULL | TEST01 | 1000 | 5000 | 3 | 00:00:01 |
n style="color: rgb(0, 128, 128);">–———————————————————————
n style="color: rgb(0, 0, 0);">
icate Information (identified by operation id):
pan>–—————————————-
“`

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时，走索引范围扫描

“`
an style="color: rgb(0, 0, 255);">Plan Hash Value : 1151852672

n style="color: rgb(0, 128, 128);">–————————————————————————————–
n style="color: rgb(128, 128, 128);">| Id | Operation | Name | Rows | Bytes | Cost | Time |
n style="color: rgb(0, 128, 128);">–————————————————————————————–
n style="color: rgb(128, 128, 128);">| 0 | SELECT STATEMENT | | 278 | 1390 | 2 | 00:00:01 |
n style="color: rgb(128, 128, 128);">| 1 | TABLE ACCESS BY INDEX ROWID | TEST01 | 278 | 1390 | 2 | 00:00:01 |
n style="color: rgb(128, 128, 128);">| * 2 | INDEX RANGE SCAN | IDX_TEST01_ID | 278 | | 1 | 00:00:01 |
n style="color: rgb(0, 128, 128);">–————————————————————————————–
n style="color: rgb(0, 0, 0);">
icate Information (identified by operation id):
an>–—————————————-
“`

“`

an style="color: rgb(0, 0, 255);">Plan Hash Value : 1151852672

n style="color: rgb(0, 128, 128);">–————————————————————————————–
n style="color: rgb(128, 128, 128);">| Id | Operation | Name | Rows | Bytes | Cost | Time |
n style="color: rgb(0, 128, 128);">–————————————————————————————–
n style="color: rgb(128, 128, 128);">| 0 | SELECT STATEMENT | | 278 | 1390 | 2 | 00:00:01 |
n style="color: rgb(128, 128, 128);">| 1 | TABLE ACCESS BY INDEX ROWID | TEST01 | 278 | 1390 | 2 | 00:00:01 |
n style="color: rgb(128, 128, 128);">| * 2 | INDEX RANGE SCAN | IDX_TEST01_ID | 278 | | 1 | 00:00:01 |
n style="color: rgb(0, 128, 128);">–————————————————————————————–
n style="color: rgb(0, 0, 0);">
icate Information (identified by operation id):
an>–—————————————-
“`

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

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

table>

id列重新收集直方图统计信息，当id=1时，走索引范围扫描 id列重新收集直方图统计信息，当id=4时，走全表扫描


an style="color: rgb(0, 0, 255);">Plan</span> Hash Value  : <span style="color: rgb(128, 0, 0); font-weight: bold;">1151852672</span> 

n style="color: rgb(0, 128, 128);">--</span><span style="color: rgb(0, 128, 128);">--------------------------------------------------------------------------------------</span>
n style="color: rgb(128, 128, 128);">|</span> Id  <span style="color: rgb(128, 128, 128);">|</span> Operation                     <span style="color: rgb(128, 128, 128);">|</span> Name          <span style="color: rgb(128, 128, 128);">|</span> Rows <span style="color: rgb(128, 128, 128);">|</span> Bytes <span style="color: rgb(128, 128, 128);">|</span> Cost <span style="color: rgb(128, 128, 128);">|</span> Time     <span style="color: rgb(128, 128, 128);">|</span>
n style="color: rgb(0, 128, 128);">--</span><span style="color: rgb(0, 128, 128);">--------------------------------------------------------------------------------------</span>
n style="color: rgb(128, 128, 128);">|</span>   <span style="color: rgb(128, 0, 0); font-weight: bold;">0</span> <span style="color: rgb(128, 128, 128);">|</span> <span style="color: rgb(0, 0, 255);">SELECT</span> STATEMENT              <span style="color: rgb(128, 128, 128);">|</span>               <span style="color: rgb(128, 128, 128);">|</span>    <span style="color: rgb(128, 0, 0); font-weight: bold;">1</span> <span style="color: rgb(128, 128, 128);">|</span>     <span style="color: rgb(128, 0, 0); font-weight: bold;">5</span> <span style="color: rgb(128, 128, 128);">|</span>    <span style="color: rgb(128, 0, 0); font-weight: bold;">2</span> <span style="color: rgb(128, 128, 128);">|</span> <span style="color: rgb(128, 0, 0); font-weight: bold;">00</span>:<span style="color: rgb(128, 0, 0); font-weight: bold;">00</span>:<span style="color: rgb(128, 0, 0); font-weight: bold;">01</span> <span style="color: rgb(128, 128, 128);">|</span>
n style="color: rgb(128, 128, 128);">|</span>   <span style="color: rgb(128, 0, 0); font-weight: bold;">1</span> <span style="color: rgb(128, 128, 128);">|</span>   <span style="color: rgb(0, 0, 255);">TABLE</span> ACCESS <span style="color: rgb(0, 0, 255);">BY</span> <span style="color: rgb(0, 0, 255);">INDEX</span> ROWID <span style="color: rgb(128, 128, 128);">|</span> TEST01        <span style="color: rgb(128, 128, 128);">|</span>    <span style="color: rgb(128, 0, 0); font-weight: bold;">1</span> <span style="color: rgb(128, 128, 128);">|</span>     <span style="color: rgb(128, 0, 0); font-weight: bold;">5</span> <span style="color: rgb(128, 128, 128);">|</span>    <span style="color: rgb(128, 0, 0); font-weight: bold;">2</span> <span style="color: rgb(128, 128, 128);">|</span> <span style="color: rgb(128, 0, 0); font-weight: bold;">00</span>:<span style="color: rgb(128, 0, 0); font-weight: bold;">00</span>:<span style="color: rgb(128, 0, 0); font-weight: bold;">01</span> <span style="color: rgb(128, 128, 128);">|</span>
n style="color: rgb(128, 128, 128);">|</span> <span style="color: rgb(128, 128, 128);">*</span> <span style="color: rgb(128, 0, 0); font-weight: bold;">2</span> <span style="color: rgb(128, 128, 128);">|</span>    <span style="color: rgb(0, 0, 255);">INDEX</span> RANGE SCAN           <span style="color: rgb(128, 128, 128);">|</span> IDX_TEST01_ID <span style="color: rgb(128, 128, 128);">|</span>    <span style="color: rgb(128, 0, 0); font-weight: bold;">1</span> <span style="color: rgb(128, 128, 128);">|</span>       <span style="color: rgb(128, 128, 128);">|</span>    <span style="color: rgb(128, 0, 0); font-weight: bold;">1</span> <span style="color: rgb(128, 128, 128);">|</span> <span style="color: rgb(128, 0, 0); font-weight: bold;">00</span>:<span style="color: rgb(128, 0, 0); font-weight: bold;">00</span>:<span style="color: rgb(128, 0, 0); font-weight: bold;">01</span> <span style="color: rgb(128, 128, 128);">|</span>
n style="color: rgb(0, 128, 128);">--</span><span style="color: rgb(0, 128, 128);">--------------------------------------------------------------------------------------</span>
n style="color: rgb(0, 0, 0);">
icate Information (identified </span><span style="color: rgb(0, 0, 255);">by</span><span style="color: rgb(0, 0, 0);"> operation id):
an><span style="color: rgb(0, 128, 128);">--</span><span style="color: rgb(0, 128, 128);">----------------------------------------</span>

</div></td>


an style="color: rgb(0, 0, 255);">Plan</span> Hash Value  : <span style="color: rgb(128, 0, 0); font-weight: bold;">262542483</span> 

n style="color: rgb(0, 128, 128);">--</span><span style="color: rgb(0, 128, 128);">---------------------------------------------------------------------</span>
n style="color: rgb(128, 128, 128);">|</span> Id  <span style="color: rgb(128, 128, 128);">|</span> Operation           <span style="color: rgb(128, 128, 128);">|</span> Name   <span style="color: rgb(128, 128, 128);">|</span> Rows <span style="color: rgb(128, 128, 128);">|</span> Bytes <span style="color: rgb(128, 128, 128);">|</span> Cost <span style="color: rgb(128, 128, 128);">|</span> Time     <span style="color: rgb(128, 128, 128);">|</span>
n style="color: rgb(0, 128, 128);">--</span><span style="color: rgb(0, 128, 128);">---------------------------------------------------------------------</span>
n style="color: rgb(128, 128, 128);">|</span>   <span style="color: rgb(128, 0, 0); font-weight: bold;">0</span> <span style="color: rgb(128, 128, 128);">|</span> <span style="color: rgb(0, 0, 255);">SELECT</span> STATEMENT    <span style="color: rgb(128, 128, 128);">|</span>        <span style="color: rgb(128, 128, 128);">|</span> <span style="color: rgb(128, 0, 0); font-weight: bold;">1000</span> <span style="color: rgb(128, 128, 128);">|</span>  <span style="color: rgb(128, 0, 0); font-weight: bold;">5000</span> <span style="color: rgb(128, 128, 128);">|</span>    <span style="color: rgb(128, 0, 0); font-weight: bold;">3</span> <span style="color: rgb(128, 128, 128);">|</span> <span style="color: rgb(128, 0, 0); font-weight: bold;">00</span>:<span style="color: rgb(128, 0, 0); font-weight: bold;">00</span>:<span style="color: rgb(128, 0, 0); font-weight: bold;">01</span> <span style="color: rgb(128, 128, 128);">|</span>
n style="color: rgb(128, 128, 128);">|</span> <span style="color: rgb(128, 128, 128);">*</span> <span style="color: rgb(128, 0, 0); font-weight: bold;">1</span> <span style="color: rgb(128, 128, 128);">|</span>   <span style="color: rgb(0, 0, 255);">TABLE</span> ACCESS <span style="color: rgb(0, 0, 255);">FULL</span> <span style="color: rgb(128, 128, 128);">|</span> TEST01 <span style="color: rgb(128, 128, 128);">|</span> <span style="color: rgb(128, 0, 0); font-weight: bold;">1000</span> <span style="color: rgb(128, 128, 128);">|</span>  <span style="color: rgb(128, 0, 0); font-weight: bold;">5000</span> <span style="color: rgb(128, 128, 128);">|</span>    <span style="color: rgb(128, 0, 0); font-weight: bold;">3</span> <span style="color: rgb(128, 128, 128);">|</span> <span style="color: rgb(128, 0, 0); font-weight: bold;">00</span>:<span style="color: rgb(128, 0, 0); font-weight: bold;">00</span>:<span style="color: rgb(128, 0, 0); font-weight: bold;">01</span> <span style="color: rgb(128, 128, 128);">|</span>
n style="color: rgb(0, 128, 128);">--</span><span style="color: rgb(0, 128, 128);">---------------------------------------------------------------------</span>
n style="color: rgb(0, 0, 0);">
icate Information (identified </span><span style="color: rgb(0, 0, 255);">by</span><span style="color: rgb(0, 0, 0);"> operation id):
an><span style="color: rgb(0, 128, 128);">--</span><span style="color: rgb(0, 128, 128);">----------------------------------------</span>

“`

tr>
body>
ble>

）总结**

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

：

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

1.png]: https://tech.souyunku.com/wp-content/uploads/2020/8/84/222624/1/72_1.png
1.png_72_1.png]: https://img2020.cnblogs.com/blog/823295/202006/823295-20200608214812523-312700012.png

文章永久链接：https://tech.souyunku.com/?p=35184

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

相关推荐

评论抢沙发

学习必备：精选优质专栏

大前端WP主题更专业更方便

觉得文章有用就打赏一下文章作者

微信扫一扫打赏

微信扫一扫打赏

QQ咨询

回顶部

相关推荐

评论 抢沙发

学习必备：精选优质专栏

大前端WP主题 更专业 更方便

觉得文章有用就打赏一下文章作者

微信扫一扫打赏

微信扫一扫打赏

QQ咨询

回顶部

评论抢沙发

大前端WP主题更专业更方便