Re: Buffer Reads?

Hi Calvin Calvin Shoults wrote: > > We reset stats with onstat -z and noticed that the buffer reads were > growing at an unbelievable rate. Within 30 min. or so there were close to > 100 million buf reads. This seems to be the case most of the time. We have > about 300 users and we are running somewhere between 10 and 40 > crystal reports at any one time. The caching is 95-98 % for reads and > writes. > Before I left work today, we had close to 1 billion buf reads. > The database is on a dedicated db server and has crystal rpts accessing it > as > well as a couple of moderate size C++ apps. > Any idea why the high buf reads and is there a way monitor it in more > detail? A buffer read is a simple cache buffer access. Whenever the server must read a row it first tries to find the row in the buffer pool. Even if the server will not find the page in the buffer, it first brings the data into the cache and will access the data finally from the cache. Every access to the buffer pool is either a buffer read or buffer write. If you have small tables with many rows and access these tables several times, the number of buffer reads increase rapidly and that's normal. If you want to monitor the buffer access in more detail, you can query the sysptprof or syssesprof table of the sysmaster database. Here you can find out which session and/or table caused the most buffer accesses. > Also, we noticed on the sessions a bunch of IX - Intent Exclusive that were > not releasing. We no Exclusive, but cannot find anything on Intent > Exclusive. > What exactly is this and do they affect performance? > > James IX locks are set automatically by the server when a session places an exclusive row or page lock on a row. row and page locks will be set when a session updates, inserts or deletes a row. The lock will be released at the end of the transaction. IX locks are set on the table-level to avoid that other sessions place a shared or exclusive lock on the table. The server never performs checks against different lock levels. This will save time but on the other hand the server had to implement the IX locks which finally cost time. But, you cannot do anything against IX locks. A table will be locked with an IS lock when you place a Shared lock on a row or page. You can avoid these locks by using the isolation level Dirty Read or Committed Read. Regards -- Stefan Weideneder Phone: +49 89/3565478-2 --------------- --- Fax: +49 89/3565478-3 ------------- ------ mailto:/stefan@weideneder.de --- -------- http://www.weideneder.de -----