Home » RDBMS Server » Performance Tuning » Select statement taking Long time to execute (Oracle 10g)
Select statement taking Long time to execute [message #637696] Thu, 21 May 2015 14:35 Go to next message
P1Oracle
Messages: 60
Registered: August 2014
Location: Hyderabad
Member
Hi ,
How can i debug the select statement which is taking some couple of hours to complete,what are the possible scenarios to find where it is taking long time...
Re: Select statement taking Long time to execute [message #637697 is a reply to message #637696] Thu, 21 May 2015 14:36 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/

Do all tables & indexes involved with this query have current statistics?

Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/mv/msg/84315/433888/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof

[Updated on: Thu, 21 May 2015 14:38]

Report message to a moderator

Re: Select statement taking Long time to execute [message #637698 is a reply to message #637697] Thu, 21 May 2015 16:34 Go to previous messageGo to next message
P1Oracle
Messages: 60
Registered: August 2014
Location: Hyderabad
Member
Hi,
EXPLAIN PLAN FOR
SELECT L.SHIP_FROM_ORG_ID ORG_ID,
      org.ORGANIZATION_CODE,
      rcta.CUSTOMER_TRX_ID,
      rctla.CUSTOMER_TRX_LINE_ID,
      rctla.PREVIOUS_CUSTOMER_TRX_LINE_ID,
      rcta.TRX_NUMBER,
      rcta.TRX_DATE,
      rctla.CREATION_DATE,
      hca.ACCOUNT_NUMBER,
      hca.ORIG_SYSTEM_REFERENCE,
      hpty.PARTY_NAME CUST_NAME,
      hcasa.CUSTOMER_CATEGORY_CODE PRICE_LEVEL,
      H.HEADER_ID,
      L.LINE_ID,
      H.ORDER_NUMBER,
      H.CUST_PO_NUMBER,
      L.LINE_NUMBER
      ||'.'
      || L.SHIPMENT_NUMBER LINE_NUMBER,
      L.INVENTORY_ITEM_ID,
      L.SHIP_FROM_ORG_ID,
      L.ORDERED_ITEM ITEM_NUMBER,
      L.ITEM_TYPE_CODE,   
      L.SOURCE_TYPE_CODE, 
      MSIB.DESCRIPTION,
      NVL(rctla.QUANTITY_INVOICED, rctla.QUANTITY_CREDITED) QUANTITY_INVOICED,
      rctla.UOM_CODE,
      L.FULFILLMENT_DATE,
      NVL(L.ACTUAL_SHIPMENT_DATE,L.FULFILLMENT_DATE) ACTUAL_SHIPMENT_DATE,
      to_number(REPLACE(L.ATTRIBUTE1,',','')) MSRP,
      NVL(rctla.UNIT_SELLING_PRICE,0) UNIT_SELLING_PRICE,
      rctla.EXTENDED_AMOUNT,
      --L.UNIT_COST,                  
      COALESCE(to_number(l.ATTRIBUTE5), 
      (SELECT DISTINCT ITEM_COST
      FROM CST_ITEM_COSTS cst                         
      WHERE cst.inventory_item_id=l.inventory_item_id 
      AND cst.organization_id    =l.ship_from_org_id  
      AND L.ITEM_TYPE_CODE       ='KIT'               
      AND cst.cost_type_id       =3
      ),0) UNIT_COST,                          
      rcta.INVOICE_CURRENCY_CODE,              
      NVL(rcta.EXCHANGE_RATE,1) EXCHANGE_RATE, 
      NVL(L.ATTRIBUTE7,1) SALES_RATE,          
      trunc(l.creation_date) LINE_CREATION_DATE,
      l.PRICING_DATE,
      rcta.ORG_ID OU_ID,                       
      hou.NAME OU_NAME,                        
      L.LINE_CATEGORY_CODE,
      L.INVOICE_TO_ORG_ID,
      NVL(L.SHIP_TO_ORG_ID,H.SHIP_TO_ORG_ID) SHIP_TO_ORG_ID, 
      L.PRICE_LIST_ID,
      rcta.BILL_TO_SITE_USE_ID,
      hzloc.ADDRESS2 BILL_TO_ADDR1,
      hzloc.ADDRESS3 BILL_TO_ADDR2,
      hzloc.CITY BILL_TO_CITY,
      --hzloc.STATE    BILL_TO_STATE,      
      NVL(hzloc.STATE, hzloc.PROVINCE) BILL_TO_STATE, 
      hzloc.POSTAL_CODE BILL_TO_POSTAL_CODE,
      hzloc.COUNTRY BILL_TO_COUNTRY,
      rcta.SHIP_TO_SITE_USE_ID,
      hzloc2.ADDRESS1 SHIP_TO_NAME,
      hzloc2.ADDRESS2 SHIP_TO_ADDR1,
      hzloc2.ADDRESS3 SHIP_TO_ADDR2,
      hzloc2.CITY SHIP_TO_CITY,
      --hzloc2.STATE     SHIP_TO_STATE ,        
      NVL(hzloc2.STATE, hzloc2.PROVINCE) SHIP_TO_STATE , 
      hzloc2.POSTAL_CODE SHIP_TO_POSTAL_CODE,
      hzloc2.COUNTRY SHIP_TO_COUNTRY,
      H.END_CUSTOMER_SITE_USE_ID,
      hzloc3.ADDRESS1 END_CUST_NAME,
      hzloc3.ADDRESS2 END_CUST_ADDR1,
      hzloc3.ADDRESS3 END_CUST_ADDR2,
      hzloc3.CITY END_CUST_CITY,
      --hzloc3.STATE     END_CUST_STATE ,         
      NVL(hzloc3.STATE, hzloc3.PROVINCE) END_CUST_STATE , 
      hzloc3.POSTAL_CODE END_CUST_POSTAL_CODE,
      hzloc3.COUNTRY END_CUST_COUNTRY,
      rcta.PRIMARY_SALESREP_ID,
      srep.NAME SALESREP_NAME,
      rctta.NAME TRAN_TYPE_NAME,
      rctta.TYPE TRAN_TYPE,
      hcsua.TERRITORY_ID,           
      terr.SEGMENT1 TERRITORY_NAME, 
      H.FREIGHT_TERMS_CODE,         
      FLV.MEANING FREIGHT_TERMS,    -- V2.1 --
      H.SHIPPING_METHOD_CODE,       -- V2.1 --
      WCS.SHIP_METHOD_MEANING       -- V2.1 --
      ,
      ptrm.NAME TERM_NAME -- V3.0 --
      ,
      rcta.TERM_ID -- V3.0 --
    FROM APPS.OE_ORDER_LINES_ALL L ,
      APPS.OE_ORDER_HEADERS_ALL H ,
      APPS.MTL_SYSTEM_ITEMS_B MSIB ,
      APPS.RA_CUSTOMER_TRX_LINES_ALL rctla ,
      APPS.RA_CUSTOMER_TRX_ALL rcta ,
      APPS.RA_CUST_TRX_TYPES_ALL rctta ,
      APPS.HZ_CUST_ACCOUNTS hca ,
      APPS.HZ_PARTIES hpty ,
      APPS.HZ_CUST_SITE_USES_ALL hcsua ,
      APPS.HZ_CUST_ACCT_SITES_ALL hcasa ,
      APPS.HZ_LOCATIONS hzloc ,
      APPS.HZ_PARTY_SITES hzpsi ,
      APPS.HZ_CUST_SITE_USES_ALL hcsua2 ,
      APPS.HZ_CUST_ACCT_SITES_ALL hcasa2 ,
      APPS.HZ_LOCATIONS hzloc2 ,
      APPS.HZ_PARTY_SITES hzpsi2 ,
      APPS.HZ_CUST_SITE_USES_ALL hcsua3 ,
      APPS.HZ_CUST_ACCT_SITES_ALL hcasa3 ,
      APPS.HZ_LOCATIONS hzloc3 ,
      APPS.HZ_PARTY_SITES hzpsi3 ,
      APPS.RA_TERRITORIES terr -- V1.4
      ,
      AR.RA_TERMS_TL ptrm -- V3.0 --
      ,
      APPS.jtf_rs_salesreps srep ,
      APPS.org_organization_definitions org ,
      HR.HR_ALL_ORGANIZATION_UNITS hou -- V2.0 --
      ,
      APPS.FND_LOOKUP_VALUES_VL FLV -- V2.1 --
      ,
      WSH.WSH_CARRIER_SERVICES WCS -- V2.1 --
    WHERE L.HEADER_ID       = H.HEADER_ID
    AND L.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
    AND L.SHIP_FROM_ORG_ID  = MSIB.ORGANIZATION_ID
    AND L.SHIP_FROM_ORG_ID  = ORG.ORGANIZATION_ID
      --and to_char(l.LINE_ID) = rctla.INTERFACE_LINE_ATTRIBUTE6      -- V2.8 --
    AND to_number(rctla.INTERFACE_LINE_ATTRIBUTE6) = l.LINE_ID -- V2.8 --
    AND rctla.CUSTOMER_TRX_ID                      = rcta.CUSTOMER_TRX_ID
    AND rcta.BILL_TO_CUSTOMER_ID                   = hca.CUST_ACCOUNT_ID
    AND rcta.CUST_TRX_TYPE_ID                      = rctta.CUST_TRX_TYPE_ID
    AND hca.PARTY_ID                               = hpty.PARTY_ID
    AND rcta.BILL_TO_SITE_USE_ID                   = hcsua.SITE_USE_ID
    AND hcsua.cust_acct_site_id                    = hcasa.cust_acct_site_id
    AND hcsua.TERRITORY_ID                         = terr.TERRITORY_ID(+) 
    AND hcasa.PARTY_SITE_ID                        = hzpsi.PARTY_SITE_ID
    AND hzpsi.location_id                          = hzloc.location_id
    AND rcta.SHIP_TO_SITE_USE_ID                   = hcsua2.SITE_USE_ID
    AND hcsua2.cust_acct_site_id                   = hcasa2.cust_acct_site_id
    AND hcasa2.PARTY_SITE_ID                       = hzpsi2.PARTY_SITE_ID
    AND hzpsi2.location_id                         = hzloc2.location_id
    AND H.END_CUSTOMER_SITE_USE_ID                 = hcsua3.SITE_USE_ID(+)
    AND hcsua3.cust_acct_site_id                   = hcasa3.cust_acct_site_id(+)
    AND hcasa3.PARTY_SITE_ID                       = hzpsi3.PARTY_SITE_ID(+)
    AND hzpsi3.location_id                         = hzloc3.location_id(+)
    AND rcta.PRIMARY_SALESREP_ID                   = srep.SALESREP_ID(+)
    AND rcta.ORG_ID                                = hou.ORGANIZATION_ID     
    AND rcta.TERM_ID                               = ptrm.TERM_ID(+)         
    AND 'US'                                       = ptrm.LANGUAGE(+)        
    AND FLV.LOOKUP_TYPE(+)                         = 'FREIGHT_TERMS'        
    AND H.FREIGHT_TERMS_CODE                       = FLV.LOOKUP_CODE(+)      
    AND H.SHIPPING_METHOD_CODE                     = WCS.SHIP_METHOD_CODE(+) 
    AND rcta.ORG_ID                                = srep.ORG_ID(+)
    AND rctla.WH_UPDATE_DATE IS NULL
/



When i ran SELECT * FROM table(dbms_xplan.display);
it is showing 130 rows selected.
Re: Select statement taking Long time to execute [message #637699 is a reply to message #637698] Thu, 21 May 2015 16:42 Go to previous messageGo to next message
P1Oracle
Messages: 60
Registered: August 2014
Location: Hyderabad
Member
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2255527393

----------------------------------------------------------------------------------------------------
-----------------------------------------------

| Id  | Operation                                                | Name                           |
Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------------------
-----------------------------------------------

|   0 | SELECT STATEMENT                                         |                                |
98716 |   116M|       | 17345   (1)| 00:03:29 |

|*  1 |  FILTER                                                  |                                |
      |       |       |            |          |

|   2 |   TABLE ACCESS BY INDEX ROWID                            | CST_ITEM_COSTS                 |
    1 |    17 |       |     2   (0)| 00:00:01 |

|*  3 |    INDEX UNIQUE SCAN                                     | CST_ITEM_COSTS_U1              |
    1 |       |       |     1   (0)| 00:00:01 |

|*  4 |  HASH JOIN RIGHT OUTER                                   |                                |
98716 |   116M|       | 17345   (1)| 00:03:29 |

|   5 |   TABLE ACCESS FULL                                      | HZ_LOCATIONS                   |
 8598 |   403K|       |    31   (4)| 00:00:01 |

|*  6 |   HASH JOIN RIGHT OUTER                                  |                                |
98716 |   112M|       | 17313   (1)| 00:03:28 |

|   7 |    TABLE ACCESS FULL                                     | HZ_PARTY_SITES                 |
15927 |   155K|       |    31   (0)| 00:00:01 |

|*  8 |    HASH JOIN RIGHT OUTER                                 |                                |
98716 |   111M|       | 17281   (1)| 00:03:28 |

|   9 |     TABLE ACCESS FULL                                    | HZ_CUST_ACCT_SITES_ALL         |
 1893 | 18930 |       |     8   (0)| 00:00:01 |

|* 10 |     HASH JOIN                                            |                                |
98716 |   110M|       | 17272   (1)| 00:03:28 |

|  11 |      TABLE ACCESS FULL                                   | HZ_LOCATIONS                   |
 8598 |   260K|       |    31   (4)| 00:00:01 |

|* 12 |      HASH JOIN                                           |                                |
98716 |   107M|       | 17240   (1)| 00:03:27 |

|  13 |       TABLE ACCESS FULL                                  | HZ_PARTY_SITES                 |
15927 |   155K|       |    31   (0)| 00:00:01 |

|* 14 |       HASH JOIN                                          |                                |
98716 |   106M|       | 17208   (1)| 00:03:27 |

|  15 |        TABLE ACCESS FULL                                 | HZ_CUST_ACCT_SITES_ALL         |
 1893 | 51111 |       |     8   (0)| 00:00:01 |

|* 16 |        HASH JOIN                                         |                                |
98716 |   103M|       | 17198   (1)| 00:03:27 |

|  17 |         TABLE ACCESS FULL                                | HZ_LOCATIONS                   |
 8598 |   403K|       |    31   (4)| 00:00:01 |

|* 18 |         HASH JOIN                                        |                                |
98716 |    99M|       | 17167   (1)| 00:03:27 |

|  19 |          TABLE ACCESS FULL                               | HZ_PARTY_SITES                 |
15927 |   155K|       |    31   (0)| 00:00:01 |

|* 20 |          HASH JOIN                                       |                                |
98716 |    98M|       | 17134   (1)| 00:03:26 |

|  21 |           TABLE ACCESS FULL                              | HZ_CUST_ACCT_SITES_ALL         |
 1893 | 18930 |       |     8   (0)| 00:00:01 |

|* 22 |           HASH JOIN                                      |                                |
98716 |    97M|       | 17125   (1)| 00:03:26 |

|  23 |            TABLE ACCESS FULL                             | HZ_PARTIES                     |
40250 |   943K|       |   162   (1)| 00:00:02 |

|* 24 |            HASH JOIN RIGHT OUTER                         |                                |
98716 |    95M|       | 16963   (1)| 00:03:24 |

|  25 |             TABLE ACCESS FULL                            | RA_TERRITORIES                 |
   50 |   450 |       |     3   (0)| 00:00:01 |

|* 26 |             HASH JOIN                                    |                                |
98716 |    94M|       | 16959   (1)| 00:03:24 |

|  27 |              TABLE ACCESS FULL                           | HZ_CUST_SITE_USES_ALL          |
 3758 | 45096 |       |    12   (0)| 00:00:01 |

|* 28 |              HASH JOIN                                   |                                |
98731 |    93M|       | 16945   (1)| 00:03:24 |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

|  29 |               TABLE ACCESS FULL                          | HZ_CUST_SITE_USES_ALL          |
 3758 | 37580 |       |    12   (0)| 00:00:01 |

|* 30 |               HASH JOIN                                  |                                |
98731 |    92M|       | 16932   (1)| 00:03:24 |

|  31 |                TABLE ACCESS FULL                         | HR_ALL_ORGANIZATION_UNITS      |
 2505 | 60120 |       |     8   (0)| 00:00:01 |

|* 32 |                HASH JOIN                                 |                                |
98731 |    89M|       | 16923   (1)| 00:03:24 |

|  33 |                 TABLE ACCESS FULL                        | HZ_CUST_ACCOUNTS               |
 1048 | 22008 |       |     5   (0)| 00:00:01 |

|* 34 |                 HASH JOIN                                |                                |
98745 |    87M|       | 16917   (1)| 00:03:24 |

|* 35 |                  TABLE ACCESS FULL                       | RA_CUST_TRX_TYPES_ALL          |
  882 | 28224 |       |     6   (0)| 00:00:01 |

|* 36 |                  HASH JOIN RIGHT OUTER                   |                                |
62247 |    53M|       | 16910   (1)| 00:03:23 |

|  37 |                   TABLE ACCESS FULL                      | JTF_RS_SALESREPS               |
  585 | 15795 |       |     4   (0)| 00:00:01 |

|* 38 |                   HASH JOIN RIGHT OUTER                  |                                |
62247 |    51M|       | 16905   (1)| 00:03:23 |

|  39 |                    TABLE ACCESS BY INDEX ROWID           | RA_TERMS_TL                    |
   26 |   468 |       |     2   (0)| 00:00:01 |

|* 40 |                     INDEX SKIP SCAN                      | RA_TERMS_TL_N1                 |
   26 |       |       |     1   (0)| 00:00:01 |

|* 41 |                    HASH JOIN                             |                                |
62247 |    50M|  3472K| 16902   (1)| 00:03:23 |

|* 42 |                     TABLE ACCESS FULL                    | RA_CUSTOMER_TRX_ALL            |
49320 |  2889K|       |   404   (2)| 00:00:05 |

|* 43 |                     HASH JOIN                            |                                |
62247 |    47M|    22M| 13946   (1)| 00:02:48 |

|* 44 |                      TABLE ACCESS FULL                   | RA_CUSTOMER_TRX_LINES_ALL      |
  433K|    17M|       |  1564   (3)| 00:00:19 |

|  45 |                      NESTED LOOPS OUTER                  |                                |
70812 |    50M|       |  8704   (1)| 00:01:45 |

|* 46 |                       HASH JOIN RIGHT OUTER              |                                |
70812 |    50M|       |  8704   (1)| 00:01:45 |

|  47 |                        TABLE ACCESS FULL                 | WSH_CARRIER_SERVICES           |
  111 |  4995 |       |     3   (0)| 00:00:01 |

|* 48 |                        HASH JOIN RIGHT OUTER             |                                |
70812 |    47M|       |  8700   (1)| 00:01:45 |

|  49 |                         TABLE ACCESS BY INDEX ROWID      | FND_LOOKUP_VALUES              |
    1 |    57 |       |     4   (0)| 00:00:01 |

|* 50 |                          INDEX RANGE SCAN                | FND_LOOKUP_VALUES_U1           |
    1 |       |       |     3   (0)| 00:00:01 |

|* 51 |                         HASH JOIN                        |                                |
70812 |    43M|  2272K|  8695   (1)| 00:01:45 |

|  52 |                          TABLE ACCESS FULL               | OE_ORDER_HEADERS_ALL           |
44620 |  1742K|       |   200   (4)| 00:00:03 |

|* 53 |                          HASH JOIN                       |                                |
70812 |    40M|       |  6323   (2)| 00:01:16 |

|  54 |                           TABLE ACCESS FULL              | MTL_SYSTEM_ITEMS_B             |
28090 |   932K|       |   753   (1)| 00:00:10 |

|* 55 |                           HASH JOIN                      |                                |
69859 |    37M|       |  5569   (2)| 00:01:07 |

|* 56 |                            HASH JOIN                     |                                |
   13 |  1313 |       |    50   (6)| 00:00:01 |

|* 57 |                             HASH JOIN                    |                                |
   25 |  2325 |       |    46   (5)| 00:00:01 |

|  58 |                              NESTED LOOPS                |                                |
      |       |       |            |          |

|  59 |                               NESTED LOOPS               |                                |
   25 |  1625 |       |    28   (4)| 00:00:01 |

|  60 |                                NESTED LOOPS              |                                |
   25 |  1425 |       |    27   (8)| 00:00:01 |


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|* 61 |                                 HASH JOIN                |                                |
   37 |  1517 |       |    27   (8)| 00:00:01 |

|* 62 |                                  TABLE ACCESS FULL       | HR_ORGANIZATION_INFORMATION    |
   52 |  1716 |       |    17   (0)| 00:00:01 |

|* 63 |                                  TABLE ACCESS FULL       | HR_ALL_ORGANIZATION_UNITS      |
   37 |   296 |       |     9  (12)| 00:00:01 |

|* 64 |                                 INDEX UNIQUE SCAN        | HR_ALL_ORGANIZATION_UNTS_TL_PK |
    1 |    16 |       |     0   (0)| 00:00:01 |

|* 65 |                                INDEX UNIQUE SCAN         | MTL_PARAMETERS_U1              |
    1 |       |       |     0   (0)| 00:00:01 |

|  66 |                               TABLE ACCESS BY INDEX ROWID| MTL_PARAMETERS                 |
    1 |     8 |       |     1   (0)| 00:00:01 |

|* 67 |                              TABLE ACCESS FULL           | HR_ORGANIZATION_INFORMATION    |
  196 |  5488 |       |    17   (0)| 00:00:01 |

|* 68 |                             TABLE ACCESS FULL            | GL_LEDGERS                     |
   49 |   392 |       |     3   (0)| 00:00:01 |

|  69 |                            TABLE ACCESS FULL             | OE_ORDER_LINES_ALL             |
  203K|    90M|       |  5518   (2)| 00:01:07 |

|  70 |                       TABLE ACCESS BY INDEX ROWID        | HZ_CUST_SITE_USES_ALL          |
    1 |    10 |       |     0   (0)| 00:00:01 |

|* 71 |                        INDEX UNIQUE SCAN                 | HZ_CUST_SITE_USES_U1           |
    1 |       |       |     0   (0)| 00:00:01 |

----------------------------------------------------------------------------------------------------
-----------------------------------------------


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

   1 - filter(:B1='KIT')
   3 - access("CST"."INVENTORY_ITEM_ID"=:B1 AND "CST"."ORGANIZATION_ID"=:B2 AND "CST"."COST_TYPE_ID"
=3)

   4 - access("HZPSI3"."LOCATION_ID"="HZLOC3"."LOCATION_ID"(+))
   6 - access("HCASA3"."PARTY_SITE_ID"="HZPSI3"."PARTY_SITE_ID"(+))
   8 - access("HCSUA3"."CUST_ACCT_SITE_ID"="HCASA3"."CUST_ACCT_SITE_ID"(+))
  10 - access("HZPSI"."LOCATION_ID"="HZLOC"."LOCATION_ID")
  12 - access("HCASA"."PARTY_SITE_ID"="HZPSI"."PARTY_SITE_ID")
  14 - access("HCSUA"."CUST_ACCT_SITE_ID"="HCASA"."CUST_ACCT_SITE_ID")
  16 - access("HZPSI2"."LOCATION_ID"="HZLOC2"."LOCATION_ID")
  18 - access("HCASA2"."PARTY_SITE_ID"="HZPSI2"."PARTY_SITE_ID")
  20 - access("HCSUA2"."CUST_ACCT_SITE_ID"="HCASA2"."CUST_ACCT_SITE_ID")
  22 - access("HCA"."PARTY_ID"="HPTY"."PARTY_ID")
  24 - access("HCSUA"."TERRITORY_ID"="TERR"."TERRITORY_ID"(+))
  26 - access("RCTA"."BILL_TO_SITE_USE_ID"="HCSUA"."SITE_USE_ID")
  28 - access("RCTA"."SHIP_TO_SITE_USE_ID"="HCSUA2"."SITE_USE_ID")
  30 - access("RCTA"."ORG_ID"="HOU"."ORGANIZATION_ID")
  32 - access("RCTA"."BILL_TO_CUSTOMER_ID"="HCA"."CUST_ACCOUNT_ID")
  34 - access("RCTA"."CUST_TRX_TYPE_ID"="CUST_TRX_TYPE_ID")
  35 - filter("ZD_EDITION_NAME"='ORA$BASE')
  36 - access("RCTA"."PRIMARY_SALESREP_ID"="SREP"."SALESREP_ID"(+) AND "RCTA"."ORG_ID"="SREP"."ORG_I
D"(+))

  38 - access("RCTA"."TERM_ID"="PTRM"."TERM_ID"(+))
  40 - access("PTRM"."LANGUAGE"(+)='US')
       filter("PTRM"."LANGUAGE"(+)='US')
  41 - access("RCTLA"."CUSTOMER_TRX_ID"="RCTA"."CUSTOMER_TRX_ID")
  42 - filter("RCTA"."SHIP_TO_SITE_USE_ID" IS NOT NULL)
  43 - access("L"."LINE_ID"=TO_NUMBER("RCTLA"."INTERFACE_LINE_ATTRIBUTE6"))
  44 - filter("RCTLA"."WH_UPDATE_DATE" IS NULL)
  46 - access("H"."SHIPPING_METHOD_CODE"="WCS"."SHIP_METHOD_CODE"(+))
  48 - access("H"."FREIGHT_TERMS_CODE"="LOOKUP_CODE"(+))
  50 - access("LOOKUP_TYPE"(+)='FREIGHT_TERMS' AND "LANGUAGE"(+)=USERENV('LANG') AND "ZD_EDITION_NAM
E"(+)='V_20141219_1621')

       filter("ZD_EDITION_NAME"(+)='V_20141219_1621' AND "LANGUAGE"(+)=USERENV('LANG'))
  51 - access("L"."HEADER_ID"="H"."HEADER_ID")
  53 - access("L"."INVENTORY_ITEM_ID"="MSIB"."INVENTORY_ITEM_ID" AND "L"."SHIP_FROM_ORG_ID"="MSIB"."
ORGANIZATION_ID")

  55 - access("L"."SHIP_FROM_ORG_ID"="HAO"."ORGANIZATION_ID")
  56 - access("LGR"."LEDGER_ID"=TO_NUMBER(DECODE(RTRIM(TRANSLATE("HOI2"."ORG_INFORMATION1",'01234567
89','

              ')),NULL,"HOI2"."ORG_INFORMATION1",'-99999')))
  57 - access("HAO"."ORGANIZATION_ID"="HOI2"."ORGANIZATION_ID")
  61 - access("HAO"."ORGANIZATION_ID"="HOI1"."ORGANIZATION_ID")
  62 - filter("HOI1"."ORG_INFORMATION1"='INV' AND "HOI1"."ORG_INFORMATION2"='Y' AND "HOI1"."ORG_INFO
RMATION_CONTEXT"||''='CLASS')

  63 - filter("HAO"."BUSINESS_GROUP_ID"=DECODE("HR_GENERAL"."GET_XBG_PROFILE"(),'Y',"HAO"."BUSINESS_
GROUP_ID","HR_GENERAL"."GET_BUSINESS_GR

              OUP_ID"()))
  64 - access("HAO"."ORGANIZATION_ID"="ORGANIZATION_ID" AND "LANGUAGE"=USERENV('LANG') AND "ZD_EDITI
ON_NAME"='ORA$BASE')

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

       filter(DECODE("HR_SECURITY"."VIEW_ALL"(),'Y','TRUE',"HR_SECURITY"."SHOW_RECORD"('HR_ALL_ORGAN
IZATION_UNITS',"ORGANIZATION_ID"))='TRU

              E')
  65 - access("HAO"."ORGANIZATION_ID"="MP"."ORGANIZATION_ID")
  67 - filter("HOI2"."ORG_INFORMATION_CONTEXT"||''='Accounting Information')
  68 - filter("LGR"."OBJECT_TYPE_CODE"='L' AND NVL("LGR"."COMPLETE_FLAG",'Y')='Y')
  71 - access("H"."END_CUSTOMER_SITE_USE_ID"="HCSUA3"."SITE_USE_ID"(+))

130 rows selected.
Re: Select statement taking Long time to execute [message #637700 is a reply to message #637699] Thu, 21 May 2015 17:54 Go to previous messageGo to next message
P1Oracle
Messages: 60
Registered: August 2014
Location: Hyderabad
Member
Please reply me..any one
Re: Select statement taking Long time to execute [message #637701 is a reply to message #637700] Thu, 21 May 2015 18:12 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
thiruvari wrote on Thu, 21 May 2015 15:54
Please reply me..any one


With free advice, sometimes you get what you paid for it.

If you are ever dissatisfied with the tone, tenor, completeness, correctness, or timeliness of any response,
please submit your request for a prompt & full refund to /dev/null

NOBODY here owes you a quick response or any response for that matter.

Please explain why EXPLAIN PLAN reports that query should complete in about 3.5 minutes.

Are statistics for all tables & indexes current & valid?
Re: Select statement taking Long time to execute [message #637706 is a reply to message #637701] Thu, 21 May 2015 22:07 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Please refer to the page BlackSwan has noted. I have duplicated here part of that page but your should review the full page anyway.


Quote:
I am attaching here the promotional first chapter of my book on SQL Tuning, and the scripts file for people to find. These are free to use and share. As always, there is no warranty express or implied and use at your own risk.

At the suggestion of BlackSwan, I have also attached the latest document which describes the information needed to tune a SQL statement and how you can get it using the scripts also available here. If you are going to ask for help in tuning a SQL statement, please review this document first. You will at some point be asked to produce this information so if you can have it in your original post, better results will follow. I suggest providing the BASIC information first, then follow up with the ADVANCED information when asked.

Enjoy. Kevin



As indicated, here are the attachments. There is a minimum amount of information anyone here needs to help you, which is why everyone is asking for it. All who have responded to your request for help can help you tune your query, if you provide the information. They are all experts in Oracle which I know because I have conversed with them all over many years.

Good luck, post back when you have the requested info, or at least the BASIC info noted in the attachment. At some point you may become interested in the book itself. Follow the link. The book is at its lowest price ever.

Kevin

[Updated on: Thu, 21 May 2015 22:08]

Report message to a moderator

Re: Select statement taking Long time to execute [message #637707 is a reply to message #637706] Thu, 21 May 2015 23:18 Go to previous message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
How manw rows are in APPS.RA_CUSTOMER_TRX_LINES_ALL?
How many of them have WH_UPDATE_DATE IS NULL?

Your query is doing a lot of Full Table Scans. That might be fine if a significant portion of the rows in RA_CUSTOMER_TRX_LINES_ALL have a NULL WH_UPDATE_DATE. But if there are (say) <1% with a NULL date, then your query would be best to start with that table and then do Indexed Nested Loops joins to the rest.

So to start with, run these and tell us the results:
SELECT COUNT(*) 
FROM APPS.RA_CUSTOMER_TRX_LINES_ALL;

SELECT COUNT(*) 
FROM APPS.RA_CUSTOMER_TRX_LINES_ALL
WHERE WH_UPDATE_DATE IS NULL;


Oh, and please run them on the environment where it is slow, not some development environment where the tables are almost empty.

Ross Leishman

P.S. Just because it looks like I'm helping, don't ignore the other guys' request for more information.
Previous Topic: Set NOLOGGING for tables in UAT
Next Topic: SQL execution takes long time
Goto Forum:
  


Current Time: Fri Mar 29 02:28:53 CDT 2024