This pl/sql will report average read time and write time for a specific asm disk with sample times every 3 seconds. Change the group number and disk number to the disk you which to sample.
set serveroutput on
alter session set nls_date_format = "DD-MON-YYYY HH24:MI:SS";
declare
cursor c_asm_io is
select group_number, disk_number, reads, read_time, writes, write_time
from v$asm_disk_stat
where group_number = 1 --Enter diskgroup number
and disk_number = 0; --Enter diskgroup number
v_date date;
v_group_number number;
v_disk_number number;
v_reads number;
v_read_time number;
v_writes number;
v_write_time number;
v_read_prev number;
v_read_time_prev number;
v_write_prev number;
v_write_time_prev number;
v_reads_delta number;
v_read_time_delta number;
v_writes_delta number;
v_write_time_delta number;
begin
for i in 1 .. 6 loop
for rec in c_asm_io loop
v_group_number := rec.group_number;
v_disk_number := rec.disk_number;
v_reads := rec.reads;
v_read_time := rec.read_time;
v_writes := rec.writes;
v_write_time := rec.write_time;
select sysdate into v_date from dual;
dbms_output.put_line('Time: '||v_date);
--Total reads and writes
dbms_output.put_line('Disk#: '||v_disk_number||' total_reads: '||v_reads||' total_writes: '||v_writes);
--Get the deltas
v_reads_delta := v_reads - v_read_prev;
v_writes_delta := v_writes - v_write_prev;
dbms_output.put_line('Delta reads: '||v_reads_delta||' Delta writes: '||v_writes_delta);
--read times
v_read_time_delta := v_read_time - v_read_time_prev;
if v_reads_delta > 0 then
dbms_output.put_line(' avg_rd_tm(ms): '||(v_read_time_delta/v_reads_delta)*1000);
else
dbms_output.put_line(' avg_rd_tm(ms): 0.0');
end if;
--wrtie times
v_write_time_delta := v_write_time - v_write_time_prev;
if v_writes_delta > 0 then
dbms_output.put_line(' avg_wr_tm(ms): '||(v_write_time_delta/v_writes_delta)*1000);
else
dbms_output.put_line(' avg_wr_tm(ms): 0.0');
end if;
v_read_prev := v_reads;
v_write_prev := v_writes;
v_read_time_prev := v_read_time;
v_write_time_prev := v_write_time;
dbms_lock.sleep(3);
dbms_output.put_line('--');
end loop;
end loop;
end;
/
******************************************
keywords: asm performance
******************************************
rdbms version: 11g
******************************************
No comments:
Post a Comment