Backup Recovery Import and Export of statistics

Question: I want to understand when to export and import by dbms_stats statistics and learn when it is a good idea to export and import statistics.

Answer: importing and exporting statistics for the CBO and the systems stats (external system statistics for CPU, I/O. etc) and useful in a variety of areas:

• Export production into test to make test systems "look like" large systems for execution plan generation ".
• Export/imports can be used to control execution plans by "freezing execution plans ".
• Statistics are used as a backup before re-analyzing a schema.
• System stats can be moved to a smaller server to make it appear as if Oracle is executing on a large fast server.

You can Batch Together entire Import Export job under these scenarios:

-System stats: when migrating to a new server, you can export the old system statistics to ensure consistent execution plans until you are ready to use the "real" system stats.

-Systems reverse: conversely, You can migrate system stats from production to test to make a tiny server appear to be a larger server. this will not improve SQL Execution speed, but developers will see the same execution plans that they wocould see in production:

-Backup stats: before making any production change to the CBO stats with dbms_stats, take a full schema backup and an backup of your dbms_stats system stats. remember, the primary reason for re-analyzing stats is to change SQL Execution plans.

For example, here we export production table stats and backport them to the test database to make it appear to be a larger table:

Exec dbms_stats.create_stat_table (ownname => User, stattab => 'temp _ stat ');

Exec dbms_stats.export_table_stats (ownname => User, stattab => 'temp _ stat', tabname => 'mytable', statid => 'stats03252011 ');

FTP stats to new database by exporting the table temp_stat table

Exec dbms_stats.import_table_stats (ownname => User, stattab => 'temp _ stat', tabname => 'customer _ fact ')

Oracle recommends that you collect and save CBO statistics and swap them whenever you see a major change in workloads, such as a database that runs in OLTP mode during the day and processes Batch jobs at night. 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, 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_schema_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: copy the export file (e.g. FTP) over to the production server:

FTP-I prodserv...

Step 6: backup the existing production statistics:

Exec dbms_stats.create_stat_table (
Ownname => 'sys ',
Stattab => 'test _ stats ',-
Tblspace => 'system ');

Dbms_stats.gather_schema_stats (
Ownname => '<schema> ',
Estimate_percent => dbms_stats.auto_sample_size
Cascade => true,
Method_opt => 'for all columns size auto ')

Step 7: Delete the existing production schema stats

Exec dbms_stats.delete_schema_stats (ownname => '<schema> ');

Step 8: import the stats:

Exec dbms_stats.import_schema_stats ('test', 'stats _ table', null, 'test ');

Step 9: 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 ');

 

Simple usage for dbms_stats:

Using procedures in dbms_stats package one can backup statistics and restore them. assumes the user Scott already has access to execute dbms_stats and using "grant execute on dbms_stats to Scott;" as sysdba one can grant Execute access to dbms_stats.

-Create Table to backup statistics, ownname is statistics table owner and stattab is statistics table name
SQL> execute dbms_stats.create_stat_table (ownname = 'Scott ', stattab = 'backup _ stats ');

-Procedure to export statistics, exports statistics Scott. Test into Scott. backup_stats, cascade => true means it will export index statistics too
SQL> exec dbms_stats.export_table_stats (ownname => 'Scott ', tabname => 'test', statown => 'Scott', stattab => 'backup _ stats ', cascade => true );

-Import table stats
SQL> exec dbms_stats.import_table_stats (ownname => 'Scott ', tabname => 'test1', statown => 'Scott', stattab => 'backup _ stats ', cascade => true );

-Drop statistics table
SQL> execute dbms_stats.drop_stat_table (ownname = 'Scott ', stattab = 'backup _ stats ');

Certificate -----------------------------------------------------------------------------------------------------------------------------------------------------------------

 

10 Gb can be usedDbms_stats.restore_table_statsRestore the old statistical information of a table.

