cancel
Showing results for 
Search instead for 
Did you mean: 

Generating statistics has negative impact on performance

Former Member
0 Kudos

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?

Accepted Solutions (1)

Accepted Solutions (1)

markus_doehr2
Active Contributor
0 Kudos

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

Former Member
0 Kudos

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

stefan_koehler
Active Contributor
0 Kudos

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

Former Member
0 Kudos

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.

Former Member
0 Kudos

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

Answers (0)