RE: In an Analyze Necessary?

Rajesh,

You seem to be one of those lucky DBAs who don't have performance issues :) Just keep in mind that when statistics are NOT present and the CBO is forced by one of the many reasons, some defaults will be assumed. These defaults are absolutely invalid and incorrect. So, even if you analyze once, these stats will remain and provide the CBO with a picture which is more or less correct (unless your data is really skewed).

Rather than completely switch off stats collection, why don't you look at what's the difference between that stats between one weekend's ANALYZE and the next? If the changes are not significant, then you may choose to perform this only once a quarter or so....

One of the myths with Stats collection is 'Analyze everyday!', but the truth is 'it all depends'.

John Kanagaraj
Oracle Applications DBA
DBSoft Inc
(W): 408-970-7002

The manuals for Oracle are here: http://tahiti.oracle.com The manual for Life is here: http://www.gospelcom.net ** The opinions and statements above are entirely my own and not those of my employer or clients **

> But, me thinks, I will go ahead and disable that once a week
> analyze cron
> job. The CPU can be better utilized for the other night batch jobs.

> > A datawarehouse. There are only a few selected SQL's run
> against this
> > database. And all this SQL's are tuned to optimum during design with
> hints
> > embedded to take the least execution time. The SQL's
> currently execute
> in
> > times, much better than what the expected response time was
> by the users.
> > Would it still be required to analyze the tables? What
> other benefits
> would
> > one reap from an analyze? Assume that there are no other
> SQL's running
> > against the database other than this select few, and the data in the
> > underlying tables has been accounted for a period of 2 years during
> design.
> > Any new SQL's which would need to be added would go thru the drawing
> board.
> > Absolutely Nothing adhoc.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: John Kanagaraj
  INET: john.kanagaraj_at_hds.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).