Home > Oracle > file system I/O usage

file system I/O usage


Script to find out last hour  READ statistics in FS level.

col AVERAGE_READ_TIME for 999.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) AVERAGE_READ_TIME,
sum( 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));

Script to find out last hour WRITE statistics in FS level.

col AVERAGE_WRITE_TIME for 999.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) AVERAGE_WRITE_TIME,
sum( 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));

Average time is Centi Seconds – 1/ 100th of a second.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: