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]

Monday Jan 16, 2012

Upcoming events : RMOUG 2012

Even though the new year has just begun my calendar is already filling up with conferences. The first conference of the year is the Rocky Mountain Oracle User Groups Training days on Febuary 14 - 16 at the Colorado Convention Center in Denver, Colorado. It a great conference with over 700
attendee and some really great speakers. I am going to be delivering three session at this years conference:

[Read More]

Monday Jan 09, 2012

Fixed Objects Statistics and why they are important

Fixed objects are the x$ tables and their indexes. The v$performance views in Oracle are defined in top of X$ tables (for example V$SQL and V$SQL_PLAN). Since V$ views can appear in SQL statements like any other user table or view then it is important to gather optimizer statistics on these tables to help the optimizer generate good execution plans. However, unlike other database tables, dynamic sampling is not automatically use for SQL statement involving X$ tables when optimizer statistics are missing. The Optimizer uses predefined default values for the statistics if they are missing. These defaults may not be representative and could potentially lead to a suboptimal execution plan, which could cause severe performance problems in your system. It is for this reason that we strong recommend you gather fixed objects statistics.[Read More]

Monday Dec 19, 2011

Why was the RULE hint ignored?

Recently I got a call from a customer that had just upgraded to Oracle
Database and was panicking because they thought the Rule Based
Optimizer (RBO) had been removed from Oracle Database 11g. The reason
they thought this was because when they tried to add the RULE hint to a
SQL statement they still got a Cost Based Optimizer (CBO) plan.

[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.


« June 2016