We are troubleshooting performance problem in a program, it takes about 5 seconds overall. Tkprof output for the below query has elapsed time 3.90 seconds almost most of the overall time is spent on this query. It does look like a heavy CPU consumption could be the cause of the problem.. Please give your suggestions. Appreciate all your help. THank you
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 3.67 3.65 0 151250 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 3.67 3.65 0 151250 0 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 83
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 HASH JOIN (cr=151253 pr=0 pw=0 time=0 us cost=1262 size=1510 card=2)
1 1 1 NESTED LOOPS OUTER (cr=5 pr=0 pw=0 time=0 us cost=4 size=96 card=1)
1 1 1 TABLE ACCESS BY INDEX ROWID DETAIL (cr=4 pr=0 pw=0 time=0 us cost=4 size=71 card=1)
1 1 1 INDEX RANGE SCAN ORD_IDX (cr=3 pr=0 pw=0 time=0 us cost=3 size=0 card=1)(object id 665908)
0 0 0 TABLE ACCESS BY INDEX ROWID ORD_POSITION (cr=1 pr=0 pw=0 time=0 us cost=0 size=25 card=1)
0 0 0 INDEX RANGE SCAN DETAIL_IDX (cr=1 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 665938)
46865 46865 46865 VIEW SALES (cr=151248 pr=0 pw=0 time=64789 us cost=1258 size=2706513 card=4107)
46865 46865 46865 SORT UNIQUE (cr=151248 pr=0 pw=0 time=40429 us cost=1258 size=802521 card=4107)
46865 46865 46865 UNION-ALL (cr=151248 pr=0 pw=0 time=3491716 us)
3720 3720 3720 HASH JOIN (cr=874 pr=0 pw=0 time=14628 us cost=243 size=663374 card=3706)
3732 3732 3732 TABLE ACCESS FULL SALES_TAB (cr=626 pr=0 pw=0 time=8023 us cost=172 size=317750 card=3875)
3722 3722 3722 TABLE ACCESS FULL EDI (cr=248 pr=0 pw=0 time=5700 us cost=71 size=359676 card=3708)
43145 43145 43145 NESTED LOOPS (cr=150371 pr=0 pw=0 time=765847 us)
43145 43145 43145 NESTED LOOPS (cr=107206 pr=0 pw=0 time=615918 us cost=1012 size=139147 card=401)
43145 43145 43145 FILTER (cr=75440 pr=0 pw=0 time=474180 us)
43145 43145 43145 HASH JOIN RIGHT OUTER (cr=75440 pr=0 pw=0 time=447796 us cost=611 size=93433 card=401)
9 9 9 TABLE ACCESS BY INDEX ROWID DATA_TAB(cr=3 pr=0 pw=0 time=19 us cost=4 size=352 card=8)
9 9 9 INDEX RANGE SCAN DTA_TYPE (cr=2 pr=0 pw=0 time=8 us cost=2 size=0 card=8)(object id 664172)
43145 43145 43145 NESTED LOOPS (cr=75437 pr=0 pw=0 time=388270 us)
43145 43145 43145 NESTED LOOPS (cr=32292 pr=0 pw=0 time=205840 us cost=607 size=84105 card=445)
43147 43147 43147 NESTED LOOPS (cr=629 pr=0 pw=0 time=52489 us cost=175 size=50730 card=445)
1 1 1 TABLE ACCESS BY INDEX ROWID DATA_TAB(cr=3 pr=0 pw=0 time=0 us cost=3 size=32 card=1)
1 1 1 INDEX RANGE SCAN DT_UNIQUE (cr=2 pr=0 pw=0 time=0 us cost=2 size=0 card=1)(object id 664171)
43147 43147 43147 TABLE ACCESS FULL SALES_TAB (cr=626 pr=0 pw=0 time=29975 us cost=172 size=3164134 card=38587)
43145 43145 43145 INDEX UNIQUE SCAN IMP_PK (cr=31663 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 664879)
43145 43145 43145 TABLE ACCESS BY INDEX ROWID IMP (cr=43145 pr=0 pw=0 time=0 us cost=1 size=75 card=1)
43145 43145 43145 INDEX UNIQUE SCAN EDIT_PK (cr=31766 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 664822)
43145 43145 43145 TABLE ACCESS BY INDEX ROWID EDIT (cr=43165 pr=0 pw=0 time=0 us cost=1 size=114 card=1)