Oracle Scratchpad

September 13, 2010

Subquery Factoring (4)

Filed under: CBO,Oracle,Subquery Factoring,Troubleshooting,Tuning — Jonathan Lewis @ 6:38 pm BST Sep 13,2010

I’ve written before about the effects of subquery factoring (common table expressions – or CTEs) on the optimizer, and the way that the optimizer can “lose” some strategies when you start factoring out subquery expressions. Here’s another example I came across quite recently. It involved a join of about 15 tables so I’ve only extracted a few lines from the SQL and resulting execution plans.

We start with the original query, which had factored out an aggregate subquery then used it in place of an inline view:

with max_cust_comm as (
	select
		ccm.order_id,
		max(ccm.comm_date)
	from
		customer_communications ccm
	group by
		ccm.order_id
)
select
	...
from
	...
left join
	max_cust_comm	mcc
on	mcc.order_id = ord.order_id
...

The execution path for this query included the following lines:

|   6 |     HASH JOIN OUTER            |                            |     1 |
|   7 |      NESTED LOOP               |                            |     1 |
               ...
|  41 |      VIEW                      |                            |   798K|
|  42 |       HASH GROUP BY            |                            |   798K|
|  43 |        TABLE ACCESS FULL       | CUSTOMER_COMMUNICATIONS    |   798K|

You can see that the optimizer has created a result set (VIEW) at line 41 by scanning the entire customer_communications table, for a total of about 800,000 rows, aggregating the data by order_id. This is not very efficient becauase (a) I happen to have a very useful index on the customer_communications table that contains all the data I need, and (b) there are just a few input rows where I need to find this max(comm_date).

Note, by the way, that we have an outer join into the aggregate view, so Oracle cannot use complex view merging (CVM) to avoid the need to create the result set – but it could, in principle, use join predicate pushdown (JPPD) to create a result once for each input value.

So now we change the code to copy the subquery back to the inline position:

select
	...
from
	...
left join
	(
	select
		ccm.order_id,
		max(ccm.comm_date)
	from
		customer_communications ccm
	group by
		ccm.order_id
	) mcc
on	mcc.order_id = ord.order_id
...

This, with no further intervention, is what happened to the plan.

|   6 |     NESTED LOOP OUTER                   |                    |     1 |
|   7 |      NESTED LOOP                        |                    |     1 |
               ...
|  43 |      VIEW PUSHED PREDICATE              |                    |     1 |
|  44 |       SORT AGGREGATE                    |                    |     1 |
|  45 |        FIRST ROW                        |                    |     1 |
|* 46 |         INDEX RANGE SCAN (MIN/MAX)      | CST_CMM_IX2        |     1 |

Notice that the hash join outer has changed to a nested loop outer, and the full tablescan has changed to an optimal index-only access using the ideal (for this query) index range scan (min/max). Moving the inline view into a common table expression at the head of the SQL was enough to stop Oracle from investigating this option – even though the optimizer immediately moved it back inline.

So be careful the next time you think about using subquery factoring to make your queries look cleaner and simpler – it’s a good idea, and a nice piece of technology, but sometimes it will cause your execution plans to change for the worse.

Related Articles:

Complex View Merging
Pushing Predicates (1)
Pushing Predicates (2)

Footnote: This error, and the error described in the previous note, may be related to (unpublished) bug 9850588

[Further reading on "subquery factoring"]

