RE: timed statistics update speed

From: ramick <ramick_at_dotster.com>
Date: Thu, 13 Jul 2006 14:49:49 -0700
Message-Id: <20060713215008.63D20398B5B@turing.freelists.org>

What is the setting of STATISTICS_LEVEL?

I wonder if there is a link between TIMED_OS_STATISTICS and the posting of stats to the underlying x$ structures?

(TIMED_OS_STATISTICS defaults to 5 seconds if STATISTICS_LEVEL is set to ALL and you are seeing this over a 5 second interval)

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jeremiah Wilton
Sent: Thursday, July 13, 2006 12:17 PM
To: oracle-l_at_freelists.org
Subject: timed statistics update speed

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

--

http://www.freelists.org/webpage/oracle-l Received on Thu Jul 13 2006 - 16:49:49 CDT