SQL & PL/SQL

Can anyone tell me how can i tune this query.

SELECT DISTINCT pha.segment1 "PO"
,rsh.receipt_num "Receipt #"
,rsh.creation_date "Create Date"
,pa.full_name "Receiver"
,req_det.req_on_po "Requestor on PO"
,req_det.req_on_req "Requestor on Req"
,req_det.preparer_name "Preparer Name"
,recp_amt.amt "$"
,recp_date.need_date "Need By Date"
,tran_typ.trans_type "Transaction Type"
,0 "Release Num"
,pha.type_lookup_code "Document Type"
FROM apps.po_headers_all pha
,apps.rcv_transactions rt
,apps.per_all_people_f pa
,apps.rcv_shipment_headers rsh
,(SELECT DISTINCT pa2.full_name req_on_po
,pa3.full_name req_on_req
,pda.po_header_id header_id
,pda.org_id org_id
,pa1.full_name preparer_name
FROM apps.po_distributions_all pda
,apps.per_all_people_f pa1
,apps.per_all_people_f pa2 ,apps.per_all_people_f pa3
,apps.po_req_distributions_all prda
,apps.po_requisition_lines_all prla
,apps.po_requisition_headers_all prha
WHERE pa1.person_id=prha.preparer_id
AND pa2.person_id=pda.deliver_to_person_id
AND pa3.person_id=prla.to_person_id
AND pda.req_distribution_id=prda.distribution_id
AND prda.requisition_line_id=prla.requisition_line_id
AND prla.requisition_header_id=prha.requisition_header_id
)req_det
,(SELECT pla.po_header_id header_id
,pla.org_id org_id
,pll.need_by_date need_date
,sum(pll.quantity_received*pla.unit_price)amt
FROM apps.po_lines_all pla
,apps.po_line_locations_all pll
WHERE pla.po_line_id=pll.po_line_id
GROUP BY pla.po_header_id
,pla.org_id
,pll.need_by_date
)recp_amt
,(SELECT DISTINCT pla.po_header_id header_id
,pla.org_id org_id
,pll.need_by_date need_date
FROM apps.po_lines_all pla
,apps.po_line_locations_all pll
WHERE pla.po_line_id=pll.po_line_id
)recp_date
,(SELECT DISTINCT rt.transaction_type trans_type
,pha.po_header_id header_id
,pha.org_id org_id
FROM apps.po_headers_all pha
,apps.rcv_transactions rt
WHERE pha.po_header_id=rt.po_header_id
AND rt.transaction_type in ('RECEIVE','RETURN TO VENDOR','RETURN TO RECEIVING','CORRECT')
)tran_typ
WHERE pha.org_id=3763
AND rt.employee_id=pa.person_id
AND pha.po_header_id=rt.po_header_id
AND rt.shipment_header_id=rsh.shipment_header_id
AND pha.po_header_id=req_det.header_id
AND pha.org_id=req_det.org_id
AND pha.po_header_id=recp_amt.header_id
AND pha.org_id=recp_amt.org_id
AND pha.po_header_id=recp_date.header_id
AND pha.org_id=recp_date.org_id
AND pha.po_header_id=tran_typ.header_id
AND pha.org_id=tran_typ.org_id
AND pha.type_lookup_code='STANDARD'
AND trunc(pha.approved_date)between to_date('01-Apr-2007')and to_date('30-Jun-2007')


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=14146 Card=3647 By
tes=1827147)

1 0 HASH (UNIQUE) (Cost=14146 Card=3647 Bytes=1827147)
2 1 HASH (GROUP BY) (Cost=14146 Card=3647 Bytes=1827147)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'RCV_TRANSACTIONS' (T
ABLE) (Cost=5 Card=5 Bytes=135)

4 3 NESTED LOOPS (Cost=13755 Card=3647 Bytes=1827147)
5 4 NESTED LOOPS (Cost=11173 Card=779 Bytes=369246)
6 5 NESTED LOOPS (Cost=9551 Card=540 Bytes=237060)
7 6 NESTED LOOPS (Cost=8470 Card=540 Bytes=218700)
8 7 NESTED LOOPS (Cost=8261 Card=63 Bytes=23688)
9 8 NESTED LOOPS (Cost=8160 Card=40 Bytes=1400
0)

10 9 NESTED LOOPS (Cost=8097 Card=25 Bytes=83
25)

11 10 NESTED LOOPS (Cost=8046 Card=17 Bytes=
5066)

12 11 NESTED LOOPS (Cost=8010 Card=12 Byte
s=3156)

13 12 NESTED LOOPS (Cost=7986 Card=8 Byt
es=1824)

14 13 NESTED LOOPS (Cost=7970 Card=8 B
ytes=1600)

15 14 NESTED LOOPS (Cost=7943 Card=8
Bytes=1440)

16 15 NESTED LOOPS (Cost=7927 Card
=8 Bytes=1256)

17 16 NESTED LOOPS (Cost=7911 Ca
rd=8 Bytes=1024)

18 17 NESTED LOOPS (Cost=7895
Card=8 Bytes=832)

19 18 NESTED LOOPS (Cost=787
1 Card=8 Bytes=568)

20 19 TABLE ACCESS (BY IND
EX ROWID) OF 'PO_HEADERS_ALL' (TABLE) (Cost=7855 Card=8 Byte
s=392)

21 20 INDEX (RANGE SCAN)
OF 'GECM_PO_HEADERS_ALL_N98' (INDEX) (Cost=47 Card=19873)

