I have a query which takes indefinite time, taking almost 60% of CPU time for 3 hours then simply hangs the PLSQL process. What I do is:

  1. Get "explain plan" for the query
  2. I noticed a "table scan" in the "explain plan" graph
  3. I added an index for the specific column
  4. I refreshed statistics for this table

I now get "explain plan" but the figures are the same and no execution optimization.

Any ideas?

  • 5
    An idea: show us your query and plan and table and index definitions.
    – dezso
    Jul 12 '12 at 11:54

The optimizer may still look at the table scan to be a good idea. To insure the optimizer uses the index, have you tried adding a hint to your query to insure the optimizer uses the index? And echoing the earlier response, more information to work with would be nice.

Not the answer you're looking for? Browse other questions tagged or ask your own question.