Here are some scripts related to Disk I/O, Events, Waits
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
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"
select sum(PHYRDS) PHYS_READS,
where df.FILE# = fs.FILE#
order by fs.PHYBLKRD+fs.PHYBLKWRT desc
SGA STAT NOTES:Statistic Name - Name of the statistic
Bytes - Size
order by NAME
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.
where NAME like 'sort%'
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
round(AVERAGE_WAIT,2) "Average Wait"
order by TOTAL_WAITS
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
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
order by CLASS
Since you are seeing this, it means that your browser does not
support cascading style sheets. Please download and use one
of the many browsers that support web standards.