How To Compare Current Stats With Stats From History?

Imagine this. You get a call. A specific SQL statement is running slow today, and the statement was running fine yesterday.
You quickly review the SQL statement, apply the steps from my troubleshooting guide, and determine the cause of the slowness is an execution plan change.

What is your next question?

I would ask, why did the plan change?

Most of the times, the plan change is caused by new statistics in the database. Wouldn’t it be awesome to determine quickly if the stats have changed, and what exactly has changed?

Of course it would!

Last week we discussed that anytime you gather new statistics, the current set of stats is saved in the SYSAUX tablespace in the WRI$% tables.

Oracle provides a tool to compare the current statistics with the ones saved in the history. You can also compare the current statistics with statistics saved in the stat table, or with pending statistics (stats that were gathered but not published yet).

To run the comparison, you need to use dbms_stats package.

Let’s see how this works.

The test case is setup on the SCOTT.EMP table. Stats were gathered a few times on the table, as you will see in the example.

When you want to compare the stats with previous ones, you need to provide the timestamps for the comparison. Usually time1 is the current time, and time2 is the point in time in the past, you do the comparison against.
However both time1 and time2 could be in the past.
This process, involves 2 steps. First find out what statistics you have available, and second, do the comparison.

Let’s see it in action!

1. Check the availability of the stats for the table: (hint, query dba_tab_stats_history)

select table_name,stats_update_time from dba_tab_stats_history where owner='SCOTT' and table_name='EMP';

TABLE_NAME	STATS_UPDATE_TIME
--------------- -------------------------------------
EMP		28-NOV-17 07.38.41.840467 PM -08:00
EMP		28-NOV-17 07.47.19.102063 PM -08:00

2. Compare the stats: (hint, use dbms_stats.diff_table_stats_in_history)

set long 2000000
set pagesize 1000

select * from table(dbms_stats.diff_table_stats_in_history(
                    ownname => 'SCOTT',
                    tabname => 'EMP',
                    time1 => systimestamp,
                    time2 => to_timestamp('28-NOV-17 07.47.19 PM','DD-MON-YY hh12.mi.ss AM'),
                    pctthreshold => 0));  

How to read this report, and what to look out for?

As you can see, in this report, at the current time (Source A), there were 24 rows in the table. At time Source B, there were 19 rows in the table.
This procedure would provide more value, the more data you have in the table.
The neat thing is that you can set a threshold, and generate the report if the threshold is met.
In this example, I set the threshold to 0, which means generate a report even if no changes in stats. You can set the threshold to 5%, meaning only generate the report if the stats have changed by 5% or more. That is pretty cool.

You can also get some basic historical data from WRI$_OPTSTAT_TAB_HISTORY to review. But this table contains only the historical data, and not the current statistics:

select savtime, rowcnt, blkcnt, avgrln,samplesize 
from WRI$_OPTSTAT_TAB_HISTORY
where obj#=(select object_id from dba_objects where owner='SCOTT' and object_name='EMP');

SAVTIME 				ROWCNT	   BLKCNT     AVGRLN     SAMPLESIZE
--------------------------------------  ---------- ---------- ---------- ----------
28-NOV-17 07.38.41.840467 PM -08:00	14		5	  38	     14
28-NOV-17 07.47.19.102063 PM -08:00	19		5	  36	     19

A question that comes to my mind is, why certain tables have no entries in the dba_tab_stats_history view?

There could be a few possible answers why:

• table has no statistics (last_analyzed is null)
• table was imported with statistics from a different database, thus the statistics were not saved in your current database.
• table stats were gathered with the ANALYZE command, instead of dbms_stats package

An interesting application of the above statements would be to create a report for the tables who’s stats have changed by more than 5%. I might just create that!

If you enjoyed this article, and would like to learn more about databases, please sign up below, and you will receive
The Ultimate 3 Step Guide To Find The Root Cause Of The Slow Running SQL!

–Diana