Re: Index Organized table - hanging on selects
Date: Wed, 2 May 2012 13:27:47 -0700 (PDT)
Message-ID: <420f3f2b-88fa-40f1-8dac-490f50c0ab0e_at_cl4g2000vbb.googlegroups.com>
On Apr 30, 6:18 pm, John Hurley <johnthehur..._at_gmail.com> 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 11.2.0.2.0 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