Топ-100
top of page
CerebroSQL

Oracle File stats

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

FileStat general

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

Oracle detail file stats - CerebroSQL

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

Oracle session I/O

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.

Oracle I/O Stat_file

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

Oracle I/O Stat_function

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

bottom of page