jump to content

Disk I/O, Events, Waits

Here are some scripts related to Disk I/O, Events, Waits .

Datafile I/O

DATAFILE I/O NOTES:

  • 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

    SGA Stats

    SGA STAT NOTES:

  • Statistic Name - Name of the statistic
  • Bytes - Size 

    select 	NAME,
	BYTES
from 	v$sgastat
order	by NAME

    Sort Stats

    SORT NOTES:

  • Sort Parameter - Name of the sort parameter
  • Value - Number of sorts

  • sorts (memory) - The number of sorts small enough to be performed entirely in sort areas without using temporary segments.
  • sorts (disk) - The number of sorts that were large enough to require the use of temporary segments for sorting.
  • sorts (rows) - Number of sorted rows

  • The memory area available for sorting is set via the SORT_AREA_SIZE and SORT_AREA_RETAINED_SIZE init.ora parameters. 

    select 	NAME,
	VALUE
from 	v$sysstat
where  	NAME like 'sort%'

    All Events

    SYSTEM EVENT (ALL) NOTES:

  • Event Name - Name of the event
  • Total Waits - Total number of waits for the event
  • Total Timeouts - Total number of timeouts for the event
  • Time Waited - The total amount of time waited for this event, in hundredths of a second
  • Average Wait - The average amount of time waited for this event, in hundredths of a second 

    select 	EVENT,
	TOTAL_WAITS,
	TOTAL_TIMEOUTS,
	TIME_WAITED,
	round(AVERAGE_WAIT,2) "Average Wait"
from 	v$system_event
order	by TOTAL_WAITS

    All Statistics

    SYSTEM STATISTICS (ALL) NOTES:

  • Stat# - Number of the statistic
  • Name - Name of the statistic
  • Class - Statistic class: 1 (User), 2 (Redo), 4 (Enqueue), 8 (Cache), 16 (OS), 32 (Parallel Server), 64 (SQL), 128 (Debug)
  • Value - Value of the statistic 

    select 	STATISTIC#,
	NAME,
	CLASS,
	VALUE
from 	v$sysstat

    Wait Stats

    WAIT STATISTIC NOTES:

  • Class - Class of block subject to contention
  • Count - Number of waits by this OPERATION for this CLASS of block
  • Time -Sum of all wait times for all the waits by this OPERATION for this CLASS of block

  • Data Blocks - Usually occurs when there are too many modified blocks in the buffer cache; reduce contention by adding DBWR processes.
  • Free List - May occur if multiple data loading programs run simultaneously.
  • Segment Header - May occur when may full table scans execute simultaneously with data loading processes; aggravated by the parallel options. Reschedule data loading jobs to reduce contention;
  • Sort Block - Rarely seen except when the Parallel Query option is used; reduce contention by reducing the degree of parallelism or decreasing the SORT_AREA_SIZE init.ora parameter setting.
  • Undo Block - Very rarely occurs; may be caused by multiple users updating records in the same data block at a very fast rate; contention can usually be resolved by increasing the PCTFREE of the tables being modified.
  • Undo Header - May occur if there are not enough rollback segments to support the number of concurrent transactions. 

    select 	CLASS,
	COUNT,
	TIME
from  	v$waitstat
order	by CLASS