查看oracle 历史执行计划（根据awr的snap_id）

来源：这里教程网时间：2026-03-03 20:34:26 作者：

一确认问题时间段 2014年9月23日 19:42 通过以下语句把昨晚ash报告中有问题的执行计划导出来 select sql_id,plan_hash_value,SNAP_ID from dba_hist_sqlstat where sql_id='42fc3fw90t4sk' and SNAP_ID between 18765 and 18766 order by SNAP_ID; select sql_id,plan_hash_value,SNAP_ID from dba_hist_sqlstat where sql_id='8wa7cgcpd019z' and SNAP_ID between 18765 and 18766 order by SNAP_ID; select sql_id,plan_hash_value,SNAP_ID from dba_hist_sqlstat where sql_id='gxfd8aggfd6pf' and SNAP_ID between 18765 and 18766 order by SNAP_ID; 根据以上查询出来的sql_id通过以下找到在18765 and 18766快照时间段内的执行计划 select * from table(dbms_xplan.display_awr('******')); select * from table(dbms_xplan.display_cursor('&SQL_ID',&child_number)); SQL Plan Management (SPM) Frequently Asked Questions (Doc ID 1524658.1) How to Get Execution Plans and Execution Elapsed Times for a Refresh Generated Recursive SQL (Doc ID 1315632.1) 某保险公司 select sql_id,plan_hash_value,SNAP_ID from dba_hist_sqlstat where sql_id='1pamdbsybqt64' and SNAP_ID between 25868 and 25868 order by SNAP_ID; 短信系统awr执行计划 select * from table( DBMS_XPLAN.DISPLAY_AWR('634p3yhu8d9aj',NULL,NULL, 'ALL')); select sql_id,plan_hash_value,SNAP_ID from dba_hist_sqlstat where sql_id='dq5hm0mpsx0x2'; select sql_id,plan_hash_value,SNAP_ID from dba_hist_sqlstat where sql_id='8mdn1hnm4rzat'; select sql_id,plan_hash_value,SNAP_ID from dba_hist_sqlstat where sql_id='0b4vmh6cxx6c1'; ------------------------------- 历史session信息 select SESSION_ID,count(*) from v$active_session_history select min(sample_time), max(sample_time) from v$active_session_history; col END_INTERVAL_TIME for a30 set line 160 select END_INTERVAL_TIME,sum(EXECUTIONS_DELTA) from dba_hist_sqlstat t1,dba_hist_snapshot t2 where t1.snap_id=t2.snap_id and plan_hash_value='1618008348' and END_INTERVAL_TIME>=sysdate-10 group by END_INTERVAL_TIME order by 1; END_INTERVAL_TIME SUM(EXECUTIONS_DELTA) ------------------------------ --------------------- 02-OCT-15 09.00.27.732 AM 13 02-OCT-15 11.00.10.339 AM 97 .. 03-OCT-15 10.00.14.862 AM 38 .. 03-OCT-15 12.00.01.642 PM 74 03-OCT-15 01.00.06.360 PM 26 .. 04-OCT-15 09.00.31.120 AM 12 .. 04-OCT-15 07.00.16.060 PM 3 04-OCT-15 07.00.16.161 PM 3 05-OCT-15 09.00.07.185 AM 29 05-OCT-15 09.00.07.265 AM 29 .. 06-OCT-15 09.00.05.343 AM 18 06-OCT-15 09.00.05.458 AM 18 06-OCT-15 10.00.09.288 AM 52 06-OCT-15 10.00.09.389 AM 52 06-OCT-15 11.00.03.984 AM 76 .. 07-OCT-15 11.00.27.014 AM 105 07-OCT-15 11.00.27.242 AM 105 07-OCT-15 12.00.36.772 PM 71 07-OCT-15 12.00.36.951 PM 71 07-OCT-15 01.00.01.717 PM 37 .. 08-OCT-15 09.00.32.016 AM 65 08-OCT-15 09.00.32.334 AM 65 08-OCT-15 10.00.02.879 AM 200 08-OCT-15 10.00.03.046 AM 200 08-OCT-15 11.00.20.101 AM 224 08-OCT-15 11.00.20.391 AM 224 08-OCT-15 12.00.14.853 PM 230 08-OCT-15 12.00.15.071 PM 230 08-OCT-15 01.00.21.342 PM 78 08-OCT-15 01.00.21.694 PM 78 .. 08-OCT-15 09.00.14.070 PM 1 08-OCT-15 10.00.18.266 PM 1 08-OCT-15 10.00.18.413 PM 1 问题时间段

08-OCT-15 10.00.02.879 AM 200

08-OCT-15 10.00.03.046 AM 200

08-OCT-15 11.00.20.101 AM 224

08-OCT-15 11.00.20.391 AM 224

08-OCT-15 12.00.14.853 PM 230

08-OCT-15 12.00.15.071 PM 230 根据sql_id查看对应不同的执行计划（plan_hash_value） ---select sql_id,plan_hash_value,snap_id from dba_hist_sqlstat where sql_id='&:sql_id' and snap_id between and order by snap_id;start $ORACLE_HOME/rdbms/admin/awrsqrpt.sql 使用sta优化建议创建优化建议任务 variable sts_task varchar2(64); exec:sts_task:=dbms_sqltune.create_tuning_task(task_name=>'sts_tune_tune1',sql_id=>'9dbq3dpu2mfjh') 执行优化建议 exec dbms_sqltune.execute_tuning_task('sts_tune_tune1'); 查看优化报告 select dbms_sqltune.report_tuning_task('sts_tune_tune1') from dual; ---task_name=>'sts_tune_tune1',接收优化建议 exec dbms_sqltune.accept_sql_profile(task_name=>'sts_tune_tune1',replace=> true)