Real-time SQL Plan Management hands-on
Table of contents
Preamble
In a previous post we have seen that Automatic SQL Plan Management (ASPM) has been backported to 19c from 23c. Now, as 23c has been replaced by 26ai, there is a new feature called Real-time SQL Plan Management that extend interval task based feature to real time !
As already stated SQL Plan Management (SPM) if free and so are Automatic SQL Plan Management (ASPM) and Real-time SQL Plan Management. Check the Oracle official licensing guide for latest information:

My testing has been done with Oracle AI database 26ai (23.26.2.0.0) running on Red Hat Enterprise Linux release 9.7 (Plow). My test server has 12 cores and 64GB of memory. I am obviously in a pluggable database as it is now mandatory with 26ai…
Real-time SQL Plan Management setup
As suggested I keep optimizer_capture_sql_plan_baselines to false:
SQL> show parameter optimizer%baselines NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ optimizer_capture_sql_plan_baselines boolean FALSE optimizer_use_sql_plan_baselines boolean TRUE
In 19c we had only the option to set the task to ON or OFF but now in 26ai we can set it to AUTO:
SQL> exec dbms_spm.configure('auto_spm_evolve_task','AUTO');
PL/SQL procedure successfully completed.You can check it has been done with:
SQL> select * from dba_sql_management_config where parameter_name like 'AUTO_SPM_EVOLVE_TASK%'; PARAMETER_NAME PARAMETER_VALUE PARAMETER_VALUE_CLOB LAST_MODIFIED MODIFIED_BY ___________________________________ __________________ _______________________ ______________________________ ______________ AUTO_SPM_EVOLVE_TASK AUTO AUTO 14/07/26 11:29:26,000000000 SYS AUTO_SPM_EVOLVE_TASK_INTERVAL 3600 3600 AUTO_SPM_EVOLVE_TASK_MAX_RUNTIME 1800 1800
The list of configurable parameters:
SQL> select
parameter_name, parameter_value, description
from dba_advisor_parameters
where task_name = 'SYS_AUTO_SPM_EVOLVE_TASK'
and parameter_value != 'UNUSED';
PARAMETER_NAME PARAMETER_VALUE DESCRIPTION
___________________________ __________________ _________________________________________________________________________________________________________________________________________________
DAYS_TO_EXPIRE UNLIMITED The expiration time in days for the current SQL Access Advisor task
JOURNALING INFORMATION Specifies logging of messages to the advisor journal
MODE COMPREHENSIVE Specifies either a limited or comprehensive analysis operation, where limited runs in less time but may produce slightly lower quality results
TARGET_OBJECTS 1 Deprecated Parameter
TIME_LIMIT 3600 The maximum time that an analysis can execute
DEFAULT_EXECUTION_TYPE SPM EVOLVE Tune the performance of SQL statements
EXECUTION_DAYS_TO_EXPIRE 30 Specifies the expiration time in days for individual executions of the current task
ALTERNATE_PLAN_LIMIT UNLIMITED
ALTERNATE_PLAN_SOURCE FOREGROUND
ALTERNATE_PLAN_BASELINE AUTO
ACCEPT_PLANS TRUE TRUE if SQL plan baselines should be accepted by the task, FALSE otherwise
11 rows selected.And finally to track the execution of the task:
SQL> select
task_name, status, interval, max_run_time, elapsed_time, last_schedule_time, enabled
from dba_autotask_schedule_control
where dbid = sys_context('userenv','con_dbid')
and task_name = 'Auto SPM Task';
TASK_NAME STATUS INTERVAL MAX_RUN_TIME ELAPSED_TIME LAST_SCHEDULE_TIME ENABLED
________________ ____________ ___________ _______________ _______________ _____________________________________ __________
Auto SPM Task SUCCEEDED 3600 1800 0 14/07/26 11:30:15,908000000 +02:00 TRUEReal-time SQL Plan Management test case
Table creation
I create the exact same test table, and histogram, as in Automatic SQL Plan Management article that I create within the APP01 Oracle username:
SQL> drop table if exists test01;
Table TEST01 dropped.
SQL> create table if not exists test1(id number, descr varchar(50)) tablespace users;
Table TEST1 created.
SQL> declare
i number;
nbrows number;
begin
i:=1;
nbrows:=50000;
loop
exit when i>nbrows;
if (i=1) then
insert into test1 values(1, rpad('A',49,'A'));
else
insert into test1 values(nbrows, rpad('A',49,'A'));
end if;
i:=i+1;
end loop;
commit;
end;
/
PL/SQL procedure successfully completed.
SQL> create index test1_idx_id on test1(id) tablespace users;
Index TEST1_IDX_ID created.
SQL> exec dbms_stats.gather_table_stats(ownname => 'app01', tabname => 'test1', estimate_percent => NULL, method_opt => 'FOR ALL INDEXED COLUMNS SIZE 2', degree => DBMS_STATS.DEFAULT_DEGREE, CASCADE => TRUE);
PL/SQL procedure successfully completed.Perfect world situation
The initial situation is an INDEX RANGE SCAN, thanks to the created histograms. Execute below with an account that has enough privileges (DBA). The /* Yannick */ is here to find myself easily in V$SQL:
SQL> set long 32000
SQL> set pages 10000
SQL> alter session set statistics_level=all;
Session altered.
SQL> alter system flush shared_pool;
System altered.
SQL> alter system flush shared_pool;
System altered.
SQL> select /* Yannick */ * from app01.test1 where id=1;
ID DESCR
_____ ____________________________________________________
1 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
SQL> select
sql_id,child_number,plan_hash_value,is_bind_sensitive,is_bind_aware,is_shareable,is_obsolete,sql_plan_baseline
from v$sql
where sql_text like 'select /* Yannick%';
SQL_ID CHILD_NUMBER PLAN_HASH_VALUE IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE IS_OBSOLETE SQL_PLAN_BASELINE
________________ _______________ __________________ ____________________ ________________ _______________ ______________ ____________________
a6p80sap7w2zy 0 1636184578 N N Y N
SQL> select * from table(dbms_xplan.display_cursor(sql_id=>'a6p80sap7w2zy', cursor_child_no=>'0', format=> 'all adaptive allstats'));
PLAN_TABLE_OUTPUT
_________________________________________________________________________________________________________________________________________________
SQL_ID a6p80sap7w2zy, child number 0
-------------------------------------
select /* Yannick */ * from app01.test1 where id=1
Plan hash value: 1636184578
----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 2 (100)| | 1 |00:00:00.01 | 4 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST1 | 1 | 1 | 30 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 4 |
|* 2 | INDEX RANGE SCAN | TEST1_IDX_ID | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
----------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / "TEST1"@"SEL$1"
2 - SEL$1 / "TEST1"@"SEL$1"
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=1)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "ID"[NUMBER,22], "TEST1"."DESCR"[VARCHAR2,50]
2 - "TEST1".ROWID[ROWID,10], "ID"[NUMBER,22]
31 rows selected.What’s funny is the scheduling of the Auto SQL Plan Management that is also here. So I understand we have the new Real-time SPM but the Auto SPM is still there underground (maybe doing nothing as the Real-time should take over):
SQL> select
task_name, status, interval, max_run_time, elapsed_time, last_schedule_time, enabled
from dba_autotask_schedule_control
where dbid = sys_context('userenv','con_dbid')
and task_name = 'Auto SPM Task';
TASK_NAME STATUS INTERVAL MAX_RUN_TIME ELAPSED_TIME LAST_SCHEDULE_TIME ENABLED
________________ ____________ ___________ _______________ _______________ _____________________________________ __________
Auto SPM Task SUCCEEDED 3600 1800 0 14/07/26 12:28:18,709000000 +02:00 TRUE
SQL> set long 32000
SQL> set pages 10000
SQL> select dbms_spm.report_auto_evolve_task(type => 'TEXT', level => 'ALL', section => 'ALL') as report from dual;
REPORT
________________________________________________________________________________________________
GENERAL INFORMATION SECTION
---------------------------------------------------------------------------------------------
Task Information:
---------------------------------------------
Task Name : SYS_AUTO_SPM_EVOLVE_TASK
Task Owner : SYS
Description : Automatic SPM Evolve Task
Execution Name : SYS_SPM_2026-07-14/12:28:18
Execution Type : SPM EVOLVE
Scope : COMPREHENSIVE
Status : COMPLETED
Started : 07/14/2026 12:28:18
Finished : 07/14/2026 12:28:18
Last Updated : 07/14/2026 12:28:18
Global Time Limit : 3600
Per-Plan Time Limit : UNUSED
Number of Errors : 0
---------------------------------------------------------------------------------------------
SUMMARY SECTION
---------------------------------------------------------------------------------------------
Number of plans processed : 0
Number of findings : 0
Number of recommendations : 0
Number of errors : 0
---------------------------------------------------------------------------------------------Someone has (wrongly) gathered statistics on a table
I destroy the histogram with:
exec dbms_stats.gather_table_stats(ownname => 'app01', tabname => 'test1', estimate_percent => NULL, method_opt => 'FOR ALL INDEXED COLUMNS SIZE 1', degree => DBMS_STATS.DEFAULT_DEGREE, cascade => TRUE);
I re-execute the same statement:
SQL> select /* Yannick */ * from app01.test1 where id=1;
ID DESCR
_____ ____________________________________________________
1 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAThe PLAN_HASH_VALUE has changed versus the first query and we know have a Full Table Scan (FTS):
SQL> select
sql_id,child_number,plan_hash_value,is_bind_sensitive,is_bind_aware,is_shareable,is_obsolete,sql_plan_baseline
from v$sql
where sql_text like 'select /* Yannick%';
SQL_ID CHILD_NUMBER PLAN_HASH_VALUE IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE IS_OBSOLETE SQL_PLAN_BASELINE
________________ _______________ __________________ ____________________ ________________ _______________ ______________ ____________________
a6p80sap7w2zy 0 4122059633 N N Y N
SQL> select * from table(dbms_xplan.display_cursor(sql_id=>'a6p80sap7w2zy', cursor_child_no=>'0', format=> 'all adaptive allstats'));
PLAN_TABLE_OUTPUT
________________________________________________________________________________________________________________________
SQL_ID a6p80sap7w2zy, child number 0
-------------------------------------
select /* Yannick */ * from app01.test1 where id=1
Plan hash value: 4122059633
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 136 (100)| | 1 |00:00:00.01 | 443 |
|* 1 | TABLE ACCESS FULL| TEST1 | 1 | 25000 | 732K| 136 (0)| 00:00:01 | 1 |00:00:00.01 | 443 |
---------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / "TEST1"@"SEL$1"
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=1)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "ID"[NUMBER,22], "TEST1"."DESCR"[VARCHAR2,50]
Note
-----
- This is SQL Plan Management Test Plan
32 rows selected.If we check in SQL Plan Baselines we have now this new FOREGROUND-CAPTURE Plan Baseline creation and FOREGROUND_LAST_VERIFIED new column telling us when Real-time SPM has worked. Almost instantaneous on my quiet test database:
SQL> select
sql_handle, plan_name, origin, enabled, accepted, fixed, foreground_last_verified
from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME ORIGIN ENABLED ACCEPTED FIXED FOREGROUND_LAST_VERIFIED
_______________________ _________________________________ _____________________ __________ ___________ ________ ______________________________
SQL_1afcbca8bb6c934e SQL_PLAN_1pz5wp2xqt4uf6c0c005c FOREGROUND-CAPTURE YES YES NO
SQL_1afcbca8bb6c934e SQL_PLAN_1pz5wp2xqt4uffe026eff FOREGROUND-CAPTURE YES NO NO 14/07/26 12:34:18,000000000I execute again the same query and we see that a new child have been created and the SQL Plan Baseline is used to be back with the INDEX RANGE SCAN even if the histogram has gone. The SQL Plan Baseline number (SQL_PLAN_1pz5wp2xqt4uf6c0c005c) used is the one that has been accepted in previous query:
SQL> select /* Yannick */ * from app01.test1 where id=1;
ID DESCR
_____ ____________________________________________________
1 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
SQL> select
sql_id,child_number,plan_hash_value,is_bind_sensitive,is_bind_aware,is_shareable,is_obsolete,sql_plan_baseline
from v$sql
where sql_text like 'select /* Yannick%';
SQL_ID CHILD_NUMBER PLAN_HASH_VALUE IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE IS_OBSOLETE SQL_PLAN_BASELINE
________________ _______________ __________________ ____________________ ________________ _______________ ______________ _________________________________
a6p80sap7w2zy 0 4122059633 N N Y N
a6p80sap7w2zy 2 1636184578 N N Y N SQL_PLAN_1pz5wp2xqt4uf6c0c005c
SQL> select * from table(dbms_xplan.display_cursor(sql_id=>'a6p80sap7w2zy', cursor_child_no=>'2', format=> 'all adaptive allstats'));
PLAN_TABLE_OUTPUT
_________________________________________________________________________________________________________________________________________________
SQL_ID a6p80sap7w2zy, child number 2
-------------------------------------
select /* Yannick */ * from app01.test1 where id=1
Plan hash value: 1636184578
----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 250 (100)| | 1 |00:00:00.01 | 4 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST1 | 1 | 25000 | 732K| 250 (0)| 00:00:01 | 1 |00:00:00.01 | 4 |
|* 2 | INDEX RANGE SCAN | TEST1_IDX_ID | 1 | 25000 | | 49 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
----------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / "TEST1"@"SEL$1"
2 - SEL$1 / "TEST1"@"SEL$1"
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=1)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "ID"[NUMBER,22], "TEST1"."DESCR"[VARCHAR2,50]
2 - "TEST1".ROWID[ROWID,10], "ID"[NUMBER,22]
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 6
---------------------------------------------------------------------------
0 - STATEMENT
- ALL_ROWS
- DB_VERSION('23.1.0')
- IGNORE_OPTIM_EMBEDDED_HINTS
- OPTIMIZER_FEATURES_ENABLE('23.1.0')
1 - SEL$1 / "TEST1"@"SEL$1"
- BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "TEST1"@"SEL$1")
- INDEX_RS_ASC(@"SEL$1" "TEST1"@"SEL$1" ("TEST1"."ID"))
Note
-----
- SQL plan baseline SQL_PLAN_1pz5wp2xqt4uf6c0c005c used for this statement
49 rows selected.And this real-time without the scheduled Auto SPM task:
SQL> select dbms_spm.report_auto_evolve_task(type => 'TEXT', level => 'ALL', section => 'ALL') as report from dual; REPORT ________________________________________________________________________________________________ GENERAL INFORMATION SECTION --------------------------------------------------------------------------------------------- Task Information: --------------------------------------------- Task Name : SYS_AUTO_SPM_EVOLVE_TASK Task Owner : SYS Description : Automatic SPM Evolve Task Execution Name : SYS_SPM_2026-07-14/12:28:18 Execution Type : SPM EVOLVE Scope : COMPREHENSIVE Status : COMPLETED Started : 07/14/2026 12:28:18 Finished : 07/14/2026 12:28:18 Last Updated : 07/14/2026 12:28:18 Global Time Limit : 3600 Per-Plan Time Limit : UNUSED Number of Errors : 0 --------------------------------------------------------------------------------------------- SUMMARY SECTION --------------------------------------------------------------------------------------------- Number of plans processed : 0 Number of findings : 0 Number of recommendations : 0 Number of errors : 0 ---------------------------------------------------------------------------------------------
Magical isn’t it ?
If you want to display he plan use DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE with THE SQL_HANDLE and PLAN_NAME from DBA_SQL_PLAN_BASELINES:
SQL> set pages 10000
SQL> select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle => 'SQL_1afcbca8bb6c934e', plan_name => 'SQL_PLAN_1pz5wp2xqt4uf6c0c005c'));
PLAN_TABLE_OUTPUT
_______________________________________________________________________________________________________
--------------------------------------------------------------------------------
SQL handle: SQL_1afcbca8bb6c934e
SQL text: select /* Yannick */ * from app01.test1 where id=1
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_1pz5wp2xqt4uf6c0c005c Plan id: 1636184578
Enabled: YES Fixed: NO Accepted: YES Origin: FOREGROUND-CAPTURE
Plan rows: From Auto SQL Tuning Set
--------------------------------------------------------------------------------
Plan hash value: 1636184578
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST1 | 1 | 30 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEST1_IDX_ID | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=1)
26 rows selected.
SQL> select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle => 'SQL_1afcbca8bb6c934e', plan_name => 'SQL_PLAN_1pz5wp2xqt4uffe026eff'));
PLAN_TABLE_OUTPUT
___________________________________________________________________________________
--------------------------------------------------------------------------------
SQL handle: SQL_1afcbca8bb6c934e
SQL text: select /* Yannick */ * from app01.test1 where id=1
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_1pz5wp2xqt4uffe026eff Plan id: 4122059633
Enabled: YES Fixed: NO Accepted: NO Origin: FOREGROUND-CAPTURE
Plan rows: From Auto SQL Tuning Set
--------------------------------------------------------------------------------
Plan hash value: 4122059633
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 136 (100)| |
|* 1 | TABLE ACCESS FULL| TEST1 | 25000 | 732K| 136 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=1)
Note
-----
- This is SQL Plan Management Test Plan
29 rows selected.If you want to delete a SQL Plan Baseline you can do:
declare l_plans_dropped pls_integer; begin l_plans_dropped := dbms_spm.drop_sql_plan_baseline(sql_handle => 'SQL_1afcbca8bb6c934e', plan_name => 'SQL_PLAN_1pz5wp2xqt4uf6c0c005c'); end; /
Accessing DBA_SQLSET_STATEMENTS we can also see the differences the FTS plan (4122059633) and the INDEX RANGE SCAN plan (1636184578):
SQL> select
plan_hash_value,
cpu_time,
buffer_gets,
disk_reads,
direct_writes,
rows_processed,
fetches,
executions,
optimizer_cost,
TO_CHAR(plan_timestamp,'dd-mon-yyyy hh24:mi:ss') AS plan_timestamp
from dba_sqlset_statements
where sqlset_name='SYS_AUTO_STS'
and sql_id='a6p80sap7w2zy'
order by plan_timestamp desc;
PLAN_HASH_VALUE CPU_TIME BUFFER_GETS DISK_READS DIRECT_WRITES ROWS_PROCESSED FETCHES EXECUTIONS OPTIMIZER_COST PLAN_TIMESTAMP
__________________ ___________ ______________ _____________ ________________ _________________ __________ _____________ _________________ _______________________
4122059633 245437 3759 94 2 1 2 1 136 14-jul-2026 12:34:17
1636184578 19227 62 0 0 1 2 1 250 14-jul-2026 11:46:54After the scheduled Automatic SPM has run we have details in the execution report:
SQL> select dbms_spm.report_auto_evolve_task(type => 'TEXT', level => 'ALL', section => 'ALL') as report from dual; REPORT ________________________________________________________________________________________________ GENERAL INFORMATION SECTION --------------------------------------------------------------------------------------------- Task Information: --------------------------------------------- Task Name : SYS_AUTO_SPM_EVOLVE_TASK Task Owner : SYS Description : Automatic SPM Evolve Task Execution Name : SYS_SPM_2026-07-14/13:28:21 Execution Type : SPM EVOLVE Scope : COMPREHENSIVE Status : COMPLETED Started : 07/14/2026 13:28:21 Finished : 07/14/2026 13:28:22 Last Updated : 07/14/2026 13:28:22 Global Time Limit : 3600 Per-Plan Time Limit : UNUSED Number of Errors : 0 --------------------------------------------------------------------------------------------- SUMMARY SECTION --------------------------------------------------------------------------------------------- Number of plans processed : 1 Number of findings : 0 Number of recommendations : 0 Number of errors : 1 --------------------------------------------------------------------------------------------- DETAILS SECTION --------------------------------------------------------------------------------------------- Object ID : 2 Test Plan Name : SQL_PLAN_1pz5wp2xqt4uffe026eff Base Plan Name : Cost-based plan SQL Handle : SQL_1afcbca8bb6c934e Parsing Schema : C##YJAQUIER Test Plan Creator : C##YJAQUIER SQL Text : SELECT /* Yannick */ * FROM app01.test1 WHERE id=1 ERRORS SECTION --------------------------------------------------------------------------------------------- ORA-16963: The given user or schema is not supported by this feature. ---------------------------------------------------------------------------------------------
References
- What Has Real-time SQL Plan Management Done for You?
- What is automatic SQL plan management and why should you care?





