How to Create AWR Report in Oracle 10g?

he remaining procedures in the dbms_workload_repository package are awr_report_text and awr_report_html, which generate the AWR report for the specified snapshot range in text or HTML formats, respectively.  The following script segment shows how to retrieve the AWR text report for any snapshot range or duration:

SELECT

   output 

FROM    TABLE(dbms_workload_repository.awr_report_text (45634523,1,5600,5612 ));

The sample output below shows the typical report generated for AWR data.  The output displays shows the four arguments to the awr_report_text stored procedure:

§       The database ID is 37933856.

§       The instance number for RAC is 1.

§       The starting snapshot number is5600

§       The ending snapshot number is5612

AWR Reports |Workload Repository Reports in Oracle 10g

Oracle provide two scripts to produce workload repository reports (awrrpt.sql and awrrpti.sql). They are similar in format to the statspack reports and give the option of HTML or plain text formats. The two reports give essential the same output but the awrrpti.sql allows you to select a single instance. The reports can be generated as follows:

@$ORACLE_HOME/rdbms/admin/awrrpt.sql
@$ORACLE_HOME/rdbms/admin/awrrpti.sql

The scripts prompt you to enter the report format (html or text), the start snapshot id, the end snapshot id and the report filename. The resulting report can be opend in a browser or text editor accordingly.

What are the Workload Repository Views AWR Views in Oracle 10g?

Workload Repository Views

The following workload repository views are available:

• V$ACTIVE_SESSION_HISTORY - Displays the active session history (ASH) sampled every second.
• V$METRIC - Displays metric information.
• V$METRICNAME - Displays the metrics associated with each metric group.
• V$METRIC_HISTORY - Displays historical metrics.
• V$METRICGROUP - Displays all metrics groups.
• DBA_HIST_ACTIVE_SESS_HISTORY - Displays the history contents of the active session history.
• DBA_HIST_BASELINE - Displays baseline information.
• DBA_HIST_DATABASE_INSTANCE - Displays database environment information.
• DBA_HIST_SNAPSHOT - Displays snapshot information.
• DBA_HIST_SQL_PLAN - Displays SQL execution plans.
• DBA_HIST_WR_CONTROL - Displays AWR settings.