15.5 Workload characteristics

---

This parameter dictates the number of blocks that each instance would try to read from the requesting instance during a given request. However, a very small value could affect the single instance performance.

Latencies displayed in the STATSPACK report are the average values and are arrived at based on the values of other latencies also found in the STATSPACK report.

Table 15.4 provides the methods used to arrive at the average latency measurements reported in the STATSPACK report. The table also provides the acceptable values for the various latencies.

Table 15.4: STATSPACK Latency Values^[a]

Latency	How to Calculate (in ms)	Typical Values (ms)	Upper Limit (ms)
Ave global lock get time	(Global lock get time) × 10/(Global lock sync gets + Global lock async gets)	20–30	60
Ave global lock convert time	(Global lock convert time) × 10/(Global lock sync converts + Global lock async converts)	10–20	100
Ave global cache get time	(Global cache get time) × 10/(Global cache gets)	2–3	10
Ave global cache convert time	(Global cache convert time) × 10/(Global cache converts)	6–8	15
Ave global cache CR request time	(Global cache CR block receive time) × 10/(Global cache CR blocks received)	5–30	50
[a]Source: Oracle Corporation

Global cache hit ratio

This is an overall indicator of how much work is being done at the buffer cache level to maintain the global cache. It represents the hit as a result of global cache operation. This hit ratio is a derivate of the buffer cache hit ratio and the local cache hit ratio and is determined using the following formula: Global cache hit = (Buffer cache hit ratio – Local cache hit ratio)

The hit ratio could be improved by:

• Ensuring efficient access to local data, for example by using automatic segment space management (ASSM) in tablespace definitions, or if the application uses the Oracle sequences extensively, increasing the sequence cache size.

• Avoiding long full table scan operations, especially in an OLTP environment. This could be accomplished by ensuring that the SQL queries are well tuned.

• Minimizing contention for a small set of buffers. The hot buffers could be identified by querying the V$SEGMENT_STATISTICS view and identifying the hot objects.

KJC: Wait for msg send to complete

This wait event is triggered when a foreground or background process wants to send a message to the other instance. The event ends when the message has reached the destination. A high wait average on this event indicates:

• High system load.

• High CPU usage.

• Scheduling latencies which could be related to long run queues.

• High interconnect latency.

Other events shown in the output above are:

Ratio of current block defers

Indicator of how often Oracle defers the shipping of a block because of active transactions on the local instance.

Ratio of I/O for coherence

Indicator of how much global cache gets are resolved by physical reads.

---