advantage and disadvantage of analyze

Hello:

I would like to know that what are the advantage and disadvantage
of analyzing the dynamic tables and indexes every day.
Also, which optimizer_mode should I use?


Thanks.

Check my reply to "question on CBO reliability " further down this same page.

Rule based optimization will be discontinued in the future versions of Oracle. If you are just starting out, your best bet is to stay away from it.

The advantage to analyzing every day is you capture the most up-to-date picture of your data. The optimizer will usually perform better with recent statistics.

The down-side is that you have to analyze every night. Analyzing takes CPU and I/O. If you have other jobs that run at night, this may introduce contention in your nightly processing.

Which option is better to use?

Better is a relative term. Better for my environment is analyze once a week. Better for my last environment was analyze every day. It all depends on the frequency your data changes.

Data is put into my database on a adaily basis. So, do u think I need to run analyze everyday.

Thanks.

Quote:

---

Originally posted by pst
Data is put into my database on a adaily basis. So, do u think I need to run analyze everyday.

Thanks.

---

Depends on the % of data that you add. If you are only increasing the table size by 1% each day, then no, you don't have to analyze every day. That being said, the more often you can analyze, the better...