Re: Index Organized table - hanging on selects

From: Jonathan Lewis <>
Date: Sun, 29 Apr 2012 09:36:52 +0100
Message-ID: <>

"Dan" <> wrote in message
| > 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.


Jonathan Lewis
Oracle Core (Apress 2011)
Received on Sun Apr 29 2012 - 03:36:52 CDT

Original text of this message