Short reminder, in the two previous articles I made up two test scenarios of delving into the Auto SQL Plan Management.
In the first part I constructed some fairly obvious conditions for Automatic SQL Plan Management to operate — it worked exactly as expected.
The choice was based on obvious metrics: lower CPU usage, fewer logical reads, and shorter elapsed time.
In the second part, I devised another test scenario to make Auto-SPM reach a certain compromise.
In this case, the decision was less straightforward — the selected SQL Plan exhibited significantly fewer logical reads, which appeared to outweigh its longer elapsed time.
While the duration was roughly twice as long from a mathematical standpoint, the difference remained imperceptible from a human perspective.
It is worth noting that both the tested and the reference SQL Plans completed within milliseconds, suggesting that this compromise likely fell within a margin of tolerance accepted by the Auto-SPM mechanism.
In my opinion, this approach appears quite reasonable. A query that executes relatively quickly but requires a substantial number of logical I/Os (disregarding the fact that they were all physical reads as I ran it with offload on Exadata machine) may not remain fast under high concurrency. Under such conditions, it could eventually saturate CPU resources, leading to a piggyback effect and degraded performance.
It should be emphasized (and I will include this in the summary) that three primary metrics appear to be evaluated: elapsed time, CPU time, and the number of logical reads accompanied by – let me I put it this way – certain margin of tolerance in regards to the elapsed time.
The exact formula — that is, the weighted combination of these factors used to determine whether one SQL Plan is preferred over another — remains a mystery. However, it seems evident that the optimizer cost associated with a SQL Plan is not considered in this evaluation.
So, being intrigued by the above depicted circumstances, I made up yet another scenario: I have created test SQL which leaves no doubts as far as the elapsed time is concerned (it runs not just slightly faster, but much faster), but on the other hand it will consume significantly more resources.
This time I applied much larger range: 10% (see the predicate: ID <= 2e6).
See which baseline has been chosen and “accepted”.
Here it is.
First execution as SYS won’t be intercepted by STS – I ran it essentially to gain the sql_id: 26q6d22722tqh in order to (10046) trace it.
Nonetheless, I intentionally left the runtime statistics in place for the initial analysis.
Most notably, the CBO decided to use the index (it could, as I made it “visible”) and ended up on ~3 seconds. Underneath, it obviously carried out the classical cost-based calculations based on comparing the cost of FTS and Index Access.
This is wrong decision (on Exadata) as it will turn out shortly.
Word of reassurance: the strategy of selecting the proper baseline would most certainly work reasonably on non-Exadata systems, as well (it’d be naturally self-adjustable).
Why? As mentioned, SPM clearly applies the resource usage analysis based on true (runtime) values and the elapsed time.
So, if a full table scan without offloading turns out to last longer than an index-based execution, it will simply be discarded in favor of the index-based one.
Admittedly, pure CBO costing of FTS is also indirectly based on available resource capabilities and utilization (if the workload statistics are in use), but it’s still “a theory” derived from the system statistics.
Coming back to the tests – let’s get the SQL_ID.
Here we have it :
11:50:57 SYS@MYTCDB1->MYPDB> @test4b
11:51:01 SYS@MYTCDB1->MYPDB> alter index TSTUS.IDX_TESTASPM_ID &set_index2_vis_invisb ;
Enter value for set_index2_vis_invisb: visible
old 1: alter index TSTUS.IDX_TESTASPM_ID &set_index2_vis_invisb
new 1: alter index TSTUS.IDX_TESTASPM_ID visible
Index altered.
11:51:06 SYS@MYTCDB1->MYPDB> @fb
11:51:06 SYS@MYTCDB1->MYPDB> set echo on
11:51:06 SYS@MYTCDB1->MYPDB> alter system flush buffer_cache ;
System altered.
11:51:07 SYS@MYTCDB1->MYPDB> set echo off
11:51:07 SYS@MYTCDB1->MYPDB> set feedback on sql_id
11:51:07 SYS@MYTCDB1->MYPDB> set echo off
11:51:07 SYS@MYTCDB1->MYPDB> SET AUTOTRACE TRACE EXPLAIN STATISTICS
11:51:07 SYS@MYTCDB1->MYPDB> set echo on
11:51:07 SYS@MYTCDB1->MYPDB> select /* PS04_TESTASPM_RANGE_2E6 */ max(padding) from TSTUS.TESTASPM where ID <= 2e6 ;
1 row selected.
SQL_ID: 26q6d22722tqh
Elapsed: 00:00:02.69
Execution Plan
----------------------------------------------------------
Plan hash value: 3639302403
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 21146 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 39 | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| TESTASPM | 2000K| 74M| 21146 (1)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_TESTASPM_ID | 2000K| | 4745 (1)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID"<=2e6)
Statistics
----------------------------------------------------------
70 recursive calls
4 db block gets
20949 consistent gets
18916 physical reads
1437216 redo size
638 bytes sent via SQL*Net to client
108 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
7 sorts (memory)
0 sorts (disk)
1 rows processed
Below I will set up the SQL trace of my SQL_ID, then I will run the true test comprising two executions of the same SQL, one without index, so with full table scan, and one with Index Range Scan.
11:51:10 SYS@MYTCDB1->MYPDB> @trsql
11:51:55 SYS@MYTCDB1->MYPDB> alter system set events 'sql_trace [sql:&sql_id_to_10046] level 12';
Enter value for sql_id_to_10046: 26q6d22722tqh
old 1: alter system set events 'sql_trace [sql:&sql_id_to_10046] level 12'
new 1: alter system set events 'sql_trace [sql:26q6d22722tqh] level 12'
System altered.
Test scenario
Finally I will execute the test SQL with full table scan (index toggled to “invisible”), but before, let me explain what I intended to achieve here.
I executed the same query with:
1). full table scan (with short elapsed time but using more resources)
2). index access (lasting much longer, but engaging fewer resources)
Note (once again): I’m running these tests on an Exadata machine and took advantage of its offload capabilities.
This explains why the Full Table Scan completed extremely fast — in less than one second.
Execution with Full Table Scan (45 ms)
The noted elapsed time of both will be very similar, with FTS getting the result slightly faster, 40ms and 100ms.
Now we’ll observe that the Auto-SPM had to “struggle” and apparently worked out some sort of compromise as the associated figures weren’t that obvious any longer.
Reminder: the range of IDs is this time significantly larger, so not just 20 as in the previous post, but 100k (out of 20 mln).
This obviously does not make the Index Range Scan as attractive.
In addition, I abandoned the idea of deliberately hindering the Full Table Scan and let it run with the full throttle, utilizing the smart-scan feature of Exadata.
Note: (again) I am running the tests on Exadata machine and take advantage on the offload capabilities. This is why the FTS made it really fast, that is below 1 sec.
11:53:45 TSTUS @ MYPDB> @test4b
11:53:48 TSTUS @ MYPDB>
11:53:48 TSTUS @ MYPDB> alter index TSTUS.IDX_TESTASPM_ID &set_index2_vis_invisb ;
Enter value for set_index2_vis_invisb: invisible
old 1: alter index TSTUS.IDX_TESTASPM_ID &set_index2_vis_invisb
new 1: alter index TSTUS.IDX_TESTASPM_ID invisible
Index altered.
11:53:53 TSTUS @ MYPDB> @fb
11:53:53 TSTUS @ MYPDB> alter system flush buffer_cache ;
System altered.
11:53:53 TSTUS @ MYPDB> set echo off
11:53:53 TSTUS @ MYPDB> set feedback on sql_id
11:53:53 TSTUS @ MYPDB> set echo off
INST_ID SID SERIAL# OSPID PID TRACEFILE
------- ----------- ------- ---------- ---------- ----------------------------------------------------------------------------------------------------
1 2585 40633 289823 93 MYTCDB1_ora_289823.trc
1 row selected.
11:53:54 TSTUS @ MYPDB> SET AUTOTRACE TRACE EXPLAIN STATISTICS
11:53:54 TSTUS @ MYPDB> select /* PS04_TESTASPM_RANGE_2E6 */ max(padding) from TSTUS.TESTASPM where ID <= 2e6 ;
1 row selected.
SQL_ID: 26q6d22722tqh
Elapsed: 00:00:00.30
Execution Plan
----------------------------------------------------------
SQL_ID: 74pt1d53bbfq0
Plan hash value: 2834813234
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 39441 (1)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 39 | | |
|* 2 | TABLE ACCESS STORAGE FULL| TESTASPM | 2000K| 74M| 39441 (1)| 00:00:02 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - storage("ID"<=2e6)
filter("ID"<=2e6)
Statistics
----------------------------------------------------------
1089 recursive calls
0 db block gets
145656 consistent gets
144407 physical reads
9348 redo size
638 bytes sent via SQL*Net to client
108 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
79 sorts (memory)
0 sorts (disk)
1 rows processed
As expected, the first SQL Trace file appeared id the Diag Trace directory :
SQL Trace the FTS :
# grep 26q6d22722tqh *.trc
MYTCDB1_ora_289823.trc:PARSING IN CURSOR #139978106875160 len=86 dep=0 uid=134 oct=3 lid=134 tim=6172993576366 hv=2384553680 ad='8c91ee78' sqlid='26q6d22722tqh'Let’s take the immediate glance at it (raw 10046 SQL Trace) – just important excerpts :
4770 PARSING IN CURSOR #139978106875160 len=86 dep=0 uid=134 oct=3 lid=134 tim=6172993576366 hv=2384553680 ad='8c91ee78' sqlid='26q6d22722tqh'
4771 select /* PS04_TESTASPM_RANGE_2E6 */ max(padding) from TSTUS.TESTASPM where ID <= 2e6
4772 END OF STMT
4773 EXEC #139978106875160:c=30,e=30,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2834813234,tim=6172993576366
4774 WAIT #139978106875160: nam='gc obj ckpt direct read' ela=26 pdb#=6 tsn#=6 obj#=82773 obj#=11061 tim=6172993576632
4775 WAIT #139978106875160: nam='cell smart table scan' ela=207 cellhash#=976102048 p2=0 p3=0 obj#=82773 tim=6172993577662
4776 WAIT #139978106875160: nam='cell smart table scan' ela=234 cellhash#=2743020163 p2=0 p3=0 obj#=82773 tim=6172993578100
4777 WAIT #139978106875160: nam='cell smart table scan' ela=206 cellhash#=2957023425 p2=0 p3=0 obj#=82773 tim=6172993578505
4778 WAIT #139978106875160: nam='cell disk open' ela=138 cellid#=2957023425 where=2 p3=0 obj#=82773 tim=6172993578934
4779 WAIT #139978106875160: nam='cell smart table scan' ela=4 cellhash#=976102048 p2=0 p3=0 obj#=82773 tim=6172993580360
4780 WAIT #139978106875160: nam='cell smart table scan' ela=5 cellhash#=2743020163 p2=0 p3=0 obj#=82773 tim=6172993580413
(…)
... many more cell smart table scans
(…)
4837 WAIT #139978106875160: nam='cell smart table scan' ela=24 cellhash#=2957023425 p2=0 p3=0 obj#=82773 tim=6172993620815
4838 WAIT #139978106875160: nam='cell smart table scan' ela=100 cellhash#=2957023425 p2=0 p3=0 obj#=82773 tim=6172993621183
4839 WAIT #139978106875160: nam='cell smart table scan' ela=140 cellhash#=2957023425 p2=0 p3=0 obj#=82773 tim=6172993621824
4840 FETCH #139978106875160:c=29345,e=45812,p=144284,cr=144288,cu=0,mis=0,r=1,dep=0,og=1,plh=2834813234,tim=6172993622248
4841 STAT #139978106875160 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=144288 pr=144284 pw=0 str=1 time=45812 dr=144284 dw=0 us)'
4842 STAT #139978106875160 id=2 cnt=180411 pid=1 pos=1 obj=82773 op='TABLE ACCESS STORAGE FULL TESTASPM (cr=144288 pr=144284 pw=0 str=1 time=29065 dr=144284 dw=0 us cost=39441 size=78000000 card=2000000)'Let’s also control the current status of STS (Capture Task: I have changed it to 60sec) :
TASK_NAME INTERVAL STATUS LAST_SCHEDULE_TIME CURRENT_TIME
---------------------------------------------------------------- -------- -------------------- ------------------- -------------------
Auto STS Capture Task 60 SUCCEEDED 13-10-2025 12:01:23 13-10-2025 12:01:23
Elapsed: 00:00:00.01
PLAN_TIMESTAMP SQL_ID SQL_TEXT PARSING_SCHEMA_NAME ELAPSED_TIME CPU_TIME BUFFER_GETS DISK_READS EXECS PARSING_US FIRST_LOAD_TIME SQL_PLAN_BASELINE OPTIMIZER_COST
------------------- --------------- -------------------------------------------------- ------------------------------ ------------ ------------ ----------- -------------------- ------------ ---------- ------------------- ------------------------------ --------------
13-10-2025 11:53:54 26q6d22722tqh select /* PS04_TESTASPM_RANGE_2E6 */ max(padding) TSTUS 258,353 232,444 145,656 144,407 1 TSTUS 2025-10-13/11:53:54 39441
from TSTUS.TESTASPM where ID <= 2e6
If you take a look at the result of the second checking query, it closely matches the data from the FETCH phase withdrawn from the SQL Trace (10046) for the FTS as far as number of PIOs = DISK_READS and (alleged) LIOs = BUFFER_GETS is concerned :
FETCH # c=29345,e=45812,p=144284,cr=144288
There is still an oddity which refers to CPU_TIME and ELAPSED_TIME (c and e) – as if it the real cpu time was multiplied by ~10 by the SPM mechanism.
But also this will ultimately accurately match the data from 10046 (reason unclear to me why not from the start).
Execution with Range Index Scan (4 seconds)
Back to test.
Now I’ll execute the the same SQL statement, this time with the index set to visible :
12:03:39 TSTUS @ MYPDB> @test4b
12:03:42 TSTUS @ MYPDB> alter index TSTUS.IDX_TESTASPM_ID &set_index2_vis_invisb ;
Enter value for set_index2_vis_invisb: visible
old 1: alter index TSTUS.IDX_TESTASPM_ID &set_index2_vis_invisb
new 1: alter index TSTUS.IDX_TESTASPM_ID visible
Index altered.
12:03:45 TSTUS @ MYPDB> @fb
12:03:45 TSTUS @ MYPDB> set echo on
12:03:45 TSTUS @ MYPDB> alter system flush buffer_cache ;
System altered.
12:03:45 TSTUS @ MYPDB> set echo off
12:03:45 TSTUS @ MYPDB> set feedback on sql_id
12:03:45 TSTUS @ MYPDB> set echo off
INST_ID SID SERIAL# OSPID PID TRACEFILE
------- ----------- ------- ---------- ---------- ----------------------------------------------------------------------------------------------------
1 2902 54436 389382 117 /MYTCDB1_ora_389382.trc
1 row selected.
12:03:45 TSTUS @ MYPDB> SET AUTOTRACE TRACE EXPLAIN STATISTICS
12:03:45 TSTUS @ MYPDB> set echo on
12:03:45 TSTUS @ MYPDB> select /* PS04_TESTASPM_RANGE_2E6 */ max(padding) from TSTUS.TESTASPM where ID <= 2e6 ;
1 row selected.
SQL_ID: 26q6d22722tqh
Elapsed: 00:00:03.99
Execution Plan
----------------------------------------------------------
SQL_ID: 3fb7a70m43cjt
Plan hash value: 2834813234
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 39441 (1)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 39 | | |
|* 2 | TABLE ACCESS STORAGE FULL| TESTASPM | 2000K| 74M| 39441 (1)| 00:00:02 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - storage("ID"<=2e6)
filter("ID"<=2e6)
Note
-----
- SQL plan baseline "SQL_PLAN_datjfmtpn0jvcae0e4dcc" used for this statement
Statistics
----------------------------------------------------------
833 recursive calls
109 db block gets
21861 consistent gets
19013 physical reads
1469224 redo size
638 bytes sent via SQL*Net to client
108 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
52 sorts (memory)
0 sorts (disk)
1 rows processed
Ouch.. wait –
it took nearly 4 seconds with the same SQL Plan, that is: with full table scan, which previously was back in less than 1 sec!
On top, Oracle reported that it had utilized the SQL plan baseline (SQL_PLAN_datjfmtpn0jvcae0e4dcc) which supposed bring improvement.
What went wrong?
This is where SQL Trace comes in handy (yes — it was also generated for the second execution of that SQL_ID).
So, the SQL Trace files available so far are:
# grep 26q6d22722tqh *.trc
1.
MYTCDB1_ora_289823.trc:PARSING IN CURSOR #139978106875160 len=86 dep=0 uid=134 oct=3 lid=134 tim=6172993576366 hv=2384553680 ad='8c91ee78' sqlid='26q6d22722tqh'
2.
MYTCDB1_ora_389382.trc: value="<notes><fg_spm><sql_id>26q6d22722tqh</sql_id><test_phv>522503748</test_phv><ref_phv>2920173004</ref_phv><test_ofe>23.1.0</test_ofe><ref_ofe>23.1.0</ref_ofe><ver>normal</ver><rfp>1</rfp><rep>1</rep><osfe>1</osfe><oln>0</oln><hfe>0</hfe><tosfe>17</tosfe><to"...
MYTCDB1_ora_389382.trc:PARSING IN CURSOR #140301493204024 len=86 dep=0 uid=134 oct=3 lid=134 tim=6173584780502 hv=2384553680 ad='8c91ee78' sqlid='26q6d22722tqh'
MYTCDB1_ora_389382.trc: value="<notes><fg_spm><sql_id>26q6d22722tqh</sql_id><test_phv>522503748</test_phv><ref_phv>2920173004</ref_phv><test_ofe>23.1.0</test_ofe><ref_ofe>23.1.0</ref_ofe><ver>normal</ver><rfp>1</rfp><rep>1</rep><osfe>1</osfe><oln>0</oln><hfe>0</hfe><tosfe>17</tosfe><to"...The second one (MYTCDB1_ora_389382.trc) is obviously more interesting and we’re going to go through it (I have enclosed the comments directly in the dump, markig important excerpts) :
5688 PARSING IN CURSOR #140301493204024 len=86 dep=0 uid=134 oct=3 lid=134 tim=6173584780502 hv=2384553680 ad='8c91ee78' sqlid='26q6d22722tqh'
5689 select /* PS04_TESTASPM_RANGE_2E6 */ max(padding) from TSTUS.TESTASPM where ID <= 2e6
5690 END OF STMT
5691 EXEC #140301493204024:c=118,e=118,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3639302403,tim=6173584780501
-- now it's getting interesting :
-- seeing the names of the wait events and two different object_ids (Index: 82787 and Table: 82773), this excerpt represents the SQL execution with the Index Range Scan, not Full Table Scan
5692 WAIT #140301493204024: nam='cell single block physical read: RDMA' ela=75 cellhash#=2957023425 diskhash#=502742783 bytes=8192 obj#=82787 tim=6173584780725
5693 WAIT #140301493204024: nam='gc cr grant 2-way' ela=175 p1=67 p2=2625207 p3=1 obj#=82787 tim=6173584781028
5694 WAIT #140301493204024: nam='cell single block physical read: RDMA' ela=49 cellhash#=2743020163 diskhash#=3295160665 bytes=8192 obj#=82787 tim=6173584781131
5695 WAIT #140301493204024: nam='cell single block physical read: RDMA' ela=33 cellhash#=2957023425 diskhash#=502742783 bytes=8192 obj#=82787 tim=6173584781252
(…)
(…) plenty of single block access waits: index-leaf -> table block
(…)
33985 WAIT #140301493204024: nam='cell single block physical read: RDMA' ela=27 cellhash#=2957023425 diskhash#=1124687604 bytes=8192 obj#=82787 tim=6173588469066
33986 WAIT #140301493204024: nam='cell single block physical read: RDMA' ela=24 cellhash#=976102048 diskhash#=319733354 bytes=8192 obj#=82773 tim=6173588469159
33987 WAIT #140301493204024: nam='cell single block physical read: RDMA' ela=23 cellhash#=976102048 diskhash#=319733354 bytes=8192 obj#=82773 tim=6173588469257
33988 WAIT #140301493204024: nam='cell single block physical read: RDMA' ela=22 cellhash#=976102048 diskhash#=319733354 bytes=8192 obj#=82773 tim=6173588469351
33989 WAIT #140301493204024: nam='cell single block physical read: RDMA' ela=23 cellhash#=2957023425 diskhash#=1124687604 bytes=8192 obj#=82787 tim=6173588469415
33990 WAIT #140301493204024: nam='cell single block physical read: RDMA' ela=24 cellhash#=976102048 diskhash#=319733354 bytes=8192 obj#=82773 tim=6173588469520
-- Finally "the summary" (the p=PIOs and cr=LIOs closely matching the "physical reads" and "consistent gets" from the AUTOTRACE TRACE above) :
33991 FETCH #140301493204024:c=2989555,e=3688976,p=18886,cr=20897,cu=0,mis=0,r=1,dep=0,og=1,plh=3639302403,tim=6173588469580
-- In addition, the true SQL Plan - clearly Index Range Scan :
34821 STAT #140301493204024 id=2 cnt=2000000 pid=1 pos=1 obj=82773 op='TABLE ACCESS BY INDEX ROWID BATCHED TESTASPM (cr=20897 pr=18886 pw=0 str=1 time=4413918 dr=0 dw=0 us cost=21146 size=78000000 card=2000000)'
34822 STAT #140301493204024 id=3 cnt=2000000 pid=2 pos=1 obj=82787 op='INDEX RANGE SCAN IDX_TESTASPM_ID (cr=4598 pr=4598 pw=0 str=1 time=1375909 dr=0 dw=0 us cost=4745 size=0 card=2000000)'
-- Then comes the known: runtime statistics which were saved in the sqlobj$auxdata.NOTES (XML, #Bind34) :
34066 PARSING IN CURSOR #140301495178016 len=5018 dep=1 uid=0 oct=189 lid=0 tim=6173588472794 hv=3618241547 ad='c04a9b88' sqlid='1fbu2jmbumz0b'
34067 MERGE /*+ INDEX(dest (signature category obj_type plan_id)) */ INTO sqlobj$auxdata dest USING (SELECT :1 signature, :2 category, :3 obj_type, :4 plan_id FROM dual) src ON (dest.signature = src.signature AND dest.category = src.category AND dest.obj_type = src.obj_type AND dest.plan_id = src.plan_id) WHEN MATCHED THEN UPDATE SET description = :5, creator = nvl(:6, cr eator), origin = :7, version = :8, created = :9, last_modified = :10, last_verified = :11, parse_cpu_time = NULL, optimizer_cost = nvl(:12, optimizer_cost), module = nvl(:13, module), action = nvl(:14, action), priority = nvl(:15, priority), optimizer_env = nvl(:16, optimizer_env), bind_data = nvl(:17, bind_data), parsing_schema_name = nvl(:18, parsing_schema_name), executions = nvl(:19, executions), elapsed_time = nvl(:20, elapsed_time), cpu_time = nvl(:21, cpu_time), buffer_gets = nvl(:22, buffer_gets), disk_reads = nvl(:23, disk_reads), direct_writes = nvl(:24, direct_writes), rows_processed = nvl(:25, rows_processed), fetches = nvl(:26, fetches), end_of_fetch_count = nvl(:27, end_of_fetch_count), task_id = nvl(:28, task_id), task_exec_name = nvl(:29, task_exec_name), task_obj_id = nvl(:30, task_obj_id), task_fnd_id = nvl(:31, task_fnd_id), task_rec_id = nvl(:32, task_rec_id), flag s = 0, spare1 = null, spare2 = null, task_con_dbid = nvl(:33, task_con_dbid), foreground_last_verified = :34, notes = :35 WHEN NOT MATCHED THEN INSERT (sig nature, category, obj_type, plan_id, description, creator, origin, version, created, last_modified, last_verified, parse_cpu_time, optimizer_cost, module, action, priority , optimizer_env, bind_data, parsing_schema_name, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, direct_writes, rows_processed, fetches,end_of_fetch_count, task_id, task_exec_name, task_obj_id, task_fnd_id, task_rec_id, flags, spare1, spare2, task_con_dbid, foreground_last_verified, notes) VALUES (:36, :37, :38, :39, :40, :41, :42, :43, :44, :45, null, null, :46, :47, :48, :49, :50, :51, :52, :53, :54, :55, :56, :57, :58, :59, :60, :61, :62, :63, :64, :65, :66, 0, null, null, :67, null, null)
34068 END OF STMTThen
34069 PARSE #140301495178016:c=0,e=150,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3568304656,tim=6173588472793
34070 BINDS #140301495178016:
-- signature
34072 Bind#0
34073 oacdty=01 mxl=32(20) mxlc=00 mal=00 scl=00 pre=00
34074 oacflg=21 fl2=0000 frm=01 csi=873 siz=248 off=0
34075 kxsbbbfp=7f9a7e8525a0 bln=32 avl=20 flg=05
34076 value="15377029235642222444"
(...)
(...)
34220 Bind#33
34221 oacdty=12 mxl=07(07) mxlc=00 mal=00 scl=00 pre=00
34222 oacflg=11 fl2=0000 frm=00 csi=00 siz=8 off=0
34223 kxsbbbfp=a2ebf918 bln=07 avl=07 flg=09
34224 value="10/13/2025 12:3:49"
34225 Bind#34
34226 oacdty=96 mxl=2000(625) mxlc=00 mal=00 scl=00 pre=00
34227 oacflg=11 fl2=0000 frm=01 csi=873 siz=2000 off=0
34228 kxsbbbfp=84c32538 bln=2000 avl=625 flg=09
34229 value="<notes><fg_spm><sql_id>26q6d22722tqh</sql_id><test_phv>522503748</test_phv><ref_phv>2920173004</ref_phv><test_ofe>23.1.0</test_ofe><ref_ofe>23.1.0</ref_ofe><ver>normal</ver><rfp>1</rfp><rep>1</rep><osfe>1</osfe><oln>0</oln><hfe>0</hfe><tosfe>17</tosfe><to"...
Before I’ll jump to presenting of the formatted version of NOTES xml tree (just like before), here’s the formatted MERGE from above.
So what do we see here?
If there us such, data for already present SQL Plan will get updated, burt the “newcomer” will get inserted into sqlobj$auxdata – obviously along with the intercepted runtime stats:
MERGE /*+ INDEX(dest (signature category obj_type plan_id)) */ INTO sqlobj$auxdata dest USING
(SELECT :1 signature,
:2 category,
:3 obj_type,
:4 plan_id
FROM dual) src ON (dest.signature = src.signature
AND dest.category = src.category
AND dest.obj_type = src.obj_type
AND dest.plan_id = src.plan_id)
WHEN MATCHED THEN
UPDATE
SET description = :5,
creator = nvl(:6, cr eator),
origin = :7,
VERSION = :8,
created = :9,
last_modified = :10,
last_verified = :11,
parse_cpu_time = NULL,
optimizer_cost = nvl(:12, optimizer_cost),
MODULE = nvl(:13, MODULE),
action = nvl(:14, action),
priority = nvl(:15, priority),
optimizer_env = nvl(:16, optimizer_env),
bind_data = nvl(:17, bind_data),
parsing_schema_name = nvl(:18, parsing_schema_name),
executions = nvl(:19, executions),
elapsed_time = nvl(:20, elapsed_time),
cpu_time = nvl(:21, cpu_time),
buffer_gets = nvl(:22, buffer_gets),
disk_reads = nvl(:23, disk_reads),
direct_writes = nvl(:24, direct_writes),
rows_processed = nvl(:25, rows_processed),
fetches = nvl(:26, fetches),
end_of_fetch_count = nvl(:27, end_of_fetch_count),
task_id = nvl(:28, task_id),
task_exec_name = nvl(:29, task_exec_name),
task_obj_id = nvl(:30, task_obj_id),
task_fnd_id = nvl(:31, task_fnd_id),
task_rec_id = nvl(:32, task_rec_id),
flags = 0,
spare1 = NULL,
spare2 = NULL,
task_con_dbid = nvl(:33, task_con_dbid),
foreground_last_verified = :34,
notes = :35
WHEN NOT MATCHED THEN
INSERT (signature,
category,
obj_type,
plan_id,
description,
creator,
origin,
VERSION,
created,
last_modified,
last_verified,
parse_cpu_time,
optimizer_cost,
MODULE,
action,
priority ,
optimizer_env,
bind_data,
parsing_schema_name,
executions,
elapsed_time,
cpu_time,
buffer_gets,
disk_reads,
direct_writes,
rows_processed,
fetches,
end_of_fetch_count,
task_id,
task_exec_name,
task_obj_id,
task_fnd_id,
task_rec_id,
flags,
spare1,
spare2,
task_con_dbid,
foreground_last_verified,
notes)
VALUES (:36, :37, :38, :39, :40, :41, :42, :43, :44, :45, NULL, NULL, :46, :47, :48, :49, :50, :51, :52, :53, :54, :55, :56, :57, :58, :59, :60, :61, :62, :63, :64, :65, :66, 0, NULL, NULL, :67, NULL, NULL) The XML from the #Bind34 (so, XML NOTES column) upon formatting reports this shown below.
As previously, I have added the FETCHes from both SQL Trace files: the Full Table Scan and the Index Range Scan (note, FETCH phase reports microseconds for the time-related values, whereas NOTES has them converted to milliseconds (microseconds/1000)).
# cat xml_fine.xml
<?xml version="1.0"?>
<notes>
<fg_spm>
<sql_id>26q6d22722tqh</sql_id>
<test_phv>522503748</test_phv>
<ref_phv>2920173004</ref_phv>
<test_ofe>23.1.0</test_ofe>
<ref_ofe>23.1.0</ref_ofe>
<ver>normal</ver>
<rfp>1</rfp>
<rep>1</rep>
<osfe>1</osfe>
<oln>0</oln>
<hfe>0</hfe>
<tosfe>17</tosfe>
<toln>0</toln>
<thfe>0</thfe>
<comp_time>2025-10-13/12:03:45</comp_time>
<ref_hp>
<cpu>202</cpu>
<bg>1368</bg>
</ref_hp>
<test_hp>
<cpu>216</cpu>
<bg>1028</bg>
</test_hp>
<ref_perf>
<bg>144288</bg>
<cpu>30</cpu>
<elps>258</elps>
</ref_perf>
FETCH:c=29345,e=45812,p=144284,cr=144288,cu=0,mis=0,r=1,dep=0,og=1,plh=2834813234
<test_perf>
<bg>20897</bg>
<cpu>2989</cpu>
<elps>3689</elps>
</test_perf>
FETCH:c=2989555,e=3688976,p=18886,cr=20897,cu=0,mis=0,r=1,dep=0,og=1,plh=3639302403
<status>worse</status>
<ver_time>2025-10-13/12:03:49</ver_time>
</fg_spm>
</notes>
I feel a bit embarrassed to present you with this rainbow-style set of inline comments – but at least it should make it easier to match the corresponding values.
Their meaning is almost self-explanatory, but if in doubt get to the previous post.
Here’s just a brief reminder:
elps = total elapsed time,
cpu = cpu time,
bg (this one is not really intuitive) = number of logical reads (possibly alleged).
The FETCH gives you the same, just in microseconds, so c = cpu time, e = total elapsed time, cr = logical reads (consistent reads in 10046).
Further:
ref_perf – the one with FTS (being referenced to)
test_perf – currently tested, so with Index Range Scan.
As you can spot, most of these values do match (“most”, as for to me unknown reason the cpu time reported by the SQL Trace (c) has been increased in the XML – but just in case of the ref_perf).
The test_perf case can be 100% matched (remember to convert us to ms).
Most importantly — and this should be clearly emphasized — this time the SQL Plan with a Full Table Scan (FTS) is the winner.
See: <status>worse</status> – this is the “verdict” that resulted from comparing test_perf (Index Access) with ref_perf (FTS), even though the FTS reported much higher number of LIOs. The decisive factor, however, seems to be the elapsed time.
Compared to the previous example (discussed in the previous post), the elapsed time for the SQL Plan using an Index Range Scan is significantly higher:
3.6 seconds versus 258 ms for the FTS plan (or even less — 45 ms, according to the SQL Trace).
So, among all the weighted metrics — LIOs, CPU time, and elapsed time — there must have been “the straw that broke the camel’s back.” This time, that “straw” was almost certainly the elapsed time.
Let’s take a look at the status of the baseline evaluation (note SQL_PLAN_datjfmtpn0jvcae0e4dcc with the status: ACC (accepted).
Taking opportunity, I believe, there’s a small bug in reporting the values by DBA_SQL_PLAN_BASELINE.
Namely, the cost shown for the baseline with FTS is not 21145 (which is the cost of Index Range Scan), but 39441.
On the other hand, this time the ELAPSED_TIME and CPU_TIME represent almost the same values as the FETCH for FTS does (expressed in microseconds).
Anyhow, it’s quite visible how the baseline has “matured” over time. Honestly, at some point I simply gave up trying to follow this process any further — and to be fair (to myself), that was never really the primary goal of these articles.
The point is, Auto-SPM once again – in my opinion – made the right decision, choosing the baseline with a Full Table Scan (44 ms) over the CBO-driven Index Range Scan that took nearly four seconds, although it surely must have taken into account the remaining factors: LIOs and CPU Time.
Here it is:
SYS@MYTCDB1->MYPDB> @lssqlsts
TASK_NAME INTERVAL STATUS LAST_SCHEDULE_TIME CURRENT_TIME
---------------------------------------------------------------- -------- -------------------- ------------------- -------------------
Auto STS Capture Task 60 SUCCEEDED 13-10-2025 15:36:02 13-10-2025 15:37:10
Elapsed: 00:00:00.01
PLAN_TIMESTAMP SQL_ID SQL_TEXT PARSING_SCHEMA_NAME ELAPSED_TIME CPU_TIME BUFFER_GETS DISK_READS EXECS PARSING_US FIRST_LOAD_TIME SQL_PLAN_BASELINE OPTIMIZER_COST
------------------- --------------- -------------------------------------------------- ------------------------------ ------------ ------------ ----------- -------------------- ------------ ---------- ------------------- ------------------------------ --------------
13-10-2025 11:53:54 26q6d22722tqh select /* PS04_TESTASPM_RANGE_2E6 */ max(padding) TSTUS 258,353 232,444 145,656 144,407 1 TSTUS 2025-10-13/11:53:54 39441
from TSTUS.TESTASPM where ID <= 2e6
13-10-2025 12:03:45 26q6d22722tqh select /* PS04_TESTASPM_RANGE_2E6 */ max(padding) TSTUS 3,932,719 3,206,519 21,925 19,013 1 TSTUS 2025-10-13/11:53:54 21146
from TSTUS.TESTASPM where ID <= 2e6
Elapsed: 00:00:00.06
SIGNATURE SQL_TEXT PLAN_NAME CREATED ENABLED ACC REP OPTIMIZER_COST ELAPSED_TIME CPU_TIME BUFFER_GETS FOREGROUND_LAST_VER
------------------------- -------------------------------------------------- ------------------------------ ------------------- -------------------- --- --- -------------- ------------ ------------ ----------- -------------------
15377029235642222444 select /* PS04_TESTASPM_RANGE_2E6 */ max(padding) SQL_PLAN_datjfmtpn0jvc1f24c644 13-10-2025 12:03:45 YES NO YES 21145 3,689,130 2,989,676 20,897 13-10-2025 12:03:49
from TSTUS.TESTASPM where ID <= 2e6
15377029235642222444 select /* PS04_TESTASPM_RANGE_2E6 */ max(padding) SQL_PLAN_datjfmtpn0jvcae0e4dcc 13-10-2025 12:03:45 YES YES YES 21145 44,512 29,700 144,288 13-10-2025 15:08:48
from TSTUS.TESTASPM where ID <= 2e6
Let’s pivot the two entries from dba_sql_plan_baselines.
Then let’s take a brief look at the associated XMLs (NOTES).
The upper one represents the Index Range Scan, whereas the lower one: the FTS.
It’s also quite obvious that the NOTES (XML) in dba_sql_plan_baselines comes from the NOTES referenced earlier while examining the entries in sqlobj$auxdata.
SYS@MYTCDB1->MYPDB> select * from dba_sql_plan_baselines where SIGNATURE = 15377029235642222444
15:19:30 2 @pr
==============================
SIGNATURE : 15377029235642222444
SQL_HANDLE : SQL_d5662e9e6b40476c
SQL_TEXT : select /* PS04_TESTASPM_RANGE_2E6 */ max(padding) from TSTUS.TESTASPM where ID <= 2e6
PLAN_NAME : SQL_PLAN_datjfmtpn0jvc1f24c644
CREATOR : TSTUS
ORIGIN : FOREGROUND-CAPTURE
PARSING_SCHEMA_NAME : TSTUS
DESCRIPTION :
VERSION : 23.0.0.0.0
CREATED : 13-OCT-25 12.03.45.621022 PM
LAST_MODIFIED : 13-OCT-25 03.08.48.000000 PM
LAST_EXECUTED :
LAST_VERIFIED : 13-OCT-25 12.58.26.000000 PM
ENABLED : YES
ACCEPTED : NO
FIXED : NO
REPRODUCED : YES
AUTOPURGE : YES
ADAPTIVE : NO
OPTIMIZER_COST : 21145
MODULE : SQL*Plus
ACTION :
EXECUTIONS : 1
ELAPSED_TIME : 3689130
CPU_TIME : 2989676
BUFFER_GETS : 20897
DISK_READS : 1
DIRECT_WRITES : 140727944816632
ROWS_PROCESSED : 0
FETCHES : 0
END_OF_FETCH_COUNT :
FOREGROUND_LAST_VERIFIED : 13-OCT-25 12.03.49.000000 PM
NOTES :
<notes><plan_id>522503748</plan_id><flags>1033</flags><fg_spm><sql_id>26q6d22722tqh</sql_id><test_phv>522503748</test_phv><ref_phv>2920173004</ref_phv><test_ofe>23.1.0</test_ofe><ref_ofe>23.1.0</ref_ofe><ver>normal</ver><rfp>1</rfp><rep>1</rep><osfe>1</osfe><oln>0</oln><hfe>0</h fe><tosfe>17</tosfe><toln>0</to
ln><thfe>0</thfe><comp_time>2025-10-13/12:03:45</comp_time><ref_hp><cpu>202</cpu><bg>1368</bg></ref_hp><test_hp><cpu>216</cpu><bg>1028</bg></test_hp><ref_perf><bg>144288</bg><cpu>30</cpu><elps>258</elps></ref_perf><test_perf><bg>20897</bg><cpu>2989</cpu><elps>3689</elps></test_perf><status>worse</status><ver_time>2025-10-13/12:03:49</ver_time></fg_spm></notes>
==============================
SIGNATURE : 15377029235642222444
SQL_HANDLE : SQL_d5662e9e6b40476c
SQL_TEXT : select /* PS04_TESTASPM_RANGE_2E6 */ max(padding) from TSTUS.TESTASPM where ID <= 2e6
PLAN_NAME : SQL_PLAN_datjfmtpn0jvcae0e4dcc
CREATOR : TSTUS
ORIGIN : FOREGROUND-CAPTURE
PARSING_SCHEMA_NAME : TSTUS
DESCRIPTION :
VERSION : 23.0.0.0.0
CREATED : 13-OCT-25 12.03.45.524015 PM
LAST_MODIFIED : 13-OCT-25 03.08.48.000000 PM
LAST_EXECUTED : 13-OCT-25 03.08.48.000000 PM
LAST_VERIFIED :
ENABLED : YES
ACCEPTED : YES
FIXED : NO
REPRODUCED : YES
AUTOPURGE : NO
ADAPTIVE : NO
OPTIMIZER_COST : 21145
MODULE : SQL*Plus
ACTION :
EXECUTIONS : 1
ELAPSED_TIME : 44512
CPU_TIME : 29700
BUFFER_GETS : 144288
DISK_READS : 140736970343232
DIRECT_WRITES : 404954793
ROWS_PROCESSED :
FETCHES :
END_OF_FETCH_COUNT :
FOREGROUND_LAST_VERIFIED : 13-OCT-25 03.08.48.000000 PM
NOTES :
<notes><plan_id>2920173004</plan_id><flags>1059</flags><fg_spm><sql_id>26q6d22722tqh</sql_id><test_phv>2920173004</test_phv><ref_phv>522503748</ref_phv><test_ofe>23.1.0</test_ofe><ref_ofe>0.0.0</ref_ofe><ver>reverse</ver><rfp>1</rfp><rep>1</rep><osfe>1</osfe><oln>0</oln><hfe>0</ hfe><tosfe>24</tosfe><toln>0</t
oln><thfe>0</thfe><comp_time>2025-10-13/15:08:48</comp_time><ref_hp/><test_hp><cpu>157</cpu><bg>765</bg></test_hp><ref_perf><bg>20897</bg><cpu>2989</cpu><elps>3689</elps></ref_perf><test_perf><bg>144288</bg><cpu>29</cpu><elps>44</elps></test_perf><status>better</status><ver_time >2025-10-13/15:08:48</ver_time>
</fg_spm></notes>
Then, if you format both xml entries:– The upper one from 13-OCT-25 12.03.49.000000 PM and the lower one from 13-OCT-25 03.08.48.000000 PM, you’ll receive the following.
The upper one (xml notes upon formatting) :
# cat notes_formatted_upper.xml
<?xml version="1.0"?>
<notes>
<plan_id>522503748</plan_id>
<flags>1033</flags>
<fg_spm>
<sql_id>26q6d22722tqh</sql_id>
<test_phv>522503748</test_phv>
<ref_phv>2920173004</ref_phv>
<test_ofe>23.1.0</test_ofe>
<ref_ofe>23.1.0</ref_ofe>
<ver>normal</ver>
<rfp>1</rfp>
<rep>1</rep>
<osfe>1</osfe>
<oln>0</oln>
<hfe>0</hfe>
<tosfe>17</tosfe>
<toln>0</toln>
<thfe>0</thfe>
<comp_time>2025-10-13/12:03:45</comp_time>
<ref_hp>
<cpu>202</cpu>
<bg>1368</bg>
</ref_hp>
<test_hp>
<cpu>216</cpu>
<bg>1028</bg>
</test_hp>
-- FTS:
<ref_perf>
<bg>144288</bg>
<cpu>30</cpu>
<elps>258</elps>
</ref_perf>
-- Index Access:
<test_perf>
<bg>20897</bg>
<cpu>2989</cpu>
<elps>3689</elps>
</test_perf>
<status>worse</status>
<ver_time>2025-10-13/12:03:49</ver_time>
</fg_spm>
</notes>The lower one (xml notes upon formatting) :
# cat notes_formatted_lower.xml
<?xml version="1.0"?>
<notes>
<plan_id>2920173004</plan_id>
<flags>1059</flags>
<fg_spm>
<sql_id>26q6d22722tqh</sql_id>
<test_phv>2920173004</test_phv>
<ref_phv>522503748</ref_phv>
<test_ofe>23.1.0</test_ofe>
<ref_ofe>0.0.0</ref_ofe>
<ver>reverse</ver>
<rfp>1</rfp>
<rep>1</rep>
<osfe>1</osfe>
<oln>0</oln>
<hfe>0</hfe>
<tosfe>24</tosfe>
<toln>0</toln>
<thfe>0</thfe>
<comp_time>2025-10-13/15:08:48</comp_time>
<ref_hp/>
<test_hp>
<cpu>157</cpu>
<bg>765</bg>
</test_hp>
-- Index Access :
<ref_perf>
<bg>20897</bg>
<cpu>2989</cpu>
<elps>3689</elps>
</ref_perf>
-- FTS: - the accepted one
<test_perf>
<bg>144288</bg>
<cpu>29</cpu>
<elps>44</elps>
</test_perf>
<status>better</status>
<ver_time>2025-10-13/15:08:48</ver_time>
</fg_spm>
</notes>Last but not least, let’s perform a straightforward execution of my test SQL to verify whether — and with what runtime statistics — the baseline with the FTS will actually be applied.
Indeed, it was (see: SQL plan baseline “SQL_PLAN_datjfmtpn0jvcae0e4dcc” used for this statement). The elapsed time (for FTS, offloaded) was also short, as expected.
To mimic the real environment, I set the index to visible.
And by the way, I believe this execution also corresponds to the most recent entry in the XML notes within DBA_SQL_PLAN_BASELINES, if you take a closer look at the timestamps.
15:08:41 SYS@MYTCDB1->MYPDB> @test4b
15:08:44 SYS@MYTCDB1->MYPDB> alter index TSTUS.IDX_TESTASPM_ID &set_index2_vis_invisb ;
Enter value for set_index2_vis_invisb: visible
old 1: alter index TSTUS.IDX_TESTASPM_ID &set_index2_vis_invisb
new 1: alter index TSTUS.IDX_TESTASPM_ID visible
Index altered.
15:08:47 SYS@MYTCDB1->MYPDB> @fb
15:08:47 SYS@MYTCDB1->MYPDB> set echo on
15:08:47 SYS@MYTCDB1->MYPDB> alter system flush buffer_cache ;
System altered.
15:08:48 SYS@MYTCDB1->MYPDB> set echo off
15:08:48 SYS@MYTCDB1->MYPDB> set feedback on sql_id
15:08:48 SYS@MYTCDB1->MYPDB> set echo off
INST_ID SID SERIAL# OSPID PID TRACEFILE
------- ----------- ------- ---------- ---------- ----------------------------------------------------------------------------------------------------
1 2745 35430 356182 95 MYTCDB1_ora_356182.trc
1 row selected.
15:08:48 SYS@MYTCDB1->MYPDB> SET AUTOTRACE TRACE EXPLAIN STATISTICS
15:08:48 SYS@MYTCDB1->MYPDB> set echo on
15:08:48 SYS@MYTCDB1->MYPDB> select /* PS04_TESTASPM_RANGE_2E6 */ max(padding) from TSTUS.TESTASPM where ID <= 2e6 ;
1 row selected.
SQL_ID: 26q6d22722tqh
Elapsed: 00:00:00.26
Execution Plan
----------------------------------------------------------
Plan hash value: 2834813234
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 39441 (1)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 39 | | |
|* 2 | TABLE ACCESS STORAGE FULL| TESTASPM | 2000K| 74M| 39441 (1)| 00:00:02 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - storage("ID"<=2e6)
filter("ID"<=2e6)
Note
-----
- SQL plan baseline "SQL_PLAN_datjfmtpn0jvcae0e4dcc" used for this statement
Statistics
----------------------------------------------------------
521 recursive calls
56 db block gets
145062 consistent gets
144375 physical reads
29896 redo size
638 bytes sent via SQL*Net to client
108 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
50 sorts (memory)
0 sorts (disk)
1 rows processed
Summary
As the prefix in the feature’s name suggests, Auto SQL Plan Management operates automatically and holds the ultimate decision: which SQL Plan gets selected – and thus which one will be applied in your (PROD?) environment – without any manual intervention.
Now, it’s a well-known fact that whenever an experienced DBA hears the word “auto” in reference to an Oracle feature, she makes an indulgent face. The Oracle documentation doesn’t really change that perception :
“The database is able to recover from the suboptimal plan and the application service level is restored. This occurs transparently, without manual intervention.”
However, it does not mention – nor do any of the other available sources I’ve gone through – what mechanism actually controls this behavior.
And I’m sure many DBAs will be understandably hesitant to let the database decide something that is absolutely paramount in the Oracle world when it comes to end-user performance perception: the SQL plan itself.
So much about “being afraid.”
The reassuring word about Auto SQL Plan Management is that it relies on the ultimate metrics that genuinely influence end-user perception.
Notice, it’s not the optimizer COST (which may, and quite often does, rely on inaccurate statistics and thus produce a suboptimal plan).
Nor is it a mechanism for correcting miscalculated cardinalities — albeit that’s a profound concept and much closer to fixing a suboptimal plan (e.g., Adaptive Plans).
From my analysis, the decisive factors are elapsed time, supported by CPU time and the number of logical reads (the latter may be debatable).
How these are weighted, and which formulas sit behind them, seems secondary.
After all, you can’t get any closer to reality and this – in my opinion – makes this feature worth considering.
Leave a Reply