Re: Index Organized table - hanging on selects
From: joel garry <joel-garry_at_home.com>
Date: Fri, 27 Apr 2012 14:58:45 -0700 (PDT)
Message-ID: <1f2ebf33-5a6f-475c-ba9e-8afdb517b25f_at_ot8g2000pbb.googlegroups.com>
On Apr 27, 1:08 pm, Dan <daniel.oster..._at_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
Date: Fri, 27 Apr 2012 14:58:45 -0700 (PDT)
Message-ID: <1f2ebf33-5a6f-475c-ba9e-8afdb517b25f_at_ot8g2000pbb.googlegroups.com>
On Apr 27, 1:08 pm, Dan <daniel.oster..._at_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
-- _at_home.com is bogus. http://www.utsandiego.com/news/2012/apr/19/mother-son-duo-convicted-mortgage-scam/Received on Fri Apr 27 2012 - 16:58:45 CDT