Oracle Scratchpad

June 9, 2013

10053

Filed under: CBO,Oracle,Troubleshooting — Jonathan Lewis @ 9:24 am BST Jun 9,2013

I thought I’d try to spend some of today catching up on old comments – first the easier 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 CBO trace).

In “real-life” I don’t often look at 10053 trace files because they tend to be very long and messy and usually you can see all you need from an execution plan – perhaps through SQL Monitoring, or possible with the rowsource execution stats enabled. However there are questions that can sometimes be resolved very quickly by a simple text search of a trace file; questions of the form: “why isn’t the optimizer using feature X”, for example: “why didn’t the optimizer use predicate push for this view?” If you have a question of this type, then search the trace file for the word “bypass” – you may find comments like the following:

        JPPD:     JPPD bypassed: Outer query references right side of outer join
        JPPD:     JPPD bypassed: View has a single or group set function.
        JPPD:     JPPD bypassed: OLD_PUSH_PRED hint specified
        JPPD:     JPPD bypassed: User-defined operator
        JPPD:     JPPD bypassed: Push-down not enabled
        JPPD:     JPPD bypassed: View not on right-side of outer join
        JPPD:     JPPD bypassed: View contains a group by.
        JPPD:     JPPD bypassed: View contains a window function.
        JPPD:     JPPD bypassed: View contains a MODEL clause.
        JPPD:     JPPD bypassed: View contains a DISTINCT.
        JPPD:     JPPD bypassed: View contains a rownum reference.
        JPPD:     JPPD bypassed: START WITH query block.
        JPPD:     JPPD bypassed: View is a set query block.
        JPPD:     JPPD bypassed: Negative hint found
        JPPD:     JPPD bypassed: Outline does not contain hint
        JPPD:     JPPD bypassed: Base table missing statistics
        JPPD:     JPPD bypassed: Remote table
        JPPD:     JPPD bypassed: Security violation
        JPPD:     JPPD bypassed: Possible security violation.
        JPPD bypassed: Table level NO_PUSH_PRED hint.
        JPPD bypassed: Table-level hint.
        JPPD bypassed: Query block NO_PUSH_PRED hint.
        JPPD bypassed: View semijoined to table
        JPPD bypassed: Contained view has no table in fro list.
        JPPD bypassed: View contains a START WITH query block.
        JPPD bypassed: Does not contain a view.
        JPPD bypassed: Found branches of different types

According to a note that I’ve got with this list, by the way, I used “strings -a” on the Oracle executable for 10.2.0.1 to generate it. It’s probably about time I did the same for newer versions of Oracle.

Footnote:

My plans for addressing comments have been disrupted somewhat. Just as I published this note, an email holding two 10053 trace files arrived. (The author had asked before sending them, and I was sufficiently curious that I had agreed to take a quick look). So I’ve spent most of the last hour doing what I’ve just said I hardly ever to – looking at 10053 trace files.

The question was “why does this query run serially if I have a particular scalar subquery in the select list, but run parallel if I replace it with a function”.  The immediate answer, after I’d seen the query and thought about it for a bit, was: “because the manuals (10.2 – the relevant version) say that you don’t parallelize if you have scalar subqueries in the select list”; but this changed the question to: “why is it just this one scalar subquery that causes serialization when the other two scalar subqueries don’t”. Of the three scalar subqueries, only one of them cause the query to serialize.

The answer to that question is a little more subtle – and I’ll blog about it when I can find time to model the scenario.

