v$filemetric_history


col AVERAGE_READ_TIME for 9999.999
col FS for a15
col host_name for a15
select HOST_NAME,substr(file_name,1,(instrb(file_name,'/',2,1) - 1)) FS,avg(AVERAGE_READ_TIME *10 ) AVERAGE_READ_TIME,
sum(PHYSICAL_READS + PHYSICAL_BLOCK_READS) Reads
from gv$filemetric_history fh,dba_data_files df, gv$instance i
where df.file_id = fh.file_id and AVERAGE_READ_TIME > 0 and i.INST_ID = fh.INST_ID
group by HOST_NAME,substr(file_name,1,(instrb(file_name,'/',2,1) - 1));

col AVERAGE_WRITE_TIME for 9999.999
col FS for a15
col host_name for a15
select HOST_NAME,substr(file_name,1,(instrb(file_name,'/',2,1) - 1)) FS,avg(AVERAGE_WRITE_TIME * 10) AVERAGE_WRITE_TIME,
sum(PHYSICAL_WRITES + PHYSICAL_BLOCK_WRITES) Writes
from gv$filemetric_history fh,dba_data_files df, gv$instance i
where df.file_id = fh.file_id and AVERAGE_WRITE_TIME > 0 and i.INST_ID = fh.INST_ID
group by HOST_NAME,substr(file_name,1,(instrb(file_name,'/',2,1) - 1));

col AVERAGE_WRITE_TIME for 99999.999
col FS for a15
col host_name for a15
select HOST_NAME,to_char(END_TIME,'HH24:MI') EndTime,substr(file_name,1,(instrb(file_name,'/',2,1) - 1)) FS,avg(AVERAGE_WRITE_TIME * 10) AVERAGE_WRITE_TIME,
sum(PHYSICAL_WRITES + PHYSICAL_BLOCK_WRITES) Writes
from gv$filemetric_history fh,dba_data_files df, gv$instance i
where df.file_id = fh.file_id and AVERAGE_WRITE_TIME > 0 and i.INST_ID = fh.INST_ID
group by HOST_NAME,to_char(END_TIME,'HH24:MI'),substr(file_name,1,(instrb(file_name,'/',2,1) - 1));

Advertisements
%d bloggers like this: