Considing performance, should I rebulid index and execute DBMS_UTILITY.ANALYZE_SCHEMA periodically?

[xiongye_2]

Considing performance, should I rebulid index and execute DBMS_UTILITY.ANALYZE_SCHEMA periodically? Thanks !!

[Andrew S Babb]

Statistics only really need to rebuilt on objects that have volatile data. If you have a table that is static, as far as indexed columns are concerned, then you will not need to analyze the index more than once.

Rebuilding indexes is required for volatile objects, and for objects that are right sided (that is new records added to the right hand side of an index such as indexes based on Oracle Sequences that do not cycle).

[Aashish Raj]

Hi,

you can analyze only indexes after their rebuild by running
analyze index compute statistics;
or analyze index estimate statitics sample 40 percent;

If the indexes are big >400 MB you can improve the analyze performance by setting sort area size before running analyze command.
alter session set sort_area_size=100000000;

AR

[Simeon Fox]

No simple answer to this one. You have to balance the overhead of running the analyze commands against the volatility of the data. If changing data is not re-analyzed it could mean the Cost Based Optimizer is not finding the best access path. Another issue, as Raj points out is whether, for a given table, to analyze all rows (compute) or a sample (estimate). Also bear in mind that when using Oracle CBO you must monitor query performance. We have found that more certain queries can perform much worse under CBO than the supposedly near-obselete rule based optimizer.

[Raynald Boucher]

A periodic rebuild of indexes is always good.

It permits reorganization of the tablespace, recovery of unused space, shortens full index scans and helps avoid dynamic extent allocation.

When you do, rebuild all indexes of a given tablespace.

If you are using CBO, Oracle recommends rebuilding statistics periodically. They also recommmend estimating statistics using 10% sample (but they have a disclaimer on that).

As Simeon said, a well tuned application using rule will beat out an untuned app using CBO.