Oracle File stats

The window allows you to carry out detailed diagnostics of the use of data files in the Oracle DBMS

Detail file

I/O statistics for database files as a percentage of total I/O.

Allows you to define the data file (and, as a result, objects) that make the maximum contribution to the utilization of disk operations

SELECT a.tablespace_name,
a.file_name,
a.blocks,
round ((a.phywrts * (8192/1024))/1024) phywrts,
round ((a.phyrds * (8192/1024))/1024) phyrds
FROM
(SELECT t.name tablespace_name,
d.name file_name,
d.blocks,
f.phywrts,
f.phyrds
FROM v$filestat f,
v$datafile d,
V$TABLESPACE t
WHERE f.file# = d.file#
AND d.blocks> 0
AND t.ts# = d.ts#
UNION ALL
SELECT t.name tablespace_name,
d.name file_name,
d.blocks,
f.phywrts,
f.phyrds
FROM v$tempstat f,
v$tempfile d,
V$TABLESPACE t
WHERE f.file# = d.file#
AND d.blocks> 0
AND t.ts# = d.ts#) a
order by 4 desc

Session I/O

Statistics on the use of the I/O subsystem by sessions in the Oracle database

SELECT
s.sid || ':' || s.serial# SS,
NVL (DECODE (s.type, 'BACKGROUND', 'SYS (' || b.name || ')',
s.username), substr (p.program, instr (p.program, '('))) oracle_user,
s.status status,
s.machine machine,
nvl (s.osuser, '(' || b.name || ')') os_user,
round(((i.block_gets + i.consistent_gets) * 8192)/1024/1024,3) logical_reads,
round ((i.physical_reads * 8192) / 1024 / 1024.3) physical_reads,
s.program client_program,
t.ksusestv * 10 cpu_usage,
s.logon_time logon_time,
s.sql_hash_value
FROM v$session s,
v$process p,
v$sess_io i,
x$ksusesta t,
v$bgprocess b
WHERE p.addr = s.paddr
AND i.sid = s.sid
AND t.indx = s.sid
AND t.ksusestn = 12
AND p.addr = b.paddr (+)
order by i.physical_reads desc

IOStat_File

Data from v_$iostat_file view. Total input/output data by file types used in Oracle DBMS

Displays information about disk I/O statistics for database files (including data files, temporary files, and other types of files). I/O statistics for data files and temporary files are provided for each file. All other file types (like control files, log files, archived logs, etc.) have their own statistics.

with iostat_file as
(select filetype_name, sum (large_read_reqs) large_read_reqs,
   sum (large_read_servicetime) large_read_servicetime,
   sum (large_write_reqs) large_write_reqs,
   sum (large_write_servicetime) large_write_servicetime,
   sum (small_read_reqs) small_read_reqs,
   sum (small_read_servicetime) small_read_servicetime,
   sum (small_sync_read_latency) small_sync_read_latency,
   sum (small_sync_read_reqs) small_sync_read_reqs,
   sum (small_write_reqs) small_write_reqs,
   sum (small_write_servicetime) small_write_servicetime
   from sys.v_$iostat_file
group by filetype_name)
select filetype_name, small_read_reqs + large_read_reqs "reads",
large_write_reqs + small_write_reqs "writes",
round ((small_read_servicetime + large_read_servicetime) / 1000)
   read_time_sec,
round ((small_write_servicetime + large_write_servicetime) / 1000)
   write_time_sec,
case when small_sync_read_reqs> 0 then
   round (small_sync_read_latency / small_sync_read_reqs, 2)
end avg_sync_read_ms,
round (( small_read_servicetime + large_read_servicetime
   + small_write_servicetime + large_write_servicetime)
   / 1000, 2) total_io_seconds
from iostat_file
order by 7 desc

IOStat_function

Oracle Database I/O Usage Statistics

select
function_name,
small_read_reqs + large_read_reqs reads,
small_write_reqs + large_write_reqs writes,
wait_time/1000 wait_time_sec,
case when number_of_waits> 0 then
round (wait_time/number_of_waits, 2)
end avg_wait_ms
from
v$iostat_function
order by
wait_time desc