Oracle Database Discussions

below qurrery is runing slow. it's taking 16 mins. Can any one please help me out why it 's taking this much time.

SELECT t1.ebiz_cntrl_no,
COUNT (DISTINCT (tl.mast_ebiz_lp_no)) pallet_count,
trl.pro_no, trl.trailer_type, t1.comp_id
FROM dirp_trn_ebiztask t1,
dirp_trn_lp tl,
dirp_trn_ebiztask t2,
dirp_trn_trailer trl
WHERE t1.task_type = 'PICK'
AND t1.act_qty > 0
AND t1.act_end_date IS NOT NULL
AND t1.ebiz_contlp_no = tl.ebiz_lp_no
AND t2.task_type = 'SHIP'
AND t2.ebiz_lp_no = tl.mast_ebiz_lp_no
AND t2.ebiz_trailer_no = trl.ebiz_trailer_no
AND t1.COMP_ID = tl.COMP_ID
AND t1.COMP_ID = t2.COMP_ID
AND t1.COMP_ID = trl.COMP_ID
GROUP BY t1.ebiz_cntrl_no, pro_no, trailer_type, t1.comp_id



Rows Row Source Operation
------- ---------------------------------------------------
3 TABLE ACCESS BY INDEX ROWID CCOL$ (cr=8 pr=0 pw=0 time=459 us)
3 INDEX RANGE SCAN I_CCOL1 (cr=5 pr=0 pw=0 time=473 us)(object id 54)

********************************************************************************

SELECT t1.ebiz_cntrl_no,
COUNT (DISTINCT (tl.mast_ebiz_lp_no)) pallet_count,
trl.pro_no, trl.trailer_type, t1.comp_id
FROM dirp_trn_ebiztask t1,
dirp_trn_lp tl,
dirp_trn_ebiztask t2,
dirp_trn_trailer trl
WHERE t1.task_type = 'PICK'
AND t1.act_qty > 0
AND t1.act_end_date IS NOT NULL
AND t1.ebiz_contlp_no = tl.ebiz_lp_no
AND t2.task_type = 'SHIP'
AND t2.ebiz_lp_no = tl.mast_ebiz_lp_no
AND t2.ebiz_trailer_no = trl.ebiz_trailer_no
AND t1.COMP_ID = tl.COMP_ID
AND t1.COMP_ID = t2.COMP_ID
AND t1.COMP_ID = trl.COMP_ID
GROUP BY t1.ebiz_cntrl_no, pro_no, trailer_type, t1.comp_id

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.12 0.17 0 0 0 0
Execute 3 0.00 0.00 0 0 0 0
Fetch 22964 275.41 2520.61 833760 1108408 9 344389
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 22970 275.53 2520.79 833760 1108408 9 344389

Misses in library cache during parse: 1

Optimizer mode: CHOOSE
Parsing user id: 67 (GSMOLAP)

Rows Row Source Operation
------- ---------------------------------------------------
114793 SORT GROUP BY (cr=409403 pr=280247 pw=16436 time=662980659 us)
1780310 HASH JOIN (cr=409403 pr=277866 pw=14055 time=638715201 us)
1780433 HASH JOIN (cr=408274 pr=270127 pw=7440 time=614559870 us)
496477 TABLE ACCESS BY INDEX ROWID DIRP_TRN_EBIZTASK (cr=160500 pr=59306 pw=0 time=384814524 us)
496477 INDEX RANGE SCAN NI_TRN_EBIZTASK_INDX4 (cr=3393 pr=3206 pw=0 time=44214117 us)(object id 149238)
1786441 HASH JOIN (cr=247774 pr=210821 pw=7440 time=234883291 us)
1789215 TABLE ACCESS FULL DIRP_TRN_EBIZTASK (cr=207388 pr=163028 pw=0 time=209363676 us)
1458129 TABLE ACCESS FULL DIRP_TRN_LP (cr=40386 pr=40353 pw=0 time=20426305 us)
59742 TABLE ACCESS FULL DIRP_TRN_TRAILER (cr=1129 pr=1124 pw=0 time=1563491 us)


Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: CHOOSE
114793 SORT (GROUP BY)
1780310 HASH JOIN
1780433 HASH JOIN
496477 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'DIRP_TRN_EBIZTASK' (TABLE)
496477 INDEX MODE: ANALYZED (RANGE SCAN) OF
'NI_TRN_EBIZTASK_INDX4' (INDEX)
1786441 HASH JOIN
1789215 TABLE ACCESS MODE: ANALYZED (FULL) OF
'DIRP_TRN_EBIZTASK' (TABLE)
1458129 TABLE ACCESS MODE: ANALYZED (FULL) OF 'DIRP_TRN_LP'
(TABLE)
59742 TABLE ACCESS MODE: ANALYZED (FULL) OF 'DIRP_TRN_TRAILER'
(TABLE)



Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 7655 0.00 0.01
db file sequential read 57297 0.37 549.32
latch free 2 0.00 0.00
direct path write temp 1506 0.63 18.86
direct path read temp 1506 0.11 9.90
db file scattered read 13784 0.24 251.02
SQL*Net message from client 7654 1.09 10.09
********************************************************************************

ALTER SESSION SET EVENTS '10046 trace name context forever, level 8'


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.02 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.02 0 0 0 0

Misses in library cache during parse: 0
Parsing user id: 67 (GSMOLAP)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 21.89 21.89



********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 4 0.12 0.17 0 0 0 0
Execute 4 0.00 0.02 0 0 0 0
Fetch 22964 275.41 2520.61 833760 1108408 9 344389
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 22972 275.53 2520.81 833760 1108408 9 344389

Misses in library cache during parse: 1

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 7656 0.00 0.01
SQL*Net message from client 7655 21.89 31.98
db file sequential read 57297 0.37 549.32
latch free 2 0.00 0.00
direct path write temp 1506 0.63 18.86
direct path read temp 1506 0.11 9.90
db file scattered read 13784 0.24 251.02


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 10 0.03 0.02 0 0 0 0
Execute 129 0.09 0.17 0 0 0 0
Fetch 278 0.02 0.26 28 446 0 1102
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 417 0.14 0.46 28 446 0 1102

Misses in library cache during parse: 10
Misses in library cache during execute: 10
4 user SQL statements in session.
129 internal SQL statements in session.
133 SQL statements in session.
1 statement EXPLAINed in this session.
********************************************************************************
Trace file: biprod_ora_23463.trc
Trace file compatibility: 10.01.00
Sort options: default

1 session in tracefile.
4 user SQL statements in trace file.
129 internal SQL statements in trace file.
133 SQL statements in trace file.
12 unique SQL statements in trace file.
1 SQL statements EXPLAINed using schema:
GSMOLAP.prof$plan_table
Default table was used.
Table was created.
Table was dropped.
113796 lines in trace file.
3724 elapsed seconds in trace file.