PLSQL_統計信息系列5_統計信息生成和還原（案例），plsql統計信息

PLSQL_統計信息系列5_統計信息生成和還原（案例），plsql統計信息

2015-02-01 Created By BaoXinjian

一、摘要

---

統計信息在重新生成後，發現並不能改善程序的性能，甚至更差的時候

Oracle提供了dbms_stat包，對統計信息進行還原

1. 還原步驟如下

Step1. Regather the stats of the tables involved in the query. 重新產生統計信息;

Step2. Check the excution plan of the SQL with explain plan. 統計信息更新後解析計劃效果;

Step3. If the plan changes back, ask to kill the current running job and re-run it. 如果效果可以，則殺掉該進程，重啟程序;

Step4. If regather doesn't work, try to restore the stats of the table whose stats are gathered recently. 如效果不行，則對計息計劃進行還原;

 

2. 獲取解析計劃的腳本

set pagesize 0 set linesize 150 set serveroutput on size 10000 col plan_table_output format a125 undefine hash_value set verify off feedback off var 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, partition_start,partition_stop,partition_id,other,distribution, cpu_cost,io_cost,temp_space,access_predicates,filter_predicates, 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,distribution, cpu_cost,io_cost,temp_space,access_predicates,filter_predicates, :hash_value,OBJECT_ALIAS,DEPTH,PROJECTION,TIME,QBLOCK_NAME from v$sql_plan where hash_value = :hash_value / col piece noprint select distinct piece,sql_text from v$sqltext where hash_value = :hash_value order by piece / @?/rdbms/admin/utlxplp.sql set linesize 80 set verify on feedback on pagesize 1000 Get Explain Plan Scripts

 

二、案例

---

Step1. 獲取Session對應的sql_id

  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;

 

Step2. 獲取解析計劃

SQL> @getplan
Enter value for hash_value: 684487124
ALTER 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. 重新分析表，收集統計信息

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；

 

Step4. 再次getplan獲取計劃，查看解析計劃是否更新，更新後的解析計劃是否正確

 

Step5. 若新的解析計劃不正確，對統計信息進行回滾

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

 

Thanks and Regards