Buffer Busy Waits Page
Note: The information displayed in the Buffer Busy Waits page may be inaccurate if the Fast Initialize setting is on. The object for which the "buffer busy waits" are occurring may not be displayed. If you suspect that the Buffer Busy Waits page is displaying inaccurate information, turn the fast initialize setting off and then restart Spotlight on Oracle. Oracle Initialization
To open the Busy Buffer Waits Page
- Select the Spotlight on Oracle connection in the Spotlight Browser.
-
Click Activity | Busy Buffer Waits.
Tablespace Chart
Tablespace shows the buffer busy waits per tablespace. The most common type of block is "data block" which may indicate contention for "hot" blocks. Most of the time, the contention is caused by multiple sessions wanting to modify different rows within a single block.
Block class chart
Block Class shows the buffer busy waits per block class.
User Buffer Busy grid
Buffer Busy Waits shows the number and duration of buffer busy waits for every logged-on user who has waited during this session.
| Column | Description |
|---|---|
|
User name |
The Oracle account name of the session. Note: Oracle returns no value for this field when the user is Oracle itself. |
|
SID |
The session identifier for the session. |
|
Total waits |
The number of buffer busy waits. |
|
Waited (ms) |
The time spent waiting for busy buffers. |
|
Machine |
The name of the computer on which the client is running. |
|
OS user |
The Operating System user for the client. |
|
Process |
The Operating System client process ID. |
|
Program |
The Operating System program name. |
Show Busy Segments (Button and Grid)
Oracle does not keep a record of the buffers that are most often the subject of buffer busy waits. However, these details are available while the buffer busy wait is actually occurring. Spotlight records the details of any buffers that are busy when a normal (foreground) refresh occurs.
The details (segment name) of these buffers are displayed in the Buffer busy waits page when you click the Show busy segments button.
| Column | Description |
|---|---|
|
Segment |
The name of the segment (table, index, rollback segment, etc) experiencing buffer busy waits. |
|
Occurs |
The number of times a buffer busy wait was recorded against this segment. Since Spotlight checks for busy buffers only when it performs a foreground refresh, the actual number of buffer busy waits is likely to be much higher than this number. |