OraclePLSQL_统计新闻比比皆是08_计算音讯变更和回复

2015-02-01 Created By
BaoXinjian

Oracle 1一、摘要


计算音信在再一次生成后,发现并不可以改进度序的性质,甚至更差的时候

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. 获得解析布置的剧本

Oracle 2Oracle 3

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

 

Oracle 4二、案例


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

Oracle 5

相关文章