Oracle DBA – A lifelong learning experience

Importing schema statistics

Posted by John Hallas on April 25, 2008

In a development environment we were generating a lot of schema builds and as more data was introduced the gather schema statistics job was taking longer

I decided to  gather statistics against a sample schema, store them and import into each new schema at the end of the build.

As user devadmin

create a table to hold the statistics

exec dbms_stats.create_stat_table(ownname => ‘devadmin’, stattab => ‘stats’);

gather schema statistics from a sample schema

execute dbms_stats.gather_schema_stats(ownname=>’DEVUSER1′,estimate_percent=>10,degree=>2,cascade=>true);

export the statistics into the stats table owned by devadmin

exec DBMS_STATS.export_schema_stats(ownname=>’DEVUSER1′,stattab=>’stats’,statown=>’DEVADMIN’);

As this is a test, ensure that no statistics are created for the new schema

exec DBMS_STATS.delete_schema_stats (ownname=>’DEVUSER2′);

Now apply the statistics from the stored table to the new schema

exec DBMS_STATS.import_schema_stats(ownname=>’DEVUSER2′,stattab=>’stats’,statown=>’DEVADMIN’);

The gather schema statistics was taking at least 2 minutes and sometime more depending upon how busy the server was and this was for a small development schema (20Mb). Importing schema statistics took no longer than 10 seconds.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your 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: