Scripts: Disk I/O, Events, Waits (Contention issues)
disk contention problem as well as contention issues for Latches.
DATAFILES I/O:
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
File Name - Datafile name
Physical Reads - Number of physical reads
Reads % - Percentage of physical reads
Physical Writes - Number of physical writes
Writes % - Percentage of physical writes
Total Block I/O's - Number of I/O blocks
Use this report to identify any "hot spots" or I/O contention
Select NAME,
PHYRDS "Physical Reads",
round((PHYRDS / PD.PHYS_READS)*100,2) "Read %",
PHYWRTS "Physical Writes",
round(PHYWRTS * 100 / PD.PHYS_WRTS,2) "Write %",
fs.PHYBLKRD+FS.PHYBLKWRT "Total Block I/O's"
from (
select sum(PHYRDS) PHYS_READS, sum(PHYWRTS) PHYS_WRTS
from v$filestat
) pd, v$datafile df, v$filestat fs
where df.FILE# = fs.FILE#
order by fs.PHYBLKRD+fs.PHYBLKWRT desc;
* A brif difference between phyrds and phyblkrd implies table scan are going on. Check the aplication SQL statements. I/O shoud be spread evenly across drives.
column tablespace format a20
column file_name format a50
select d.tablespace_name as Tablespace, d.file_name, f.phyrds,
f.phyblkrd, f.readtim, f.phywrts, f.phyblkwrt, f.writetim
from v$filestat f, dba_data_files d
where f.file# = d.file_id
order by tablespace_name, file_name;
* Check for full table scan operations. Investigate the need for full table scans. Consider specify the DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter.
Select name, value
from v$sysstat
where name like '%table scan%';
* Monitor long-running full table scans.
Select SID, ELAPSED_SECONDS as el_sec, lpad( to_char( trunc(TIME_REMAINING/60) ) ,4,' ') || 'm ' || lpad( '(' || to_char(TIME_REMAINING), 6, ' ') || 's)' as TIME_REMAINING,
substr( MESSAGE,1,85 ) as message,
substr( USERNAME,1,10 ) as username,
START_TIME, SOFAR, TOTALWORK,
substr( OPNAME||' '||TARGET||' '||TARGET_DESC,1,40) as oper,
substr(UNITS,1,10) as units
from v$session_longops vl
where totalwork > sofar
and exists( select 1 from v$session s where sid = vl.sid
and (select spid from v$process where addr = s.paddr) is not null )
order by 3 desc, 1;
