Top 5 Wait Events

[steeve123]

I have these statistics in statspack report, any suggestion on what to do.


Top 5 Wait Events
~~~~~~~~~~~~~~~~~ Wait % Total
Event Waits Time (cs) Wt Time
-------------------------------------------- ------------ ------------ -------
db file parallel write 17,343 483,876 39.37
log buffer space 30,862 185,338 15.08
log file parallel write 85,880 183,931 14.97
db file sequential read 114,001 145,934 11.87
db file scattered read 49,965 68,996 5.61



Do i need more disk?
Do i need to change init.ora parameters?

[marist89]

They're all I/O related waits. I would check the balance of my physical disks to make sure my load was distributed. If you're load is distributed and no disk/filesystem is spinning 100% of the time, I would check my SCSI throughput.

The "db file" events are related to datafiles. Use something like http://home.adelphia.net/~marist89/s...snap_fieio.htm to see how your I/O is distributed by file and http://home.adelphia.net/~marist89/s...ap_mountio.htm to check by mount point.

You may also want to check your buffer hit ratio to make sure you're not using your disk more than you have to.

The "log file" is related to your redo log files. I would check to make sure my redo logs are not on busy devices.

Wait descriptions are at http://otn.oracle.com/docs/products/...block.htm#2528

[marist89]

Also, you may want to check for Full Table Scans.

[mrvajrala]

db file parallel write :
-----------------------

This shows the DBWR is waiting for all I/O requests to complete

Log Buffer Space :
---------------------

This wait shows your application is generating more REDO , faster than LGWR can write it to DISK

Log File Parallel Write:
-------------------------

Again LGWR is waiting for blocks to be written all REDO LOG memebers.

Db File Sequential Read:
----------------------------

This wait is due to the block read from datafile to Buffer Cache.


DB File Scattered Read:
+++++++++++++++

This wait is due to Full table scan.


OK , that said , i would like you to concentrate one wait event at a time to tune your database.

And metalkink is having all the related documents and soulations of the waits... and you may wish to read them , before you alter the init.ora file.

I am just trying give a direction and not the exact answer to your post...

[anandkl]

To check if these wait events are really causing a performance problem,execute the following query couple of times and if you see sessions waiting for the same resource each time u execute then,check for the file#.If events are waiting for the same file#,then it means that there is I/O issue.i.e index tablespace and data tablesapce are placed in the same disk.Move then to different disk.And using the block_id, got from this query..you can know on which table these events are waiting.

select sid,substr(event,1,35) event,substr(p1text,1,10),p1,
substr(p2text,1,10),p2,substr(p3text,1,10),p3 from v$session_wait where
event not like '%SQL%' and event not like '%rdbms%';

regards
anandkl