Re: Serious Issue in oracle

What I said below....

Yep, query gv$session and filter based on username, schemaname, and sql_id if you know it. Pay close attention to the event and blocking session columns. That will tell you if there is some sort of deadlock occurring.

On Mon, Jan 25, 2010 at 1:46 PM, Adam Musch <ahmusch_at_gmail.com> wrote:

> 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
>
>
>

-- 
Thomas Roach
813-404-6066
troach_at_gmail.com

--
http://www.freelists.org/webpage/oracle-l