Data buffer quality is not good and buffer busy wait waiting for data block

[Former Member]

Hi Oracle Gurus,

we have SAP R/3 and database is oracle 9.2.0.8.

Data buffer quality is not good (75.5) whereas it should be more than 95. I checked and found buffer busy wait waiting for data block in st04 trans-> Detail analyis menu -> Busy buffer wait.

Current Database size is 74 GB and sga_max_size is 1200M and shared_pool_size=392M and db_block_buffers=96000, db_block_size=8192 so db_buffer_cache= db_block_buffers * db_block_size 750M.

Did anyone face such issue?

Please suggest what should I do to improve data buffer quality?

Thanks,

Bhupesh

[Former Member]

As long as you don't have a performance issue (for example users complaining), don't do anything. The hit ratio as single information is not always a good indicator for performance.

If you have performance issues, then you should first try to increase the buffer cache to 2000M. But make sure you have enough free memory on the server, otherwise things will get worse.

Another approach could be to try to find SQL causing a lot of disk reads or block gets in general. You might be able to tune some of them. You can use transaction ST04 (shared cursor cache) and AWR reports for the analysis. But this is much more sophisticated.

Also think about upgrading to 10g, this might not increase your buffer quality, but at least give you a current release.

Regards, Michael

[Former Member]

HI Michael,

Thanks for replying.

Database is not set as per SAP recommendataion (SAP note 124361) like event and underscore parameter are not set.

Oracle 9i feartures are not used like PGA is not used, undo is not used (still PSAPROLL tablespace is ussd). db_buffer_cache parameter is not set instead db_block_buffers is used.

I am planning to set database as per SAP recommondataion. This might also increase database performance.

I need to focus to increase db_buffer_cache quality and buffer busy wait event. Can you please help in resolving buffer busy wait issue?

Thanks,

Bhupesh

[stefan_koehler]

Hello Bhupesh,

please post the value of the indicator "reads per user call".

Regards

Stefan

[Former Member]

Hi Stefan,

Can you please tell me how to find "reads per user call"?

Thanks,

Bhupesh

[stefan_koehler]

Hello Bhupesh,

you can get this value with the SAP transaction ST04.

Here is the documentation: http://help.sap.com/saphelp_sm32/helpdata/en/c4/3a7223505211d189550000e829fbbd/content.htm

Reads / User calls
The value Reads / User calls displays the number of Oracle blocks on average that were read from the data buffer to satisfy a request (call) sent to the database. If this value is greater than 30, this indicates expensive SQL statements.

Regards

Stefan