Re: Index Organized table - hanging on selects
From: John Hurley <johnthehurley_at_gmail.com>
Date: Fri, 27 Apr 2012 17:59:51 -0700 (PDT)
Message-ID: <ac5ef794-f975-433c-9beb-56cc14a2ec27_at_e9g2000yqm.googlegroups.com>
On Apr 27, 4: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 17:59:51 -0700 (PDT)
Message-ID: <ac5ef794-f975-433c-9beb-56cc14a2ec27_at_e9g2000yqm.googlegroups.com>
On Apr 27, 4: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
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? Received on Fri Apr 27 2012 - 19:59:51 CDT