SQL tunning advise

[candyman]

anyone has any suggestion on the below query:

select * from
(select distinct
ps.ctr_no
,NVL(table_dec,'XXXX') DEPT_ID
,NVL(b.ba_no,'XXXX') ba_no
,NVL(actpay_cd,'') actpay_cd
,NVL(actrec_cd,'') actrec_cd
,NVL(affil_cd,'') affil_cd
,NVL(ap_adr_seq,'') ap_adr_seq
,NVL(ar_adr_seq,'') ar_adr_seq
,ps.net_payable
,ps.net_receivable
,ps.net_adjustment
,ps.ref_invc_no
,c.clsf
,max(decode(naid.ba_use, 'W','W','C')) ba_use
from party_settle ps, paystatn p, codetabs ct, ba_hdr b, contract c, naid
where ps.ps_no = 'LAM'
and ps.busn_dt = TO_DATE('01-JUL-2003','DD-MON-YYYY')
and p.ps_no = 'LAM'
and p.busn_dt = TO_DATE('01-JUL-2003','DD-MON-YYYY')
and ps.ctr_no = p.mstr_ctr_no
and c.ps_no = 'LAM'
and ps.ctr_no = c.ctr_no
and c.edt_to >= TO_DATE('01-JUL-2003','DD-MON-YYYY')
and c.edt_from<= TO_DATE('01-JUL-2003','DD-MON-YYYY')
and '177' = ct.table_id(+)
and p.loc_code = ct.table_key(+)
and substr(c.ctr_pty_no,1,6) = b.ba_no(+)
and rtrim(c.ctr_pty_no) = rtrim(naid.na_id)
and nvl(c.doi_disb_fl,'N') <> 'Y'
group by ps.ctr_no, table_dec, b.ba_no, actpay_cd, actrec_cd, affil_cd,
ap_adr_seq, ar_adr_seq, ps.net_payable, ps.net_receivable, ps.net_adjustment
, ps.ref_invc_no, c.clsf
union
select distinct
ps.ctr_no
,NVL(table_dec,'XXXX') DEPT_ID
,NVL(b.ba_no,'XXXX') ba_no
,NVL(actpay_cd,'') actpay_cd
,NVL(actrec_cd,'') actrec_cd
,NVL(affil_cd,'') affil_cd
,NVL(ap_adr_seq,'') ap_adr_seq
,NVL(ar_adr_seq,'') ar_adr_seq
,ps.net_payable
,ps.net_receivable
,ps.net_adjustment
,ps.ref_invc_no
,c.clsf
,max(decode(naid.ba_use, 'W','W','C')) ba_use
from party_settle ps, ctr_man_adjust cma, codetabs ct, ba_hdr b, contract c,
naid
where ps.ps_no = 'LAM'
and ps.busn_dt = TO_DATE('01-JUL-2003','DD-MON-YYYY')
and cma.ps_no = 'LAM'
and cma.busn_dt= TO_DATE('01-JUL-2003','DD-MON-YYYY')
and ps.ctr_no = cma.ctr_no
and c.ps_no = 'LAM'
and ps.ctr_no = c.ctr_no
and c.edt_to >= TO_DATE('01-JUL-2003','DD-MON-YYYY')
and c.edt_from<= TO_DATE('01-JUL-2003','DD-MON-YYYY')
and '177' = ct.table_id(+)
and cma.loc_code = ct.table_key(+)
and substr(c.ctr_pty_no,1,6) = b.ba_no(+)
and rtrim(c.ctr_pty_no) = rtrim(naid.na_id)
and nvl(c.doi_disb_fl,'N') <> 'Y'
group by ps.ctr_no, table_dec, b.ba_no, actpay_cd, actrec_cd, affil_cd,
ap_adr_seq, ar_adr_seq, ps.net_payable, ps.net_receivable, ps.net_adjustment
, ps.ref_invc_no, c.clsf
)
order by 1,2

[KenEwald]

Can you post the "expain plan"?

On first glance...
Why do you need a distinct if you're grouping (via max())?

[lesstjm]

Make sure you are passing bind variables.

[balajiyes]

max(decode(naid.ba_use, 'W','W','C')) ba_use

wht does MAX() do in this DECODE