11.0.1.2405 Indexes not being used - Is this a known Problem?

[MCMartin]

In 11.0.1.2405 we have a productive DB which has run for months now. Today we noticed heavy performance degradation. After inspecting some plans we realized, that NO index was considered by the optimizer at all. After investigating some time indexes are used again. Still sporadically even for the same statement no index is used and not even considered in the plan (1 out of 10 tries).

Has anyone had this before, that the database server was out of a sudden refusing to consider an index on any table in the db?

------------- Update

I have to correct me, the problem disappeared after restart of db server. So I assume it was a one time incident. If it will reoccur I will provide more information, thanks for all answers and comments.

[Former Member]

It would be helpful if you attached a graphical plan with statistics for technical support to look at.

You mentioned above that after investigating, indexes were used again. What did you change?

[MCMartin]

I have played around with set temporary option optimization_level, then even other connections used again indexes. Before that even providing a user estimate or an index hint changed nothing. The details of the plan even showed, that only seq scan and the primary key have been considered, but the primary key was also not used where applicable. Unfortunately I have not saved a plan, I really have to make this a standard procedure 😉

[Former Member]

1. Update statistics, may be it's broken.
2. Use WITH ( index-hint ) clause and INDEX ONLY ON parameter or FORCE INDEX ( index-name ) in FROM Table_name section