Re: Index Organized table - hanging on selects

"Dan" <daniel.ostertag_at_visaer.com> wrote in message news:83665304-70e2-41fd-b810-9de8915cac8e_at_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