OraFAQ Forum: Performance Tuning » Buffer Busy waits, db file scatteredl read, sequential read

Home » RDBMS Server » Performance Tuning » Buffer Busy waits, db file scatteredl read, sequential read

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Buffer Busy waits, db file scatteredl read, sequential read [message #245330]

Sat, 16 June 2007 01:14

tkmittal1
Messages: 143
Registered: May 2007
Location: Delhi

Senior Member

Dear All

I m using oracle 9.2.0.1 on win 2000 server

I m getting follwoing waits events ---- Buffer Busy waits, db file scatteredl read, sequential read.

my db_cache_size is 1.3GB
no of user connect at a time 100-120

Kindly advice what i need to check

regards

Tarun MIttal

Report message to a moderator

Re: Buffer Busy waits, db file scatteredl read, sequential read [message #245335 is a reply to message #245330]

Sat, 16 June 2007 01:30

Michel Cadot
Messages: 68651
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

1/ Are these waits the major ones
2/ Are these waits not normal
3/ SQL with big numbers of logical IOs

Regards
Michel

Report message to a moderator

Re: Buffer Busy waits, db file scatteredl read, sequential read [message #245336 is a reply to message #245335]

Sat, 16 June 2007 01:37

tkmittal1
Messages: 143
Registered: May 2007
Location: Delhi

Senior Member

Are these waits the major ones
2/ Are these waits not normal
3/ SQL with big numbers of logical IOs

Hi

1. Yes these waits are major
2. It's not normal and it's cont.... however db_cache_hit ratio above 90%
3. yes, u r right there r number of logical read

regards

tarun mittal

Report message to a moderator

Re: Buffer Busy waits, db file scatteredl read, sequential read [message #245338 is a reply to message #245336]

Sat, 16 June 2007 01:48

Michel Cadot
Messages: 68651
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

2. It's not normal and it's cont.... however db_cache_hit ratio above 90%

What is the % of time waiting these event? You didn't show us your statspack report so we can't know.

90% of hit is a clue of bad SQL -> point 3, tune these SQL

Regards
Michel

Report message to a moderator

Re: Buffer Busy waits, db file scatteredl read, sequential read [message #245340 is a reply to message #245338]

Sat, 16 June 2007 02:02

tkmittal1
Messages: 143
Registered: May 2007
Location: Delhi

Senior Member

We r monitorning the database using third party tool(QCO)

so wait event is cont.... and variant time to time like 13 to 75

also buffer cache hit ratio some time goes 90% to 95% but most of the time it's 98% to 99%

kindly suggest

Report message to a moderator

Re: Buffer Busy waits, db file scatteredl read, sequential read [message #245343 is a reply to message #245340]

Sat, 16 June 2007 02:53

Michel Cadot
Messages: 68651
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

buffer cache hit ratio some time goes 90% to 95% but most of the time it's 98% to 99%

This is even worse.
Now it is sure you have very bad tuned SQL.
Or you execute many times the same queries or fetch one row at a time, and have a bad tuned application.

Regards
Michel

Report message to a moderator