col name format a45
select name,phyrds,phywrts,avgiotim
from v$datafile df,
v$filestat fs
where df.file#=fs.file#;
/*

Oracle recommends that the avg latency of a consitent block request shoudl typically be about 15ms depending on the system configuration and volume.  The average latency of a consitent blcok request is the average latency of a consitent=read request round trip from the requesting instance to the holding instance and back to the requesting instance.

*/

 

set numwidth 20
set pages 150
set lines 100

column “AVG CR BLOCK RECEIVE TIME (ms)” format 9999999.9
select
b1.inst_id,
b2.value “GCS CR BLOCKS RECEIVED”,
b1.value “GCS CR BLOCK RECEIVE TIME”,
((b1.value/b2.value)*10) “AVG CR BLOCK RECEIVE TIME (ms)”
from gv$sysstat b1,
gv$sysstat b2
where b1.name = ‘gc cr block receive time’
and b2.name = ‘gc cr blocks received’
and b1.inst_id = b2.inst_id;

 

/*
current reads:

average time to build a consistent read block is calculated as follows
gc cr block build time/gc cr blocks served

average time spent waiting for a redo log flush is calcuated as follows:
gc cr block flush time/gc cr blocks served

average time to send a completed block is calculated as follows:
gc cr block send time/gc cr blocks served

the followign query can be used to calculate the average LMS service time for consitent block reads:

 

FYI
the difference between the average latency time and the sum of the average build, flush, and send times represents the time spent in the LMS service and the time spent transmitting the meassages across the interconnect.

*/

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’
)
);

/*
current blocks

average latency involved in processing requests for current blocks using the following:

*/

select
gc_current_block_receive_time as “Receive Time”,
gc_current_blocks_received as “Blocks Received”,
(gc_current_block_receive_time * 10) / gc_current_blocks_received as “Average (MS)”
from
(
select value as gc_current_block_receive_time from v$sysstat
where name = ‘gc current block receive time’
),
(
select value as gc_current_blocks_received
from v$sysstat
where name = ‘gc current blocks received’
);

/*
amount of overall latency that can be attributed to the LMS process using the following:

average LMS Service time = average latency – average time to pin current blocks – avg time to wait for log flush – average time to send completed blocsk

*/

 

select
average_latency as “Average Latency”,
average_pin_time as “Average Pin Time”,
average_flush_time as “Average Flush Time”,
average_send_time as “Average Send Time”,
average_latency – average_pin_time – average_flush_time – average_send_time
as “Average LMS Service Time”
from
(
select
(gc_current_block_receive_time * 10) / gc_current_blocks_received as average_latency,
(gc_current_block_pin_time * 10) / gc_current_blocks_served as average_pin_time,
(gc_current_block_flush_time * 10) / gc_current_blocks_served as average_flush_time,
(gc_current_block_send_time * 10) / gc_current_blocks_served as average_send_time
from
(
select value as gc_current_block_receive_time from v$sysstat
where name = ‘gc current block receive time’
),
(
select value as gc_current_blocks_received from v$sysstat
where name = ‘gc current blocks received’
),
(
select value as gc_current_block_pin_time from v$sysstat
where name = ‘gc current block pin time’
),
(
select value as gc_current_block_flush_time from v$sysstat
where name = ‘gc current block flush time’
),
(
select value as gc_current_block_send_time from v$sysstat
where name = ‘gc current block send time’
),
(
select value as gc_current_blocks_served from v$sysstat
where name = ‘gc current blocks served’
)
);

 

select
namespace ,
dlm_lock_requests,
dlm_pin_requests,
dlm_pin_releases,
dlm_invalidation_requests,
dlm_invalidations
from v$librarycache;