Home » RDBMS Server » Performance Tuning » Buffer Busy waits, db file scatteredl read, sequential read
Buffer Busy waits, db file scatteredl read, sequential read [message #245330] Sat, 16 June 2007 01:14 Go to next message
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

Re: Buffer Busy waits, db file scatteredl read, sequential read [message #245335 is a reply to message #245330] Sat, 16 June 2007 01:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
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
Re: Buffer Busy waits, db file scatteredl read, sequential read [message #245336 is a reply to message #245335] Sat, 16 June 2007 01:37 Go to previous messageGo to next message
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

Re: Buffer Busy waits, db file scatteredl read, sequential read [message #245338 is a reply to message #245336] Sat, 16 June 2007 01:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
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
Re: Buffer Busy waits, db file scatteredl read, sequential read [message #245340 is a reply to message #245338] Sat, 16 June 2007 02:02 Go to previous messageGo to next message
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

Re: Buffer Busy waits, db file scatteredl read, sequential read [message #245343 is a reply to message #245340] Sat, 16 June 2007 02:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
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
Re: Buffer Busy waits, db file scatteredl read, sequential read [message #245346 is a reply to message #245343] Sat, 16 June 2007 04:00 Go to previous messageGo to next message
tkmittal1
Messages: 143
Registered: May 2007
Location: Delhi
Senior Member
Hi

u mean i have to tune the sql statment only

is it so?
Re: Buffer Busy waits, db file scatteredl read, sequential read [message #245348 is a reply to message #245346] Sat, 16 June 2007 04:39 Go to previous message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I mean you have to FIRST review the application and tune the SQL statements BEFORE doing anything on database.

Regards
Michel
Previous Topic: Improving performance of insert statement
Next Topic: UPDATE DATE AND TIME OF TABLE
Goto Forum:
  


Current Time: Fri Dec 09 09:40:44 CST 2016

Total time taken to generate the page: 0.05586 seconds