PLSQL_统计信息系列08_统计信息生成和还原

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