This discussion is archived
6 Replies Latest reply: Jan 29, 2013 9:37 PM by Kanish RSS

How to tune this query?

Kanish Journeyer
Currently Being Moderated
Hi Exports,

I am working on oracle apps. On apps while running a report it took 4 to 6 hours to complete. I enabled the trace and taken the tkprof output. I am giving the area where takes too long time to run. Please guide me in this. How could i reduce the time.



Banner

 Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
"CORE     10.2.0.3.0     Production"
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
Query 1
SELECT SUM(CMPNT_COST)   
FROM
 CM_CMPT_DTL_VW CM,CM_CLDR_DTL CAL  WHERE ITEM_ID =  (SELECT ITEM_ID   FROM 
  IC_ITEM_MST  WHERE ITEM_NO = :b1 )  AND COST_MTHD_CODE = 'PMAC'  AND 
  CM.CALENDAR_CODE = CAL.CALENDAR_CODE  AND CM.PERIOD_CODE = CAL.PERIOD_CODE  
  AND :b2 BETWEEN CAL.START_DATE AND CAL.END_DATE  AND WHSE_CODE =  (SELECT 
  ORGANIZATION_CODE   FROM MTL_PARAMETERS_VIEW  WHERE ORGANIZATION_ID = :b3 )


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.02       1.74          0          0          0           0
Execute    898      0.10       0.14          0          0          0           0
Fetch      898   6725.58   33276.46   89695038   81677590          0         898
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     1798   6725.71   33278.35   89695038   81677590          0         898

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 173  

Rows     Row Source Operation
-------  ---------------------------------------------------
    449  SORT AGGREGATE (cr=40838795 pr=44939444 pw=4597635 time=16613240123 us)
   4829   HASH JOIN  (cr=40838795 pr=44939444 pw=4597635 time=9057442461 us)
    898    TABLE ACCESS FULL CM_CLDR_DTL (cr=6735 pr=56 pw=0 time=104182 us)
 101742    VIEW  CM_CMPT_DTL_VW (cr=40832060 pr=44939388 pw=4597635 time=9682849482 us)
340891576     UNION-ALL  (cr=40824876 pr=44938969 pw=4597635 time=17933942059 us)
340891576      HASH GROUP BY (cr=20412438 pr=24769239 pw=4597635 time=10846575027 us)
361186825       TABLE ACCESS FULL CM_CMPT_DTL (cr=20412438 pr=20171604 pw=0 time=7164703993 us)
      0      TABLE ACCESS FULL CM_CMPT_DTL (cr=20412438 pr=20169730 pw=0 time=6901261441 us)
    449     NESTED LOOPS  (cr=2245 pr=216 pw=0 time=5859356 us)
    449      TABLE ACCESS BY INDEX ROWID IC_ITEM_MST_B (cr=1347 pr=177 pw=0 time=5095717 us)
    449       INDEX UNIQUE SCAN IC_ITEM_MST_B_UNQ1 (cr=898 pr=51 pw=0 time=1021648 us)(object id 326600)
    449      INDEX UNIQUE SCAN IC_ITEM_MST_TL_PK (cr=898 pr=39 pw=0 time=743956 us)(object id 326545)
    449     NESTED LOOPS OUTER (cr=4939 pr=203 pw=0 time=3994946 us)
    449      NESTED LOOPS  (cr=3592 pr=107 pw=0 time=2354467 us)
    449       FILTER  (cr=1796 pr=34 pw=0 time=987087 us)
    449        NESTED LOOPS OUTER (cr=1796 pr=34 pw=0 time=927028 us)
    449         NESTED LOOPS OUTER (cr=1796 pr=34 pw=0 time=946673 us)
    449          NESTED LOOPS OUTER (cr=1796 pr=34 pw=0 time=917867 us)
    449           NESTED LOOPS OUTER (cr=1796 pr=34 pw=0 time=886142 us)
    449            NESTED LOOPS OUTER (cr=1796 pr=34 pw=0 time=863553 us)
    449             NESTED LOOPS OUTER (cr=1796 pr=34 pw=0 time=830785 us)
    449              NESTED LOOPS  (cr=1796 pr=34 pw=0 time=808504 us)
    449               NESTED LOOPS OUTER (cr=1347 pr=34 pw=0 time=779000 us)
    449                TABLE ACCESS BY INDEX ROWID MTL_PARAMETERS (cr=898 pr=2 pw=0 time=202640 us)
    449                 INDEX UNIQUE SCAN MTL_PARAMETERS_U1 (cr=449 pr=2 pw=0 time=183457 us)(object id 38517)
    449                INDEX UNIQUE SCAN CST_COST_GROUPS_U1 (cr=449 pr=32 pw=0 time=557604 us)(object id 30846)
    449               INDEX UNIQUE SCAN HR_ORGANIZATION_UNITS_PK (cr=449 pr=0 pw=0 time=11606 us)(object id 43657)
      0              INDEX UNIQUE SCAN MTL_PICKING_RULES_U1 (cr=0 pr=0 pw=0 time=6183 us)(object id 38530)
      0             INDEX UNIQUE SCAN MTL_ATP_RULES_U1 (cr=0 pr=0 pw=0 time=14490 us)(object id 38531)
      0            INDEX UNIQUE SCAN BOM_RESOURCES_U2 (cr=0 pr=0 pw=0 time=6062 us)(object id 30440)
      0           TABLE ACCESS BY INDEX ROWID HR_ALL_ORGANIZATION_UNITS (cr=0 pr=0 pw=0 time=15751 us)
      0            INDEX UNIQUE SCAN HR_ORGANIZATION_UNITS_PK (cr=0 pr=0 pw=0 time=6126 us)(object id 43657)
      0          INDEX UNIQUE SCAN MTL_PARAMETERS_U1 (cr=0 pr=0 pw=0 time=6153 us)(object id 38517)
      0         INDEX RANGE SCAN HR_ALL_ORGANIZATION_UNTS_TL_PK (cr=0 pr=0 pw=0 time=6630 us)(object id 44020)
    449       INDEX RANGE SCAN FND_LOOKUP_VALUES_U1 (cr=1796 pr=73 pw=0 time=1345141 us)(object id 34010)
      0      VIEW PUSHED PREDICATE  FND_COMMON_LOOKUPS (cr=1347 pr=96 pw=0 time=1623600 us)
      0       NESTED LOOPS  (cr=1347 pr=96 pw=0 time=1614352 us)
    449        TABLE ACCESS BY INDEX ROWID FND_LOOKUP_TYPES (cr=1347 pr=96 pw=0 time=1591386 us)
    449         INDEX RANGE SCAN FND_LOOKUP_TYPES_U1 (cr=898 pr=64 pw=0 time=1164297 us)(object id 34004)
      0        INDEX UNIQUE SCAN FND_LOOKUP_VALUES_U1 (cr=0 pr=0 pw=0 time=6778 us)(object id 34010)

