SQL & PL/SQL

HEllo everyone,

I have got a report[Oracle 10g and later] which has 4 queries, out of which there is one because of which the report takes around 10 mins or so to get generated.
I will be pasting the report below, please help me in optimizing the report. Any help will be highly appreciated:

select
prs_id,
p.ELMNT_ID,
b.ELEMENT_DESC_N,
value
from
prl_employee_allowances p,bsh_elements b
where
p.ELMNT_ID=b.id
and p.PRS_ID = (select id from pn_persons where employee_no = :EMP_NO)
and regularity_av='PERIODIC'
and p.effective_start_date <= svd.GET_DATE
and (p.effective_end_date is null or p.effective_end_date >= svd.GET_DATE)
and allw_sign_av = 'ENTITLEMENT'
and nvl(status,'-1') <>'DELETED'
and p.ELMNT_ID not in (11,14109,12806)
and nvl(value,0)>0
and p.elmnt_id not in
(
select px.ELMNT_ID
from prl_employee_allowances px,bsh_elements bx
where --prs_id=3643 and
px.ELMNT_ID=bx.id
and px.PRS_ID = (select id from pn_persons where employee_no = :EMP_NO)
and regularity_av='PERIODIC'
and px.effective_start_date <= svd.GET_DATE
and (px.effective_end_date is null or px.effective_end_date >= svd.GET_DATE)
and allw_sign_av = 'ENTITLEMENT'
and nvl(status,'-1') <>'DELETED'
and px.ELMNT_ID not in ( 11,14109)
and nvl(px.value,0)>0
and px.ELMNT_ID not in
(
select distinct element_id from bsh_element_rules_table where element_id in
(
select py.ELMNT_ID
from prl_employee_allowances py,bsh_elements by1
where --prs_id=3643 and
py.ELMNT_ID=by1.id
and py.PRS_ID = (select id from pn_persons where employee_no = :EMP_NO)
and regularity_av='PERIODIC'
and py.effective_start_date <= svd.GET_DATE
and ( py.effective_end_date is null or py.effective_end_date >= svd.GET_DATE)
and allw_sign_av = 'ENTITLEMENT'
and nvl(status,'-1') <>'DELETED'
and py.ELMNT_ID not in ( 11,14109)
and nvl(value,0)>0
group by py.ELMNT_ID
)
)
group by px.ELMNT_ID
)
order by 1,4 desc
=======

AUTO TRACE:

Description Value

sorts (memory) 5
sorts (disk) 0
redo size 0
recursive calls 24373
physical reads 0
db block gets 0
consistent gets 48967
bytes sent via SQL*Net to client 489
bytes received via SQL*Net from client 2160
SQL*Net roundtrips to/from client 2


Even if I just run this:

select
prs_id,
p.ELMNT_ID,
b.ELEMENT_DESC_N,
value
from
prl_employee_allowances p,bsh_elements b
where
p.ELMNT_ID=b.id
and p.PRS_ID = (select id from pn_persons where employee_no = :EMP_NO)
and regularity_av='PERIODIC'
and p.effective_start_date <= svd.GET_DATE
and (p.effective_end_date is null or p.effective_end_date >= svd.GET_DATE)
and allw_sign_av = 'ENTITLEMENT'
and nvl(status,'-1') <>'DELETED'
and p.ELMNT_ID not in (11,14109,12806)
and nvl(value,0)>0
order by 1,4 desc

It takes time. so its something to do in these lines of codes...plzzzzzz help

Edited by: 1001580 on Apr 22, 2013 11:44 PM