query tuning

Printable View

Show 40 post(s) from this thread on one page

08-02-2004, 04:22 PM
Lily_Liu_2004

query tuning

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
08-02-2004, 04:24 PM
Mr.Hanky

:p :p :p :p :p

This is why I like Lecco

Is :p a valid charachter?

MH
08-02-2004, 04:32 PM
slimdave

Applying the to_char functions to the dates in the predicate is unnecessary, and ought to be coded as ...

Code:

... vs.load_date between :p_begindate and :p_enddate or ...

... if the dates contain no time portion. If they do, then use ...

Code:

... (vs.load_date >= :p_begindate and vs.load_date < :p_enddate+1) or ...
08-02-2004, 04:36 PM
gandolf989

SQL Navigator isn't so bad either.
Are you sure that you aren't getting a cartesian product?
What does the explain plan look like?

Code:

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
08-02-2004, 06:44 PM
raghud

This sql will not go into cartesian product. Some improvement can be done by taking the filters to the bottom and by creating function based indexes such as upper() and index on test_date,clienttype ...

Any way good luck on outer joins
08-02-2004, 06:54 PM
slimdave

I wonder also whether ...

Code:

vs.act_site_code = DECODE( UPPER( :p_servicecenter ),'ALL', vs.act_site_code,:p_servicecenter )

... might not be better as ...

Code:

(( vs.act_site_code = :p_servicecenter And Upper(:p_servicecenter) != 'ALL') Or Upper(:p_servicecenter) = 'ALL')

It would be worth benchmarking, for values of :p_servicecenter equal to and not equal to ALL.
08-03-2004, 08:54 AM
gandolf989

Quote:

Originally posted by raghud
This sql will not go into cartesian product. Some improvement can be done by taking the filters to the bottom and by creating function based indexes such as upper() and index on test_date,clienttype ...

Any way good luck on outer joins

I never said it was a catesian product. With so many tables being joined and no information about the key structure. It could be that he doesn't have a cartesian product, and that a few simple tweaks will make all the difference. But without an explain plan, and with the query having so many outer joins, how can you say what it will do? :confused:
08-03-2004, 10:31 AM
raghud

Yaa.. It is true , he need to post query plan. Since the original person did not showned up again. We hope he solved the problem himself.
08-04-2004, 02:19 PM
Lily_Liu_2004

1 Attachment(s)

Sorry for the late response. Too busy to get a chance.

The attached is the execution plan.
08-04-2004, 02:20 PM
marist89

Code:

Plan Rows ------------------------------------------------------------ ----- SELECT STATEMENT 1 NESTED LOOPS OUTER 1 NESTED LOOPS OUTER 1 NESTED LOOPS OUTER 1 NESTED LOOPS OUTER 1 NESTED LOOPS OUTER 1 NESTED LOOPS OUTER 1 NESTED LOOPS OUTER 1 NESTED LOOPS OUTER 1 NESTED LOOPS OUTER 1 NESTED LOOPS 1 VIEW V_PN_SITE 17 SORT UNIQUE 17 FILTER NESTED LOOPS OUTER NESTED LOOPS 1 NESTED LOOPS 1 TABLE ACCESS BY INDEX R PN_INFORMA 4 INDEX RANGE SCAN PN_INFORMA 4 TABLE ACCESS BY INDEX R SITE_HEADE 1 INDEX UNIQUE SCA SITE_HEADE 1 TABLE ACCESS BY INDEX R EXPRESS_SI 432 INDEX UNIQUE SCA EXPRESS_SI 432 TABLE ACCESS BY INDEX R ESSAY_TEST 38098 INDEX RANGE SCAN ESSAY_TEST 38098 TABLE ACCESS BY INDEX R LEVEL2_HEA 1607 INDEX RANGE SCAN LEVEL2_HEA 1607 TABLE ACCESS BY INDEX R EXAMINEE 27718 INDEX RANGE SCAN EXAMINEE_L 27718 VIEW V_PN_L_DET 3067 INDEX FAST FULL IDX_ESSAY_ 3067 VIEW V_PN_W_DET 3466 INDEX FAST FULL IDX_ESSAY_ 3466 VIEW V_PN_RFI_D 9676 NESTED LOOPS 9676 TABLE ACCESS FULL FORMS 79 INDEX RANGE SCAN IDX_MC_TES 72019 VIEW V_PN_AM_DE 9676 NESTED LOOPS 9676 TABLE ACCESS FULL FORMS 79 INDEX RANGE SCAN IDX_MC_TES 72019 VIEW V_PN_LI_DE 9676 NESTED LOOPS 9676 TABLE ACCESS FULL FORMS 79 INDEX RANGE SCAN IDX_MC_TES 72019 VIEW V_PN_TW_DE 9676 NESTED LOOPS 9676 TABLE ACCESS FULL FORMS 79 INDEX RANGE SCAN IDX_MC_TES 72019 VIEW V_PN_AT_DE 9676 NESTED LOOPS 9676 TABLE ACCESS FULL FORMS 79 INDEX RANGE SCAN IDX_MC_TES 72019 VIEW V_PN_OB_DE 9676 NESTED LOOPS 9676 TABLE ACCESS FULL FORMS 79 INDEX RANGE SCAN IDX_MC_TES 72019

Show 40 post(s) from this thread on one page