Insights into Statistics, Query Optimization and the Oracle Optimizer
SQL Plan Management
Upgrade to Oracle Database 12c and Avoid Query Regression
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...
Friday, March 10, 2017 | SQL Plan Management | Read More
New SQL Plan Management White Paper
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 12c 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...
Monday, January 30, 2017 | SQL Plan Management | Read More
Upgrade to Oracle Database 12c and Avoid Query Regression
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...
Thursday, December 3, 2015 | SQL Plan Management | Read More
How does SQL Plan Management match SQL statements to SQL plan baselines?
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...
Wednesday, January 9, 2013 | SQL Plan Management | Read More
What is the difference between SQL Profiles and SQL Plan Baselines?
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...
Tuesday, May 8, 2012 | SQL Plan Management | Read More
Does the use of SQL Plan Management and the DBMS_SPM database package require a tuning or diagnostic pack license?
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...