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

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