/* 	BLOCK contention exist on the following blocks:
*/
SELECT /*+ RULE */  DISTINCT
	SW.INST_ID	
,	DE.OWNER
,	DE.SEGMENT_NAME
,	DE.SEGMENT_TYPE 
FROM 	DBA_EXTENTS  DE
,	GV$SESSION_WAIT SW
WHERE SW.P1 = DE.FILE_ID 
AND 	SW.P2 BETWEEN DE.BLOCK_ID AND DE.BLOCK_ID+BLOCKS-1
AND 	EVENT = 'BUFFER BUSY GLOBAL CR'; 
/*
THIS QUERY NEEDS TO BE MODIFIED TO COME OF GV$SESSION_WAIT

If there is contention, then index leaf blocks are usually the most contended blocks. 
To reduce contention on individual blocks, you can use a smaller block size for the index tablespace, 
or hash partitioning. These modifications distribute the load among more distinct blocks.
**********************************************************************************************************************
Number of lost blocks (should be (near) zero)!
*/
SELECT 	INST_ID
,	DECODE(NAME,'global cache blocks lost',VALUE) as "LOST BLOCKS" 
FROM	GV$SYSSTAT
WHERE DECODE(NAME,'global cache blocks lost',VALUE) IS NOT NULL;
/*

**********************************************************************************************************************
Number of lost blocks divided by the number of 'global cache current blocks served' + 
'global cache crt blocks served'  should be as close to zero as possible
*/
SELECT INST_ID
,	CASE 
	WHEN sum(DECODE(NAME,'global cache current blocks served',VALUE,NULL))+sum(DECODE(NAME,'global cache cr blocks served',VALUE,NULL)) >0
	THEN (sum(DECODE(NAME,'global cache blocks lost',VALUE,NULL)) / 
	(sum(DECODE(NAME,'global cache current blocks served',VALUE,NULL))+sum(DECODE(NAME,'global cache cr blocks served',VALUE,NULL))))/100 
	ELSE 0
	END CASE 
FROM	GV$SYSSTAT
GROUP BY INST_ID;
/*

**********************************************************************************************************************

Number of Corrupted blocks (should be (near) zero)!
*/
SELECT 	INST_ID
,	DECODE(NAME,'global cache blocks corrupt',VALUE) as "CORRUPTED BLOCKS" 
FROM	GV$SYSSTAT
WHERE DECODE(NAME,'global cache blocks corrupt',VALUE) IS NOT NULL;
/*
**********************************************************************************************************************
To estimate the use of the Global Cache Service relative to the number of buffer cache reads, 
or logical reads, divide the sum of Global Cache Service requests by the number of logical reads for a 
given statistics collection interval.

Oracle makes a Global Cache Service request whenever a user accesses a buffer cache to read or 
 	modify a data block and the block is not in the local cache. This results in a remote cache read, a disk 
read, or a change of access privileges. In other words, logical reads are a superset of 
Global Cache Service operations
*/ 
 SELECT INST_ID
,	CASE
	WHEN
	(	sum(DECODE(NAME,'db block gets',VALUE,NULL))
	+	sum(DECODE(NAME,'consistent gets',VALUE,NULL))
	) >0
	THEN
	(	sum(DECODE(NAME,'global cache current blocks received',NVL(VALUE,0),NULL))
	+	sum(DECODE(NAME,'global cache cr blocks received',NVL(VALUE,0),NULL))
	+	sum(DECODE(NAME,'global cache converts',NVL(VALUE,0),NULL))
	+	sum(DECODE(NAME,'global cache gets',NVL(VALUE,0),NULL))
	)/
	(	sum(DECODE(NAME,'db block gets',VALUE,NULL))
	+	sum(DECODE(NAME,'consistent gets',VALUE,NULL))
	)/100
	ELSE 0
	END CASE
FROM	GV$SYSSTAT
GROUP BY INST_ID;
/*
**********************************************************************************************************************

Some blocks may be very hot, or in other words, frequently requested by local and remote users. 
Sometimes a block transfer is delayed for a few milliseconds to permit local users to complete their work. 
The following ratio provides a rough estimate of how probable this is:
*/
 SELECT INST_ID
,	CASE
	WHEN
	(	sum(DECODE(NAME,'global cache current blocks served',VALUE,NULL))
	) >0
	THEN
	(	sum(DECODE(NAME,'global cache defers',NVL(VALUE,0),NULL))
	)/
	(	sum(DECODE(NAME,'global cache current blocks served',VALUE,NULL))
	)/100
	ELSE 0
	END CASE
