Re: confused between explain plan and analyze table

"Harry Boswell" <NOhboswel1SPAM_at_bellsouth.net> wrote in message news:mh76nv8dhb0imian33avqpmmc48v2dir5k_at_4ax.com...
> On Thu, 25 Sep 2003 11:53:16 +0100, "Niall Litchfield"
> <n-litchfield_at_audit-commission.gov.uk> wrote:
>
> >"Francesco" <marchioni.NOSPAM_at_libero.it> wrote in message
> >news:vEwcb.345346$Ny5.11000373_at_twister2.libero.it...
> >> Hi all,
> >> I'm studying Oracle tuning, I'm a bit confused because of
> >> so many tools available. Can anybody give a minimalist explanation
> >> about when it's useful to "explain plan" for a statement and
> >> when is useful to "analyze a table" and compute statistics ?
> >
> >Analyze table gathers statistics about the table for use by the cost
 based
> >optimiser in determining an appropriate access path to the data, that is
 it
> >populates things like the number of blocks used by the table, the number
 of
> >rows, how selective an index is etc. It is thus useful to run when
> >
> >1. you are using the cost based optimiser.
> >2. stats are non-existent or out of date.
> >
>
> Is there somewhere to look to see when the last Analyze Table was run?

the last_analyzed column in dba_tables will do it :(

It is perhaps worth clarifying what 'out of date' might mean.

It can mean a time based measure in which case last_analyzed might be useful, or you might just schedule dbms_stats using the scheduler your prefer (we do this).

It probably means a volume of change based measure in which case enabling monitoring on tables and only analyzing when stale would bemore appropriate (we probably should do this).

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************