Index Organized table - hanging on selects

We are running Oracle 11g and seem to be encountering an occasional
problem in which index organized tables (IOT) are hanging for a long
time upon a select. Locks are the causing it, and it eventually
returns data, but even if doing a "select * from iot_table" it may
take 5 minutes to return rows in TOAD, when this normally is
instantaneous.

We have a large query in which one of the tables joined to is an IOT.
Every once in a while (maybe 5% of the time) a query will slow up to
the point that a 1 hour query could take 2 days. It doesn't happen
all the time. Yesterday on a particularly troublesome query I
recreated the IOT as a heap table with an index and the query ran as
expected.

Has anyone encountered issues like this? IOT's are suppsed to be
faster while reading? Is there a bug or a problem perhaps with how
I'm building the table?

Thanks,
Dan

On Apr 27, 1:08*pm, Dan <daniel.oster...@visaer.com> wrote:
> We are running Oracle 11g and seem to be encountering an occasional
> problem in which index organized tables (IOT) are hanging for a long
> time upon a select. *Locks are the causing it, and it eventually
> returns data, but even if doing a "select * from iot_table" it may
> take 5 minutes to return rows in TOAD, when this normally is
> instantaneous.
>
> We have a large query in which one of the tables joined to is an IOT.
> Every once in a while (maybe 5% of the time) a query will slow up to
> the point that a 1 hour query could take 2 days. *It doesn't happen
> all the time. *Yesterday on a particularly troublesome query I
> recreated the IOT as a heap table with an index and the query ran as
> expected.
>
> Has anyone encountered issues like this? *IOT's are suppsed to be
> faster while reading? *Is there a bug or a problem perhaps with how
> I'm building the table?
>
> Thanks,
> Dan

You might go into enterprise manager and see what the waiting is all
about.

jg
--
@home.com is bogus.
http://www.utsandiego.com/news/2012/...mortgage-scam/

On Fri, 27 Apr 2012 13:08:43 -0700, Dan wrote:

> We are running Oracle 11g and seem to be encountering an occasional
> problem in which index organized tables (IOT) are hanging for a long
> time upon a select. Locks are the causing it, and it eventually returns
> data, but even if doing a "select * from iot_table" it may take 5
> minutes to return rows in TOAD, when this normally is instantaneous.

Dan, Oracle doesn't use locking for query processing. The only exception
from that rule is distributed transaction processing. That makes locks
unlikely culprit for the "hanging".

>
> We have a large query in which one of the tables joined to is an IOT.
> Every once in a while (maybe 5% of the time) a query will slow up to the
> point that a 1 hour query could take 2 days. It doesn't happen all the
> time. Yesterday on a particularly troublesome query I recreated the IOT
> as a heap table with an index and the query ran as expected.

Do you have performance tuning and diagnostic pack licenses? I would
strongly advise using dbms_xplan.display_awr and even creating a tuning
task. Also, tracing the application may be a good idea. DBMS_MONITOR has
many options that would enable you to trace an application of your
choice. What you are interested in are wait events and the time spent
waiting. Also, taking a look at the AWR reports for the period in which
the slowness occurs would probably be a good idea, too.

>
> Has anyone encountered issues like this? IOT's are suppsed to be faster
> while reading? Is there a bug or a problem perhaps with how I'm
> building the table?
>
> Thanks,
> Dan

Dan, tables are collections of rows and are usually not going anywhere,
if things are done right. Table is a place, application is a process.
Tables are not slow or fast, the applications that access the table are.
Saying that IOT is "faster while reading" is a nonsense. Index organized
tables are essentially indexes. They are supposed to speed up
applications which access a relatively small part of the table. IOT is
not meant for "select * from IOT". If the table is regularly accessed
using the full table scan, IOT is a bad choice. IOT is normally used for
small, frequently accessed tables of the "phone book" type. You want to
access just the information for the particular phone type. Ask the
application designer to justify his decision to use IOT.

Also, please post your RDBMS version and platform. That may help people
who would try to help you.




--
http://mgogala.byethost5.com

On Apr 27, 4:08*pm, Dan <daniel.oster...@visaer.com> wrote:
> We are running Oracle 11g and seem to be encountering an occasional
> problem in which index organized tables (IOT) are hanging for a long
> time upon a select. *Locks are the causing it, and it eventually
> returns data, but even if doing a "select * from iot_table" it may
> take 5 minutes to return rows in TOAD, when this normally is
> instantaneous.
>
> We have a large query in which one of the tables joined to is an IOT.
> Every once in a while (maybe 5% of the time) a query will slow up to
> the point that a 1 hour query could take 2 days. *It doesn't happen
> all the time. *Yesterday on a particularly troublesome query I
> recreated the IOT as a heap table with an index and the query ran as
> expected.
>
> Has anyone encountered issues like this? *IOT's are suppsed to be
> faster while reading? *Is there a bug or a problem perhaps with how
> I'm building the table?
>
> Thanks,
> Dan

