Re: After table analyzed query runs 8 hours longer

From: Patrick Burns <pburns13_at_hotmail.com>
Date: 21 Sep 2004 07:44:42 -0700
Message-ID: <61a26a0d.0409210644.79d1b8b0@posting.google.com>

Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1095730444.18891_at_yasure>...
> Daniel Roy wrote:
>
> > From what I see, your slow-running query accesses multiple tables, but
> > you say that you calculated stats for ONE table. Be consistent and
> > analyze the stats for ALL the tables involved. Please post at least
> > the query.
> >
> > Daniel
>
> And, in addition, do not use DBMS_UTLITY procedures ... you should be
> using DBMS_STATS.

Sorry for the confusion all of the tables were previously analyzed but the one that I just did. I will attempt to use DBMS_STATS.

explain plan set statement_id= 'x' for select /*+ CHOOSE */ distinct substr(ea.alte_post_id,7,3),aj.actg_evnt,tj.dtyp,tj.ref_dtyp_cat,sum(tj.dllr_am) FROM MF_TJ tj, MF_AJ aj, ST_EXP_AUTY_MNTN ea, MF_SYS_STGS syst

WHERE 	(ea.actg_cetr_post_id = syst.actg_cetr_post_id) AND
      	(aj.orgn_typ = 'SH') AND
    	(tj.uid_lo = aj.tj_uid_lo) AND
	(TJ.OPLK > '01-JAN-99') AND
      	((((aj.actg_evnt = 'SP02') or (aj.actg_evnt = 'SP03') or
(aj.actg_evnt = 'SPN3') or (aj.actg_evnt = 'SP07') or 			(aj.actg_evnt
= 'SPN7') or (aj.actg_evnt = 'AR04') or (aj.actg_evnt = 'AR05') or
(aj.actg_evnt = 'AR06') or 			(aj.actg_evnt = 'AR10') or (aj.actg_evnt
= 'AR11') or (aj.actg_evnt ='AR12'))
      	OR (aj.actg_evnt = 'SP05' AND ((tj.line_typ = 'PP') or
(tj.line_typ = 'PR')))
      	OR (aj.actg_evnt = 'SPN5' AND ((tj.line_typ = 'PP') or
(tj.line_typ = 'PR')))
      	OR (aj.actg_evnt = 'SP08' AND ((tj.line_typ = 'PP') or
(tj.line_typ = 'PR')))
      	OR (aj.actg_evnt = 'SPN8' AND ((tj.line_typ = 'PP') or
(tj.line_typ = 'PR')))) AND
     	AJ.PATN = substr(EA.PATN_ID,7,4) AND
      	(aj.fund = substr(EA.FUND_ID,7,instr(ea.fund_id,'&',1,3)-instr(ea.fund_id,'&',1,2)-1)
AND
       	AJ.BBFY = substr(EA.FUND_ID,instr(ea.fund_id,'&',1,3)+1,instr(ea.fund_id,'&',1,4)-instr(ea.fund_id,'&',1,3)-1))
AND
       	aj.orgn = substr(EA.ORGN_ID,instr(ea.orgn_id,'&',1,2)+1,instr(ea.orgn_id,'&',1,3)-instr(ea.orgn_id,'&',1,2)-1))

GROUP BY substr(ea.alte_post_id,7,3),aj.actg_evnt,tj.dtyp,tj.ref_dtyp_cat UNION
SELECT /*+ CHOOSE */ substr(ea.alte_post_id,7,3), aj.actg_evnt,'ZZ',' ', sum(tj.dllr_am)
FROM MF_TJ tj, MF_AJ aj, ST_EXP_AUTY_MNTN ea, MF_SYS_STGS syst

WHERE  	(ea.actg_cetr_post_id = syst.actg_cetr_post_id) AND
    	(aj.orgn_typ = 'SH') AND
    	(tj.uid_lo = aj.tj_uid_lo) AND
	(TJ.OPLK > '01-JAN-99') AND
    	(aj.actg_evnt = 'AR04' or aj.actg_evnt = 'AR05' or aj.actg_evnt =
'AR06' or
	aj.actg_evnt = 'AR10' or aj.actg_evnt = 'AR11' or aj.actg_evnt =
'AR12') AND
	(tj.ref_doc_num is null and tj.cat = 'CR') AND
    	AJ.PATN = substr(EA.PATN_ID,7,4) AND
 	aj.fund = substr(EA.FUND_ID,7,instr(ea.fund_id,'&',1,3)-instr(ea.fund_id,'&',1,2)-1)
AND
      	AJ.BBFY = substr(EA.FUND_ID,instr(ea.fund_id,'&',1,3)+1,instr(ea.fund_id,'&',1,4)-instr(ea.fund_id,'&',1,3)-1)
AND
      	aj.orgn = substr(EA.ORGN_ID,instr(ea.orgn_id,'&',1,2)+1,instr(ea.orgn_id,'&',1,3)-instr(ea.orgn_id,'&',1,2)-1)
AND 	tj.dtyp||tj.doc_num||rtrim(tj.actg_lnum_ch) in
		(select substr(cr.dtyp_id,7)||cr.doc_num||rtrim(crln.lnum)
		from MF_CR cr, MF_CR_LN crln
		where cr.uidy = crln.parn_of_line_id and crln.rord_line_id is not

null)
GROUP BY substr(ea.alte_post_id,7,3), aj.actg_evnt, ' ',' '; Received on Tue Sep 21 2004 - 09:44:42 CDT