- We often experience performance issues because of the inaccurate statistics for the objects involved and we want to know what the difference is between the statistics of good time and bad time
- With 12c, we can use the DBMS_STATS procedure to find the difference between the statistics
How to compare statistics using history for a table
- We have a function DIFF_TABLE_STATS_IN_HISTORY in DBMS_STATS which can be used to compare statistics for a table from two timestamps in past and compare the statistics as of that timestamps.
- The “DIFF” functions also compare the statistics of the dependent objects (indexes, columns, partitions), and display all the statistics for the object(s) from both sources if the difference between the statistics exceeds a specified threshold. The threshold can be specified as an argument to the function; the default value is 10%. The statistics corresponding to the first source will be used as the basis for computing the differential percentage.
Syntax
SQL> set long 99999
SQL> set lines 200
SQL> Set pages 120
SQL> select * from table(dbms_stats.diff_table_stats_in_history(ownname => '&owner',tabname => '&table_name', time1 => &time1, time2 => &time2, pctthreshold => &pct));
The time value can be obtained using the below query
select TABLE_NAME, STATS_UPDATE_TIME from dba_tab_stats_history where table_name='&table_name' and owner='&owner';
Example
select * from table(dbms_stats.diff_table_stats_in_history(ownname => ‘SCOTT’,tabname => ‘TEST’, time1 => ’22-JUL-20 09.22.25.000000 AM +00:00”, time2 => ’25-JUL-20 07.22.25.000000 AM +00:00′, pctthreshold => 0));
STATISTICS DIFFERENCE REPORT FOR:
……………………………
TABLE: TEST
OWNER: SCOTT
SOURCE A: Statistics as of 22-JUL-20 09.22.25.000000 AM +00:00
SOURCE B: Statistics as of 25-JUL-20 07.22.25.000000 AM +00:00
PCTTHRESHOLD : 0
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
TABLE / (SUB)PARTITION STATISTICS DIFFERENCE:
………………………………………
OBJECT NAME TYP SRC ROWS BLOCKS ROWLEN SAMPSIZE
…………………………………………………………………….
TEST T A 2000 5 7 20
B 10000 5 7 10
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
COLUMN STATISTICS DIFFERENCE:
………………………..
COLUMN_NAME SRC NDV DENSITY HIST NULLS LEN MIN MAX SAMPSIZ
…………………………………………………………………….
NAME A 20 .025 YES 0 4 61616 6C6D 20
B 10 .1 NO 0 5 61616 6C6C6 10
NO A 20 .05 NO 0 3 C102 C115 20
B 10 .1 NO 0 3 C102 C10B 10
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
INDEX / (SUB)PARTITION STATISTICS DIFFERENCE:
………………………………………
OBJECTNAME TYP SRC ROWS LEAFBLK DISTKEY LF/KY DB/KY CLF LVL SAMPSIZE
…………………………………………………………………….
INDEX: TEST_IND
……………
TEST_IND I A 20 1 20 1 1 1 0 20
B 10 1 10 1 1 1 0 10
###############################################################################
Related Articles
How to import statistics in Oracle
How to export statistics in Oracle
How to transfer statistics between databases in Oracle
How to create stat table in Oracle
How to set statistics for table in oracle