玉面飞龙的BLOG

buffer busy wait split in oracle10g

July 22nd, 2007 | Categories: Boring | Tags:

Oracle10g中，引入 “read by other session”等待事件；在9i中，该事件属于”buffer busy wait”等待事件的一部分。

Oracle9i中，v$waitstat.TOTAL_WAITS基本上等于buffer busy wait等待次数。

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production

SQL> select sum(TOTAL_WAITS) from v$system_event where event='buffer busy waits' ;
SUM(TOTAL_WAITS)
----------------
33070394

SQL> select sum(count) from v$waitstat;
SUM(COUNT)
----------
33069335

在10g中，v$waitstat.TOTAL_WAITS依旧基本上等于buffer busy wait+read by other session的等待次数之和。

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> select sum(TOTAL_WAITS) from v$system_event where event='buffer busy waits' or event='read by other session';
SUM(TOTAL_WAITS)
----------------
60675815

SQL> select sum(count) from v$waitstat;
SUM(COUNT)
----------
60423739

虽然一分为二，但基本的优化方法还是和原先一样。

————————————-

Quote more information here,

“read by other session Definition: When information is requested from the database, Oracle will first read the data from disk into the database buffer cache. If two or more sessions request the same information, the first session will read the data into the buffer cache while other sessions wait.

In previous versions this wait was classified under the “buffer busy waits” event.

However, in Oracle 10.1 and higher this wait time is now broken out into the “read by other session” wait event. Excessive waits for this event are typically due to several processes repeatedly reading the same blocks, e.g. many sessions scanning the same index or performing full-table scans on the same table. Tuning this issue is a matter of finding and eliminating this contention.”

————————————-