How to calculate average LMS service time?

average LMS service time = average latency - average time to build consistent read block - average time to wait for log flush - average time to send completed block



SELECT
 average_latency AS "Average Latency",
 average_build_time AS "Average Build Time",
 average_flush_time AS "Average Flush Time",
 average_send_time AS "Average Send Time",
 average_latency - average_build_time - average_flush_time - average_send_time
 AS "Average LMS Service Time"
 FROM
 (
 SELECT
 (gc_cr_block_receive_time * 10) / gc_cr_blocks_received AS average_latency,
 (gc_cr_block_build_time * 10) / gc_cr_blocks_served AS average_build_time,
 (gc_cr_block_flush_time * 10) / gc_cr_blocks_served AS average_flush_time,
 (gc_cr_block_send_time * 10) / gc_cr_blocks_served AS average_send_time
 FROM
 (
 SELECT value AS gc_cr_block_receive_time FROM v$sysstat
 WHERE name = 'gc cr block receive time'
 ),
 (
 SELECT value AS gc_cr_blocks_received FROM v$sysstat
 WHERE name = 'gc cr blocks received'
 ),
 (
 SELECT value AS gc_cr_block_build_time FROM v$sysstat
 WHERE name = 'gc cr block build time'
 ),
 (
 SELECT value AS gc_cr_block_flush_time FROM v$sysstat
 WHERE name = 'gc cr block flush time'
 ),
 (
 SELECT value AS gc_cr_block_send_time FROM v$sysstat
 WHERE name = 'gc cr block send time'
 ),
 (
 SELECT value AS gc_cr_blocks_served FROM v$sysstat
 WHERE name = 'gc cr blocks served'
 )
 );


Read more »

How to calculate average time for consistent read block requests for the instance in milliseconds?

How to calculate average time for consistent read block requests for the instance in milliseconds

Global Cache Block Transfer Rate=(gc cr block receive time * 10) / (gc cr blocks received)


SELECT
 gc_cr_block_receive_time AS "Receive Time",
 gc_cr_blocks_received AS "Blocks Received",
 (gc_cr_block_receive_time * 10) /
 gc_cr_blocks_received AS "Average Latency (MS)"
 FROM
 (
 SELECT value AS gc_cr_block_receive_time FROM v$sysstat
 WHERE name = 'gc cr block receive time'
 ),
 (
 SELECT value AS gc_cr_blocks_received FROM v$sysstat
 WHERE name = 'gc cr blocks received'
 );

Sample query output: 
Receive Time            Blocks Received                            Average Latency (MS)
------------              ---------------                               --------------------
5305                        2179                                              24.3460303

Julian Dyke - "Pro Oracle Database 10g RAC on Linux" - p692
"The latency of a consistent block request is the time elapsed between the original request and
the receipt of the consistent block image at the local instance. Using a Gigabit Ethernet interconnect,
this value should normally be less than 5 ms and should not exceed 15 ms, although this can be
affected by system configuration and volume."

Script to check global cache activity for the table segments of a schema

SELECT
 table_name AS "Table Name",
 gc_buffer_busy AS "Buffer Busy",
 gc_cr_blocks_received AS "CR Blocks Received",
 gc_current_blocks_received AS "Current Blocks Received"
 FROM
 (
 SELECT table_name FROM dba_tables
 WHERE owner = '&schema_name'
 ) t,
 (
 SELECT object_name,value AS gc_buffer_busy
 FROM v$segment_statistics
 WHERE owner = '&schema_name'
 AND object_type = 'TABLE'
 AND statistic_name = 'gc buffer busy'
 ) ss1,
 (
 SELECT object_name,value AS gc_cr_blocks_received
 FROM v$segment_statistics
 WHERE owner = '&schema_name'
 AND object_type = 'TABLE'
 AND statistic_name = 'gc cr blocks received'
 ) ss2,
 (
 SELECT object_name,value AS gc_current_blocks_received
 FROM v$segment_statistics
 WHERE owner = '&schema_name'
 AND object_type = 'TABLE'
 AND statistic_name = 'gc current blocks received'
 ) ss3
 WHERE t.table_name = ss1.object_name
 AND t.table_name = ss2.object_name
 AND t.table_name = ss3.object_name;


Note: this script is copied from Julian Dyke's book "Pro Oracle Database 10g RAC on Linux", p691