Download
1 / 61
720 likes | 967 Views
Oracle AWR. Prepared For: XYZ Corp Data Collected on 2011-09-07. Agenda. This Storage Assessment is organized in the following sections: Introduction Summary IO and Load Details IO and Load Details by Instance (if RAC) Top 10 Tablespaces Top 10 Wait Events – IO and Non-IO Related
E N D
Oracle AWR Prepared For: XYZ Corp Data Collected on 2011-09-07
Agenda • This Storage Assessment is organized in the following sections: • Introduction • Summary • IO and Load Details • IO and Load Details by Instance (if RAC) • Top 10 Tablespaces • Top 10 Wait Events – IO and Non-IO Related • Burst Analysis • The following additional files were provided: • Details.xlsx: Additional information to provide further detail
Introduction - Methodology • The disk estimates in this report are calculated by analyzing the RAID-adjusted IOPS (read + write) and estimating the number of required spindles to sustain the workload. These sizing estimates use EMC's ratings for sustained IOPS, not burst, for each drive speed. • These estimates are performance based estimates based on the sample data supplied. These estimates do not consider database capacity, capacity growth or specific EMC array capabilities. These estimates make up one part of the recommended EMC architecture. • This analysis does not include additional spindles required for standard best-practice database components, such as redo, archive logs, backups to disk, replication and clones.
Introduction - Drive Calculations • For RAID-5 and RAID-10 adjusted IOPs • RAID-5 Adjusted IOPS=Physical read IO requests per second + 4*(Physical write IO requests per second + Redo writes per second) • RAID-10 Adjusted IOPS=Physical read IO requests per second + 2*(Physical write IO requests per second + Redo writes per second) • Note: the metrics used for RAID adjusted IOPs exclude archive log, RMAN backup or restore, and Flashback database IOPs. RAID-5 or RAID-6 10k RPM or 7200 RPM RAID groups or pools are usually sufficient for the sequential IOPs generated by these processes • For the RAID-5 and RAID-10 drive counts, the relevant adjusted RAID IOPs figure is divided by the IOPs rating for each drive type: 180 IOPs for 15k RPM and 2500 IOPs for EFDs.
Introduction - Additional Calculations • The following calculations are used throughout this report • % Physical Read IO to Total IO = 100 * Physical read IO requests per second / (Physical read IO requests per second + Physical write IO requests per second + Redo writes per second) • % Percentage physical reads to logical (total) reads = 100*(physical reads per sec / logical reads per sec). These are Oracle data block reads, not read IO calls. • Read Bandwidth (MB/sec) = Physical reads total bytes per second / (1024*1024) • Write Bandwidth (MB/sec) = Physical writes total bytes per second / (1024*1024) • Note: This report shows both 95th percentiles and peak values. When less than 20 samples (AWR reports) are provided, 95th percentile will be mathematically equivalent to the peak value.
IOPS and Drive Estimates By Instance These maximums may occur at different times and should not be added to get a total drive estimate.
Tablespaces • Oracle tablespaces are logical containers holding various database segments, such as of tables, indices or their partitions, undo or rollback, system meta-data, and temporary sort area. • Underlying a tablespace are one or more files or devices • IO contention is indicated where tablespaces are showing average read times in milliseconds (Av Rd(ms)) equal to or greater than 20 milliseconds, though with modern array caching, 10 milliseconds or more is becoming the indicator of IO contention. • EFDs show their best performance gains with small-block random-read OLTP-like workloads. See Blocks Per Read for read size, where a block is an Oracle data block. The default Oracle data block size is on the Input Summary slide. • Databases showing IO contention via high values for Av Rd ms and showing small block random reads (8-16KB) via Blocks per Read, in conjunction with the database block size, are ideal candidates for EFDs.
Top 10 Tablespaces (by Total IOs) (xyz1) Due to the changing locality of database activity, the listed tablespaces can show discontinuities.
Wait Events • The wait events analyzed here are from the Top 5 Timed/Waited Events section of the AWR reports. • Response time can be thought of as the sum of service time, i.e. CPU time, plus wait time. • Gaps indicate that an event was not in the Top 5 for a particular sample. • Oracle uses the terms sequential and scattered to mean the opposite. "Sequential" implies random IOs, and "scattered" implies sequential IOs. • Use these slides to evaluate indicators of IO contention, as well as suitability for EFDs. • EFDs show their best performance gains with random read IOs, indicated as "sequential reads". EFDs show good to moderate gains with sequential read IOs, indicated as "scattered reads". • The column '% Total Call Time' represents the percentage of time during processing an Oracle process was waiting on this IO related wait event and 'Av Wait MS' is the average process wait time in milliseconds.
Wait Events(IO) (xyz1) Extracted from the "Top 5 Timed Events" section of AWR/StatsPack report.
