Tuesday Nov 08, 2011
Monday Oct 31, 2011
By Maria Colgan-Oracle on Oct 31, 2011
UK Oracle User Group Conference 2011, takes place in Birmingham on December 4th - 7th. This year there will be 15 different streams and over 250 sessions to choose from. I am lucky enough to be presenting two session at this years conference.
- Monday, December 5th at 12:15pm Upgrading to Oracle Database 11g without Pain
In this session I will describe the different approach you can take to use SQL Plan Management to prevent plan regressions during a database upgrade and beyond.
- Tuesday, December 6th at 12:25pm Oracle Optimizer: Best Practices for Managing Optimizer Statistics
In this session I will discuss the features introduced in Oracle Database 11g to improve the quality and efficiency of statistics-gathering as well as strategies for managing statistics in various database environments.
Monday Oct 24, 2011
Does the use of SQL Plan Management and the DBMS_SPM database package require a tuning or diagnostic pack license?
By Maria Colgan-Oracle on Oct 24, 2011
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 SQL Plan Management". What this is actually referring to is the interaction between the SQL Tune Advisor's nightly tuning task and SPM. This interaction does require you to have a license to use the Tuning pack.
In Oracle Database 11g, the SQL Tuning Advisor, runs automatically during the maintenance window. This automatic SQL tuning task targets high-load SQL statements. These statements are identified by the execution performance data collected in the Automatic Workload Repository (AWR) snapshots and not from SPM. If the SQL Tuning Advisor finds a better execution plan for a SQL statement it will recommend a SQL profile. Some of these high-load SQL statements may already have SQL plan baselines created for them. If a SQL profile recommendation is implemented, the execution plan found by the SQL Tuning Task will be added as an accepted plan to the existing SQL plan baseline.
The Tuning pack or RAT pack are also required if you want to load plans from a SQL Tuning Set (STS) into SPM. Again this is because an STS can only be created if you have licensed one of these packs. It has nothing to do with SPM.Maria Colgan+
Thursday Oct 20, 2011
By Allison on Oct 20, 2011
We recently got a comment about our paper on adaptive cursor sharing and cardinality feedback, in which the author wished for more technical papers from the optimizer team. We've actually published papers on a few different topics over the years, so we wanted to summarize and post links to them here. Unfortunately they are not all available online for free, but where possible, we've posted links to the full text.
Cost-Based Query Transformation in Oracle
Rafi Ahmed, Allison W. Lee, Andrew Witkowski, Dinesh Das, Hong Su, Mohamed Zait, Thierry Cruanes: Cost-Based Query Transformation in Oracle. VLDB 2006: 1026-1036
This paper talks about many of the query transformations in Oracle 11g which we have discussed here before, including join elimination, view merging, subquery unnesting, join predicate pushdown, join factorization, plus some others that we have not covered here before. It also describes the cost-based framework itself, some of the challenges of transforming queries in a cost-based manner, and presents performance results for some experiments on a query workload from the Oracle E-Business Suite.
Optimizer Plan Change Management (full text)
Mohamed Ziauddin, Dinesh Das, Hong Su, Yali Zhu, Khaled Yagoub: Optimizer plan change management: improved stability and performance in Oracle 11g. PVLDB 1(2): 1346-1355 (2008)
This describes SQL plan management, which we discussed on here in a four-part SPM series. The paper gives a bit more detail about the internals of SPM, plus analyzes the behavior of SPM on a large query workload from Oracle E-Business Suite.
Closing the Query Processing Loop in Oracle 11g (full text)
Allison W. Lee, Mohamed Zait: Closing the query processing loop in Oracle 11g. PVLDB 1(2): 1368-1378 (2008)
This paper discusses adaptive cursor sharing and cardinality feedback, plus the general concept of using feedback from execution to improve plan selection. We've covered both of these topics on here before, but the paper also includes an analysis of the performance impact of the two features on a query workload from Oracle E-Business Suite.
Efficient and scalable statistics gathering for large databases in Oracle 11g
Sunil Chakkappen, Thierry Cruanes, Benot Dageville, Linan Jiang, Uri Shaft, Hong Su, Mohamed Zait: Efficient and scalable statistics gathering for large databases in Oracle 11g. SIGMOD Conference 2008: 1053-1064
This paper discusses two major improvements to statistics gathering in 11g -- approximate NDV and incremental statistics gathering. We've discussed both of these topics here before, but the paper has some additional details of the algorithms, plus results of experiments on a few different data sets.
Enhanced Subquery Optimizations in Oracle, VLDB 2009 (full text)
Srikanth Bellamkonda, Rafi Ahmed, Andrew Witkowski, Angela Amor, Mohamed Zait, Chun Chieh Lin: Enhanced Subquery Optimizations in Oracle. PVLDB 2(2): 1366-1377 (2009)
This paper talks about several of the newer subquery optimizations in Oracle. We talked about basic subquery unnesting in two posts here a while ago, and alluded to some of the other subquery transformations we perform -- this paper talks about all of those. It also talks about a new form of join elimination which we introduced in 11gR2. Nearly all of the topics in this paper are NOT covered here on the blog yet, but we hope to fix that soon!
For future reference, we will be adding a new section on the right side of the page called "Optimizer Technical Papers" with links to all of these papers.
Monday Oct 17, 2011
By Maria Colgan-Oracle on Oct 17, 2011
We had a great time talking to everyone at Open World 2011 both at our Demogrounds booth and in our technical sessions. We got asked a lot of interesting questions and we plan to address them in future blog posts... so stay tuned.
The slides from our session on managing optimizer statistics are now available on Oracle Open World Content Catalog. The slides for our session on preventing suboptimal plans should be posted soon.
We are still working on a good mechanism for sharing the exercises from the hands - on lab but we will blog more about that once its in place.
If you didn't get to attend the conference, but have a question about the Optimizer you would like to see answered on the blog, you can submit it through via email.
We look forward to see you all next year at Open World 2012
Wednesday Oct 05, 2011
By Maria Colgan-Oracle on Oct 05, 2011
Its the last day of Oracle Open World and we have saved the very best for last. So hopefully you are still awake and functioning at this stage!
We present our top tip session today at 12 noon in Moscone south, room 104. The session is called Oracle Optimizer: Tips for Preventing Suboptimal Execution Plans (Session 14069). This session is a continuation from a session we did last year called Top Tips for getting optimal SQL execution every time. This year we answers questions like why an index wasn't used, why partition eliminations did not occur, why statistics were ignored, and many more. With clear how-to examples, you will learn to identify and quickly resolve these issues without the need for optimizer hints or initialization parameters.
We hope you have enjoy the conference so far and will stop by our session this afternoon.
Tuesday Oct 04, 2011
By Maria Colgan-Oracle on Oct 04, 2011
Thanks to all those who stopped by the demoground to chat with the Optimizer developers and to check out what is new in the Oracle Optimizer over the last two days. Remember today is the last day of the demoground, so if you haven't had a chance to stop by yet do so today. The Optimizer developers will be there today from 9 am until 4pm.
To day at Open World we will be giving a sessions on Optimizer statistics. The session starts at 10:15 am and is called Oracle Optimizer: Best Practices for Managing Optimizer Statistics (Session 13961) and is in Moscone South, room 103. In this session we will discuss the features introduced in Oracle Database 11g to improve the quality and efficiency of statistics-gathering as well as strategies for managing statistics in various database environments. This is your opportunity to finally understand Optimizer statistics and when and how you should gather them.
Looking forward to seeing you at our session or at the demoground!
Monday Oct 03, 2011
By Maria Colgan-Oracle on Oct 03, 2011
Hopefully you enjoyed yesterday, the first full day of technical sessions at Oracle Open World and are ready for more today! The Optimizer team are not presenting a session today but we at the Oracle Database Demo Grounds all day. Demo Grounds open at 9:45 am and run until 5:30pm. So stop by and find out what's new with the Optimizer and the statistics that feed it!
Sunday Oct 02, 2011
By Maria Colgan-Oracle on Oct 02, 2011
Oracle Open World started yesterday and San Francisco is just buzzing with Oracle folks! If you are attending the conference don't miss the opportunity to chat with the Optimizer development team at one of our technical sessions, or at the Oracle Demo grounds.
Our first technical session happens today at 12:30pm. Our hands on lab, Oracle Optimizer: Prevent Suboptimal Execution Plans (Session 29380) is scheduled at the Marriott Marquis hotel in Salon 12 / 13. In this hands on lab you will get an opportunity to solve 4 SQL tuning problems either on your own or with the help of the Optimizer development team. The problems will include scenario like, why was my index not selected, why statistics were ignored, and many more.
Hope you can join us at the hands-on lab!
Wednesday Aug 03, 2011
By Maria Colgan-Oracle on Aug 03, 2011
It is often recommended when you are tuning a SQL statement to confirm that the Optimizer's cardinality estimates, in the execution plan, are accurate. But how do you do that?
It is probably easier to answer this question with an example. Let's take the following example of a simple two table join.[Read More]
Friday Jul 22, 2011
By Maria Colgan-Oracle on Jul 22, 2011
Oracle Open World, the largest gathering of Oracle customers, partners, developers, and technology enthusiasts will happen October 2-6 in San Francisco. Of course the Optimizer development group will be there and you will have multiple opportunities to meet the team, in one of our technical sessions, or at the Oracle demogrounds.
This year the Optimizer team has 3 technical sessions;
- Monday, October 3rd at 12:30pm our hands on lab, Oracle Optimizer: Prevent Suboptimal Execution Plans
Session 29380 at the Marriott Marquis - Salon 12 / 13
In this hands on lab you will get an opportunity to solve 4 SQL tuning problems either on your own or with the help of the Optimizer development team. The problems will include scenario like, why was my index not selected, why statistics were ignored, and many more.
- Wednesday, October 5th at 10:15am Oracle Optimizer: Best Practices for Managing Optimizer Statistics
Session 13961 at Moscone South - room 103
In this session we will discuss the features introduced in Oracle Database 11g to improve the quality and efficiency of statistics-gathering as well as strategies for managing statistics in various database environments.
- Thursday, October 6th at 12 noon Oracle Optimizer: Tips for Preventing Suboptimal Execution Plans
Session 14069 at Moscone South - room 104
This session is a continuation from a session we did last year called Top Tips for getting optimal SQL execution every time. This year we answers questions like why an index wasn't used, why partition eliminations did not occur, why statistics were ignored, and many more. With clear how-to examples, you will learn to identify and quickly resolve these issues without the need for optimizer hints or
If you have some burning Optimizer or statistics related questions, you can ask them at the Optimizer demo booth in the Database area of the demogrounds. Members of the Optimizer development team will be there Monday to Wednesday from 9:45 am until 5:30pm. The full searchable OOW catalog is on-line, or you can browse the speakers by name. So start planning your trip today!
Tuesday Jul 12, 2011
By Maria Colgan-Oracle on Jul 12, 2011
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:[Read More]
Tuesday May 24, 2011
By Allison on May 24, 2011
Thursday May 12, 2011
By Maria Colgan-Oracle on May 12, 2011
Let me provide a little background to this question before answering it.
This application is a home-grown Java based app and the SQL statement in questions looks like this:
INSERT /*+APPEND*/ INTO t1
(ROW_ID, MODIFICATION_NUM, OPERATION, LAST_UPD)
SELECT ROW_ID ,1 ,'I' ,LAST_UPD
WHERE t2.LAST_UPD > :1;
The table T2 has approximately 1.4 million rows and one index IND_T2 on the LAST_UPD and ROW_ID columns. The execution plans are quite simple and involve just the IND_T2 index. The paln in SQL* Plus does an index range scan on the IND_T2 index.
Monday May 09, 2011
By Maria Colgan-Oracle on May 09, 2011
The Oracle Optimizer blog is written by members of the Optimizer development team. The goal of this blog is to provide an insight into the workings of the Optimizer and the statistics it relies on. The views expressed on this blog are our own and do not necessarily reflect the views of Oracle and its affiliates. The views and opinions expressed by visitors on this blog are theirs solely and may not reflect ours.
- How to Use SQL Plan Management
- Group-by and Aggregation Elimination
- Upgrade to Oracle Database 12c and Avoid Query Regression
- Oracle OpenWorld 2015
- Tips on SQL Plan Management and Oracle Database In-Memory – Part 3
- Tips on SQL Plan Management and Oracle Database In-Memory - Part 2
- Tips on SQL Plan Management and Oracle Database In-Memory Part 1
- What you need to know about SQL Plan Management and Auto Capture
- Space Management and Oracle Direct Path Load
- Controlling Access To The In-Memory Column Store Via Hints
Optimizer White papers
- Best Practices For Gathering Optimizer Statistics In Oracle Database 11g - Part2
- SQL Plan Management in Oracle Database 12c
- Understanding Optimizer Statistics in Oracle Database 12c
- Upgrading from 11g to 12c: What to expect from the Optimizer
- Best Practices For Gathering Optimizer Statistics In Oracle Database 12 - Part2
- SQL Plan Management in Oracle Database 11g
- Understanding Optimizer Statistics in Oracle Database 11g
- Upgrading from 10g to 11g: What to expect from the Optimizer
- Explain the Explain Plan
- Upgrading from 9i to 10g: What to expect from the Optimizer
Optimizer Development Technical Papers
- Closing the Query Processing Loop in Oracle 11g
- Cost-Based Query Transformation in Oracle
- Efficient and scalable statistics gathering for large databases in Oracle 11g
- Enhanced Subquery Optimizations in Oracle, VLDB 2009
- Optimizer plan change management: improved stability and performance in Oracle 11g
- /Cursor Sharing
- /Diagnostic tools
- /How do I
- /Oracle OpenWorld
- /Oracle Optimizer
- /SQL Plan Management
- /Upcoming events