SQL Plan Management – Known Issues in Oracle 12.1.0.2

Our Support colleagues released the patch recommendation note for SQL Plan Management (SPM) for Oracle Database 12.1.0.2.

SPM Note

In the unlikely event you’d like to upgrade to Oracle Database 11.2.0.4 or (very very unlikely hopefully) 11.2.0.3 please see these matching notes:

Some additional things to mention:

  • SPM is an Oracle Enterprise Edition feature at no extra cost
  • SPM is THE feature to ensure plan stability tackling changes such as (of course) upgrades and migrations
  • SPM has been improved a lot internally in Oracle Database 12.1.0.2:
    • We now store entire plans instead of a large accumulation of hints in the SQL Management Base (SMB) in SYSAUX tablespace
    • The “Evolvetask does happen automatically (SYS_AUTO_SPM_EVOLVE_TASK) as part of the Automatic SQL Tuning Task
  • You should always adjust the retention when starting to play with SQL Plan Management as the default retention of 53 weeks may lead to a too large LOB segment in SYSAUX tablespace (and LOB segments never shrink)
    • SQL> exec DBMS_SPM.CONFIGURE(‘plan_retention_weeks’,5);
  • See the Oracle Database 12c documentation about SPM:

–Mike

6 thoughts on “SQL Plan Management – Known Issues in Oracle 12.1.0.2

  1. Mike,

    I really appreciate the way you’re putting out warnings and advice about problems that could otherwise waste so much time for DBAs and users alike.

    Thanks.

    Jonathan Lewis

  2. Hi Mike,
    Thanks for great presentation on OOW2016.
    I have a question on SPM. We plan to use SPM for all sqls where parsing schema is app ID and DBO (schema owner). Do you think this is good approach or would be overkill? Should we use SPM for most important or troubled queries only or for all?
    We also plan to use SQL Tuning Sets to cleanse sqls and use only one from appID/dboID and then create SPM baselines from them. Do you know any nicer/easyer approach to capture only subset of sqls? If we are using optimizer_capture_sql_plan_baselines=TRUE on the system level it will capture them all.
    Thanks
    Lazar

  3. Hi Mike,
    Thanks for great presentations on OOW2016.
    I have a question about SPM. We plan to use SPM for all sqls where parsing schema is app ID and DBO (schema owner). Do you think this is good approach or would be overkill? Should we use SPM for most important or troubled queries only or for all?
    We also plan to use SQL Tuning Sets to cleanse sqls and use only one from appID/dboID and then create SPM baselines from them. Do you know any nicer/easyer approach to capture only subset of sqls? If we are using optimizer_capture_sql_plan_baselines=TRUE on the system level it will capture them all.

    Thanks
    Lazar

  4. Lazar,

    thanks for your feedback – and glad that you’ve enjoyed our OOW talk 🙂

    Actually I’m more a fan of preserving the plans by taking them from AWR or from the cursor cache – and then decide during testing which I will need to store into the Plan Baseline to protect them from changing. I usually don’t do the "all-in" capture approach is the information is written into a LOB segment in SYSAUX, and once it is big it never shrinks.

    Be aware of two things:
    – you may tweak the setup as I explained in the talk
    – when you deal with literals the feature may not work very well for you and you better may rely on SQL Profiles with the FORCE=>TRUE option.

    Cheers
    Mike

  5. Hello Lazar,

    Don’t SET the optimizer_capture_sql_plan_baselines=TRUE on the system level you might be disagreeably surprised. A couple of months ago one of my friends called me about a query which is spending an enormous time during parsing (execution plan compilation). After a couple of minutes of discussion I figured out that the local DBA has set the optimizer_capture_sql_plan_baselines=TRUE and that the query which is suffering a parse issue has 15 accepted execution plan in the SPM. I haven’t verified this situation but it might be very possible to have this high amount of execution plans when using ACS (Adaptive Cursor Sharing)

    Best regards

Leave a Reply

Your email address will not be published. Required fields are marked *