I was asked to compare SQL plan management auto capture with automatic SQL plan management, so here goes... SQL plan management (SPM) auto capture is enabled by...
I was asked to compare SQL plan management auto capture with automatic SQL plan management, so here goes... SQL plan management (SPM) auto capture is enabled by setting the database parameter optimizer_capture_sql_plan_baselines to TRUE (covered here). Then, by default, all plans for all SQL statements executed in the database more than once are captured. From Oracle Database 12c Release 2 you can specify filters to limit which SQL statements are chosen. This is done...
I was asked to compare SQL plan management auto capture with automatic SQL plan management, so here goes... SQL plan management (SPM) auto capture is enabled by setting the database parameter...
Always check your license guide for up-to-date information on your specific database version, but you will see that SPM is available as part of Oracle Database...
Always check your license guide for up-to-date information on your specific database version, but you will see that SPM is available as part of Oracle Database Enterprise Edition and, in addition, a subset of SPM functionality is available in Oracle Database Standard Edition from Oracle Database 18c onwards. If you have a licence to use AWR, then SQL plan management can be used in conjunction with this repository without additional licenses. SQL plan management can interact...
Always check your license guide for up-to-date information on your specific database version, but you will see that SPM is available as part of Oracle Database Enterprise Edition and, in addition, a...
You were hoping for an uneventful day at work when you notice that you have a critical workload query that is taking much longer to execute than usual, and you...
You were hoping for an uneventful day at work when you notice that you have a critical workload query that is taking much longer to execute than usual, and you can see that it is beginning to affect your service levels. You suspect that it has a sub-optimal execution plan and you need to get it sorted out immediately. Is there a quick and easy way to do that? Automatic SQL plan management can deal with this type of issue without DBA intervention, but what can you do if this...
You were hoping for an uneventful day at work when you notice that you have a critical workload query that is taking much longer to execute than usual, and you can see that it is beginning to...
A popular enhancement request I see is to provide an easier way to diagnose issues with SQL plan baselines; in particular the situation where a SQL plan...
A popular enhancement request I see is to provide an easier way to diagnose issues with SQL plan baselines; in particular the situation where a SQL plan baseline is ACCEPTED but is not being used for whatever reason. This is rare, but can happen if changes are made to the database such as dropping indexes or changing partitioning schemes. If a SQL plan baseline can't be used, you will see something like this in Oracle Database 19c: So why did it fail? In this example I...
A popular enhancement request I see is to provide an easier way to diagnose issues with SQL plan baselines; in particular the situation where a SQL plan baseline is ACCEPTED but is not being used for...
Oracle Database 18c Standard Edition includes a new way to control SQL execution plans. Stored outlines were deprecated in Oracle Database 11g Release 1 and SQL...
Oracle Database 18c Standard Edition includes a new way to control SQL execution plans. Stored outlines were deprecated in Oracle Database 11g Release 1 and SQL plan management (SPM) was only available in Oracle Database Enterprise Edition. This meant that many DBAs continued to use stored outlines in Standard Edition and they continue to work to this day. At some point in the future we will begin to remove support for stored outlines so, to avoid leaving a hole in SE, we...
Oracle Database 18c Standard Edition includes a new way to control SQL execution plans. Stored outlines were deprecated in Oracle Database 11g Release 1 and SQL plan management (SPM) was...
SQL plan management (SPM) is designed to prevent performance regression for all SQL statements used by an application (assuming that the SQL statements in...
SQL plan management (SPM) is designed to prevent performance regression for all SQL statements used by an application (assuming that the SQL statements in question are used more than once). SPM uses SQL plan baselines that are associated with individual SQL statements to control what execution plans they are permitted to use. It’s a simple but powerful idea that opens the door to the possibility of using SQL plan baselines in a more selective and reactive way: to influence...
SQL plan management (SPM) is designed to prevent performance regression for all SQL statements used by an application (assuming that the SQL statements in question are used more than once). SPM uses...
A new white paper for SQL Plan Management is now available. Just go to the Query Optimization page in OTN and click the SQL Plan Management with Oracle Database...
A new white paper for SQL Plan Management is now available. Just go to the Query Optimization page in OTN and click the SQL Plan Management with Oracle Database link. Just like the Optimizer white paper, I've kept the content and format similar to the way Maria organized it so that it will be familiar to those of you that have read previous versions. Post comments to this blog if you want to give me feedback on the white paper's content. If you want more examples, let me know...
A new white paper for SQL Plan Management is now available. Just go to the Query Optimization page in OTN and click the SQL Plan Management with Oracle Database link. Just like the Optimizer white...
Those of you that made it to the demo grounds at Oracle Open World this year (BTW - it’s still 2015 - just) will have had the chance to chat with the Oracle...
Those of you that made it to the demo grounds at Oracle Open World this year (BTW - it’s still 2015 - just) will have had the chance to chat with the Oracle developers and throw lots of difficult questions at them! For everyone in the Optimizer development team it was a great opportunity to get lots of interaction and feedback, so thanks to all of you that took the time to come along and talk to us. We’re not all lucky enough to get a trip out to San Francisco, so I’ve been...
Those of you that made it to the demo grounds at Oracle Open World this year (BTW - it’s still 2015 - just) will have had the chance to chat with the Oracle developers and throw lots of...
Happy New Year to all of our readers! As more and more of your systems migrate to Oracle Database 11g, SQL Plan Management (SPM) is becoming increasingly...
Happy New Year to all of our readers! As more and more of your systems migrate to Oracle Database 11g, SQL Plan Management (SPM) is becoming increasingly popular as a mechanism to maintain plan stability and subsequently system performance. However, one of the key frustrations folks encounter when using SPM is that the SQL plan baseline they painstakingly created is not used. So, how does Oracle decide whether or not to use an existing SQL plan baseline? SQL statements are...
Happy New Year to all of our readers! As more and more of your systems migrate to Oracle Database 11g, SQL Plan Management (SPM) is becoming increasingly popular as a mechanism to maintain...
