Oracle Scratchpad

December 3, 2006

Saving Statistics

Filed under: Infrastructure,Statistics,Statspack — Jonathan Lewis @ 10:46 pm BST Dec 3,2006

[Further Reading on Statspack]

I see that Doug Burns  has just published an example of “reason 2″ for using Statspack

The reason for mentioning this particular posting is not specifically its reference to Statspack, it’s for the throwaway comment that Doug uses to explain how he was rapidly able to address the problem highlighted by Statspack:

Better still, because we save all of our object statistics for the CBO for at least 30 days, we could see which statistics had changed and investigate why Oracle was picking a different execution plan.

It is a little known fact (first brought to my attention in a presentation by Tim Gorman) that it is very easy to save the existing statistics when collecting new statistics. All you need to do is create a ‘stats table’ and then reference it – with some sort of identifier for the statistics you are saving – in the call to gather statistics. For example:

rem
rem	Create a stats table
rem

begin
	dbms_stats.create_stat_table(
		ownname		=> user,
		stattab 	=> 'JPL_STATS',
		tblspace	=> 'STATS_TS'
	);
end;
/

rem
rem	Extract referencing the stats table
rem	Note the last three parameters
rem

	begin
		dbms_stats.gather_table_stats(
			ownname		 => user,
			tabname		 => 'TABLE_X',
			cascade		 => true,
			estimate_percent => 0.01,
			block_sample 	 => true,
			granularity      => 'default',
			method_opt 	 => 'for all columns size 1',
			statid		 => 'JPL_' || to_char(sysdate,'yyyymmdd'),
			stattab		 => 'JPL_STATS',
			statown		 => user
		);
	end;

In fact, this technique is also mentioned and demonstrated on another of Doug Burns’ postings, So I’ll let you read that rather than creating a full example.

Footnote: 10g automatically saves the old statistics into a set of built-in stats history tables (with names like ‘%OPTSTAT%’) whenever you generate new statistics with the dbms_stats package (but misses some of them if you are still using the analyze command). This highlights an important detail to check when you upgrade. If you are already using the older ‘save stats’ method will you now be saving two sets of stats every time you collect new stats ? If so, you may want to make a strategic change to your methods – bearing in mind that the stats saved automatically in 10g will be discarded after 31 days because of the default setting of the ‘stats_retention’ parameter, which can be modified by a call to dbms_stats.alter_stats_history_retention().

[Further Reading on Statspack]

11 Comments »

  1. Jonathan,
    Thanks for the link (although I had to tidy up my formatting a bit ;-)). Your example is better than mine because you embed the date in the STATID, which is a more sensible approach to building up a history of stats, rather than just one ‘PREVIOUS’ set. That’s what we actually use at work, too.
    I am *so* glad we implemented object statistics retention. It’s proved its worth in some very tricky situations.

    Comment by Doug Burns — December 4, 2006 @ 12:04 am BST Dec 4,2006 | Reply

  2. Jonathan,
    Excellent post and that is what we are doing.
    i was just wondering if anyone has used the saved stats to do trend reporting or sort of a curve on how statistics change. i want ot use the data i have to do some meaningful analysis and was wondering if you had done so and what interesting things did you find. .

    Comment by Fuad Arshad — June 5, 2007 @ 4:45 pm BST Jun 5,2007 | Reply

  3. Just had a database where the WRI$_OPTSTAT_HISTGRM_HISTORY table was growing like mad and this post popped up at the top of a google search. Helped me identify (pretty quickly) a likely cause and solution. Thanks!

    Comment by Jeremy Schneider — November 15, 2007 @ 6:18 pm BST Nov 15,2007 | Reply

  4. Same problem as Jeremy – so did a reset of the retention period to just 16 days. Then tried to shrink the goddam table. Guess what :-)
    SQuirreL>alter table WRI$_OPTSTAT_HISTHEAD_HISTORY shrink space compact;
    ORA-10631: SHRINK clause should not be specified for this object

    Comment by Jens Kieffer-Olsen — February 15, 2008 @ 8:47 am BST Feb 15,2008 | Reply

  5. Jens,

    How odd, I wonder if that’s documented anywhere. At least you can do a move of the table and rebuild of the indexes.

    Comment by Jonathan Lewis — February 15, 2008 @ 11:44 am BST Feb 15,2008 | Reply

  6. Thanks for good example

    Comment by /zyloprim/ — February 26, 2008 @ 12:35 am BST Feb 26,2008 | Reply

  7. Thank you for the information. I had the same problem, the history stat table filling up sysaux. Following is how you set retention to 7 days & verify it:
    exec DBMS_STATS.ALTER_STATS_HISTORY_RETENTION ( 7 );
    select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;

    Comment by Hector Sanchez — November 21, 2008 @ 4:05 pm BST Nov 21,2008 | Reply

  8. Brilliant. Thanks. Found the problem!

    Comment by Andrew — December 24, 2008 @ 12:59 pm BST Dec 24,2008 | Reply

  9. Hi Jonathan,

    Can it be possible to restore stats history for just one table in Oracle 10g? Or can we view changed stats for one particular table which we are interested in?

    Thanks,
    Deepank

    Comment by Deepank — March 9, 2009 @ 2:44 pm BST Mar 9,2009 | Reply

    • Deepank

      Check the script (or pages in the PL/SQL Supplied Packages manual) that defines the dbms_stats package. There are procedures with names like restore_table_stats() that are probably the things you are interested in.

      If you just want to see older versions of stats, you can find them in tables with names like wri$_optstat_tab_history (there are probably views on top of these tables as well, but if there are I can’t think of their names right now).

      Comment by Jonathan Lewis — March 22, 2009 @ 12:46 am BST Mar 22,2009 | Reply


RSS feed for comments on this post. TrackBack URI

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

The Rubric Theme Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,508 other followers