Re: Top SQLs from Statspack Vs SQLs from v$session_lonops
Date: Thu, 5 Mar 2009 19:18:36 -0800 (PST)
Message-ID: <a1de809b-a50e-420b-b0e7-05feca93b488_at_v1g2000prd.googlegroups.com>
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
Received on Thu Mar 05 2009 - 21:18:36 CST