Yes. Use one common STS loaded from all three AWR windows, then run one SQL Tuning Advisor task per SQL_ID. Do not run one giant Advisor task for all SQLs together; it will likely time out and become hard to interpret.
Also, important correction from your screenshot: remove this condition:
OR s.elapsed_time_delta > 0
That will pull almost every SQL from those AWR windows. For STS, keep the filter only on your watchlist SQL IDs.
Below script excludes 9kfty957z5y3 because you marked it NOT NEEDED. Add it back if required.
1. Create one STS for all selected SQL IDs
SET SERVEROUTPUT ON SIZE UNLIMITED
DECLARE
l_sts_name VARCHAR2(30) := 'STS_JOB_WATCH_AWR';
BEGIN
BEGIN
DBMS_SQLTUNE.DROP_SQLSET(
sqlset_name => l_sts_name,
sqlset_owner => USER
);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => l_sts_name,
description => 'Watchlist SQLs from BAD1, BAD2, and GOOD AWR windows'
);
DBMS_OUTPUT.PUT_LINE('Created STS: ' || l_sts_name);
END;
/
2. Load BAD1, BAD2, and GOOD windows into the STS
This loads all selected SQLs and all available plan hashes from those windows.
SET SERVEROUTPUT ON SIZE UNLIMITED
DECLARE
l_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
l_sts_name VARCHAR2(30) := 'STS_JOB_WATCH_AWR';
l_filter VARCHAR2(4000) :=
q'[sql_id IN (
'1mufpt4a9awyx',
'f2y77p2gtdg41',
'7r1gn0tpbwjjj',
'fsgs2mtg3tfpx',
'6wa1frsf52nsy'
)]';
PROCEDURE load_window(
p_label IN VARCHAR2,
p_begin_snap IN NUMBER,
p_end_snap IN NUMBER
)
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Loading ' || p_label || ' snaps ' || p_begin_snap || ' to ' || p_end_snap);
OPEN l_cursor FOR
SELECT VALUE(p)
FROM TABLE(
DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(
begin_snap => p_begin_snap,
end_snap => p_end_snap,
basic_filter => l_filter,
ranking_measure1 => 'elapsed_time',
result_limit => 100,
attribute_list => 'ALL',
dbid => 352148541
)
) p;
DBMS_SQLTUNE.LOAD_SQLSET(
sqlset_name => l_sts_name,
populate_cursor => l_cursor,
load_option => 'MERGE'
);
DBMS_OUTPUT.PUT_LINE('Loaded ' || p_label);
END;
BEGIN
load_window('BAD1_APR6_23_TO_APR7_03', 49153, 49160);
load_window('BAD2_APR7_15_TO_21', 49184, 49196);
load_window('GOOD_APR8_00_TO_01', 49202, 49204);
DBMS_OUTPUT.PUT_LINE('Completed STS load: ' || l_sts_name);
END;
/
3. Validate what loaded into the STS
SET LINES 240
SET PAGES 200
COLUMN sqlset_name FORMAT A25
COLUMN sql_id FORMAT A15
COLUMN sql_text_sample FORMAT A90
SELECT sqlset_name,
sql_id,
plan_hash_value,
SUM(executions) AS executions,
ROUND(SUM(elapsed_time)/1e6, 2) AS elapsed_sec,
ROUND(SUM(cpu_time)/1e6, 2) AS cpu_sec,
SUM(buffer_gets) AS buffer_gets,
SUM(disk_reads) AS disk_reads,
SUBSTR(MAX(sql_text), 1, 90) AS sql_text_sample
FROM dba_sqlset_statements
WHERE sqlset_name = 'STS_JOB_WATCH_AWR'
GROUP BY sqlset_name,
sql_id,
plan_hash_value
ORDER BY sql_id,
elapsed_sec DESC;
For 7r1gn0tpbwjjj, you should see both:
2340129260 -- bad plan
3257229599 -- good/recommended plan
4. Create SQL Tuning Advisor tasks for each SQL_ID
This creates one Advisor task per SQL. It does not execute yet.
SET SERVEROUTPUT ON SIZE UNLIMITED
DECLARE
l_task_name VARCHAR2(128);
BEGIN
FOR r IN (
SELECT DISTINCT sql_id
FROM dba_sqlset_statements
WHERE sqlset_name = 'STS_JOB_WATCH_AWR'
ORDER BY sql_id
)
LOOP
l_task_name := 'STA_' || UPPER(r.sql_id) || '_AWR';
BEGIN
DBMS_SQLTUNE.DROP_TUNING_TASK(task_name => l_task_name);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
l_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sqlset_name => 'STS_JOB_WATCH_AWR',
sqlset_owner => USER,
basic_filter => 'sql_id = ''' || r.sql_id || '''',
scope => DBMS_SQLTUNE.SCOPE_COMPREHENSIVE,
time_limit => 7200,
task_name => l_task_name,
description => 'SQL Tuning Advisor from AWR STS for SQL_ID ' || r.sql_id
);
DBMS_OUTPUT.PUT_LINE('Created task: ' || l_task_name);
END LOOP;
END;
/
5. Execute the Advisor tasks one by one
Do not execute everything blindly if you are short on time. Start with the most important SQLs.
Execute only the critical SQL first
BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(
task_name => 'STA_7R1GN0TPBWJJJ_AWR'
);
END;
/
Then run the others one at a time
BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'STA_1MUFPT4A9AWYX_AWR');
END;
/
BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'STA_F2Y77P2GTDG41_AWR');
END;
/
BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'STA_FSGS2MTG3TFPX_AWR');
END;
/
BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'STA_6WA1FRSF52NSY_AWR');
END;
/
If you want to include 9kfty957z5y3, create a separate task for it after adding it into the STS filter.
6. Check Advisor task status
SET LINES 240
SET PAGES 200
COLUMN task_name FORMAT A35
COLUMN status FORMAT A15
COLUMN execution_start FORMAT A30
COLUMN execution_end FORMAT A30
COLUMN status_message FORMAT A100
SELECT task_name,
status,
execution_start,
execution_end,
status_message
FROM dba_advisor_log
WHERE task_name LIKE 'STA_%_AWR'
ORDER BY execution_start DESC;
7. Generate Advisor report for each SQL
Example for 7r1gn0tpbwjjj:
SET LONG 10000000
SET LONGCHUNKSIZE 10000000
SET LINES 240
SET PAGES 0
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(
task_name => 'STA_7R1GN0TPBWJJJ_AWR',
type => 'TEXT',
level => 'ALL'
) AS report
FROM dual;
For all reports, change the task name:
STA_1MUFPT4A9AWYX_AWR
STA_F2Y77P2GTDG41_AWR
STA_7R1GN0TPBWJJJ_AWR
STA_FSGS2MTG3TFPX_AWR
STA_6WA1FRSF52NSY_AWR
8. Pull high-level Advisor findings for all tasks
SET LINES 240
SET PAGES 200
COLUMN task_name FORMAT A35
COLUMN status FORMAT A15
COLUMN finding_name FORMAT A55
COLUMN rec_type FORMAT A35
COLUMN benefit FORMAT 999999999999
COLUMN command FORMAT A120
SELECT t.task_name,
t.status,
f.finding_name,
r.type AS rec_type,
r.benefit,
SUBSTR(a.command, 1, 120) AS command
FROM dba_advisor_tasks t
LEFT JOIN dba_advisor_findings f
ON f.task_id = t.task_id
LEFT JOIN dba_advisor_recommendations r
ON r.task_id = t.task_id
AND r.finding_id = f.finding_id
LEFT JOIN dba_advisor_actions a
ON a.task_id = t.task_id
AND a.rec_id = r.rec_id
WHERE t.task_name LIKE 'STA_%_AWR'
ORDER BY t.task_name,
r.benefit DESC;
9. For tomorrow, do not wait for all Advisor tasks
For immediate production protection, prioritize:
1. SQL_ID 7r1gn0tpbwjjj
Good plan hash: 3257229599
Bad plan hash : 2340129260
2. SQL_ID 1mufpt4a9awyx
High reads/gets and AppFirewallCore impact
3. SQL_ID f2y77p2gtdg41
Very high elapsed time
4. SQL_ID fsgs2mtg3tfpx
Repeated long elapsed SQL
5. SQL_ID 6wa1frsf52nsy
High CPU in good window
For 7r1gn0tpbwjjj, Advisor already confirmed the best action: create SQL Plan Baseline for plan hash 3257229599 and control PX/concurrency. The STS/Advisor work for the other SQLs is for deeper tuning, not tomorrow’s first-line protection.
------------------------------------------------------
This result is expected for a heavy historical AWR SQL. The task did execute, but SQL Tuning Advisor timed out:
Completion Status : INTERRUPTED
Error : ORA-13639: The current operation was interrupted because it timed out
Number of SQLs with Timeouts : 1
This is not an Oracle outage/error. It means the advisor could not finish deep analysis within your 1500 seconds.
For tomorrow’s job, do not wait on SQL Tuning Advisor as the primary fix. You already found the immediate control point:
Bad plan hash : 2340129260
Good plan hash: 3257229599
Risk driver : PX / DOP / session explosion
So the immediate fix remains:
1. Load fixed baseline for good plan hash 3257229599.
2. Control/disable parallelism for this SQL/job.
3. Reduce job concurrency.
4. Monitor PX sessions live.
SQL Tuning Advisor can continue in parallel, but it is not the outage-prevention mechanism for tomorrow.
1. First check whether Advisor produced partial findings
Even with timeout, sometimes Oracle still captures useful findings.
SET LINES 240
SET PAGES 200
COLUMN task_name FORMAT A35
COLUMN status FORMAT A15
COLUMN finding_name FORMAT A50
COLUMN type FORMAT A30
COLUMN benefit FORMAT 999999999999
COLUMN command FORMAT A120
SELECT t.task_name,
t.status,
f.finding_name,
r.type,
r.benefit,
SUBSTR(a.command, 1, 120) AS command
FROM dba_advisor_tasks t
LEFT JOIN dba_advisor_findings f
ON f.task_id = t.task_id
LEFT JOIN dba_advisor_recommendations r
ON r.task_id = t.task_id
AND r.finding_id = f.finding_id
LEFT JOIN dba_advisor_actions a
ON a.task_id = t.task_id
AND a.rec_id = r.rec_id
WHERE t.task_name = 'STA_7R1GN_BAD1_STS'
ORDER BY r.benefit DESC;
Also check the task log:
SET LINES 240
SET PAGES 200
COLUMN task_name FORMAT A35
COLUMN status FORMAT A20
COLUMN status_message FORMAT A100
SELECT task_name,
status,
execution_start,
execution_end,
status_message
FROM dba_advisor_log
WHERE task_name = 'STA_7R1GN_BAD1_STS'
ORDER BY execution_start DESC;
2. Re-run Advisor with higher time limit
Since the 25-minute run timed out, create a clean second task with a higher limit. For this SQL, I would use 2 hours if running off-window.
SET SERVEROUTPUT ON SIZE UNLIMITED
DECLARE
l_task_name VARCHAR2(128);
BEGIN
l_task_name := 'STA_7R1GN_BAD1_STS_2H';
BEGIN
DBMS_SQLTUNE.DROP_TUNING_TASK(task_name => l_task_name);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
l_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sqlset_name => 'STS_7R1GN_BAD1',
sqlset_owner => 'RDSADM',
basic_filter => q'[sql_id = '7r1gn0tpbwjjj' and plan_hash_value = 2340129260]',
scope => DBMS_SQLTUNE.SCOPE_COMPREHENSIVE,
time_limit => 7200,
task_name => 'STA_7R1GN_BAD1_STS_2H',
description => '2-hour SQL Tuning Advisor for bad plan 2340129260'
);
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => l_task_name);
DBMS_OUTPUT.PUT_LINE('Executed task: ' || l_task_name);
END;
/
Report:
SET LONG 10000000
SET LONGCHUNKSIZE 10000000
SET LINES 240
SET PAGES 0
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(
task_name => 'STA_7R1GN_BAD1_STS_2H',
type => 'TEXT',
level => 'ALL'
) AS report
FROM dual;
3. Quick Advisor run for tomorrow readiness
For a fast pre-check, run limited scope. This will not do the full deep SQL Profile analysis, but it can quickly catch stats/index/access-path recommendations.
SET SERVEROUTPUT ON SIZE UNLIMITED
DECLARE
l_task_name VARCHAR2(128);
BEGIN
l_task_name := 'STA_7R1GN_BAD1_LIMITED';
BEGIN
DBMS_SQLTUNE.DROP_TUNING_TASK(task_name => l_task_name);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
l_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sqlset_name => 'STS_7R1GN_BAD1',
sqlset_owner => 'RDSADM',
basic_filter => q'[sql_id = '7r1gn0tpbwjjj' and plan_hash_value = 2340129260]',
scope => DBMS_SQLTUNE.SCOPE_LIMITED,
time_limit => 600,
task_name => 'STA_7R1GN_BAD1_LIMITED',
description => 'Quick limited SQL Tuning Advisor for 7r1gn0tpbwjjj'
);
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => l_task_name);
DBMS_OUTPUT.PUT_LINE('Executed task: ' || l_task_name);
END;
/
Report:
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(
task_name => 'STA_7R1GN_BAD1_LIMITED',
type => 'TEXT',
level => 'ALL'
) AS report
FROM dual;
4. Do not make tomorrow dependent on Advisor
For tomorrow, the hard control should be this:
-- Good historical plan hash
3257229599
-- Bad historical plan hash
2340129260
Load the good plan as fixed baseline:
SET SERVEROUTPUT ON SIZE UNLIMITED
DECLARE
l_loaded PLS_INTEGER;
BEGIN
l_loaded := DBMS_SPM.LOAD_PLANS_FROM_AWR(
begin_snap => 49184,
end_snap => 49196,
basic_filter => q'[sql_id = '7r1gn0tpbwjjj' and plan_hash_value = 3257229599]',
fixed => 'YES',
enabled => 'YES'
);
DBMS_OUTPUT.PUT_LINE('Plans loaded: ' || l_loaded);
END;
/
Then verify live tomorrow:
SET LINES 240
SET PAGES 200
COLUMN sql_profile FORMAT A40
COLUMN sql_patch FORMAT A40
COLUMN sql_plan_baseline FORMAT A40
COLUMN module FORMAT A45
SELECT inst_id,
sql_id,
child_number,
plan_hash_value,
sql_profile,
sql_patch,
sql_plan_baseline,
module,
executions,
elapsed_time/1000000 AS elapsed_sec,
cpu_time/1000000 AS cpu_sec,
buffer_gets,
disk_reads,
px_servers_executions
FROM gv$sql
WHERE sql_id = '7r1gn0tpbwjjj'
ORDER BY inst_id, child_number;
Expected tomorrow:
plan_hash_value should be 3257229599
plan_hash_value should NOT be 2340129260
PX_SERVERS_EXECUTIONS should not explode
5. My recommendation now
Use this decision path:
For tomorrow outage prevention:
Baseline good plan hash 3257229599.
Cap/disable parallelism for this job SQL.
Reduce job concurrency.
For root-cause tuning:
Run SQL Tuning Advisor with 7200 seconds off-window.
Review recommendations.
Do not blindly accept new SQL Profile if it increases parallelism.
Meeting-ready wording:
SQL Tuning Advisor was executed successfully from the historical AWR SQL Tuning Set, but the comprehensive analysis timed out after 1500 seconds. That confirms this is a heavy SQL optimization case, but it does not block tomorrow’s mitigation. We already identified the actionable risk: bad plan hash
2340129260and high PX usage. For tomorrow, we should stabilize with the good plan hash3257229599, control parallelism/concurrency, and continue the extended Advisor run off-window for permanent tuning recommendations.
