jump to navigation

Saving current statistics before gathering. 24 January 2011

Posted by David Alejo Marcos in Oracle 11.2, Tuning.
Tags: ,
trackback

One of the things I like the most about my job is the constant learning. Does not matter for how long you have been working with Oracle, there is always something new to learn, even from old releases.

The problem:

As part of our upgrade to 11.2.0.2 and subsequent performance degradation, it was decided to gather statistics at schema level.

As precaution, I wanted to perform a backup of the current statistics, in case the new statistics did not perform as well as expected.

In the past, I would have used DBMS_STATS.EXPORT_xxx_STATS to create a backup, where xxx is the object (database, schema, table, index, etc.)

DBMS_STATS.EXPORT_SCHEMA_STATS (
 ownname VARCHAR2,
 stattab VARCHAR2,
 statid VARCHAR2 DEFAULT NULL,
 statown VARCHAR2 DEFAULT NULL);

But that was in the past.

The solution:

Reading the 11g documentation,  I noticed a couple of parameters I never used before (From Oracle doc):

1.- stabtab: User statistics table identifier describing where to save the current statistics.

2.- statid: Identifier (optional) to associate with these statistics within stattab.

3.- statown: Schema containing stattab (if different from ownname).

To backup current statistics to a table will be as simple as:

1.- Create backup table:

SYS@xxx> begin
 2  dbms_stats.create_stat_table(
 3  ownname => 'MYTEST',
 4  stattab => 'DAVID_STATS',
 5  tblspace => 'USERS'
 6  );
 7  end;
 8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.11
SYS@xxx>

2.- Gather stats:

SYS@xxx> BEGIN
 2  DBMS_STATS.GATHER_SCHEMA_STATS (
 3   ownname =>'TEST',
 4   degree => 12,
 5   cascade => true,
 6   method_opt=>"for all column size repeat",
 7   stattab => 'DAVID_STATS',
 8   statid =>'JANUARY_110122',
 9   statown =>'MYTEST',
 10  no_invalidate =>false);
 11 END;
 12 /

PL/SQL procedure successfully completed.

Elapsed: 00:44:09.44
SYS@xxx>

if you decide to rollback statistics using your backup table as source, you will need to execute:

begin
 dbms_stats.import_schema_stats(ownname=>'TEST',
 stattab=>'DAVID_STATS',
 statown=>'MYTEST',
 statid=>'JANUARY_110122');
end;

These parameters have been there for some time now; I just never used them before as I always did a dbms_stats.import_xxx_stats and dbms_stats.export_xxx_stats. This shows me, yet again, there are always different ways to achieve the same goal.

Note.- We are all aware whenever statistics in dictionary are modified, old statistics are automatically being saved for future restoring. But having statistics being backed up on your own table will help you to keep track of them and will not be automatically deleted unless I decide to do it.

As always, comments are welcome.

Comments»

No comments yet — be the first.

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

%d bloggers like this: