Oracle Scratchpad

November 19, 2012

Plan timing

Filed under: Execution plans,Oracle,subqueries — Jonathan Lewis @ 9:59 pm BST Nov 19,2012

I’ve copied the following question up from a recent comment because it’s a useful generic type of question, and I think I can answer it in the few minutes I have to spare.

Hi Jonathan. I have a query plan where I cannot explain how time adds up. I did the ALTER SESSION trick but it changed nothing. I ran this, and got the following plan. I have two questions (I appoligize for not being able to format this code and plan but I saw not formatting buttons on the insert box).

1) @ step #8 in the plan, the query jumps to 3 and 1/2 minutes. This step says VIEW but gives no indication of what it did that actually took 3 and 1/2 minutes. Can you explain or give me some idea how to find out what is being done on this line that takes that long. Especially with so few rows.

2) the total elapsed time of the query was 3 minutes 45 seconds. This jives with step #1 that says 3:44.54. But if A-TIME is supposed to be among other things, the sum of child steps, then how to we explain step #2. Step #2 says 3 minutes 3 minutes 44 seconds. But the sum of its child steps (#3 and #8) is7 minutes and 25 seconds. Did this query do these steps “in parallel” so to speak? Or is there something really off in the numbers and if so how to I prove it?

This is not something that is super pressing but I sure would like to be able to explain the differences to people. It is making it somewhat harder to get people to believe I know what I am doing when I cannot such obvious issues as these two things.

Thanks, Kevin Meade

Fortunately Kevin included enough information to allow me to understand what was going on. In this case that means: “Here’s the query and the execution plan pulled from memory – showing the execution statistics”. As follows:

ALTER SESSION SET STATISTICS_LEVEL=ALL
/

drop table kevtemp1
/

create table kevtemp1
nologging
as
SELECT SRCE_EFF_START_TMSP,
       AGGREGATECLAIMUID
FROM (
     SELECT  A.AGGREGATECLAIMUID,
             A.SNAPSHOT_DT,
             MAX(A.SRCE_EFF_START_TMSP) SRCE_EFF_START_TMSP
     FROM (SELECT PI.AGGREGATECLAIMUID,
                  PI.SRCE_EFF_START_TMSP,
                  (SELECT DISTINCT BW.SNAPSHOT_DT
                   FROM RRS_SHR.RRS_ETL_BATCH_WINDOW BW, RRS_SHR.RRS_LOAD_STATUS RLS
                   WHERE RLS.ASTG_LOAD_STATUS = 'STARTED'
                   AND RLS.SNAPSHOT_DT = BW.SNAPSHOT_DT
                   AND BW.RPTG_WINDOW_TYPE = 'D'
                   AND BW.OBJECT_NM = 'R_AGGREGATE_CLAIM_SEED'
                   AND PI.SRCE_EFF_START_TMSP
                       BETWEEN
                       BW.BEGIN_TMSP
                       AND
                       BW.END_TMSP
                  ) AS SNAPSHOT_DT
           FROM RRS_PSTG.P_PCD_AGGREGATECLAIM PI
           WHERE PI.BATCH_ID IN
                 (SELECT src_batch_id
                  FROM batch_processing_queue
                  WHERE dm_batch_id =
                        batch_id_pkg.get_current_batch_id('PCD','ATM')
                 )
          ) A
          GROUP BY A.AGGREGATECLAIMUID, A.SNAPSHOT_DT
     )
/

Table created.

Elapsed: 00:03:45.14

Plan hash value: 3515505567

