Re: index - performance problem

Also check init.ora settings like db_file_multiblock_read_count. May be in productions scans look cheaper because of a higher setting of this parameter.

Thanks,

Anjo Kolk

Ulrik Hoffmann wrote:

> Hi Sean,
>
> there may be a bug in the analyze-function on 8.05:
>
> use
>
> analyze table <TABLE>estimate (or compute) statistics for all indexed
> columns
>
> and then
>
> analyze table <TABLE> estimate (or compute) statistics
>
> If that doesn't work, what's the SGA's size? What's with the temporary
> tablespace?
>
> Bye
> Uli
>
> Sean Welsh <seanw_at_order.com.au> schrieb in im Newsbeitrag:
> PGmY3.141$dp.5249_at_nsw.nnrp.telstra.net...
> > Hello,
> >
> > I have a major drama with my Oracle 8.05 server running on NT 4.
> >
> > Symptoms are CPU running at 100% for extended periods. Query response time
> > has degraded substantially to near unuseable levels.
> >
> > My only clue is that the indexes do not seem to be being picked up by the
> > optimiser.
> >
> > For example in DEV which is working fine. Explain plan for select count(*)
> > from ref_price shows the use of an index whereas explain plan in PROD
> which
> > runs badly is suggesting that a full table scan is the plan.
> >
> > Table is 240,000 records and the index is there and it has been analysed.
> >
> > Anyone got any ideas why this would be so?
> >
> > --
> > Sean Welsh
> >
> >
> >
> >
Received on Thu Nov 18 1999 - 05:50:53 CST