Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1217
  • Last Modified:

Top five wait events

How to get and analyze the top five wait events in oracle.
I have oracle running in win 2003 and hpunix

version are 9i and 10g
thanks
0
taaz
Asked:
taaz
  • 2
2 Solutions
 
schwertnerCommented:
Top 5 Timed Events (Called "Top 5 Wait Events" )
This section is crucial in determining what some of the performance drains in your database are. It will actually tell you the amount of time the instance spent waiting. Here are some common reasons for high wait events:
DB file scattered read: This can be seen fairly often. Usually, if this number is high, then it means there are a lot of full tablescans going on. This could be because you need indexes or the indexes you do have are not not being used.


DB file sequential read: This could indicate poor joining orders in your SQL or waiting for writes to 'temp' space. It could mean that a lot of index reads/scans are going on. Depending on the problem it may help to tune PGA_AGGREGATE_TARGET and/or DB_CACHE_SIZE.


CPU Time: This could be completely normal. However, if this is your largest wait event then it could mean that you have some CPU intensive SQL going on. You may want to examine some of the SQL further down in the Statspack report for SQL statements that have large CPU Time.


SQL*Net more data to client: This means the instance is sending a lot of data to the client. You can decrease this time by having the client bring back less data. Maybe the application doesn't need to bring back as much data as it is.


log file sync: A Log File Sync happens each time a commit takes place. If there are a lot of waits in this area then you may want to examine your application to see if you are committing too frequently (or at least more than you need to).


Logfile buffer space: This happens when the instance is writing to the log buffer faster than the log writer process can actually write it to the redo logs. You could try getting faster disks but you may want to first try increasing the size of your redo logs; that could make a big difference (and doesn't cost much).


Logfile switch: This could mean that your committed DML is waiting for a logfile switch to occur. Make sure your filesystem where your archive logs reside are not getting full. Also, the DBWR process may not be fast enough for your system so you could add more DBWR processes or make your redo logs larger so log switches are not needed as much.
0
 
taazAuthor Commented:
Thanks S. I was accually looking for some documnetaions to get to topfive waits events.
How to generate a report that shows the top five waits events?

thanks
0
 
PilouteCommented:
Hi,

To get these kind of reports, you have to get some snapshots that you have to get compared in order  to generate a report.

In 10g you can use AWR in you EM DB Console. It offers a 1 click snapshot + 1 click report generation. This is quite easy. But for 9i you will have to use StatsPack tools (also available in 10g).

Have a look to the Oracle documentation and look for AWR and Statspack : you should find everything you need in the Perf Tuning Reference book.  

9i doc here : http://www.oracle.com/pls/db92/db92.homepage
10g doc here : http://www.oracle.com/pls/db10g/portal.portal_demo3?selected=1

Cheers,
P
0
 
schwertnerCommented:
Good luck!
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now