Hello all,

I am trying to capture timed statistics (session logical reads) for a large
set of SQL statements. The script I wrote opens two SQL*Plus sessions; one
to run the queries and one to collect session logical reads for the first
session.

I am finding that after the statement I am testing returns, the timed
statistics for the session continue to grow for an unpredictable amount of
time, up to five seconds (see example below).

statement: 1678786
running... complete.
12:11:01 value:6891
12:11:02 value:21118
12:11:03 value:36275
12:11:04 value:51239
12:11:05 value:65629
12:11:06 value:65629

Each value is collected from v$sesstat using a separate session after the
session being tested has completed returning rows. The only thing the first
session does is run the queries to be benchmarked. It sits idle while the
second session collects the session logical reads. There is a one second
sleep between each query of v$sesstat following each query. The query is:

select value from v$sesstat ss, v$statname sn where ss.statistic# =
sn.statistic# and sn.name = 'session logical reads' and ss.sid = :a;

Does anyone have any idea if I can force timed statistics to be flushed to
the v$ views, or if there is some amount of time after which I can be
certain that the stats are "caught up"?

Thanks,
--
Jeremiah Wilton
ORA-600 Consulting
http://ora-600.net


Version: Oracle EE 10.2.0.1.0 / Solaris 9

PS. For the curious, this analysis is being used to detect statements that
have large increases in logical reads (i.e. blown plans) after upgrade to
10g.

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