Troubleshooting/resolution
f18mgmxm76kdr –sql_id provide by user

1. 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

2. 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

3. 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;
/

5. 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')
 );

6. 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;
/

7. 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');