Wednesday Aug 03, 2011

How do I know if the cardinality estimates in a plan are accurate?

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

What the Optimizer team will be up to at Oracle Open World 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
    initialization parameters.

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

How do I migrate stored outlines to SQL Plan Management?

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

Cardinality Feedback

Cardinality feedback was introduced in Oracle Database 11gR2. The purpose of this feature is to automatically improve plans for queries that are executed repeatedly, for which the optimizer does not estimate cardinalities in the plan properly. The optimizer may misestimate cardinalities for a variety of reasons, such as missing or inaccurate statistics, or complex predicates. Whatever the reason for the misestimate, cardinality feedback may be able to help.[Read More]

Thursday May 12, 2011

How do I get an application to use the same execution plan I get in SQL*Plus?

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:


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.

[Read More]

Monday May 09, 2011

Explain the Explain Plan white paper finally published!

New Oracle Optimizer white paper, Explain the Explain Plan, published on OTN.

[Read More]

Tuesday Apr 26, 2011

Optimizer Transformations: Table Expansion

The table expansion transformation was introduced in 11gR2, to improve performance of a specific category of queries. If you understand how the transformation works, you can tailor your indexing scheme to improve plans. We added the transformation based on a few key observations:[Read More]

Wednesday Apr 20, 2011

How do I drop an existing histogram on a column and stop the Auto Stats gathering job from creating it in the future?

Before answering this question, I want to provide a bit more background to this scenario. The person who submitted this question has a table with a VARCHAR2 column called ID whose data distribution is skewed. When the Auto Stats job kicks in for this table a histogram is automatically created on this column as it is used in nearly every SQL statement and it has a data skew. However, the values in the ID column are extremely long and the first 32 characters in each ID are identical.[Read More]

Tuesday Apr 19, 2011

Upcoming events : ODTUG Kaleidoscope 2011

ODTUG Kaleidoscope 2011, June 26 - June 30 Long Beach CA is a great conference for Oracle developers and architects, offering the best content by renowned experts. I will be delivering two Optimizer sessions this year, 'Explaining the Explain plan' and 'Oracle Optimizer- Top Tips to get Optimal SQL Execution'. In the Explain the Explain plan session we will discuss each aspect of an execution plan (from selectivity to parallel execution), explain what information you should be getting from the plan, and how it affects the execution. While in the Top tips session, I will show you how to identify and resolving the most common SQL execution performance problems including, poor cardinality estimations, wrong access method being used, and much more. This session is packed with clear how-to examples making it accessible for even the most novice of Oracle Users.

ODTUG is a great conference where you can learn lots in a fun and casual atmosphere. Looking forward to seeing some of you there!

Monday Apr 11, 2011

Optimizer Transformations: OR Expansion

OR expansion is a transformation that can be used to optimize disjunctive queries (queries that contain OR clauses). The basic idea in OR expansion is to transform a query containing disjunctions into the form of a UNION ALL query of two or more branches. This is done by splitting the disjunction into its components and associating each component with a branch of a UNION ALL query.[Read More]

Monday Mar 28, 2011

What happened to the Optimizer white papers on OTN?

We have gotten a lot of questions recently about what happened to the Optimizer white papers that use to be on OTN. The white papers are still there but the URL has changed slightly, which is causing the problem. Here are new URL's for the most recent Optimizer white papers:

SQL Plan Management in Oracle Database 11g

Upgrading from Oracle Database 9i to 10g: What to expect from the Optimizer

Upgrading from Oracle Database 10g to 11g: What to expect from the Optimizer

Thursday Mar 24, 2011

How do I know what extended statistics are needed for a given workload?

In our previous post we introduced extended statistics, which help the Optimizer improve the accuracy of cardinality estimates for SQL statements that contain predicates involving a function wrapped column (e.g. UPPER(LastName)) or multiple columns from the same table used in filter predicates, join conditions, or group-by keys. So extended statistics are extremely useful but how do you know which extended statistics should be created?

In Oracle Database we introduced Auto Column Group Creation, which automatically determines which column groups are required for a table based on a given workload. Please note this functionality does not create extended statistics for function wrapped columns it is only for column groups. Auto Column Group Creation is a simple three step process:

[Read More]

Thursday Mar 17, 2011

Extended Statistics

In real-world data, there is often a relationship or correlation between the data stored in different columns of the same table. For example, in the customers table, the values in the cust_state_province column are influenced by the values in the country_id column, as the state of California is only going to be found in the United States. Until now, the Optimizer had no way of knowing about these real-world relationships and could potentially miscalculate the cardinality estimate if multiple columns from the same table are used in the where clause of a statement. With extended statistics you now have an opportunity to tell the Optimizer about these real-world relationships between the columns.[Read More]

Wednesday Mar 16, 2011

Oracle OpenWorld 2011 Call For Papers

The Oracle OpenWorld 2011 call for papers is now open. Oracle customers and partners are encouraged to submit proposals to present at this year's Oracle OpenWorld conference, which will be held October 2-6, 2011 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 Sunday, March 27 2011 at 11:59 pm PDT. We look forward to checking out your sessions on the Optimizer, SQL Plan Management, and statistics!

Friday Feb 25, 2011

Optimizer Transformations: Join Factorization

We continue our series on optimizer transformations with a post that describes the Join Factorization transformation. The Join Factorization transformation was introduced in Oracle 11g Release 2 and applies to UNION ALL queries. Union all queries are commonly used in database applications, especially in data integration applications. In many scenarios the branches in a UNION All query share a common processing, i.e, refer to the same tables. In the current Oracle execution strategy, each branch of a UNION ALL query is evaluated independently, which leads to repetitive processing, including data access and join. The join factorization transformation offers an opportunity to share the common computations across the UNION ALL branches. Currently, join factorization only factorizes common references to base tables only, i.e, not views.

