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