CREATE TABLE sum_read_writes
AS SELECT SUM(phyrds) phys_reads, sum(phywrts) phys_wrts
FROM V$FILESTAT;
TTITLE ' DATAFILE DISK I/O REPORT'
COLUMN name FORMAT a30
COLUMN phyrds FORMAT 999,999,999
COLUMN phywrts FORMAT 999,999,999
COLUMN read_pct FORMAT 999.99
COLUMN write_pct FORMAT 999.99
SELECT name,
phyrds,
phyrds * 100 / srw.phys_reads read_pct,
phywrts,
phywrts * 100 / srw.phys_wrts write_pct
FROM sum_read_writes srw, v$datafile df, v$filestat fs
WHERE df.file# = fs.file#
ORDER BY phyrds desc;
DROP TABLE sum_read_writes;
clear columns
TTITLE off
select t.snap_id,t.filename,READTIM,PHYRDS, READTIM/PHYRDS ,SINGLEBLKRDTIM/SINGLEBLKRDS from DBA_HIST_FILESTATXS t where t.file#=3370
select *from DBA_HIST_FILESTATXS
select *From dba_hist_snapshot
CREATE TABLE sum_read_writes_53572
AS SELECT SUM(phyrds) phys_reads, sum(phywrts) phys_wrts
FROM DBA_HIST_FILESTATXS t where t.snap_id=53572;
CREATE TABLE sum_read_writes_53472
AS SELECT SUM(phyrds) phys_reads, sum(phywrts) phys_wrts
FROM DBA_HIST_FILESTATXS t where t.snap_id=53472;
select *from sum_read_writes_53572;
select *from sum_read_writes_53472;
SELECT filename,
phyrds,
phyrds * 100 / srw.phys_reads read_pct,
phywrts,
phywrts * 100 / srw.phys_wrts write_pct,
READTIM/PHYRDS ,SINGLEBLKRDTIM/SINGLEBLKRDS
FROM sum_read_writes_53572 srw,DBA_HIST_FILESTATXS fs
where fs.snap_id=53572
ORDER BY phyrds desc;
SELECT filename,
phyrds,
phyrds * 100 / srw.phys_reads read_pct,
phywrts,
phywrts * 100 / srw.phys_wrts write_pct,
READTIM/PHYRDS ,SINGLEBLKRDTIM/SINGLEBLKRDS
FROM sum_read_writes_53472 srw,DBA_HIST_FILESTATXS fs
where fs.snap_id=53472
ORDER BY phyrds desc;
Applies to:
Oracle Database - Enterprise Edition - Version 11.2.0.4 and later
Oracle Database Cloud Schema Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Information in this document applies to any platform.
Purpose
In 11.2.0.4 or later, new stats "1-bk Rds/s" and "Av 1-bk Rd(ms)" appear in "Tablespace IO Stats" and "File IO Stats" in AWR report.
What is the difference between the old stats "Av Rds/s", "Av Rd(ms)" and the new stats "1-bk Rds/s" and "Av 1-bk Rd(ms)"?
Scope
Details
The following columns are the combined statistics for both single block reads and multi block reads:
Av Rds/s : Number of physical reads per second
Av Rd(ms) : Average read time of physical read
The following columns are related to single block reads only:
1-bk Rds/s : Number of single block reads per second
Av 1-bk Rd(ms) : Average read time of single block read
"Av Rd(ms)" is obtained by computing READTIM/PHYRDS.
"Av 1-bk Rd(ms)" is obtained by computing SINGLEBLKRDTIM/SINGLEBLKRDS
All the stats above come from the columns of DBA_HIST_FILESTATXS:
PHYRDS : Number of physical reads
READTIM : Cumulative physical read time (in hundredths of a second)
SINGLEBLKRDS : Number of single block reads
SINGLEBLKRDTIM : Cumulative single block read time (in hundredths of a second)
