| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: wait events
Jack,
I have a few comments on places to look. A lot of the items near the top of your list are normally ignored (like the 'SQL*Net message from client') but very high figures can indicate a networking issue either in how the network itself is doing (competition from other traffic, packet size, presence of a 'chattering' card on the net, etc.) or a Net8 issue. The high number of db file scattered reads may be of more concern, however, since it shows a lot of full table scans are going on. This would point to getting on your developers 'top ten SQL queries from hell' and optimizing them. This usually represents a large part of the problem that is 'Oracle's fault' or 'that dang DBA.' Be nice to your developers, as a rule, but if they continue to write badly tuned SQL that ignores indexes and just look at you oddly when you talk about cost-based optimization and hints, consider having one of them hung in a cage above the cubicles as an example to the others.
I would be interested in seeing the results of this query on your system at a busy time:
SELECT
Event, SUM(Total_Waits), SUM(Total_Timeouts) FROM V$Session_Event WHERE total_timeouts > 0 GROUP BY Event
This should pop some problem children to the surface. Some of your values look like you have a similar problem to us: bad I/O, particularly redo. We have always had problems with redo, and could still use more tuning.
How balanced is your I/O? Do you have the luxury of having things spread out, or are you stuck with what an SA laid down in concrete for disk/array setup? Also, what kind of a system is it, OLTP, DSS, mixed? High transaction rate?
I just read in your next message in this thread that you moved to a 4K block size. Generally not a good idea, though I suppose there are some really high-transaction OLTP systems that can actually benefit. We're comfortable with an 8K on both our OLTP and DSS instances, but then we have a transaction rate a fast typist could probably keep up with (~1,000 transactions a day) and you should probably consider 16 or higher for anything faintly resembling a warehouse.
Regards,
Chris Gait
-----Original Message-----
From: Jack van Zanen [mailto:nlzanen1_at_ey.nl]
Hi All,
I have run utlestat & utlbstat plus some other scripts and have a lot of
wait
events
Event Name Count Total Time Avg Time -------------------------------- ------------- ------------- ------------- SQL*Net message from client 443489 4645139 10.47 rdbms ipc message 1995 999996 501.25 db file scattered read 1087432 798071 .73 PL/SQL lock timer 1 10001 10001 buffer busy waits 3542 2415 .68 latch free 6804 2149 .32 db file sequential read 35326 1812 .05 SQL*Net message to client 443501 617 0 log file sync 1354 514 .38 SQL*Net more data to client 5142 98 .02 control file sequential read 18 4 .22 enqueue 1 0 0 file open 13 0 0 refresh controlfile command 6 0 014 rows selected.
SVRMGR>
SVRMGR>
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 5441 1383003 254.18
smon timer 7 210005 30000.71
pmon timer 668 200693 300.44
log file parallel write 1456 499 .34
control file parallel write 666 420 .63
latch free 8 5 .63
control file sequential read 12 0 0
****************************************************************************
*************************
Now performance is poor and they all point the finger at ORACLE. I defend
oracle
and say it's the application, but in the meantime will be rebuilding the
database to 4K. Does anybody see anything else I should be looking at ?
Jack
-- Author: Jack van Zanen INET: nlzanen1_at_ey.nl 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-LReceived on Tue May 16 2000 - 14:39:55 CDT
 |
 |