| Friday, June 19, 2009 |
| Disk I/O, Events, Waits |
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 CLASSLabels: Disk I/O, Events, Waits |
posted by Srinivasan .R @ 12:50 AM  |
|
|
|
