Re: Bad data from statspack?

From: Chuck <chuckh_at_softhome.net>
Date: 18 Apr 2003 20:16:53 GMT
Message-ID: <Xns9361A59ED8614chuckhsofthomenet@130.133.1.4>

Sybrand Bakker <gooiditweg_at_nospam.demon.nl> wrote in news:1j3u9v06irbnm8mpm7ekct6plg0cu38mv2_at_4ax.com:

> On 17 Apr 2003 17:46:15 GMT, Chuck <chuckh_at_softhome.net> wrote:
>

>>I'm reviewing statspack snapshots on an 8.1.7 database and am seeing
>>something that looks totally wacked out. The time waited on one of my
>>system events jumped 1000x between two hourly snapshots. Anyone else
>>ever seen something like this? I'm highly suspicous that something got
>>corrupted in the v$system_event view. Look at the jump in time waited 
>>between snapshots 6170 and 6171 for this event. Are there any know bugs 
>>that would cause this? TIA.
>>
>>SELECT
>>  s.snap_id
>>, s.snap_time
>>, e.event
>>, e.total_waits
>>, e.time_waited
>>FROM stats$system_event e, stats$snapshot s
>>WHERE s.snap_id IN (6160,6170,6171,6172)
>>AND s.snap_id = e.snap_id
>>AND e.event = 'db file scattered read'
>>/
>>
>>SNAP_ID SNAP_TIME       EVENT                    TOTAL_WAITS TIME_WAITED
>>------- --------------- ------------------------ ----------- -----------
>>   6160 01-Apr-03 15:00 db file scattered read       3392264     1183083
>>   6170 01-Apr-03 16:00 db file scattered read       3423393     1193985
>>   6171 01-Apr-03 17:00 db file scattered read       3792096  1845246031
>>   6172 01-Apr-03 18:00 db file scattered read       3792968  1845246570

>
>
> Never seen it. You would need to get data from tools like sar to
> demonstrate nothing else was going on on that system. Just one sample
> of 'strange' data is way insufficient to start shouting 'bug', and if
> you think it is a bug you should search Metalink and/or submit an
> Itar.
>
>
> Sybrand Bakker, Senior Oracle DBA
>
> To reply remove -verwijderdit from my e-mail address
>

How can it be anything but a bug given that the snapshots were taken 1 hour apart but the difference in wait time is over 5000 hours? Even if every session on the database (about 50) waited the entire hour on that event, it wouldn't come close to accounting for that much wait time.

The sar data shows nothing out of the normal going on at that time. There was something else that happened though. DBMS_STATS failed with a shared pool error. The client running the procedure was getting ora-4031 but the trace file it produced showed only ora-0600 [16515] which is a documented DBMS_STATS bug (# 2252824). That ora-0600 occurred between the two snapshots in question. Coincidence?

If anyone else has seen anything like this happen to v$system_event I'd like to hear about it and how you've prevented it from happening again. Upgrading to 9.2 is unfortunately not an option at this time. The application vendor has not certfied their software for 9.2. Received on Fri Apr 18 2003 - 15:16:53 CDT