15.4 Cluster instance tuning

---

Tuning of the instance in a RAC configuration is different from a single instance configuration. In Chapter 5 (Transaction Management) we discussed how the data is maintained by recording the block requests and changes in the GRD and how the GCS is responsible for maintaining the data consistency across the instances participating in the clustered configuration. This feature of Oracle adds additional opportunities for tuning the instance.

All views that are available at the single stand-alone configuration level are also available as a global view. These views are qualified with a prefix ''G.'' For example, the V$SYSTEM_EVENT dynamic performance global view would be GV$SYSTEM_EVENT and would contain statistics from all instances participating in the clustered database configuration.

In the examples discussed in the previous chapters, we looked at using session-level statistics to get to the bottom of an issue. It is important to capture cluster-level wait statistics at the session level. However, since session-level information is only retained by Oracle for the duration of the session, it is important to store such information for future analysis. As indicated in Chapter 13, session-level statistics can be captured by using a log-off trigger, when the session-level data is saved from V$SESSION_ EVENT, V$SESSION_WAIT and V$SESSTAT views into permanent tables for future analysis.

The query and its output below should be familiar from Chapter 13, where we discussed the instance-level wait events. In this section, we continue our discussions on the same query output, this time looking into the cluster-related wait events:

COL SID FORMAT 9999  COL USERNAME FORMAT A15  COL EVENT FORMAT A30  COL P1 FORMAT 9999  COL P2 FORMAT 9999  COL P3 FORMAT 9999 SELECT MVASW_SID SID,        MVASW_USERNAME USERNAME,        MVASW_SQL_ADDRESS SQL_ADDRESS,        MVASW_EVENT EVENT,        MVASW_P1 P1,        MVASW_P2 P2,        MVASW_P3 P3,        MVASW_WAIT_TIME WAIT,        MVASW_FAILED_OVER FO FROM MV_AUDIT_SESSION_WAIT  WHERE MVASW_USERNAME IS NOT NULL  AND (MVASW_EVENT LIKE  OR  MVASW_EVENT LIKE  OR  MVASW_EVENT LIKE  OR  MVASW_EVENT LIKE  OR  MVASW_EVENT LIKE ORDER BY SID / SID SQL_ADDRESS      EVENT                    P1    P2          P3 WT FO --- ---------------- ----------------------  --- ----- ----------- -- --  30 0000000443D7CF48 db file sequential read  18 56903           1  0 NO  32 0000000442A622A0 db file sequential read 194 20683           1  0 NO  32 0000000442174720 db file sequential read  18 25804           1  0 NO  32 00000004421739C0 db file sequential read  18 11786           1  0 NO  32 0000000443AB2528 global cache null to x   18 25804 17917873152  0 NO  36 0000000442178608 db file sequential read  18 24480           1  0 NO  36 0000000442178608 global cache s to x      18  1164 17934759808  0 NO  39 00000004421781C8 global cache open x      18 28712 17968379616  0 NO  45 00000004421760F8 db file sequential read  18 16482           1  0 NO  50 00000004448FA0C8 db file sequential read  18 11787           1  0 NO  77 0000000443D27A68 global cache null to x   18 19651 16978156672  0 NO  90 00000004448FE370 db file sequential read  18 15422           1  0 NO  99 0000000443D7DDD8 global cache null to x   18 11888 17045147456  0 NO 106 0000000444901728 global cache s to x      18  7053 17397452288  0 NO 109 00000004448FE370 db file sequential read  18 17213           1  0 NO 117 0000000444901728 global cache null to s   18  7053 17397452288  0 NO 120 00000004448F9728 db file sequential read  18 11891           1  0 NO 125 00000004448F8FA0 db file sequential read  18 11864           1  0 NO 125 00000004448FE370 global cache cr request  18 23689 17028513088  0 NO                                                        19 rows selected.

Global cache CR request

This event indicates the time waited when a session is looking for a consistent-read (CR) version of a block (indicated by block# in column P2 that belongs to the file indicated in column P1 in the output above) and cannot find it in its local cache and hence has made a request against a remote instance for the block. However, the transferred block has not yet arrived at the requesting instance. It also implies that the current block is not cached locally. This event ends when the session gets the block or permission to read the block from disk.

This event may not always indicate a problem in the GCS requests. Some of the issues where these wait events can be found are when:

1. Data blocks are being modified frequently on all instances.

2. Requests for block resulted in a cache miss.

3. LMS cannot keep up with the high number of CR requests.

4. There are latency issues with the interconnect.

5. There are full table scans.

High waits on this event could be reduced by looking at the system and scheduling delays; for example, ensuring that the LMSx processes get enough CPU. Another rather critical factor when it comes to the RAC architecture is the interconnect latency/bandwidth. Wait times could be reduced by ensuring that a high-speed interconnect with low latency is used.

Global cache NULL to x and global cache NULL to s

These events are waited for when a block (indicated by block# in column P2 that belongs to the file indicated in column P1 in the output above) was used by an instance, transferred to another instance, and then requested again by the original instance. Processes waiting for these events are usually waiting for a block to be transferred from the instance that last modified it. If one-instance requests cached data blocks from other instances, then it is normal that these events consume a greater proportion of the total wait time. The event ends when the session gets the block from a remote instance or has proper access mode to modify the block.

The wait time for this event depends on:

• The ''busyness'' of the block that is being requested by the session.

• The processing power available at the send side (e.g., CPU).

• Interconnect speed at requester/sender side, which is determined by the interconnect latency factor.

If this event is listed in the top 5 wait events, and the average wait time is less than 30 ms, the waits are to be considered as part of the normal activity. However, if the average is above the 30 ms threshold, attention should be given to reducing the wait times by:

• Looking for system load and scheduling delays; this could be obtained for example by ensuring the LMSX processes has enough CPU resources available.

• Ensuring that a high-speed interconnect with low latency is being used and validated.

• Identifying the busy blocks and the related objects by querying the V$SEGMENT_STATISTICS dynamic performance view.

Global cache open x

This event is associated with the initial access of a particular data block (indicated by block# in column P2 that belongs to the file indicated in column P1 in the output above) by an instance. If the duration of the wait is short, then the completion of the wait is most likely followed by a read from disk. If these events reflect a high wait time, this indicates that the block is currently not cached in the local instance or in a remote instance and requires a disk read to complete the request.

Global cache open s

Similar to the event described above, in this case the session waits to receive the resource identified by file# in the P1 column and block# identified by P2 in a shared mode.

Buffer busy due to global cache

This is the counterpart wait event to the ''buffer busy wait'' encountered and discussed in the previous chapter. However, the buffer busy wait encountered due to global cache is due to waits encountered at the global cache level. This wait is encountered on file# indicated by the value in the P1 column and block# indicated by the value in the P2 column.

Enqueues

These are shared memory structures (locks) that serialize access to database resources. They can be associated with a session or transaction. Enqueue names are displayed in the LOCK_TYPE column of the DBA_LOCK and DBA_LOCK_INTERNAL data dictionary views.

A resource uniquely identifies an object that can be locked by different sessions within an instance (local resource) or between instances (global resource). Each session that tries to lock the resource will have an enqueue on the resource.

The enqueue wait tracked by the V$SESSION_WAIT view indicates that the session is waiting for a local enqueue. The parameters P1 and P2 indicate the name of the enqueue and mode respectively. The possible modes are shown in Table 15.2.

Table 15.2: Enqueue Mode Descriptions

Mode Value	Description
1	Null mode
2	Subshare
3	Subexclusive
4	Share
5	Share/subexclusive
6	Exclusive

Querying the P1RAW column in the V$SESSION_WAIT view or MVASW_P1RAW column in the MV_AUDIT_SESSION_WAIT table for event enqueue can provide an indication as to which data object the enqueue is mapped against:

SELECT MVASW_EVENT,        MVASW_P1RAW FROM   MV_AUDIT_SESSION_WAIT WHERE  MVASW_EVENT = 'enqueue'; EVENT   P1RAW ------- ---------------- enqueue 0000000050530006 enqueue 0000000050530006 enqueue 0000000050530006 enqueue 0000000050530006 enqueue 0000000050530006 enqueue 0000000050530006 enqueue 0000000050530006 enqueue 0000000050530006

The P1RAW column stores the information in hexadecimal format and can be interpreted in the following manner:

• First 6 bytes indicate the type of object enqueue is waiting for.

• Second 2 bytes indicate the type of locks being held. Type 4 indicates a shared lock and type 6 indicates an exclusive lock.

---