Skip to Main Content
  • Questions

  • Same SQL_ID uses sometimes FIXED Baseline and sometimes NO

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Alessandra.

Asked: June 13, 2024 - 5:08 pm UTC

Last updated: June 19, 2024 - 12:18 pm UTC

Version: 19.21

Viewed 1000+ times

You Asked

Dear AskTom Team,
I'm getting crazy wondering why a query with a FIXED baseline runs only sometimes with plan fixed by the baseline.

SQL_HANDLE                     LAST_EXEC FIX REP CREATED
------------------------------ -------- --- --- --------
SQL_1a03d3b9c1fe160c           13.06.24 YES YES 11.01.24


The Plan that was fixed was 2507794094

MIN_SNAP_ID    MAX_SNAP_ID           SQL_ID    PLAN_HASH_VALUE    ELAPSED_SEC    EXEC_NOT_PERC    ELAPSED_NOT_PERC    ROWS_PROCESSED 
______________ ______________ ________________ __________________ ______________ ________________ ___________________ _________________ 
         84480          89078 adrtxjw1h6jkz            2507794094         0.2175              702           152709935            170063 
         84559          86193 adrtxjw1h6jkz            1004117763         10.744              531          5705062085            113276 
         86572          89081 adrtxjw1h6jkz             363704739        10.0697             1197         12053489390            426007

 DBID           SQL_ID            SNAP_ID BEGIN_INTERVAL_TIME    END_INTERVAL_TIME    PLAN_HASH_VALUE 
_____________ ________________ __________ ______________________ ____________________ __________________
   2714425756 adrtxjw1h6jkz         89041 13/06/2024 09:06       13/06/2024 09:16             2507794094 
   2714425756 adrtxjw1h6jkz         89052 13/06/2024 10:56       13/06/2024 11:06              363704739 
   2714425756 adrtxjw1h6jkz         89075 13/06/2024 14:46       13/06/2024 14:56              363704739 
   2714425756 adrtxjw1h6jkz         89075 13/06/2024 14:46       13/06/2024 14:56             2507794094 
   2714425756 adrtxjw1h6jkz         89078 13/06/2024 15:16       13/06/2024 15:26              363704739 
   2714425756 adrtxjw1h6jkz         89078 13/06/2024 15:16       13/06/2024 15:26             2507794094 
   2714425756 adrtxjw1h6jkz         89081 13/06/2024 15:46       13/06/2024 15:56              363704739


and Chris said...

Setting a baseline to FIXED doesn't guarantee the optimizer will use it. Only that it prefers a fixed plan to a non-fixed one.

Nigel Bayliss, optimizer PM, has this to say about fixed plans in his SPM cheat sheet:

I often see FIXED=YES proposed in cases where the SQL plan baseline isn’t being used because the optimizer is not using the plan encoded in the plan baseline. It is commonly thought that FIXED=YES somehow makes plan enforcement ‘stronger,’ perhaps guaranteeing or forcing a particular plan. This is not accurate. FIXED=YES tells the optimizer to choose a fixed SQL plan baseline in preference to FIXED=NO. However, the internal mechanism to enforce a particular execution plan is the same whether the plan baseline is fixed or not. If a plan will not reproduce with FIXED=YES, it will not reproduce with FIXED=NO.

https://blogs.oracle.com/optimizer/post/spm-cheat-sheet-part-1

Based on the little info provided, it's hard to say why the optimizer is not choosing the fixed plan in this case. Check whether the fixed plan can be reproduced. There are details on how to do this in Nigel's post.

If you can't find any obvious reasons the fixed plan won't reproduce, take this up with support.

Is this answer out of date? If it is, please let us know via a Comment
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.