Re: Performance Questions Re-Indexes

"Keith Jamieson" <jamiesonk_at_phoenix.ie> writes:

> Q2) If we switched to the cost based optimiser, could I assume that as long
> as the statistics were regenerated on a regular basis, then
> the performance would be better than the rule based optimiser.

There's no substitute for actually understanding what query plans are being used and what indexes are being used. Do an explain plan on every query starting with the queries using the most disk access.

Find any query using a full table scan to access only a subset of the records. Determine if any existing indexes are being ignored or whether new indexes are needed or whether the query can be written more intelligently.

One danger with the cost based optimizer and regular analyzes is that the decisions it makes can change spontaneously. Unpredictability is the bane of proper software engineering. I prefer to analyze tables manually when there's a problem rather than throw the whole application to Oracle periodically and cross my fingers that it does the right things automatically. All it takes is for Oracle to make one wrong decision and my whole application could fall over.

--
greg Received on Wed Feb 02 2000 - 20:59:19 CST