RE: timed statistics update speed
- From: "ramick" <ramick@xxxxxxxxxxx>
- To: <jeremiah@xxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 13 Jul 2006 14:49:49 -0700
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Jeremiah Wilton
Sent: Thursday, July 13, 2006 12:17 PM
To: oracle-l@xxxxxxxxxxxxx
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.
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
