Saving current statistics before gathering. 24 January 2011Posted by David Alejo Marcos in Oracle 11.2, Tuning.
Tags: Oracle 11.2, Tuning
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.
As part of our upgrade to 18.104.22.168 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.
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.