OraclePLSQL_统计信息比比皆是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.
如效果挺,则针对计息计划开展回复;

 

  1. 取解析计划的剧本

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

相关文章