Oracle/HP Best Practices Guide for HP IO Accelerators

Discovering your application I/O profile and selecting an architecture 13
Discovering your application I/O profile and
selecting an architecture
Using Oracle tools
Understanding how your database uses its storage resources is the first step in selecting an appropriate
architecture and applying any needed performance tunings. You can identify where the I/O bottlenecks
are in your system. Then you can choose from a list of recommended architectures to help eliminate the
bottlenecks.
Oracle Enterprise Manager
Determining what causes an I/O bottleneck in Oracle might be challenging. The Oracle tools you can
use to accomplish this analysis are EM and the AWR and ADDM reports. With EM, you can easily detect
I/O issues by looking for sessions or SQL statements that have high I/O waits. You can use EM to drill
down into these sessions to look at the underlying SQL execution plans, or you can invoke an AWR report
from the EM interface. Running AWR from EM provides further opportunity for drill-down analysis.
You can also create the AWR and ADDM reports from the SQL command line, as described in the
following sections.
Running an AWR report
You can generate an AWR for a database from EM or from the SQL command line, by using the
awrrpt.sql SQL script. Beginning with Oracle 11g R2, you can use the awrgrpt.sql SQL script to
create an AWR report that includes all available database instances in an Oracle RAC environment.
Optionally, you can use the awrgrpti.sql SQL script to create the AWR report for a single instance or
a combination of instances of the RAC database. These scripts can be found in the
$ORACLE_HOME/rdbms/admin directory.
The AWR report script generates an HTML or text report that displays statistics for a range of snapshot
IDs.
NOTE: When creating the AWR report, HP recommends selecting the HTML option.
To generate an AWR report:
1. At the SQL prompt, enter the following command:
@$ORACLE_HOME/rdbms/admin/awrrpt.sql
2. Specify whether you want an HTML or a text report. For example, enter the value for report_type
as HTML.
3. Specify the number of days for which you want to list snapshot IDs.
4. Enter 2 as the value for num_days. A list of existing snapshots for the specified time range appears.