Consider a simple example of query Q1.

    select t1.c1, t2.c2
    from t1, t2, t3
t1.c1 = t2.c1 and t1.c1 > 1 and t2.c2 = 2 and t2.c2 = t3.c2 
  union all
    select t1.c1, t2.c2
    from t1, t2, t4
    where t1.c1 = t2.c1 and t1.c1 > 1 and t2.c3 = t4.c3;

Table t1 appears in both the branches. As does the filter predicates on t1 (t1.c1 > 1) and the join predicates involving t1 (t1.c1 = t2.c1). Nevertheless, without any transformation, the scan (and the filtering) on t1 has to be done twice, once per branch. Such a query may benefit from join factorization which can transform Q1 into Q2 as follows:

    select t1.c1, VW_JF_1.item_2
    from t1, (select t2.c1 item_1, t2.c2 item_2
                   from t2, t3
                   where t2.c2 = t3.c2 and t2.c2 = 2                 
                 union all
                   select t2.c1 item_1, t2.c2 item_2
                   from t2, t4 
                   where t2.c3 = t4.c3) VW_JF_1
    where t1.c1 = VW_JF_1.item_1 and t1.c1 > 1;

In Q2, t1 is "factorized" and thus the table scan and the filtering on t1 is done only once (it's shared). If t1 is large, then avoiding one extra scan of t1 can lead to a huge performance improvement.

Another benefit of join factorization is that it can open up more join orders. Let's look at query Q3.

    select *
    from t5,
(select t1.c1, t2.c2
                  from t1, t2, t3
                  where t1.c1 = t2.c1 and t1.c1 > 1 and t2.c2 = 2 and t2.c2 = t3.c2 
                union all
                  select t1.c1, t2.c2
                  from t1, t2, t4
                  where t1.c1 = t2.c1 and t1.c1 > 1 and t2.c3 = t4.c3) V;

   where t5.c1 = V.c1

In Q3, view V is same as Q1. Before join factorization, t1, t2 and t3 must be joined first before they can be joined with t5. But if join factorization factorizes t1 from view V, t1 can then be joined with t5. This opens up new join orders. That being said, join factorization imposes certain join orders. For example, in Q2, t2 and t3 appear in the first branch of the UNION ALL query in view VW_JF_1. T2 must be joined with t3 before it can be joined with t1 which is outside of the VW_JF_1 view. The imposed join order may not necessarily be the best join order. For this reason, join factorization is performed under cost-based transformation framework; this means that we cost the plans with and without join factorization and choose the cheapest plan.

Note that if the branches in UNION ALL have DISTINCT clauses, join factorization is not valid. For example, Q4 is NOT semantically equivalent to Q5. 

     select distinct t1.* 
     from t1, t2
     where t1.c1 = t2.c1
  union all
     select distinct t1.*
     from t1, t2
     where t1.c1 = t2.c1

    select distinct t1.* 
    from t1, (select t2.c1 item_1 
                  from t2
                union all 
                  select t2.c1 item_1
                  from t2) VW_JF_1 
    where t1.c1 = VW_JF_1.item_1

Q4 might return more rows than Q5. Q5's results are guaranteed to be duplicate free because of the DISTINCT key word at the top level while Q4's results might contain duplicates.  

The examples given so far involve inner joins only. Join factorization is also supported in outer join, anti join and semi join. But only the right tables of outer join, anti join and semi joins can be factorized. It is not semantically correct to factorize the left table of outer join, anti join or semi join. For example, Q6 is NOT semantically equivalent to Q7.

    select t1.c1, t2.c2
    from t1, t2
    where t1.c1 = t2.c1(+) and t2.c2 (+) = 2 
 union all
    select t1.c1, t2.c2
    from t1, t2 
    where t1.c1 = t2.c1(+) and t2.c2 (+) = 3

    select t1.c1, VW_JF_1.item_2
    from t1, (select t2.c1 item_1, t2.c2 item_2

                  from t2
                  where t2.c2 = 2
                union all
                  select t2.c1 item_1, t2.c2 item_2
                  from t2                                                                                  

                  where t2.c2 = 3) VW_JF_1     
  where t1.c1 = VW_JF_1.item_1(+)                                                                 

However, the right side of an outer join can be factorized. For example, join factorization can transform Q8 to Q9 by factorizing t2, which is the right table of an outer join.

    select t1.c2, t2.c2
    from t1, t2

    where t1.c1 = t2.c1 (+) and t1.c1 = 1
 union all
    select t1.c2, t2.c2
    from t1, t2
    where t1.c1 = t2.c1(+) and t1.c1 = 2

   select VW_JF_1.item_2, t2.c2
   from t2,

           (select t1.c1 item_1, t1.c2 item_2
            from t1
            where t1.c1 = 1
           union all
            select t1.c1 item_1, t1.c2 item_2
            from t1
            where t1.c1 = 2) VW_JF_1
   where VW_JF_1.item_1 = t2.c1(+)

All of the examples in this blog show factorizing a single table from two branches. This is just for ease of illustration. Join factorization can factorize multiple tables and from more than two UNION ALL branches. 

Join factorization is a cost-based transformation. It can factorize common computations from branches in a UNION ALL query which can lead to huge performance improvement. 

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


« May 2015