Monday Sep 17, 2012
Wednesday Sep 05, 2012
By Maria Colgan-Oracle on Sep 05, 2012
Oracle OpenWorld is the largest gathering of Oracle customers, partners, developers, and technology enthusiasts. This year it will take place between September 30th and October 4th 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 Database demogrounds.[Read More]
Wednesday Aug 29, 2012
By Maria Colgan-Oracle on Aug 29, 2012
I got an interesting question from one of my colleagues in the performance team last week about how to restrict a concurrent statistics gather to a small subset of tables from one schema, rather than the entire schema. I thought I would share the solution we came up with because it was rather elegant, and took advantage of concurrent statistics gathering, incremental statistics, and the not so well known “obj_filter_list” parameter in DBMS_STATS.GATHER_SCHEMA_STATS procedure.[Read More]
Friday Jul 13, 2012
By Maria Colgan-Oracle on Jul 13, 2012
The Indiana Oracle User Group (INOUG) have been kind enough to ask me to present an all day Optimizer workshop at their annual training day on July 26th at Eli Lilly & Co. The workshop will consist of four 90 minute session and will explain the fundamentals of the cost based Optimizer and the statistics that feed it, as well as providing a methodology for diagnosing and resolving SQL execution performance problems.
The workshop begins with an in-depth look at statistics and statistics maintenance, a challenge all DBAs must face in order to prevent suboptimal execution plans. It continues with a detailed explanation on how to interpret an execution plan. Each aspect of an execution plan will be examined, from cardinality estimates to parallel execution, and you will learn what information you should be gleam from the plan and how it affects the execution performance of a SQL statement. After lunch the workshop focuses on the process of analyzing and resolving the most common SQL execution performance problems including poor cardinality estimations, bind peeking issues, selecting the wrong access method and much, much more.
You can register for the event here. I hope you can join us!
Thursday Jul 05, 2012
By Maria Colgan-Oracle on Jul 05, 2012
At the recent ODTUG Kscope 12 conference several people told me that they really enjoyed our blog on the Optimizer but were frustrated because they couldn’t cut and paste the commands used in the blog posts straight into their environment.
Typically I use screen shots in the blog posts to make the commands clear but it does mean that it is impossible to cut and paste the commands into your environment. In order to get around this I have created a downloadable .sql script for each of our blog posts. You should now see the sentence “You can get a copy of the script I used to generate this post here”, appearing at the bottom of each blog post. Clicking on the link will open the .sql script that contains all of the commands used in the post. You can either save the entire script or just cut and paste the particular command you are interested in!
I have added scripts for all of this year’s blog posts and am slowly making my way through our old posts until we have a script for everything we have posted to date.
Hopefully this will help!Read More]
Monday Jul 02, 2012
By Maria Colgan-Oracle on Jul 02, 2012
Thank you all for voting for your favorite Optimizer bumper sticker slogans. We are proud to announce we have a winner! With over 40% of the votes, "Proud parent of a child cursor" will be the official Optimizer bumper sticker at this year's Oracle Open World!
Don't forget you will be able to pickup your Optimizer bumper sticker at the Optimizer demo booth in the Oracle demo grounds!Looking forward to seeing you there!
Monday Jun 25, 2012
By Maria Colgan-Oracle on Jun 25, 2012
There is still time to vote in our competition to find the best Optimizer bumper sticker, which we will give away at the Optimizer demo booth at this years Oracle Open World.
Remember voting will close on June 30th and the winning slogan will be announced in early July.
Saturday Jun 09, 2012
By Maria Colgan-Oracle on Jun 09, 2012
For the last couple of months we have been running a competition on twitter (
Monday May 28, 2012
By Maria Colgan-Oracle on May 28, 2012
Kscope 12, is the annual conference of the Oracle Development Tools User Group, which is taking place June 24 - June 28, in San Antonio Texas this year. This is a great conference for Oracle developers and architects, offering the best content by renowned experts. I am luck enough to be involved in five sessions this year around the Oracle Optimizer and performance. Below are details on the session I will be presenting or co-presenting on. I hope you have an opportunity to check out some of these sessions if you plan to attend the conference![Read More]
Friday May 18, 2012
By Maria Colgan-Oracle on May 18, 2012
This question came up recently when I was helping a customer migrate a large data warehouse to Oracle Database 11g. Prior to the upgrade, they were using an ESTIMATE_PERCENT of 0.000001, the smallest possible sample size allowed, when they gathering statistics on their larger tables. When I asked why they picked such a tiny sample size they said it was because they needed statistics to be gathered extremely quickly after their daily load both at the partition and at the global level.
Since these large tables were partitioned, I thought they would be an excellent candidate for incremental statistics. However, in order to use incremental statistics gathering you have to let the ESTIMATE_PERCENT parameter default to AUTO_SAMPLE_SIZE. Although the customer saw the benefit of using incremental statistics they were not keen on changing the value of ESTIMATE_PERCENT. So I argued that with the new AUTO_SAMPLE_SIZE in Oracle Database 11g they would get statistics that were equivalent to a 100% sample but with the speed of a 10% sample. And since we would be using incremental statistics we would only have to gather statistics on the freshly loaded partition and the global statistics (table level statistics) would be automatically aggregated correctly.
So with much skepticism, they tried incremental statistics in their test system and they were pleasantly surprised at the elapse time. However, they didn’t trust the statistics that were gathered and asked me, “how do I compare the statistics I got with AUTO_SAMPLE_SIZE to the statistics I normally get with an ESTIMATE_PERCENT of 0.000001?”The answer to that was easy, ‘use DBMS_STAT.DIFF_TABLE_STATS’.
Tuesday May 08, 2012
By Maria Colgan-Oracle on May 08, 2012
Since Oracle Database 11g was released I have gotten a lot of questions 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 statement. In some cases, defects in either these inputs or the optimizer can lead to a sub-optimal plan. A SQL profile contains auxiliary information that mitigates this problem. When used together with its regular inputs, a SQL profile helps the optimizer minimize mistakes and thus more likely to select the best plan.
A SQL plan baseline for a SQL statement consists of a set of accepted plans. When the statement is parsed, the optimizer will only select the best plan from among this set. If a different plan is found using the normal cost-based selection process, the optimizer will add it to the plan history but this plan will not be used until it is verified to perform better than the existing accepted plan and is evolved. We described this behavior in more detail in a previous post.[Read More]
Sunday Apr 08, 2012
By Maria Colgan-Oracle on Apr 08, 2012
There was huge interest in our OOW session on Managing Optimizer Statistics. It seems statistics and the maintenance of them continues to baffle people. In order to help dispel the mysteries surround statistics management we have created a two part white paper series on Optimizer statistics.
Part one of this series was released in November last years and describes in detail, with worked examples, the different concepts of Optimizer statistics. Today we have published part two of the series, which focuses on the best practices for gathering statistics, and examines specific use cases including, the fears that surround histograms and statistics management of volatile tables like Global Temporary Tables.[Read More]
Monday Apr 02, 2012
I thought the new AUTO_SAMPLE_SIZE in Oracle Database 11g looked at all the rows in a table so why do I see a very small sample size on some tables?
By Maria Colgan-Oracle on Apr 02, 2012
Monday Mar 26, 2012
By Maria Colgan-Oracle on Mar 26, 2012
Sunday Mar 18, 2012
Incremental Statistics Maintenance – what statistics will be gathered after DML occurs on the table?
By Maria Colgan-Oracle on Mar 18, 2012
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.
- 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
- The Optimizer & the new Oracle Database In-Memory option
- Oracle Open World 2013 - It's a wrap
- Day 4 of Oracle OpenWorld 2013 September 25th
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