23 Comments »

  1. Whenever I’m handed a query to tune, I immediately remove CTE’s, hints, anything that’s not plain vanilla. See what the optimizer has for me… and with regular frequency that’s all the ‘fix’ that’s needed. Premature optimization is a pathology that affects a wide variety of programmers and universally affects those with a little knowledge and an overinflated self-assessment of that knowledge.

    Comment by Mark Brady — September 13, 2010 @ 11:53 pm BST Sep 13,2010 | Reply

    • Mike,
      Since most queries that use hints don’t use them properly, removing the hints to see what happens is a good idea; but I wouldn’t risk rewriting the CTEs until I’d worked the execution path Oracle should have taken and figured out why it didn’t take it.

      If I have to sort out problematical SQL it’s not usually the type of SQL that can safely be rewritten without doing a lot of tests to check that the query is still logically the same – so a manual rewrite comes very low on the list of priorities.

      Comment by Jonathan Lewis — September 14, 2010 @ 12:00 pm BST Sep 14,2010 | Reply

  2. I’ve found many times in the past that the optimizer can get “confused” by complex queries as this example was by the sound of things. Trial and error is usually a good way to resolve performance issues in SQL and as the previous comment from Mike said, don’t optimize prematurely.

    Comment by John Seaman — September 14, 2010 @ 4:14 am BST Sep 14,2010 | Reply

    • Trial and error? This is an not a clever way to understand what’s going wrong. More importantly, it usually takes more time to resolve an issue (it doesn’t really matter whether it’s performance related or not). In this case supplied data doesn’t have enough evidence that one or another approach to the query execution is efficient or not. To get an idea of where the time is spent, it’s needed to collect row source execution statistics, without it you are almost blind in the analysis process. That data would allow you to make a decision about how efficient Oracle was able to handle your query. After that, depending on whether cardinality estimates for the first row source in the join are correct, you can make a well-considered conclusion.

      Comment by Timur Akhmadeev — September 14, 2010 @ 7:08 am BST Sep 14,2010 | Reply

      • OK “trial and error” was a poor choice of words. I probably should have said proposing a change based on observations (facts such as statistics, actual execution plan, knowledge of application and the database) and then testing the new approach to ensure that theory matches reality. I wasn’t implying blindly trying a new approach in the hope it will work and then trying some other random change if that doesn’t work in the hope that eventually you will hit on something that does work. For all the theory though you can’t be sure of anything until you’ve tested it.

        Comment by John Seaman — September 15, 2010 @ 5:41 am BST Sep 15,2010 | Reply

    • Trial and error is definitely not the way to go.

      The more complex the query, the more important it is to understand the data and what the query is trying to do, otherwise your trial and error could easily give you an execution plan that happens to work for a while and then requires you to go through the whole process again a little while later.

      See http://jonathanlewis.wordpress.com/2010/05/26/visual-tuning/
      and http://www.simple-talk.com/sql/performance/designing-efficient-sql-a-visual-approach/

      Comment by Jonathan Lewis — September 14, 2010 @ 12:07 pm BST Sep 14,2010 | Reply

  3. Unlike Mark, the first problem I look for in a badly-performing query is a Cartesian Product! They’re still way too common.

    Of course, I only look for those in queries that others hand me to look at, not in my own! I’ve found that writing joins using ANSI syntax keeps me from writing cross-products 99.99% of the time. :-)

    Comment by StewS — September 14, 2010 @ 11:15 am BST Sep 14,2010 | Reply

    • My note on “Cartesian Merge Join” is one of the most popular notes on the blog – http://jonathanlewis.wordpress.com/2010/04/20/veiwing-figures/ but after hitting a peak in the early days of migration to 10g its popularity has dropped off as poeple realise that the optimizer can produce them through transitive closure and they aren’t necessarily a bad thing.

      ANSI is something that I’m not keen on. Although strategically it’s the sensible choice and has a couple of options that are not available to Oracle’s dialect, I find that if you need to force an execution path onto a query that’s written in ANSI it’s a lot harder to do because of the way that the optimizer transforms ANSI before optimising it.

      Comment by Jonathan Lewis — September 14, 2010 @ 12:14 pm BST Sep 14,2010 | Reply

  4. What happens to the query if you add the /*+ INLINE */ hint to the query?

    I must say that I see CTE’s as the most important addition to SQL in all the years I have been working with SQL.

    The possibility to actual structure your query so other people have a chance to consume a complex query bit-by-bit is soooo important when more than one person works on code… (The optimizer must be smart enough to do the text substitution suggested by JL)

    Sincerely,

    Lars

    Comment by Lars Villadsen — September 14, 2010 @ 11:21 am BST Sep 14,2010 | Reply

    • The CTE was already placed inline by the optimizer – so the /*+ INLINE */ hint would have had no effect.

      I agree that CTEs can be very helpful in clarifying the structure of a query – and when they are used well it’s remarkable how much easier it is to understand the intent of a SQL statement.

      Unfortunately I’ve also found cases where people use them in a totally unsuitable fashion, making their SQL much harder to read and virtually impossible to visualise. If you ever find a programmer who introduces a new (nested) CTE for every table that he adds to a query – get rid of him immediately.

      Comment by Jonathan Lewis — September 14, 2010 @ 12:20 pm BST Sep 14,2010 | Reply

  5. Hi Jonathan,

    In a procedure which returns multiple sys_refcursors, is it possible to use subfactoring once and use it for other 7/8 cursors, as all 8 queries have some common portion.

    Comment by Vaij — September 15, 2010 @ 2:16 am BST Sep 15,2010 | Reply

    • Vaij,

      No, not possible.

      Depending on the way the procedure works you could populate a global temporary table as it starts and use that in your queries; or you could define a materialized view for the common portion and start the procedure with a refresh of the MV.

      Depending on how you use the 8 sys_refcursors you could rewrite the code into a single sys_refcursor and does a union all – then you could use subquery factoring.

      If you’re opening 8 cursors, by the way,have you set your isolation so that they all open at the same SCN ? If not you could have 8 sets of results which are not consistent with each other.

      Comment by Jonathan Lewis — September 15, 2010 @ 8:30 am BST Sep 15,2010 | Reply

  6. [...] Performance nicht besser wird, möglicherweise sogar schlechter, wie in diesem Blogbeitrag (und dem nachfolgenden) beschrieben. Es empfiehlt sich also, immer Vergleichstests zu machen oder wenigstens den Explain [...]

    Pingback by Subqueries sparen mit der WITH-Clause « Oraculix — September 15, 2010 @ 4:38 pm BST Sep 15,2010 | Reply

  7. I’ve just been directed to another problem with subquery factoring that makes the irritation factor of my example pale into insignificance.

    How’s this for wrong results: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4423923392083#2674339300346087357

    You run a query with a restriction that should reduce the row count – and get back more rows than you put in !

    Comment by Jonathan Lewis — September 16, 2010 @ 9:39 am BST Sep 16,2010 | Reply

    • It’s not related to subquery factoring, because Oracle fails to produce correct results without it (10.2.0.5 & 11.2.0.1). Looks like Oracle lost join predicate somewhere on the step of subquery unnesting:

      SQL> select num from t2
        2  where 1 >= any (select num as col from t1
        3             union all
        4             select num from t1
        5             union all
        6             select num from t1
        7             union all
        8             select num from t1);
      
                       NUM
      --------------------
                         1
                         1
                         1
                         1
      
      4 rows selected.
      
      SQL> @x
      
      PLAN_TABLE_OUTPUT
      ----------------------------------------------------------------------
      ----------------------------------------------------------------------
      SQL_ID  11s2f4nmsbwum, child number 1
      -------------------------------------
      select num from t2 where 1 >= any (select num as col from t1
      union all            select num from t1            union all
      select num from t1            union all            select num from t1)
      
      Plan hash value: 2572381663
      
      ---------------------------------------------------
      | Id  | Operation             | Name     | E-Rows |
      ---------------------------------------------------
      |   0 | SELECT STATEMENT      |          |        |
      |   1 |  MERGE JOIN CARTESIAN |          |      1 |
      |   2 |   TABLE ACCESS FULL   | T2       |      1 |
      |   3 |   BUFFER SORT         |          |      4 |
      |   4 |    VIEW               | VW_NSO_1 |      4 |
      |   5 |     UNION-ALL         |          |        |
      |*  6 |      TABLE ACCESS FULL| T1       |      1 |
      |*  7 |      TABLE ACCESS FULL| T1       |      1 |
      |*  8 |      TABLE ACCESS FULL| T1       |      1 |
      |*  9 |      TABLE ACCESS FULL| T1       |      1 |
      ---------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         6 - filter("NUM"<=1)
         7 - filter("NUM"<=1)
         8 - filter("NUM"<=1)
         9 - filter("NUM"<=1)
      

      Comment by Timur Akhmadeev — September 16, 2010 @ 12:08 pm BST Sep 16,2010 | Reply

      • 11.2.0.2 doesn’t unnest subquery and the result is correct

        ----------------------------------------------------------------------------
        | Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
        ----------------------------------------------------------------------------
        |   0 | SELECT STATEMENT    |      |     1 |    13 |    15   (0)| 00:00:01 |
        |*  1 |  FILTER             |      |       |       |            |          |
        |   2 |   TABLE ACCESS FULL | T2   |     1 |    13 |     3   (0)| 00:00:01 |
        |   3 |   UNION-ALL         |      |       |       |            |          |
        |*  4 |    TABLE ACCESS FULL| T1   |     1 |    13 |     3   (0)| 00:00:01 |
        |*  5 |    TABLE ACCESS FULL| T1   |     1 |    13 |     3   (0)| 00:00:01 |
        |*  6 |    TABLE ACCESS FULL| T1   |     1 |    13 |     3   (0)| 00:00:01 |
        |*  7 |    TABLE ACCESS FULL| T1   |     1 |    13 |     3   (0)| 00:00:01 |
        ----------------------------------------------------------------------------
        
        Predicate Information (identified by operation id):
        ---------------------------------------------------
        
           1 - filter( EXISTS ( (SELECT "NUM" FROM "T1" "T1" WHERE "NUM"<=1)
                      UNION ALL  (SELECT "NUM" FROM "T1" "T1" WHERE "NUM"<=1) UNION ALL
                      (SELECT "NUM" FROM "T1" "T1" WHERE "NUM"<=1) UNION ALL  (SELECT "NUM"
                      FROM "T1" "T1" WHERE "NUM"<=1)))
           4 - filter("NUM"<=1)
           5 - filter("NUM"<=1)
           6 - filter("NUM"<=1)
           7 - filter("NUM"<=1)
        

        with explicit message in 10053 trace:

        Subquery Unnesting on query block SEL$1 (#1)SU: Performing unnesting that does not require costing.
        SU: Considering subquery unnest on query block SEL$1 (#1).
        SU:   Checking validity of unnesting subquery SET$1 (#2)
        SU:     SU bypassed: Unnest would lead to cartesian product.
        SU:   Validity checks failed.
        

        Comment by Timur Akhmadeev — September 16, 2010 @ 5:17 pm BST Sep 16,2010 | Reply

  8. Hi Jonathan,

    is the ‘,’ behind mcc in the first query just a typo?

    —————–8<—————–

    left join
    max_cust_comm mcc, — <<<<
    on mcc.order_id = ord.order_id
    —————–8<—————–

    Shouldn't you get an ORA-00905 – Missing keyword?

    Comment by Martin Berns — September 21, 2010 @ 10:10 am BST Sep 21,2010 | Reply

  9. Hello Jonathan –
    I have re-written a view using subquery factoring. Here’s a brief,

    Original view (V1)
    ————–
    select *
    from t1, (select a complex query here) X
    where
    union all
    select *
    from t2, (select same complex query ) X
    where

    subquery factoring applied to the V1 (let’s say V2)
    ———–
    with X as
    (select a complex query here)
    select *
    from t1, X
    where
    union all
    select *
    from t2, X
    where

    The report using the view V2 with a filter takes longer than the unconstrained one.
    I guess, it is because it is loading all the data from the complex query into the temp table, then goes on with the rest of the query.
    Is there a way to tell ORACLE to evaluate the filter first and then proceed to the temp table trans formation ?

    Thanks,
    Sharas

    Comment by Sharas Vital — September 29, 2010 @ 11:16 am BST Sep 29,2010 | Reply

  10. Sharas,

    You haven’t mentioned a version of Oracle, and you haven’t supplied any execution plans – although we might guess from what you’ve said that the subquery is being materialized.

    I’d have to guess, from what you have said, that the optimizer was using a simple filter pushdown (FPD) when the views were inline, but wasn’t able to do the pushdown when the subquery materialized. This sounds very like an (unpublished) bug that Karen Morten mentioned to me some time ago.

    If you want a workaround – try creating the subquery as a real ‘in-database’ view.

    Comment by Jonathan Lewis — September 29, 2010 @ 2:50 pm BST Sep 29,2010 | Reply

  11. [...] visits the product_pricing table twice might be a very good idea – because there is a special “index (min/max) range scan” optimisation that works very well if (a) have the right indexes in place (b) use a nested loop and (c) only ask [...]

    Pingback by Quiz Night « Oracle Scratchpad — November 21, 2010 @ 12:28 pm BST Nov 21,2010 | Reply

  12. [...] included a couple of “factored subqueries” – and there are a few outstanding optimizer defects with handling subquery factoring – so when he claimed that this was an example of Oracle [...]

    Pingback by ANSI – argh « Oracle Scratchpad — December 3, 2010 @ 7:32 pm BST Dec 3,2010 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,173 other followers