9 Comments »

  1. In 11.2.0.4, I am seeing issue with old_push_pred where only one predicates getting pushed.In below sql we have join on view based on two columns where as only one predicate is getting pushed and the other predicate join is happening after evaluating view.Optimizer should have pushed both the columns.I can’t see this issue in 12C.Please find below the plan from 11g and 12c instance.Please let me know if we have any bug related to this or shall we log a bug.The problem in customer instance is the bad predicates getting pushed instead of the good one.It should have pushed both.

    Test case:

    with x as (select max(col1) col1,max(col2) col2 from t1)
    (select * from
    (select * from t2
    union
    select * from t3) y,
    x
    where
    x.col1=y.col1
    and x.col2=y.col2
    union all
    select  * from
    (select * from t3
    union
    select * from t4) y,
    x
    where
    x.col1=y.col1
    and x.col2=y.col2)
    
    Output of outline data in 11g:
    
    NODE    STAGE    JOIN_POS    HINT
    
    1    1    0    OLD_PUSH_PRED(@"SEL$5" "Y"@"SEL$5" ("T4"."COL2"))
    1    1    0    OLD_PUSH_PRED(@"SEL$2" "Y"@"SEL$2" ("T3"."COL1"))
    
    Plan in 11g:
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------------------------------------------------------
    --------------------------------------------------------------------------------------------------------------------------------
    Plan hash value: 3733401563
    
    -----------------------------------------------------------------------------------------------------------------
    | Id  | Operation                         | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                  |                             |     2 |    46 |     8   (0)| 00:00:01 |
    |   1 |  TEMP TABLE TRANSFORMATION        |                             |       |       |            |          |
    |   2 |   LOAD AS SELECT                  | SYS_TEMP_0FD9D6653_95955876 |       |       |            |          |
    |   3 |    SORT AGGREGATE                 |                             |     1 |     8 |            |          |
    |   4 |     TABLE ACCESS FULL             | T1                          |  1000 |  8000 |     5   (0)| 00:00:01 |
    |   5 |   UNION-ALL                       |                             |       |       |            |          |
    |   6 |    NESTED LOOPS                   |                             |     1 |    23 |     4   (0)| 00:00:01 |
    |   7 |     VIEW                          |                             |     1 |    14 |     2   (0)| 00:00:01 |
    |   8 |      TABLE ACCESS FULL            | SYS_TEMP_0FD9D6653_95955876 |     1 |     8 |     2   (0)| 00:00:01 |
    |*  9 |     VIEW                          |                             |     1 |     9 |     2   (0)| 00:00:01 |
    |  10 |      SORT UNIQUE                  |                             |       |       |            |          |
    |  11 |       UNION-ALL PARTITION         |                             |       |       |            |          |
    |  12 |        TABLE ACCESS BY INDEX ROWID| T2                          |     1 |     8 |     2   (0)| 00:00:01 |
    |* 13 |         INDEX RANGE SCAN          | IDX_T2                      |     1 |       |     1   (0)| 00:00:01 |
    |  14 |        TABLE ACCESS BY INDEX ROWID| T3                          |     1 |     8 |     2   (0)| 00:00:01 |
    |* 15 |         INDEX RANGE SCAN          | IDX_T3                      |     1 |       |     1   (0)| 00:00:01 |
    |  16 |    NESTED LOOPS                   |                             |     1 |    23 |     4   (0)| 00:00:01 |
    |  17 |     VIEW                          |                             |     1 |    14 |     2   (0)| 00:00:01 |
    |  18 |      TABLE ACCESS FULL            | SYS_TEMP_0FD9D6653_95955876 |     1 |     8 |     2   (0)| 00:00:01 |
    |* 19 |     VIEW                          |                             |     1 |     9 |     2   (0)| 00:00:01 |
    |  20 |      SORT UNIQUE                  |                             |       |       |            |          |
    |  21 |       UNION-ALL PARTITION         |                             |       |       |            |          |
    |  22 |        TABLE ACCESS BY INDEX ROWID| T3                          |     1 |     8 |     2   (0)| 00:00:01 |
    |* 23 |         INDEX RANGE SCAN          | IDX_T32                     |     1 |       |     1   (0)| 00:00:01 |
    |  24 |        TABLE ACCESS BY INDEX ROWID| T4                          |     1 |     8 |     2   (0)| 00:00:01 |
    |* 25 |         INDEX RANGE SCAN          | IDX_T4                      |     1 |       |     1   (0)| 00:00:01 |
    -----------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       9 - filter("X"."COL2"="Y"."COL2")
      13 - access("T2"."COL1"="X"."COL1")
      15 - access("T3"."COL1"="X"."COL1")
      19 - filter("X"."COL1"="Y"."COL1")
      23 - access("T3"."COL2"="X"."COL2")
      25 - access("T4"."COL2"="X"."COL2")
    
    42 rows selected.
    
    12C Plan:
    
    
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------------------------
    ------------------------------------------------------------------------------------------------------------------------------------------
    Plan hash value: 2404916025
    
    -------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                                 | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                          |                             |     2 |    56 |    16  (25)| 00:00:01 |
    |   1 |  TEMP TABLE TRANSFORMATION                |                             |       |       |         |             |
    |   2 |   LOAD AS SELECT                          | SYS_TEMP_0FD9D6CB3_9526902A |       |       |         |             |
    |   3 |    SORT AGGREGATE                         |                             |     1 |     8 |         |             |
    |   4 |     TABLE ACCESS FULL                     | T1                          |  1000 |  8000 |     4   (0)| 00:00:01 |
    |   5 |   UNION-ALL                               |                             |       |       |         |             |
    |   6 |    NESTED LOOPS                           |                             |     1 |    28 |     8  (25)| 00:00:01 |
    |   7 |     VIEW                                  |                             |     1 |    14 |     2   (0)| 00:00:01 |
    |   8 |      TABLE ACCESS FULL                    | SYS_TEMP_0FD9D6CB3_9526902A |     1 |     8 |     2   (0)| 00:00:01 |
    |   9 |     VIEW                                  |                             |     1 |    14 |     6  (34)| 00:00:01 |
    |  10 |      SORT UNIQUE                          |                             |     2 |    16 |     6  (34)| 00:00:01 |
    |  11 |       UNION ALL PUSHED PREDICATE          |                             |       |       |         |             |
    |* 12 |        TABLE ACCESS BY INDEX ROWID BATCHED| T2                          |     1 |     8 |     2   (0)| 00:00:01 |
    |* 13 |         INDEX RANGE SCAN                  | IDX_T2                      |     1 |       |     1   (0)| 00:00:01 |
    |* 14 |        TABLE ACCESS BY INDEX ROWID BATCHED| T3                          |     1 |     8 |     2   (0)| 00:00:01 |
    |* 15 |         INDEX RANGE SCAN                  | IDX_T3                      |     1 |       |     1   (0)| 00:00:01 |
    |  16 |    NESTED LOOPS                           |                             |     1 |    28 |     8  (25)| 00:00:01 |
    |  17 |     VIEW                                  |                             |     1 |    14 |     2   (0)| 00:00:01 |
    |  18 |      TABLE ACCESS FULL                    | SYS_TEMP_0FD9D6CB3_9526902A |     1 |     8 |     2   (0)| 00:00:01 |
    |  19 |     VIEW                                  |                             |     1 |    14 |     6  (34)| 00:00:01 |
    |  20 |      SORT UNIQUE                          |                             |     2 |    16 |     6  (34)| 00:00:01 |
    |  21 |       UNION ALL PUSHED PREDICATE          |                             |       |       |         |             |
    |* 22 |        TABLE ACCESS BY INDEX ROWID BATCHED| T3                          |     1 |     8 |     2   (0)| 00:00:01 |
    |* 23 |         INDEX RANGE SCAN                  | IDX_T3                      |     1 |       |     1   (0)| 00:00:01 |
    |* 24 |        TABLE ACCESS BY INDEX ROWID BATCHED| T4                          |     1 |     8 |     2   (0)| 00:00:01 |
    |* 25 |         INDEX RANGE SCAN                  | IDX_T4                      |     1 |       |     1   (0)| 00:00:01 |
    -------------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
      12 - filter("T2"."COL2"="X"."COL2")
      13 - access("T2"."COL1"="X"."COL1")
      14 - filter("T3"."COL2"="X"."COL2")
      15 - access("T3"."COL1"="X"."COL1")
      22 - filter("T3"."COL2"="X"."COL2")
      23 - access("T3"."COL1"="X"."COL1")
      24 - filter("T4"."COL1"="X"."COL1")
      25 - access("T4"."COL2"="X"."COL2")
    
    44 rows selected.
    

    Comment by Chandragupt Rai — June 1, 2016 @ 10:04 am BST Jun 1,2016 | Reply

    • Chandragupt Rai,

      This isn’t an example of anything I’ve seen before – but the thing I notice about the two plans is that 11g is pushing only the things that can be used as ACCESS predicates, while 12c is pushing access predicates and filter predicates. Perhaps this is a coded limitation in 11g. To test the hypothesis I would try modelling the data and adding multiple column indexes on t2, t3, and t4 to see if both column values where then pushed. In your circumstances I would also raise an SR with Oracle support to ask the question.

      Comment by Jonathan Lewis — June 4, 2016 @ 12:00 pm BST Jun 4,2016 | Reply

      • Thanks a lot Jonathan for the reply and looking into the issue.I follow your post very regularly and found them very informative and the best part is the way you explain is very simple and very easy to understand.
        Here I have few doubts,
        1-why m I not seeing push_pred and instead observing old_push_pred?
        2-When ever optimizer is picking old_push_pred, the hints on the view is not getting honored, I don’t have test case as of now but I can try and let you know if I am able to reproduce it.
        3-In one of your earlier post,, I had asked if its good to make push_pred parameterized. As of now its parameterized but those are version specific.When ever we give push_pred hint, optimizer will evaluate all the tables first and then push all the predicates in the view.e.g. if the sequence of execution and join order is as follow: (table)tab1->(view)V1->(table) tab2. When we give push_pred(v1) hint optimizer will evaluate tab1 and tab2 first performing MJC between tab1 and tab2 as there is no join between them and then push predicate in v1. Ideally optimizer should have evaluated tab1 first and then push the predicate in v1 and then tab2.

        Thanks
        Chandragupt Rai

        Comment by Chandragupt Rai — June 4, 2016 @ 12:27 pm BST Jun 4,2016 | Reply

        • Chandragupt Rai,

          Thanks for the compliment, but remember I didn’t write the spec or code – so when the optimizer does something I don’t expect I have to try and work out why just like everyone else.

          1) I don’t know.

          2) Not sure about this comment – it’s hard to be certain with the column names and default query block names, but it looks as if the outline hints you’ve supplied have been honoured in the plan you’ve supplied for 11g. To eliminate some of the possible sources of error, and to make interpretation easier, I would not use the same column names in all the table (e.g. use col41, col42 for tab4) and I would name every query block explcitly.

          3) Thanks for the comment on the push_pred hint (and a new comment has recently appeared on the earlier push_pred note, prompting me to think bout possible interpretations of the hints)

          Comment by Jonathan Lewis — June 14, 2016 @ 10:12 am BST Jun 14,2016

  2. I tested after creating index on both the columns but still optimizer is pushing only one predicate(I suspect the column which comes first in the table column list).

    Comment by Chandragupt Rai — June 4, 2016 @ 12:44 pm BST Jun 4,2016 | Reply

    • Chandragupt Rai,

      Thanks for the comment – it’s certainly believable that “first named column only” was a deliberate strategy – but it’s also slightly suggestive that there may be a counting error in the code (one bit of code starting at zero while another starts at one ?). I’d be tempted to try with three-column joins to see if you still get just one pushed or whether you get the first two.

      Comment by Jonathan Lewis — June 14, 2016 @ 10:15 am BST Jun 14,2016 | Reply

      • Hi Jonathan,

        I did mistake while testing after creating index on both the columns.For correction “with index on both the columns, now both the predicates are getting pushed”.

        Did another test by using three-column join as per your last suggestion
        1- creating index on all the three columns: All the predicates gets pushed
        2- creating index on 2 columns: indexed column predicates got pushed whereas unindexed column didn’t get pushed

        Comment by Chandragupt Rai — June 30, 2016 @ 12:34 pm BST Jun 30,2016 | Reply

        • instead of saying unindexed column(as we can have index on this column only) we can say the column not existing in the composite index which is getting picked by optimizer.

          Comment by Chandragupt Rai — June 30, 2016 @ 12:52 pm BST Jun 30,2016

        • did another test case
          1: with two column join and created index on (col1,col2,col3).Only col1 was pushed and not col3. Seems only access predicates are getting pushed and not filter predicates.

          with x as ( select max ( col2 ) col1 , max ( col1 ) col2 ,max ( col3 ) col3
          from t1 ) ( select /*+ push_pred(y) */ * from (
           select * from t2 
          union 
          select * from t3 ) y , 
               x 
          where x.col1=y.col1
          and x.col3=y.col3 
          union all 
          select /*+ push_pred(y) */ * from (
           select * from t3 
          union 
          select * from t4 ) y , 
               x 
          where x.col1=y.col1 
          and x.col3=y.col3  )
          

          Comment by chandraguptrai — July 1, 2016 @ 9:09 am BST Jul 1,2016


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

Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 6,669 other followers