Oracle DBA – A lifelong learning experience

Managing plans – identifying which plans have been used

Posted by John Hallas on December 7, 2015

This blog has the aim of answering quetions about how a sql statement has performed and which plan it has used. I have asked the type of questions that we as DBAs normally have to answer and shown how an answer may be arrived at.

We know the SQL_ID and we want to see what has been happening

set long 5000 lines 160
select sql_text from dba_hist_sqltext where sql_id = '7vt1xg0afxkba';
SQL_TEXT
 --------------------------------------------------------------------------------
 INSERT INTO WC_INV_ITEM_DAILY_BAL_F(PRODUCT_WID,INVENTORY_PROD_WID,INVENTORY_ORG
 _WID,PLANT_LOC_WID,STORAGE_LOC_WID,INV_BALANCE_DT_WID,INV_BALANCE_TM_WID,UNIT_ST
 D_COST,VALUE_ONLY_AMT,VALUE_XFER_AMT,STD_COST_AMT,EXPIRATION_DATE,OBSELETE_QTY,D
 AYS_SINCE_EXPIRED,DAYS_LEFT_EXPIRY,DAYS_IN_INVENTORY,AVAILABLE_QTY,IN_TRANSIT_QT
 Y,INSPECTION_QTY,RESTRICTED_QTY,BLOCKED_QTY,RETURNED_QTY,REORDER_POINT,REPLENISH
 MENT_QTY,AVAILABLE_CONSIGN_QTY,INSP_CONSIGN_QTY,RESTRICTED_CONSIGN_QTY,BLOCKED_C
 ONSIGN_QTY,WIP_AMT,DOC_CURR_CODE,LOC_CURR_CODE,LOC_EXCHANGE_RATE,GLOBAL1_EXCHANG
 E_RATE,GLOBAL2_EXCHANGE_RATE,GLOBAL3_EXCHANGE_RATE,CREATED_BY_WID,CHANGED_BY_WID
 ,CREATED_ON_DT,CHANGED_ON_DT,AUX1_CHANGED_ON_DT,AUX2_CHANGED_ON_DT,AUX3_CHANGED_
 ON_DT,AUX4_CHANGED_ON_DT,DELETE_FLG,W_INSERT_DT,W_UPDATE_DT,DATASOURCE_NUM_ID,ET
 L_PROC_WID,INTEGRATION_ID,TENANT_ID,EFFECTIVE_FROM_DT,EFFECTIVE_TO_DT,X_CUSTOM,L

The SQL_ID is unknown but you do know some of the sql statement that will be used

select sql_id from dba_hist_sqltext where sql_text like 'INSERT%WC_INV_ITEM_DAILY_BAL_F%';

SQL_ID
————-
7vt1xg0afxkba
7052jrcrhfjbg

When did a sql statement run and what happened – did it use a different plan to normal

Once we have a sql_id when can do a lot with it
The first thing to do is identify out when it ran and what plan it used plus a few other useful details

I use the following query but  there are many variants available

set serveroutput on size 1000000
set lines 200 pages 1000
set verify off
col begin_time for a16
col end_time for a5
col module for a20
col action for a20
col execs for 999,999,990 heading "Executions"
col physrd_exec for 999,999,990 heading "Disk|Reads|per Exec"
col logrds_exec for 999,999,990 heading "Buffer|Gets|per Exec"
col CPU_EXEC for 999,990.9999 heading "CPU|Time per|Exec(secs)"
col ELA_EXEC for 999,990.9999 heading "Elapsed|Time per|Exec(secs)"
col drwrites_exec for 999,999,990 heading "Direct|Writes|per Exec"
col rowsprc_exec for 999,999,990 heading "Rows|Processed|per Exec"
col fetch_EXEC for 999,990 heading "Fetches|per Exec"
col sharable_mb for 999,990.99 heading "Sharable|Mem(mb)"
col VERSION_COUNT for 999,990 heading "Version|Count"
col plan_hash for 999999999999

accept start_in prompt 'Start date (DD-MON-RRRR HH24:MI): '
accept end_in prompt 'End date (DD-MON-RRRR HH24:MI): '
accept sql_id prompt 'SQL_ID: '


select to_char(b.BEGIN_INTERVAL_TIME,'DD-MON-RR HH24:MI') begin_time,
       to_char(b.END_INTERVAL_TIME,'HH24:MI') end_time,
       --a.sql_id,
       a.plan_hash_value plan_hash,
       --a.invalidations_delta,
       --a.module,
           --a.action,
       a.executions_delta execs,
           (case when a.executions_delta > 0 then round((a.elapsed_time_delta/a.executions_delta)/1000000,4)
             when a.executions_delta = 0 then NULL end) ELA_EXEC,
           (case when a.executions_delta > 0 then round((a.cpu_time_delta/a.executions_delta)/1000000,4)
             when a.executions_delta = 0 then NULL end) CPU_EXEC,
       (case when a.executions_delta > 0 then round(a.buffer_gets_delta/a.executions_delta,0)
             when a.executions_delta = 0 then NULL end) logrds_exec,
       (case when a.executions_delta > 0 then round(a.disk_reads_delta/a.executions_delta,0)
             when a.executions_delta = 0 then NULL end) physrd_exec,
       (case when a.executions_delta > 0 then round(a.DIRECT_WRITES_DELTA/a.executions_delta,0)
             when a.executions_delta = 0 then NULL end) drwrites_exec,
       (case when a.executions_delta > 0 then round(a.ROWS_PROCESSED_DELTA/a.executions_delta,0)
             when a.executions_delta = 0 then NULL end) rowsprc_exec
       ,round(a.SHARABLE_MEM/1024/1024,2) sharable_mb
       ,a.VERSION_COUNT
       --a.disk_reads_delta physrds,
       --a.buffer_gets_delta logrds,
       --round(a.cpu_time_delta/1000000,0) "cpu_time(s)",
       --round(a.elapsed_time_delta/1000000,0) "ela_time(s)",
       --(case when a.executions_delta > 0 then round((a.FETCHES_DELTA/a.executions_delta)/1000000,0)
       --      when a.executions_delta = 0 then NULL end) fetch_EXEC
       --(case when a.executions_delta > 0 then round(a.disk_reads_delta/a.executions_delta,0) when a.executions_delta = 0 then NULL end)/(case when a.executions_delta > 0 then round(a.ROWS_PROCESSED_DELTA/a.executions_delta,0) when a.executions_delta = 0 then NULL end) pr_per_row
from   DBA_HIST_SQLSTAT a,
       DBA_HIST_SNAPSHOT b
where  a.snap_id between (SELECT MIN(SNAP_ID) FROM DBA_HIST_SNAPSHOT WHERE BEGIN_INTERVAL_TIME >= to_date('&start_in','DD-MON-RRRR HH24:MI:SS')) and (SELECT MAX(SNAP_ID) FROM DBA_HIST_SNAPSHOT WHERE BEGIN_INTERVAL_TIME < to_date('&end_in','DD-MON-RRRR HH24:MI:SS'))
and    a.snap_id = b.snap_id
and    a.instance_number = b.instance_number
and    a.sql_id = '&&sql_id'
and    a.executions_delta > 0
order by b.BEGIN_INTERVAL_TIME,a.plan_hash_value
/
/
Start date (DD-MON-RRRR HH24:MI): 25-NOV-2015 07:00
 End date (DD-MON-RRRR HH24:MI): 27-NOV-2015 10:00
 SQL_ID: 7vt1xg0afxkba
Elapsed           CPU       Buffer         Disk         Rows
 Time per      Time per         Gets        Reads    Processed    Sharable        Version
 SQL_ID        BEGIN_TIME       END_T     PLAN_HASH   Executions    Exec(secs)    Exec(secs)     per Exec     per Exec     per Exec     Mem(mb)          Count
 ------------- ---------------- ----- ------------- ------------ ------------- ------------- ------------ ------------ ------------ ----------- --------
 7vt1xg0afxkba 25-NOV-15 07:00  08:00             0        3,074        0.0832        0.0092          197           40          112      0.08        1
 7vt1xg0afxkba 26-NOV-15 08:00  09:00             0        3,089        0.0938        0.0096          196           41          112      0.08        1
 7vt1xg0afxkba 27-NOV-15 07:00  08:00             0        3,134        0.0661        0.0101          195           41          112      0.08        1

This is a bad example because it does not have a plan hash value  – well actually because the column plan_hash_value has a null constraint on it is forced to have a value and that value is always zero
The reason in this particular case is that it is an insert statement and the PHV will always be zero, unless it is an insert with a select statement in which case it will have a PHV
There are other occassions when PHV is zero, one being when the cursor isn’t a SQL query, but rather a PL/SQL block.

For most cases we can get to the sql and the PHV we want using the queries above.

Next we want to view the various explain plans the same sql_id has used over time – I will use the dba_hist_sqlstat view listed above to give me a statement to work with

SQL_ID: gzpcnuxy18mjy

                                                                      Elapsed           CPU       Buffer         Disk      Rows
                                                                     Time per      Time per         Gets        Reads Processed    Sharable   Version
SQL_ID        BEGIN_TIME       END_T     PLAN_HASH   Executions    Exec(secs)    Exec(secs)     per Exec     per Exec  per Exec     Mem(mb)     Count
------------- ---------------- ----- ------------- ------------ ------------- ------------- ------------ ------------ ------------ ----------- --------
gzpcnuxy18mjy 01-NOV-15 07:00  08:00    3896237981            0    1,641.5961    1,614.4416  349,580,531       55,367 0         0.19        1
gzpcnuxy18mjy 03-NOV-15 07:00  08:00     454626560            0    1,155.5816    1,143.7611  335,744,525       35,224 0         0.19        1
gzpcnuxy18mjy 05-NOV-15 05:00  06:00     551161647            0    2,342.7830    1,566.2029  183,442,396      999,517 0         0.19        1

Another way, once you have a SQL_ID you are interested in is :-

select distinct sql_id, plan_hash_value from dba_hist_sqlstat
 where plan_hash_value != 0
 and sql_id = 'gzpcnuxy18mjy'
 order by 1,2
SQL_ID        PLAN_HASH_VALUE
------------- ---------------
gzpcnuxy18mjy       454626560
                    551161647
                    732682918
                   1216225714
                   1727631351
                   1862958986
                   1887098697
                   3896237981

select * from table(dbms_xplan.display_awr('gzpcnuxy18mjy',1887098697));

You can also leave off the plan hash value and it will give you all the plans in one hit in numeric order The following sql has a large number of plans so to get list of them and the output – which can run to a lot of lines so I might run these type of commands

spool x.lis
select * from table(dbms_xplan.display_awr('gzpcnuxy18mjy')
spool off
!grep -i "plan hash value:" x.lis 
Plan hash value: 93492374                                                                                                     
Plan hash value: 447933896                                                                                                    
Plan hash value: 454626560                                                                                                    
Plan hash value: 551161647                                                                                                    
Plan hash value: 732682918                                                                                                    
Plan hash value: 1020948310                                                                                                   
Plan hash value: 1093014199                                                                                                   
Plan hash value: 1216225714                                                                                                   
Plan hash value: 1727631351                                                                                                   
Plan hash value: 1862958986                                                                                                   
Plan hash value: 1887098697                                                                                                   
Plan hash value: 2270812687                                                                                                   
Plan hash value: 2701494370                                                                                                   
Plan hash value: 3896237981                                                                                                   
Plan hash value: 4095092079  

So we have a means of finding sql statements and seeing which plans it has used. I have only touched the surface of trying to show how to answer the questions that might be asked and I think it will require several more blogs to complete. I think I want to finish this one with a quick review of the options when using dbms_xplan.display_awr

Options when using dbms_xplan.display_awr

I will use the same sql_id and PHV mentioned earlier select * from table(dbms_xplan.display_awr(‘gzpcnuxy18mjy’,1887098697)); which produces 115 rows of output. It is a large and complex statement but that actually makes it more useful as a demonstration.

select * from table(dbms_xplan.display_awr('gzpcnuxy18mjy',1887098697));
SQL_ID gzpcnuxy18mjy
--------------------

insert /*+ APPEND */  into ODI_TEMP.I$_DWD_X_ORDER_INVENTORY    (
        PICK_DT_KEY,    SKU_ITEM_KEY,   SRC_BSNS_UNIT_KEY,      DSTN_BSNS_UNIT_KEY,
        DISK_RUN,       ACT_PICKED_CASES,       OUTSTANDING_CASES,      LAST_UPDT_DT,
        LAST_UPDT_BY    ) select                DWD_X_ORDER_INVENTORY.PICK_DT_KEY,
        DWD_X_ORDER_INV_SUBSELECT.SKU_ITEM_KEY,
        DWD_X_ORDER_INVENTORY.SRC_BSNS_UNIT_KEY,
        DWD_X_ORDER_INVENTORY.DSTN_BSNS_UNIT_KEY,
        DWD_X_ORDER_INVENTORY.DISK_RUN,         DWD_X_ORDER_INV_SUBSELECT.ACT_PICK_CAS
ES,     DWD_X_ORDER_INVENTORY.RQSTD_UNIT_QTY -
DWD_X_ORDER_INV_SUBSELECT.ACT_PICK_CASES,       SYSDATE,        USER   from     (
select          SOH.PICK_NOT_BEFORE_DATE_KEY
PICK_NOT_BEFORE_DATE_KEY,       SOH.ORG_BSNS_UNIT_KEY
ORG_BSNS_UNIT_KEY,      SOH.ORDER_TYPE    ORDER_TYPE,   SOH.DISTRO_NBR
DISTRO_NBR,     ICL.SKU_ITEM_KEY    SKU_ITEM_KEY,       SUM(ICL.CONTAINER_QTY)
ACT_PICK_CASES, SOL.DEST_ID    DEST_ID, SOL.DSTN_BSNS_UNIT_KEY
DSTN_BSNS_UNIT_KEY from BIA_RTL.DWB_X_LGS_INV_CNTRL_DOC   ICD,
BIA_RTL.DWB_X_LGS_INV_CNTRL_DOC_LI   ICL,
BIA_RTL.DWB_X_LGS_STOCK_ORDER_HDR   SOH,
BIA_RTL.DWB_X_LGS_STOCK_ORDER_LI   SOL where    (1=1)  And
(ICD.INV_CNTRL_DOC_NBR = ICL.INV_CNTRL_DOC_NBR     AND
ICD.ORG_BSNS_UNIT_KEY = ICL.ORG_BSNS_UNIT_KEY     AND
ICD.INV_DOC_TYP_CD    = 'P'     AND ICD.VIRTUAL_PICK_IND  = 'N'     AND
ICD.STATUS_FLAG <> 'D'     AND ICL.DISTRO_NBR        = SOH.DISTRO_NBR
  AND SOH.DISTRO_NBR = SOL.DISTRO_NBR     AND SOL.SKU_ITEM_KEY =
ICL.SKU_ITEM_KEY     AND ICL.ORG_BSNS_UNIT_KEY = SOH.ORG_BSNS_UNIT_KEY
AND (SOH.DISTRO_NBR,SOH.ORG_BSNS_UNIT_KEY) IN (SELECT DSOH.DISTRO_NBR ,
DSOH.ORG_BSNS_UNIT_KEY    FROM  BIA_RTL.DWB_X_LGS_INV_CNTRL_DOC IICD,
        BIA_RTL.DWB_X_LGS_INV_CNTRL_DOC_LI IICL,
ODI_TEMP.W$_DWB_X_LGS_STOCK_ORDER_HDR   DSOH   WHERE
IICD.INV_CNTRL_DOC_NBR = IICL.INV_CNTRL_DOC_NBR     AND
IICD.ORG_BSNS_UNIT_KEY = IICL.ORG_BSNS_UNIT_KEY      AND
IICL.DISTRO_NBR  =  DSOH.DISTRO_NBR     AND IICL.ORG_BSNS_UNIT_KEY =
DSOH.ORG_BSNS_UNIT_KEY             AND TRIM(UPPER(IICD.INV_DOC_TYP_CD))
   = 'P'     AND IICD.VIRTUAL_PICK_IND = 'N' AND IICD.STATUS_FLAG <>
'D'))  Group By SOH.PICK_NOT_BEFORE_DATE_KEY,  SOH.ORG_BSNS_UNIT_KEY,
SOH.ORDER_TYPE,  SOH.DISTRO_NBR,  ICL.SKU_ITEM_KEY,  SOL.DEST_ID,
SOL.DSTN_BSNS_UNIT_KEY  )   DWD_X_ORDER_INV_SUBSELECT,
BIA_RTL.DWD_X_ORDER_INVENTORY   DWD_X_ORDER_INVENTORY where     (1=1)  And
(DWD_X_ORDER_INVENTORY.SKU_ITEM_KEY =
DWD_X_ORDER_INV_SUBSELECT.SKU_ITEM_KEY AND
DWD_X_ORDER_INVENTORY.SRC_BSNS_UNIT_KEY =
DWD_X_ORDER_INV_SUBSELECT.ORG_BSNS_UNIT_KEY AND
DWD_X_ORDER_INV_SUBSELECT.DISTRO_NBR = DWD_X_ORDER_INVENTORY.DISK_RUN
AND DWD_X_ORDER_INVENTORY.DSTN_BSNS_UNIT_KEY     =
DWD_X_ORDER_INV_SUBSELECT.DSTN_BSNS_UNIT_KEY AND
DWD_X_ORDER_INV_SUBSELECT.PICK_NOT_BEFORE_DATE_KEY =
DWD_X_ORDER_INVENTORY.PICK_DT_KEY)

Plan hash value: 1887098697

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                               | Name                          | Rows  | Bytes | Cost (%CPU)| Time   | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                                        |                               |       |       | 26531 (100)|                |       |       |        |      |            |
|   1 |  LOAD AS SELECT                                         |                               |       |       |         |           |       |       |        |      |            |
|   2 |   PX COORDINATOR                                        |                               |       |       |         |           |       |       |        |      |            |
|   3 |    PX SEND QC (RANDOM)                                  | :TQ10008                      |   430 | 42570 | 26531   (3)| 00:01:50 |     |       |  Q1,08 | P->S | QC (RAND)  |
|   4 |     HASH GROUP BY                                       |                               |   430 | 42570 | 26531   (3)| 00:01:50 |     |       |  Q1,08 | PCWP |            |
|   5 |      PX RECEIVE                                         |                               |   430 | 42570 | 26531   (3)| 00:01:50 |     |       |  Q1,08 | PCWP |            |
|   6 |       PX SEND HASH                                      | :TQ10007                      |   430 | 42570 | 26531   (3)| 00:01:50 |     |       |  Q1,07 | P->P | HASH       |
|   7 |        HASH GROUP BY                                    |                               |   430 | 42570 | 26531   (3)| 00:01:50 |     |       |  Q1,07 | PCWP |            |
|   8 |         NESTED LOOPS                                    |                               |   430 | 42570 | 26530   (3)| 00:01:50 |     |       |  Q1,07 | PCWP |            |
|   9 |          NESTED LOOPS                                   |                               |   430 | 42570 | 26530   (3)| 00:01:50 |     |       |  Q1,07 | PCWP |            |
|  10 |           VIEW                                          | VM_NWVW_2                     |   430 | 23220 | 26522   (3)| 00:01:50 |     |       |  Q1,07 | PCWP |            |
|  11 |            HASH UNIQUE                                  |                               |   430 |   105K| 26522   (3)| 00:01:50 |     |       |  Q1,07 | PCWP |            |
|  12 |             PX RECEIVE                                  |                               |   430 |   105K| 26522   (3)| 00:01:50 |     |       |  Q1,07 | PCWP |            |
|  13 |              PX SEND HASH                               | :TQ10006                      |   430 |   105K| 26522   (3)| 00:01:50 |     |       |  Q1,06 | P->P | HASH       |
|  14 |               HASH UNIQUE                               |                               |   430 |   105K| 26522   (3)| 00:01:50 |     |       |  Q1,06 | PCWP |            |
|  15 |                NESTED LOOPS                             |                               |   430 |   105K| 26521   (3)| 00:01:50 |     |       |  Q1,06 | PCWP |            |
|  16 |                 NESTED LOOPS                            |                               |  4111 |   105K| 26521   (3)| 00:01:50 |     |       |  Q1,06 | PCWP |            |
|  17 |                  HASH JOIN                              |                               |  4111 |   875K| 26276   (3)| 00:01:49 |     |       |  Q1,06 | PCWP |            |
|  18 |                   PX RECEIVE                            |                               |    87 | 16095 | 20733   (3)| 00:01:26 |     |       |  Q1,06 | PCWP |            |
|  19 |                    PX SEND BROADCAST                    | :TQ10005                      |    87 | 16095 | 20733   (3)| 00:01:26 |     |       |  Q1,05 | P->P | BROADCAST  |
|  20 |                     BUFFER SORT                         |                               |   164 | 18696 |         |           |       |       |  Q1,05 | PCWP |            |
|  21 |                      NESTED LOOPS                       |                               |    87 | 16095 | 20733   (3)| 00:01:26 |     |       |  Q1,05 | PCWP |            |
|  22 |                       NESTED LOOPS                      |                               | 18259 | 16095 | 20733   (3)| 00:01:26 |     |       |  Q1,05 | PCWP |            |
|  23 |                        HASH JOIN                        |                               | 18259 |  2710K| 19283   (3)| 00:01:20 |     |       |  Q1,05 | PCWP |            |
|  24 |                         PX RECEIVE                      |                               |  4257 | 55341 |     2   (0)| 00:00:01 |     |       |  Q1,05 | PCWP |            |
|  25 |                          PX SEND BROADCAST              | :TQ10002                      |  4257 | 55341 |     2   (0)| 00:00:01 |     |       |  Q1,02 | P->P | BROADCAST  |
|  26 |                           PX BLOCK ITERATOR             |                               |  4257 | 55341 |     2   (0)| 00:00:01 |     |       |  Q1,02 | PCWC |            |
|  27 |                            TABLE ACCESS STORAGE FULL    | W$_DWB_X_LGS_STOCK_ORDER_HDR  |  4257 | 55341 |     2   (0)| 00:00:01 |     |       |  Q1,02 | PCWP |            |
|  28 |                         HASH JOIN                       |                               |  3235K|   428M| 19280   (3)| 00:01:20 |     |       |  Q1,05 | PCWP |            |
|  29 |                          PX RECEIVE                     |                               |   754K|    28M|   580   (1)| 00:00:03 |     |       |  Q1,05 | PCWP |            |
|  30 |                           PX SEND HASH                  | :TQ10003                      |   754K|    28M|   580   (1)| 00:00:03 |     |       |  Q1,03 | P->P | HASH       |
|  31 |                            PX BLOCK ITERATOR            |                               |   754K|    28M|   580   (1)| 00:00:03 |     1 |  1195 |  Q1,03 | PCWC |          |
|  32 |                             TABLE ACCESS STORAGE FULL   | DWB_X_LGS_STOCK_ORDER_HDR     |   754K|    28M|   580   (1)| 00:00:03 |     1 |  1195 |  Q1,03 | PCWP |          |
|  33 |                          PX RECEIVE                     |                               |    24M|  2380M| 18696   (3)| 00:01:18 |     |       |  Q1,05 | PCWP |            |
|  34 |                           PX SEND HASH                  | :TQ10004                      |    24M|  2380M| 18696   (3)| 00:01:18 |     |       |  Q1,04 | P->P | HASH       |
|  35 |                            HASH JOIN BUFFERED           |                               |    24M|  2380M| 18696   (3)| 00:01:18 |     |       |  Q1,04 | PCWP |            |
|  36 |                             PX RECEIVE                  |                               |  7832K|   336M|  6891   (3)| 00:00:29 |     |       |  Q1,04 | PCWP |            |
|  37 |                              PX SEND HASH               | :TQ10000                      |  7832K|   336M|  6891   (3)| 00:00:29 |     |       |  Q1,00 | P->P | HASH       |
|  38 |                               PX BLOCK ITERATOR         |                               |  7832K|   336M|  6891   (3)| 00:00:29 |     1 |  1195 |  Q1,00 | PCWC |          |
|  39 |                                TABLE ACCESS STORAGE FULL| DWB_X_LGS_INV_CNTRL_DOC       |  7832K|   336M|  6891   (3)| 00:00:29 |     1 |  1195 |  Q1,00 | PCWP |          |
|  40 |                             PX RECEIVE                  |                               |   292M|    14G| 11756   (3)| 00:00:49 |     |       |  Q1,04 | PCWP |            |
|  41 |                              PX SEND HASH               | :TQ10001                      |   292M|    14G| 11756   (3)| 00:00:49 |     |       |  Q1,01 | P->P | HASH       |
|  42 |                               PX BLOCK ITERATOR         |                               |   292M|    14G| 11756   (3)| 00:00:49 |     1 |  1195 |  Q1,01 | PCWC |          |
|  43 |                                TABLE ACCESS STORAGE FULL| DWB_X_LGS_INV_CNTRL_DOC_LI    |   292M|    14G| 11756   (3)| 00:00:49 |     1 |  1195 |  Q1,01 | PCWP |          |
|  44 |                        INDEX RANGE SCAN                 | DWB_X_LGS_STOCK_ORDER_LI_PK   |     1 |       |     0   (0)|                |       |       |  Q1,05 | PCWP |            |
|  45 |                       TABLE ACCESS BY GLOBAL INDEX ROWID| DWB_X_LGS_STOCK_ORDER_LI      |     1 |    33 |     0   (0)|                | ROWID | ROWID |  Q1,05 | PCWP |            |
|  46 |                   PX BLOCK ITERATOR                     |                               |   292M|  9199M|  5495   (2)| 00:00:23 |     |       |  Q1,06 | PCWC |            |
|  47 |                    INDEX STORAGE FAST FULL SCAN         | DWB_X_LGS_INV_CNTRL_DOC_LI_PK |   292M|  9199M|  5495   (2)| 00:00:23 |     |       |  Q1,06 | PCWP |            |
|  48 |                  INDEX RANGE SCAN                       | DWB_X_LGS_INV_CNTRL_DOC_PK    |     1 |       |     0   (0)|                |       |       |  Q1,06 | PCWP |            |
|  49 |                 TABLE ACCESS BY GLOBAL INDEX ROWID      | DWB_X_LGS_INV_CNTRL_DOC       |     1 |    33 |     0   (0)|                | ROWID | ROWID |  Q1,06 | PCWP |            |
|  50 |           PARTITION RANGE ITERATOR                      |                               |     1 |       |     0   (0)|                |   KEY |   KEY |  Q1,07 | PCWP |            |
|  51 |            INDEX RANGE SCAN                             | DWD_X_ORDER_INVENTORY_PK      |     1 |       |     0   (0)|                |   KEY |   KEY |  Q1,07 | PCWP |            |
|  52 |          TABLE ACCESS BY LOCAL INDEX ROWID              | DWD_X_ORDER_INVENTORY         |     1 |    45 |     0   (0)|                |     1 |     1 |  Q1,07 | PCWP |            |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=6)
   - automatic DOP: Computed Degree of Parallelism is 56


115 rows selected.

 

Using the keyword ALL we get an additional 18 lines which show the query block names and aliases

select * from table(dbms_xplan.display_awr('gzpcnuxy18mjy',1887098697,null,'ALL'));

Query Block Name / Object Alias (identified by operation id):
————————————————————-

1 – SEL$56D93BC4
10 – SEL$555A942D / VM_NWVW_2@SEL$3E72925F
11 – SEL$555A942D
27 – SEL$555A942D / DSOH@SEL$3
32 – SEL$555A942D / SOH@SEL$2
39 – SEL$555A942D / ICD@SEL$2
43 – SEL$555A942D / ICL@SEL$2
44 – SEL$555A942D / SOL@SEL$2
45 – SEL$555A942D / SOL@SEL$2
47 – SEL$555A942D / IICL@SEL$3
48 – SEL$555A942D / IICD@SEL$3
49 – SEL$555A942D / IICD@SEL$3
51 – SEL$56D93BC4 / DWD_X_ORDER_INVENTORY@SEL$1
52 – SEL$56D93BC4 / DWD_X_ORDER_INVENTORY@SEL$1

 

Personally I run a simplified version of the call on the first pass and remove the parallel details and I have never found the bytes information useful so that goes as well

 select * from table(dbms_xplan.display_awr('gzpcnuxy18mjy',1887098697,null,'TYPICAL -BYTES -PARALLEL'));

Wrapping up I have shown how I start to answer the following questions

  1. We know the SQL_ID and we want to see what has been happening
  2. The SQL_ID is unknown but you do know some of the sql statement that will be used
  3. When did a sql statement run and what happened
  4. Options when using dbms_xplan.display_awr

In my next entry I want to show how I would start to answer this question

  1. What was happening in the period between time a and time b

 

Advertisements

One Response to “Managing plans – identifying which plans have been used”

  1. Krishna Mohan said

    This article is awesome…it helps me a lot…. N would like one more thing Can we import profile of one sql_id to another sql_id (like Prod to Dev) n how we do that.

    Thanks,
    Krish.

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

 
%d bloggers like this: