Oracle recommends that you collect and save CBO statistics and swap them whenever you see a major change in workloads. The dbms_stats export and import utilities are perfect for matching your statistics to a changing workload.
You can use the Oracle dbms_stats and export utilities to migrate schema statistics from your PROD instance to your TEST instance. Below are the steps:
Step 1: Create the stats_table:
exec dbms_stats.create_stat_table(
ownname => ‘SYS’,
stattab => ‘prod_stats_tb‘, – >
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_tb‘);
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_tb rows=yes
Step 5: FTP to the production server:
ftp -i prodserver
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_tb rows=yes
Step 7: We can now use the import_system_stats procedure in Oracle dbms_stats to over write the existing CBO statistics from the smaller TEST instance:
dbms_stats.import_system_stats(‘STATS_TO_MOVE’);
*************************************************************************
Our Website : https://www.ksplsoft.com/
About Us:
KSPL is a leading group of highly skilled professional group, who are having more than 10 years of experience in various technologies. We are providing support and maintenance for existing product like RDBMS, Reporting, Cloud Solutions, Mobile development and Web development etc. Also we develop new product using various technologies like cloud, Unix, Database, Reports, Mobile applications and web designing and creation. With a team of dedicated and creative engineers, we focus on mentioned work/projects you to meet the challenges with tight deadline and trust to market.
KSPLsoft 