Oracle Scratchpad

February 17, 2008

Pushing Predicates

Filed under: CBO,Execution plans,Hints — Jonathan Lewis @ 8:32 pm BST Feb 17,2008

Some time ago I wrote a note (on my website) about the push_pred() and no_push_pred() hints. I’ve recently discovered a bug in the 9.2 optimizer that means you may find that Oracle will not use “join predicate pushdown (JPPD)” when it is obviously a good idea.

This note discusses a sequence of execution plans, taken from a system running 9.2.0.8, to demonstrate the point.

We start with a query where the optimizer pushes a predicate through an outer join into a non-mergeable view – this happens because the t2_n1 index is a very effective index and very cheap to use. [Note: the view is non-mergeable for a combination of reasons: it includes a join of its own, and it's the target of an outer join.]

We can see the non-mergeable view appearing in the VIEW operation at line three, but we can also see that the line tells us that predicate pushing took place; moreover the access predicate in line seven tells use that the t3.n1 value is the thing that started life “outside” the view, and has been pushed “inside” the view:

-------------------------------------------------------------------------------
| Id  | Operation                       |  Name       | Rows  | Bytes | Cost  |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |             |     1 |    46 |     6 |
|   1 |  NESTED LOOPS OUTER             |             |     1 |    46 |     6 |
|*  2 |   TABLE ACCESS FULL             | T3          |     1 |    19 |     4 |
|   3 |   VIEW PUSHED PREDICATE         |             |     1 |    27 |     2 |
|   4 |    TABLE ACCESS BY INDEX ROWID  | T1          |     1 |    16 |     2 |
|   5 |     NESTED LOOPS                |             |     1 |    37 |     4 |
|   6 |      TABLE ACCESS BY INDEX ROWID| T2          |     1 |    21 |     2 |
|*  7 |       INDEX RANGE SCAN          | T2_N1       |     1 |       |     1 |
|*  8 |      INDEX RANGE SCAN           | T1_I1       |     1 |       |     1 |
-------------------------------------------------------------------------------    

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("T3"."ID"=1)
   7 - access("T2"."N1"="T3"."N1")
   8 - access("T1"."ID"="T2"."ID")
       filter("T1"."ID">=NVL((),()) AND "T1"."ID"<=())    

The second plan shows the optimizer declining to use predicate pushing for the same data set when I made the t2_n1 index slightly more expensive to use (which I did by using the packaged procedure dbms_stats.set_index_stats() to increase its blevel).

------------------------------------------------------------------------------
| Id  | Operation                      |  Name       | Rows  | Bytes | Cost  |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |             |     1 |    46 |   284 |
|*  1 |  HASH JOIN OUTER               |             |     1 |    46 |   284 |
|*  2 |   TABLE ACCESS FULL            | T3          |     1 |    19 |     4 |
|   3 |   VIEW                         |             |   250 |  6750 |   279 |
|*  4 |    HASH JOIN                   |             |   250 |  9250 |   279 |
|   5 |     TABLE ACCESS BY INDEX ROWID| T1          |   250 |  4000 |    10 |
|*  6 |      INDEX RANGE SCAN          | T1_I1       |   450 |       |     2 |
|   7 |     TABLE ACCESS FULL          | T2          |   100K|  2050K|   268 |
------------------------------------------------------------------------------    

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("V1"."N1"(+)="T3"."N1")
   2 - filter("T3"."ID"=1)
   4 - access("T1"."ID"="T2"."ID")
   6 - access("T1"."ID">=NVL((),()) AND "T1"."ID"<=())    

Finally, again with the same query and the same dataset and leaving the increased blevel in place for the critical index, I make the t2 table scan that appeared in the second plan more expensive by decreasing the db_file_multiblock_read_count (I don’t have CPU costing enabled in this example, so the optimizer uses the parameter to calculate the tablescan cost, rather than the MBRC value from sys.aux_stats$ – the system statistics).

-------------------------------------------------------------------------------
| Id  | Operation                       |  Name       | Rows  | Bytes | Cost  |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |             |     1 |    46 |    15 |
|   1 |  NESTED LOOPS OUTER             |             |     1 |    46 |    15 |
|*  2 |   TABLE ACCESS FULL             | T3          |     1 |    19 |    12 |
|   3 |   VIEW PUSHED PREDICATE         |             |     1 |    27 |     3 |
|   4 |    TABLE ACCESS BY INDEX ROWID  | T1          |     1 |    16 |     2 |
|   5 |     NESTED LOOPS                |             |     1 |    37 |     6 |
|   6 |      TABLE ACCESS BY INDEX ROWID| T2          |     1 |    21 |     4 |
|*  7 |       INDEX RANGE SCAN          | T2_N1       |     1 |       |     3 |
|*  8 |      INDEX RANGE SCAN           | T1_I1       |     1 |       |     1 |
-------------------------------------------------------------------------------    

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("T3"."ID"=1)
   7 - access("T2"."N1"="T3"."N1")
   8 - access("T1"."ID"="T2"."ID")
       filter("T1"."ID">=NVL((),()) AND "T1"."ID"<=())

This third plan (which is the same as the original plan but with different costs) is the one the optimizer should have used in the second case instead of the “unpushed” plan with the tablescan on t2.  Notice that in the second case the optimizer chose a plan costing 284 over a plan costing 15 – and that shouldn’t be possible!! (In fact, with the original value for the db_file_multiblock_read_count, the correct plan for the second case would have cost only 7, because the table scan of t3 originally cost 4, not 12)

Analysis of the 10053 trace file suggests fairly conclusively that there is a bug in 9i (fixed by 10.2.0.3) in the code that deals with “join predicate pushing”.

The optimizer makes a very silly little mistake. When calculating the total cost of the plan that does not push the join predicate, it has to work out the cost of creating and using the result set from view v1 (which, internally, it labels as from$_subquery$_002).

The cost of creating the result using a hash join between t1 and t2 turns out to be 279. But the size of the result set is rather small – it is the equivalent of a table with a tablescan cost of just 2.

Having worked out the cost of generating the result set, the optimizer then has to work out the cost of joining table t3 with “table” from$_subquery_002$. At this point, I believe that for the purposes of costing the hash join the optimizer uses only the cost of scanning the result set and “forgets” the previously calculated cost of generating the result set.

Unfortunately, to generate the maximum possible confusion, the optimizer still reports the cost of generating the result set in the 10053 trace – with the effect that the trace file seems to show that the cost of the pushed nested loop join is 7, but the cost of the hash join (at 284) is repoted as being less than 7!

I can’t be 100% certain that my hypothesis is correct, and there may be other requirements that have to be met before the anomaly appears. But a few experimental adjustments of the base statistics suggest that it really is just the cost of using the from$_subquery_002 data that optimizer uses to makes its choice – and the cost of generating the result is simply forgotten.

Footnote: This code also seems to demonstrate a second bug. When I added the hint /*+ push_pred(v1) */ to the query, the second plan did not change; the optimizer ignored the hint!

I believe the push_pred hint really is supposed to mean “you will push join predicates unconditionally”, so the hint should have forced the better plan for the second case above. Just to check that I was (probably) using the hint correctly, I also ran the test adding the hint /*+ no_push_pred(v1) */ to the SQL, and the optimizer behaved as I expected – predicate pushing stopped happening in cases one and three as well.

22 Comments »

  1. We found a similar problem with pushing predicates in 10.1.0.3 that was fixed in 10.1.0.4:
    http://blog.nominet.org.uk/tech/index.php?s=push+predicate&Search.x=0&Search.y=0&Search=submit

    We managed to workaround the issue (i.e. get the plan we wanted) using _optimizer_cost_based_transformation for the sessions we needed to do the predicate pushing.

    jason.

    Comment by jason arneil — February 18, 2008 @ 8:59 am BST Feb 18,2008 | Reply

  2. Just last week I tripped over that problem as well regarding PUSH_PRED being ignored in 9.2.0.8. I didn’t know what was going on, but I tried a rewrite of the view to make it mergeable and it worked OK in my case.

    Do you know if it’s fixed in later versions?

    Comment by Jason Bucata — February 18, 2008 @ 7:18 pm BST Feb 18,2008 | Reply

  3. Jason (Bucata),

    My test case behaved properly in 10.2, but if you follow the link from Jason (Arneil) you will see that he had a problem in 10.1.0.3 – with a workaround of disabling cost based query transformation.

    Jason (A),
    Thanks for the reference. Idle curiosity makes me wonder if using the opt_param() hint in the critical statement would also work, as in:
    /*+ opt_param('_optimizer_cost_based_transformation','off') */

    Comment by Jonathan Lewis — February 18, 2008 @ 9:29 pm BST Feb 18,2008 | Reply

  4. [...] also has an item on pushing predicates. “Some time ago I wrote a note . . . about the push_pred() and [...]

    Pingback by Log Buffer #85: a Carnival of the Vanities for DBAs — February 23, 2008 @ 6:39 pm BST Feb 23,2008 | Reply

  5. Recently we encounter an odd issue with predicate pushing. We had a SQL which involves a UNION ALL inline view join with several tables. The SQL runs with Oracle Parallel Query forced at session level. It would insert 200k rows with DOP 4, whereas it inserts 0 rows with DOP 16. When we took a look, the execution plans for the queries were not the same. The “0” one, Oracle pushing the value of the join column into the UNION ALL non-mergeable view, whereas the “200k” one, predicate pushing does not happen.
    The “0” one execution plan is obviously wrong by Oracle. As the tables in the UNION ALL are composite Range-Hash Partitioned tables. With predicate pushing, we should expect something like this:

        PARTITION RANGE SINGLE             
         PARTITION HASH ITERATOR           
          TABLE ACCESS BY LOCAL INDEX ROWID
           INDEX RANGE SCAN            
    

    Instead we only see PARTITION HASH SINGLE without RANGE PARTITION access ant that’s we got 0 num_rows back.

    VIEW                                 
      UNION ALL PUSHED PREDICATE          
       FILTER                             
        PX PARTITION HASH SINGLE          
         TABLE ACCESS BY LOCAL INDEX ROWID
          INDEX SKIP SCAN                 
       FILTER                             
        PX PARTITION HASH SINGLE          
         TABLE ACCESS BY LOCAL INDEX ROWID
          INDEX RANGE SCAN                
    

    Is there any bug report related to this?

    Comment by Ying — March 10, 2008 @ 7:31 pm BST Mar 10,2008 | Reply

  6. Ying,

    It’s hard to figure out which bits of your plan may be relevant to your problem, since (a) it doesn’t look like you’ve given me the full execution plan, and (b) you haven’t used the methods described in the “How to comment” page (see link at top right) to make the plan readable. Moreover, you don’t mention an Oracle version. Finally, of course, I have as much chance as you of finding a bug report on Metalink.

    By searching the bug database, however, for product “Oracle server – enterprise edition”, with the search words “wrong results push predicate partition” I came across this bug in 9.2.0.8, reported Feb 2008:

    Bug 6791891 – Join predicate lost when pushed into union view.

    Maybe your problem is related.

    Comment by Jonathan Lewis — March 11, 2008 @ 6:10 am BST Mar 11,2008 | Reply

  7. Jonathan,

    I was wondering if you have run across any settings in 10.2.0.x (We are running 10.2.0.4) with the push_pred hint to be able to specify a specific predicate to push or an option to ignore a predicate. For example we are joining two tables and one view. The view contains 3 union all’s. The idea is to have table 1 join to the view and then the results should join another column from the view to table 2. When I use the push predicate hint it automatically shifts the view to the last step in the execution plan (the leading or ordered hints also are ignored). This doesnt seem to work with the push_subq either as the push_pred overrides it. I’ll give you a high level example below.

    select /*+ PUSH_PRED(b) */ c.customer_id
    from table_1 a, view_1 b, table_2 c
    where a.amount_due_remaining > 0
    and a.customer_id = b.customer_id
    and b.related_customer_id = c.customer_id

    so I would like to push only the predicate (a.customer_id = b.customer_id) to the view, but the optimizer is pushing both of the predicates to the view (a.customer_id = b.customer_id and b.related_customer_id = c.customer_id). My last resort option is to create a outline and then edit the private outline but I wanted to see if you came across this scenario before.

    Comment by Jason D — June 23, 2009 @ 1:10 am BST Jun 23,2009 | Reply

  8. Jonathan,

    The only way we were able to work around this was to build another subquery containing the view and the table 1 and putting the hint (PUSH_PRED) in the subquery. Our solution in this case worked, but it would be a nice feature to be able to specify a predicate to push with the hint rather than rewriting a additional subquery.

    select /*+ NO_MERGE(aa) leading(aa) */ c.customer_id
    from 
        (select /*+ PUSH_PRED(b) */ b.customer_id, b.related_customer_id
         from table_1 a, view_1 b
         where a.amount_due_remaining > 0
         and a.customer_id = b.customer_id) aa,
        table_2 c
    where aa.related_customer_id = c.customer_id
    

    Comment by Jason D — June 23, 2009 @ 5:30 pm BST Jun 23,2009 | Reply

    • Jason,
      I’m going to pass this one on to a couple of people at Oracle. I can get Oracle to do what you want in 11g by (heavy) hinting, and it works you expect in 10g with outer joins when a UNION ALL is not involved. But in your example in 10g the 10053 trace specifically reports that it can’t do join predicate pushdown (in fact quoting the wrong reason). 11g makes a similar (though more appropriate) report even when I make it do the JPPD.

      Your solution is exactly the sort of thing I would have done in this type of circumstance.

      Comment by Jonathan Lewis — June 24, 2009 @ 7:48 am BST Jun 24,2009 | Reply

    • Jason,

      I did a little more work on this over the weekend (planning to write the case up for my next book ;)) and uncovered more of the problem.

      Taking your two tables and a view (comprised of three tables in a union all) as an example, I simply added the hints:

      /*+
             index(view_1.table_A)
             index(view_1.table_B)
             index(view_1.table_C)
             index(table_2)
      */

      This did exactly what we wanted – and produced a great stack of hints as its outline.

      So I took the outline hints and played them back into the query – and got the wrong execution plan.

      One of the generated hints was a leading() hint – roughly leading(table_1 view_1 table_2), but after replaying the outline this changed to leading(table_1 table_2 view_1), so I think something is going wrong with the use of hints as Oracle is doing a recursive descent of the view definition.

      I’ve passed the test case on to Oracle.

      Comment by Jonathan Lewis — June 28, 2009 @ 8:53 pm BST Jun 28,2009 | Reply

  9. [...] Oracle doesn’t ignore hints – unless they’re illegal, out of context, or you’ve found a bug (although sometimes the hints aren’t supposed to do what you think – so even when you [...]

    Pingback by Quiz Night « Oracle Scratchpad — October 2, 2009 @ 6:17 pm BST Oct 2,2009 | Reply

  10. [...] For each row from the join recreate the view (push_pred) [...]

    Pingback by Strategic Hints « Oracle Scratchpad — May 2, 2010 @ 9:50 am BST May 2,2010 | Reply

  11. [...] Related Articles: Complex View Merging Subquery Factoring Pushing Predicates (1) Pushing Predicates (2) [...]

    Pingback by Subquery Factoring (4) « Oracle Scratchpad — September 13, 2010 @ 6:40 pm BST Sep 13,2010 | Reply

  12. I have a view that uses CONNECT BY, will PUSH_PRED work? I am not able to get it working. How do i get it working?

    e.g.

    SELECT /*+  NO_MERGE(hdr2) PUSH_PRED(hdr2) */
           *
      FROM (SELECT     /*+ QB_BLOCK(hdr1)  */
                       ROW_NUMBER () OVER (PARTITION BY transaction_id ORDER BY LEVEL)
                                                                       line_order
                  FROM (SELECT /*+  QB_BLOCK(hdr2)  */
                                        *
                          FROM txn_hdr  hdr, txn_ln LN
                         WHERE hdr.company_id = 123
                           AND hdr.company_id = LN.company_id
                           AND LN.transaction_id = hdr.transaction_id
                           -- AND ln.transaction_id  = 210 -- I want the PUSH predicate here ...
                           AND ROWNUM > 0) gtt
            START WITH target_id = start_target_id
            CONNECT BY PRIOR transaction_id = transaction_id
                   AND PRIOR next_target_id = target_id) iv
     WHERE transaction_id = 210;
    

    Comment by tushar — October 17, 2011 @ 11:00 pm BST Oct 17,2011 | Reply

    • tushar,

      I can’t answer this question from this information.
      What version of Oracle ? What are the table and index definitions.

      Two immediate problems, though;
      1) the push_pred() no_merge() hints need the an “@” symbol, viz: no_merge(@hdr2) push_pred(@hdr2)
      2) you’re trying to push a predicate into query block hdr1, and from there into query block hdr2, so it would be a bit of luck if your single push_pred() hint worked.

      If you want to post the SQL to define the tables and indexes, I am sufficiently curious that I’ll try to find a few minutes to look at the problem.

      Comment by Jonathan Lewis — November 13, 2011 @ 12:43 pm BST Nov 13,2011 | Reply

  13. I have a view in my 11.1.0.7.0 database where UNION ALL PUSHED PREDICATE kicks in beautifully – works like a dream :-)

    The view is something like:

    select key, columns... from table1
    union all
    select key, columns... from table2
    ...
    union all
    select key, max(col) over (partition by key), columns... from table8
    

    If I select from that view where key=123, the plan nicely uses UNION ALL PUSHED PREDICATE and uses index access returning about 100 rows of data in a fraction of a second. (Looks like it very intelligently knows that the predicate matches the partition by clause, so it will work even though there is analytic functions involved – I am impressed :-)

    Among other places I use the view in a query like this:

    select ......
    from tablea a
    join tableb b
       on b.key=a.key
    join view v
       on v.key=a.key
    

    Again predicate pushing works very nicely, thank you ;-)
    I’ll call that query [query1].

    I have another query with different tables, different joins and no view involved. It returns the same type of columns. Call it [query2].
    Then I had the need to do this:

    [query1]
    union all
    [query2]
    

    To my surprise, the explain plan of the first half of the union now involved massive parallel work and full table scans of 125 million row tables :-( Even though [query1] is absolutely identical to the previous case – I have only appended “union all [query2]“.

    Further testing showed that while this works OK with predicate being pushed:

    select * from (
       [query1]
    )
    

    Then this does not do predicate pushing:

    with s1 as (
       [query1]
    )
    select * from s1
    

    I am a bit stumped here :-) PUSH_PRED hints seems not to make a difference at all? Could it be different parts of the optimizer handling the different cases?

    I am trying to develop something where I take the result of [query1] union all [query2] and do some more analytics and probably pivot clause in the end as well. My workaround would be to insert into gtt [query1], then insert into gtt [query2], and then a final select doing analytics and pivot on the gtt.

    But before I start creating a gtt, I just thought I might give a shot at asking the acknowledged expert if there is something obvious that I have forgotten/not thought of? I realize you cannot do support here and I have not been able to create a simple testcase that showed the same behaviour, but maybe you have an idea of something I might try? :-)

    Thanks for you great contributions to the Oracle community :-)

    Regards
    Kim Berg Hansen

    Comment by kibeha — March 21, 2012 @ 8:45 am BST Mar 21,2012 | Reply

    • I’m not entirely surprised that the behaviour with the CTE (common table expression, or factored subquery) doesn’t behave consistently with the alternative – there are a number of cases where the route through the optimizer code is changed by subquery factoring, and these are constantly being addressed in newer patch releases and versions.

      The trouble with your problem is that in outline there isn’t enough detail, and if you gave me the detail I wouldn’t want to spend the time working through it all. Two ideas comes to mind – have you tried using the no_merge hint to isolate that bit that starts out behaving properly – this may be enough to stop Oracle flattening that part of the query. The other is that you may be doomed to failure because of simple limitations in how Oracle can treat UNION ALL views under joins. (In the original “partition view” implementation the objects in the UNION ALL had to be simple tables with exactly the same definition – we’ve come a long way since then, but a formal definition of the rules no longer exists.)

      Comment by Jonathan Lewis — March 31, 2012 @ 6:48 am BST Mar 31,2012 | Reply

      • Hi,

        Sorry about the long time without response – meant to do it and then forgot it ;-)

        As you suspected I was doomed. NO_MERGE didn’t do it, so I did it in two passes inserting to a gtt – acceptable compromise.

        Had I given you sufficient detail to work with, then I should have hired you instead ;-) As it is, I am grateful for your time and comment.

        Regards
        Kim Berg Hansen

        Comment by Kim Berg Hansen — April 30, 2012 @ 8:14 am BST Apr 30,2012 | Reply

        • Kim,
          No need to apologise for the delay, it can be very hard to find time when you’re busy. I’m sorry you didn’t manage to find a nicer workaround, but thanks for letting us know the outcome.

          Comment by Jonathan Lewis — April 30, 2012 @ 2:29 pm BST Apr 30,2012

  14. Hi Jonathan,

    I am having hard time pushing the predicates for the query that involves Remote tables(or views) and local tables(or views). Especially when the query contains outer joins and more than 1 remote table(or view). When i go through the 10053, it shows the reasons “JPPD bypassed: View not on right-side of outer-join” and “JPPD bypassed: Remote table referenced”. I don’t see any documentation on these restrictions or details. DB Version is 11.2.0.2. Below is the sample query.

    It works alright(pushes predicates) when all the tables/views in the query are local.

    select 
            /*+ push_pred(c) */ 
            a.id, a.nam, b.typ, b.disc, c.ttyp, c.mnt
    from 
            remote_tbl1@dblink a, 
            remote_tbl2@dblink b, 
            local_view c
    where 
            a.id = :B1 
    and     a.typid = c.typid(+) 
    and     a.bid = b.bid(+);
    

    Comment by josh — March 29, 2013 @ 5:42 pm BST Mar 29,2013 | Reply

    • Josh,

      It’s a smart move to have a quick look at the 10053 trace when an expected feature doesn’t appear, just in case you spot a line like this (“bypass” is a good search word).
      The line about “remote table referenced” seems a reasonably clear explanation – although slightly puzzling that that should be an automatic restriction. I’m at a loss to explain the “not on right-side”, though, as it clearly is (I’m taking “right-side” to mean “preserved” when I say this, of course, which is presumably the intent).

      It’s unfortunate that the documentation is often incomplete when it comes – and especially so when it comes to hints; it’s one of the main reasons why people have come to think that Oracle can simply ignore hints.

      Comment by Jonathan Lewis — June 9, 2013 @ 9:34 am BST Jun 9,2013 | Reply

  15. […] ones, then the outstanding questions on Oracle Core. The very first one I looked at was about pushing predicates, and the specific comment prompted me to jot down this little note about the 10053 trace file (the […]

    Pingback by 10053 | Oracle Scratchpad — June 9, 2013 @ 9:24 am BST Jun 9,2013 | 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

The Rubric Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,514 other followers