Re: Index statistics causing query performance problems

On Wed, 4 Oct 2000 15:03:13 +1100, "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
>

If I understand you correctly you are talking about indexes not being used while according to you they should. IMO the only thing that counts is the actual number of logical reads. Quite often people mistakingly think a full table scan is bad, while in fact the full table scan is consuming less I/O than the index scan. So probably you first should be absolutely sure the fts is really bad..
Secondly, for every statement parsed the optimizer makes an estimate about how selective an index is. If you have situations where the distribution of the data is extreemly skewed, the index statistics *only* are not going to help, and you need to compute histograms in addition, usually 'for all indexed columns size <any bucket size>' (in addition to the normale analyze table compute statistics) Generally that should resolve the problem. You can even do that in a call to analyze_schema, using the method_opt parameter. Thirdly, as probably only a few individual statements are affected, I would not even consider such a rash measure as deleting the statistics. Instead I would use the /*+first_rows*/ or even the /*+rule*/ hint, but usually first_rows results in the same execution path as the rule hint. That approach will definitely use indexes and it will also not affect adhoc statements. Finally, are you sure you did *everything* to optimize the statement? (or verified the Harrison book about high-performance tuning)

Regards,

Sybrand Bakker, Oracle DBA Received on Wed Oct 04 2000 - 01:04:59 CDT