Oracle Scratchpad

June 7, 2016

Quiz Night

Filed under: 12c,Execution plans,Oracle,subqueries — Jonathan Lewis @ 7:35 pm BST Jun 7,2016

Here’s an execution plan from a recent OTN database forum posting:

 
------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT          |                    |     1 |   231 |   160   (6)| 00:00:02 |
|   1 |  UPDATE                   | GS_TABLE           |       |       |            |          |
|*  2 |   HASH JOIN SEMI          |                    |     1 |   231 |   130   (0)| 00:00:02 |
|*  3 |    TABLE ACCESS FULL      | GS_TABLE           |     5 |   895 |   123   (0)| 00:00:02 |
|*  4 |    TABLE ACCESS FULL      | UPDATEDPROGRAMCODE |   850 | 44200 |     7   (0)| 00:00:01 |
|*  5 |   VIEW                    |                    |    11 |  2024 |     8  (13)| 00:00:01 |
|*  6 |    WINDOW SORT PUSHED RANK|                    |    11 |   440 |     8  (13)| 00:00:01 |
|*  7 |     FILTER                |                    |       |       |            |          |
|*  8 |      TABLE ACCESS FULL    | UPDATEDPROGRAMCODE |    11 |   440 |     7   (0)| 00:00:01 |
|   9 |   VIEW                    |                    |   850 |  1138K|     9  (23)| 00:00:01 |
|  10 |    SORT ORDER BY          |                    |   850 |   685K|     9  (23)| 00:00:01 |
|* 11 |     VIEW                  |                    |   850 |   685K|     8  (13)| 00:00:01 |
|  12 |      WINDOW SORT          |                    |   850 | 47600 |     8  (13)| 00:00:01 |
|* 13 |       FILTER              |                    |       |       |            |          |
|* 14 |        TABLE ACCESS FULL  | UPDATEDPROGRAMCODE |   850 | 47600 |     7   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Unfortunately the originator of this plan declined to show us the query or answer any questions about where the work was going, but did confirm a speculative comment I had made that the instance was 12c. So the question is this: can you spot what it was that made me think that the plan came from 12c ?

I have to say, by the way, that there may be ways to get this plan from 11g, it was just that my first impression was that it was probably 12c and I didn’t attempt to come up with a way of getting a similar plan from 11g. (And, as far as the general shape of the plan is concerned, I can think of two different types of query that could produce it.)

Footnote

You are allowed to prove me wrong.

Answer

(Which might be me showing ignorance rather than inspiration)

The basic shape of the plan suggests to me that the query is of the form:

update gs_table 
set     col1 = (select from updatedprogramcode),
        col2 = (select from updatedprogramcode)
where   exists (select from updatedprogramcode)         -- possibly "where IN (subquery)"
;

There are a couple of variations in how the “set” subquery content might vary, and I’ll write up a short blog about that later.

Having noted this basic shape, I then noted that the subqueries involved analytic functions – as indicated by the WINDOW SORT operations; moreover one of them used a PUSHED RANK option and the other was embedded in a non-mergeable VIEW (operation 11). Updates with subqueries generally involve correlated columns – and prior to 12c there are some restrictions on how far up the tree the correlation can go. Here’s a sample query (using two tables that I’ve cloned from all_objects) to demonstrate:


update t1 set
        data_object_id = (
                select  objno
                from    (
                        select
                                object_id objno,
                                row_number() over (order by  object_id desc) rn
                        from
                                t2
                        where
                                t2.object_type = t1.object_type
                        )
                where rn = 1
        )
/

We need to embed the inner select statement in an inline view because we want to use the result of the row_number() analytic function in a filter predicate, but in Oracle 11g the reference to t1.object_id can’t correlate back to the outer t1 table, while in 12c this isn’t a problem. Here’s the 12c plan, followed by the 11g error:


12c Plan (autotrace)
----------------------------------------------------------------------------------
| Id  | Operation                 | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT          |      |  5000 | 45000 | 70012  (15)| 00:04:34 |
|   1 |  UPDATE                   | T1   |       |       |            |          |
|   2 |   TABLE ACCESS FULL       | T1   |  5000 | 45000 |    12   (0)| 00:00:01 |
|*  3 |   VIEW                    |      |     1 |    26 |    13   (8)| 00:00:01 |
|*  4 |    WINDOW SORT PUSHED RANK|      |   556 |  6116 |    13   (8)| 00:00:01 |
|*  5 |     TABLE ACCESS FULL     | T2   |   556 |  6116 |    12   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("RN"=1)
   4 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("OBJECT_ID")
              DESC )<=1)
   5 - filter("T2"."OBJECT_TYPE"=:B1)


