Xref: alice comp.databases.oracle.server:83162 Path: alice!news-feed.fnsi.net!netnews.com!howland.erols.net!torn!news1.bellglobal.com!news20.bellglobal.com.POSTED!not-for-mail Sender: stark@HSE-Montreal-ppp33976.qc.sympatico.ca Newsgroups: comp.databases.oracle.server Subject: Re: Performance Questions Re-Indexes References: <878uue$7lr$1@kermit.esat.net> From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Message-ID: <87ln53at4w.fsf@HSE-Montreal-ppp33976.qc.sympatico.ca> Lines: 25 User-Agent: Gnus/5.070095 (Pterodactyl Gnus v0.95) Emacs/20.5 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Date: Thu, 03 Feb 2000 02:59:19 GMT X-Trace: news20.bellglobal.com 949546759 216.209.192.133 (Wed, 02 Feb 2000 21:59:19 EST) NNTP-Posting-Date: Wed, 02 Feb 2000 21:59:19 EST "Keith Jamieson" 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