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 "Evolve" task 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

 

Comments:

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

Posted by guest on September 16, 2015 at 09:05 AM CEST #

Thanks for your comment, Jonathan.

Highly appreciated!!!

Thanks and keep up your excellent work!

Thanks Mike

Posted by Mike on September 16, 2015 at 01:23 PM CEST #

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

Posted by Lazar Milicevic on October 04, 2016 at 06:25 PM CEST #

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

Posted by guest on October 04, 2016 at 07:54 PM CEST #

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

Posted by Mike on October 07, 2016 at 02:11 PM CEST #

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

Posted by guest on March 02, 2017 at 09:47 AM CET #

Post a Comment:
Comments are closed for this entry.
About

Mike Dietrich - Oracle Mike Dietrich
Master Product Manager - Database Upgrade & Migrations - Oracle

BLOG is moving to:

https://MikeDietrichDE.com

Search


Archives
« March 2017
SunMonTueWedThuFriSat
   
3
4
5
10
11
12
13
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
 
       
Today