22 19 TABLE ACCESS (BY IND
EX ROWID) OF 'PO_HEADERS_ALL' (TABLE) (Cost=2 Card=1 Bytes=2
2)

23 22 INDEX (UNIQUE SCAN
) OF 'PO_HEADERS_U1' (INDEX (UNIQUE)) (Cost=1 Card=1)

24 18 TABLE ACCESS (BY INDEX
ROWID) OF 'PO_DISTRIBUTIONS_ALL' (TABLE) (Cost=4 Card=1 Byt
es=33)

25 24 INDEX (RANGE SCAN) O
F 'PO_DISTRIBUTIONS_N3' (INDEX) (Cost=2 Card=5)

26 17 TABLE ACCESS (BY INDEX R
OWID) OF 'PO_REQ_DISTRIBUTIONS_ALL' (TABLE) (Cost=2 Card=1 B
ytes=24)

27 26 INDEX (UNIQUE SCAN) OF
'PO_REQ_DISTRIBUTIONS_U1' (INDEX (UNIQUE)) (Cost=1 Card=1)

28 16 TABLE ACCESS (BY INDEX ROW
ID) OF 'PO_REQUISITION_LINES_ALL' (TABLE) (Cost=2 Card=1 Byt
es=29)

29 28 INDEX (UNIQUE SCAN) OF '
PO_REQUISITION_LINES_U1' (INDEX (UNIQUE)) (Cost=1 Card=1)

30 15 TABLE ACCESS (BY INDEX ROWID
) OF 'PO_REQUISITION_HEADERS_ALL' (TABLE) (Cost=2 Card=1 Byt
es=23)

31 30 INDEX (UNIQUE SCAN) OF 'PO
REQUISITIONHEADERS_U1' (INDEX (UNIQUE)) (Cost=1 Card=1)

32 14 TABLE ACCESS (BY INDEX ROWID)
OF 'PO_LINES_ALL' (TABLE) (Cost=5 Card=1 Bytes=20)

33 32 INDEX (RANGE SCAN) OF 'PO_LI
NES_U2' (INDEX (UNIQUE)) (Cost=2 Card=6)

34 13 TABLE ACCESS (BY INDEX ROWID) OF
'PO_LINES_ALL' (TABLE) (Cost=2 Card=1 Bytes=28)

35 34 INDEX (RANGE SCAN) OF 'PO_LINE
S_U2' (INDEX (UNIQUE)) (Cost=2 Card=6)

36 12 TABLE ACCESS (BY INDEX ROWID) OF '
PER_ALL_PEOPLE_F' (TABLE) (Cost=3 Card=1 Bytes=35)

37 36 INDEX (RANGE SCAN) OF 'PER_PEOPL
E_F_PK' (INDEX (UNIQUE)) (Cost=2 Card=1)

38 11 TABLE ACCESS (BY INDEX ROWID) OF 'PE
R_ALL_PEOPLE_F' (TABLE) (Cost=3 Card=1 Bytes=35)

39 38 INDEX (RANGE SCAN) OF 'PER_PEOPLE_
F_PK' (INDEX (UNIQUE)) (Cost=2 Card=1)

40 10 TABLE ACCESS (BY INDEX ROWID) OF 'PER_
ALL_PEOPLE_F' (TABLE) (Cost=3 Card=1 Bytes=35)

41 40 INDEX (RANGE SCAN) OF 'PER_PEOPLE_F_
PK' (INDEX (UNIQUE)) (Cost=2 Card=1)

42 9 TABLE ACCESS (BY INDEX ROWID) OF 'PO_LIN
E_LOCATIONS_ALL' (TABLE) (Cost=3 Card=2 Bytes=34)

43 42 INDEX (RANGE SCAN) OF 'PO_LINE_LOCATIO
NS_N1' (INDEX) (Cost=2 Card=2)

44 8 TABLE ACCESS (BY INDEX ROWID) OF 'PO_LINE_
LOCATIONS_ALL' (TABLE) (Cost=3 Card=2 Bytes=52)

45 44 INDEX (RANGE SCAN) OF 'PO_LINE_LOCATIONS
_N1' (INDEX) (Cost=2 Card=2)

46 7 TABLE ACCESS (BY INDEX ROWID) OF 'RCV_TRANSA
CTIONS' (TABLE) (Cost=5 Card=9 Bytes=261)

47 46 INDEX (RANGE SCAN) OF 'RCV_TRANSACTIONS_N4
' (INDEX) (Cost=2 Card=9)

48 6 TABLE ACCESS (BY INDEX ROWID) OF 'RCV_SHIPMENT
_HEADERS' (TABLE) (Cost=2 Card=1 Bytes=34)

49 48 INDEX (UNIQUE SCAN) OF 'RCV_SHIPMENT_HEADERS
_U1' (INDEX (UNIQUE)) (Cost=1 Card=1)

50 5 TABLE ACCESS (BY INDEX ROWID) OF 'PER_ALL_PEOPLE
_F' (TABLE) (Cost=3 Card=1 Bytes=35)

51 50 INDEX (RANGE SCAN) OF 'PER_PEOPLE_F_PK' (INDEX
(UNIQUE)) (Cost=2 Card=1)

52 4 INDEX (RANGE SCAN) OF 'RCV_TRANSACTIONS_N4' (INDEX
) (Cost=2 Card=9)





Statistics
----------------------------------------------------------
166 recursive calls
0 db block gets
419 consistent gets
30 physical reads
0 redo size
511 bytes sent via SQL*Net to client
2093 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed


Thanks

SS

Message was edited by:
SS