Re: Top SQLs from Statspack Vs SQLs from v$session_lonops

From: <shweta.kaparwan_at_googlemail.com>
Date: Fri, 6 Mar 2009 03:19:39 -0800 (PST)
Message-ID: <3778b1a0-b54b-435a-a1a8-d65b5138c57e_at_y13g2000yqn.googlegroups.com>

On Mar 6, 3:18 am, ca111..._at_gmail.com wrote:
> Statspack uses thresholds for buffer gets, disk reads, executions,
> sharable memory, etc
> when capturing statements. So even very efficient statement (4 buffer
> gets per execution) will
> be captured if it gets executed 100 times per second.
> For a statement to be displayed in Statspack report (spreport)
> following needs to be true:
> - the statement should be present in both begin and end snapshots
> - it should be one of the top statements by buffer gets, disk reads,
> etc
>
> V$SESSION_LONGOPS should capture any statement with execution more
> than 5 seconds or so.
>
> So in most cases Statspack report and V$SESSION_LONGOPS will contain
> different statements
> although there will be some overlap for long-running SQL
>
> On Mar 6, 10:38 am, shweta.kapar..._at_googlemail.com wrote:
>
>
>
> > Hi All
>
> > I'm trying to understand the difference vs TOP SQL statement from
> > Statspack report  vs. SQLs  shown by  v$session_longops.
>
> > Suppose,
> > I took Statspack report between 12:00 hr to 12:15 hrs.
> > and during the same time i qurried the v$session_longops then
> > is it most likely that all SQLs which are captured from v
> > $session_longops view will also be present in the statspack report?.
>
> >  at 12:10 hrs users reported that database is dead slow. and i have
> > Statspack snap getting genrated at every 15 mins .
> > So in such scenario whether v$session_longops will be helpful?
> > or
> > Statspack report would suffice.
>
> > Kind Regards
>
> > Shweta- Hide quoted text -
>
> - Show quoted text -

It means we may miss SQLs which ran long ( >6 sec & on >10k blocks) once during Statspack report.

Regards
Shweta Received on Fri Mar 06 2009 - 05:19:39 CST