watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=

          subject         part

stay Oracle in , How to query historical statistics of tables and indexes ?


     
          Answer section          



from Oracle 10g Start , When collecting table statistics , The old statistics are retained , If there is a performance problem due to new statistics , Old statistics can be recovered . The statistics are stored in the following tables :

l WRI$_OPTSTAT_TAB_HISTORY Table statistics

l WRI$_OPTSTAT_IND_HISTORY Index statistics

l WRI$_OPTSTAT_HISTHEAD_HISTORY Column Statistics

l WRI$_OPTSTAT_HISTGRM_HISTORY Histogram information

From view DBA_TAB_STATS_HISTORY You can query the history of the time to collect statistics , But we can't find the number of rows , So we need to combine the base table to query , Of the query SQL The statement is as follows :

 1SELECT B.OWNER,
 2       B.OBJECT_NAME TABLE_NAME, 
 3       TO_CHAR(D.ANALYZETIME, 'YYYY-MM-DD HH24:MI:SS') LAST_ANALYZETIME,
 4       TO_CHAR(D.SAVTIME, 'YYYY-MM-DD HH24:MI:SS') CURR_ANALYZETIME,
 5       D.ROWCNT
 6  FROM SYS.WRI$_OPTSTAT_TAB_HISTORY D, DBA_OBJECTS B
 7 WHERE D.OBJ# = B.OBJECT_ID
 8   AND B.OBJECT_NAME IN
 9       ('TEST_STAT', 'TPCCBOKBAL_TMP', 'TPCCBOKBALJN', 'PK_TPCCBOKBAL')
10 ORDER BY D.OBJ#, D.SAVTIME;
     

watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=

Query the historical statistics of the index SQL The statement is as follows :

 1SELECT B.OWNER,
 2       B.OBJECT_NAME INDEX_NAME,
 3       TO_CHAR(D.ANALYZETIME, 'YYYY-MM-DD HH24:MI:SS') LAST_ANALYZETIME,
 4       TO_CHAR(D.SAVTIME, 'YYYY-MM-DD HH24:MI:SS') CURR_ANALYZETIME,
 5       D.ROWCNT,
 6       D.BLEVEL,
 7       D.LEAFCNT,
 8       D.DISTKEY,
 9       D.CLUFAC
10  FROM SYS.WRI$_OPTSTAT_IND_HISTORY D, DBA_OBJECTS B
11 WHERE D.OBJ# = B.OBJECT_ID
12   AND B.OBJECT_NAME IN ('IND_TEST')
13 ORDER BY D.OBJ#, D.SAVTIME;
     

watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=

By default, statistics will be retained 31 God , You can use the following command to modify :

1EXECUTE DBMS_STATS.ALTER_STATS_HISTORY_RETENTION (XX);  --xx Is the number of days to keep 
     


Be careful : These statistics are in SYSAUX Extra storage overhead in the table space , So you should pay attention to and prevent statistics from filling up the table space .

1SELECT DBMS_STATS.GET_STATS_HISTORY_RETENTION FROM DUAL;-- Query the number of days the statistics are currently held .
     


The following query returns the date to which the statistics have been deleted ( So only statistics after this date can be recovered ). Any request to recover statistics older than this date will fail :“ORA-20006: Unable to restore statistics , statistics history not available”:

1SELECT TO_CHAR(DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY,'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
     

After the query can recover the statistical information to a better time , You can execute the following command to recover :

1EXECUTE DBMS_STATS.RESTORE_TABLE_STATS ('OWNER','TABLE',DATE);-- Recover table statistics
2EXECUTE DBMS_STATS.RESTORE_DATABASE_STATS(DATE);-- Recover statistics from the database
3EXECUTE DBMS_STATS.RESTORE_DICTIONARY_STATS(DATE);-- Recover statistics from the data dictionary
4EXECUTE DBMS_STATS.RESTORE_FIXED_OBJECTS_STATS(DATE);-- Recover fixed table statistics
5EXECUTE DBMS_STATS.RESTORE_SCHEMA_STATS('OWNER',DATE);-- Recover a user's statistics
6EXECUTE DBMS_STATS.RESTORE_SYSTEM_STATS(DATE);-- recovery SYSTEM Statistical information 
     

It can be returned by the following command 2 The results of the comparison of statistical information :

1SELECT *
2  FROM TABLE(DBMS_STATS.DIFF_TABLE_STATS_IN_HISTORY(OWNNAME => 'TPLHR',
3                                                    TABNAME => 'TPLHR_AB',
4                                                    TIME1   => TO_TIMESTAMP('2016-09-07 10:24:45','YYYY-MM-DD HH24:MI:SS'),--SYSTIMESTAMP
5                                                    TIME2   => TO_TIMESTAMP('2016-09-07 10:29:22','YYYY-MM-DD HH24:MI:SS')));
     

give the result as follows :

 1###############################################################################
 2STATISTICS DIFFERENCE REPORT FOR:
 3.................................
 4TABLE         : TPLHR_AB
 5OWNER         : TPLHR
 6SOURCE A      : Statistics as of 07-SEP-16 10.24.45.000000 AM +08:00
 7SOURCE B      : Statistics as of 07-SEP-16 10.29.22.000000 AM +08:00
 8PCTTHRESHOLD  : 10
 9~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
10NO DIFFERENCE IN TABLE / (SUB)PARTITION STATISTICS
11~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
12COLUMN STATISTICS DIFFERENCE:
13.............................
14COLUMN_NAME     SRC NDV     DENSITY    HIST NULLS   LEN  MIN   MAX   SAMPSIZ
15...............................................................................
16CREDITAMT       A   1143    .000874890 NO   0       3    80    C12A2 3.9E+07
17                B   1278    .000782472 NO   0       3    80    C50D3 3.9E+07
18CURRBALANCE     A   2478080 .000424268 YES  0       5    3D475 C60F5 5557   
19                B   2477312 .000589622 YES  0       5    3D475 C60F5 5508   
20DEBITAMT        A   114     .008771929 NO   0       3    80    C1021 3.9E+07
21                B   116     .008620689 NO   0       3    80    C1022 3.9E+07
22LASTBALANCE     A   2476288 .000424268 YES  0       5    3D475 C60F5 5557   
23                B   2476288 .000589622 YES  0       5    3D475 C60F5 5508   
24~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
25NO DIFFERENCE IN INDEX / (SUB)PARTITION STATISTICS
26###############################################################################