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