从AWR快照中固定执行计划
2023-05-19 16:34 明朝散发 阅读(51) 评论(0) 编辑 收藏 举报Troubleshooting/resolution
-
cw97pxhjgtcqq –sql_id provide by user
-
if not pls confirm sql_id using following query:
--1. Active sessions info order by Last_call_et
col username for a15
col spid for a10
col machine for a15
col module for a15
col logon_time_ for a15
SELECT NVL(a.username, '(oracle)') AS username,
a.blocking_session as blk_sess,
a.status,
a.sid,
a.serial#,
d.spid,
a.machine,
a.module,
a.last_call_et last_call_secs,
a.sql_id, a.sql_child_number,
TO_CHAR(a.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time_
FROM v$session a,
v$process d
WHERE a.paddr = d.addr
and a.username is not null
AND a.status = 'ACTIVE'
and a.sid not in (select sid from v$mystat where rownum=1)
ORDER BY status, last_call_et desc
/
- check sql plan history
col btime for a25
select
a.sql_id,a.plan_hash_value,
to_char(begin_interval_time,'dd-mon-yy hh24:mi') btime,
executions_delta executions,
round(ELAPSED_TIME_delta/1000000/greatest(executions_delta,1),1) avg_duration_sec
from dba_hist_SQLSTAT a, dba_hist_snapshot b
where sql_id='&sql_id'
and a.snap_id=b.snap_id
and a.instance_number=b.instance_number
order by a.snap_id desc, elapsed_time_delta desc
/
SQL_ID PLAN_HASH_VALUE BTIME EXECUTIONS AVG_DURATION_SEC
------------- --------------- ------------------------- ---------- ----------------
cw97pxhjgtcqq 3595800810 08-oct-23 12:00 0 3602.9
cw97pxhjgtcqq 3595800810 08-oct-23 11:00 0 3600.8
cw97pxhjgtcqq 3595800810 08-oct-23 10:00 4 783.8
cw97pxhjgtcqq 3595800810 08-oct-23 09:00 2 4
cw97pxhjgtcqq 2158386679 08-oct-23 08:00 2 2.6
cw97pxhjgtcqq 2158386679 07-oct-23 02:00 1 .2
cw97pxhjgtcqq 2158386679 07-oct-23 00:00 7 133.4
cw97pxhjgtcqq 2158386679 06-oct-23 23:00 6 60
cw97pxhjgtcqq 2158386679 06-oct-23 22:00 2 3.3
cw97pxhjgtcqq 2158386679 06-oct-23 21:00 4 3.6
cw97pxhjgtcqq 3595800810 06-oct-23 00:00 0 1118.4
SQL_ID PLAN_HASH_VALUE BTIME EXECUTIONS AVG_DURATION_SEC
------------- --------------- ------------------------- ---------- ----------------
cw97pxhjgtcqq 3595800810 05-oct-23 23:00 0 3607.9
cw97pxhjgtcqq 3595800810 05-oct-23 22:00 0 3597.8
cw97pxhjgtcqq 3595800810 05-oct-23 21:00 1 3181
cw97pxhjgtcqq 3595800810 05-oct-23 17:00 0 393.8
cw97pxhjgtcqq 3595800810 05-oct-23 16:00 0 3601.8
cw97pxhjgtcqq 3595800810 05-oct-23 15:00 0 3600.8
cw97pxhjgtcqq 3595800810 05-oct-23 14:00 0 3601.9
cw97pxhjgtcqq 3595800810 05-oct-23 13:00 0 3600.8
cw97pxhjgtcqq 3595800810 05-oct-23 12:00 0 3601.8
cw97pxhjgtcqq 3595800810 05-oct-23 11:00 0 3599.8
cw97pxhjgtcqq 3595800810 05-oct-23 10:00 0 3603.6
SQL_ID PLAN_HASH_VALUE BTIME EXECUTIONS AVG_DURATION_SEC
------------- --------------- ------------------------- ---------- ----------------
cw97pxhjgtcqq 3595800810 05-oct-23 09:00 0 3609
cw97pxhjgtcqq 3595800810 05-oct-23 08:00 6 9
cw97pxhjgtcqq 1551343453 05-oct-23 06:01 2 2.9
cw97pxhjgtcqq 1551343453 05-oct-23 04:00 4 1.4
cw97pxhjgtcqq 1551343453 04-oct-23 07:00 4 70.6
cw97pxhjgtcqq 1551343453 04-oct-23 06:00 15 66.3
SQL_ID PLAN_HASH_VALUE BTIME EXECUTIONS AVG_DURATION_SEC
------------- --------------- ------------------------- ---------- ----------------
fs03cgzxytjc9 2404011218 09-oct-23 14:00 0 3603.8
fs03cgzxytjc9 2404011218 09-oct-23 13:00 0 3601.8
fs03cgzxytjc9 2404011218 09-oct-23 12:00 0 3598.8
fs03cgzxytjc9 2404011218 09-oct-23 11:00 1 475.8
fs03cgzxytjc9 3501865267 09-oct-23 08:00 1 2.6
- the PLAN_HASH_VALUE 2158386679 is better than 3595800810
--due to PLAN_HASH_VALUE 2158386679 missing in cache, look for it in awr snapshot
SELECT SS.SNAP_ID,
SS.INSTANCE_NUMBER,
BEGIN_INTERVAL_TIME,
SQL_ID,
PLAN_HASH_VALUE,OPTIMIZER_COST,
DISK_READS_TOTAL,
BUFFER_GETS_TOTAL,
ROWS_PROCESSED_TOTAL,
CPU_TIME_TOTAL,
ELAPSED_TIME_TOTAL,
IOWAIT_TOTAL,
NVL (EXECUTIONS_DELTA, 0) EXECS,
( ELAPSED_TIME_DELTA
/ DECODE (NVL (EXECUTIONS_DELTA, 0), 0, 1, EXECUTIONS_DELTA))
/ 1000000
AVG_ETIME,
( BUFFER_GETS_DELTA
/ DECODE (NVL (BUFFER_GETS_DELTA, 0), 0, 1, EXECUTIONS_DELTA))
AVG_LIO
FROM DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
WHERE SQL_ID = '&sql_id'
AND SS.SNAP_ID = S.SNAP_ID
AND SS.INSTANCE_NUMBER = S.INSTANCE_NUMBER
AND EXECUTIONS_DELTA > 0
ORDER BY 1, 2, 3;
SNAP_ID INSTANCE_NUMBER BEGIN_INTERVAL_TIME SQL_ID PLAN_HASH_VALUE OPTIMIZER_COST DISK_READS_TOTAL BUFFER_GETS_TOTAL ROWS_PROCESSED_TOTAL CPU_TIME_TOTAL ELAPSED_TIME_TOTAL IOWAIT_TOTAL EXECS AVG_ETIME AVG_LIO
---------- --------------- --------------------------------------------------------------------------- ------------- --------------- -------------- ---------------- ----------------- -------------------- -------------- ------------------ ------------ ---------- ---------- ----------
52464 1 04-OCT-23 06.00.46.278 AM cw97pxhjgtcqq 1551343453 5892 5425519 83939643 801996 918990582 994465785 136453224 15 66.297719 5595976.2
52465 1 04-OCT-23 07.00.48.027 AM cw97pxhjgtcqq 1551343453 5892 7161932 102478608 1129955 1172460226 1276696717 177141866 4 70.557733 4634741.25
52486 1 05-OCT-23 04.00.56.909 AM cw97pxhjgtcqq 1551343453 5892 7169775 102843452 1152152 1177470343 1282420216 177919876 4 1.39039575 87439.75
52488 1 05-OCT-23 06.01.00.290 AM cw97pxhjgtcqq 1551343453 5892 7178183 103140913 1173380 1182759998 1288225867 178575779 2 2.9028255 148730.5
52490 1 05-OCT-23 08.00.03.600 AM cw97pxhjgtcqq 3595800810 4222 290704 2565574 2125 50738269 54229568 6815580 6 9.03826133 427595.667
52503 1 05-OCT-23 09.00.32.881 PM cw97pxhjgtcqq 3595800810 4222 182916338 320993404 2125 2.8587E+10 3.2449E+10 3987468436 1 3181.04246 33735562
52527 1 06-OCT-23 09.00.55.145 PM cw97pxhjgtcqq 2158386679 6031 9058 350473 22101 13620524 14443396 1054047 4 3.610849 87618.25
52528 1 06-OCT-23 10.00.56.959 PM cw97pxhjgtcqq 2158386679 6031 17600 650906 43329 19695285 21137772 1736207 2 3.347188 150216.5
52529 1 06-OCT-23 11.00.58.584 PM cw97pxhjgtcqq 2158386679 6031 2355608 31118531 344295 313397045 381325009 92096681 6 60.0312062 5077937.5
52530 1 07-OCT-23 12.00.03.113 AM cw97pxhjgtcqq 2158386679 6031 7319003 101936795 1129953 1064455651 1315080396 336379475 7 133.393627 10116894.9
52532 1 07-OCT-23 02.00.10.367 AM cw97pxhjgtcqq 2158386679 6031 7319050 101952028 1130048 1064559915 1315285811 336485307 1 .205415 15233
SNAP_ID INSTANCE_NUMBER BEGIN_INTERVAL_TIME SQL_ID PLAN_HASH_VALUE OPTIMIZER_COST DISK_READS_TOTAL BUFFER_GETS_TOTAL ROWS_PROCESSED_TOTAL CPU_TIME_TOTAL ELAPSED_TIME_TOTAL IOWAIT_TOTAL EXECS AVG_ETIME AVG_LIO
---------- --------------- --------------------------------------------------------------------------- ------------- --------------- -------------- ---------------- ----------------- -------------------- -------------- ------------------ ------------ ---------- ---------- ----------
52562 1 08-OCT-23 08.00.32.440 AM cw97pxhjgtcqq 2158386679 6031 7341023 102602445 1173377 1074152058 1325773600 337768628 2 2.638941 150216.5
52563 1 08-OCT-23 09.00.34.121 AM cw97pxhjgtcqq 3595800810 4227 319 40662 430 7852857 8041734 204940 2 4.020867 20331
52564 1 08-OCT-23 10.00.35.794 AM cw97pxhjgtcqq 3595800810 4227 16574494 32132417 2125 2907134257 3143371836 258621987 4 783.832526 8022938.75
- create STS
BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => 'STS_2158386679',
description => 'SQL Tuning Set for loading plan into SQL Plan Baseline');
END;
/
- load STS from snap_id (based on step 3)
--验证：
SELECT VALUE(P)
FROM TABLE(
dbms_sqltune.select_workload_repository(begin_snap=>52585, end_snap=>52586,basic_filter=>'sql_id =''fs03cgzxytjc9''',attribute_list=>'ALL')) p;
DECLARE
cur sys_refcursor;
BEGIN
OPEN cur FOR
SELECT VALUE(P)
FROM TABLE(
dbms_sqltune.select_workload_repository(begin_snap=>52527, end_snap=>52529,basic_filter=>'sql_id =''&SQL_ID''',attribute_list=>'ALL')) p;
DBMS_SQLTUNE.LOAD_SQLSET( sqlset_name=> '&SQLSET', populate_cursor=>cur);
CLOSE cur;
END;
/
- check STS loading status
SELECT
first_load_time ,
executions as execs ,
parsing_schema_name ,
elapsed_time / 1000000 as elapsed_time_secs ,
cpu_time / 1000000 as cpu_time_secs ,
buffer_gets ,
disk_reads ,
direct_writes ,
rows_processed ,
fetches ,
optimizer_cost ,
sql_plan ,
plan_hash_value ,
sql_id ,
sql_text
FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(sqlset_name => '&SQLSET')
);
- fix sql plan from AWR snapshop(STS)
DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
sqlset_name => '&SQLSET',
basic_filter=>'plan_hash_value = ''&HASH_VALUE''',
fixed => 'YES', enabled => 'YES'
);
END;
/
- verify fixed sql plan
col sql_handle for a35
col plan_name for a35
set lin 300
SELECT distinct a.plan_name,a.sql_handle,s.sql_id,a.BUFFER_GETS,enabled, accepted,FIXED,a.origin,a.reproduced, a.autopurge, a.enabled,a.created
FROM
DBA_SQL_PLAN_BASELINES a,
SYS.SQL$TEXT b,
DBA_HIST_SQLSTAT s
where
a.SQL_HANDLE=b.SQL_HANDLE
and s.sql_id='&sql_id' order by a.created;
- purge bad plan from shared_pool
select ADDRESS,HASH_VALUE from v$sqlarea where SQL_ID='&sql_id';
exec DBMS_SHARED_POOL.PURGE ('&ADDRESS,&HASH_VALUE ','C');
- base on step 8 , delete the fixed plan which you dont need anymore
PLAN_NAME SQL_HANDLE SQL_ID BUFFER_GETS ENA ACC FIX ORIGIN REP AUT ENA CREATED
----------------------------------- ----------------------------------- ------------- ----------- --- --- --- ----------------------------- --- --- --- ---------------------------------------------------------------------------
SQL_PLAN_68nx36k9wkww1132bbc7c SQL_6453a33493c97381 cw97pxhjgtcqq 404688 YES YES YES MANUAL-LOAD-FROM-STS YES YES YES 14-APR-23 11.16.10.127221 AM
SQL_PLAN_2vmrahza6anubcb51d231 SQL_2dceea87d465534b cw97pxhjgtcqq 42727560 YES YES NO MANUAL-LOAD-FROM-AWR YES YES YES 06-SEP-23 09.26.01.244839 PM
SQL_PLAN_2vmrahza6anub894de589 SQL_2dceea87d465534b cw97pxhjgtcqq 0 YES NO NO AUTO-CAPTURE YES YES YES 07-SEP-23 02.57.33.566724 AM
SQL_PLAN_2vmrahza6anub1177edc3 SQL_2dceea87d465534b cw97pxhjgtcqq 0 YES NO NO AUTO-CAPTURE YES YES YES 12-SEP-23 10.45.59.584890 AM
SQL_PLAN_2vmrahza6anub3b2fd31e SQL_2dceea87d465534b cw97pxhjgtcqq 0 YES NO NO AUTO-CAPTURE YES YES YES 03-OCT-23 04.52.25.933859 PM
SQL_PLAN_2vmrahza6anub1a04dc6f SQL_2dceea87d465534b cw97pxhjgtcqq 0 YES NO NO AUTO-CAPTURE YES YES YES 06-OCT-23 01.08.33.874683 PM
SQL_PLAN_c0d44g7fuh49z03d16531 SQL_c0348479dda8113f cw97pxhjgtcqq 30768058 YES YES YES MANUAL-LOAD-FROM-STS YES YES YES 08-OCT-23 02.20.43.124636 PM
var tmp number
exec :tmp := dbms_spm.drop_sql_plan_baseline(sql_handle=>'SQL_2dceea87d465534b',plan_name=>'SQL_PLAN_2vmrahza6anubcb51d231');
- modify aotopurge to "NO"
DECLARE
l_plans_altered PLS_INTEGER;
BEGIN
l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(
sql_handle => '&SQL_HANDLE',
plan_name => '&PLAN_NAME',
attribute_name => 'AUTOPURGE',
attribute_value => 'NO');
DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);
END;
/
