More statistics history

After writing about table statistics, I tried my query to retrieve them many times. Soon I found out, I couldn’t find index statistics data. After a while I figured it out. Duh! The table is called WRI$_OPTSTAT_TAB_HISTORY. So I quickly went looking for other WRI$_OPTSTAT_ tables and found them:

WRI$_OPTSTAT_IND_HISTORY – contains index statistics history, including the row count, blevels, leaf count, distinct keys and clustering factor. And not to mention the sample size.

WRI$_OPTSTAT_HISTHEAD_HISTORY – contains column statistics, like the null count, the density, low values and high values, the number of distinct values in the sample and so on. Here also the sample size, which I have seen varying in some cases of performance issues with column statistics.

WRI$_OPTSTAT_HISTGRM_HISTORY – contains a history of histogram data. You’ll find colums like bucket, endpoint, epvalue. I haven’t tried this one out, for fear of getting overwelmed with data. But if ever you suspect wild changes in the number of buckets, you might want to look the data up here.

WRI$_OPTSTAT_AUX_HISTORY – If you regularely gather system statistics, I’ll assume you will find older versions of those statistics here.

There are more WRI$_OPTSTAT_ tables, for which I haven’t found a use yet:
WRI$_OPTSTAT_OPR
WRI$_OPTSTAT_SYNOPSIS_PARTGRP
WRI$_OPTSTAT_SYNOPSIS_HEAD$
WRI$_OPTSTAT_SYNOPSIS$

I wrote two simple scripts to gather index and column statistics history: index_stats_history.sql and column_stats_history.sql.

These statistics history tables are very valuable to see what has changed in data and amount of data. When query performance suddenly goes wrong without a clear explanation, you might find it here. Enjoy!

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, Oracle scripts 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