Oracle-L: Statspack reliability

From: Stephane Paquette <stephane.paquette_at_standardlife.ca>
Date: Mon, 14 Apr 2003 09:43:40 -0800
Message-ID: <F001.0058087B.20030414094340@fatcity.com>

Hi all,

We're running statspack each hour on all the production instances, Oracle 8.1.7.2 AIX 4.3.3
Last week, on one database, we had 3 numbers that were 100000 times bigger than usual.

2 were about df file sequential read that were over 100000 times the average.
Top 5 Wait Events

~~~~~~~~~~~~~~~~~                                             Wait     %
Total
Event                                               Waits  Time (cs)   Wt

Time

-------------------------------------------- ------------ ------------ -----
--
db file sequential read                           126,541 ############
99.99
db file scattered read                            491,345      154,658


.01

rdbms ipc reply                                     2,983       24,293


.00

library cache lock                                    367        7,197


.00

db file parallel write                              9,551        5,981

.00

Top 5 Wait Events

~~~~~~~~~~~~~~~~~                                             Wait     %
Total
Event                                               Waits  Time (cs)   Wt

Time

-------------------------------------------- ------------ ------------ -----
--
db file sequential read                           262,496 ############
99.99
db file scattered read                            144,216       65,771


.00

rdbms ipc reply                                     1,788       14,919


.00

direct path read                                   17,478        9,586


.00

db file parallel read                               1,701        4,465

.00

The third wait is
Top 5 Wait Events

~~~~~~~~~~~~~~~~~                                             Wait     %
Total
Event                                               Waits  Time (cs)   Wt

Time

-------------------------------------------- ------------ ------------ -----
--
SQL*Net more data to client                     3,041,574 ############
99.99
db file scattered read                             79,465       67,945


.00

db file sequential read                            56,900       50,106


.00

rdbms ipc reply                                       497        3,530


.00

db file parallel write                              1,224          971

.00

Here is the wait time per snap_id from the statpack tables

      5693  1882138496 db file sequential read
      5692       22114 db file sequential read

      5819  3803314081 db file sequential read
      5818  1892117288 db file sequential read

      5765  1900925255 SQL*Net more data to client
      5764       66883 SQL*Net more data to client

I checked on the unix side.
For the first Oracle wait I had a spike in the IO performance graph (disk read/write per sec) but it was for writing. For the second and third Oracle wait, I had nothing on the unix side.

So is that a glitch in statspack or where else can I look ?

Stephane Paquette
Administrateur de bases de donnees
Database Administrator
Standard Life
www.standardlife.ca
Tel. (514) 925-7187
stephane.paquette_at_standardlife.ca <mailto:stephane.paquette_at_standardlife.ca>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Paquette
  INET: stephane.paquette_at_standardlife.ca

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Mon Apr 14 2003 - 12:43:40 CDT