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 11.2.0.2, 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′;

LAST_EXECUTED PLAN_NAME
————————————————————————— ——————————
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.

Tagged: 8864741, DBA_SQL_PLAN_BASELINES, LAST_EXECUTE, LAST_EXECUTE DBA_SQL_PLAN_BASELINES, LAST_LOAD_TIME, Outlines, Outlines SQL profiles SPM, SPM, SQL plan management, SQL profiles, SQLOBJ$