Топ-100
 
CerebroSQL

Oracle File stats

The window allows for 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 determine the data file (and, as a consequence, objects) that make the maximum contribution to the utilization of disk operations

Detail file.jpg

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

Session IO.jpg

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. Summary data on I/O by file types used in Oracle DBMS

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

IOStat_file.jpg

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

IOStat_function.jpg

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