 |
|
Exporting dbms_stats tips
Oracle Database Tips by Donald Burleson |
Export Import statistics
with dbms_stats
Exporting and importing dbms_stats are great for
several areas:
1 - Making a small test database look like a
large database to the SQL
2 - Make a cloned database behave the
same as another database.
You can use the Oracle dbms_stats and export
utilities to migrate schema statistics from your PROD instance to
your TEST instance, so that your developers will be able to do
more-realistic execution-plan tuning of new SQL before it's migrated
into PROD. Here are the steps:
Step 1: Create the stats_table:
exec
dbms_stats.create_stat_table(ownname => 'SYS', stattab => 'prod_stats',
- >
tblspace => 'SYSTEM');
Step 2: Gather the statistics with
gather_system_stats. In this dbms_stats example, we compute
histograms on all indexed columns:
DBMS_STATS.gather_schema_stats(
ownname=>'<schema>',
estimate_percent=>dbms_stats.auto_sample_size
cascade=>TRUE,
method_opt=>'FOR ALL COLUMNS SIZE AUTO')
Step 3: Export the stats to the
prod_stats table using export_system_stats::
exec
dbms_stats.export_system_stats(ownname => 'SYS', stattab => 'prod_stats');
Step 4: Export the stats to the
prod_stats table using exp:
exp scott/tiger
file=prod_stats.dmp log=stats.log tables=prod_stats
rows=yes
Step 5: FTP to the production server:
ftp -i
prodserv . . .
Step 6: Import the stats from the
prod_stats.dmp table using the import (imp) utility:
imp scott/tiger
file=prod_stats.dmp log=stats.log tables=prod_stats
rows=yes
Step 7: We can now use the
import_system_stats procedure in Oracle dbms_stats to overlay the existing
CBO statistics from the smaller TEST instance:
dbms_stats.import_system_stats('STATS_TO_MOVE');
|
|
 |
|
Get the Complete
Oracle Utility Information
The landmark book
"Advanced Oracle
Utilities The Definitive Reference" contains over 600 pages of
filled with valuable information on Oracle's secret utilities.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
|
|
|
