PLSQL _ Statistical Information Series 5 _ statistical information generation and restoration (CASE), plsql statistics

PLSQL _ Statistical Information Series 5 _ statistical information generation and restoration (CASE), plsql statistics

2015-02-01 Created By BaoXinjian

I. Summary

After the statistical information is re-generated, it is found that the program performance cannot be improved, or even worse.

Oracle provides the dbms_stat package to restore statistics.

1. The Restoration procedure is as follows:

Step1. Regather the stats of the tables involved in the query. generate statistical information again;

Step2. Check the excution plan of the SQL with explain plan. Analyze the plan performance after the statistical information is updated;

Step3. If the plan changes back, ask to kill the current running job and re-run it. If the effect is acceptable, kill the process and restart the program;

Step 4. If regather doesn't work, try to restore the stats of the table whose stats are gathered recently. If the effect does not work, restore the interest-bearing plan;

 

2. Get the parsing plan script

Set pagesize 0 set linesize 150 set serveroutput on size limit Col plan_table_output format a125undefine hash_valueset verify off feedback offvar hash_value varchar2 (20) begin: hash_value: = '& hash_value'; end; /insert into plan_table (statement_id, timestamp, operation, options, object_node, object_owner, object_name, optimizer, search_columns, id, parent_id, position, cost, cardinality, bytes, other_tag, response Tart, delimiter, partition_id, other, distribution, cpu_cost, io_cost, temp_space, interval, distance, plan_id, OBJECT_ALIAS, DEPTH, PROJECTION, TIME, QBLOCK_NAME) select distinct hash_value, sysdate, operation, options, object_node, object_owner, object_name, optimizer, search_columns, id, parent_id, position, cost, cardinality, bytes, other_tag, partition_start, partition_stop, partition_id, other, dist Ribution, cpu_cost, expires, temp_space, expires, expires,: hash_value, OBJECT_ALIAS, DEPTH, PROJECTION, TIME, QBLOCK_NAME from v $ SQL _plan where hash_value =: hash_value/col piece noprintselect distinct piece, SQL _text from v $ sqltext where hash_value =: hash_value order by piece /@? /Rdbms/admin/utlxplp. sqlset linesize 80 set verify on feedback on pagesize 1000Get Explain Plan Scripts

 

Ii. Case studies

Step1. obtain the SQL _id corresponding to the Session.

  SELECT   b.begin_interval_time, a.sql_id, a.plan_hash_value    FROM   dba_hist_sqlstat a, dba_hist_snapshot b   WHERE   a.snap_id = b.snap_id      AND   a.SQL_ID = '<SQL_ID>'ORDER BY   1;

 

Step 2. Obtain the resolution plan

SQL> @getplanEnter value for hash_value: 684487124ALTER DATABASE OPEN----------------------------------------------------------------------------------------------------| Id  | Operation                    | Name                | Rows  | Bytes | Cost (%CPU)| Time     |----------------------------------------------------------------------------------------------------|   0 | UPDATE STATEMENT             |                     |       |       |     1 (100)|          ||   1 |  UPDATE                      | MGMT_TARGETS        |       |       |            |          ||*  2 |   TABLE ACCESS BY INDEX ROWID| MGMT_TARGETS        |     1 |   182 |     1   (0)| 00:00:01 ||*  3 |    INDEX UNIQUE SCAN         | MGMT_TARGETS_IDX_01 |     1 |       |     0   (0)|          |----------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - filter(("LAST_LOAD_TIME"<:B2 OR "LAST_LOAD_TIME" IS NULL))   3 - access("TARGET_GUID"=:B1)

 

Step3. analyze the table again to collect statistics

BEGIN   DBMS_STATS.GATHER_TABLE_STATS (      ownname            => '<TABLE OWNER>',      tabname            => '<TABLE NAME>',      degree             => 8,      method_opt         => 'FOR ALL COLUMNS SIZE 1',      cascade            => TRUE,      estimate_percent   => 1,      GRANULARITY        => 'GLOBAL AND PARTITION',      no_invalidate      => FALSE   );END；

 

Step 4. Get the plan again by getplan and check whether the plan is updated and whether the updated plan is correct.

 

Step 5. roll back the statistics if the new resolution plan is incorrect

BEGIN  DBMS_STATS.RESTORE_TABLE_STATS('TABLE OWNER','TABLE NAME', <TIMESTAMP>, NO_INVALIDATE=>FALSE);END;

 

Thanks and Regards