Troubleshooting/resolution
f18mgmxm76kdr –sql_id provide by user
- 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
------------- --------------- ------------------------- ---------- ----------------
f18mgmxm76kdr 2941319763 14-apr-23 09:00 0 3603.2
f18mgmxm76kdr 2941319763 14-apr-23 08:00 0 3602.2
f18mgmxm76kdr 2941319763 14-apr-23 07:00 0 3595.3
f18mgmxm76kdr 2941319763 14-apr-23 06:00 0 3602.4
f18mgmxm76kdr 2941319763 14-apr-23 05:00 1 1573.7
f18mgmxm76kdr 1110904408 08-apr-23 14:00 63 .4
f18mgmxm76kdr 1110904408 08-apr-23 10:00 2 3.9
- can see the PLAN_HASH_VALUE 1110904408 is better than 2941319763
--due to PLAN_HASH_VALUE 1110904408 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
---------- --------------- --------------------------------------------------------------------------- ------------- --------------- -------------- ---------------- ----------------- -------------------- -------------- ------------------ ------------ ---------- ---------- ----------
48237 1 08-APR-23 10.00.59.837 AM f18mgmxm76kdr 1110904408 5027004749 12552 46764 53820 7256735 7712472 609076 2 3.856236 23382
48241 1 08-APR-23 02.00.08.692 PM f18mgmxm76kdr 1110904408 5027004749 22805 451452 102950 31973545 32636517 957780 63 .395619762 6423.61905
48376 1 14-APR-23 05.00.05.603 AM f18mgmxm76kdr 2941319763 3982193851 6721 31529396 0 1572852978 1573656049 778207 1 1573.65605 31529396
- create STS
BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => 'STS_1110904408',
description => 'SQL Tuning Set for loading plan into SQL Plan Baseline');
END;
/
4.load STS from snap_id (based on step 3)
DECLARE
cur sys_refcursor;
BEGIN
OPEN cur FOR
SELECT VALUE(P)
FROM TABLE(
dbms_sqltune.select_workload_repository(begin_snap=>48240, end_snap=>48242,basic_filter=>'sql_id =''f18mgmxm76kdr''',attribute_list=>'ALL')) p;
DBMS_SQLTUNE.LOAD_SQLSET( sqlset_name=> 'STS_1110904408', 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 => 'STS_1110904408')
);
- fix sql plan from AWR snapshop(STS)
DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
sqlset_name => 'STS_1110904408',
basic_filter=>'plan_hash_value = ''1110904408''',
fixed => 'YES', enabled => 'YES'
);
END;
/
- verify fixed sql plan
select sql_handle, substr(sql_text,1, 100) text, created, enabled, accepted, fixed from DBA_SQL_PLAN_BASELINES;
8.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');
标签:快照,23,AWR,1110904408,固定,sql,f18mgmxm76kdr,TOTAL,id
From: https://www.cnblogs.com/sage914/p/17415664.html