Home » Oracle » Oracle Database » How to compare statistics using history for a table in Oracle

How to compare statistics using history for a table in Oracle

  • 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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

See also  How To Export -Import TDE Master Encryption Key

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

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top