Previous | Table of Contents | Next

Page 698

Monitoring V$BH

The V$BH table shows you the current contents of the database buffer cache of the instance. It provides the file and block identification numbers for the block in each buffer along with the status of the PCM lock on the block. A query along with the partial listing is shown in Listing 27.8.

Listing 27.8 Querying the V$BH Table
SQL> SELECT file#, block#, class#, status, xnc FROM v$bh;

FILE# BLOCK# CLASS# STATUS XNC
---- ------ ------ ------ ---
    8    438      1 XCUR     0
    8    467      1 SCUR     0
    8    468      1 SCUR     0
    8    468      1 SCUR     0
    9    192      1 CR       0
    9    198      1 CR       2
    9    201      1 XCUR    10
. . .

The status column has one of seven values, described in Table 27.8. You will typically only see the rows with XCUR, SCUR, or CR values after your instance has been running for a while. By then, all the buffers will have been used at least once and any media or instance recovery should be complete. Occasionally, you may catch a buffer that is waiting for a physical read to complete, but do not hold your breath.

Table 27.8 Buffer Status as Reported in V$BH
Status Explanation
FREE The buffer is not currently in use.
XCUR The block in the buffer is covered by an exclusive PCM lock.
SCUR The block in the buffer is covered by a shared PCM lock.
CR The block in the buffer was covered by an XCUR or SCUR lock that was downgraded.
READ A block is being read into the buffer from disk.
MREC The block in the buffer is in media recovery mode.
IREC The block in the buffer is in instance recovery mode.


Page 699

TIP
If you find buffers with the MREC or IREC status, you should exit from the database immediately. You can learn no useful information about how well your database is working on behalf of your users when the database is in the process of recovery. Further, your session is probably competing for resources that the recovery process could be using to complete its work.

You will usually find that the cache contains many buffers in CR status when there is excessive X to NULL or X to S conversion counts in V$LOCK_ACTIVITY. The XNC column shows the X to NULL conversion count for a particular block. Rows with non-zero values in this column are actually being pinged. You can identify which blocks are involved from the FILE# and BLOCK# columns. By comparing the blocks held in the buffer cache's of the other instances, using their V$BH tables or the Oracle8 GV$BH table, you can determine which blocks are being pinged and between which instances. In some cases, you will not find the same blocks in any other instance's cache. This indicates that false pings are taking place because the lock is being released but the blocks themselves were not needed.

To solve real or false pinging problems, it is useful to know which tables and even which values in those tables are involved. You can look at the blocks involved by querying tables using the ROWID pseudo-column. The query in Listing 27.9 is an example of such a query in a Version 7.3 database. Index entries cannot be found by this method; the best you can do is to identify the name of the index.

Listing 27.9 Examining Row Contents of Specific Blocks
SQL> SELECT * FROM DEPT
 2   WHERE SUBSTR(rowid,1,8) = `00000201'        -- the block id number
 3   AND SUBSTR(rowid,15,4) = `0009';           -- the file id number

DEPTNO NAME                  LOCATION    MGR_ID
------ -------------------- ---------- --------
    14 Human Resources       Fairview       198
    22 International Sales   New York      1356
. . .

To find the name of the segments associated with specific blocks identified in V$BH, you can query the DBA_EXTENTS view but, more easily, you can use the V$CACHE or V$PING views.

Monitoring V$CACHE and V$PING

The two dynamic performance tables, V$CACHE and V$PING, are based on the same buffer information as the V$BH view, but they include three additional columns (see Listing 27.10). These identify the name, type, and owner ID number of the segment to which the block in the buffer belongs. These views do not contain segment information for newly added extents unless catparr.sql has been run since their creation. If the NAME, KIND, and OWNER# columns contain nulls, simply run the script again to populate them with the new information.

Page 700

Listing 27.10 Querying V$CACHE
SQL> SELECT name, kind, owner#, file#, block#, status, xnc FROM v$cache;

NAME            KIND         OWNER# FILE# BLOCK# CLASS# STATUS XNC
-------------- ------------ ------ ---- ------ ------ ------ -----
EMP             TABLE            14     8    438      1 XCUR     0
EMP             TABLE            14     8    467      1 SCUR     0
EMP             TABLE            14     8    468      1 SCUR     0
EMP             TABLE            14     8    468      1 SCUR     0
DEPT            TABLE            14     9    192      1 CR       0
DEPT            TABLE            14     9    198      1 CR       2
DEPT            TABLE            14     9    201      1 XCUR    10
. . .

The difference between these two views is that V$CACHE contains an entry for every buffer in the cache whereas V$PING only contains a row for each buffer that has potentially been pinged. This is determined by the buffer having a non-zero value in the XNC column, indicating at least one X to NULL conversion for the block it contains.

You may also use the V$FALSE_PING dynamic performance table to identify potential false pings. This table contains the same columns as V$PING, but it only contains rows for blocks that are highly likely to have been pinged falsely a large number of times. Oracle does not guarantee, however, that every block listed in this table is undergoing false pinging, nor that the table includes every block that has been falsely pinged. You should still check the other instances' caches to find definitive evidence of false pinging as discussed previously.

Tuning Strategy for Parallel Server

The basic tuning strategy for the parallel instance components of a multi-instance database consists of the following four steps:

  1. Determine if there is excessive pinging.
  2. Identify what is being pinged.
  3. Resolve whether the pinging is true or false.
  4. Solve the pinging problem.

By using the Parallel Server dynamic performance tables discussed above, you can find the amount of pinging, the objects being pinged, and whether the same blocks are being pinged by one or more instances. What should you do with this information?

As with all tuning activities, you only need to tune if the database performance is not at the level required by the users. By monitoring the values in V$LOCK_ACTIVITY over time, you can tell if increases in any of the values are inversely proportional to the response time of the transactions. If they are, you then need to find the blocks responsible from V$BH, V$CACHE, or V$PING.

Previous | Table of Contents | Next