Re: Index Organized table - hanging on selects
Date: Mon, 30 Apr 2012 10:51:59 -0700 (PDT)
Message-ID: <7bf84d62-990f-4c39-b124-2925dc883ed8_at_w5g2000vbp.googlegroups.com>
On Apr 29, 4:36 am, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> "Dan" <daniel.oster..._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 Lewishttp://jonathanlewis.wordpress.com
> Oracle Core (Apress 2011)http://www.apress.com/9781430239543
Thanks for all the help on this.
I might have misled everyone when I said "locking". The point is that
there are no locks happening. If there were locks I could easily
address them. The query just marches along at a very slow pace but
nothing seems amiss while running. It seems like a lock but it
clearly isn't - especially when this is a select query, not an update
or insert. I'm currently in the process of analyzing my AWR reports
to see if there is anything out of place. I'm not doing a 'select *'
on the table, I just used that as an example of how selects were VERY
slow. If I do select * or "select * from IOT where", it is equally
slow.
Per one person's advice, it may be best to abandon the IOT in general. We aren't doing full scans on it but we are stepping thru almost every record getting a particular score for everyone in our driving table. It's accessed by an index but maybe a heap table would be better...not sure yet.
My main purpose was just to see if anyone else has had (or heard of) issues with IOT's in which the table occasionally performs very slowly on a select (I know writes/updates are slow). Another person in my company experienced the same problem several months ago in which a newly created IOT was completely inaccessible, it completely hanged on select (with no locks present). Very strange...
Dan Received on Mon Apr 30 2012 - 12:51:59 CDT