Statistics history, revisited

Tuesday last week I did a presentation on the Planboard DBA Symposium in De Meern, the Netherlands, about SQL performance issues. The scope of my presentation was what to do when a SQL statement suddenly does not perform adequately. Often a reason for that is that something changed. Something like a sudden increase in the data or a change in statistics. As a DBA you want to know that and table/index statistcs history is usually a good way to investigate that.

I showed how to find the statistics history in SYS.WRI$_% tables and my audience told me that there actually is a regular view for that: DBA_TAB_STATS_HISTORY. I was taken aback a little. How could I have missed such an obviously named view? When I queried this view, I remembered that I found it and why I don’t use it: it contains owner, table_name columns and one with a timestamp. But I need columns like num_rows, avg_row_len. I can’t find other DBA_% views that do have real statistics history, like the WRI$_% tables.

SELECT owner, table_name, stats_update_time
FROM dba_tab_stats_history
WHERE owner='OWNER_APP'
AND table_name='ORDERS'
ORDER BY stats_update_time;

OWNER           TABLE_NAME   STATS_UPDATE_TIME
--------------- ------------ ------------------------------------
[..]
OWNER_APP       ORDERS       20-JUN-14 10.05.40.151376 PM +02:00
OWNER_APP       ORDERS       21-JUN-14 06.05.21.934080 AM +02:00
OWNER_APP       ORDERS       22-JUN-14 06.04.59.492061 AM +02:00
OWNER_APP       ORDERS       23-JUN-14 10.04.04.331964 PM +02:00

Someone in the audience also told me DBMS_STATS has a function to see the difference in statistics between to dates. The function is called DIFF_TABLE_STATS_IN_HISTORY and it delivers a complete report of changes in statistics, including table statistics, column statistics and index statistics.

select * from table(dbms_stats.diff_table_stats_in_history(
ownname => 'OWNER_APP',
tabname => 'ORDERS',
time1 => systimestamp,
time2 => systimestamp-30,
pctthreshold => 0));

At first the report didn’t look that impressive:

REPORT                                                  MAXDIFFPCT
------------------------------------------------------- ----------
#####################################################   105.156373

Then I remembered to set long on a high enough value. And I ran the statement again and got this thorough and interesting report.

set long 9999

select report from table(dbms_stats.diff_table_stats_in_history(
ownname => 'OWNER_APP',
tabname => 'ORDERS',
time1 => systimestamp,
time2 => systimestamp-30,
pctthreshold => 0));

REPORT
------------------------------------------------------------------
##################################################################

STATISTICS DIFFERENCE REPORT FOR:
.................................

TABLE         : ORDERS
OWNER         : OWNER_APP
SOURCE A      : Statistics as of 23-JUN-14 05.34.11.859991 PM +02:00
SOURCE B      : Statistics as of 24-MAY-14 05.34.11.000000 PM +02:00
PCTTHRESHOLD  : 0
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

TABLE / (SUB)PARTITION STATISTICS DIFFERENCE:
.............................................

OBJECTNAME                  TYP SRC ROWS       BLOCKS     ROWLEN     SAMPSIZE
..................................................................

ORDERS                      T   A   2812174    110694     264        2812174
                                B   2787429    110694     264        2787429
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

COLUMN STATISTICS DIFFERENCE:
.............................

COLUMN_NAME     SRC NDV     DENSITY    HIST NULLS   LEN  MIN   MAX   SAMPSIZ
..................................................................

BLOCKED         A   18302   .000054638 NO   2786649 2    78420 78740 25525
                B   19638   .000112095 YES  2760134 2    78420 78740 5478
CREATIONDATE    A   2812174 .000000355 NO   0       11   786A0 78740 2812174
                B   2787328 .000000358 NO   0       11   786A0 78740 2787429
ORDER_ID        A   1826432 .000000547 NO   26760   10   21103 29283 2785414
                B   1790080 .000000558 NO   41657   10   21103 29283 2745772
ORDER_NUMBER    A   5400    .000185185 NO   26760   5    31303 39393 2785414
                B   5400    .000185185 NO   41657   5    31303 39393 2745772
DEVICEID        A   2812174 .000000355 NO   0       6    C004  C1043 2812174
                B   2787429 .000000358 NO   0       6    C004  C1041 2787429
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

INDEX / (SUB)PARTITION STATISTICS DIFFERENCE:
.............................................

OBJECTNAME      TYP SRC ROWS    LEAFBLK DISTKEY LF/KY DB/KY CLF     LVL SAMPSIZ
..................................................................


INDEX: I_BLOCKED
.......................

I_BLOCKED        I   A   25525   125     18302   1     1     25504   1   25525
                     B   27295   117     19638   1     1     27266   1   27295

INDEX: I_CREATIONDATE
.........................

I_CREATIONDATE    I   A   2748306 990512  2748306 1     1     2748306 3   2647
                      B   3072051 1063290 2787429 1     1     3072051 3   3155

##################################################################

It’s all in there: table statistics history, history of column statistics and index statistics history.

I also discussed that I was thinking of using the dbms_stats.export_schema_stats procedure to keep my own statistics history. My audience told me that there already is such a thing. There is dbms_stats.restore_database_stats, restore_schema_stats and restore_table_stats (and more).

execute dbms_stats.restore_database_stats(sysdate-1);

execute dbms_stats.restore_schema_stats (ownname=>'OWNER_APP',AS_OF_TIMESTAMP=>sysdate-1);

execute dbms_stats.restore_table_stats (ownname=>'OWNER_APP', tabname=>'ORDERS', AS_OF_TIMESTAMP=>sysdate-1);

So that is a quite useful feature. I’ve noticed however that on tables where partitions are dropped or exchanged, you’re losing global statistics history. So maybe I’ll continue developing my own version anyway.

Advertisements

About Marcel-Jan Krijgsman

Ever since I started working with Oracle, I had an interest in Oracle database performance tuning. This led, eventually, to a four day training I made and gave for customers of Transfer Solutions. Since 2012 I work for Rabobank Nederland. A few years ago I also became interested in Oracle database security. All technology aside, it is my experience that security usually plays out on a political level. I'm a Oracle certified professional for the 8i, 9i, 10g and 11g databases and Oracle Database 11g Performance Tuning Certified Expert.
This entry was posted in Oracle performance tuning and tagged , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s