FROM	GV$SYSSTAT
GROUP BY INST_ID;
/*
A ratio of more than 0.3 indicates a fairly hot data set. In such a case, closely analyze the blocks involved 
in busy waits. To do this, query columns such as NAME, TYPE, FORCED_READS, and FORCED_WRITES 
from the V$CACHE_TRANSFER view, or examine the CR_TRANSFERS and CURRENT_TRANSFERS 
columns in V$OBJ_STATS. Also examine the values shown for the "global cache busy", 
"buffer busy global cache", and "buffer busy global CR" statistics as described under the heading 
"Global Cache Service Wait Events".

If you discover a problem, then identify the object causing it, the instance that is accessing the object, and 
how the object is being accessed. If necessary, alleviate the contention by reducing:

1.	Hot spots by spreading the accesses to index blocks or data blocks, possibly by using Oracle hash 
	or range partitions wherever applicable--just as you would in single-instance Oracle databases. 
2.	Concurrency on the object by implementing load balancing or resource management. 
	For example, decrease the rate of modifications to that object (use fewer database processes). 
**********************************************************************************************************************

As mentioned earlier, in single-instance Oracle databases and in Real Application Clusters, blocks are 
only written to disk for aging, cache replacement, or checkpoints. When a data block is replaced from 
the cache due to aging or when a checkpoint occurs and the block was previously changed in another 
instance but not written to disk, Oracle sends a message to notify the other instance that Oracle will 
write the data block to disk. This is called a fusion write, and the following ratio reveals the proportion 
of writes that Oracle manages in this way:
*/
 SELECT INST_ID
,	CASE
	WHEN
	(	sum(DECODE(NAME,'physical writes',VALUE,NULL))
	) >0
	THEN
	(	sum(DECODE(NAME,'DBWR fusion writes',NVL(VALUE,0),NULL))
	)/
	(	sum(DECODE(NAME,'physical writes',VALUE,NULL))
	)/100
	ELSE 0
	END CASE
FROM	GV$SYSSTAT
GROUP BY INST_ID;
/*
The larger this ratio is, the higher the number of written blocks that have been copied with previous 
changes in other instances. A large ratio can result from insufficiently sized caches or it can be 
because checkpoints have not occurred for some time. A large number can also indicate the proportion 
of the global working set that is composed of buffers written due to cache replacement or checkpointing. 
For example, 0.1 means that 10% of the buffers written to disk were globally dirty.

Note that a fusion write is not an additional write to disk. However, a fusion write requires messaging 
to coordinate the transfer with the other instance. Therefore, a fusion write is a subset of all physical 
writes incurred by an instance.

**********************************************************************************************************************

Calculate the average latency of a consistent block request, in other words, its round-trip time, as: 

*/
 SELECT INST_ID
,	CASE
	WHEN
	(	sum(DECODE(NAME,'global cache cr blocks received',VALUE,NULL))
	) >0
	THEN
	(	sum(DECODE(NAME,'global cache cr block receive time',value,NULL))*10
	)/
	(	sum(DECODE(NAME,'global cache cr blocks received',VALUE,NULL))
	)/100
	ELSE 0
	END CASE
FROM	GV$SYSSTAT
GROUP BY INST_ID;
/*
The result, which should typically be about 15 milliseconds depending on your system configuration 
and volume, is the average latency of a consistent-read request round-trip from the requesting instance 
to the holding instance and back to the requesting instance. If your CPU has limited idle time and 
your system typically processes long-running queries, then the latency may be higher. 
However, it is possible to have an average latency of less than one millisecond with User-mode IPC. 

Request latency can also be influenced by a high value for the DB_MULTI_BLOCK_READ_COUNT 
parameter. This is because a requesting process can issue more than one request for a block 
depending on the setting of this parameter. Correspondingly, the requesting process may wait longer

**********************************************************************************************************************

For a high number of incoming requests, especially in report-intensive applications, or if there are 
multiple nodes from which requests are dispatched to an LMS process, the round-trip time can increase 
because LMS' service time increases. To determine whether the length of the delay is caused by 
LMS overhead, compute the average service time per request using the following equation: 

The sum of global cache cr queue, build, flush, and send times, divided by global cache cr blocks served.


Over a period of time during peak processing intervals, track the average LMS service time per request 
and the total round-trip time per request as presented in this step. 

To determine which part of the service time correlates most with the total service time, 
derive the time waited to queue the consistent-read request, the time needed to build the consistent-read 
block, the time waited for a log flush and the time spent to send the completed request 
using the following three equations. 

Time needed to build the consistent-read block: 
*/
 SELECT INST_ID
,	CASE
	WHEN
	(	sum(DECODE(NAME,'global cache cr blocks served',VALUE,NULL))
	) >0
	THEN
	(	sum(DECODE(NAME,'global cache cr block build time',value,NULL))*10
	)/
	(	sum(DECODE(NAME,'global cache cr blocks served',VALUE,NULL))
	)/100
	ELSE 0
	END CASE
