Statistics history for Database objects in oracle.

Oracle enthusiast, knows importance of underlying stats on db objects and its prime role to decide execution plan.

Most of time during case analysis, for thorough plan change analysis we need past snapshot of table\index\column\partition stats, as many a times we involve Performance analyst post the problem was occurred.

But by that time Latest stats gathering is occur but we might be interested to check previous stats history on DB objects when problem actually occured.

As data dictionary (Dba_tables, dba_tab_columns, ….) give latest stats picture, So we find it difficult to get actual stats metric in past where actual issue must have occurred.

Below statistics history tables are very valuable to see what has changed in data and amount of data.

SYS.WRI$_OPTSTAT_TAB_HISTORY – Table stats history
SYS.WRI$_OPTSTAT_IND_HISTORY – Index Stats history
SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY – Columns Stats history
SYS.WRI$_OPTSTAT_HISTGRM_HISTORY – Histogram Stats History

When query performance suddenly goes wrong without a clear explanation due to Bad plan, you might find it in tables mentioned.

Also we can get report of stats difference for an objects, using below oracle utility.

select report from table(dbms_stats.diff_table_stats_in_history(
ownname => ‘OWNER’,
tabname => ‘TABLE_NAME’,
time1 => systimestamp,
time2 => systimestamp-30,
pctthreshold => 0));

About Deepak Mahto

Having 8+ Years of relevant/exciting/fruitful/challenging/learning in Oracle Technology. Currently working as Oracle Performance Consultant in an MNC. Hold Expertise in Performance Engineering, SQL Tuning, Database Tuning, SQL, PLSQL Development/Design Considerations and Oracle Internals. I enjoy reading on Oracle internals, posting some of my finding or observations and playing football.
This entry was posted in Oracle Internal 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