Introduction
In this blog post I’m sharing a query and a PL/SQL script that I created to identify all SQL_ID with more than one Plan Hash Value, ordered by the average execution time and generates the commands to create a SPM Baseline using the DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE function.
Please be aware it is not a recommendation of performance or anything like that, it is just a way to easily execute this task in case you dediced to do so based on your own analysis, or if you was asked to do it. The script uses only the Cursor Cache and does not try to get anything from AWR or SQL Tuning Set, because it was originally created for a Standard Edition scenario. Also, it is important to note that if your target database is 18c or higher, we can create SPM Baseline in Standard Edition being limited to a maximum of 1 Baseline per SQL_ID.
If you are interested in executing this task using AWR in a Enterprise Edition database, you can take a look at my old blog post from 2023 here:
SQL Plan Management (SPM) – Criando Baselines Com Os Melhores Planos de Cada SQL_ID no AWR – 11gR2+
Listing the SQL_ID with the TOP #1 PLAN_HASH_VALUE from Cursor Cache
The below query identifies the SQL_ID with multiple PLAN_HASH_VALUE in the cursor cache, and get the PHV with the shortest average execution time:
-- total_execs it the sum of executions for all PHV
-- executions is the sum of executions for the specific PHV
col total_execs format 999,999,999,999
col executions format 999,999,999,999
col elapsed_time format 999,999,999,999
col Elapsed_Time_avg format 999,999,999.999
with multiple_phv as (
select sql_id,
sum(executions) as total_execs,
count(distinct(plan_hash_value)) as phv_count
from gv$sql
group by sql_id
having count(distinct(plan_hash_value)) > 1
and sum(executions) > 100
)
select rownum, x.* from (
select s.sql_id,
s.plan_hash_value,
max(m.phv_count) as phv_count,
max(m.total_execs) as total_execs,
sum(s.executions) as executions,
sum(s.elapsed_time) as elapsed_time,
sum(s.elapsed_time/1000) / greatest(sum(s.executions),1) as elapsed_time_avg,
ROW_NUMBER( ) OVER (PARTITION BY s.sql_id ORDER BY sum(s.elapsed_time/1000) / greatest(sum(s.executions),1) ) as rn
from multiple_phv m
join gv$sql s on m.sql_id = s.sql_id
where 1=1
-- and s.sql_text not like '%v$sql%'
group by s.sql_id, s.plan_hash_value
order by executions desc
) x
where rn = 1;
For example:
ROWNUM SQL_ID PLAN_HASH_VALUE PHV_COUNT EXECUTIONS ELAPSED_TIME ELAPSED_TIME_AVG RN
---------- ------------- --------------- ---------- ---------------- ---------------- ---------------- ----------
1 2tj3m7457gpr3 2825529294 2 61,848 34,768,261 .562 1
2 c01ucb01g032x 3100787888 3 28,978 8,706,505 .300 1
3 8qjg21ssc8tfu 3532950372 2 21,924 627,778 .029 1
4 4f1j7hzjdy8xm 3772573687 2 14,542 1,473,178 .101 1
5 b50f1dkya0u1f 2722875835 2 13,843 7,513,524 .543 1
6 7azxftvubufnj 1503499597 2 11,537 1,594,028 .138 1
7 4f1j7hzjdy8xm 2535594703 2 7,942 977,772 .123 1
8 c01ucb01g032x 2825529294 3 7,869 9,412,325 1.196 1
9 bnwu9ywbjac7c 863446948 2 7,737 2,932,508 .379 1
10 bhr1jm24yvum6 3532950372 2 7,429 173,709 .023 1
11 07tskcp73c3bk 453157637 3 7,354 38,376,152 5.218 1
12 5g2pz0c912svb 3939250741 2 6,603 429,385 .065 1
13 2jh31081zj39z 2959227480 2 6,431 241,608 .038 1
14 5tq9kzhq8hqhy 2722875835 3 5,337 3,465,327 .649 1
15 c01ucb01g032x 4053359837 3 4,683 208,141,097 44.446 1
16 5pazx6dft6hru 3949148835 2 3,908 507,060 .130 1
17 5tq9kzhq8hqhy 368524258 3 3,412 1,851,556 .543 1
18 2tj3m7457gpr3 3100787888 2 3,340 2,503,010 .749 1
19 bnmk423t339g3 3532950372 2 3,313 125,996 .038 1
20 bhr1jm24yvum6 2537912479 2 3,205 109,521 .034 1
...
...
...
107 rows selected.
Generating the Commands to Create SPM Baseline for Each SQL_ID
The following PL/SQL script uses the previous query to identifiy the faster PLAN_HASH_VALUE for each SQL ID and generates a second PL/SQL block that we can copy and execute in the SQL*PLUS:
SET SERVEROUTPUT ON;
DECLARE
CURSOR C1 IS
with multiple_phv as (
select sql_id,
sum(executions) as total_execs,
count(distinct(plan_hash_value)) as phv_count
from gv$sql
group by sql_id
having count(distinct(plan_hash_value)) > 1
and sum(executions) > 100
)
select rownum, x.* from (
select s.sql_id,
s.plan_hash_value,
max(m.phv_count) as phv_count,
sum(s.executions) as executions,
sum(s.elapsed_time) as elapsed_time,
sum(s.elapsed_time/1000) / greatest(sum(s.executions),1) as elapsed_time_avg,
ROW_NUMBER( ) OVER (PARTITION BY s.sql_id ORDER BY sum(s.elapsed_time/1000) / greatest(sum(s.executions),1) ) as rn
from multiple_phv m
join v$sql s on m.sql_id = s.sql_id
where 1=1
--and s.sql_text not like '%v$sql%'
group by s.sql_id, s.plan_hash_value
order by executions desc
) x
where rn = 1;
BEGIN
DBMS_OUTPUT.PUT_LINE('DECLARE');
DBMS_OUTPUT.PUT_LINE(' vLoad PLS_INTEGER;');
DBMS_OUTPUT.PUT_LINE('BEGIN');
for x in c1 loop
dbms_output.put_line ('vLoad := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (SQL_ID => '''|| x.sql_id ||''', PLAN_HASH_VALUE => '|| x.plan_hash_value ||');');
end loop;
DBMS_OUTPUT.PUT_LINE('END;');
DBMS_OUTPUT.PUT_LINE('/');
END;
/
When you execute this PLSQL block, it will generate a result like this:
DECLARE vLoad PLS_INTEGER; BEGIN vLoad := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (SQL_ID => '2tj3m7457gpr3', PLAN_HASH_VALUE => 2825529294); vLoad := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (SQL_ID => 'c01ucb01g032x', PLAN_HASH_VALUE => 3100787888); vLoad := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (SQL_ID => '8qjg21ssc8tfu', PLAN_HASH_VALUE => 3532950372); vLoad := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (SQL_ID => '4f1j7hzjdy8xm', PLAN_HASH_VALUE => 3772573687); vLoad := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (SQL_ID => 'b50f1dkya0u1f', PLAN_HASH_VALUE => 2722875835); vLoad := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (SQL_ID => '7azxftvubufnj', PLAN_HASH_VALUE => 1503499597); vLoad := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (SQL_ID => '4f1j7hzjdy8xm', PLAN_HASH_VALUE => 2535594703); vLoad := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (SQL_ID => 'c01ucb01g032x', PLAN_HASH_VALUE => 2825529294); vLoad := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (SQL_ID => 'bnwu9ywbjac7c', PLAN_HASH_VALUE => 863446948); vLoad := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (SQL_ID => 'bhr1jm24yvum6', PLAN_HASH_VALUE => 3532950372); vLoad := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (SQL_ID => '07tskcp73c3bk', PLAN_HASH_VALUE => 453157637); vLoad := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (SQL_ID => '5g2pz0c912svb', PLAN_HASH_VALUE => 3939250741); vLoad := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (SQL_ID => '2jh31081zj39z', PLAN_HASH_VALUE => 2959227480); vLoad := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (SQL_ID => '5tq9kzhq8hqhy', PLAN_HASH_VALUE => 2722875835); vLoad := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (SQL_ID => 'c01ucb01g032x', PLAN_HASH_VALUE => 4053359837); END; /
So you can copy this new PL/SQL block and execute directly in the SQL*PLUS, or save it to be used later.
Listing all Created SPM Baseline
If you want to list all baseline created with SPM, you can start with this query and ajust the filter as you need:
SET VERIFY OFF
SET LINESIZE 400
SET PAGESIZE 500
COL SIGNATURE FORMAT 9999999999999999999999
COL SQL_HANDLE FORMAT A20
COL PLAN_NAME FORMAT A30
COL ORIGIN FORMAT A15 TRUNC
COL CREATED FORMAT A20
COL MODIFIED FORMAT A20
COL ENABLED FORMAT A8
COL ACCEPTED FORMAT A7
COL ADAPTIVE FORMAT A8
COL PLAN_HASH_VALUE FORMAT A15
ALTER SESSION SET NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS';
SELECT SIGNATURE,
SQL_HANDLE,
PLAN_NAME,
ORIGIN,
CREATED,
LAST_MODIFIED as MODIFIED,
ENABLED,
ACCEPTED,
ADAPTIVE
,(select replace(plan_table_output,'Plan hash value: ','')
from table(dbms_xplan.display_sql_plan_baseline('' || SQL_HANDLE || '','' || PLAN_NAME || ''))
where plan_table_output like '%Plan hash value%'
and rownum = 1
) as PLAN_HASH_VALUE
FROM DBA_SQL_PLAN_BASELINES
ORDER BY CREATED DESC;
Exame of the result:
SIGNATURE SQL_HANDLE PLAN_NAME ORIGIN CREATED MODIFIED ENABLED ACCEPTE ADAPTIVE PHV
----------------------- -------------------- ------------------------------ --------------- -------------------- -------------------- -------- ------- -------- ---------------
17577661824197041602 SQL_f3f0664c75359dc2 SQL_PLAN_g7w369jumb7f22c285640 MANUAL-LOAD-FRO 2025-07-31 12:20:56 2025-07-31 12:20:56 YES YES NO 1800161985
6434293604856485211 SQL_594b33f17a09d55b SQL_PLAN_5kktmy5x0mpav6e3ed0fc MANUAL-LOAD-FRO 2025-07-31 12:20:56 2025-07-31 12:20:56 YES YES NO 256891726
11025155299370295686 SQL_99013854bc7b6186 SQL_PLAN_9k09saky7qsc634bfe369 MANUAL-LOAD-FRO 2025-07-31 12:20:56 2025-07-31 12:20:56 YES YES NO 1202594339
16224222674512733627 SQL_e1280490172b65bb SQL_PLAN_f2a04k0bkqtdv084b2bd4 MANUAL-LOAD-FRO 2025-07-31 12:20:56 2025-07-31 12:20:56 YES YES NO 3532950372
8714364469874954176 SQL_78efa4508e2e8fc0 SQL_PLAN_7jvx4a272x3y0824ab8a4 MANUAL-LOAD-FRO 2025-07-31 12:20:56 2025-07-31 12:20:56 YES YES NO 764777169
3264851763845932396 SQL_2d4f15619c58256c SQL_PLAN_2umspc6f5h9bc4a7dcd90 MANUAL-LOAD-FRO 2025-07-31 12:20:56 2025-07-31 12:20:56 YES YES NO 1427005295
11220100851602364670 SQL_9bb5ce4723d118fe SQL_PLAN_9rdff8wjx267y4a353032 MANUAL-LOAD-FRO 2025-07-31 12:20:56 2025-07-31 12:20:56 YES YES NO 4077877387
12170433957997742882 SQL_a8e61133fcce8b22 SQL_PLAN_ajthj6gycx2t27cc8fcb9 MANUAL-LOAD-FRO 2025-07-31 12:20:56 2025-07-31 12:20:56 YES YES NO 931355061
12836832712234046605 SQL_b2259756ddd3148d SQL_PLAN_b49cravfx654d8fcfcb96 MANUAL-LOAD-FRO 2025-07-31 12:20:56 2025-07-31 12:20:56 YES YES NO 1269876583
4911227560187777426 SQL_44282f71f819d192 SQL_PLAN_48a1gf7w1mnckad72aecf MANUAL-LOAD-FRO 2025-07-31 12:20:56 2025-07-31 12:20:56 YES YES NO 208277707
6800554087575262587 SQL_5e606be969c1157b SQL_PLAN_5ws3bx5nw25bv0077b930 MANUAL-LOAD-FRO 2025-07-31 12:20:56 2025-07-31 12:20:56 YES YES NO 2985215041
10122252226877956898 SQL_8c7976c535f7a322 SQL_PLAN_8sybqsnuzg8t216346224 MANUAL-LOAD-FRO 2025-07-31 12:20:56 2025-07-31 12:20:56 YES YES NO 1191625878
7346130640793798803 SQL_65f2b2e576208893 SQL_PLAN_6bwpkwpv2124m360d1462 MANUAL-LOAD-FRO 2025-07-31 12:20:56 2025-07-31 12:20:56 YES YES NO 2555168756
1113369130933499701 SQL_0f737b53612d3735 SQL_PLAN_0ywvvadhkudtp18aa6677 MANUAL-LOAD-FRO 2025-07-31 12:20:56 2025-07-31 12:20:56 YES YES NO 2722875835
9326206025523666583 SQL_816d56f7c5721e97 SQL_PLAN_82vaqyz2r47nr39aa0f62 MANUAL-LOAD-FRO 2025-07-31 12:20:55 2025-07-31 12:20:55 YES YES NO 566437284
1107050257837896747 SQL_0f5d085816eeb42b SQL_PLAN_0yr88b0bfxd1bef7757f3 MANUAL-LOAD-FRO 2025-07-31 12:20:55 2025-07-31 12:20:55 YES YES NO 2215135354
11466973283495421459 SQL_9f22df725ba3a613 SQL_PLAN_9y8qzf9du79hm8467908d MANUAL-LOAD-FRO 2025-07-31 12:20:55 2025-07-31 12:20:56 YES YES NO 2829263652
2990266249356071060 SQL_297f8f4f88b55094 SQL_PLAN_2kzwg9y4ban4n8c73c33d MANUAL-LOAD-FRO 2025-07-31 12:20:55 2025-07-31 12:20:55 YES YES NO 4147820115
9951196781848998718 SQL_8a19c0bfd81eb33e SQL_PLAN_8n6f0rzc1xcty91cb08da MANUAL-LOAD-FRO 2025-07-31 12:20:55 2025-07-31 12:20:55 YES YES NO 93487544
11182523283278257033 SQL_9b304dadb855db89 SQL_PLAN_9qc2dpqw5bqw9a56abb5d MANUAL-LOAD-FRO 2025-07-31 12:20:55 2025-07-31 12:20:55 YES YES NO 3099149394
7126736697546312267 SQL_62e7414470fd0a4b SQL_PLAN_65tu18jsgu2kb548d860d MANUAL-LOAD-FRO 2025-07-31 12:20:55 2025-07-31 12:20:55 YES YES NO 4202102758
10676414590238620820 SQL_942a3e80f5f87c94 SQL_PLAN_98ajyh3uzhz4n91cb08da MANUAL-LOAD-FRO 2025-07-31 12:20:55 2025-07-31 12:20:55 YES YES NO 93487544
....
....
....
83 rows selected.
Conclusion
In this blog post the general idea is to share the method to achieve the goal, but you can and should adapt the script based on your needs. You can for example adjust the script to create the baseline automatically instead of generating a separate PL/SQL block to be executed later. Or if you want just to use the same approach I demonstrated here, but you have a lot of SQL_ID to create the baseline, like 500+ queries, it would be good to save the output in the spool and execute the SQL file.
Also if you need to execute this task for a high number of SQL_ID in a very transactional database, I would recommend you to execute it during a low activity window, since a lot of executions for DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE and cursor cache invalidations can cause some Shared Pool contention during the execution of the script.