Previous | Table of Contents | Next

Page 696

Listing 27.6 Querying a Global Dynamic Performance Table
SQL> SELECT * FROM gv$version;

INSTANCE_ID BANNER
---------- --------------------------------------------------
          1 CORE Version 4.0.2.0.0
          1 NLSRTL Version 3.3.0.0.0
          1 Oracle8 Server Release 8.0.3.0.0
          1 PL/SQL Release 3.0.2.0.0
          1 TNS for Solaris: Version 3.0.2.0..0
          2 CORE Version 4.0.2.0.0
          2 NLSRTL Version 3.3.0.0.0
          2 Oracle8 Server Release 8.0.3.0.0
          2 PL/SQL Release 3.0.2.0.0
          2 TNS for Solaris: Version 3.0.2.0.0

Monitoring V$LOCK_ACTIVITY

The first table you need to monitor is V$LOCK_ACTIVITY. This table shows the number and types of lock conversions that have occurred in a single instance since it started up. Although the actual numbers are not meaningful themselves because each system handles locks and related activity at different speeds, you can use them to compare the performance of different instances and to compare the behavior of a specific instance at different times. A query against the V$LOCK_ACTIVITY table produces an output similar to Listing 27.7.

TIP
You should try to capture a "baseline" copy of the values in the V$LOCK_ACTIVITY table when the database is still fairly new and running at acceptable levels of performance. If performance degrades later, you can compare the current statistics with the baseline to see if there are any major changes. If there are not, you can eliminate PCM lock activity as one of the causes of the problem. You may even find it instructive to keep a representative sample output from this table on a scheduled basis to track changes in the number of lock conversions.
If you decide to follow this practice, make sure that you collect data that represents similar conditions, such as the length of time the instance has been running and the type of work being performed. Ten hours of uptime over a weekend is not necessarily the same as ten hours in the middle of a workday. You may want to set up a scheduled job to collect the statistics at the beginning and at the end of a specific time period. For example, you could choose to collect the lock conversion data at 10:00 A.M. and at noon on the first Monday, or the last Thursday, of each month. You can then compute the average lock conversions per minute for each month during the period monitored.

Listing 27.7 Querying V$LOCK_ACTIVITY
FROM TO   ACTION                                          COUNTER
---- ---- ---------------------------------------------- -------
NULL S    Lock buffers for read                              3595
NULL X    Lock buffers for write                             8111

Page 697

S    NULL Make buffers CR (no write)                         2763
S    X    Upgrade read lock to write                         1046
X    NULL Make buffers CR (write dirty buffers)                92
X    S    Downgrade write lock to read (write dirty buffers) 1220
X    SSX  Write transaction table/undo blocks                 907
SSX  NULL Transaction table/undo blocks (write dirty buffers)   0
SSX  S    Make transaction table/undo block available share     0
SSX  X    Rearm transaction table write mechanism             907

The table shows the different types of PCM lock conversions that can occur in an instance. The ones already discussed in this chapter are NULL to SHARED and NULL to EXCLUSIVE, as well as SHARED to EXCLUSIVE and the reverse of each of these. These are the lock conversions that take place when an instance acquires a lock that it didn't own or returns the lock to the DLM. Shared locks are used for querying table and index blocks, and exclusive locks are used when the block contents need to be changed.

Rollback segments have special forms of PCM locks. Because each rollback segment is acquired by an instance at startup and only that instance can write to it, there is no need for the instance to give up its exclusive ownership of the locks on the segment. However, other instances need to read the rollback information written by an instance when they need to build undo blocks for their queries. To achieve this, a special form of lock is used, called a shared exclusive lock. The conversions used for these locks show up in the V$LOCK_ACTIVITY table as X to SSX and SSX to X. You should see that the two counters for these conversions contain the same values. This is because an instance that obtains an SSX lock, in order to read the undo information written by another instance, should automatically release the lock as soon as the undo data is read. You can ignore the entries for SSX to NULL and SSX to S conversions as they result from operations performed during system startup or tablespace management activity.

If you suspect that PCM lock conversions and pinging are causing performance problems, or if you wish to be proactive and monitor the behavior of the PCM locks on a regular basis, the V$LOCK_ACTIVITY is where you should begin. You will always see some lock conversion activity, because an instance owns all PCM locks in NULL status at instance startup and must convert the ones it needs before it can do any work. You should concentrate on the values of the lock conversions that return locks to a NULL status and on the values for rollback segment lock activity. Conversions to a NULL status only occur when a lock is needed by a different instance than the one you are monitoring. This means that some form of pinging is occurring. If the counter for these conversions is high and continually increasing, you should suspect pinging as one cause of any degraded database performance. You will need to examine the other Parallel Server dynamic performance tables to determine which blocks and segments are involved.

The paired conversions between X to SSX and SSX to X indicate that rollback segment data is being pinged. If these numbers are high, you should consider increasing the number of rollback locks using GC_ROLLBACK_LOCKS, possibly switching to fine grain locks if the problem persists.

Previous | Table of Contents | Next