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...
Those of you that made it to the demo grounds at Oracle Open World this year (it’s still just about 2015) 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 (it’s still just about 2015) 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 (it’s still just about 2015) will have had the chance to chat with the Oracle developers and throw lots of...
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...
I'm frequently asked about the difference between SQL profiles and SQL plan baselines and why SQL profiles can be shared but SQL plan baselines can't. So I...
I'm frequently asked about the difference between SQL profiles and SQL plan baselines and why SQL profiles can be shared but SQL plan baselines can't. So I thought it would be a good idea to write a post explaining the differences between them and how they interact. But first let's briefly recap each feature. The query optimizer normally uses information like object and system statistics, compilation environment, bind values and so on to determine the best plan for a SQL...
I'm frequently asked about the difference between SQL profiles and SQL plan baselines and why SQL profiles can be shared but SQL plan baselines can't. So I thought it would be a good idea to write...
