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.

asked 27 Jan '12, 12:13

Martin's gravatar image

Martin
8.6k119151237
accept rate: 14%

edited 15 Apr '14, 09:20

Mark%20Culp's gravatar image

Mark Culp
23.4k9132275

1

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?

(27 Jan '12, 16:22) Glenn Paulley
Replies hidden
1

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 ;-)

(28 Jan '12, 05:21) Martin

  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
permanent link

answered 28 Jan '12, 06:52

AlexeyK77's gravatar image

AlexeyK77
70761224
accept rate: 8%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×137
×27
×27

question asked: 27 Jan '12, 12:13

question was seen: 1,171 times

last updated: 15 Apr '14, 09:20