Re: Index Organized table - hanging on selects

From: Dan <>
Date: Wed, 2 May 2012 13:27:47 -0700 (PDT)
Message-ID: <>

On Apr 30, 6:18 pm, John Hurley <> wrote:
> Dan:
> > My main purpose was just to see if anyone else has had (or heard of)
> > issues with IOT's in which the table occasionally performs very slowly
> > on a select (I know writes/updates are slow).  Another person in my
> > company experienced the same problem several months ago in which a
> > newly created IOT was completely inaccessible, it completely hanged on
> > select (with no locks present).  Very strange...
> I still do not think you have provided any specific details on your
> database environment level / patching / os platform etc.
> Can you produce a reproducible test case?
> Can you get a 10046 trace of a complete execution of something that
> you believe is not working well?
> With a 10046 trace and a resource profiler ( like the free orasrp )
> you have the tools to figure out what is slow and ( hopefully
> eventually ) why.
> Cary Millsaps book "Optimizing Oracle Performance" gives the road map
> for attacking stuff like this.

I am running Oracle on a Windows 64 machine, Windows 8 OS. We have Oracle standard edition.

I don't have any test case at this moment because the problems are intermittant. But I am 99% sure that the IOT in general is my problem. We've been running this same SQL statement for years with the same 4 tables joining together in a CTAS that produces a new table. It has been running fine forever, but every once in a while this happens. And it seems to happen more and more lately. I have gone thru (and had Oracle go thru on an SR) all sorts of logs, optimizer plans, AWR reports, etc and nothing pops up.

But, when a problem does happen all I need to do is recreate the IOT as a heap table with an index (all 3 fields indexed), then have the user resubmit the job. Then everything works fine and finishes in, say, 15 minutes instead of 2 days. My solution now - without some epiphany or new information - is to change the application and abandon IOTs and use indexed heap tables instead.

Dan Received on Wed May 02 2012 - 15:27:47 CDT

Original text of this message