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...
html{font-size:45%;} tbody{font-family:Arial, Helvetica, sans-serif; font-size:110%;} .mycode{font-family: monospace; font-size:100%;} .ilc{font-family:...
html{font-size:45%;} tbody{font-family:Arial, Helvetica, sans-serif; font-size:110%;} .mycode{font-family: monospace; font-size:100%;} .ilc{font-family: monospace; font-size:100%;} .u14mnav{font-size:100%;} .u14logo{font-size:100%;} var h1Headers = document.getElementsByTagName("h1"); for (var i = 0; i< h1Headers.length; i++) { h1Headers[i].style.fontSize = '28px'; } Those of you that made it to the demo grounds at Oracle Open World this year (it’s still just about 2015) will...
html{font-size:45%;} tbody{font-family:Arial, Helvetica, sans-serif; font-size:110%;} .mycode{font-family: monospace; font-size:100%;} .ilc{font-family: monospace;...
html{font-size:45%;} tbody{font-family:Arial, Helvetica, sans-serif; font-size:110%;} .mycode{font-family: monospace; font-size:100%;} .ilc{font-family: monospace; font-size:100%;} .u14mnav{font-size:100%;} .u14logo{font-size:100%;} var h1Headers = document.getElementsByTagName("h1"); for (var i = 0; i< h1Headers.length; i++) { h1Headers[i].style.fontSize = '28px'; } A new white paper for SQL Plan Management is now available. Just go to the Query Optimization page in OTN and...
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...
Recently during the Open World conference a lot of people asked me about what additional licenses are need to use SQL Plan Management. No additional licenses...
Recently during the Open World conference a lot of people asked me about what additional licenses are need to use SQL Plan Management. No additional licenses are needed to use SQL Plan Management (SPM) or any of the procedures in the DBMS_SPM package. SPM is available as part of Oracle Database Enterprise Edition 11g. I believe the confusion arises from the wording in the Oracle Database Licensing documentation that says the Tuning Pack includes "Automatic Plan Evolution of...
Recently during the Open World conference a lot of people asked me about what additional licenses are need to use SQL Plan Management. No additional licenses are needed to use SQL Plan Management...
Stored outlines were deprecated in Oracle Database 11g and even though we have done a number of posts about SQL Plan Management (SPM) we have never discussed...
Stored outlines were deprecated in Oracle Database 11g and even though we have done a number of posts about SQL Plan Management (SPM) we have never discussed the reasons why you should migrate stored outlines to SQL plan baselines and the steps necessary to do the migration. It is strongly recommended that you migrate existing stored outlines to SPM because: SQL plan baselines allow multiple execution plans per SQL statement. Different plans may be optimal under different...
Stored outlines were deprecated in Oracle Database 11g and even though we have done a number of posts about SQL Plan Management (SPM) we have never discussed the reasons why you should migrate stored...
In the example in Part 2, we saw that the optimizer used an accepted plan instead of a brand new plan. The statement has two plans in its plan history, but only...
In the example in Part 2, we saw that the optimizer used an accepted plan instead of a brand new plan. The statement has two plans in its plan history, but only one is accepted and thus in the SQL plan baseline: SQL> select sql_text, plan_name, enabled, accepted from dba_sql_plan_baselines; SQL_TEXT PLAN_NAME ENA ACC ---------------------------------------- ------------------------------ --- --- select...
In the example in Part 2, we saw that the optimizer used an accepted plan instead of a brand new plan. The statement has two plans in its plan history, but only one is accepted and thus in the SQL...