********************************************************************************
Query 2
SELECT OH1.HEADER_ID A,OEL.LINE_ID B   
FROM
 RA_CUSTOMER_TRX_ALL RA,RA_CUSTOMER_TRX_LINES_ALL RCTLA,OE_ORDER_HEADERS_ALL 
  OH,OE_ORDER_HEADERS_ALL OH1,OE_ORDER_LINES_ALL OEL  WHERE 
  RA.CUSTOMER_TRX_ID = RCTLA.CUSTOMER_TRX_ID  AND (RA.CT_REFERENCE) = 
  TO_CHAR(OH1.ORDER_NUMBER)  AND OH1.SOURCE_DOCUMENT_ID = OH.HEADER_ID  AND 
  OH.ORDER_NUMBER = :b1  AND RCTLA.INVENTORY_ITEM_ID =  (SELECT DISTINCT 
  INVENTORY_ITEM_ID   FROM MTL_SYSTEM_ITEMS_B  WHERE SEGMENT1 = :b2 )  AND 
  OH1.HEADER_ID = OEL.HEADER_ID  AND OEL.INVENTORY_ITEM_ID = 
  RCTLA.INVENTORY_ITEM_ID


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute    387      0.04       0.04          0          0          0           0
Fetch      389    689.22   33361.26   19979867   20144715          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      777    689.26   33361.31   19979867   20144715          0           2

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 173  

Rows     Row Source Operation
-------  ---------------------------------------------------
      2  TABLE ACCESS BY INDEX ROWID OE_ORDER_LINES_ALL (cr=20144715 pr=19979867 pw=0 time=33024227812 us)
    391   NESTED LOOPS  (cr=20144713 pr=19979865 pw=0 time=33169147608 us)
      2    NESTED LOOPS  (cr=20144705 pr=19979861 pw=0 time=33024244282 us)
      2     HASH JOIN  (cr=20144516 pr=19979682 pw=0 time=33022474026 us)
2363409      TABLE ACCESS FULL OE_ORDER_HEADERS_ALL (cr=7992324 pr=7935681 pw=0 time=10973123196 us)
159464124      MERGE JOIN CARTESIAN (cr=12152192 pr=12044001 pw=0 time=21849267761 us)
    387       TABLE ACCESS BY INDEX ROWID OE_ORDER_HEADERS_ALL (cr=1553 pr=79 pw=0 time=1416188 us)
    387        INDEX RANGE SCAN OE_ORDER_HEADERS_U2 (cr=1166 pr=79 pw=0 time=1400563 us)(object id 41431)
