Table and index statistics history

As a performance tuning DBA I regularely get questions about query performance gone bad. Often you get to hear that everyone was minding their own business and certainly did not change the application code or anything, when suddenly, from one day to the other certain screens took minutes to load in stead of milliseconds.

On performance tuning trainings I tell my students that my attitude in these performance investigations is somewhat like that of Dr. House (from the House MD series). Not that I call everyone a moron like House does, but certainly I don’t take anything for granted. Dr. House says “Everybody lies”. I don’t think people blatantly lie about what preceded, but the truth is often out there. “Nothing was changed and the performance went bad”? I often doubt that. Something has changed, whether it’s the application code or database objects or data multiplied overnight. Not to mention changed statistics. In Oracle 10g R2 I had numerous occasions where dbms_stats’ automatic ways suddenly decided that it was time to have a sample size of only 0.4% or so. So check what changed everywhere – if you can.

But how do you check for changes in the application. While DBA’s often can’t check the application itself, you can check changes in the data model, packages, etc.. As a crude first investigation I use this query:

select owner,object_type, object_name, last_ddl_time
from dba_objects
where owner not in ('SYS', 'SYSTEM', 'EXFSYS', 'ORACLE_OCM')
order by last_ddl_time desc;

----------- ------------ -------------- -------------------
APP_OWNER   PACKAGE      PCK_APP        27-01-2013:13:33:44
APP_OWNER   TABLE        CHANGES        11-01-2013:13:33:44
APP_OWNER   INDEX        I1_CHANGES     11-01-2013:13:33:39
APP_OWNER   INDEX        TEST1          18-12-2012:14:40:21
APP_OWNER   VIEW         V_INCIDENTS_2  04-12-2012:21:59:05
APP_OWNER   VIEW         V_INCIDENTS_1  04-12-2012:21:59:05

I’ve investigated a case of bad performance once. It was for a customer of my former employer, Transfer Solutions. The customer assured me that “nothing had changed”, but with above query I quickly found changes in lots of packages, views, etc.. from the night before. Did the customer “confess” the changes? No. He was completely unaware that another departement in his organization had done an update of their part of the application. It’s how these things go. He did vow to talk with the other departments to share information about changes to the application from then on.

There is however another category of changes that you’ll won’t detect with above query. And that is when the volume of data has changed. Or the average row length has changed. I didn’t think there was such a thing as statistics history until I ran a recent version of SQLT (or SQLTxplain). That is a highly recommended tool found on the Oracle support site (ID 215187.1). You feed SQLT a query or SQL_ID and it generates loads and loads of data concerning the tables the query does access. And indexes, statistics, execution plans, etc., etc..

Amongst the data is a list of statistics history of tables and indexes. I’ve racked my brain where this data could have come from. I don’t want to install and run SQLT on every database, but I do want that statistics history. At a certain point it became an obsession. Collegues told me it could not be done. And yet.. and yet. SQLT could do it.

I tried to reverse engineer SQLT, but I didn’t quite find it. I might have given up the search, was it not for a bug I encountered with AWR in an Oracle database. I started a SR, and after a while the bug seemed to concern rows in sys.WRI$_OPTSTAT_TAB_HISTORY. Normally that table is automatically cleaned after (default) 31 days, but for some reason not in this particular database. Long story slightly shorter: I looked in that table and lo and behold: table and index statistics history. With the following query you can retrieve it:

SELECT ob.owner, ob.object_name, ob.object_type, rowcnt, avgrln ,samplesize, analyzetime
FROM sys.WRI$_OPTSTAT_TAB_HISTORY, dba_objects ob
WHERE owner=upper('&OWNER')
and object_name=upper('&TABLE')
and object_type in ('TABLE')
and object_id=obj#
order by savtime asc;

---------- ------------ ----------- ------- ------- ---------- -----------
APP_OWNER  PROBLEMS     TABLE       368470    2428   36847     19-01-2013
APP_OWNER  PROBLEMS     TABLE       369510    2426   36951     20-01-2013
APP_OWNER  PROBLEMS     TABLE       369000    2426   36900     21-01-2013
APP_OWNER  PROBLEMS     TABLE       368710    2427   36871     22-01-2013
APP_OWNER  PROBLEMS     TABLE       371500    2426   37150     23-01-2013
APP_OWNER  PROBLEMS     TABLE       373730    2427   37373     24-01-2013
APP_OWNER  PROBLEMS     TABLE       376700    2428   37670     25-01-2013
APP_OWNER  PROBLEMS     TABLE       373160    2426   37316     26-01-2013
APP_OWNER  PROBLEMS     TABLE       414450    2424   8312      27-01-2013

I’d say that’s pretty useful.

[Update November 12th 2013]: I’ve created a script that can give you table/index/column statistics history, based on a SQL_ID (actually based on the execution plan of that SQL_ID). The script is called sqlperf.sql and you can read more about it here.



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.

2 Responses to Table and index statistics history

  1. Pingback: sqlperf_noawr.sql for the Diagnosics Pack impaired | Marcel-Jan's Oracle Blog

  2. A. Sadeghzadeh says:

    Good Article !

Leave a Reply

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

You are commenting using your 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