Database Tuning (MOSC)

MOSC Banner

wrong indexes has been used after collect table statistics

edited Sep 29, 2009 3:35AM in Database Tuning (MOSC) 8 commentsAnswered
 Hi everyone,

We run into something funny here.

We have a Oracle 10.2.0.4 database. It used by SAP.

We have about 300millions row in one big table, and recently we collected statistics for this table. However suddently, the performance went down. A daily task usually only take 30mins but it takes 18hours now. Somehow after collected statistics, Oracle start to use another index, a wrong index, for this query.

Eventually we have to restore the old statistics, then Oracle picked up the right index to do the job. And performance went back to normal.

Any suggestions? Any clue what is the root cause?

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

Instructions for posting

×

1. Select a discussion category from the picklist.


2. Enter a title that clearly identifies the subject of your question.


3. In the body, insert detailed information, including Oracle product and version.


Please abide by the Oracle Community guidelines and refrain from posting any customer or personally identifiable information (PI/CI).


Cloud / Fusion customers - Our Cloud community has moved! Please go to Cloud Customer Connect .


New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center