159464124       BUFFER SORT (cr=12150639 pr=12043922 pw=0 time=20287027876 us)
159464124        TABLE ACCESS FULL RA_CUSTOMER_TRX_ALL (cr=12150639 pr=12043922 pw=0 time=19458528384 us)
      2     TABLE ACCESS BY INDEX ROWID RA_CUSTOMER_TRX_LINES_ALL (cr=189 pr=179 pw=0 time=1762590 us)
      2      INDEX RANGE SCAN RA_CUSTOMER_TRX_LINES_N2 (cr=8 pr=1 pw=0 time=20802 us)(object id 29156)
      2      HASH UNIQUE (cr=179 pr=176 pw=0 time=1713629 us)
     26       TABLE ACCESS BY INDEX ROWID MTL_SYSTEM_ITEMS_B (cr=179 pr=176 pw=0 time=1056631 us)
     26        INDEX SKIP SCAN MTL_SYSTEM_ITEMS_B_N1 (cr=172 pr=172 pw=0 time=1056300 us)(object id 38528)
      2    INDEX RANGE SCAN OE_ORDER_LINES_N1 (cr=8 pr=4 pw=0 time=44331 us)(object id 41462)

********************************************************************************
Please help me this regards.

Thanks & Regards,
Kanish
  • 1. Re: How to tune this query?
    Karthick_Arp Guru
    Currently Being Moderated
    One thing that i see is there is a huge difference between Elapsed time and CPU time. So i would suggest you to check the wait events.

    Can you run the below command and get the output
    explain plan for
    select sum(cmpnt_cost)   
      from cm_cmpt_dtl_vw cm
         , cm_cldr_dtl cal  
     where cm.calendar_code = cal.calendar_code 
       and cm.period_code   = cal.period_code  
       and cost_mthd_code   = 'PMAC'  
       and :b2 between cal.start_date and cal.end_date  
       and item_id   = (
                          select item_id
                            from ic_item_mst  
                     where item_no = :b1
                 ) 
       and whse_code = (
                          select organization_code   
                      from mtl_parameters_view  
                     where organization_id = :b3 
                 );
    
    select * from table(dbms_xplan.display);
  • 2. Re: How to tune this query?
    Kanish Journeyer
    Currently Being Moderated
    Hi karthick,

     
    -------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                                 | Name                           | Rows  | Bytes |TempSpc| Cost (%CPU)|
    -------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                          |                                |     1 |    62 |       | 25886   (1)|
    |   1 |  SORT AGGREGATE                           |                                |     1 |    62 |       |            |
    |*  2 |   HASH JOIN                               |                                |   112 |  6944 |       | 25875   (1)|
    |*  3 |    TABLE ACCESS FULL                      | CM_CLDR_DTL                    |     1 |    24 |       |     5   (0)|
    |*  4 |    VIEW                                   | CM_CMPT_DTL_VW                 | 44951 |  1668K|       | 25870   (1)|
    |   5 |     UNION-ALL                             |                                |       |       |       |            |
    |   6 |      HASH GROUP BY                        |                                | 44416 |  5682K|    22M| 13591   (1)|
    |*  7 |       TABLE ACCESS FULL                   | CM_CMPT_DTL                    | 44416 |  5682K|       | 12288   (1)|
    |*  8 |      TABLE ACCESS FULL                    | CM_CMPT_DTL                    |   535 | 70085 |       | 12278   (1)|
    |   9 |     NESTED LOOPS                          |                                |     1 |    28 |       |     2   (0)|
    |  10 |      TABLE ACCESS BY INDEX ROWID          | IC_ITEM_MST_B                  |     1 |    20 |       |     2   (0)|
    |* 11 |       INDEX UNIQUE SCAN                   | IC_ITEM_MST_B_UNQ1             |     1 |       |       |     1   (0)|
    |* 12 |      INDEX UNIQUE SCAN                    | IC_ITEM_MST_TL_PK              | 11310 | 90480 |       |     0   (0)|
    |  13 |     NESTED LOOPS OUTER                    |                                |     1 |   195 |       |     9   (0)|
    |  14 |      NESTED LOOPS                         |                                |     1 |   161 |       |     5   (0)|
    |* 15 |       FILTER                              |                                |       |       |       |            |
    |  16 |        NESTED LOOPS OUTER                 |                                |     1 |   125 |       |     3   (0)|
    |  17 |         NESTED LOOPS OUTER                |                                |     1 |   118 |       |     2   (0)|
    |  18 |          NESTED LOOPS OUTER               |                                |     1 |   114 |       |     2   (0)|
    |  19 |           NESTED LOOPS OUTER              |                                |     1 |   106 |       |     1   (0)|
    |  20 |            NESTED LOOPS OUTER             |                                |     1 |   101 |       |     1   (0)|
    |  21 |             NESTED LOOPS OUTER            |                                |     1 |    97 |       |     1   (0)|
    |  22 |              NESTED LOOPS                 |                                |     1 |    94 |       |     1   (0)|
    |  23 |               NESTED LOOPS OUTER          |                                |     1 |    90 |       |     1   (0)|
    |  24 |                TABLE ACCESS BY INDEX ROWID| MTL_PARAMETERS                 |     1 |    85 |       |     1   (0)|
    |* 25 |                 INDEX UNIQUE SCAN         | MTL_PARAMETERS_U1              |     1 |       |       |     0   (0)|
    |* 26 |                INDEX UNIQUE SCAN          | CST_COST_GROUPS_U1             |    64 |   320 |       |     0   (0)|
    |* 27 |               INDEX UNIQUE SCAN           | HR_ORGANIZATION_UNITS_PK       |    88 |   352 |       |     0   (0)|
    |* 28 |              INDEX UNIQUE SCAN            | MTL_PICKING_RULES_U1           |     1 |     3 |       |     0   (0)|
    |* 29 |             INDEX UNIQUE SCAN             | MTL_ATP_RULES_U1               |     2 |     8 |       |     0   (0)|
    |* 30 |            INDEX UNIQUE SCAN              | BOM_RESOURCES_U2               |   705 |  3525 |       |     0   (0)|
    |* 31 |           TABLE ACCESS BY INDEX ROWID     | HR_ALL_ORGANIZATION_UNITS      |     1 |     8 |       |     1   (0)|
    |* 32 |            INDEX UNIQUE SCAN              | HR_ORGANIZATION_UNITS_PK       |     1 |       |       |     0   (0)|
    |* 33 |          INDEX UNIQUE SCAN                | MTL_PARAMETERS_U1              |    49 |   196 |       |     0   (0)|
    |* 34 |         INDEX RANGE SCAN                  | HR_ALL_ORGANIZATION_UNTS_TL_PK |     1 |     7 |       |     1   (0)|
    |* 35 |       INDEX RANGE SCAN                    | FND_LOOKUP_VALUES_U1           |     1 |    36 |       |     2   (0)|
    |  36 |      VIEW PUSHED PREDICATE                | FND_COMMON_LOOKUPS             |     1 |    34 |       |     4   (0)|
    |  37 |       NESTED LOOPS                        |                                |     1 |    74 |       |     4   (0)|
    |* 38 |        TABLE ACCESS BY INDEX ROWID        | FND_LOOKUP_TYPES               |     1 |    30 |       |     3   (0)|
    |* 39 |         INDEX RANGE SCAN                  | FND_LOOKUP_TYPES_U1            |     1 |       |       |     2   (0)|
    |* 40 |        INDEX UNIQUE SCAN                  | FND_LOOKUP_VALUES_U1           |     1 |    44 |       |     1   (0)|
    -------------------------------------------------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
       2 - access("CM"."CALENDAR_CODE"="CAL"."CALENDAR_CODE" AND "CM"."PERIOD_CODE"="CAL"."PERIOD_CODE")
       3 - filter("CAL"."START_DATE"<=:B2 AND "CAL"."END_DATE">=:B2)
       4 - filter("ITEM_ID"= (SELECT "B"."ITEM_ID" FROM "GMI"."IC_ITEM_MST_B" "B","GMI"."IC_ITEM_MST_TL" "T" WHERE 
                  "T"."LANGUAGE"=USERENV('LANG') AND "B"."ITEM_ID"="T"."ITEM_ID" AND "B"."ITEM_NO"=:B1) AND "WHSE_CODE"= (SELECT 
                  "A"."ORGANIZATION_CODE" FROM "BOM"."CST_COST_GROUPS" "CST","HR"."HR_ALL_ORGANIZATION_UNITS" 
                  "I","HR"."HR_ALL_ORGANIZATION_UNITS_TL" "HT","INV"."MTL_PICKING_RULES" "G","INV"."MTL_ATP_RULES" 
                  "F","BOM"."BOM_RESOURCES" "E","APPLSYS"."FND_LOOKUP_VALUES" "LV", (SELECT "FLT"."APPLICATION_ID" 
                  "APPLICATION_ID","FLT"."LOOKUP_TYPE" "LOOKUP_TYPE","FLV"."LOOKUP_CODE" "LOOKUP_CODE","FLV"."CREATED_BY" 
                  "CREATED_BY","FLV"."CREATION_DATE" "CREATION_DATE","FLV"."LAST_UPDATE_DATE" 
                  "LAST_UPDATE_DATE","FLV"."LAST_UPDATED_BY" "LAST_UPDATED_BY","FLV"."LAST_UPDATE_LOGIN" 
                  "LAST_UPDATE_LOGIN","FLV"."ATTRIBUTE_CATEGORY" "ATTRIBUTE_CATEGORY","FLV"."ATTRIBUTE1" 
                  "ATTRIBUTE1","FLV"."ATTRIBUTE2" "ATTRIBUTE2","FLV"."ATTRIBUTE3" "ATTRIBUTE3","FLV"."ATTRIBUTE4" 
                  "ATTRIBUTE4","FLV"."ATTRIBUTE5" "ATTRIBUTE5","FLV"."ATTRIBUTE6" "ATTRIBUTE6","FLV"."ATTRIBUTE7" 
                  "ATTRIBUTE7","FLV"."ATTRIBUTE8" "ATTRIBUTE8","FLV"."ATTRIBUTE9" "ATTRIBUTE9","FLV"."ATTRIBUTE10" 
                  "ATTRIBUTE10","FLV"."ATTRIBUTE11" "ATTRIBUTE11","FLV"."ATTRIBUTE12" "ATTRIBUTE12","FLV"."ATTRIBUTE13" 
                  "ATTRIBUTE13","FLV"."ATTRIBUTE14" "ATTRIBUTE14","FLV"."ATTRIBUTE15" "ATTRIBUTE15" FROM 
                  "APPLSYS"."FND_LOOKUP_VALUES" "FLV","APPLSYS"."FND_LOOKUP_TYPES" "FLT" WHERE "FLT"."VIEW_APPLICATION_ID"=3 AND 
                  "FLT"."LOOKUP_TYPE"='DEMAND_CLASS' AND "FLT"."APPLICATION_ID"=700 AND "FLV"."LANGUAGE"=USERENV('LANG') AND 
                  "FLV"."SECURITY_GROUP_ID"="FLT"."SECURITY_GROUP_ID" AND "FLV"."LOOKUP_CODE"="A"."DEFAULT_DEMAND_CLASS" AND 
                  "FLV"."VIEW_APPLICATION_ID"=3 AND "FLV"."LOOKUP_TYPE"='DEMAND_CLASS' AND 
                  "FLV"."SECURITY_GROUP_ID"="FND_GLOBAL"."LOOKUP_SECURITY_GROUP"("FLV"."LOOKUP_TYPE","FLV"."VIEW_APPLICATION_ID")) 
                  "B","INV"."MTL_PARAMETERS" "HH","HR"."HR_ALL_ORGANIZATION_UNITS" "HAO","INV"."MTL_PARAMETERS" "A" WHERE 
                  "A"."ORGANIZATION_ID"=TO_NUMBER(:B3) AND "ORGANIZATION_ID"(+)="A"."SOURCE_ORGANIZATION_ID" AND 
                  "HAO"."BUSINESS_GROUP_ID"(+)=DECODE("HR_GENERAL"."GET_XBG_PROFILE"(),'Y',"HAO"."BUSINESS_GROUP_ID"(+),"HR_GENERAL
                  "."GET_BUSINESS_GROUP_ID"()) AND DECODE("HR_SECURITY"."VIEW_ALL"(),'Y','TRUE',"HR_SECURITY"."SHOW_RECORD"('HR_ALL
                  _ORGANIZATION_UNITS',"HAO"."ORGANIZATION_ID"(+)))='TRUE' AND "HH"."ORGANIZATION_ID"(+)="ORGANIZATION_ID" AND 
                  "LV"."VIEW_APPLICATION_ID"=700 AND "LV"."LOOKUP_TYPE"='MTL_PRIMARY_COST' AND "LV"."LANGUAGE"=USERENV('LANG') AND 
                  "LV"."SECURITY_GROUP_ID"="FND_GLOBAL"."LOOKUP_SECURITY_GROUP"("LV"."LOOKUP_TYPE","LV"."VIEW_APPLICATION_ID") AND 
                  "A"."PRIMARY_COST_METHOD"=TO_NUMBER("LV"."LOOKUP_CODE") AND "E"."RESOURCE_ID"(+)="A"."DEFAULT_MATERIAL_COST_ID" 
                  AND "F"."RULE_ID"(+)="A"."DEFAULT_ATP_RULE_ID" AND "G"."PICKING_RULE_ID"(+)="A"."DEFAULT_PICKING_RULE_ID" AND 
                  "ORGANIZATION_ID"="HT"."ORGANIZATION_ID"(+) AND DECODE(TO_CHAR("HT"."ORGANIZATION_ID"),NULL,'1',"HT"."LANGUAGE")=
                  DECODE(TO_CHAR("HT"."ORGANIZATION_ID"),NULL,'1',USERENV('LANG')) AND 
                  "I"."ORGANIZATION_ID"="A"."MASTER_ORGANIZATION_ID" AND "A"."DEFAULT_COST_GROUP_ID"="CST"."COST_GROUP_ID"(+)))
       7 - filter("COST_MTHD_CODE"='PMAC' AND NVL("RMCALC_TYPE",0)>0)
       8 - filter(NVL("RMCALC_TYPE",0)=0 AND "COST_MTHD_CODE"='PMAC')
      11 - access("B"."ITEM_NO"=:B1)
      12 - access("B"."ITEM_ID"="T"."ITEM_ID" AND "T"."LANGUAGE"=USERENV('LANG'))
      15 - filter(DECODE(TO_CHAR("HT"."ORGANIZATION_ID"),NULL,'1',"HT"."LANGUAGE")=DECODE(TO_CHAR("HT"."ORGANIZATION_
                  ID"),NULL,'1',USERENV('LANG')))
      25 - access("A"."ORGANIZATION_ID"=TO_NUMBER(:B3))
      26 - access("A"."DEFAULT_COST_GROUP_ID"="CST"."COST_GROUP_ID"(+))
      27 - access("I"."ORGANIZATION_ID"="A"."MASTER_ORGANIZATION_ID")
      28 - access("G"."PICKING_RULE_ID"(+)="A"."DEFAULT_PICKING_RULE_ID")
      29 - access("F"."RULE_ID"(+)="A"."DEFAULT_ATP_RULE_ID")
      30 - access("E"."RESOURCE_ID"(+)="A"."DEFAULT_MATERIAL_COST_ID")
      31 - filter("HAO"."BUSINESS_GROUP_ID"(+)=DECODE("HR_GENERAL"."GET_XBG_PROFILE"(),'Y',"HAO"."BUSINESS_GROUP_ID"(
                  +),"HR_GENERAL"."GET_BUSINESS_GROUP_ID"()))
      32 - access("ORGANIZATION_ID"(+)="A"."SOURCE_ORGANIZATION_ID")
           filter(DECODE("HR_SECURITY"."VIEW_ALL"(),'Y','TRUE',"HR_SECURITY"."SHOW_RECORD"('HR_ALL_ORGANIZATION_UNITS
                  ',"HAO"."ORGANIZATION_ID"(+)))='TRUE')
      33 - access("HH"."ORGANIZATION_ID"(+)="ORGANIZATION_ID")
      34 - access("ORGANIZATION_ID"="HT"."ORGANIZATION_ID"(+))
      35 - access("LV"."LOOKUP_TYPE"='MTL_PRIMARY_COST' AND "LV"."VIEW_APPLICATION_ID"=700 AND 
                  "LV"."LANGUAGE"=USERENV('LANG'))
           filter("LV"."LANGUAGE"=USERENV('LANG') AND "LV"."SECURITY_GROUP_ID"="FND_GLOBAL"."LOOKUP_SECURITY_GROUP"("
                  LV"."LOOKUP_TYPE","LV"."VIEW_APPLICATION_ID") AND "A"."PRIMARY_COST_METHOD"=TO_NUMBER("LV"."LOOKUP_CODE"))
      38 - filter("FLT"."APPLICATION_ID"=700)
      39 - access("FLT"."LOOKUP_TYPE"='DEMAND_CLASS' AND "FLT"."VIEW_APPLICATION_ID"=3)
      40 - access("FLV"."LOOKUP_TYPE"='DEMAND_CLASS' AND "FLV"."VIEW_APPLICATION_ID"=3 AND 
                  "FLV"."LOOKUP_CODE"="A"."DEFAULT_DEMAND_CLASS" AND "FLV"."SECURITY_GROUP_ID"="FLT"."SECURITY_GROUP_ID" AND 
                  "FLV"."LANGUAGE"=USERENV('LANG'))
           filter("FLV"."SECURITY_GROUP_ID"="FND_GLOBAL"."LOOKUP_SECURITY_GROUP"("FLV"."LOOKUP_TYPE","FLV"."VIEW_APPL
                  ICATION_ID"))
     
    Note
    -----
       - 'PLAN_TABLE' is old version 
    kanish
  • 3. Re: How to tune this query?
    Karthick_Arp Guru
    Currently Being Moderated
    Oh at first glance i dint note, cm_cmpt_dtl_vw is a view so you might want to post the SQL associated with this view.

    Also i would suggest you to read {message:id=9360003}
  • 4. Re: How to tune this query?
    Kanish Journeyer
    Currently Being Moderated
    Dear Export

    The actual view query
    SELECT 0,
      item_id,
      whse_code,
      calendar_code,
      period_code,
      cost_mthd_code,
      cost_cmpntcls_id,
      cost_analysis_code,
      0 cost_level,
      SUM (cmpnt_cost),
      burden_ind,
      fmeff_id,
      rollover_ind,
      total_qty,
      costcalc_orig,
      rmcalc_type,
      rollup_ref_no,
      acproc_id,
      trans_cnt,
      text_code,
      delete_mark,
      request_id,
      program_application_id,
      program_id,
      program_update_date,
      attribute1,
      attribute2,
      attribute3,
      attribute4,
      attribute5,
      attribute6,
      attribute7,
      attribute8,
      attribute9,
      attribute10,
      attribute11,
      attribute12,
      attribute13,
      attribute14,
      attribute15,
      attribute16,
      attribute17,
      attribute18,
      attribute19,
      attribute20,
      attribute21,
      attribute22,
      attribute23,
      attribute24,
      attribute25,
      attribute26,
      attribute27,
      attribute28,
      attribute29,
      attribute30,
      creation_date,
      created_by,
      last_update_date,
      last_updated_by,
      last_update_login,
      attribute_category,
      period_trans_qty,
      period_perp_qty
    FROM cm_cmpt_dtl
    WHERE NVL(rmcalc_type,0) > 0
    GROUP BY item_id,
      whse_code,
      calendar_code,
      period_code,
      cost_mthd_code,
      cost_cmpntcls_id,
      cost_analysis_code,
      burden_ind,
      fmeff_id,
      rollover_ind,
      total_qty,
      costcalc_orig,
      rmcalc_type,
      rollup_ref_no,
      acproc_id,
      trans_cnt,
      text_code,
      delete_mark,
      request_id,
      program_application_id,
      program_id,
      program_update_date,
      attribute1,
      attribute2,
      attribute3,
      attribute4,
      attribute5,
      attribute6,
      attribute7,
      attribute8,
      attribute9,
      attribute10,
      attribute11,
      attribute12,
      attribute13,
      attribute14,
      attribute15,
      attribute16,
      attribute17,
      attribute18,
      attribute19,
      attribute20,
      attribute21,
      attribute22,
      attribute23,
      attribute24,
      attribute25,
      attribute26,
      attribute27,
      attribute28,
      attribute29,
      attribute30,
      creation_date,
      created_by,
      last_update_date,
      last_updated_by,
      last_update_login,
      attribute_category,
      period_trans_qty,
      period_perp_qty
    UNION ALL
    SELECT cmpntcost_id,
      item_id,
      whse_code,
      calendar_code,
      period_code,
      cost_mthd_code,
      cost_cmpntcls_id,
      cost_analysis_code,
      cost_level,
      cmpnt_cost,
      burden_ind,
      fmeff_id,
      rollover_ind,
      total_qty,
      costcalc_orig,
      rmcalc_type,
      rollup_ref_no,
      acproc_id,
      trans_cnt,
      text_code,
      delete_mark,
      request_id,
      program_application_id,
      program_id,
      program_update_date,
      attribute1,
      attribute2,
      attribute3,
      attribute4,
      attribute5,
      attribute6,
      attribute7,
      attribute8,
      attribute9,
      attribute10,
      attribute11,
      attribute12,
      attribute13,
      attribute14,
      attribute15,
      attribute16,
      attribute17,
      attribute18,
      attribute19,
      attribute20,
      attribute21,
      attribute22,
      attribute23,
      attribute24,
      attribute25,
      attribute26,
      attribute27,
      attribute28,
      attribute29,
      attribute30,
      creation_date,
      created_by,
      last_update_date,
      last_updated_by,
      last_update_login,
      attribute_category,
      period_trans_qty,
      period_perp_qty
    FROM cm_cmpt_dtl
    WHERE NVL(rmcalc_type,0) = 0;
    Explain Plan
     
    --------------------------------------------------------------------------------
    | Id  | Operation           | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)|
    --------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT    |             | 89902 |    11M|       | 27178  (46)|
    |   1 |  UNION-ALL          |             |       |       |       |            |
    |   2 |   HASH GROUP BY     |             | 88832 |    11M|    44M| 14900   (1)|
    |*  3 |    TABLE ACCESS FULL| CM_CMPT_DTL | 88832 |    11M|       | 12295   (1)|
    |*  4 |   TABLE ACCESS FULL | CM_CMPT_DTL |  1070 |   136K|       | 12278   (1)|
    --------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
       3 - filter(NVL("RMCALC_TYPE",0)>0)
       4 - filter(NVL("RMCALC_TYPE",0)=0)
     
    Note
    -----
       - 'PLAN_TABLE' is old version
    Kanish
  • 5. Re: How to tune this query?
    Sven W. Guru
    Currently Being Moderated
    I think there are three approaches that you can try.

    I make the assumption that the bind parameter :item_no filters the output well.
    However it seems to me as if this parameter is not propagated into the view.
    The reason might be that the view is a little complex using a combination of UNION ALL and GROUP BY.

    approach one would be:
    1) Use joins instead of subquery when calling the view. This might help the database to better prepare for any parameters that could potentially propagated.

    approach two would be:
    2) Prepare the view in such a way, that it is easier .
    UNION ALL is usually not a problem, but group by might. One way to rewrite the view is to eliminate the union all. This at least would reduce some complexity.

    3) Merge both statements into one. This would be very useful for performance testing too.
    FOr example use the select from the vew, but put the subquery call for :item_no into each union all part in the main query. See if that helps, even if it wouldn't be the final solution.

    Now some code sniplets to help with approach 1) and 2).




    2) We use a when condition on the selected columns, instead a union all
    SELECT case when NVL(rmcalc_type,0) = 0 then cmpntcost_id else 0 end  as  cmpntcost_id,
      item_id,
    ...
      case when NVL(rmcalc_type,0) = 0 then cost_level else 0 end cost_level,
      SUM (cmpnt_cost), -- no case needed here!
    ...
    FROM cm_cmpt_dtl
    /* where condition is now a combination of both previous conditions */
    --WHERE NVL(rmcalc_type,0) > 0 
    --WHERE NVL(rmcalc_type,0) = 0
    WHERE NVL(rmcalc_type,0) >= 0
    GROUP BY item_id,
      whse_code,
      calendar_code,
    ...
     case when NVL(rmcalc_type,0) = 0 then cmpntcost_id else 0 end 
    ;
    Important is to include the new conditional logic in the group by clause

    1) The main query can be rewritten to normal join logic instead of subselects. This very often helps the CBO to find a better plan.
    original as posted by karthik
    select sum(cmpnt_cost)   
      from cm_cmpt_dtl_vw cm
         , cm_cldr_dtl cal  
     where cm.calendar_code = cal.calendar_code 
       and cm.period_code   = cal.period_code  
       and cost_mthd_code   = 'PMAC'  
       and :b2 between cal.start_date and cal.end_date  
       and item_id   = (
                          select item_id
                            from ic_item_mst  
                     where item_no = :b1
                 ) 
       and whse_code = (
                          select organization_code   
                      from mtl_parameters_view  
                     where organization_id = :b3 
                 );
    Join version
    select sum(cmpnt_cost)   
    from cm_cmpt_dtl_vw cm
    join cm_cldr_dtl cal  on cm.calendar_code = cal.calendar_code and cm.period_code   = cal.period_code  
    join ic_item_mst i on cm. item_id = i.item_id
    join mtl_parameters_view p on cm.whse_code = p.organization_code 
    where i.item_no = :b1
    and :b2 between cal.start_date and cal.end_date  
    and p.organization_id = :b3
    and cost_mthd_code   = 'PMAC'  
    ;
    Edited by: Sven W. on Jan 29, 2013 2:56 PM

    3) I just noticed that doing all the "grouped sums" in the view are quite useless, if you want to have just one big sum at the end. Simply replace the view with the main table then.
    select sum(cmpnt_cost)   
    from cm_cmpt_dtl cm /* source view was changed to source table */
    join cm_cldr_dtl cal  on cm.calendar_code = cal.calendar_code and cm.period_code   = cal.period_code  
    join ic_item_mst i on cm. item_id = i.item_id
    join mtl_parameters_view p on cm.whse_code = p.organization_code 
    where i.item_no = :b1
    and :b2 between cal.start_date and cal.end_date  
    and p.organization_id = :b3
    and cost_mthd_code   = 'PMAC'  
    ;
    I didn't add the where condition "WHERE NVL(rmcalc_type,0) >= 0" because I guess that rmcalc_type can not be negative. If it can, this filter would have to be added.
  • 6. Re: How to tune this query?
    Kanish Journeyer
    Currently Being Moderated
    Really thanks for all of your contribution on this part

    And very special thanks to Sven W. for given excellent path to improve the performance

    Thanks & Regards,
    Kanish

Legend

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