query tuning

[Lily_Liu_2004]

Hi DBAs,

The following is a query thrown to my desk by a developer.
He claimed it took much longer time than it should be.
I did something but no significant changes.

all yr input is greatly appreciated.

select vs.act_site_code, vs.site_name, vs.pn, vs.test_date,
vs.load_date, vs.match_date, vs.billing_code, vs.hours24_flag, vc.code, vc.name, decode(vc.client_type,'E', 'Education', 'B', 'Business') client_type,
vf.litho_code, vf.lname, vf.ssn_id,
vr.test_type rfi,
vm.test_type am,
vl.test_type l,
vw.test_type w,
vi.test_type li,
vt.test_type tw,
va.test_type at,
vb.test_type ob
from wkbus.v_pn_site vs,
wkbus.v_pn_client vc,
wkbus.v_pn_folder_detail vf,
wkbus.v_pn_rfi_detail vr,
wkbus.v_pn_am_detail vm,
wkbus.v_pn_l_detail vl,
wkbus.v_pn_w_detail vw,
wkbus.v_pn_li_detail vi,
wkbus.v_pn_tw_detail vt,
wkbus.v_pn_at_detail va,
wkbus.v_pn_ob_detail vb
where vs.act_site_code = decode(upper(: p_servicecenter), 'ALL', vs.act_site_code, : p_servicecenter) and
((to_char(vs.load_date,'yymmdd') between to_char(: p_begindate,'yymmdd') and to_char(: p_enddate,'yymmdd')) or
(to_char(vs.match_date,'yymmdd') between to_char(: p_begindate,'yymmdd') and to_char(: p_enddate,'yymmdd')) ) and
vs.pn = vc.pn and
vs.pn = vf.pn(+) and
vf.pn = vr.pn(+) and vf.litho_code = vr.litho_code(+) and vf.doc_id = vr.doc_id(+) and
vf.pn = vm.pn(+) and vf.litho_code = vm.litho_code(+) and vf.doc_id = vm.doc_id(+) and
vf.pn = vl.pn(+) and vf.litho_code = vl.litho_code(+) and vf.doc_id = vl.doc_id(+) and
vf.pn = vw.pn(+) and vf.litho_code = vw.litho_code(+) and vf.doc_id = vw.doc_id(+) and
vf.pn = vi.pn(+) and vf.litho_code = vi.litho_code(+) and vf.doc_id = vi.doc_id(+) and
vf.pn = vt.pn(+) and vf.litho_code = vt.litho_code(+) and vf.doc_id = vt.doc_id(+) and
vf.pn = va.pn(+) and vf.litho_code = va.litho_code(+) and vf.doc_id = va.doc_id(+) and
vf.pn = vb.pn(+) and vf.litho_code = vb.litho_code(+) and vf.doc_id = vb.doc_id(+)
order by vs.test_date