Oracle File stats
[EN]
The window allows you to conduct 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
SQL
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
SQL
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 the v_$iostat_file view. Total I/O data by file types used in the Oracle DBMS
Displays disk I/O statistics information for database files (including data files, temporary files, and other file types). I/O statistics for data files and temporary files are provided on a per-file basis. All other file types (such as control files, log files, archive logs, etc.) have their own statistics.
SQL
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
Statistics on disk I/O usage by functions in the Oracle DBMS
SQL
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
[RU]
Окно позволяет проводить детальную диагностику использования файлов данных в СУБД Oracle
Detail file
Статистика по вводу/выводу для файлов БД в процентах от общего ввода/вывода.
Позволяет определить файл данных (и как следствие объекты) вносящие максимальный вклад в утилизация дисковых операций
SQL
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
Статистика по использованию подсистемы ввода/вывода сессиями в БД Oracle
SQL
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
Данные из представления v_$iostat_file. Суммарные данные по вводу/выводу по типам файлов используемых в СУБД Oracle
Отображает информацию о статистике дискового ввода-вывода файлов базы данных (включая файлы данных, временные файлы и другие типы файлов). Статистика ввода-вывода для файлов данных и временных файлов предоставляется для каждого файла. Все остальные типы файлов (например, управляющие файлы, файлы журналов, архивные журналы и т. Д.) имеют свою статистику.
SQL
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
SQL
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