on 08-06-2008 6:01 PM
Hello,
We have a strange situation with our COPA CE4XXXX table. When database statistics are generated (DB20) for this table, we immediately notice a very significant performance drop when selecting from this table. In orer to correct this, if we adjust/rebuild the index of the table using the database utility tool, it seems to resolve the issue. Either way, the indexes that Oracle chooses for the query we are monitoring does not change, but performance is decreased after new statistics are generated.
The Oracle version is 9.2.0.8.0, and the statistics were generated with method 'E' sample 'P25'.
Any clues?
Do you mean you notice that during the creation of the statistics or after you created them? Is there any different in the access plan?
Markus
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Markus,
This is after the creation of the statistics. I found that in our QA environment, that it was choosing a more inefficient index after stats were generated. In our prod environment, the same index appears to be used before and after the stats are generated (according to our basis). I will try to do a retest in production once there is a window to do so, and update the thread.
Thanks.
Dan
Hello Daniel,
i am not quite sure about Oracle 9i, DB20 and the index rebuild.
Please check the options which are given to the REBUILD index command by SAP.
http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_18a.htm#SQLRF00805
> COMPUTE STATISTICS Clause
> Specify COMPUTE STATISTICS if you want to collect statistics at relatively little cost during the rebuilding of an index.
>
> If you create an index using another index (instead of a table), the original index might not provide adequate statistical information. Therefore, Oracle generally uses the base table to compute the statistics, which will improve the statistics but may negatively affect performance.
I am only sure about Oracle 10g (because there is always a compute).. maybe your gathered statistics with method 'E' sample 'P25' are not good enough.. or this is maybe a bug.
But without the execution plans, statistic data and the exact commands that are generated in the background.. it is really hard to say.
Regards
Stefan
Seems we've solved the problem.
We had two custom indexes created for our CE4XXXX table. It seems that one of them was causing problems, and somehow influencing Oracle to chose the most inefficient index. We eliminated this index, and now everything appears to be running properly after new statistics are generated.
Thanks.
Hi Daniel,
well it seemed the CBO didn't had proper information about table and indexes.
Due to the fact CBO is a piece of software and it has its quirks and flaws -
giving proper and sufficient information and KNOWING your data is essential
to get very often the best execution plan.
Be sure you have also histogram statistics calculated. At least when you don't use bind variables
in your statements they often help to give a better clue to the CBO (i.e. useful info on data distribution of attributes
in case of strange default values of fields, using improper data types (number for a date type ,
character for a number ...) .
So it's not the question to have index A or index B but how you can force CBO
to choose the right one. Sometimes it's not as easy as removing an index (because
it's needed by other components).
Bye
yk
User | Count |
---|---|
83 | |
24 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.