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 would 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 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 => 'back_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');
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
10G中可以使用dbms_stats.restore_table_stats恢复表老的统计信息，这功能还是挺不错的。
因为统计信息的变化有时会引起某些语句执行计划变差，这时恢复原有统计信息，常常会有效果。当然分析前备份老的统计信息，始终是个好习惯。
查询统计信息保存时间，当然这个时间不一定能保证：
SQL> select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;
GET_STATS_HISTORY_RETENTION
---------------------------
31
|
也可以通过execute DBMS_STATS.ALTER_STATS_HISTORY_RETENTION (45) 来修改这个保存时间。
查询统计信息能恢复到哪个时间点：
SQL> select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual;
GET_STATS_HISTORY_AVAILABILITY
----------------------------------------------------------------------
20-NOV-11 10.08.13.843271000 PM +08:00
|
小测试：
当前统计信息：
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
|
恢复到分析之前：
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
|
The Advantage of DBMS_STATS PACKAGE RATHER THAN Analyze command
if you gathered the statistics for a table and caused the wrong execution plan , then you have to rollback your operation.
if you didn't backup the statistics via dbms_stats.export_table_stats, you still can have a survive if you used dbms_stats package to gather
the statistics are on 10G. The command 'Analyze' will not have this ability as only the dbms_stats package will try to backup the old statistics
when ganther a new statistics. So you'd better use dbms_stats.export_table_stats to backup statistics before gathering statistics.
Beginning with Oracle10G when statistics are gathered for a table the old statistics are retained so that should There be any problem with performance of queries dependent on those statistics, the old ones can be restored.
How long does Oracle retain the statistics for ?
The default period for which statistics are retained is 31 days but this can be altered with:
execute DBMS_STATS.ALTER_STATS_HISTORY_RETENTION (xx) -
where xx is the number of days to retain them
NOTE: There is an overhead of storage in the SYSAUX tablespace with statistics so care should be taken not to cause the tablespace to fill with the statistics
How do I know how many days the statistics are available for ?
select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual; --will return the number of days stats are currently retained for.
select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual;--will return the oldest possible date stats can be restored from
How do I find the statistics history for a given table ?
select TABLE_NAME, STATS_UPDATE_TIME from dba_tab_stats_history;--Will show the times statistics were regathered for a given table.
select table_name, stats_update_time from user_tab_stats_history;--all_tab_stats_history/dba_tab_stats_history
select index_name, num_rows, last_analyzed from user_indexes where index_name = 'IND_WANGWEIFENG';
How do I restore the statistics ?
Having decided what date you know the statistics were good for, you can use:-
execute DBMS_STATS.RESTORE_TABLE_STATS
(’owner’,'table’,date)
execute
DBMS_STATS.RESTORE_DATABASE_STATS(date)
execute
DBMS_STATS.RESTORE_DICTIONARY_STATS(date)
execute
DBMS_STATS.RESTORE_FIXED_OBJECTS_STATS(date)
execute
DBMS_STATS.RESTORE_SCHEMA_STATS(’owner’,date)
execute
DBMS_STATS.RESTORE_SYSTEM_STATS(date)
example
execute dbms_stats.restore_table_stats (’SCOTT’,'EMP’,'25-JUL-07 12.01.20.766591 PM +02:00′);
D:appAdministratorproduct11.2.0dbhome_1RDBMSADMINdbmsstat.sql --create package dbms_stats.
D:appAdministratorproduct11.2.0dbhome_1RDBMSADMINcatost.sql --create the static views for statistic
refference:
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