All of our database tables are analyzed with the exception of 1. The
queries run in Choose mode and when we analyze that one table a query
goes from 1.5 hours to 9.5 hours. All of the other queries run faster
but this one. I have tried creating all kinds of indexes with no
success.

----the table in question is analyzed here (MF_AJ - 40 millon
rows)-----
SELECT STATEMENT Cost=258592081
SORT UNIQUE
UNION-ALL
SORT GROUP BY
HASH JOIN
TABLE ACCESS FULL MF_SYS_STGS
NESTED LOOPS
HASH JOIN
INDEX FAST FULL SCAN I3_ST_EXP_AUTY_MNTN
TABLE ACCESS FULL MF_AJ
TABLE ACCESS BY INDEX ROWID MF_TJ
INDEX UNIQUE SCAN PK_TJ
SORT GROUP BY
HASH JOIN
HASH JOIN
TABLE ACCESS FULL MF_SYS_STGS
NESTED LOOPS
HASH JOIN
INDEX FAST FULL SCAN I3_ST_EXP_AUTY_MNTN
TABLE ACCESS FULL MF_AJ
TABLE ACCESS BY INDEX ROWID MF_TJ
INDEX UNIQUE SCAN PK_TJ
VIEW VW_NSO_1
SORT UNIQUE
HASH JOIN
TABLE ACCESS FULL MF_CR_LN
TABLE ACCESS FULL MF_CR

-----table not analyzed------------
SELECT STATEMENT Cost=335577
SORT UNIQUE
UNION-ALL
SORT GROUP BY
NESTED LOOPS
HASH JOIN
INDEX FAST FULL SCAN I3_ST_EXP_AUTY_MNTN
NESTED LOOPS
TABLE ACCESS FULL MF_AJ
TABLE ACCESS BY INDEX ROWID MF_TJ
INDEX UNIQUE SCAN PK_TJ
TABLE ACCESS FULL MF_SYS_STGS
SORT GROUP BY
HASH JOIN
TABLE ACCESS FULL MF_SYS_STGS
NESTED LOOPS
HASH JOIN
INDEX FAST FULL SCAN I3_ST_EXP_AUTY_MNTN
HASH JOIN
TABLE ACCESS FULL MF_AJ
TABLE ACCESS FULL MF_TJ
VIEW VW_NSO_1
SORT UNIQUE
HASH JOIN
TABLE ACCESS FULL MF_CR_LN
TABLE ACCESS FULL MF_CR

On 17 Sep 2004 12:59:02 -0700, pburns13 (AT) hotmail (DOT) com (Patrick Burns)
wrote:

Wchich query? Which indexes? Which Analyze command? Which version of
Oracle?


--
Sybrand Bakker, Senior Oracle DBA

structure different.

single indexes ass well as multiple indexes.

estimate statistics.

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

Daniel Roy wrote:

And, in addition, do not use DBMS_UTLITY procedures ... you should be
using DBMS_STATS.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)

Daniel Morgan <damorgan@x.washington.edu> wrote


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,t j.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,inst r(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,inst r(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,t j.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,inst r(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,inst r(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, ' ',' ';