What kind of locks exactly and from where?

Try putting a 10046 trace on a session that is "hanging" on the select
command ...

If you do not have OEM on this machine lots of sample scripts that
will help diagnose locks "who is waiting for what and why" ...

As mladen ( I think ) noted what exact version and patched up to what
on which platform?

On Friday, April 27, 2012 4:08:43 PM UTC-4, Dan wrote:
> We are running Oracle 11g and seem to be encountering an occasional
> problem in which index organized tables (IOT) are hanging for a long
> time upon a select. Locks are the causing it, and it eventually
> returns data, but even if doing a "select * from iot_table" it may
> take 5 minutes to return rows in TOAD, when this normally is
> instantaneous.
>
> We have a large query in which one of the tables joined to is an IOT.
> Every once in a while (maybe 5% of the time) a query will slow up to
> the point that a 1 hour query could take 2 days. It doesn't happen
> all the time. Yesterday on a particularly troublesome query I
> recreated the IOT as a heap table with an index and the query ran as
> expected.
>
> Has anyone encountered issues like this? IOT's are suppsed to be
> faster while reading? Is there a bug or a problem perhaps with how
> I'm building the table?
>
> Thanks,
> Dan



having slow performance on an IOT is indicative of not using the index properly. You must be looking for EXACT MATCH lookups on the entire key or at least the leading edge(s) and BEWARE of implicit data type conversions..

Example:

when you realize that:

to_date( SOME_DATE, 'dd-mon-yyyy')

is equivalent to:

to_date(
TO_CHAR(some_date),
'dd-mon-yyyy' )

and that that is really equivalent to:

to_date(
to_char(some_date,'NLS_DATE_FORMAT'),
'dd-mon-yyyy' )

You may not be getting the results you were looking for either...

Also something like datefield = to_char(sysdate,'NLS_DATE_FORMAT') is evaluating a date to a character - can you say FTS?

"Dan" <daniel.ostertag@visaer.com> wrote in message
news:83665304-70e2-41fd-b810-9de8915cac8e@e15g2000vbt.googlegroups.com...
| > Locks are the causing it, and it eventually
| returns data, but even if doing a "select * from iot_table" it may
| take 5 minutes to return rows in TOAD, when this normally is
| instantaneous.

Five minutes is a very long time even for all the "well-known" issues that
might have this type of effect.

How do you know that "locks are causing it" ?

Next time you see this problem in a simple "select * from iot_table" check
what the session is waiting on (v$session_wait, v$session_wait_history, or
snapshots on v$session_event), and pull the execution plan from memory.

|
| We have a large query in which one of the tables joined to is an IOT.
| Every once in a while (maybe 5% of the time) a query will slow up to
| the point that a 1 hour query could take 2 days. It doesn't happen
| all the time. Yesterday on a particularly troublesome query I
| recreated the IOT as a heap table with an index and the query ran as
| expected.
|

This could simply be a case of a change in execution plan - you haven't
told us anything to rule out the possibility for the join.
Rebuilding as a heap table with PK gives you a whole new set of stats.

| Has anyone encountered issues like this? IOT's are suppsed to be
| faster while reading? Is there a bug or a problem perhaps with how
| I'm building the table?

Unlikely, but hard to tell since you haven't told us how you are building
the table.

--
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com
Oracle Core (Apress 2011)
http://www.apress.com/9781430239543

