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 0set linesize 150set serveroutput on size 10000col 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, 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 noprintselect distinct piece,sql_text from v$sqltext where hash_value = :hash_value order by piece/@?/rdbms/admin/utlxplp.sqlset linesize 80set verify on feedback on pagesize 1000
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> @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. 重新分析表，收集统计信息
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
