Saturday Jun 09, 2012

Optimizer Bumper Sticker Competition

For the last couple of months we have been running a competition on twitter (@SQLMaria) looking for the best Optimizer bumper sticker, which we will give away at the Optimizer demo booth at this years Oracle Open World. We got tons of excellent entries and had a tough time narrowing it down to just 5 but we have. So now we need your help to pick the final winner. Click here to vote for your favorite. Then stop by the Optimizer demo booth at Oracle Open World to claim your bumper sticker!

[Read More]

Monday May 28, 2012

Upcoming events : ODTUG Kscope 12

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

How do I compare statistics?

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’.
[Read More]

Tuesday May 08, 2012

What is the difference between SQL Profiles and SQL Plan Baselines?

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

Lies, damned lies, and statistics Part 2

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?

I recently got asked this question and thought it was worth a quick blog post to explain in a little more detail what is going on with the new AUTO_SAMPLE_SIZE on Oracle Database 11g and what you should expect to see in the dictionary views. Let’s take the SH.CUSTOMERS table as an example.  There are 55,500 rows in the SH.CUSTOMERS tables.[Read More]

Monday Mar 26, 2012

Upcoming events : OBUG Connect Conference 2012

The Oracle Benelux User Group (OBUG) have given me an amazing opportunity to present a one day Optimizer workshop at their annual Connect Conference in Maastricht on April 24th. The workshop will run as one of the parallel tracks at the conference and consists of three 45 minute sessions. Each session can be attended stand alone but they will build on each other to allow someone new to the Oracle Optimizer or SQL tuning to come away from the conference with a better understanding of how the Optimizer works and what techniques they should deploy to tune their SQL. Below is a brief description of each of the sessions[Read More]

Sunday Mar 18, 2012

Incremental Statistics Maintenance – what statistics will be gathered after DML occurs on the table?

Incremental statistics maintenance was introduced in Oracle Database 11g to improve the performance of gathering statistics on large partitioned table. When incremental statistics maintenance is enabled for a partitioned table, oracle accurately generated global level  statistics by aggregating partition level statistics. As more people begin to adopt this functionality we have gotten more questions around how they expected incremental statistics to behave in a given scenario. For example, last week we got a question around what partitions should have statistics gathered on them after DML has occurred on the table?[Read More]

Monday Mar 12, 2012

Upcoming events : Oracle User Group Norway Conference

On March 22nd the  Oracle user group in Norway will take to the sea for their annual conference! This year for the first time they are starting the conference on dry land on Wednesday March 21st, followed by a two day trip from Oslo to Germany and back on a ferry. I am presenting the following three sessions at the conference.[Read More]

Friday Mar 09, 2012

Oracle OpenWorld 2012 Call For Papers

I can't believe it's time once again to start thinking about Oracle OpenWorld! The Oracle OpenWorld 2012 call for papers opens March 14th. Oracle customers and partners are encouraged to submit proposals to present at this year's Oracle OpenWorld conference, which will be held September 30 - October 4, 2012 at the Moscone Center in San Francisco. Details and submission guidelines are available on the Oracle OpenWorld Call for Papers web site. The deadline for submissions is April 9th 2012 at 11:59 pm PDT. We look forward to checking out your sessions on the Optimizer, SQL Plan Management, and statistics!

[Read More]

Friday Mar 02, 2012

Additional Information on SQL Patches

Our last post on SQL patches generated a lot of comments and questions.  Today I will address a couple of questions that are quick to answer.  For those that require more discussion, we'll post more details in the next few weeks.[Read More]

Monday Feb 27, 2012

Using SQL Patch to add hints to a packaged application

In my last post, I showed how you can use the BIND_AWARE hint to skip the monitoring phase of adaptive cursor sharing.  If you have a packaged application, you might be wondering how you can use this hint if you can't edit a query directly.  In this post I'll who you how to do just that, for any hint (or set of hints).[Read More]

Friday Feb 17, 2012

Upcoming events : Hotsos Symposium 2012

I have been lucky enough to be invited to the tenth annual Hotsos Symposium in Irving Texas on March 4-8. The Hotsos Symposium differs from other Oracle conferences because it is a dedicated Oracle performance conference and attracts some of the top performance speakers like, Tom Kyte, Cary Millsap, and Jonathan Lewis. If you get a chance you should really check it out. I have two sessions at this years symposium.[Read More]

Monday Feb 13, 2012

How do I force a query to use bind-aware cursor sharing?

Way back in our original post on adaptive cursor sharing, a commenter asked if there was a way to skip the monitoring for a query and go straight to bind-aware cursor sharing.  We mentioned then that we would add a hint for this purpose, but we never addressed the new hint here on the blog.  Starting in, the BIND_AWARE hint can be used to force bind-aware cursor sharing from the first execution.[Read More]

Wednesday Jan 25, 2012

My cursor wasn’t shared because of STB_OBJECT_MISMATCH. What does that mean?

I got a question recently about cursor sharing and why a cursor wasn’t shared due to an STB_OBJECT_MISMATCH.I thought the question and subsequent investigation might make an interesting blog post. Let me begin by outlining the question in detail.[Read More]

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.


« March 2015