一
确认问题时间段
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)