Because changes in statistical information sometimes cause program execution to deteriorate, the original statistical information is often restored. Of course, it is always a good habit to analyze the old statistical information backed up before.

Query the retention time of statistical information. Of course, this time may not be guaranteed:

SQL> select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual; GET_STATS_HISTORY_RETENTION--------------------------- 31

You can also use execute dbms_stats.alter_stats_history_retention (45) to modify the save time.

Time point at which statistics can be restored:

SQL> select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual; GET_STATS_HISTORY_AVAILABILITY----------------------------------------------------------------------20-NOV-11 10.08.13.843271000 PM +08:00

Small test:
Current statistics:

SQL> SELECT COLUMN_NAME,NUM_DISTINCT,NUM_BUCKETS, 2 HISTOGRAM,DENSITY,LOW_VALUE, 3 HIGH_VALUE,LAST_ANALYZED 4 FROM DBA_TAB_COL_STATISTICS a 5 WHERE a.TABLE_NAME = ‘T1‘ 6 and a.owner = ‘SYS‘; COLUMN_NAM NUM_DISTINCT NUM_BUCKETS HISTOGRAM DENSITY LOW_VALUE HIGH_VALUE LAST_ANALYZED---------- ------------ ----------- ---------- ---------- --------------- --------------- ----------------ID 108046 100 HEIGHT BAL .000015395 C3020104 C30C512D 2011-12-22 14:53 ANCED NAME 7775 100 HEIGHT BAL .001328021 2F3130303065386 73756E2F7574696 2011-12-22 14:53 ANCED 4315F4C696E6B65 C2F427564646869 64486173684D617 737443616C656E6 056616C75654974 46172

Before restoring to analysis:

SQL> select to_timestamp (‘2011-12-22 14:52‘,‘yyyy-mm-dd hh24:mi‘) from dual; TO_TIMESTAMP(‘2011-12-2214:52‘,‘YYYY-MM-DDHH24:MI‘)---------------------------------------------------------------------------22-DEC-11 02.52.00.000000000 PM SQL> BEGIN 2 DBMS_STATS.RESTORE_TABLE_STATS( 3 ownname => ‘SYS‘, 4 tabname => ‘T1‘, 5 as_of_timestamp => ‘22-DEC-11 02.52.00.000000000 PM‘ 6 ); 7 END; 8 / PL/SQL procedure successfully completed. SQL> SELECT COLUMN_NAME,NUM_DISTINCT,NUM_BUCKETS, 2 HISTOGRAM,DENSITY,LOW_VALUE, 3 HIGH_VALUE,LAST_ANALYZED 4 FROM DBA_TAB_COL_STATISTICS a 5 WHERE a.TABLE_NAME = ‘T1‘ 6 and a.owner = ‘SYS‘; COLUMN_NAM NUM_DISTINCT NUM_BUCKETS HISTOGRAM DENSITY LOW_VALUE HIGH_VALUE LAST_ANALYZED---------- ------------ ----------- ---------- ---------- --------------- --------------- ----------------ID 118045 100 HEIGHT BAL .000013915 C102 C30C512E 2011-12-22 14:45 ANCED NAME 11656 100 HEIGHT BAL .00104712 2F3130343866633 73756E2F746F6F6 2011-12-22 14:45 ANCED 9355F5772617054 C732F747265652F 6F6B656E5772617 5768696C6553746 0546F6B656E496E 174656D656E74

Http://www.dba-oracle.com/t_export_import_cbo_optimizer_statistics_dbms_stats.htm

Http://blog.csdn.net/tianlesoftware/article/details/4668723

Http://www.dba-oracle.com/oracle_tips_dbms_stats1.htm

Http://www.dba-oracle.com/art_builder_histo.htm

Http://www.datadisk.co.uk/html_docs/oracle/ SQL _optimization.htm

Http://www.dba-oracle.com/t_dbms_stats_gather_fixed_object_stats.htm