11g Error
---------
                                t2.object_type = t1.object_type
                                                 *
ERROR at line 11:
ORA-00904: "T1"."OBJECT_TYPE": invalid identifier

Notice, by the way that my predicate “rn = 1” has resulted in the WINDOW SORT PUSHED RANK that appeared in the original plan.

In case I haven’t said it enough times: this is a just a rapid inference I drew from looking briefly at the plan and I haven’t tried hard to work out whether there is a way to get a plan like this in 11g. It was nice being proved right by the follow-up post from the OP, but my guess may have been right by accident – I’d rather be proved wrong than carry on thinking I’d got it right when I hadn’t … so feel free to supply an example in the comments.

 

6 Comments »

  1. I don’t recall ever having seen an UPDATE EXPLAIN PLAN have an “update” step listed twice…

    Comment by Jason B. — June 8, 2016 @ 4:38 am BST Jun 8,2016 | Reply

    • Jason,

      It’s a measure of how much we don’t notice when we’re not specifically looking, but when I read your response I couldn’t decide whether I would expect to see two UPDATE lines or not on earlier versions of Oracle, but here’s a quick test on 11.2.0.4:

      
      SQL> set autotrace on explain
      SQL> update t1 set v1 = trunc(v1/2) where rownum <= 20;
      
      20 rows updated.
      
      
      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 2733113716
      
      ----------------------------------------------------------------------------
      | Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
      ----------------------------------------------------------------------------
      |   0 | UPDATE STATEMENT    |      |    20 |    60 |    23   (5)| 00:00:01 |
      |   1 |  UPDATE             | T1   |       |       |            |          |
      |*  2 |   COUNT STOPKEY     |      |       |       |            |          |
      |   3 |    TABLE ACCESS FULL| T1   | 50000 |   146K|    23   (5)| 00:00:01 |
      ----------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         2 - filter(ROWNUM<=20)
      
      

      Comment by Jonathan Lewis — June 8, 2016 @ 7:39 am BST Jun 8,2016 | Reply

  2. my guess is , the full scan on step 8 is on 11 rows ….instead on 850 rows on the other full scnas

    Comment by Gadi — June 8, 2016 @ 8:25 am BST Jun 8,2016 | Reply

    • Gadi,

      Bear in mind that that’s the number of rows that the optimizer expects to produce each time it executes that operation, not the number of rows it expects to scan. The filter predicate (which we weren’t given) for operation 8 is probably different from the filter predicate used at operations 4 and 14.

      Comment by Jonathan Lewis — June 8, 2016 @ 8:43 am BST Jun 8,2016 | Reply

  3. Jonathan,

    reading through the OTN thread I think it was the point that the OP indirectly suggested that your guess about the “existence” subquery was wrong, but there is a semi join in the main query, and the “Partial Join” transformation that could turn an inner join into a semi join only appeared in 12c as far as I remember.

    Randolf

    Comment by Randolf Geist — June 8, 2016 @ 12:45 pm BST Jun 8,2016 | Reply

    • Randolf,

      That’s a subtle one – I hadn’t thought of that.

      I’d actually decided that it was a 12c plan before that comment came up, and I think I interpreted the comment as meaning the original had been written as an IN subquery that the OP was going to change to an EXISTS subquery.

      Example of 11g getting to the starting semi-join:

      
      SQL> set autotrace on explain
      SQL> update
        2          /*+
        3                  leading(t1@upd$1 t2@sel$1)
        4                  use_hash(t2@sel$1)
        5          */
        6          t1
        7  set     v1 = upper(v1)
        8  where   n2 in (select n1 from t2)
        9  /
      
      3000 rows updated.
      
      
      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 3230486770
      
      --------------------------------------------------------------------------------
      | Id  | Operation              | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
      --------------------------------------------------------------------------------
      |   0 | UPDATE STATEMENT       |       |  3000 |   553K|    15   (7)| 00:00:01 |
      |   1 |  UPDATE                | T1    |       |       |            |          |
      |*  2 |   HASH JOIN SEMI       |       |  3000 |   553K|    15   (7)| 00:00:01 |
      |   3 |    TABLE ACCESS FULL   | T1    |  3000 |   541K|    12   (0)| 00:00:01 |
      |   4 |    INDEX FAST FULL SCAN| T2_I1 |  3000 | 12000 |     2   (0)| 00:00:01 |
      --------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         2 - access("N2"="N1")
      
      

      With the data in the tables I had to put in a hint to stop the side swap and RIGHT semi-join.

      Comment by Jonathan Lewis — June 8, 2016 @ 1:13 pm BST Jun 8,2016 | 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

Blog at WordPress.com.