Index statistics causing query performance problems

I am currently working on an application which has an Oracle (8.something) back-end. The tables are analyzed weekly, which I think should be adequate in our situation. However, we frequently have query performance problems (queries that run out of table space before they complete - if you use Explain Plan on these, the cost-based optimizer seems to choose an extremely difficult strategy, using few indexes and heaps of full table scans). Some of our problems can be solved (in the short term) by deleting statistics on certain indexes. Our DBA knows that these certain statistics can cause problems, but doesn't know why. (Note that these statistics appear from time to time when another of the users analyzes tables for his own needs). I am an applications programmer, not a DBA, so can someone suggest to me, in simple terms, some actions that can be taken to solve or investigate our problems. I have asked our DBA and all he can say is "I don't know".

Kathy Worrad
Information Technologist
CSC Australia Received on Tue Oct 03 2000 - 23:03:13 CDT