| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Analyze table/index -- Interval ??
Prem - "It depends". The first issue is why are you analyzing them? Some
possible reasons:
1. Because the table has changed enough that the CBO could use fresh
statistics? Then it depends on how volatile your data is. One danger is that
at some point the fresh statistics may change the execution plan for an
existing query.
2. To save a history of your table growth for reorg planning or capacity
planning?
3. To locate migrated (chained) rows?
I analyze tables weekly, but monthly would be fine. My process is manual,
but it would be easy enough to automate. If you are just getting started
with this, consider DBMS_STATS. It has some issues with 8.1.6, but Oracle
has announced that DBMS_STATS is the future, not ANALYZE. It is much easier
to automate, I'm guessing a DBMS_JOB could be used. Also, if you are using
partitions, DBMS_STATS handles them correctly.
Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com
-----Original Message-----
Sent: Monday, June 02, 2003 12:36 AM
To: Multiple recipients of list ORACLE-L
Guys,
1# At what interval, Tables/Indexes need to be analyzed ?
Is there any rule of thumb ?
2# Can it be scheduled as a job in the O/S or DB ?
OR
Has it be done manually without scheduling ?
which of the above is preferred ?
The ENV is 8.1.6/Win2k.
Regards,
Jp.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Prem Khanna J
INET: jprem_at_kssnet.co.jp
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services ---------------------------------------------------------------------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).
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services ---------------------------------------------------------------------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). Received on Mon Jun 02 2003 - 08:24:39 CDT
 |
 |