FROM	GV$SYSSTAT
GROUP BY INST_ID;
/*

Time waited for a log flush: 

*/
 SELECT INST_ID
,	CASE
	WHEN
	(	sum(DECODE(NAME,'global cache cr blocks served',VALUE,NULL))
	) >0
	THEN
	(	sum(DECODE(NAME,'global cache cr block flush time',value,NULL))*10
	)/
	(	sum(DECODE(NAME,'global cache cr blocks served',VALUE,NULL))
	)/100
	ELSE 0
	END CASE
FROM	GV$SYSSTAT
GROUP BY INST_ID;
/*

Time waited to send the completed request

*/
 SELECT INST_ID
,	CASE
	WHEN
	(	sum(DECODE(NAME,'global cache cr blocks served',VALUE,NULL))
	) >0
	THEN
	(	sum(DECODE(NAME,'global cache cr block send time',value,NULL))*10
	)/
	(	sum(DECODE(NAME,'global cache cr blocks served',VALUE,NULL))
	)/100
	ELSE 0
	END CASE
FROM	GV$SYSSTAT
GROUP BY INST_ID;
/*

By calculating these averages, you can account for almost all the processing steps of a consistent 
read block request. The remaining difference between the total round-trip time and the LMS service 
time per request is the queuing time for the Global Enqueue Service Daemon (LMD) to dispatch 
the request to the LMS process and the processing time for LMS and network IPC time. 

**********************************************************************************************************************

Calculate the average latency of a current block request, in other words, its round-trip time, as: 

*/
 SELECT INST_ID
,	CASE
	WHEN
	(	sum(DECODE(NAME,'global cache current blocks received',VALUE,NULL))
	) >0
	THEN
	(	sum(DECODE(NAME,'global cache current block receive time',value,NULL))*10
	)/
	(	sum(DECODE(NAME,'global cache current blocks received',VALUE,NULL))
	)/100
	ELSE 0
	END CASE
FROM	GV$SYSSTAT
GROUP BY INST_ID;

/* 

Recieving a current block

NO FURTHER INFO YET

*/
 SELECT INST_ID
,	CASE
	WHEN
	(	sum(DECODE(NAME,'global cache current blocks served',VALUE,NULL))
	) >0
	THEN
	(	sum(DECODE(NAME,'global cache current block flush time',NVL(VALUE,0),NULL))*10
	+	sum(DECODE(NAME,'global cache current block pin time',NVL(VALUE,0),NULL))*10
	+	sum(DECODE(NAME,'global cache current block send time',NVL(VALUE,0),NULL))*10
	)/
	(	sum(DECODE(NAME,'global cache current blocks served',VALUE,NULL))
	)/100
	ELSE 0
	END CASE
FROM	GV$SYSSTAT
GROUP BY INST_ID;
/*

Calculate which part of the time is spent to serve a request for a current block: 

The service time for a current block request encompasses the pin time and the send time. 
The next two equations help you determine processing times for the various steps of a cache transfer. 

Determining pin time: 

*/

 SELECT INST_ID
,	CASE
	WHEN
	(	sum(DECODE(NAME,'global cache current blocks served',VALUE,NULL))
	) >0
	THEN
	(	sum(DECODE(NAME,'global cache current block pin time',value,NULL))*10
	)/
	(	sum(DECODE(NAME,'global cache current blocks served',VALUE,NULL))
	)/100
	ELSE 0
	END CASE
FROM	GV$SYSSTAT
GROUP BY INST_ID;
/*

Determining send time: 

*/
 SELECT INST_ID
,	CASE
	WHEN
	(	sum(DECODE(NAME,'global cache current blocks served',VALUE,NULL))
	) >0
	THEN
	(	sum(DECODE(NAME,'global cache current block send time',value,NULL))*10
	)/
	(	sum(DECODE(NAME,'global cache current blocks served',VALUE,NULL))
	)/100
	ELSE 0
	END CASE
FROM	GV$SYSSTAT
GROUP BY INST_ID;
/*
**********************************************************************************************************************
Calculate the average convert times and average get times

Average convert Times:
*/
 SELECT INST_ID
,	CASE
	WHEN
	(	sum(DECODE(NAME,'global cache converts',VALUE,NULL))
	) >0
	THEN
	(	sum(DECODE(NAME,'global cache convert time',value,NULL))*10
	)/
	(	sum(DECODE(NAME,'global cache converts',VALUE,NULL))
	)/100
	ELSE 0
	END CASE
FROM	GV$SYSSTAT
GROUP BY INST_ID;

/*

Average Get time
*/
 SELECT INST_ID
