RE: Database Weirdness........

The first thing I would ask is what has changed on the machine. this is for dba's, developers, sysadmin, etc, to answer. Have they alter init.ora parameters, disk usage, swap space, implemented new crons/scripts/code? Secondly is to have a look at the entire sar for the time periods and see if a pattern emerges there.

I would humbly suggest that there must be a o/s or db process that is causing this resource drain and that is what you have to find.

If it recurs take a ps -ef snapshot. repeat this after the event and do you Sherlock Holmes on any differences.

Phil West - Impex IT ltd
Unix Sys Admin and Oracle Financials DBA Services

[ all opinions, unless otherwise stated, are personal and subject to change without reason or warning ! ]

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

From: Steven Monaghan [mailto:Steve.Monaghan_at_wcom.com] Sent: 28 June 2000 19:19
To: Multiple recipients of list ORACLE-L Subject: Database Weirdness........

We are running Oracle 7.3.2 on AIX with a 18G database and about 1200 connections.

We have had a situation over the last few days which has me thoroughly perplexed. The db machine slows to a crawl for about one hour. sar shows 70% sys, ps takes about 5 minutes to return, connections are extremely slow, no single process is dominating resources that we can see, you get the idea. Then, the load "disappears" and things are back to normal.

It has happened for the last 3 days without any explanation!

I ran bstat before today's episode and estat after it was over (report.txt) attached. The only item that jumped out at my fellow dba's and I was the following portion about smon and pmon timers:

• cut ----- SVRMGR> Rem System wide wait events for background processes (PMON, SMON, etc) SVRMGR> select n1.event "Event Name", 2> n1.event_count "Count", 3> n1.time_waited "Total Time", 4> round(n1.time_waited/n1.event_count, 2) "Avg Time" 5> from stats$bck_event n1 6> where n1.event_count > 0 7> order by n1.time_waited desc; Event Name Count Total Time Avg Time
  -------------------------------- ------------- ------------- -------------
  rdbms ipc message 67207 4608180 68.57 smon timer 88 999914 11362.66 pmon timer 4196 997581 237.75 db file parallel write 43301 485572 11.21
• cut -----

Can anyone offer a suggestion on what to check? It happened at 2pm Monday, 12pm Tuesday and 10am today. I cannot find any jobs running on the machine or against the database at those intervals, and I'm really starting to feel helpless.

TIA for listening and all replies are greatly appreciated.

Steve Monaghan

-- 
Author: Steven Monaghan
  INET: Steve.Monaghan_at_wcom.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

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).


*******************************************************************************
The information in this Internet e-mail is confidential and may be legally privileged.  It is intended solely for the addressee.  Access to this Internet e-mail by anyone else is unauthorised and any disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it, is prohibited and may be unlawful. 
 When addressed to our clients any opinions or advice contained in this Internet e-mail are subject to the terms and conditions expressed in any applicable documentation or market practices governing the relationship between Garban Intercapital plc and its clients.

Any views expressed in this message are those of the individual sender except where they are stated to be the views of Garban Intercapital plc.