Re: Index Organized table - hanging on selects
From: joel garry <joel-garry_at_home.com>
Date: Wed, 2 May 2012 13:41:05 -0700 (PDT)
Message-ID: <cac91681-caf0-4b6b-834d-f657ab6470c6_at_s7g2000yqm.googlegroups.com>
On May 2, 1:27 pm, Dan <daniel.oster..._at_visaer.com> wrote:
> 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
Date: Wed, 2 May 2012 13:41:05 -0700 (PDT)
Message-ID: <cac91681-caf0-4b6b-834d-f657ab6470c6_at_s7g2000yqm.googlegroups.com>
On May 2, 1:27 pm, Dan <daniel.oster..._at_visaer.com> wrote:
> 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
Was it raining? http://lmgtfy.com/?q=%22According+to+the+end-users%27+observations%2C+if+it+was+raining+heavily+Monday%22
jg
-- _at_home.com is bogus. Always remember to mount a scratch engineering student. http://www.utsandiego.com/news/2012/may/02/tp-student-says-dea-agents-left-him-alone-in/Received on Wed May 02 2012 - 15:41:05 CDT