如何将当前统计信息和历史统计信息进行比较?

版权声明:本文为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

 

分享到: 更多

Post a Comment

Your email is never published nor shared. Required fields are marked *