This content originally appeared on DEV Community and was authored by Abhilash Kumar | Oracle ACE 
{ Abhilash Kumar Bhattaram : Follow on LinkedIn }
Tuning SQL statements has always been a persistent pain to DBA’s . Cloud or On Prem , SQL statements need to constantly evolve with application teams inputs and its scalability.
SQL Plan Management
Oracle SQL Plan Management has been around since 12.x but it does take some effort to let Oracle database know that an SQL Plan can eveolve as a better plan is available.
One should always refer Oracle docuentation link on SQL Performance fundamentals here , this is a good place to start reading if you are new to tuning SQL
SQL Plan Management , whats was already available in older versions ?
In Pre 19.22 environments the following would need to be done ,
- Identify and load SQL Baselines from cursor / SQL Tuning Sets / AWR
- Evolve SQL Baselines for the SQL
Reference here
My github Source code to use SQL Plan Management
Here is my github to perform for the SQL Plan Management
https://github.com/abhilash-8/ora-tools/blob/master/spm_addv.sql
https://github.com/abhilash-8/ora-tools/blob/master/spm_addv.sql
SQL Plan Management from 19.22
Once you have identified the SQL that needs to be tuned ( usainf various utilities like AWR , oratop , etc..)
Step #1 One needs to identify what are the SQL Plan Hash Values and SQL Plan Management baselines currently available
orcl> @spm_list az1y2nzz8y42a
### Current SQL Baselines
INST|SQL_ID | PLAN_HASH_VALUE|SQL_PLAN_BASELINE | EXECUTIONS| ELAPSED_TIME|EXACT_MATCHING_SIGNATURE|SQL_TEXT
-----|--------------|----------------|------------------------------|--------------------|--------------------|------------------------|--------------------------------------------------
1|az1y2nzz8y42a | 2441334682| | 1327555| 17554624| 4628050212597469622|SELECT COUNT(1) FROM ORDERS_M.T_ORD_PRD_MS C, ORDE
1|az1y2nzz8y42a | 4222704632| | 99| 184297216| 4628050212597469622|SELECT COUNT(1) FROM ORDERS_M.T_ORD_PRD_MS C, ORDE
Elapsed: 00:00:01.01
no rows selected
Elapsed: 00:00:00.00
Step #2 DO NOTHING but ANALYZE The findings as below.
- You can can see from above that there are 2 SQL Plans in use 2441334682 and 4222704632
- At the look of it appears 2441334682 seems more efficient. Normally I would force this Plan Hash Value to the cursor but that would also mean that Oracle optimizer may not be in a position to evlove into
- So we would need Oracle Optimizer to decide which of the available Plan Hash Values it can base line for the SQL
Step #2 Use DBMS_SPM.ADD_VERIFIED_SQL_PLAN_BASELINE
for the identified sql_id
Reference : The official documentation for DBMS_SPM.ADD_VERIFIED_SQL_PLAN_BASELINE is here
Take a look at what Oracle says ( as per documentation ) for DBMS_SPM.ADD_VERIFIED_SQL_PLAN_BASELINE
SQL plan baselines are created to ensure that the best-known plans are used for the selected SQL statement. These are the steps that the function executes:
- Loads plans from Cursor Cache, Automatic Workload Repository, and Auto SQL Tuning Set (SYS_AUTO_STS) into SQL Plan Management SQL plan history in a non-accepted state.
- Uses the SQL Plan Management Evolve Advisor internally to identify the best-performing execution plans. The best-performing plans are accepted.
When the function has completed, it will have accepted the best plans among the plans available from the Cursor Cache, AWR, and the Automatic SQL Tuning Set.
What is fascinating is that this find and implement the best available SQL Plan and also provide room to evolve any SQL Plans that come up
Step #3 Implement the best SQL Plan and allow it to evolve using DBMS_SPM.ADD_VERIFIED_SQL_PLAN_BASELINE
Now for the SQL az1y2nzz8y42a I would like Oracle to evolve using DBMS_SPM.ADD_VERIFIED_SQL_PLAN_BASELINE
orcl> @spm_addv az1y2nzz8y42a
PL/SQL procedure successfully completed.
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL handle: SQL_403a2325dc8b11b6
SQL text: << truncated SQL Text >>
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_40fj34rf8q4dq8be5cf75 Plan id: 2347093877
Enabled: YES Fixed: NO Accepted: YES Origin: EVOLVE-LOAD-FROM-CURSOR-CACHE
Plan rows: From dictionary
--------------------------------------------------------------------------------
Plan hash value: 2441334682
I use my utility spm_list to check the Plans again post running spm_addv.sql
orcl> @spm_list az1y2nzz8y42a
### Current SQL Baselines
INST|SQL_ID | PLAN_HASH_VALUE|SQL_PLAN_BASELINE | EXECUTIONS| ELAPSED_TIME|EXACT_MATCHING_SIGNATURE|SQL_TEXT
-----|--------------|----------------|------------------------------|--------------------|--------------------|------------------------|--------------------------------------------------
1|az1y2nzz8y42a | 2441334682| | 1327555| 17554624| 4628050212597469622|SELECT COUNT(1) FROM ORDERS_M.T_ORD_PRD_MS C, ORDE
1|az1y2nzz8y42a | 4222704632| | 99| 184297216| 4628050212597469622|SELECT COUNT(1) FROM ORDERS_M.T_ORD_PRD_MS C, ORDE
1|az1y2nzz8y42a | 2441334682|SQL_PLAN_40fj34rf8q4dq8be5cf75| 191| 39968| 4628050212597469622|SELECT COUNT(1) FROM ORDERS_M.T_ORD_PRD_MS C, ORDE
SQL_HANDLE |PLAN_NAME |ENABLED |ACCEPTED |FIXED |CREATED
-------------------------|------------------------------|----------|----------|----------|---------------------------------------------------------------------------
SQL_403a2325dc8b11b6 |SQL_PLAN_40fj34rf8q4dq8be5cf75|YES |YES |NO |24-AUG-25 10.57.26.578422 PM
You can see here the following are accomplished.
- The best plan 2441334682 for SQL az1y2nzz8y42a baselined and is set
- The baseline is set to “EVOLVE-LOAD-FROM-CURSOR-CACHE” which means as soon as a newer plan is available Oracle will try to use it after verifying it.
This is probably one of the best backported 23ai to 19c features that is very much needed.
This content originally appeared on DEV Community and was authored by Abhilash Kumar | Oracle ACE