Re: Index statistics causing query performance problems

Hi,

I had the same problem with our new application/database. It wasn't the problem with e.g. full table scan. I found that problem is with orders of joins. It was 4 tables and depends on index statistics and histograms optimizer was right to make wrong order of joins. I'm not sure but probably selectivity of indexes for wrong for such cases. I add hint like ordered use_nl use_merge to have the same explain plan with statistics as without. And it's running fast (with also full table scan in some queries).

Rafal Czekala

In article <8rgcms$t34$1_at_gossamer.itmel.bhp.com.au>,   "Kathy Worrad" <worrad.kathy.kc_at_bhp.com.au> wrote:
> Thanks Michael, but our Oracle version is 8.06.
>
> Michael Bialik wrote in message <8rg0cf$fe5$1_at_nnrp1.deja.com>...
> >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.
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Oct 05 2000 - 03:02:57 CDT