,	CASE
	WHEN
	(	sum(DECODE(NAME,'global cache gets',VALUE,NULL))
	) >0
	THEN
	(	sum(DECODE(NAME,'global cache get time',value,NULL))*10
	)/
	(	sum(DECODE(NAME,'global cache gets',VALUE,NULL))
	)/100
	ELSE 0
	END CASE
FROM	GV$SYSSTAT
GROUP BY INST_ID;
/*

When analyzing the results from this step, consider the following points: 

High convert times can indicate excessive global concurrency. 

A large number of global cache gets, global cache converts, and rapid increases in average 
convert or get times indicates excessive contention for GCS operations 

For Global Enqueue Service (GES) operations, refer to "Analyzing Global Enqueue Statistics" 

Latencies for resource operations can be excessive due to overall system workload or 
system problems. 


--------------------------------------------------------------------------------
Note: 
A reasonable value for a cache get is 20 to 30 milliseconds while converts should average 10 to 20 
milliseconds.   

--------------------------------------------------------------------------------
 
Oracle increments global cache gets when a new resource is opened. A convert is counted 
when there is already an open resource and Oracle converts the resource to another mode. 

Therefore, the elapsed time for a get includes the allocation and initialization of new resources. 
If the average cache get or average convert times are excessive, then your system may be 
experiencing timeouts. 

If the global cache convert times or global cache get times are excessive, then refer to statistics 
in the V$SYSTEM_EVENT view to identify events with a high value for TIME_WAITED statistics. 

Analyze block mode conversion timeouts by examining the value for global cache convert timeouts. 
If your V$SYSSTAT output shows a value other than zero for this statistic, then check your system 
for congestion or high contention. In general, convert timeouts should not occur; their existence 
indicates serious performance problems. 

Analyze the global cache consistent-read timeouts by examining the value for this statistic in your 
V$SYSSTAT output. Oracle increments this statistic after the system waits too long for the completion 
of a consistent-read request. If this statistic shows a value other than zero, then too much time has 
elapsed after the initiation of a consistent-read request and a timeout has occurred. If this happens, 
then you also usually find that the average time for consistent-read request completions has increased. 
If you have timeouts and the latency is high, then your system may have an excessive workload or 
there may be excessive contention for data blocks. Timeouts might also indicate IPC or network problems. 

/*
**********************************************************************************************************************
LOCKING ISSUES

Calculate the average global enqueue get time using this formula:

*/

 SELECT INST_ID
,	CASE
	WHEN
	(	sum(DECODE(NAME,'global lock sync gets',VALUE,NULL))
	+	sum(DECODE(NAME,'global lock async gets',VALUE,NULL))
	) >0
	THEN
	(	sum(DECODE(NAME,'global lock get time',value,NULL))*10
	)/
	(	sum(DECODE(NAME,'global lock sync gets',VALUE,NULL))
	+	sum(DECODE(NAME,'global lock async gets',VALUE,NULL))
	)/100
	ELSE 0
	END CASE
FROM	GV$SYSSTAT
GROUP BY INST_ID;
/*

If the result is more than 20 or 30 milliseconds, then query the TIME_WAITED column in the V$SYSTEM_EVENT 
view using the descending (DESC) keyword to identify which resource events are waited for most frequently 
using this query: 

   SELECT TIME_WAITED, AVERAGE_WAIT 
   FROM V$SYSTEM_EVENT 
   ORDER BY TIME_WAITED DESC;
   
Oracle increments global lock gets when Oracle opens a new global enqueue for a resource. A convert is counted 
when there is already an open global enqueue on a resource and Oracle converts the global enqueue access mode
to another mode. 

Thus, the elapsed time for a get includes the allocation and initialization of a new global enqueue. If the average 
global enqueue get (global cache get time) or average global enqueue (from the above formula) conversion times 
are excessive, then your system may be experiencing timeouts. 

If the global enqueue conversion times or global enqueue get times are high, then refer to statistics in the 
V$SYSTEM_EVENT view to identify events with a high value for TIME_WAITED statistics


**********************************************************************************************************************

Calculate the average global lock convert time
*/
SELECT INST_ID
,	CASE
	WHEN
	(	sum(DECODE(NAME,'global lock sync converts',VALUE,NULL))
	+	sum(DECODE(NAME,'global lock async converts',VALUE,NULL))
	) >0
	THEN
	(	sum(DECODE(NAME,'global lock convert time',value,NULL))*10
	)/
	(	sum(DECODE(NAME,'global lock sync converts',VALUE,NULL))
	+	sum(DECODE(NAME,'global lock async converts',VALUE,NULL))
	)/100
	ELSE 0
	END CASE
FROM	GV$SYSSTAT
GROUP BY INST_ID;
/*

If the result is more than 20 milliseconds, then query the TIME_WAITED column in the V$SYSTEM_EVENT 
view using the DESC keyword to identify the event causing the delay. 

*/


