'LAST_EXECUTED of DBA_SQL_PLAN_BASELINES' is Not Updated in Timely Manner with Executions of V$SQL
(Doc ID 2466413.1)
Last updated on JULY 20, 2024
Applies to:
Oracle Database - Enterprise Edition - Version 11.1.0.6 and laterInformation in this document applies to any platform.
Symptoms
- LAST_EXECUTED of DBA_SQL_PLAN_BASELINES is not updated in timely manner and does not match to the execution records of V$SQL:
-- created SPM for sample query
SQL> select sql_handle, plan_name, LAST_MODIFIED, LAST_EXECUTED
from dba_sql_plan_baselines
where sql_text like '%spm_test%'
/
SQL_HANDLE PLAN_NAME LAST_MODIFIED LAST_EXECUTED
------------------------- ------------------------------ ------------------------------ ------------------------------
SQL_026a5caa16ef36f5 SQL_PLAN_04ukwp8bfydrp4bb80211 29-OCT-18 06.45.56.000000 PM 29-OCT-18 06.45.56.000000 PM <<~~~~~~~ Last Executed : 29-Oct-18 06.45.56
--- 1st run of sample query.
SQL> select sql_id,plan_hash_value,executions, parse_calls, first_load_time, sql_plan_baseline
from v$sql where sql_id='3ub0qzx832gmz'
/
SQL_ID PLAN_HASH_VALUE EXECUTIONS PARSE_CALLS FIRST_LOAD_TIME SQL_PLAN_BASELINE
------------- --------------- ---------- ----------- --------------------------------------------------------- ------------------------------
3ub0qzx832gmz 105935909 1 1 2018-10-29/18:45:55 SQL_PLAN_04ukwp8bfydrp4bb80211 <<~~~ executions : 1
--- 2nd run of sample query ..
SQL_ID PLAN_HASH_VALUE EXECUTIONS PARSE_CALLS FIRST_LOAD_TIME SQL_PLAN_BASELINE
------------- --------------- ---------- ----------- --------------------------------------------------------- ------------------------------
3ub0qzx832gmz 105935909 2 2 2018-10-29/18:45:55 SQL_PLAN_04ukwp8bfydrp4bb80211 <<~~~ executions : 2
QL> select sql_handle, plan_name, LAST_MODIFIED, LAST_EXECUTED, SQL_TEXT
from dba_sql_plan_baselines
where sql_text like '%spm_test%'
/
SQL_HANDLE PLAN_NAME LAST_MODIFIED LAST_EXECUTED
------------------------- ------------------------------ ------------------------------ ------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
SQL_026a5caa16ef36f5 SQL_PLAN_04ukwp8bfydrp4bb80211 29-OCT-18 06.45.56.000000 PM 29-OCT-18 06.45.56.000000 PM <<~~~~~~~ Same Last Executed : 29-Oct-18 06.45.56
select /* spm_test */ count(*) from t1 where object_type = 'TABLE'
Changes
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Symptoms |
| Changes |
| Cause |
| Solution |
| References |