Oracle: I/O

With buffered I/O, the data which is read (and written?) using the read() (and write()?) syscalls are buffered in a cache maintained by the OS . With direct I/O, read() and write()` is not buffered by the OS but directly written or read from/to the device.

Because Oracle maintains its own buffer cache, buffered I/O is generally not desirable.

In Linux, the amount of memory set aside for buffered I/O is shown under cache in the memory heading with

vmstat -S m

select *
from
   v$metricname
where
   group_name = 'I/O Stats by Function Metrics' or
   metric_name like '%I/O%'
order by
   group_name,
   metric_name;

select * from v$sysmetric where metric_name like '%I/O%';

select * from v$statname where name like '%I/O%';

select * from v$event_name where wait_class in ('User I/O', 'System I/O') or name like '%I/O%' order by wait_class, name;

select * from v$io_calibration_status;

select * from dba_hist_iostat_filetype;
select * from dba_hist_iostat_detail;
select * from dba_hist_iostat_function;
select * from dba_hist_iostat_filetype_name;
select * from dba_hist_iostat_function_name;
select * from dba_rsrc_io_calibrate;
select * from dba_hist_comp_iostat;
select * from dba_hist_sess_time_stats /* Especially columns sum_sys_io_wait and sum_user_io_wait */;

The buffer cache

A server process does not directly write to data files. Rather, it writes to the buffer cache and lets background processes do the I/O.

The init parameters

• dbwr_io_slaves
• disk_async_io
• fileio_network_adapters

Events

• direct path read
• direct path read (lob)

ORA-17002: I/O Error

IOSEEKTIM, IOTFRSPEED, MAXTHR (Oracle Optimizer Tracing)

catio.sql ($ORACLE_HOME/rdbms/admin)

ASM disk groups try to distribute data such that it can be accessed in a balanced fashion.

x$kcfio

ksf is the Kernel Service File I/O layer.

The KCBL (Kernel Cache Buffer Load) module

Events 10229, 10245, 10284, 10297, 10298

performance and tuning