----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | CREATE TABLE STATEMENT          |                             |      1 |        |      0 |00:03:44.54 |     246K|   8389 |    128 |       |       |          |
|   1 |  LOAD AS SELECT                 |                             |      1 |        |      0 |00:03:44.54 |     246K|   8389 |    128 |   530K|   530K|  530K (0)|
|   2 |   HASH GROUP BY                 |                             |      1 |  84487 |  48482 |00:03:44.41 |     245K|   8386 |      0 |  3128K|  1284K| 3538K (0)|
|   3 |    HASH UNIQUE                  |                             |  33524 |      1 |  33524 |00:03:40.98 |     236K|     12 |      0 |  1270K|  1270K|  523K (0)|
|   4 |     NESTED LOOPS                |                             |  33524 |      1 |  33524 |00:00:08.35 |     236K|     12 |      0 |       |       |          |
|*  5 |      TABLE ACCESS BY INDEX ROWID| RRS_ETL_BATCH_WINDOW        |  33524 |     11 |  33524 |00:00:07.84 |     203K|      9 |      0 |       |       |          |
|*  6 |       INDEX RANGE SCAN          | IDX1_RRS_ETL_BATCH_WINDOW   |  33524 |     41 |   5414K|00:00:04.09 |   86482 |      5 |      0 |       |       |          |
|*  7 |      INDEX UNIQUE SCAN          | IUK2_RRS_LOAD_STATUS        |  33524 |      1 |  33524 |00:00:00.31 |   33535 |      3 |      0 |       |       |          |
|   8 |    VIEW                         |                             |      1 |  84487 |  48484 |00:03:43.99 |     245K|   8386 |      0 |       |       |          |
|*  9 |     HASH JOIN                   |                             |      1 |  84487 |  48484 |00:00:02.63 |    9186 |   8374 |      0 |  1023K|  1023K| 1243K (0)|
|* 10 |      INDEX RANGE SCAN           | IAK1_BATCH_PROCESSING_QUEUE |      1 |      5 |    102 |00:00:00.22 |     855 |     44 |      0 |       |       |          |
|  11 |      TABLE ACCESS FULL          | P_PCD_AGGREGATECLAIM        |      1 |   1536K|   1536K|00:00:01.47 |    8331 |   8330 |      0 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - filter(("BW"."BEGIN_TMSP"=:B1)
   7 - access("RLS"."SNAPSHOT_DT"="BW"."SNAPSHOT_DT" AND "RLS"."ASTG_LOAD_STATUS"='STARTED')
   9 - access("PI"."BATCH_ID"="SRC_BATCH_ID")
  10 - access("DM_BATCH_ID"="BATCH_ID_PKG"."GET_CURRENT_BATCH_ID"('PCD','ATM'))

46 rows selected.

Elapsed: 00:00:01.75

Working through the layers of the query, from the outside towards the middle, we have a simple select from an inline view. The inline view, however, is an aggregate view that selects from another inline view. One of the “columns” of the innermost inline view is an scalar subquery that joins two tables, and the view also uses an IN subquery. So we need to identify all these pieces in the execution plan.

Lines 9 – 11 of the plan show us that the IN subquery of the innermost view has been unnested, producing a hash join. The VIEW operator tells us that this join has been optimized as a “leaf” query block. Lines 3 – 7 give us the separately optimized execution plan for the “select distinct” inline scalar subquery; I am slightly surprised that this part of the plan doesn’t appear after the VIEW operator at line 8, indented at the same level as line 9, since it is a plan that generates a value for each row returned in the view.

Note: although the plan in lines 3 – 7 could be executed once for each row returned by the plan in lines 9 – 11, the number of executions could (and in this case is) fewer than the worst case thanks to scalar subquery caching.

The location of the scalar subquery in the overall plan does make it harder to understand the various time and resource usage figures – but once you understand what the query has to do, it is easier to work out why the numbers have a dislocated look. The time in line 8 is 3:43.99 because it includes the time in line 9 (2.63 seconds) that generates 48,484 rows, plus the time in line 3 (3:40.98) that comes from running the scalar subquery 33,524 times.

Summary

Subqueries can appear in various places in execution plans – when they are scalar subqueries representing columns in the select list, they appear ABOVE the query block that calls them. This can be very confusing when you’re trying to follow the trail of where the time and resources are first used when you’ve collected rowsource execution statistics.

10 Comments »

  1. Thank you Sir. I guess I was being lazy. I should have seen exactly what you have described. Once I remove the scalar subquery (replace with w/sysdate for example), the query takes about 1 second instead of 4 minutes. It is obvious where the time is going.

    Thank you also for clarifying the deficiency in the query plan. I surmized there was something it was not showing me, but I was resisting the idea that the sequence of events was in a cart before the horse order. Getting your description of this gives me the material I need to move forward and to help others around me who need to know. I thank you for that. If I get a moment, I will update with success notes once I achieve success in getting the timings I want. I think this query should run in about 4 seconds or less based on what I know of the rowcounts involved. I just have to get the right query structure and plan for it etc.

    Kevin

    Comment by Kevin Meade — November 20, 2012 @ 2:32 am BST Nov 20,2012 | Reply

  2. Kevin,
    can you give the details of the index IDX1_RRS_ETL_BATCH_WINDOW

    Just wondering about the filter predicate on line 5
    5 – filter((“BW”.”BEGIN_TMSP”=:B1)

    regards
    srivenu

    Comment by srivenu kadiyala — November 21, 2012 @ 6:25 am BST Nov 21,2012 | Reply

    • Sure, In the end we found one possible solution. This particular query was the perverbial (it ran in a few minutes on TEST, but never finishes in PROD) query. As I write this, there is someone trying to finish a run of this query in PROD and it has been going for 12 hours. In any event, there are two things:

      I offered up this index and this query rewrite which gives this plan. My testing during the day saw this complete in 15 minutes. These guys ran it @8pm last night and it finished for them in 32 seconds. I can’t for the life of me figure out how they got 32 seconds when the best I could do was 15 minutes so I asked for some clarification on how they determined their answer set was correct and if they did anything else. For now though:

      I believe the solution comes from the scalar subquery initially being woofully inefficient since Oracle does not have the metadata to know that this subquery will always return one and only one row. I confirmed this with the app team and data analysts since there are (shame) not constraints on these tables. After using the ROWNUM tricks, Oracle stops looking down the index once he finds the hit he wants because by the nature of the data their will always be one row and only one row that batches the date bracketing, for each key. I always supected the scalar was the issues because removing it from the query yielded a 2 second query every time. So doing something to “FIX” how the scalar subquery was being processed seemed like the right approach.

      BY THE WAY, anyone know how to format in this forum. I like this place and figure on contributing but I feel bad about posted hard to read stuff.

      Notice the use of ROWNUM in two places. Yes a hack but it gives me what I want without (given the nature of the data) changing the semantics of the query.

      09:46:27 SQL> create index kevi1 on RRS_SHR.RRS_ETL_BATCH_WINDOW (RPTG_WINDOW_TYPE,OBJECT_NM,BEGIN_TMSP,END_TMSP,SNAPSHOT_DT)
      10:18:32   2  nologging
      10:18:32   3  /
      
      Index created.
      
      Elapsed: 00:00:03.50
      
      10:18:36 SQL> create table kevtemp5
      10:19:38   2  nologging
      10:19:38   3  as
      10:19:38   4  SELECT SRCE_EFF_START_TMSP, 
      10:19:38   5         AGGREGATECLAIMUID 
      10:19:38   6  FROM (
      10:19:38   7       SELECT  A.AGGREGATECLAIMUID, 
      10:19:38   8               A.SNAPSHOT_DT, 
      10:19:38   9               MAX(A.SRCE_EFF_START_TMSP) SRCE_EFF_START_TMSP
      10:19:38  10       FROM (SELECT PI.AGGREGATECLAIMUID, 
      10:19:38  11                    PI.SRCE_EFF_START_TMSP, 
      10:19:38  12                    (SELECT BW.SNAPSHOT_DT
      10:19:38  13                     FROM RRS_SHR.RRS_ETL_BATCH_WINDOW BW, RRS_SHR.RRS_LOAD_STATUS RLS
      10:19:38  14                     WHERE RLS.ASTG_LOAD_STATUS = 'STARTED'   
      10:19:38  15                     AND RLS.SNAPSHOT_DT = BW.SNAPSHOT_DT
      10:19:38  16                     AND BW.RPTG_WINDOW_TYPE = 'D'    
      10:19:38  17                     AND BW.OBJECT_NM = 'R_AGGREGATE_CLAIM_SEED'    
      10:19:38  18                     AND PI.SRCE_EFF_START_TMSP BETWEEN BW.BEGIN_TMSP AND BW.END_TMSP
      10:19:38  19                     and rownum = 1
      10:19:38  20                    ) AS SNAPSHOT_DT
      10:19:38  21             FROM RRS_PSTG.P_PCD_AGGREGATECLAIM PI
      10:19:38  22             WHERE PI.BATCH_ID IN 
      10:19:38  23                   (SELECT src_batch_id
      10:19:38  24                    FROM batch_processing_queue
      10:19:38  25                    WHERE dm_batch_id = batch_id_pkg.get_current_batch_id('PCD','ATM')
      10:19:38  26                   )
      10:19:38  27             and rownum >= 1
      10:19:38  28            ) A
      10:19:38  29            GROUP BY A.AGGREGATECLAIMUID, A.SNAPSHOT_DT
      10:19:38  30       )
      10:19:38  31  /
      
      Table created.
      
      Elapsed: 00:14:21.17
      10:34:00 SQL> 
      
      PLAN_TABLE_OUTPUT
      -------------------------------------------------------------------------------------------------------------------
      Plan hash value: 2256974412
      
      ------------------------------------------------------------------------------------------------------------------
      | Id  | Operation                  | Name                        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
      ------------------------------------------------------------------------------------------------------------------
      |   0 | CREATE TABLE STATEMENT     |                             |  1576K|    36M|       | 15014   (1)| 00:03:01 |
      |   1 |  LOAD AS SELECT            | KEVTEMP5                    |       |       |       |            |          |
      |   2 |   HASH GROUP BY            |                             |  1576K|    36M|    54M| 13995   (1)| 00:02:48 |
      |*  3 |    COUNT STOPKEY           |                             |       |       |       |            |          |
      |   4 |     NESTED LOOPS           |                             |     2 |   130 |       |     3   (0)| 00:00:01 |
      |*  5 |      INDEX RANGE SCAN      | KEVI1                       |    13 |   624 |       |     3   (0)| 00:00:01 |
      |*  6 |      INDEX UNIQUE SCAN     | IUK2_RRS_LOAD_STATUS        |     1 |    17 |       |     0   (0)| 00:00:01 |
      |   7 |    VIEW                    |                             |  1581K|    36M|       |  2887   (1)| 00:00:35 |
      |   8 |     COUNT                  |                             |       |       |       |            |          |
      |*  9 |      FILTER                |                             |       |       |       |            |          |
      |* 10 |       HASH JOIN RIGHT SEMI |                             |  1581K|    39M|       |  1791   (2)| 00:00:22 |
      |* 11 |        INDEX RANGE SCAN    | IAK1_BATCH_PROCESSING_QUEUE |   205 |  1640 |       |     2   (0)| 00:00:01 |
      |  12 |        INDEX FAST FULL SCAN| JGACI1_AGGREGATECLAIM       |  1581K|    27M|       |  1780   (1)| 00:00:22 |
      ------------------------------------------------------------------------------------------------------------------
      
      Query Block Name / Object Alias (identified by operation id):
      -------------------------------------------------------------
      
         1 - SEL$F5BB74E1
         3 - SEL$4
         5 - SEL$4        / BW@SEL$4
         6 - SEL$4        / RLS@SEL$4
         7 - SEL$26A4F268 / A@SEL$2
         8 - SEL$26A4F268
        11 - SEL$26A4F268 / BATCH_PROCESSING_QUEUE@SEL$5
        12 - SEL$26A4F268 / PI@SEL$3
      
      Outline Data
      -------------
      
        /*+
            BEGIN_OUTLINE_DATA
            USE_NL(@"SEL$4" "RLS"@"SEL$4")
            LEADING(@"SEL$4" "BW"@"SEL$4" "RLS"@"SEL$4")
            INDEX(@"SEL$4" "RLS"@"SEL$4" ("RRS_LOAD_STATUS"."SNAPSHOT_DT" "RRS_LOAD_STATUS"."ASTG_LOAD_STATUS"))
            INDEX(@"SEL$4" "BW"@"SEL$4" ("RRS_ETL_BATCH_WINDOW"."RPTG_WINDOW_TYPE" "RRS_ETL_BATCH_WINDOW"."OBJECT_NM"
                    "RRS_ETL_BATCH_WINDOW"."BEGIN_TMSP" "RRS_ETL_BATCH_WINDOW"."END_TMSP"
                    "RRS_ETL_BATCH_WINDOW"."SNAPSHOT_DT"))
            SWAP_JOIN_INPUTS(@"SEL$26A4F268" "BATCH_PROCESSING_QUEUE"@"SEL$5")
            USE_HASH(@"SEL$26A4F268" "BATCH_PROCESSING_QUEUE"@"SEL$5")
            LEADING(@"SEL$26A4F268" "PI"@"SEL$3" "BATCH_PROCESSING_QUEUE"@"SEL$5")
            INDEX(@"SEL$26A4F268" "BATCH_PROCESSING_QUEUE"@"SEL$5" ("BATCH_PROCESSING_QUEUE"."DM_BATCH_ID"
                    "BATCH_PROCESSING_QUEUE"."SRC_BATCH_ID"))
            INDEX_FFS(@"SEL$26A4F268" "PI"@"SEL$3" ("P_PCD_AGGREGATECLAIM"."BATCH_ID"
                    "P_PCD_AGGREGATECLAIM"."AGGREGATECLAIMUID" "P_PCD_AGGREGATECLAIM"."SRCE_EFF_START_TMSP"))
            USE_HASH_AGGREGATION(@"SEL$F5BB74E1")
            NO_ACCESS(@"SEL$F5BB74E1" "A"@"SEL$2")
            OUTLINE(@"SEL$2")
            OUTLINE(@"SEL$1")
            OUTLINE(@"SEL$5")
            OUTLINE(@"SEL$3")
            MERGE(@"SEL$2")
            OUTLINE_LEAF(@"SEL$F5BB74E1")
            UNNEST(@"SEL$5")
            OUTLINE_LEAF(@"SEL$26A4F268")
            OUTLINE_LEAF(@"SEL$4")
            ALL_ROWS
            DB_VERSION('11.2.0.2')
            OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
            IGNORE_OPTIM_EMBEDDED_HINTS
            END_OUTLINE_DATA
        */
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         3 - filter(ROWNUM=1)
         5 - access("BW"."RPTG_WINDOW_TYPE"='D' AND "BW"."OBJECT_NM"='R_AGGREGATE_CLAIM_SEED' AND
                    "BW"."END_TMSP">=:B1 AND "BW"."BEGIN_TMSP"=:B1)
         6 - access("RLS"."SNAPSHOT_DT"="BW"."SNAPSHOT_DT" AND "RLS"."ASTG_LOAD_STATUS"='STARTED')
         9 - filter(ROWNUM>=1)
        10 - access("PI"."BATCH_ID"="SRC_BATCH_ID")
        11 - access("DM_BATCH_ID"="BATCH_ID_PKG"."GET_CURRENT_BATCH_ID"('PCD','ATM'))
      
      Column Projection Information (identified by operation id):
      -----------------------------------------------------------
      
         1 - SYSDEF[4], SYSDEF[32720], SYSDEF[1], SYSDEF[96], SYSDEF[32720]
         2 - (#keys=2) "A"."AGGREGATECLAIMUID"[NUMBER,22], "A"."SNAPSHOT_DT"[DATE,7],
             MAX("A"."SRCE_EFF_START_TMSP")[7]
         3 - "BW".ROWID[ROWID,10], "BW"."OBJECT_NM"[VARCHAR2,30], "BW"."SNAPSHOT_DT"[DATE,7],
             "BW"."BEGIN_TMSP"[DATE,7], "BW"."END_TMSP"[DATE,7], "BW"."RPTG_WINDOW_TYPE"[VARCHAR2,2],
             "RLS".ROWID[ROWID,10], "RLS"."SNAPSHOT_DT"[DATE,7], "RLS"."ASTG_LOAD_STATUS"[VARCHAR2,15], ROWNUM[4]
         4 - (#keys=0) "BW".ROWID[ROWID,10], "BW"."OBJECT_NM"[VARCHAR2,30], "BW"."SNAPSHOT_DT"[DATE,7],
             "BW"."BEGIN_TMSP"[DATE,7], "BW"."END_TMSP"[DATE,7], "BW"."RPTG_WINDOW_TYPE"[VARCHAR2,2],
             "RLS".ROWID[ROWID,10], "RLS"."SNAPSHOT_DT"[DATE,7], "RLS"."ASTG_LOAD_STATUS"[VARCHAR2,15]
         5 - "BW".ROWID[ROWID,10], "BW"."OBJECT_NM"[VARCHAR2,30], "BW"."SNAPSHOT_DT"[DATE,7],
             "BW"."BEGIN_TMSP"[DATE,7], "BW"."END_TMSP"[DATE,7], "BW"."RPTG_WINDOW_TYPE"[VARCHAR2,2]
         6 - "RLS".ROWID[ROWID,10], "RLS"."SNAPSHOT_DT"[DATE,7], "RLS"."ASTG_LOAD_STATUS"[VARCHAR2,15]
         7 - "A"."AGGREGATECLAIMUID"[NUMBER,22], "A"."SRCE_EFF_START_TMSP"[DATE,7], "A"."SNAPSHOT_DT"[DATE,7]
         8 - "SRC_BATCH_ID"[NUMBER,22], "PI"."BATCH_ID"[NUMBER,22], "SYS_ALIAS_1".ROWID[ROWID,10],
             "PI"."AGGREGATECLAIMUID"[NUMBER,22], "PI"."SRCE_EFF_START_TMSP"[DATE,7], ROWNUM[4]
         9 - "SRC_BATCH_ID"[NUMBER,22], "PI"."BATCH_ID"[NUMBER,22], "SYS_ALIAS_1".ROWID[ROWID,10],
             "PI"."AGGREGATECLAIMUID"[NUMBER,22], "PI"."SRCE_EFF_START_TMSP"[DATE,7]
        10 - (#keys=1) "SRC_BATCH_ID"[NUMBER,22], "PI"."BATCH_ID"[NUMBER,22], "SYS_ALIAS_1".ROWID[ROWID,10],
             "PI"."AGGREGATECLAIMUID"[NUMBER,22], "PI"."SRCE_EFF_START_TMSP"[DATE,7]
        11 - "BATCH_PROCESSING_QUEUE".ROWID[ROWID,10], "DM_BATCH_ID"[NUMBER,22], "SRC_BATCH_ID"[NUMBER,22]
        12 - "SYS_ALIAS_1".ROWID[ROWID,10], "PI"."BATCH_ID"[NUMBER,22], "PI"."SRCE_EFF_START_TMSP"[DATE,7],
             "PI"."AGGREGATECLAIMUID"[NUMBER,22]
      
      105 rows selected.
      
      Elapsed: 00:00:01.00
      

      Comment by Kevin Meade — November 21, 2012 @ 1:46 pm BST Nov 21,2012 | Reply

  3. I think you misunderstood my question. Can you give me the details of the index IDX1_RRS_ETL_BATCH_WINDOW

    Comment by srivenu kadiyala — November 21, 2012 @ 3:46 pm BST Nov 21,2012 | Reply

    • Yes you are correct, I mis-read. You want the original index.

      10:50:58 SQL> @showindexes rrs_shr rrs_etl_batch_window
      
      INDEX_NAME                     COLUMN_NAME                    INDEX_TYPE                  UNIQUENES TABLESPACE_NAME
      ------------------------------ ------------------------------ --------------------------- --------- --------------------
      IAK1_RRS_ETL_BATCH_WINDOW      SNAPSHOT_DT                    NORMAL                      NONUNIQUE RRS_SHR_TS
      
      IAK2_RRS_ETL_BATCH_WINDOW      OBJECT_NM                      NORMAL                      NONUNIQUE RRS_SHR_TS
                                     SNAPSHOT_DT                    NORMAL                      NONUNIQUE RRS_SHR_TS
                                     RPTG_WINDOW_TYPE               NORMAL                      NONUNIQUE RRS_SHR_TS
                                     BEGIN_TMSP                     NORMAL                      NONUNIQUE RRS_SHR_TS
                                     END_TMSP                       NORMAL                      NONUNIQUE RRS_SHR_TS
      
      IDX$$_57E20001                 OBJECT_NM                      NORMAL                      NONUNIQUE RRS_SHR_TS
                                     RPTG_WINDOW_TYPE               NORMAL                      NONUNIQUE RRS_SHR_TS
                                     END_TMSP                       NORMAL                      NONUNIQUE RRS_SHR_TS
      
      IDX1_RRS_ETL_BATCH_WINDOW      OBJECT_NM                      NORMAL                      NONUNIQUE RRS_SHR_TS
                                     END_TMSP                       NORMAL                      NONUNIQUE RRS_SHR_TS
                                     SNAPSHOT_DT                    NORMAL                      NONUNIQUE RRS_SHR_TS
      
      IPK_RRS_ETL_BATCH_WINDOW       RRS_ETL_BATCH_WINDOW_PK_ID     NORMAL                      UNIQUE    RRS_SHR_TS
      
      IUK1_RRS_ETL_BATCH_WINDOW      OBJECT_ID                      NORMAL                      UNIQUE    RRS_SHR_TS
                                     SNAPSHOT_DT                    NORMAL                      UNIQUE    RRS_SHR_TS
      
      
      15 rows selected.
      
      Elapsed: 00:00:00.51
      10:51:06 SQL>
      

      Comment by Kevin Meade — November 21, 2012 @ 3:51 pm BST Nov 21,2012 | Reply

  4. [...] previous post reminded me of another (fairly special) case where the order of operations in an execution plan [...]

    Pingback by Plan Order « Oracle Scratchpad — November 21, 2012 @ 6:53 pm BST Nov 21,2012 | Reply

  5. Hi Jonathan,
    I was just wondering about this predicate information for line 5 in the old plan.

    5 – filter((“BW”.”BEGIN_TMSP”=:B1)

    thanks & regards
    srivenu

    Comment by srivenu kadiyala — November 25, 2012 @ 6:13 am BST Nov 25,2012 | Reply

    • Given the imbalance in the brackets, I think there’s some extra part of the predicate that’s gone missing while the text was being copied. Possibly because of the problem of “greater than” and “less than” symbols, but maybe just a line lost in cutting and pasting. (Notice that the “between” predicate from the query doesn’t seem to be present in the predicate section of the plan.)

      Comment by Jonathan Lewis — November 25, 2012 @ 9:27 am BST Nov 25,2012 | Reply

  6. I completely missed the missing bracket. I expected to see the “greater than” and “lesser than” and was wondering why only the “equality” predicate was present.

    Comment by srivenu kadiyala — November 26, 2012 @ 4:48 pm BST Nov 26,2012 | Reply

  7. Hi Jonathan
    When automatic degree policy is set to AUTO. the parallel degree policy is derived from Elapsed time .
    Many times the estimate is wrong and queries doesnt use parallel degree.
    Can you give some examples of how elapsed time are determined in explain plan ,just like you did for cost in your book

    Comment by kgandhi80 — February 25, 2013 @ 12:06 pm BST Feb 25,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

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

Follow

Get every new post delivered to your Inbox.

Join 4,115 other followers