转://利用从awr中查找好的执行计划来优化SQL
原文地址：http://blog.csdn.net/zengxuewen2045/article/details/53495613
同事反应系统慢，看下是不是有锁了，登入数据库检查，没有异常锁定，但发现有支SQL有30个会话执行，且出现异常等待事件：read by other session和db file sequential read.
这两个等待事件描述应该是这样的：
会话a在进行把磁盘上的数据块读到内存（data buffer cache）中这个操作，
会话b和会话c 同时也请求这个数据块。因为会话a还未完全读入内存(data buffer cache)，就导致了b，c read by other session
所以会话a一般是db file sequential read 或 db file scattered read。
这个是Oracle 10g 从oracle 9i的buffer busy waits中分离出来的，也是一种热块现象。
SQL> select count(*) from gv$session where sql_id='bpgqf9b2ykqmu';
COUNT(*)
----------
30
SQL> select distinct event from gv$session where sql_id='bpgqf9b2ykqmu';
EVENT
----------------------------------------------------------------
db file sequential read
read by other session
通过select * from table(dbms_xplan.display_cursor('bpgqf9b2ykqmu',''))查不到任何计划，再查awr：select * from table(dbms_xplan.display_awr('bpgqf9b2ykqmu','')),
执行计划如下：
PLAN_TABLE_OUTPUT
SQL_ID b93dmn0vjqyfn, child number 0
-------------------------------------
PLAN_TABLE_OUTPUT
SQL_ID bpgqf9b2ykqmu
--------------------
SQL省略
Plan hash value: 1664736063
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 1690K(100)| |
| 1 | LOAD TABLE CONVENTIONAL | | | | | |
| 2 | FILTER | | | | | |
| 3 | FILTER | | | | | |
| 4 | TABLE ACCESS BY INDEX ROWID | SFC_WIP_LOG | 122 | 26108 | 1690K (1)| 05:38:06 |
| 5 | INDEX RANGE SCAN | IDX_SFC_WIP_LOG_IX9 | 2245K| | 83695 (1)| 00:16:45 |
| 6 | COLLECTION ITERATOR PICKLER FETCH| | 1 | 2 | 29 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
SQL_ID bpgqf9b2ykqmu
--------------------
SQL省略
Plan hash value: 3882290683
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 5 (100)| |
| 1 | LOAD TABLE CONVENTIONAL | | | | | |
| 2 | FILTER | | | | | |
| 3 | FILTER | | | | | |
| 4 | TABLE ACCESS BY INDEX ROWID | SFC_WIP_LOG | 1 | 214 | 5 (0)| 00:00:01 |
| 5 | INDEX RANGE SCAN | IDX_SFC_WIP_LOG_IX6 | 1 | | 4 (0)| 00:00:01 |
| 6 | COLLECTION ITERATOR PICKLER FETCH| | 1 | 2 | 29 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
上面出现两个执行计划，在于选择的索引不同，索引IDX_SFC_WIP_LOG_IX6比IDX_SFC_WIP_LOG_IX9的选择性要好。
采用下面方法绑定，并没有生效，因为执行计划是在awr中，并非在cursor中。
DECLARE
-- Local variables here
i INTEGER;
BEGIN
i := dbms_spm.load_plans_from_cursor_cache(sql_id=>'bpgqf9b2ykqmu',
plan_hash_value => '3882290683');
END;
先查看awr中好的执行计划及相关信息：
set line 1000
set pagesze 1000
col inst_snapid_sqlid_plan for a50
col Date_Time for a20
col executions_delta for 999999
col avg_etime for 99999
col avg_cputime for 99999
col avg_lio for 99999
col avg_pio for 99999
col avg_row for 99999
SELECT
SQL.instance_number||','||s.snap_id||','||sql_id||','||PLAN_HASH_VALUE inst_snapid_sqlid_plan,
to_char(s.BEGIN_INTERVAL_TIME,'mm/dd/yy_hh24mi')|| to_char(s.END_INTERVAL_TIME,'_hh24mi') Date_Time,
SQL.executions_delta,
RTRIM(to_char(decode(SQL.executions_delta,
0,
to_number(null),
SQL.elapsed_time_delta / SQL.executions_delta / 1000000),
'fm99990.999'),
'.') as avg_etime,
RTRIM(to_char(decode(SQL.executions_delta,
0,
to_number(null),
SQL.cpu_time_delta / SQL.executions_delta / 1000000),
'fm99990.999999'),
'.') as avg_cputime,
RTRIM(to_char(decode(SQL.executions_delta,
0,
to_number(null),
SQL.buffer_gets_delta / SQL.executions_delta ),
'fm999999990'),
'.') as avg_lio,
RTRIM(to_char(decode(SQL.executions_delta,
0,
to_number(null),
SQL.DISK_READS_DELTA / SQL.executions_delta ),
'fm999999990'),
'.') as avg_pio,
RTRIM(to_char(decode(SQL.executions_delta,
0,
to_number(null),
SQL.ROWS_PROCESSED_DELTA / SQL.executions_delta ),
'fm999999990.9'),
'.') as avg_row
FROM
dba_hist_sqlstat SQL,
dba_hist_snapshot s
WHERE
s.snap_id = SQL.snap_id and s.instance_number=sql.instance_number
AND sql_id in ('52tsgf9s200h0') and BEGIN_INTERVAL_TIME > trunc(sysdate - 2+1)
order by Date_Time, plan_hash_value,SQL.instance_number;
INST_SNAPID_SQLID_PLAN DATE_TIME EXECUTIONS_DELTA AVG_ETIME AVG_CPUTIME AVG_LIO AVG_PIO AVG_ROW
-------------------------------------------------- -------------------- ---------------- ---------- ------------- ---------- ---------- ------------
2,55718,bpgqf9b2ykqmu,3882290683 12/05/16_1030_1045 7 108.75 1.265896 176847 2525 18732
1,55739,bpgqf9b2ykqmu,3882290683 12/05/16_1545_1600 0
1,55740,bpgqf9b2ykqmu,3882290683 12/05/16_1600_1615 0
1,55741,bpgqf9b2ykqmu,3882290683 12/05/16_1615_1630 0
1,55742,bpgqf9b2ykqmu,3882290683 12/05/16_1630_1645 0
1,55743,bpgqf9b2ykqmu,3882290683 12/05/16_1645_1700 0
1,55744,bpgqf9b2ykqmu,3882290683 12/05/16_1700_1715 0
1,55745,bpgqf9b2ykqmu,3882290683 12/05/16_1715_1730 0
1,55747,bpgqf9b2ykqmu,3882290683 12/05/16_1745_1800 0
1,55748,bpgqf9b2ykqmu,3882290683 12/05/16_1800_1815 0
1,55749,bpgqf9b2ykqmu,3882290683 12/05/16_1815_1830 0
1,55750,bpgqf9b2ykqmu,3882290683 12/05/16_1830_1845 0
1,55751,bpgqf9b2ykqmu,3882290683 12/05/16_1845_1900 0
1,55752,bpgqf9b2ykqmu,3882290683 12/05/16_1900_1915 8 113.469 1.584481 131377 18203 1461
1,55753,bpgqf9b2ykqmu,3882290683 12/05/16_1915_1930 0
1,55754,bpgqf9b2ykqmu,3882290683 12/05/16_1930_1945 0
1,55755,bpgqf9b2ykqmu,3882290683 12/05/16_1945_2000 0
1,55756,bpgqf9b2ykqmu,3882290683 12/05/16_2000_2015 1 913.61 12.511014 435522 163029 118967
1,55757,bpgqf9b2ykqmu,3882290683 12/05/16_2015_2030 0
1,55758,bpgqf9b2ykqmu,3882290683 12/05/16_2030_2045 0
1,55759,bpgqf9b2ykqmu,3882290683 12/05/16_2045_2100 0
1,55760,bpgqf9b2ykqmu,3882290683 12/05/16_2100_2115 0
2,55761,bpgqf9b2ykqmu,1664736063 12/05/16_2115_2130 0
1,55761,bpgqf9b2ykqmu,3882290683 12/05/16_2115_2130 0
2,55761,bpgqf9b2ykqmu,3882290683 12/05/16_2115_2130 0
2,55762,bpgqf9b2ykqmu,1664736063 12/05/16_2130_2145 0
1,55762,bpgqf9b2ykqmu,3882290683 12/05/16_2130_2145 0
2,55762,bpgqf9b2ykqmu,3882290683 12/05/16_2130_2145 0
2,55763,bpgqf9b2ykqmu,1664736063 12/05/16_2145_2200 0
1,55763,bpgqf9b2ykqmu,3882290683 12/05/16_2145_2200 1 709.036 15.165542 646579 197389 0
2,55763,bpgqf9b2ykqmu,3882290683 12/05/16_2145_2200 0
2,55764,bpgqf9b2ykqmu,1664736063 12/05/16_2200_2215 0
2,55764,bpgqf9b2ykqmu,3882290683 12/05/16_2200_2215 0
2,55765,bpgqf9b2ykqmu,1664736063 12/05/16_2215_2230 0
2,55765,bpgqf9b2ykqmu,3882290683 12/05/16_2215_2230 0
2,55766,bpgqf9b2ykqmu,1664736063 12/05/16_2230_2245 0
2,55766,bpgqf9b2ykqmu,3882290683 12/05/16_2230_2245 0
2,55767,bpgqf9b2ykqmu,1664736063 12/05/16_2245_2300 0
2,55767,bpgqf9b2ykqmu,3882290683 12/05/16_2245_2300 0
2,55768,bpgqf9b2ykqmu,1664736063 12/05/16_2300_2315 0
2,55768,bpgqf9b2ykqmu,3882290683 12/05/16_2300_2315 0
2,55769,bpgqf9b2ykqmu,1664736063 12/05/16_2315_2330 0
2,55769,bpgqf9b2ykqmu,3882290683 12/05/16_2315_2330 0
2,55770,bpgqf9b2ykqmu,1664736063 12/05/16_2330_2345 0
2,55770,bpgqf9b2ykqmu,3882290683 12/05/16_2330_2345 0
2,55771,bpgqf9b2ykqmu,1664736063 12/05/16_2345_0000 0
2,55771,bpgqf9b2ykqmu,3882290683 12/05/16_2345_0000 0
2,55772,bpgqf9b2ykqmu,1664736063 12/06/16_0000_0015 0
2,55772,bpgqf9b2ykqmu,3882290683 12/06/16_0000_0015 0
2,55773,bpgqf9b2ykqmu,1664736063 12/06/16_0015_0030 0
2,55773,bpgqf9b2ykqmu,3882290683 12/06/16_0015_0030 0
2,55774,bpgqf9b2ykqmu,1664736063 12/06/16_0030_0045 0
2,55774,bpgqf9b2ykqmu,3882290683 12/06/16_0030_0045 0
2,55775,bpgqf9b2ykqmu,1664736063 12/06/16_0045_0100 0
2,55775,bpgqf9b2ykqmu,3882290683 12/06/16_0045_0100 0
2,55776,bpgqf9b2ykqmu,1664736063 12/06/16_0100_0115 0
2,55776,bpgqf9b2ykqmu,3882290683 12/06/16_0100_0115 0
2,55777,bpgqf9b2ykqmu,1664736063 12/06/16_0115_0130 0
2,55777,bpgqf9b2ykqmu,3882290683 12/06/16_0115_0130 0
2,55778,bpgqf9b2ykqmu,1664736063 12/06/16_0130_0145 1 1483.15 22.431827 1007716 273778 0
2,55778,bpgqf9b2ykqmu,3882290683 12/06/16_0130_0145 0
2,55779,bpgqf9b2ykqmu,1664736063 12/06/16_0145_0200 0
2,55779,bpgqf9b2ykqmu,3882290683 12/06/16_0145_0200 0
2,55780,bpgqf9b2ykqmu,1664736063 12/06/16_0200_0215 0
2,55780,bpgqf9b2ykqmu,3882290683 12/06/16_0200_0215 0
2,55781,bpgqf9b2ykqmu,1664736063 12/06/16_0215_0230 0
2,55781,bpgqf9b2ykqmu,3882290683 12/06/16_0215_0230 0
2,55782,bpgqf9b2ykqmu,1664736063 12/06/16_0230_0245 0
2,55782,bpgqf9b2ykqmu,3882290683 12/06/16_0230_0245 0
2,55783,bpgqf9b2ykqmu,1664736063 12/06/16_0245_0300 0
2,55783,bpgqf9b2ykqmu,3882290683 12/06/16_0245_0300 0
2,55784,bpgqf9b2ykqmu,1664736063 12/06/16_0300_0315 1 851.443 6.204348 284531 95518 0
2,55784,bpgqf9b2ykqmu,3882290683 12/06/16_0300_0315 0
2,55800,bpgqf9b2ykqmu,1664736063 12/06/16_0700_0715 0
2,55800,bpgqf9b2ykqmu,3882290683 12/06/16_0700_0715 0
2,55801,bpgqf9b2ykqmu,1664736063 12/06/16_0715_0730 0
2,55801,bpgqf9b2ykqmu,3882290683 12/06/16_0715_0730 0
2,55802,bpgqf9b2ykqmu,1664736063 12/06/16_0730_0745 0
2,55802,bpgqf9b2ykqmu,3882290683 12/06/16_0730_0745 0
2,55803,bpgqf9b2ykqmu,1664736063 12/06/16_0745_0800 0
2,55803,bpgqf9b2ykqmu,3882290683 12/06/16_0745_0800 0
2,55804,bpgqf9b2ykqmu,1664736063 12/06/16_0800_0815 0
2,55805,bpgqf9b2ykqmu,1664736063 12/06/16_0815_0830 0
2,55806,bpgqf9b2ykqmu,1664736063 12/06/16_0830_0845 0
2,55807,bpgqf9b2ykqmu,1664736063 12/06/16_0845_0900 0
2,55808,bpgqf9b2ykqmu,1664736063 12/06/16_0900_0915 0
2,55809,bpgqf9b2ykqmu,1664736063 12/06/16_0915_0930 0
2,55810,bpgqf9b2ykqmu,1664736063 12/06/16_0930_0945 0
2,55811,bpgqf9b2ykqmu,1664736063 12/06/16_0945_1000 0
2,55812,bpgqf9b2ykqmu,1664736063 12/06/16_1000_1015 0
2,55813,bpgqf9b2ykqmu,1664736063 12/06/16_1015_1030 0
2,55814,bpgqf9b2ykqmu,1664736063 12/06/16_1030_1045 0
2,55815,bpgqf9b2ykqmu,1664736063 12/06/16_1045_1100 0
2,55816,bpgqf9b2ykqmu,1664736063 12/06/16_1100_1115 0
2,55817,bpgqf9b2ykqmu,1664736063 12/06/16_1115_1130 0
2,55818,bpgqf9b2ykqmu,1664736063 12/06/16_1130_1145 0
2,55819,bpgqf9b2ykqmu,1664736063 12/06/16_1145_1200 0
2,55820,bpgqf9b2ykqmu,1664736063 12/06/16_1200_1215 0
2,55821,bpgqf9b2ykqmu,1664736063 12/06/16_1215_1230 0
2,55822,bpgqf9b2ykqmu,1664736063 12/06/16_1230_1245 2 1426.785 27.32261 2413519 285441 0
2,55823,bpgqf9b2ykqmu,1664736063 12/06/16_1245_1300 0
2,55824,bpgqf9b2ykqmu,1664736063 12/06/16_1300_1315 0
2,55825,bpgqf9b2ykqmu,1664736063 12/06/16_1315_1330 1 1028.804 11.08805 706540 186236 0
2,55826,bpgqf9b2ykqmu,1664736063 12/06/16_1330_1345 0
2,55827,bpgqf9b2ykqmu,1664736063 12/06/16_1345_1400 0
2,55828,bpgqf9b2ykqmu,1664736063 12/06/16_1400_1415 0
2,55829,bpgqf9b2ykqmu,1664736063 12/06/16_1415_1430 0
2,55830,bpgqf9b2ykqmu,1664736063 12/06/16_1430_1445 0
2,55831,bpgqf9b2ykqmu,1664736063 12/06/16_1445_1500 0
2,55832,bpgqf9b2ykqmu,1664736063 12/06/16_1500_1515 0
2,55833,bpgqf9b2ykqmu,1664736063 12/06/16_1515_1530 0
2,55834,bpgqf9b2ykqmu,1664736063 12/06/16_1530_1545 0
2,55835,bpgqf9b2ykqmu,1664736063 12/06/16_1545_1600 0
2,55836,bpgqf9b2ykqmu,1664736063 12/06/16_1600_1615 0
2,55837,bpgqf9b2ykqmu,1664736063 12/06/16_1615_1630 0
2,55838,bpgqf9b2ykqmu,1664736063 12/06/16_1630_1645 0
2,55839,bpgqf9b2ykqmu,1664736063 12/06/16_1645_1700 0
2,55840,bpgqf9b2ykqmu,1664736063 12/06/16_1700_1715 0
2,55841,bpgqf9b2ykqmu,1664736063 12/06/16_1715_1730 0
2,55842,bpgqf9b2ykqmu,1664736063 12/06/16_1730_1745 0
2,55843,bpgqf9b2ykqmu,1664736063 12/06/16_1745_1800 1 24788.559 216.954435 19870052 1089842 0
2,55844,bpgqf9b2ykqmu,1664736063 12/06/16_1800_1815 0
2,55845,bpgqf9b2ykqmu,1664736063 12/06/16_1815_1830 0
2,55846,bpgqf9b2ykqmu,1664736063 12/06/16_1830_1845 0
2,55847,bpgqf9b2ykqmu,1664736063 12/06/16_1845_1900 0
2,55848,bpgqf9b2ykqmu,1664736063 12/06/16_1900_1915 0
2,55849,bpgqf9b2ykqmu,1664736063 12/06/16_1915_1930 0
2,55850,bpgqf9b2ykqmu,1664736063 12/06/16_1930_1945 0
2,55851,bpgqf9b2ykqmu,1664736063 12/06/16_1945_2000 0
2,55852,bpgqf9b2ykqmu,1664736063 12/06/16_2000_2015 0
2,55853,bpgqf9b2ykqmu,1664736063 12/06/16_2015_2030 0
从上面确认好的执行计划是：
INST_SNAPID_SQLID_PLAN
1,55752,bpgqf9b2ykqmu,3882290683到1,55753,bpgqf9b2ykqmu,3882290683这个区间。
开始加载这个执行计划到baseline中：
1).创建 sql tuning set （每个数据库库仅需执行一次）
exec DBMS_SQLTUNE.CREATE_SQLSET('SPM_SQLS_ZENG');
2） 从 awr 将计划加载入 sql set
declare
baseline_ref_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
begin
open baseline_ref_cursor for
select VALUE(p) from table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(55752, 55753,
'sql_id='||CHR(39)||'bpgqf9b2ykqmu'||CHR(39)||' and plan_hash_value=3882290683',NULL,NULL,NULL,NULL,NULL,NULL,'ALL')) p;
DBMS_SQLTUNE.LOAD_SQLSET('SPM_SQLS_ZENG', baseline_ref_cursor);
end;
/
在执行时，输入 存在好的计划的 begin_snap, end_snap 的 snap_id 值，以及 语句的 sql_id 值，好的计划对应的 plan_hash_value 值
则其自动将相应的计划加载入 sql set 中了。
3） 确认 sqlset 中的执行计划
SELECT * FROM table ( DBMS_XPLAN.DISPLAY_SQLSET( 'SPM_SQLS_ZENG','bpgqf9b2ykqmu',SQLSET_OWNER =>'SYS'));
这里，输入语句的 sql_id 值，确认计划是期望的。
SQL> SELECT * FROM table ( DBMS_XPLAN.DISPLAY_SQLSET( 'SPM_SQLS_ZENG','bpgqf9b2ykqmu',SQLSET_OWNER =>'SYS'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL Tuning Set Name: SPM_SQLS_ZENG
SQL Tuning Set Owner: SYS
SQL_ID: bpgqf9b2ykqmu
SQL Text: SQL省略
--------------------------------------------------------------------------------
Plan hash value: 3882290683
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 5 (100)| |
| 1 | LOAD TABLE CONVENTIONAL | | | | | |
| 2 | FILTER | | | | | |
| 3 | FILTER | | | | | |
| 4 | TABLE ACCESS BY INDEX ROWID | SFC_WIP_LOG | 1 | 214 | 5 (0)| 00:00:01 |
| 5 | INDEX RANGE SCAN | IDX_SFC_WIP_LOG_IX6 | 1 | | 4 (0)| 00:00:01 |
| 6 | COLLECTION ITERATOR PICKLER FETCH| | 1 | 2 | 29 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
31 rows selected.
若同时有多个语句需要从 AWR 历史中加载好计划入 baseline，则可以循环执行 2), 3) 步。
4）从sqlset 加载入 baseline
declare
v_number number;
begin
v_number:=DBMS_SPM.LOAD_PLANS_FROM_SQLSET ('SPM_SQLS_ZENG','SYS', null, 'NO','YES');
dbms_output.put_line(v_number);
end;
/
这里为一次性将 sql set 中所有语句加载入 baseline 中。
5） 清理 sqlset
在加载入 baseline 后，清除 sqlset 中的记录
exec dbms_sqltune.DELETE_SQLSET('SPM_SQLS_ZENG');
commit;
在如此执行一次后，若未来，有新的语句需要处理，只需执行 2) - 5) 步即可。
与开发确认将原有跑这支SQL的会话kill，先查出，然后批量kill。
select 'alter system kill session '|| ''''||sid||','||serial#||','||inst_id||''''||' immediate;' from gv$session where sql_id='bpgqf9b2ykqmu';
再重跑SQL。
检查新执行计划是否有加载到baseline:
select * from dba_sql_plan_baselines
order by created desc
确认新执行计划的性能情况：
set linesize 3000 pagesize 50000
col inst_id for 9
col sql_id for a10
column chld format 9
column plan_hash format 9999999999
column exe format 9
column exes format 999
column gets format 99999999.99
column reads format 999999.99
column elaps_ms format 999999999.99
column io_ms format 999999999.99
column cpu_ms format 999999.99
column user_id format 99
column last_load_time format a20
Select inst_id, sql_id, child_number chld, plan_hash_value plan_hash, executions exes,
buffer_gets/executions gets,
disk_reads/executions reads,
rows_processed/executions rows_, cpu_time/executions/1000 cpu_ms,
Elapsed_time/executions/1000 elaps_ms,
USER_IO_WAIT_TIME/executions/1000 io_ms,
last_load_time
From gv$sql
Where sql_id='bpgqf9b2ykqmu'
and executions>0
order by last_load_time;
INST_ID SQL_ID CHLD PLAN_HASH EXES GETS READS ROWS_ CPU_MS ELAPS_MS IO_MS LAST_LOAD_TIME
------- ---------- ---- ----------- ---- ------------ ---------- ---------- ---------- ------------- ------------- --------------------
2 bpgqf9b2yk 1 1664736063 38 11913312.13 825208.79 0 137635.65 17514974.89 16207595.19 2016-12-05/21:14:59
qmu
1 bpgqf9b2yk 1 3882290683 6 151524.33 6594.33 51.6666667 864.50 37907.82 36507.76 2016-12-06/21:49:28
qmu
