Re: Serious Issue in oracle

From: Adam Musch <ahmusch_at_gmail.com>
Date: Mon, 25 Jan 2010 12:46:08 -0600
Message-ID: <516d05a1001251046u3b8542edy54afba107dc4df69_at_mail.gmail.com>



I concur. Querying a table under heavy transaction load (especially depending on the nature of this view and seven identical tables) could be causing lots of physical i/o being performed serially and a lot of block reconstruction for consistency.

What's a trace analysis of this hang show? What's the plan of the queries? What indexes exist on these tables? What's the definition of the view?

There's not enough data that's been shared about the nature and definition of the problem to solve it, and a deadlock is easy to find in Oracle -- it's an application bug. In some RDBMSs where locks are a scarce resource and are issued on reads, deadlocking problems can present erratically under load. Oracle is not among them.

On Mon, Jan 25, 2010 at 11:43 AM, Jeremiah Wilton <jwilton_at_bluegecko.net> wrote:
> Based on what evidence does this sound like a deadlock?  Nobody has
> mentioned ORA-60.  If what you mean instead is 'blocking lock' then there is
> also no evidence to support this assertion.
>
> Oracle has detailed instrumentation.  When something hangs, we look at the
> wait event for that session in v$session (v$session_wait in 9i and earlier).
>  There is no reason for conjecture in a product that simply tells you what
> it is hanging on in plain english.
>
> Amit, you will benefit from familiarizing yourself with wait events for
> diagnosing both performance and hanging issues.  There are many articles you
> can read to get started, but the documentation is also a great place to
> start:
>
> http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/instance_tune.htm#PFGRF02410
>
> Regards,
>
> Jeremiah Wilton
> Blue Gecko, Inc.
> http://www.bluegecko.net
>
> On Jan 25, 2010, at 6:39 AM, lyallbarbour_at_sanfranmail.com wrote:
>
>> Sounds like a deadlock situation.  No errors in the alert log?  ...Before
>> you bounced the DB?  Can you recreate the problem now that it's gone?  Maybe
>> Gremlins....  they like to cause havoc...  :)
>>
>> -----Original Message-----
>> From: AMIT VERMA <verma.labs_at_gmail.com>
>> Sent: Sun, Jan 24, 2010 2:21 pm
>>
>> ...The table was become non - responsive or hung, we had run the select
>> statement only. There is no error in the alert log ... Thus we have
>> restarted the db ... the table become responsive.
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Adam Musch
ahmusch_at_gmail.com
--
http://www.freelists.org/webpage/oracle-l
Received on Mon Jan 25 2010 - 12:46:08 CST

Original text of this message