Recovery and backup of oracle statistics
Oracle statistical information recovery and backup, in response to this problem, this article details the corresponding analysis and solution, hoping to help more small partners who want to solve this problem find a simpler and easier way.
oracle 11g adds backup and restore capabilities for system information at the system level, user level, and table level! When we collect statistics on oracle system objects, in order to prevent the execution plan after the new statistics is not as good as the previous one, we can use
dbms_stats.export_(database/schema/table)_stats Export statistics to created tables
dbms_stats.import_(database/schema/table)_stats Import statistics into the system
To restore statistics!
Backup and recovery of statistical information includes the following levels
1)Database statistics backup recovery (can only be under sys user)
exec dbms_stats.create_stat_table('sys','stat_sys');
exec dbms_stats.export_database_stats('stat_sys');
exec dbms_stats.import_database_stats('stat_sys');
2)Backup and restore of statistics for schema (only under the schema owner's user, yang is the username)
exec dbms_stats.create_stat_table('yang','stat_2');
exec dbms_stats.export_schema_stats('yang','stat_2');
exec dbms_stats.import_schema_stats('yang','stat_2');
3)Statistics backup restore for tables (only under users of table owner)
exec dbms_stats.create_stat_table('yang','stat_3');
exec dbms_stats.export_table_stats('yang','T1',null,'stat_3');
exec dbms_stats.import_table_stats('yang','T1',null,'stat_3');
Here's a test for the user level!
1 Create a table for statistics backup
yang@yangdb> exec dbms_stats.create_stat_table('yang','stat_4');
PL/SQL procedure successfully completed.
2 for user yang statistics, and statistical information exported to the table inside!
yang@yangdb> begin
2 dbms_stats.gather_schema_stats(
3 ownname => 'yang',
4 estimate_percent => 100,
5 method_opt => 'for all columns size auto',
6 degree => 2);
7 end;
8 /
PL/SQL procedure successfully completed.
yang@yangdb> select table_name,last_analyzed from user_tables where table_name='T2';
TABLE_NAME LAST_ANALYZED
------------------------------ ------------------
T2 27-FEB-12
yang@yangdb> exec dbms_stats.export_schema_stats('yang','stat_4');
PL/SQL procedure successfully completed.
3 Delete previous statistics and query validation
yang@yangdb> exec dbms_stats.delete_schema_stats('yang');
PL/SQL procedure successfully completed.
yang@yangdb> select table_name,last_analyzed from user_tables where table_name='T2';
TABLE_NAME LAST_ANALYZED
------------------------------ ------------------
T2
4 Reimport statistics from previous backups
yang@yangdb> exec dbms_stats.import_schema_stats('yang','stat_4');
PL/SQL procedure successfully completed.
Query the statistical analysis time of t2 table under yang user!
yang@yangdb> select table_name,last_analyzed from user_tables where table_name='T2';
TABLE_NAME LAST_ANALYZED
------------------------------ ------------------
T2 27-FEB-12
Oracle statistics on the recovery and backup of the answer to the question shared here, I hope the above content can be of some help to everyone, if you still have a lot of doubts not solved, you can pay attention to the industry information channel to learn more related knowledge.
