Re: Index statistics causing query performance problems

Thanks Sybrand

I would assume that the full table scans are bad, because whern query performance is poor, the plans use many of these, and when query performance is good, we have fewer of them and more index scans.

With regard to hints, we are currently using these on individual queries to keep our applications going. I am hoping for a solution at a lower level. I consider these hints to be patches while we are finding a better solution.

You refer to histograms - I don't think we are using these. Perhaps they may help. Anyway, I'll give some more detail about our problems:

Our Oracle version is 8.06. I'm not trying to improve the performance of our applications, I'm trying to take it back to where it was a couple of months ago. These problems have only arisen recently. I am aware of a couple of things that changed at around this time. Firstly, we upgraded from Oracle 7 to Oracle 8.06. Secondly, we implemented a number of enhancements which involved some database schema changes (not major) - 2 or three new tables and some new fields etc.

Basically, my problem is this: When someone anlyzes tables and creates statistics on indexes, including indexes 'X', 'Y' and 'Z', I notice that the performance of a number of our key queries is dreadful and that the plans (Explain Plan) use many full table scans and few indexs scans. However, if we delete only the statistics on indexes 'X', 'Y' and 'Z' our queries perform OK and the plans include fewer full table scans and more index scans. Why could indexes 'X', 'Y' and 'Z' be causing a problem? In addition, we sometimes find that if we include the /*+ RULE */ hint our performance problem for particular queries can be fixed. Since , as I mentioned above, we have only had problems for a couple of months then it is possible that nobody created these problem statistics before this time, or perhaps another underlying problem is only now causing these stastics to be problematic rather than helpful.

I would greatly appreciate any further suggestions.

Kathy Worrad
Information Technologist
CSC Australia

Sybrand Bakker wrote in message ...
>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 - 19:38:27 CDT