Re: Database Weirdness........

--0-1804289383-962283625=:7731
Content-Type: text/plain; charset=us-ascii

 Look at your sar stats and see if you began swapping. On SGI the command is sar -w. There should be an option to look at yesterday's data if you know where your admin is storing the sar files.

  Steven Monaghan <Steve.Monaghan_at_wcom.com> wrote: 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).


---------------------------------
Do You Yahoo!?
Get Yahoo! Mail - Free email you can access from anywhere!
--0-1804289383-962283625=:7731
Content-Type: text/html; charset=us-ascii



<P>&nbsp;Look at your sar stats and see if you began swapping. On SGI the command is sar -w. There should be an option to look at yesterday's data if you know where your admin is storing the sar files.<BR>

<P>&nbsp; <B><I>Steven Monaghan &lt;Steve.Monaghan_at_wcom.com&gt;</I></B> wrote: <BR>

<BLOCKQUOTE style="BORDER-LEFT: #1010ff 2px solid; MARGIN-LEFT: 5px; PADDING-LEFT: 5px">We are running Oracle 7.3.2 on AIX with a 18G database and about 1200<BR>connections.<BR><BR>We have had a situation over the last few days which has me thoroughly<BR>perplexed. The db machine slows to a crawl for about one hour. sar shows<BR>70% sys, ps takes about 5 minutes to return, connections are extremely slow,<BR>no single process is dominating resources that we can see, you get the idea.<BR>Then, the load "disappears" and things are back to normal.<BR><BR>It has happened for the last 3 days without any explanation!<BR><BR>I ran bstat before today's episode and estat after it was over (report.txt)<BR>attached. The only item that jumped out at my fellow dba's and I was the<BR>following portion about smon and pmon timers:<BR><BR>----- cut -----<BR>SVRMGR&gt; Rem System wide wait events for background processes (PMON, SMON,<BR>etc)<BR>SVRMGR&gt; select n1.event "Event Name",<BR>2&gt; n!

1.event_count "Count",<BR>3&gt; n1.time_waited "Total Time",<BR>4&gt; round(n1.time_waited/n1.event_count, 2) "Avg Time"<BR>5&gt; from stats$bck_event n1<BR>6&gt; where n1.event_count &gt; 0<BR>7&gt; order by n1.time_waited desc;<BR>Event Name Count Total Time Avg Time<BR>-------------------------------- ------------- ------------- -------------<BR>rdbms ipc message 67207 4608180 68.57<BR>smon timer 88 999914 11362.66<BR>pmon timer 4196 997581 237.75<BR>db file parallel write 43301 485572 11.21<BR>----- cut -----<BR><BR>Can anyone offer a suggestion on what to check? It happened at 2pm Monday,<BR>12pm Tuesday and 10am today. I cannot find any jobs running on the machine<BR>or against the database at those intervals, and I'm really starting to feel<BR>helpless.<BR><BR>TIA for listening and all replies are greatly appreciated.<BR><BR>Steve Monaghan<BR><BR>-- <BR>Author: Steven Monaghan<BR>INET: Steve.Monaghan_at_wcom.com<BR><BR>Fat City Network Services -- (858) 538-5051 FAX: (858)!
 538-5051<BR>San Diego, California -- Public Internet access / Mailing Lists<BR>--------------------------------------------------------------------<BR>To REMOVE yourself from this mailing list, send an E-Mail message<BR>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in<BR>the message BODY, include a line containing: UNSUB ORACLE-L<BR>(or the name of mailing list you want to be removed from). You may<BR>also send the HELP command for other information (like subscribing).</BLOCKQUOTE><p><br><hr size=1><b>Do You Yahoo!?</b><br>
Get Yahoo! Mail - Free email you can access from anywhere!