Tuesday, May 2

Copying Oracle CBO statistics

As in most sites I work with a number of Oracle systems for each application. The production system, the DR system, the staging setup, the test & traning setup and the development system(s) etc etc.
We obviously often do database exports and import into various system, sometimes when doing imports we get funny skew statistics after the imports and a statistics gathering is required. It's not uncommon that we actually import the production schema in to perhaps two or even three development schemas and all schemas of course need these statistics.
One solution is to run dbms_stats.gather_schema_stats on each schema, but thats very time consuming.
I usually just gather the CBO stats for one schema and just copy it between schemas. After all, all freshly imported schemas are identical.

exec dbms_stats.gather_schema_stats(
ownname => 'TEST1', -
options => 'GATHER AUTO', -
estimate_percent => dbms_stats.auto_sample_size, -
degree => 3 -
)

-- Create the transport table
exec dbms_stats.create_stat_table(user, 'TRANSPORT_STATS');
-- Export stats to the newly created table
exec dbms_stats.export_schema_stats(user, 'TRANSPORT_STATS');
-- Simple exp of the table
!exp test1/test1 tables=transport_stats
-- All done, no need for this any more
drop table transport_stats;

-- Reconnect as the second schema owner
connect test2/test2
-- Vanilla imp of the table
!imp test2/test2 fromuser=test1 touser=test2
-- Import the stats from the transport table
exec dbms_stats.import_schema_stats(user, 'TRANSPORT_STATS');
-- Done! Drop the transport again.
drop table transport_stats;

Cool, each additional schema in less than 30 seconds.

1 comment:

Lili said...

That blog saved my day,

Thanks,
Lee