On Apr 29, 4:36*am, "Jonathan Lewis" <jonat...@jlcomp.demon.co.uk>
wrote:
> "Dan" <daniel.oster...@visaer.com> wrote in message
>
> news:83665304-70e2-41fd-b810-9de8915cac8e@e15g2000vbt.googlegroups.com...
> | > Locks are the causing it, and it eventually
> | returns data, but even if doing a "select * from iot_table" it may
> | take 5 minutes to return rows in TOAD, when this normally is
> | instantaneous.
>
> Five minutes is a very long time even for all the "well-known" issues that
> might have this type of effect.
>
> How do you know that "locks are causing it" ?
>
> Next time you see this problem in a simple "select * from iot_table" *check
> what the session is waiting on (v$session_wait, v$session_wait_history, or
> snapshots on v$session_event), and pull the execution plan from memory.
>
> |
> | We have a large query in which one of the tables joined to is an IOT.
> | Every once in a while (maybe 5% of the time) a query will slow up to
> | the point that a 1 hour query could take 2 days. *It doesn't happen
> | all the time. *Yesterday on a particularly troublesome query I
> | recreated the IOT as a heap table with an index and the query ran as
> | expected.
> |
>
> This could simply be a case of a change in execution plan - you haven't
> told us anything to rule out the possibility for the join.
> Rebuilding as a heap table with PK gives you a whole new set of stats.
>
> | Has anyone encountered issues like this? *IOT's are suppsed to be
> | faster while reading? *Is there a bug or a problem perhaps with how
> | I'm building the table?
>
> Unlikely, but hard to tell since you haven't told us how you are building
> the table.
>
> --
> Regards
>
> Jonathan Lewishttp://jonathanlewis.wordpress.com
> Oracle Core (Apress 2011)http://www.apress.com/9781430239543

Thanks for all the help on this.
I might have misled everyone when I said "locking". The point is that
there are no locks happening. If there were locks I could easily
address them. The query just marches along at a very slow pace but
nothing seems amiss while running. It seems like a lock but it
clearly isn't - especially when this is a select query, not an update
or insert. I'm currently in the process of analyzing my AWR reports
to see if there is anything out of place. I'm not doing a 'select *'
on the table, I just used that as an example of how selects were VERY
slow. If I do select * or "select * from IOT where", it is equally
slow.

Per one person's advice, it may be best to abandon the IOT in
general. We aren't doing full scans on it but we are stepping thru
almost every record getting a particular score for everyone in our
driving table. It's accessed by an index but maybe a heap table would
be better...not sure yet.

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...

Dan

On 30.04.2012 19:51, Dan wrote:
> I might have misled everyone when I said "locking". The point is that
> there are no locks happening. If there were locks I could easily
> address them. The query just marches along at a very slow pace but
> nothing seems amiss while running. It seems like a lock but it
> clearly isn't - especially when this is a select query, not an update
> or insert.

If there is a lot concurrent update activity on the table slow access to
Undo might be an explanation.

> 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...

The first step would be to look at execution plans as has been
suggested. What did that turn up?

Kind regards

robert


--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

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.

On Apr 30, 6:18*pm, John Hurley <johnthehur...@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

On May 2, 1:27*pm, Dan <daniel.oster...@visaer.com> wrote:
> On Apr 30, 6:18*pm, John Hurley <johnthehur...@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...vily+Monday%22

jg
--
@home.com is bogus.
Always remember to mount a scratch engineering student.
http://www.utsandiego.com/news/2012/...-him-alone-in/

Dan:

# I am running Oracle 11.2.0.2.0 on a Windows 64 machine, Windows 8
OS.

On windows 8? That has not been released into production yet has it?
Your users are running stuff against a database on a pre release
version of windows?

I only have one 11.2 system now in production but it is patched up to
11.2.0.3.2 ( just out in April PSU ).

A whole whole bunch of fixes/changes for 11.2.0.3 versus 11.2.0.2 ...
could be you are running into a bug fixed already but you are not
patched up to the most recent level?

Standard edition unfortunately probably does not allow the OEM
automatic SQL monitoring that could catch stuff ... but as noted
already getting a 10046 trace is one of the most effective ways of
getting/solving performance problems.

Please take the time to purchase and read Cary's book ... it gives a
repeatable methodology for attacking and solving problems.

Sure changing the tables definition is one way out perhaps and may
just be avoiding a bug that perhaps is already fixed by oracle ... but
don't you really want to know for sure what this is?

| "Dan" <daniel.ostertag@visaer.com> wrote in message
news:420f3f2b-88fa-40f1-8dac-490f50c0ab0e@cl4g2000vbb.googlegroups.com...
|
| 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.
|

The obvious guess is that you have an intermittent change in execution
plan.
Have you already eliminated this possibility ?

Have you enabled extended trace for this statement to see where the time is
going and what the plan is.
I suggest the following two lines before the code that runs the statement:

alter session set events '10132 trace name context forever';
alter session set events '10046 trace name context forever, level 12';

with the following afterwards

alter session set events '10046 trace name context off';
alter session set events '10132 trace name context off';

Then accumulate the trace files until you have at least one fast one and
one slow one and check
a) The difference in work done
b) The execution plans reported.


--
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com
Oracle Core (Apress 2011)
http://www.apress.com/9781430239543