Oracle provides multiple ways to implement plan stability starting with Outlines(8i) followed by SQL profiles (10g). As of 11g, plan stability is implemented with SPM’s or SQL Plan Management. Off course you can use hints to implement plan stability. Some DBA’s swear by hints where as some of them are opposed to hints as they do not let the optimizer to choose most effective execution plan. Without going into the merits of one feature over the other, let me discuss about the main purpose of this blog.
If you implement outlines or SQL Profiles or SPM’s, you really want a way to ensure that optimizer is behaving as it is supposed to. Unfortunately with 22.214.171.124, there is no easy way to find if SPMs are being used or not. The LAST_EXECUTE column of DBA_SQL_PLAN_BASELINES view does not get updated always as documented. As per documentation LAST_EXECUTED column provides the timestamp when the plan baseline was last executed.
Here is a specific case scenario where time stamp of LAST_ACTIVE_TIME column from V$SQL is 11-AUG-2011 14:36:09 where as LAST_EXECUTE column of DBA_SQL_PLAN_BASELINES is 08-AUG-11 02.39.58.000000
SQL> select sql_id , SQL_PLAN_BASELINE,LAST_LOAD_TIME,LAST_ACTIVE_TIME from v$sql where sql_id =’fmg67gm9j2skh’;
SQL_ID SQL_PLAN_BASELINE LAST_LOAD_TIME LAST_ACTIVE_TIME
————- —————————— ——————- ——————–
fmg67gm9j2skh SQL_PLAN_7xaxz3w5hd4fbd6e420b9 2011-08-11/14:32:18 11-AUG-2011 14:36:09
fmg67gm9j2skh SQL_PLAN_7xaxz3w5hd4fbd6e420b9 2011-08-11/14:36:09 11-AUG-2011 14:36:09
fmg67gm9j2skh SQL_PLAN_7xaxz3w5hd4fbd6e420b9 2011-08-11/14:36:09 11-AUG-2011 15:17:40
SQL> select LAST_EXECUTED , plan_name from dba_sql_plan_baselines where plan_name=’SQL_PLAN_7xaxz3w5hd4fbd6e420b9′;
08-AUG-11 02.39.58.000000 PM SQL_PLAN_7xaxz3w5hd4fbd6e420b9
We opened an SR with Oracle. Oracle got back to us stating that we are hitting Bug 8864741 and provided some workaround to query SQLOBJ$. However even this table was NOT getting updated. So until this bug is fixed, the only fool proof way is tracing.