Re: Index statistics causing query performance problems

Hi.

 What is your exact Oracle version? Is it 8.0 or 8.1 ( 8i )?  If you are using 8i - speek with your DBA and try to set  following parameters :

 optimizer_index_caching = 80
 optimizer_index_cost_adj = 10

 I run into a lot of similar problems with Oracle 8i and that solved it  ( instead of re-tuning a lot of SQL statements ).

 HTH. Michael.

In article <8rea02$pi4$1_at_gossamer.itmel.bhp.com.au>,   "Kathy Worrad" <worrad.kathy.kc_at_bhp.com.au> wrote:
> 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
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Oct 04 2000 - 14:28:25 CDT