44
如何将当前统计信息和历史统计信息进行比较？
source link: http://www.dboracle.com/archivers/如何将当前统计信息和历史统计信息进行比较？.html?amp%3Butm_medium=referral
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
版权声明：本文为Buddy Yuan原创文章，未经允许不得转载。原文地址： 如何将当前统计信息和历史统计信息进行比较？
在我们日常的工作中，经常会遇到如下的一些问题，比如一个SQL突然今天变慢了，然后我们进行了一系列的诊断,最后我们发现是执行计划变化了。但是开发或者是运维室主任就会发问：“执行计划为什么发生变化？”大多数情况下，执行计划的变化是由于数据库中的统计信息发生了改变，因此如果能够快速的定位统计信息发生了改变，同时知道究竟是什么列上的数据导致了执行计划的变化，那是不是很棒呢？
默认情况下，当前的统计数据都会保存SYSAUX表空间中的WRI$系列表中。可以使用Oracle的dbms_stat包，将当前统计信息和历史记录表中的统计信息进行比较。
我们先来研究一下这个功能。首先创建一个表T1,插入点数据。收集统计信息，然后再此插入点数据，并再收集一次统计信息，这样我们就可以查询dba_tab_stats_history视图，可以看到该历史表里面的记录有两条。
SQL> create table t1 as select * from dba_objects;
Table created.
SQL> select table_name,stats_update_time from dba_tab_stats_history where owner='C##TEST' and table_name='T1';
no rows selected
SQL> select owner,table_name,last_analyzed,num_rows from dba_tables where table_name='T1' and owner='C##TEST';
OWNER TABLE_NAME LAST_ANALYZED NUM_ROWS
------------------------------ ------------------------------ ------------------- ----------
C##TEST T1 2018-10-05 18:21:12 72873
SQL> insert into t1 select * from t1;
72873 rows created.
SQL> commit;
Commit complete.
SQL> EXEC DBMS_STATS.gather_table_stats('C##TEST', 'T1');
PL/SQL procedure successfully completed.
SQL> select table_name,stats_update_time from dba_tab_stats_history where owner='C##TEST' and table_name='T1';
TABLE_NAME STATS_UPDATE_TIME
------------------------------ ---------------------------------------------------------------------------
T1 05-OCT-18 06.22.29.572843 PM +08:00
SQL> insert into t1 select * from t1;
145746 rows created.
SQL> insert into t1 select * from t1;
291492 rows created.
SQL> commit;
Commit complete.
SQL> EXEC DBMS_STATS.gather_table_stats('C##TEST', 'T1');
PL/SQL procedure successfully completed.
SQL> select table_name,stats_update_time from dba_tab_stats_history where owner='C##TEST' and table_name='T1';
TABLE_NAME STATS_UPDATE_TIME
------------------------------ ---------------------------------------------------------------------------
T1 05-OCT-18 06.22.29.572843 PM +08:00
T1 05-OCT-18 06.23.07.575496 PM +08:00
接下来我们运行dbms_stats.diff_table_stats_in_history进行对比。这里有一个参数叫PCTTHRESHOLD，代表着阈值为0%，没有发生统计信息更改也会生成报告，如果把阈值设置成10%，就是统计信息更改10%以上或者更多才会生成报告。
SQL> set long 2000000
SQL> set pagesize 1000
SQL>
SQL> select * from table(dbms_stats.diff_table_stats_in_history(
2 ownname => 'C##TEST',
3 tabname => 'T1',
4 time1 => systimestamp,
5 time2 => to_timestamp('05-OCT-18 06.22.29 PM','DD-MON-YY hh12.mi.ss PM'),
6 pctthreshold => 0));
REPORT MAXDIFFPCT
-------------------------------------------------------------------------------- ----------
############################################################################### 88.0680417
STATISTICS DIFFERENCE REPORT FOR:
.................................
TABLE : T1
OWNER : C##TEST
SOURCE A : Statistics as of 05-OCT-18 06.31.07.511634 PM +08:00
SOURCE B : Statistics as of 05-OCT-18 06.22.29.000000 PM +08:00
PCTTHRESHOLD : 0
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
TABLE / (SUB)PARTITION STATISTICS DIFFERENCE:
.............................................
OBJECTNAME TYP SRC ROWS BLOCKS ROWLEN SAMPSIZE
...............................................................................
T1 T A 582984 12169 133 582984
B 72873 1452 133 72873
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
COLUMN STATISTICS DIFFERENCE:
.............................
COLUMN_NAME SRC NDV DENSITY HIST NULLS LEN MIN MAX SAMPSIZ
...............................................................................
CREATED_APPID A 0 0 NO 582984 0 NULL
B 0 0 NO 72873 0 0
CREATED_VSNID A 0 0 NO 582984 0 NULL
B 0 0 NO 72873 0 0
DATA_OBJECT_ID A 5925 .000168776 NO 535288 2 C103 C3084 47696
B 5925 .000168776 NO 66911 2 C103 C3084 5962
DEFAULT_COLLATI A 1 1 NO 471496 4 55534 55534 111488
B 1 1 NO 58937 4 55534 55534 13936
EDITIONABLE A 1 1 NO 379080 2 4E 4E 203904
B 1 1 NO 47385 2 4E 4E 25488
EDITION_NAME A 0 0 NO 582984 0 NULL
B 0 0 NO 72873 0 0
LAST_DDL_TIME A 1888 .000529661 NO 8 8 786C0 78760 582976
B 1888 .000529661 NO 1 8 786C0 78760 72872
MODIFIED_APPID A 0 0 NO 582984 0 NULL
B 0 0 NO 72873 0 0
MODIFIED_VSNID A 0 0 NO 582984 0 NULL
B 0 0 NO 72873 0 0
NAMESPACE A 23 .043478260 NO 8 4 C102 C2022 582976
B 23 .043478260 NO 1 4 C102 C2022 72872
OBJECT_ID A 73752 .000013558 NO 8 5 C103 C3084 582976
B 72872 .000013722 NO 1 5 C103 C3084 72872
SUBOBJECT_NAME A 345 .002898550 NO 576832 2 24565 57524 6152
B 345 .002898550 NO 72104 2 24565 57524 769
TIMESTAMP A 1880 .000531914 NO 8 20 31393 32303 582976
B 1880 .000531914 NO 1 20 31393 32303 72872
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
NO DIFFERENCE IN INDEX / (SUB)PARTITION STATISTICS
###############################################################################
那么如何阅读这个报告呢？可以看到报告中有当前时间源(A)和历史时间源(B)。在当前时间源A中有58万行记录，在历史时间源中有7万行记录。当然我们还能看到列上的NDV和DENSITY的信息。通过这个对比报告，我们很快就能发现究竟是哪一个数据的改变导致执行计划出现了问题。
当然，有时候可能dba_tab_stats_history表中没有任何记录，这主要由以下几个原因：
1.表上没有任何统计信息，（last_analyzed为空）
2.表上的统计信息是由其他数据库的导过来的，当前数据库没有对该表收集过统计信息。
3.使用的是Analyze命令而不是dbms_stats包来收集的。
当然还有一种情况，例如移动电信行业一般营业账务数据库都有多个中心，经常就是一个中心突然出现问题，而另外一个中心是好的。比如A中心和B中心，B中心突然出了问题。现在我们可以通过函数DIFF_TABLE_STATS_IN_STATTAB来对比两个中心的统计信息的异常，可以把A库的统计信息导到STATTAB表，然后导入到B库，在B库执行对比函数来进行对比。
FUNCTION DIFF_TABLE_STATS_IN_STATTAB RETURNS DIFFREPTAB Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- OWNNAME VARCHAR2 IN TABNAME VARCHAR2 IN STATTAB1 VARCHAR2 IN STATTAB2 VARCHAR2 IN DEFAULT PCTTHRESHOLD NUMBER IN DEFAULT STATID1 VARCHAR2 IN DEFAULT STATID2 VARCHAR2 IN DEFAULT STATTAB1OWN VARCHAR2 IN DEFAULT STATTAB2OWN VARCHAR2 IN DEFAULT
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK