This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Dec 18, 2012 8:35 PM by KODS RSS

Plan shows SQL execution time Hrs 999:59:59 Sec.

KODS Newbie
Currently Being Moderated
Dear Masters,

Today my development team reported that the below query is taking more time. When I check the plan for my shok it is showing Hrs 999:59:59 Sec.

Kindly help me in tuning this query.

I am using oracle 10.2.0.3 version.
SELECT  
 S_INVLOC.NAME , S_ORDER.ACCNT_ID , S_ORDER.APPR_BY_EMP_ID , S_ORDER.BL_CON_ID , S_ORDER.BL_OU_ID , S_ORDER.CARRIER_CD , S_ORDER.CONTACT_ID , S_ORDER.CURCY_CD , S_ORDER.TAX_EXEMPT_FLG ,
 S_ORDER.REQ_SHIP_DT , S_ORDER.SHIP_ADDR_ID , S_ORDER.SHIP_CON_ID , S_ORDER.SHIP_METH_CD , S_ORDER.SHIP_OU_ID , S_ORDER.STATUS_CD , S_ORDER.TAX_EXEMPT_NUM , S_ORDER.TAX_EXEMPT_REASON ,
 S_ORDER.OPTY_ID , S_ORDER.BU_ID PR_VIS_ORG_ID , S_ORDER.PROMO_ID , S_ORDER.PRI_LST_ID , S_ORDER.AGREE_ID AGREEMENT_ID , S_ORDER.ENTLMNT_ID ENTITLEMENT_ID , S_ORDER.SR_ID ,
 S_ORDER.BILLABLE_FLG , S_ORDER_ITEM.ROW_ID , S_ORDER_ITEM.ORDER_ID , S_ORDER_ITEM.LN_NUM , S_ORDER_ITEM.PROD_ID , S_ORDER_ITEM.ADJ_UNIT_PRI , S_ORDER_ITEM.BASE_UNIT_PRI ,
 S_ORDER_ITEM.CARRIER_CD CARRIER_CD1 , S_ORDER_ITEM.EXTD_QTY , S_ORDER_ITEM.QTY_SHIPPED , S_ORDER_ITEM.REQ_SHIP_DT REQ_SHIP_DT1 , S_ORDER_ITEM.SHIP_ADDR_ID SHIP_ADDR_ID1 ,
 S_ORDER_ITEM.SHIP_CON_ID SHIP_CON_ID1 , S_ORDER_ITEM.SHIP_METH_CD SHIP_METH_CD1 , S_ORDER_ITEM.STATUS_CD STATUS_CD1 , S_ORDER_ITEM.PROD_STATUS_CD , S_ORDER_ITEM.PROD_NAME ,
 S_ORDER_ITEM.LOANER_FLG , S_ORDER_ITEM.DISCNT_METH_CD , S_ORDER_ITEM.SHIP_OU_ID SHIP_OU_ID1 , S_ORDER_ITEM.STATUS_DT , S_ORDER_ITEM.MUST_DLVR_BY_DT , S_ORDER_ITEM.ACTION_CD ,
 S_ORDER_ITEM.ROLLUP_PRI , S_PROD_INT.NAME NAME1 , A.NAME NAME2 , B.NAME NAME3 , S_VOL_DISCNT.NAME NAME4 , ACCNT.PR_INDUST_ID , POSCRTDORG.OU_ID CREATED_BY_ORG_ID ,
 POSOWNERORG.OU_ID PR_OWNER_ORG_ID , PAROITEM.LN_NUM , PAROITEM.PROD_ID , SHIPOITEM.CITY , SHIPOITEM.COUNTRY , SHIPOITEM.ZIPCODE , SHIPO.CITY , SHIPO.COUNTRY , SHIPO.ZIPCODE ,
 BILL.CITY , BILL.COUNTRY , BILL.ZIPCODE , S_CAMP_CON.PR_CALL_LST_ID SEGMENT_ID , S_ORDER.DCP_ID OFFER_ID , APPRBYPOS.PR_HELD_POSTN_ID , POSOWNERORG.ROW_ID , POSOWNERORG.PR_EMP_ID ,
 S_ENTLMNT.PAR_AGREE_ID , ROOTOITEM.PROD_ID ROOT_LN_PROD_ID , ROOTOITEM.LN_NUM ROOT_LN_NUM , S_ORDER.PR_POSTN_ID , S_CAMP_CON.CAMP_LD_WAVE_ID , S_SRC.REGION_ID , OPRI.COST_PRI ,
 S_ORDER_ITEM.ORDER_ITM_CURCY_CD OITM_CURCY_CD , ROOTOITEM.ORDER_ITM_CURCY_CD ROOTOITM_CURCY_CD , S_ORDER_ITEM.ORDER_ITM_EXCH_DT OITM_AMT_DT , S_ORDER.ORDER_EXCH_DT ,
 S_ORDER_ITEM.CREATED , S_ORDER_ITEM.NET_PRI , S_ORDER_ITEM.CRSE_OFFR_ID , S_ORDER_ITEM.PRI_LST_ID OITM_PRI_LST_ID , ROOTOITEM.PRI_LST_ID ROOTOITM_PRI_LST_ID ,
 OWNORG.PRTNR_FLG , VISORG.PRTNR_FLG , S_CAMP_LD_WAVE.LAUNCHED_TS , QUOTE.CREATED , S_ORDER.ACTIVE_FLG , S_ORDER.APPROVED_FLG , PARPROD.PROD_TYPE_CD , ROOTPROD.PROD_TYPE_CD ,
 S_PROD_INT.PRICE_TYPE_CD , S_SRC.PROG_END_DT , S_SRC.PROG_START_DT , S_SRC.ROW_ID , S_ORDER.CREATED , OWNORG.ROW_ID , 0 AS X_CUSTOM , S_ORDER_ITEM. X_PHONE_NUMBER ,
 S_ORDER_ITEM.X_BACKEND_SERVICE_ID , S_ORDER_ITEM.SERVICE_NUM , S_ORDER_ITEM.BILL_ACCNT_ID , S_ORDER_X.ATTRIB_04 , S_ORDER_X.ATTRIB_30 , S_ORDER_X.ATTRIB_31
   FROM
 SIEBEL.V_ORDER_ITEM S_ORDER_ITEM, SIEBEL.S_ORDER_ITEM PAROITEM, SIEBEL.S_ORDER_ITEM ROOTOITEM, SIEBEL.S_ORDER, SIEBEL.S_VDISCNT_ITEM A, SIEBEL.S_VOL_DISCNT, SIEBEL.S_PROD_INT,
 SIEBEL.S_VDISCNT_ITEM B, SIEBEL.S_INVLOC, SIEBEL.S_ORG_EXT ACCNT, SIEBEL.S_POSTN POSOWNERORG, SIEBEL.S_CONTACT CRTD, SIEBEL.S_CONTACT APPRBYPOS, SIEBEL.S_POSTN POSCRTDORG,
 SIEBEL.S_ADDR_ORG SHIPOITEM, SIEBEL.S_ADDR_ORG SHIPO, SIEBEL.S_ADDR_ORG BILL, SIEBEL.S_CAMP_CON, SIEBEL.S_ENTLMNT, SIEBEL.S_SRC, SIEBEL.S_ORDER_ITM_PRI OPRI,
 SIEBEL.S_CAMP_LD_WAVE, SIEBEL.S_DOC_QUOTE QUOTE, SIEBEL.S_ORG_EXT OWNORG, SIEBEL.S_ORG_EXT OWNORG1, SIEBEL.S_ORG_EXT VISORG1, SIEBEL.S_ORG_EXT VISORG,
 SIEBEL.S_PROD_INT PARPROD, SIEBEL.S_PROD_INT ROOTPROD, SIEBEL.S_ORDER_X
 WHERE 
     S_ORDER_ITEM.ORDER_ID     = S_ORDER.ROW_ID
    AND S_ORDER_ITEM.ROOT_ORDER_ITEM_ID  = ROOTOITEM.ROW_ID
    AND S_ORDER_ITEM.PAR_ORDER_ITEM_ID   = PAROITEM.ROW_ID(+)
    AND S_ORDER_ITEM.PROD_ID      = S_PROD_INT.ROW_ID(+)
    AND S_ORDER_ITEM.SRC_INVLOC_ID       = S_INVLOC.ROW_ID(+)
    AND S_ORDER_ITEM.VOL_DISCNT_ITEM_ID  = A.ROW_ID(+)
    AND S_ORDER_ITEM.VOL_DISCNT_ID       = S_VOL_DISCNT.ROW_ID(+)
    AND S_ORDER_ITEM.VOL_UPSELL_ITEM_ID  = B.ROW_ID(+)
    AND S_ORDER_ITEM.SHIP_ADDR_ID = SHIPOITEM.ROW_ID(+)
    AND S_ORDER.PR_POSTN_ID       = POSOWNERORG.ROW_ID(+)
    AND S_ORDER_ITEM.CREATED_BY   = CRTD.ROW_ID(+)
    AND CRTD.PR_HELD_POSTN_ID     = POSCRTDORG.ROW_ID(+)
    AND S_ORDER.ACCNT_ID   = ACCNT.ROW_ID(+)
    AND S_ORDER.CAMP_CON_ID       = S_CAMP_CON.ROW_ID(+)
    AND S_ORDER.SHIP_ADDR_ID      = SHIPO.ROW_ID(+)
    AND S_ORDER.BL_ADDR_ID        = BILL.ROW_ID(+)
    AND S_ORDER.APPR_BY_EMP_ID    = APPRBYPOS.ROW_ID(+)
    AND S_ORDER.ENTLMNT_ID        = S_ENTLMNT.ROW_ID(+)
    AND S_ORDER.PROMO_ID   = S_SRC.ROW_ID(+)
    AND S_ORDER_ITEM.ROW_ID       = OPRI.PAR_ROW_ID(+)
    AND S_CAMP_CON.CAMP_LD_WAVE_ID       = S_CAMP_LD_WAVE.ROW_ID(+)
    AND S_ORDER.QUOTE_ID   = QUOTE.ROW_ID(+)
    AND POSOWNERORG.OU_ID  = OWNORG1.ROW_ID(+)
    AND OWNORG1.PAR_BU_ID  = OWNORG.ROW_ID(+)
    AND S_ORDER.BU_ID      = VISORG1.ROW_ID(+)
    AND VISORG1.PAR_BU_ID  = VISORG.ROW_ID(+)
    AND PAROITEM.PROD_ID   = PARPROD.ROW_ID(+)
    AND ROOTOITEM.PROD_ID  = ROOTPROD.ROW_ID(+)
    AND S_ORDER_ITEM.ORDER_ID     = S_ORDER_X.PAR_ROW_ID(+)
    AND S_ORDER_ITEM.ROOT_ORDER_ITEM_ID IS NOT NULL;
Execution Plan is : select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 73463824

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                                | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                         |                   |  3170K|    12G|  4100G  (4)|999:59:59 |
|   1 |  NESTED LOOPS OUTER                                      |                   |  3170K|    12G|  4100G  (4)|999:59:59 |
|   2 |   NESTED LOOPS OUTER                                     |                   |  3170K|    12G|  4100G  (4)|999:59:59 |
|   3 |    NESTED LOOPS OUTER                                    |                   |  3170K|    12G|  4100G  (4)|999:59:59 |
|   4 |     NESTED LOOPS OUTER                                   |                   |  3170K|    12G|  4100G  (4)|999:59:59 |
|   5 |      NESTED LOOPS OUTER                                  |                   |  3170K|    12G|  4100G  (4)|999:59:59 |
|   6 |       NESTED LOOPS                                       |                   |  3170K|    12G|  4100G  (4)|999:59:59 |
|   7 |        NESTED LOOPS OUTER                                |                   |  3170K|    12G|  4100G  (4)|999:59:59 |
|   8 |         NESTED LOOPS OUTER                               |                   |  3170K|    12G|  4100G  (4)|999:59:59 |
|   9 |          NESTED LOOPS OUTER                              |                   |  3170K|    12G|  4100G  (4)|999:59:59 |
|  10 |           NESTED LOOPS OUTER                             |                   |  3170K|    12G|  4100G  (4)|999:59:59 |
|  11 |            NESTED LOOPS OUTER                            |                   |  3170K|    12G|  4100G  (4)|999:59:59 |
|  12 |             NESTED LOOPS OUTER                           |                   |  3170K|    12G|  4100G  (4)|999:59:59 |
|  13 |              NESTED LOOPS OUTER                          |                   |  3170K|    12G|  4100G  (4)|999:59:59 |
|  14 |               NESTED LOOPS OUTER                         |                   |  3170K|    12G|  4100G  (4)|999:59:59 |
|  15 |                NESTED LOOPS OUTER                        |                   |  3170K|    11G|  4100G  (4)|999:59:59 |
|  16 |                 NESTED LOOPS OUTER                       |                   |  3170K|    11G|  4100G  (4)|999:59:59 |
|  17 |                  NESTED LOOPS OUTER                      |                   |  3170K|    11G|  4100G  (4)|999:59:59 |
|  18 |                   NESTED LOOPS OUTER                     |                   |  3170K|    10G|  4100G  (4)|999:59:59 |
|  19 |                    NESTED LOOPS OUTER                    |                   |  3170K|    10G|  4100G  (4)|999:59:59 |
|  20 |                     NESTED LOOPS OUTER                   |                   |  3170K|    10G|  4100G  (4)|999:59:59 |
|  21 |                      NESTED LOOPS                        |                   |  3170K|  9911M|  4100G  (4)|999:59:59 |
|  22 |                       NESTED LOOPS OUTER                 |                   |  3108K|  4927M|  2569K  (2)| 01:08:47 |
|  23 |                        NESTED LOOPS OUTER                |                   |  3108K|  4835M|   115K (14)| 00:03:06 |
|  24 |                         NESTED LOOPS OUTER               |                   |  3108K|  4782M|   115K (14)| 00:03:06 |
|  25 |                          NESTED LOOPS OUTER              |                   |  3108K|  4660M|   112K (12)| 00:03:02 |
|  26 |                           NESTED LOOPS OUTER             |                   |  3108K|  4417M|   112K (12)| 00:03:02 |
|  27 |                            NESTED LOOPS OUTER            |                   |  3108K|  4227M|   112K (12)| 00:03:02 |
|  28 |                             NESTED LOOPS OUTER           |                   |  3108K|  3943M|   112K (12)| 00:03:02 |
|  29 |                              NESTED LOOPS OUTER          |                   |  3108K|  3178M|   111K (11)| 00:02:59 |
|  30 |                               TABLE ACCESS FULL          | S_ORDER           |  3108K|  2413M|   109K  (9)| 00:02:56 |
|  31 |                               TABLE ACCESS BY INDEX ROWID| S_ADDR_ORG        |     1 |   258 |     1   (0)| 00:00:01 |
|* 32 |                                INDEX UNIQUE SCAN         | S_ADDR_ORG_P1     |     1 |       |     1   (0)| 00:00:01 |
|  33 |                              TABLE ACCESS BY INDEX ROWID | S_ADDR_ORG        |     1 |   258 |     1   (0)| 00:00:01 |
|* 34 |                               INDEX UNIQUE SCAN          | S_ADDR_ORG_P1     |     1 |       |     1   (0)| 00:00:01 |
|  35 |                             TABLE ACCESS BY INDEX ROWID  | S_CAMP_CON        |     1 |    96 |     1   (0)| 00:00:01 |
|* 36 |                              INDEX UNIQUE SCAN           | S_CAMP_CON_P1     |     1 |       |     1   (0)| 00:00:01 |
|  37 |                            TABLE ACCESS BY INDEX ROWID   | S_ENTLMNT         |     1 |    64 |     1   (0)| 00:00:01 |
|* 38 |                             INDEX UNIQUE SCAN            | S_ENTLMNT_P1      |     1 |       |     1   (0)| 00:00:01 |
|  39 |                           TABLE ACCESS BY INDEX ROWID    | S_SRC             |     1 |    82 |     1   (0)| 00:00:01 |
|* 40 |                            INDEX UNIQUE SCAN             | S_SRC_P1          |     1 |       |     1   (0)| 00:00:01 |
|  41 |                          TABLE ACCESS BY INDEX ROWID     | S_CAMP_LD_WAVE    |     1 |    41 |     1   (0)| 00:00:01 |
|* 42 |                           INDEX UNIQUE SCAN              | S_CAMP_LD_WAVE_P1 |     1 |       |     1   (0)| 00:00:01 |
|  43 |                         TABLE ACCESS BY INDEX ROWID      | S_DOC_QUOTE       |     1 |    18 |     1   (0)| 00:00:01 |
|* 44 |                          INDEX UNIQUE SCAN               | S_DOC_QUOTE_P1    |     1 |       |     1   (0)| 00:00:01 |
|  45 |                        TABLE ACCESS BY INDEX ROWID       | S_POSTN           |     1 |    31 |     1   (0)| 00:00:01 |
|* 46 |                         INDEX UNIQUE SCAN                | S_POSTN_P1        |     1 |       |     1   (0)| 00:00:01 |
|* 47 |                       VIEW                               | V_ORDER_ITEM      |     1 |  1616 |  1318K  (4)| 00:35:19 |
|  48 |                        NESTED LOOPS                      |                   |  3128K|  1372M|  1318K  (4)| 00:35:19 |
|  49 |                         INDEX FAST FULL SCAN             | S_ETL_I_IMG_25_M2 |  3128K|    32M|  2756  (11)| 00:00:05 |
|* 50 |                         TABLE ACCESS BY INDEX ROWID      | S_ORDER_ITEM      |     1 |   449 |     2   (0)| 00:00:01 |
|* 51 |                          INDEX UNIQUE SCAN               | S_ORDER_ITEM_P1   |     1 |       |     2   (0)| 00:00:01 |
|  52 |                      TABLE ACCESS BY INDEX ROWID         | S_VDISCNT_ITEM    |     1 |   134 |     1   (0)| 00:00:01 |
|* 53 |                       INDEX UNIQUE SCAN                  | S_VDISCNT_ITEM_P1 |     1 |       |     1   (0)| 00:00:01 |
|  54 |                     TABLE ACCESS BY INDEX ROWID          | S_VOL_DISCNT      |     1 |   134 |     1   (0)| 00:00:01 |
|* 55 |                      INDEX UNIQUE SCAN                   | S_VOL_DISCNT_P1   |     1 |       |     1   (0)| 00:00:01 |
|  56 |                    TABLE ACCESS BY INDEX ROWID           | S_VDISCNT_ITEM    |     1 |   134 |     1   (0)| 00:00:01 |
|* 57 |                     INDEX UNIQUE SCAN                    | S_VDISCNT_ITEM_P1 |     1 |       |     1   (0)| 00:00:01 |
|  58 |                   TABLE ACCESS BY INDEX ROWID            | S_ADDR_ORG        |     1 |   258 |     1   (0)| 00:00:01 |
|* 59 |                    INDEX UNIQUE SCAN                     | S_ADDR_ORG_P1     |     1 |       |     1   (0)| 00:00:01 |
|* 60 |                  TABLE ACCESS FULL                       | S_ORDER_ITM_PRI   |     1 |    45 |     0   (0)| 00:00:01 |
|  61 |                 TABLE ACCESS BY INDEX ROWID              | S_INVLOC          |     1 |    33 |     1   (0)| 00:00:01 |
|* 62 |                  INDEX UNIQUE SCAN                       | S_INVLOC_P1       |     1 |       |     1   (0)| 00:00:01 |
|  63 |                TABLE ACCESS BY INDEX ROWID               | S_PROD_INT        |     1 |    58 |     1   (0)| 00:00:01 |
|* 64 |                 INDEX UNIQUE SCAN                        | S_PROD_INT_P1     |     1 |       |     1   (0)| 00:00:01 |
|  65 |               TABLE ACCESS BY INDEX ROWID                | S_ORDER_X         |     1 |    36 |     2   (0)| 00:00:01 |
|* 66 |                INDEX RANGE SCAN                          | S_ORDER_X_U1      |     1 |       |     2   (0)| 00:00:01 |
|  67 |              TABLE ACCESS BY INDEX ROWID                 | S_CONTACT         |     1 |    14 |     2   (0)| 00:00:01 |
|* 68 |               INDEX UNIQUE SCAN                          | S_CONTACT_P1      |     1 |       |     1   (0)| 00:00:01 |
|  69 |             TABLE ACCESS BY INDEX ROWID                  | S_POSTN           |     1 |    20 |     1   (0)| 00:00:01 |
|* 70 |              INDEX UNIQUE SCAN                           | S_POSTN_P1        |     1 |       |     1   (0)| 00:00:01 |
|  71 |            TABLE ACCESS BY INDEX ROWID                   | S_CONTACT         |     1 |    14 |     1   (0)| 00:00:01 |
|* 72 |             INDEX UNIQUE SCAN                            | S_CONTACT_P1      |     1 |       |     1   (0)| 00:00:01 |
|  73 |           TABLE ACCESS BY INDEX ROWID                    | S_ORG_EXT         |     1 |    14 |     2   (0)| 00:00:01 |
|* 74 |            INDEX UNIQUE SCAN                             | S_ORG_EXT_P1      |     1 |       |     1   (0)| 00:00:01 |
|  75 |          TABLE ACCESS BY INDEX ROWID                     | S_ORG_EXT         |     1 |    14 |     2   (0)| 00:00:01 |
|* 76 |           INDEX UNIQUE SCAN                              | S_ORG_EXT_P1      |     1 |       |     1   (0)| 00:00:01 |
|  77 |         TABLE ACCESS BY INDEX ROWID                      | S_ORG_EXT         |     1 |    17 |     2   (0)| 00:00:01 |
|* 78 |          INDEX UNIQUE SCAN                               | S_ORG_EXT_P1      |     1 |       |     1   (0)| 00:00:01 |
|  79 |        TABLE ACCESS BY INDEX ROWID                       | S_ORDER_ITEM      |     1 |    60 |     2   (0)| 00:00:01 |
|* 80 |         INDEX UNIQUE SCAN                                | S_ORDER_ITEM_P1   |     1 |       |     2   (0)| 00:00:01 |
|  81 |       TABLE ACCESS BY INDEX ROWID                        | S_ORDER_ITEM      |     1 |    24 |     2   (0)| 00:00:01 |
|* 82 |        INDEX UNIQUE SCAN                                 | S_ORDER_ITEM_P1   |     1 |       |     2   (0)| 00:00:01 |
|  83 |      TABLE ACCESS BY INDEX ROWID                         | S_PROD_INT        |     1 |    16 |     1   (0)| 00:00:01 |
|* 84 |       INDEX UNIQUE SCAN                                  | S_PROD_INT_P1     |     1 |       |     1   (0)| 00:00:01 |
|  85 |     TABLE ACCESS BY INDEX ROWID                          | S_PROD_INT        |     1 |    16 |     1   (0)| 00:00:01 |
|* 86 |      INDEX UNIQUE SCAN                                   | S_PROD_INT_P1     |     1 |       |     1   (0)| 00:00:01 |
|  87 |    TABLE ACCESS BY INDEX ROWID                           | S_ORG_EXT         |     1 |    14 |     2   (0)| 00:00:01 |
|* 88 |     INDEX UNIQUE SCAN                                    | S_ORG_EXT_P1      |     1 |       |     1   (0)| 00:00:01 |
|  89 |   TABLE ACCESS BY INDEX ROWID                            | S_ORG_EXT         |     1 |    17 |     2   (0)| 00:00:01 |
|* 90 |    INDEX UNIQUE SCAN                                     | S_ORG_EXT_P1      |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------------------

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

  32 - access("S_ORDER"."SHIP_ADDR_ID"="SHIPO"."ROW_ID"(+))
  34 - access("S_ORDER"."BL_ADDR_ID"="BILL"."ROW_ID"(+))
  36 - access("S_ORDER"."CAMP_CON_ID"="S_CAMP_CON"."ROW_ID"(+))
  38 - access("S_ORDER"."ENTLMNT_ID"="S_ENTLMNT"."ROW_ID"(+))
  40 - access("S_ORDER"."PROMO_ID"="S_SRC"."ROW_ID"(+))
  42 - access("S_CAMP_CON"."CAMP_LD_WAVE_ID"="S_CAMP_LD_WAVE"."ROW_ID"(+))
  44 - access("S_ORDER"."QUOTE_ID"="QUOTE"."ROW_ID"(+))
  46 - access("S_ORDER"."PR_POSTN_ID"="POSOWNERORG"."ROW_ID"(+))
  47 - filter("S_ORDER_ITEM"."ORDER_ID"="S_ORDER"."ROW_ID")
  50 - filter("S_ORDER_ITEM"."ROOT_ORDER_ITEM_ID" IS NOT NULL)
  51 - access("S_ORDER_ITEM"."ROW_ID"="S_ETL_I_IMG_25"."ROW_ID")
  53 - access("S_ORDER_ITEM"."VOL_DISCNT_ITEM_ID"="A"."ROW_ID"(+))
  55 - access("S_ORDER_ITEM"."VOL_DISCNT_ID"="S_VOL_DISCNT"."ROW_ID"(+))
  57 - access("S_ORDER_ITEM"."VOL_UPSELL_ITEM_ID"="B"."ROW_ID"(+))
  59 - access("S_ORDER_ITEM"."SHIP_ADDR_ID"="SHIPOITEM"."ROW_ID"(+))
  60 - filter("S_ORDER_ITEM"."ROW_ID"="OPRI"."PAR_ROW_ID"(+))

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
  62 - access("S_ORDER_ITEM"."SRC_INVLOC_ID"="S_INVLOC"."ROW_ID"(+))
  64 - access("S_ORDER_ITEM"."PROD_ID"="S_PROD_INT"."ROW_ID"(+))
  66 - access("S_ORDER_ITEM"."ORDER_ID"="S_ORDER_X"."PAR_ROW_ID"(+))
  68 - access("S_ORDER_ITEM"."CREATED_BY"="CRTD"."ROW_ID"(+))
  70 - access("CRTD"."PR_HELD_POSTN_ID"="POSCRTDORG"."ROW_ID"(+))
  72 - access("S_ORDER"."APPR_BY_EMP_ID"="APPRBYPOS"."ROW_ID"(+))
  74 - access("S_ORDER"."ACCNT_ID"="ACCNT"."ROW_ID"(+))
  76 - access("POSOWNERORG"."OU_ID"="OWNORG1"."ROW_ID"(+))
  78 - access("OWNORG1"."PAR_BU_ID"="OWNORG"."ROW_ID"(+))
  80 - access("S_ORDER_ITEM"."ROOT_ORDER_ITEM_ID"="ROOTOITEM"."ROW_ID")
  82 - access("S_ORDER_ITEM"."PAR_ORDER_ITEM_ID"="PAROITEM"."ROW_ID"(+))
  84 - access("PAROITEM"."PROD_ID"="PARPROD"."ROW_ID"(+))
  86 - access("ROOTOITEM"."PROD_ID"="ROOTPROD"."ROW_ID"(+))
  88 - access("S_ORDER"."BU_ID"="VISORG1"."ROW_ID"(+))
  90 - access("VISORG1"."PAR_BU_ID"="VISORG"."ROW_ID"(+))

132 rows selected.
Edited by: KODS on Dec 13, 2012 2:11 PM
  • 1. Re: Plan shows SQL execution time Hrs 999:59:59 Sec.
    KODS Newbie
    Currently Being Moderated
    Output of : select * from table(dbms_xplan.display_cursor('4nn6jbvwf0b2k', null, 'iostats last'));
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------------------------------------
    SQL_ID  4nn6jbvwf0b2k, child number 0
    -------------------------------------
             S_ORDER.APPR_BY_EMP_ID ,
             S_ORDER.CARRIER_CD ,
             S_ORDER.CURCY_CD ,
             S_ORDER.SHIP_ADDR_ID,
             S_ORDER.SHIP_METH_CD ,
             S_ORDER.TAX_EXEMPT_NUM ,
             S_ORDER.BU_IDID ,EASON ,
             S_ORDER.PRI_LST_ID ,
             S_ORDER.ENTLMNT_ID ENTITLEMENT_ID ,
             S_ORDER_ITEM.ROW_ID ,,
             S_ORDER_ITEM.PROD_ID,
             S_ORDER_ITEM.BASE_UNIT_PRI ,
    S_
    
    Plan hash value: 73463824
    
    -----------------------------------------------------------------------------------------------
    | Id  | Operation                                                | Name              | E-Rows |
    -----------------------------------------------------------------------------------------------
    |   1 |  NESTED LOOPS OUTER                                      |                   |   3170K|
    |   2 |   NESTED LOOPS OUTER                                     |                   |   3170K|
    |   3 |    NESTED LOOPS OUTER                                    |                   |   3170K|
    |   4 |     NESTED LOOPS OUTER                                   |                   |   3170K|
    |   5 |      NESTED LOOPS OUTER                                  |                   |   3170K|
    |   6 |       NESTED LOOPS                                       |                   |   3170K|
    |   7 |        NESTED LOOPS OUTER                                |                   |   3170K|
    |   8 |         NESTED LOOPS OUTER                               |                   |   3170K|
    |   9 |          NESTED LOOPS OUTER                              |                   |   3170K|
    |  10 |           NESTED LOOPS OUTER                             |                   |   3170K|
    |  11 |            NESTED LOOPS OUTER                            |                   |   3170K|
    |  12 |             NESTED LOOPS OUTER                           |                   |   3170K|
    |  13 |              NESTED LOOPS OUTER                          |                   |   3170K|
    |  14 |               NESTED LOOPS OUTER                         |                   |   3170K|
    |  15 |                NESTED LOOPS OUTER                        |                   |   3170K|
    |  16 |                 NESTED LOOPS OUTER                       |                   |   3170K|
    |  17 |                  NESTED LOOPS OUTER                      |                   |   3170K|
    |  18 |                   NESTED LOOPS OUTER                     |                   |   3170K|
    |  19 |                    NESTED LOOPS OUTER                    |                   |   3170K|
    |  20 |                     NESTED LOOPS OUTER                   |                   |   3170K|
    |  21 |                      NESTED LOOPS                        |                   |   3170K|
    |  22 |                       NESTED LOOPS OUTER                 |                   |   3108K|
    |  23 |                        NESTED LOOPS OUTER                |                   |   3108K|
    |  24 |                         NESTED LOOPS OUTER               |                   |   3108K|
    |  25 |                          NESTED LOOPS OUTER              |                   |   3108K|
    |  26 |                           NESTED LOOPS OUTER             |                   |   3108K|
    |  27 |                            NESTED LOOPS OUTER            |                   |   3108K|
    |  28 |                             NESTED LOOPS OUTER           |                   |   3108K|
    |  29 |                              NESTED LOOPS OUTER          |                   |   3108K|
    |  30 |                               TABLE ACCESS FULL          | S_ORDER           |   3108K|
    |  31 |                               TABLE ACCESS BY INDEX ROWID| S_ADDR_ORG        |      1 |
    |* 32 |                                INDEX UNIQUE SCAN         | S_ADDR_ORG_P1     |      1 |
    |  33 |                              TABLE ACCESS BY INDEX ROWID | S_ADDR_ORG        |      1 |
    |* 34 |                               INDEX UNIQUE SCAN          | S_ADDR_ORG_P1     |      1 |
    |  35 |                             TABLE ACCESS BY INDEX ROWID  | S_CAMP_CON        |      1 |
    |* 36 |                              INDEX UNIQUE SCAN           | S_CAMP_CON_P1     |      1 |
    |  37 |                            TABLE ACCESS BY INDEX ROWID   | S_ENTLMNT         |      1 |
    |* 38 |                             INDEX UNIQUE SCAN            | S_ENTLMNT_P1      |      1 |
    |  39 |                           TABLE ACCESS BY INDEX ROWID    | S_SRC             |      1 |
    |* 40 |                            INDEX UNIQUE SCAN             | S_SRC_P1          |      1 |
    |  41 |                          TABLE ACCESS BY INDEX ROWID     | S_CAMP_LD_WAVE    |      1 |
    |* 42 |                           INDEX UNIQUE SCAN              | S_CAMP_LD_WAVE_P1 |      1 |
    |  43 |                         TABLE ACCESS BY INDEX ROWID      | S_DOC_QUOTE       |      1 |
    |* 44 |                          INDEX UNIQUE SCAN               | S_DOC_QUOTE_P1    |      1 |
    |  45 |                        TABLE ACCESS BY INDEX ROWID       | S_POSTN           |      1 |
    |* 46 |                         INDEX UNIQUE SCAN                | S_POSTN_P1        |      1 |
    |* 47 |                       VIEW                               | V_ORDER_ITEM      |      1 |
    |  48 |                        NESTED LOOPS                      |                   |   3128K|
    |  49 |                         INDEX FAST FULL SCAN             | S_ETL_I_IMG_25_M2 |   3128K|
    |* 50 |                         TABLE ACCESS BY INDEX ROWID      | S_ORDER_ITEM      |      1 |
    |* 51 |                          INDEX UNIQUE SCAN               | S_ORDER_ITEM_P1   |      1 |
    |  52 |                      TABLE ACCESS BY INDEX ROWID         | S_VDISCNT_ITEM    |      1 |
    |* 53 |                       INDEX UNIQUE SCAN                  | S_VDISCNT_ITEM_P1 |      1 |
    |  54 |                     TABLE ACCESS BY INDEX ROWID          | S_VOL_DISCNT      |      1 |
    |* 55 |                      INDEX UNIQUE SCAN                   | S_VOL_DISCNT_P1   |      1 |
    |  56 |                    TABLE ACCESS BY INDEX ROWID           | S_VDISCNT_ITEM    |      1 |
    |* 57 |                     INDEX UNIQUE SCAN                    | S_VDISCNT_ITEM_P1 |      1 |
    |  58 |                   TABLE ACCESS BY INDEX ROWID            | S_ADDR_ORG        |      1 |
    |* 59 |                    INDEX UNIQUE SCAN                     | S_ADDR_ORG_P1     |      1 |
    |* 60 |                  TABLE ACCESS FULL                       | S_ORDER_ITM_PRI   |      1 |
    |  61 |                 TABLE ACCESS BY INDEX ROWID              | S_INVLOC          |      1 |
    |* 62 |                  INDEX UNIQUE SCAN                       | S_INVLOC_P1       |      1 |
    |  63 |                TABLE ACCESS BY INDEX ROWID               | S_PROD_INT        |      1 |
    |* 64 |                 INDEX UNIQUE SCAN                        | S_PROD_INT_P1     |      1 |
    |  65 |               TABLE ACCESS BY INDEX ROWID                | S_ORDER_X         |      1 |
    |* 66 |                INDEX RANGE SCAN                          | S_ORDER_X_U1      |      1 |
    |  67 |              TABLE ACCESS BY INDEX ROWID                 | S_CONTACT         |      1 |
    |* 68 |               INDEX UNIQUE SCAN                          | S_CONTACT_P1      |      1 |
    |  69 |             TABLE ACCESS BY INDEX ROWID                  | S_POSTN           |      1 |
    |* 70 |              INDEX UNIQUE SCAN                           | S_POSTN_P1        |      1 |
    |  71 |            TABLE ACCESS BY INDEX ROWID                   | S_CONTACT         |      1 |
    |* 72 |             INDEX UNIQUE SCAN                            | S_CONTACT_P1      |      1 |
    |  73 |           TABLE ACCESS BY INDEX ROWID                    | S_ORG_EXT         |      1 |
    |* 74 |            INDEX UNIQUE SCAN                             | S_ORG_EXT_P1      |      1 |
    |  75 |          TABLE ACCESS BY INDEX ROWID                     | S_ORG_EXT         |      1 |
    |* 76 |           INDEX UNIQUE SCAN                              | S_ORG_EXT_P1      |      1 |
    |  77 |         TABLE ACCESS BY INDEX ROWID                      | S_ORG_EXT         |      1 |
    |* 78 |          INDEX UNIQUE SCAN                               | S_ORG_EXT_P1      |      1 |
    |  79 |        TABLE ACCESS BY INDEX ROWID                       | S_ORDER_ITEM      |      1 |
    |* 80 |         INDEX UNIQUE SCAN                                | S_ORDER_ITEM_P1   |      1 |
    |  81 |       TABLE ACCESS BY INDEX ROWID                        | S_ORDER_ITEM      |      1 |
    |* 82 |        INDEX UNIQUE SCAN                                 | S_ORDER_ITEM_P1   |      1 |
    |  83 |      TABLE ACCESS BY INDEX ROWID                         | S_PROD_INT        |      1 |
    |* 84 |       INDEX UNIQUE SCAN                                  | S_PROD_INT_P1     |      1 |
    |  85 |     TABLE ACCESS BY INDEX ROWID                          | S_PROD_INT        |      1 |
    |* 86 |      INDEX UNIQUE SCAN                                   | S_PROD_INT_P1     |      1 |
    |  87 |    TABLE ACCESS BY INDEX ROWID                           | S_ORG_EXT         |      1 |
    |* 88 |     INDEX UNIQUE SCAN                                    | S_ORG_EXT_P1      |      1 |
    |  89 |   TABLE ACCESS BY INDEX ROWID                            | S_ORG_EXT         |      1 |
    |* 90 |    INDEX UNIQUE SCAN                                     | S_ORG_EXT_P1      |      1 |
    -----------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
      32 - access("S_ORDER"."SHIP_ADDR_ID"="SHIPO"."ROW_ID")
    
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------------------------------------
      34 - access("S_ORDER"."BL_ADDR_ID"="BILL"."ROW_ID")
      36 - access("S_ORDER"."CAMP_CON_ID"="S_CAMP_CON"."ROW_ID")
      38 - access("S_ORDER"."ENTLMNT_ID"="S_ENTLMNT"."ROW_ID")
      40 - access("S_ORDER"."PROMO_ID"="S_SRC"."ROW_ID")
      42 - access("S_CAMP_CON"."CAMP_LD_WAVE_ID"="S_CAMP_LD_WAVE"."ROW_ID")
      44 - access("S_ORDER"."QUOTE_ID"="QUOTE"."ROW_ID")
      46 - access("S_ORDER"."PR_POSTN_ID"="POSOWNERORG"."ROW_ID")
      47 - filter("S_ORDER_ITEM"."ORDER_ID"="S_ORDER"."ROW_ID")
      50 - filter("S_ORDER_ITEM"."ROOT_ORDER_ITEM_ID" IS NOT NULL)
      51 - access("S_ORDER_ITEM"."ROW_ID"="S_ETL_I_IMG_25"."ROW_ID")
      53 - access("S_ORDER_ITEM"."VOL_DISCNT_ITEM_ID"="A"."ROW_ID")
      55 - access("S_ORDER_ITEM"."VOL_DISCNT_ID"="S_VOL_DISCNT"."ROW_ID")
      57 - access("S_ORDER_ITEM"."VOL_UPSELL_ITEM_ID"="B"."ROW_ID")
      59 - access("S_ORDER_ITEM"."SHIP_ADDR_ID"="SHIPOITEM"."ROW_ID")
      60 - filter("S_ORDER_ITEM"."ROW_ID"="OPRI"."PAR_ROW_ID")
      62 - access("S_ORDER_ITEM"."SRC_INVLOC_ID"="S_INVLOC"."ROW_ID")
      64 - access("S_ORDER_ITEM"."PROD_ID"="S_PROD_INT"."ROW_ID")
      66 - access("S_ORDER_ITEM"."ORDER_ID"="S_ORDER_X"."PAR_ROW_ID")
      68 - access("S_ORDER_ITEM"."CREATED_BY"="CRTD"."ROW_ID")
      70 - access("CRTD"."PR_HELD_POSTN_ID"="POSCRTDORG"."ROW_ID")
      72 - access("S_ORDER"."APPR_BY_EMP_ID"="APPRBYPOS"."ROW_ID")
      74 - access("S_ORDER"."ACCNT_ID"="ACCNT"."ROW_ID")
      76 - access("POSOWNERORG"."OU_ID"="OWNORG1"."ROW_ID")
      78 - access("OWNORG1"."PAR_BU_ID"="OWNORG"."ROW_ID")
      80 - access("S_ORDER_ITEM"."ROOT_ORDER_ITEM_ID"="ROOTOITEM"."ROW_ID")
      82 - access("S_ORDER_ITEM"."PAR_ORDER_ITEM_ID"="PAROITEM"."ROW_ID")
      84 - access("PAROITEM"."PROD_ID"="PARPROD"."ROW_ID")
      86 - access("ROOTOITEM"."PROD_ID"="ROOTPROD"."ROW_ID")
      88 - access("S_ORDER"."BU_ID"="VISORG1"."ROW_ID")
      90 - access("VISORG1"."PAR_BU_ID"="VISORG"."ROW_ID")
    
    Note
    -----
       - Warning: basic plan statistics not available. These are only collected when:
           * hint 'gather_plan_statistics' is used for the statement or
           * parameter 'statistics_level' is set to 'ALL', at session or system level
    
    
    154 rows selected.
  • 2. Re: Plan shows SQL execution time Hrs 999:59:59 Sec.
    Nicosa Expert
    Currently Being Moderated
    Hi,
    KODS wrote:
    it is showing Hrs 999:59:59 Sec.
    This means the optimizer expects to query to take a looooooooooooooooooooooooooooooooong time. (could also be wrong).

    You might want to read this from the FAQ :
    {message:id=9360003}
    That would bring you there :
    {message:id=3292438}
  • 3. Re: Plan shows SQL execution time Hrs 999:59:59 Sec.
    Nikolay Savvinov Guru
    Currently Being Moderated
    Hi,

    1) it looks like your problem originates from this portion of the plan:
    |* 47 |                       VIEW                               | V_ORDER_ITEM      |     1 |  1616 |  1318K  (4)| 00:35:19 |
    |  48 |                        NESTED LOOPS                      |                   |  3128K|  1372M|  1318K  (4)| 00:35:19 |
    |  49 |                         INDEX FAST FULL SCAN             | S_ETL_I_IMG_25_M2 |  3128K|    32M|  2756  (11)| 00:00:05 |
    |* 50 |                         TABLE ACCESS BY INDEX ROWID      | S_ORDER_ITEM      |     1 |   449 |     2   (0)| 00:00:01 |
    |* 51 |                          INDEX UNIQUE SCAN               | S_ORDER_ITEM_P1   |     1 |       |     2   (0)| 00:00:01 |
    
    ...
      47 - filter("S_ORDER_ITEM"."ORDER_ID"="S_ORDER"."ROW_ID")
      50 - filter("S_ORDER_ITEM"."ROOT_ORDER_ITEM_ID" IS NOT NULL)
      51 - access("S_ORDER_ITEM"."ROW_ID"="S_ETL_I_IMG_25"."ROW_ID")
    ...
    where Oracle has to dig through 3M rows (doing an equivalent of 1.3M single-block reads) in order just to return 1 row. Tune V_ORDER_ITEM view most likely will be sufficient to resolve your entire problem. I don't have the view definition or any information about indexes on view's base tables , but it looks like the most restrictive condition here is S_ODER_ITEM.ORDER_ID = :1, and there is no index to take advantage on that. Try indexing this column, or better yet, post the view definition + index information and we'll try to suggest a better indexing strategy

    The view may also be underperforming because of the optimizer not being able to merge it or push any predicates inside it -- but once again, without having additional information (view DDL first of all) it's not possible to offer a solution.

    2) did you notice that the output of dbms_xplan.display_cursor did not populate A-time and A-rows columns? It's probably because you either didn't set STATISTICS_LEVEL = ALL or didn't wait for the query to complete. Either way, without these columns the output of dbms_xplay.display_cursor is not very useful

    Best regards,
    Nikolay
  • 4. Re: Plan shows SQL execution time Hrs 999:59:59 Sec.
    KODS Newbie
    Currently Being Moderated
    Dear Nikolay,

    The View(V_ORDER_ITEM) Metadata is :
      SELECT
     S_ORDER_ITEM.ROW_ID,S_ORDER_ITEM.CREATED,S_ORDER_ITEM.CREATED_BY,S_ORDER_ITEM.LAST_UPD,S_ORDER_ITEM.LAST_UPD_BY,S_ORDER_ITEM.MODIFICATION_NUM,S_ORDER_ITEM.CONFLICT_ID,S_ORDER_ITEM.ALW_PART_SHIP_FLG,S_ORDER_ITEM.AUTO_RECV_FLG,S_ORDER_ITEM.BILLABLE_FLG,S_ORDER_ITEM.COMPLMNTRY_FLG,
    S_ORDER_ITEM.CUSTOMIZED_FLG,S_ORDER_ITEM.DISCNT_SRC_CD,S_ORDER_ITEM.DISPLAY_FLG,S_ORDER_ITEM.EXCL_PRICING_FLG,S_ORDER_ITEM.HOLD_FLG,S_ORDER_ITEM.LATE_FLG,S_ORDER_ITEM.LN_NUM,S_ORDER_ITEM.LOANER_FLG,S_ORDER_ITEM.ORDER_ID,S_ORDER_ITEM.PROCESSED_FLG,S_ORDER_ITEM.PROMO_ITEM_FLG,S_ORDER_ITEM.ROLLUP_FLG,S_ORDER_ITEM.SHIP_COMPLETE_FLG,S_ORDER_ITEM.SHIP_TOGETHER_FLG,S_ORDER_ITEM.SINGLE_SRC_FLG,S_ORDER_ITEM.WRNTY_RCVR_FLG,S_ORDER_ITEM.SVC_CHG_INC_FLG,S_ORDER_ITEM.TAX_INC_FLG,S_ORDER_ITEM.WAIVED_FLG,S_ORDER_ITEM.ADDL_QTY_RCVD,S_ORDER_ITEM.ADJ_UNIT_PRI,S_ORDER_ITEM.AMT_DT,S_ORDER_ITEM.ATP_STATUS_DT,S_ORDER_ITEM.AVAIL_DT,S_ORDER_ITEM.AVAIL_QTY,S_ORDER_ITEM.BACK_ORDER_QTY,S_ORDER_ITEM.BASE_UNIT_PRI,S_ORDER_ITEM.BONUS_QTY,S_ORDER_ITEM.BONUS_THRESHOLD,S_ORDER_ITEM.CFG_VALDN_STAT_DT,S_ORDER_ITEM.COMPLETED_DT,S_ORDER_ITEM.COST_AMT,S_ORDER_ITEM.COST_EXCH_DT,S_ORDER_ITEM.DB_LAST_UPD,S_ORDER_ITEM.DISCNT_AMT,S_ORDER_ITEM.DISCNT_PERCENT,S_ORDER_ITEM.DISC_AMT_RC,S_ORDER_ITEM.DISC_PERCT_RC,S_ORDER_ITEM.EAI_SYNC_DT,S_ORDER_ITEM.EXTD_QTY,S_ORDER_ITEM.EXTENDED_QTY,S_ORDER_ITEM.EXTND_PRICE,S_ORDER_ITEM.EXTND_TAX,S_ORDER_ITEM.FRGHT_AMT,S_ORDER_ITEM.GROSS_VOL,S_ORDER_ITEM.GROSS_WGHT,S_ORDER_ITEM.LN_NUM2,S_ORDER_ITEM.MAX_PRICE,S_ORDER_ITEM.MIN_LOT,S_ORDER_ITEM.MUST_DLVR_BY_DT,S_ORDER_ITEM.NET_PRI,S_ORDER_ITEM.NUM_OCCURRENCE,S_ORDER_ITEM.ONETIME_CHG_SUBTOT,S_ORDER_ITEM.ORDER_ITM_EXCH_DT,S_ORDER_ITEM.PER_MTH_CHG_SUBTOT,S_ORDER_ITEM.PRI_ADJ_AMT,S_ORDER_ITEM.QTY_CANCELLED,S_ORDER_ITEM.QTY_INVCD,S_ORDER_ITEM.QTY_MAIN,S_ORDER_ITEM.QTY_MINOR,S_ORDER_ITEM.QTY_REQ,S_ORDER_ITEM.QTY_SHIPPED,S_ORDER_ITEM.QTY_TO_INVC,S_ORDER_ITEM.REQ_SHIP_DT,S_ORDER_ITEM.RESERVE_TM_INTRVL,S_ORDER_ITEM.ROLLUP_PRI,S_ORDER_ITEM.SERVICE_TS,S_ORDER_ITEM.SPRD_DISCNT_AMT,S_ORDER_ITEM.STATUS_CHANGED_FLG,S_ORDER_ITEM.STATUS_DT,S_ORDER_ITEM.SUBSCRBD_CRSE_NUM,S_ORDER_ITEM.SVC_PER_PROD_AMT,S_ORDER_ITEM.SVC_PER_PROD_PCT,S_ORDER_ITEM.TAX_AMT,S_ORDER_ITEM.TAX_EXEMPT_FLG,S_ORDER_ITEM.TAX_VAL1,S_ORDER_ITEM.TAX_VAL2,S_ORDER_ITEM.TAX_VAL3,S_ORDER_ITEM.TAX_VAL4,S_ORDER_ITEM.UNIT_PRI,S_ORDER_ITEM.UNIT_PRI_RC,S_ORDER_ITEM.UNIT_TAX_AMT,S_ORDER_ITEM.ACCNT_ORDER_NUM,S_ORDER_ITEM.ACTION_CD,S_ORDER_ITEM.ACT_PRDINT_ID,S_ORDER_ITEM.AGREE_ID,S_ORDER_ITEM.AMT_CURCY_CD,S_ORDER_ITEM.ASSET_ID,S_ORDER_ITEM.ASSET_INTEG_ID,S_ORDER_ITEM.ATP_MSG,S_ORDER_ITEM.ATP_STATUS_CD,S_ORDER_ITEM.AUC_ITEM_ID,S_ORDER_ITEM.BILL_ACCNT_ID,S_ORDER_ITEM.BILL_ADDR_ID,S_ORDER_ITEM.BK_PERIOD_ID,S_ORDER_ITEM.BL_STATUS_CD,S_ORDER_ITEM.BOOKING_ID,S_ORDER_ITEM.BO_ORDER_NUM,S_ORDER_ITEM.CARRIER_CD,S_ORDER_ITEM.CARRIER_PRIO_CD,S_ORDER_ITEM.CFG1_INTEG_ID,S_ORDER_ITEM.CFG2_INTEG_ID,S_ORDER_ITEM.CFG3_INTEG_ID,S_ORDER_ITEM.CFG_STATE_CD,S_ORDER_ITEM.CFG_TYPE_CD,S_ORDER_ITEM.CFG_VALDN_STAT_CD,S_ORDER_ITEM.CMPNS_STATUS_CD,S_ORDER_ITEM.COMMIT_TYPE_CD,S_ORDER_ITEM.CONTACT_ID,S_ORDER_ITEM.COST_CURCY_CD,S_ORDER_ITEM.CRSE_OFFR_ID,S_ORDER_ITEM.CRSE_REG_ID,S_ORDER_ITEM.CVRD_ASSET_ID,S_ORDER_ITEM.DB_LAST_UPD_SRC,S_ORDER_ITEM.DESC_TEXT,S_ORDER_ITEM.DEST_INVLOC_ID,S_ORDER_ITEM.DISCNT_METH_CD,S_ORDER_ITEM.DISPLAY_NAME,S_ORDER_ITEM.DLVRY_STATUS_CD,S_ORDER_ITEM.EAI_EXPRT_STAT_CD,S_ORDER_ITEM.EDI_LN_NUM,S_ORDER_ITEM.EFF_PRI_PERIOD_ID,S_ORDER_ITEM.ELIG_REASON,S_ORDER_ITEM.ELIG_STATUS_CD,S_ORDER_ITEM.ENTLMNT_ID,S_ORDER_ITEM.EXT_FULFL_LOC_CD,S_ORDER_ITEM.FRGHT_TERMS_CD,S_ORDER_ITEM.FUNCTION_ID,S_ORDER_ITEM.HOLD_REASON_CD,S_ORDER_ITEM.INTEGRATION_ID,S_ORDER_ITEM.ITEM_GROUP_NAME,S_ORDER_ITEM.LINE_NOTE,S_ORDER_ITEM.LN_TYPE_CD,S_ORDER_ITEM.ORDER_ITM_CURCY_CD,S_ORDER_ITEM.ORG_PROD_ID
    ,S_ORDER_ITEM.ORG_REF_ORD_ITM_ID,S_ORDER_ITEM.PAR_ORDER_ITEM_ID,S_ORDER_ITEM.PORT_VALID_PROD_ID,S_ORDER_ITEM.PREV_ITEM_REV_ID,S_ORDER_ITEM.PRICING_COMMENT,S_ORDER_ITEM.PRI_LST_ID,S_ORDER_ITEM.PRI_METH_CD,S_ORDER_ITEM.PROD_ID,S_ORDER_ITEM.PROD_NAME,S_ORDER_ITEM.PROD_OPT1_VAL_CD,S_ORDER_ITEM.PROD_OPT2_VAL_CD,S_ORDER_ITEM.PROD_PORT_ID,S_ORDER_ITEM.PROD_STATUS_CD,S_ORDER_ITEM.PROD_SUB_CAT_ID,S_ORDER_ITEM.PROMOTION_ID,S_ORDER_ITEM.PROMO_ID,S_ORDER_ITEM.PROM_INTEG_ID,S_ORDER_ITEM.PROM_ITEM_ID,S_ORDER_ITEM.PROM_SRC_INTG_ID,S_ORDER_ITEM.QUOTE_ITEM_ID,S_ORDER_ITEM.QUOTE_SOLN_ID,S_ORDER_ITEM.RESERVE_STATUS_CD,S_ORDER_ITEM.REVENUE_TYPE_CD,S_ORDER_ITEM.ROOT_ORDER_ITEM_ID,S_ORDER_ITEM.RSRV_TM_INTRVL_CD,S_ORDER_ITEM.RTRN_ADDR_ID,S_ORDER_ITEM.RTRN_CON_ID,S_ORDER_ITEM.RTRN_OU_ID,S_ORDER_ITEM.SERVICE_NUM,S_ORDER_ITEM.SERV_ACCNT_ID,S_ORDER_ITEM.SHIP_ADDR_ID,S_ORDER_ITEM.SHIP_CON_ID,S_ORDER_ITEM.SHIP_INSTRUCTIONS,S_ORDER_ITEM.SHIP_METH_CD,S_ORDER_ITEM.SHIP_OU_ID,S_ORDER_ITEM.SHIP_PER_ADDR_ID,S_ORDER_ITEM.SP_NUM,S_ORDER_ITEM.SRC_INVLOC_ID,S_ORDER_ITEM.STATUS_CD,S_ORDER_ITEM.SVCD_ORDER_ITEM_ID,S_ORDER_ITEM.SVCD_PROD_ID,S_ORDER_ITEM.TAX_EXEMPT_NUM,S_ORDER_ITEM.TAX_EXEMPT_REASON,S_ORDER_ITEM.TIER_PRI_INFO,S_ORDER_ITEM.USAGE_BL_PLAN_ID,S_ORDER_ITEM.VOL_DISCNT_ID,S_ORDER_ITEM.VOL_DISCNT_ITEM_ID,S_ORDER_ITEM.VOL_UOM_CD,S_ORDER_ITEM.VOL_UPSELL_ITEM_ID,S_ORDER_ITEM.VOL_UPSELL_MSG_TXT,S_ORDER_ITEM.WGHT_UOM_CD,S_ORDER_ITEM.X_BACKEND_SERVICE_ID,S_ORDER_ITEM.X_CONCESSIONAL_GRP,S_ORDER_ITEM.X_EAI_ORDER_NUM,S_ORDER_ITEM.X_IN_STATE,S_ORDER_ITEM.X_IN_TYPE,S_ORDER_ITEM.X_LL_CIRCLE,S_ORDER_ITEM.X_LL_SSA,S_ORDER_ITEM.X_LL_ZONE,S_ORDER_ITEM.X_NOMINEE_NAME,S_ORDER_ITEM.X_PHONE_NUMBER,S_ORDER_ITEM.X_RG_DESC,S_ORDER_ITEM.X_PC_DESC,S_ORDER_ITEM.X_PC_EMI,S_ORDER_ITEM.X_PC_INSTMNTS,S_ORDER_ITEM.X_PC_MODEL,S_ORDER_ITEM.X_PC_UPFRONT
    FROM
            siebel.S_ORDER_ITEM,
            siebel.S_ETL_I_IMG_25
    WHERE
            S_ORDER_ITEM.ROW_ID = S_ETL_I_IMG_25.ROW_ID;
    Explain plan output of above view:
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------
    Plan hash value: 903353379
    
    --------------------------------------------------------------------------------------------------
    | Id  | Operation                    | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |                   |  3128K|  1372M|  1319K  (4)| 00:35:19 |
    |   1 |  NESTED LOOPS                |                   |  3128K|  1372M|  1319K  (4)| 00:35:19 |
    |   2 |   INDEX FAST FULL SCAN       | S_ETL_I_IMG_25_M2 |  3128K|    32M|  2756  (11)| 00:00:05 |
    |   3 |   TABLE ACCESS BY INDEX ROWID| S_ORDER_ITEM      |     1 |   449 |     2   (0)| 00:00:01 |
    |*  4 |    INDEX UNIQUE SCAN         | S_ORDER_ITEM_P1   |     1 |       |     2   (0)| 00:00:01 |
    --------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       4 - access("S_ORDER_ITEM"."ROW_ID"="S_ETL_I_IMG_25"."ROW_ID")
    
    16 rows selected.
    The ROW_ID column is indexed in both the tables.




    In the mail query the below two columns are not indexed.
    S_ORDER_ITEM.CREATED_BY = CRTD.ROW_ID(+) -- CREATED_BY is not indexed.
    S_ORDER.PR_POSTN_ID = POSOWNERORG.ROW_ID(+) -- PR_POSTN_ID is not indexed.


    As you said i set the STATISTICS_LEVEL=ALL and i posted the output of dbms_xplan.display_cursor. But thie is not giving the A-time and A-row columns.


    Kindly help me and let me know if i miss any required information.

    Thanks
    Kods
  • 5. Re: Plan shows SQL execution time Hrs 999:59:59 Sec.
    Nikolay Savvinov Guru
    Currently Being Moderated
    Hi,

    what about the plan for
    SELECT * FROM V_ORDER_ITEM WHERE ROW_ID = :ROW_ID
    ?

    If it's the same, then this means that the optimizer cannot merge the view (actually, it can't even push the predicate down). Nothing in the view looks like it could've done it unmergeable (like use of rownum etc), so it must be either lack of MERGE VIEW privilege or some optimizer bug.

    Try GRANT MERGE VIEW ON V_ORDER_ITEM to <the_user_who_is_running_the_query> or run the query under the view owner's account and see what happens.

    Best regards,
    Nikolay
  • 6. Re: Plan shows SQL execution time Hrs 999:59:59 Sec.
    Boneist Guru
    Currently Being Moderated
    With all the estimated rows of 1 showing in your plan, I would guess you have a problem with missing or out of date statistics - have you tried checking that the stats are up-to-date and if not, regathering them?
  • 7. Re: Plan shows SQL execution time Hrs 999:59:59 Sec.
    Nikolay Savvinov Guru
    Currently Being Moderated
    Hi,
    Boneist wrote:
    With all the estimated rows of 1 showing in your plan, I would guess you have a problem with missing or out of date statistics - have you tried checking that the stats are up-to-date and if not, regathering them?
    1) there is more than one operation in the plan that shows much more than 1 estimated row
    2) stale statistics can lead to optimizer thinking there are e.g. 3M rows while in reality there are 5M, but it's very unlikely that it will lead the optimizer to believe that there is exactly 1 row in a table where there are actually many more; much more often, cardinality 1 is the result of incorrectly assessed selectivity (e.g. there are 5 predicates which the optimizer believes to be independent, but in fact they are correlated); another common scenario is when tables are used as temporary containers, i.e. they only contain rows only for duration of certain operations which are limited in time; in both cases gathering stats doesn't help
    3) there might or might not be a problem with stats, but there is certainly a big problem with a view that cannot be merged
    (and is responsible for 99% of the cost of the query)


    Best regards,
    Nikolay
  • 8. Re: Plan shows SQL execution time Hrs 999:59:59 Sec.
    Nicosa Expert
    Currently Being Moderated
    Hi,

    I strongly disagree with your #2.
    Stale statistics are very likely to have the optimizer compute an estimate of 1 row.

    The basic example is a column that stores date. each inserts has the sysdate of its insertion, then you stop gathering statistics.
    When your query with predicate column=sysdate comes in, the optimizer uses prorated density. If your stats are 1 week old, then sysdate seems out of range of the min/max value on the column.
    The "farther" the min/max value is from sysdate, the closer to 0 is estimated the selectivity (I've seen pretty small selectivity, around 10^-24).
    But the optimizer doesn't show such numbers (<1), it rounds it to 1 (and hence might prefer the nested loop, where actually the whole table has column=sysdate)
  • 9. Re: Plan shows SQL execution time Hrs 999:59:59 Sec.
    Nikolay Savvinov Guru
    Currently Being Moderated
    Hi
    Nicosa wrote:
    Hi,

    I strongly disagree with your #2.
    Stale statistics are very likely to have the optimizer compute an estimate of 1 row.

    The basic example is a column that stores date. each inserts has the sysdate of its insertion, then you stop gathering statistics.
    When your query with predicate column=sysdate comes in, the optimizer uses prorated density. If your stats are 1 week old, then sysdate seems out of range of the min/max value on the column.
    The "farther" the min/max value is from sysdate, the closer to 0 is estimated the selectivity (I've seen pretty small selectivity, around 10^-24).
    But the optimizer doesn't show such numbers (<1), it rounds it to 1 (and hence might prefer the nested loop, where actually the whole table has column=sysdate)
    you have a point here -- but look at the predicate section of the plan, do you see anything that would even remotely resemble your scenario? There is a bunch of join predicates on various ID columns, and just one filtering condition:
    ("S_ORDER_ITEM"."ROOT_ORDER_ITEM_ID" IS NOT NULL)
    so I don't think this is something we need to worry about here.

    Best regards,
    Nikolay
  • 10. Re: Plan shows SQL execution time Hrs 999:59:59 Sec.
    Boneist Guru
    Currently Being Moderated
    Nikolay Savvinov wrote:
    so I don't think this is something we need to worry about here.
    Stats are one of the main drivers behind the Optimizer's decision making. Checking that stats are gathered/up-to-date would be the first thing I did, since missing or out-of-date stats can cause the plans to be drastically incorrect. It's an easy step to do in the diagnostic process, so why wouldn't you check as a matter of course?
  • 11. Re: Plan shows SQL execution time Hrs 999:59:59 Sec.
    Martin Preiss Expert
    Currently Being Moderated
    Hi Kods,

    like Nikolay I think that the strange NL in step 21 is the root of all evil in your query: joining a table of 3M rows (S_ORDER) with the (non mergeable and expensive) view V_ORDER_ITEM in a NESTED LOOPS is a bad idea - and the CBO knows it and shows extreme cost (4100G) and time estimate (999:59:59). So I guess: he has no choice. Alberto Dell'Era mentioned in his blog (http://www.adellera.it/blog/2009/06/30/tuning-oracle-for-siebel-sql-template/): "In fact, Siebel blocks HASH JOINS (_hash_join_enabled=false) and SORT MERGE JOINS (_optimizer_sortmerge_join_enabled=false), which leaves NESTED LOOPS as the only surviving join method." I don't know if Siebel = Siebel (i.e. are his observations relevant for your installation) but it would at least explain the use of a NL join (where I would expect a HASH JOIN for data sets of some size).

    Regards

    Martin
  • 12. Re: Plan shows SQL execution time Hrs 999:59:59 Sec.
    Nikolay Savvinov Guru
    Currently Being Moderated
    Hi,
    Boneist wrote:
    Nikolay Savvinov wrote:
    so I don't think this is something we need to worry about here.
    Stats are one of the main drivers behind the Optimizer's decision making. Checking that stats are gathered/up-to-date would be the first thing I did, since missing or out-of-date stats can cause the plans to be drastically incorrect. It's an easy step to do in the diagnostic process, so why wouldn't you check as a matter of course?
    A very good question.

    First of all, contrary to popular belief, missing stats rarely poses a problem -- at least for recent versions of Oracle. In older versions, yes, optimizer tended to resort to built-in defaults when not being able to find table stats, but starting with 9i or 10g, the default behavior is to use dynamic sampling. So actually missing stats is more likely to lead to more, rather than less, accurate estimates.

    Second of all, how would you check whether stats are stale? It's easy to check last_analyzed_date, but what does it tell you? Suppose one of the tables was last analyzed 2 years ago -- so what if its size hasn't changed since? OK, that means that what needs to be checked is not the date when stats were collected, but rather the accuracy of stats, and first of all row counts, NDV, and min/max values. For a moderately complex query it would take several hours to check all this, or even more, especially if there are some big tables involved.

    Third of all, suppose that you do find a problem with statistics. What do you do next? Gather stats? OK, suppose you do that, and you use no_invalidate=>false to purge bad plans from library cache, and your problem goes away. Can you be sure that your solution is permanent? No, of course not. It's quite possible that the original problem was due to bind peeking, and it went away because you re-optimized the plan with a more realistic bind, and not because new stats are 10% closer to the truth about the number of rows in the table. Or because the histogram that was collected last time on a skewed column, wasn't collected now. Or one of other reasons.

    While with systematic plan troubleshooting it rarely takes than a few minutes to get to the bottom of the problem, and the solution is almost always very reliable.

    Best regards,
    Nikolay
  • 13. Re: Plan shows SQL execution time Hrs 999:59:59 Sec.
    Nikolay Savvinov Guru
    Currently Being Moderated
    Hi Martin,

    very good point about Siebel (I'm not familiar with it so I wouldn't know anyway) but in this case the problem is not NL per se. I mean, the VIEW operation only returns 1 row, so why not go for NL? Rather, the problem the fact that the optimizer cannot either merge the view or at least push down the predicate on ROW_ID. I vaguely recall having seen a similar problem a couple of years back, when the optimizer chose a plan it knew was horrible because of some limitations (like use of WITH clause) that made it do OJPPD (10053 trace file lingo for "old join predicate pushdown") instead of normal cost-based strategy. In this case I'm not sure what would have caused this -- maybe it's missing MERGE VIEW privilege (although it shouldn't affect predicate pushing, or should it) or maybe it's all the outer joins, or maybe the optimizer decides to switch to heuristic algorithms because of the sheer number of joins involved, maybe something else.

    Best regards,
    Nikolay
  • 14. Re: Plan shows SQL execution time Hrs 999:59:59 Sec.
    Nicosa Expert
    Currently Being Moderated
    Nikolay Savvinov wrote:
    you have a point here -- but look at the predicate section of the plan, do you see anything that would even remotely resemble your scenario?
    Hi Nikolay,

    To be honest, I didn't even look at the plan/query.
    I was just reacting at your #2 in a general case (but maybe you were not speaking "in general"...)
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points