Oracle restoration history statistics

Statistical information is very useful. Without it, the SQL optimizer is like a clever man! A good and efficient SQL Execution Plan depends on real statistics. However, in some cases, for example, to compare the execution plans of the production environment and the test environment, the statistical information of the production environment is required. In some cases, the historical statistics of Oracle must be restored. This article describes how to restore historical statistics based on the latter and describes the scheduling of statistical information by default for 11g.

For details about how to import and export statistics, refer:
Dbms_stats Import and Export schema-level statistics
Dbms_stats import and export table statistics

 

1. Demo Environment

Sys @ MMBO> select * from v $ version where rownum <2; BANNER browse Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production -- view the last analyze time of objects on schema HR (Note: to simplify the page, some output rows are omitted, the same below) sys @ MMBO> select table_name, last_analyzed from dba_tables where owner = 'hr'; TABLE_NAME LAST_ANALYZED tables ----------------- REGIONS 20130815 18: 03: 55 LOCATIONS 20130815 18: 03: 55 minutes 20130815 18: 03: 56 JOBS 20130815 18:03:56 -- create a TABLE STATS_TABLEsys @ MMBO> exec dbms_stats.create_stat_table ('hr', 'stats _ table') for storing exported statistics; PL/SQL procedure successfully completed. -- export the schema HR statistics. sys @ MMBO> exec dbms_stats.export_schema_stats ('hr', 'stats _ table', 'hr'); PL/SQL procedure successfully completed. -- Author: Leshami -- Blog: region> exec dbms_stats.gather_schema_stats ('hr'); PL/SQL procedure successfully completed. -- after collection, the LAST_ANALYZED of the object changes to 20140307sys @ MMBO> select table_name, last_analyzed from dba_tables where owner = 'hr'; TABLE_NAME LAST_ANALYZED limit STATS_TABLE 20140307 10: 26: 31 REGIONS 20140307: 26: 30 LOCATIONS 20140307 10: 26: 30 minutes 20140307 10:26:29

2. Statistics on the timeliness and availability of information retention

-- Statistical information within 31 days can be retained. sys @ MMBO> select distinct from dual; GET_STATS_HISTORY_RETENTION ----------------------- 31 -- the longest available historical statistics is 03-FEB-14 sys @ MMBO> select distinct from dual; GET_STATS_HISTORY_AVAILABILITY---------------------------------------------------------------------------03-FEB-14 10.28.31.948055000 PM +

3. restore historical statistics

-- Use the following process to restore historical statistics, note the following demonstration is to restore schema-level historical statistics sys @ MMBO> exec dbms_stats.restore_schema_stats ('hr', sysdate-1 ); PL/SQL procedure successfully completed. -- view the LAST_ANALYZED time of the object on scheme HR after restoration, which is consistent with that before collecting statistics -- even though we specify the sysdate-1, but in fact, the last analyzed of yesterday's historical statistics is also 20130815. That is to say, for a long time, analyze has not passed sys @ MMBO> select table_name, last_analyzed from dba_tables where owner = 'hr '; TABLE_NAME LAST_ANALYZED ---------------------------- ----------------- STATS_TABLE 20140307 10: 26: 31 REGIONS 20130815 18: 03: 55 LOCATIONS 20130815 18: 03: 55 minutes 20130815 18: 03: 56 JOBS 20130815 18:03:56 -- Next we will try to import the statistical information of the previous backup sys @ MMBO> exec dbms_stats.import_schema_stats ('hr', 'stats _ table', 'hr '); PL/SQL procedure successfully completed. sys @ MMBO> select table_name, last_analyzed from dba_tables where owner = 'hr'; TABLE_NAME LAST_ANALYZED tables ----------------- STATS_TABLE 20140307 10: 26: 31 REGIONS 20130815 18: 03: 55 LOCATIONS 20130815 18: 03: 55 deployments 20130815 18: 03: 56 JOBS 20130815 18:03:56 -- collects statistics again sys @ MMBO> exec dbms_stats.gather_schema_stats ('hr'); PL/SQL procedure successfully completed. -- the statistics are refreshed to 10: 36sys @ MMBO> select table_name, last_analyzed from dba_tables where owner = 'hr'; TABLE_NAME LAST_ANALYZED hour --------------- STATS_TABLE 20140307 10: 36: 48 REGIONS 20140307 10: 36: 48 LOCATIONS 20140307 10:36:47 -- at this time, we will give you a point in time to restore historical statistics. sys @ MMBO> exec dbms_stats.restore_schema_stats ('hr ', '07-MAR-14 10:26:32 am'); PL/SQL procedure successfully completed. sys @ MMBO> select table_name, last_analyzed from dba_tables where owner = 'hr'; TABLE_NAME LAST_ANALYZED tables --------------- STATS_TABLE 20140307 10: 26: 31 REGIONS 20140307 10: 26: 30 LOCATIONS 20140307 10:26:30 -- Next we will use sysdate to restore the entire schemasys @ MMBO> exec dbms_stats.restore_schema_stats ('hr', sysdate); PL/SQL procedure successfully completed. -- according to the following query, the sysdate parameter is used to restore the sys @ MMBO> select table_name, last_analyzed from dba_tables where owner = 'hr'; TABLE_NAME LAST_ANALYZED ------------------------------------------- STATS_TABLE 20140307 10: 26: 31 REGIONS 20140307 10: 26: 30 LOCATIONS 20140307 10:26:30 -- Restore sys @ MMBO> exec dbms_stats.restore_schema_stats ('hr', '07-MAR-14 10:36:50 am') by specifying a time point again '); PL/SQL procedure successfully completed. -- the statistics are restored to the latest sys @ MMBO> select table_name, last_analyzed from dba_tables where owner = 'hr'; TABLE_NAME LAST_ANALYZED tables --------------- STATS_TABLE 20140307 10: 36: 48 REGIONS 20140307 10: 36: 48 LOCATIONS 20140307 10:36:47

4. Scheduling statistics (11 GB)

By default, Oracle defines the scheduler for collecting statistics. The scheduler about automatic collection statistics configured in Oracle 11g is listed below. The SQL script called below is originally used in Oracle Performance Diagnosis book sys @ MMBO> @ dbms_stats_job_11gsys @ MMBO> sys @ MMBO> SELECT task_name, status 2 FROM consumer 3 WHERE client_name = 'Auto optimizer stats collect'; TASK_NAME STATUS --------------- ------- gather_stats_prog ENABLEDsys @ MMBO> sys @ MMBO> PAUSEsys @ MMBO> sys @ MMBO> SELECT program_action, number_of_arguments, enabled 2 FROM dba_scheduler_programs 3 WHERE owner = 'sys '4 AND program_name = 'gather _ STATS_PROG '; PROGRAM_ACTION specified ENABLED already exist ------- defaults 0 TRUEsys @ MMBO> sys @ MMBO> PAUSEsys @ MMBO> sys @ MMBO> SELECT window_group 2 FROM dba_autotask_client 3 WHERE client_name = 'Auto optimizer stats collect '; WINDOW_GROUP--------------ORA $ AT_WGRP_OSsys @ MMBO> sys @ MMBO> PAUSEsys @ MMBO> -- The following is the default scheduling frequency sys @ MMBO> SELECT w. window_name, w. repeat_interval, w. duration, w. enabled 2 FROM dba_autotask_window_clients c, dba_scheduler_windows w 3 WHERE c. window_name = w. window_name 4 AND c. optimizer_stats = 'enabled'; WINDOW_NAME REPEAT_INTERVAL duration enabled ---------------- restart ------------- ------- WEDNESDAY_WINDOW freq = daily; byday = WED; byhour = 22; byminute = 0; + 000 04:00:00 TRUE bysecond = 0SATURDAY_WINDOW freq = daily; byday = SAT; byhour = 6; byminute = 0; + 000 20:00:00 TRUE bysecond = 0THURSDAY_WINDOW freq = daily; byday = THU; byhour = 22; byminute = 0; + 000 04:00:00 TRUE bysecond = 0TUESDAY_WINDOW freq = daily; byday = TUE; byhour = 22; byminute = 0; + 000 04:00:00 TRUE bysecond = 0SUNDAY_WINDOW freq = daily; byday = SUN; byhour = 6; byminute = 0; + 000 20:00:00 TRUE bysecond = 0MONDAY_WINDOW freq = daily; byday = MON; byhour = 22; byminute = 0; + 000 04:00:00 TRUE bysecond = 0FRIDAY_WINDOW freq = daily; byday = FRI; byhour = 22; byminute = 0; + 000 04:00:00 TRUE bysecond = 0

More references

DML Error Logging

PL/SQL --> cursor

PL/SQL --> implicit cursor (SQL % FOUND)

Batch SQL FORALL statements

Bulk collect clause for batch SQL

Initialization and assignment of PL/SQL Sets

PL/SQL Union arrays and nested tables
PL/SQL variable-length Array
PL/SQL --> PL/SQL records

SQL tuning steps

Efficient SQL statements

Parent cursor, child cursor, and shared cursor

Bind variables and their advantages and disadvantages

Use of the display_cursor function of dbms_xplan

Use of the display function of dbms_xplan

Description of each field module in the execution plan

Use explain plan to obtain the SQL statement execution PLAN