Generating Automatic Workload Repository Reports:

An AWR Report shows data captured between two snapshots (or two points in time).The AWR reports are divided into multiple sections. The HTML report includes links that can be used to navigate quickly between sections.

You can generate AWR reports using Oracle Enterprise Manager and by running SQL scripts, as described in the following sections:

A. Generating an AWR Report

B. Generating an Oracle RAC AWR Report

C. Generating an AWR Report on a Specific Database Instance

D. Generating an Oracle RAC AWR Report on Specific Database Instance

E. Generating an AWR Report for a SQL Statement

F. Generating an AWR Report for a SQL Statement on a Specific Database Instance

To run these scripts, you must be granted the DBA role.

A. Generating an AWR Report

At the SQL prompt, enter:

SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql

Enter value for report_type: html

Enter value for num_days: 2

Enter value for begin_snap: 26142

Enter value for end_snap: 26164

Enter value for report_name: awrrpt_26142_26164.html

B. Generating an Oracle RAC AWR Report

At the SQL prompt, enter:

SQL>@$ORACLE_HOME/rdbms/admin/awrgrpt.sql

Enter value for num_days: 2

Enter value for begin_snap: 26142

Enter value for end_snap: 26164

Enter value for report_name: awrrpt_RAC_26142_26164.html

C. Generating an AWR Report on a Specific Database Instance

SQL> @$ORACLE_HOME/rdbms/admin/awrrpti.sql

Instances in this Workload Repository schema

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name      Instance     Host

------------ -------- ------------ ------------ ------------

* 1328410674        1 ORCL         orcl1        css-db-test-

                                                1

  1328410674        2 ORCL         orcl2        css-db-test-

                                                2

Using 1328410674 for database Id

Enter value for inst_num: 1

Using 1 for instance number

Enter value for num_days: 2

Enter value for begin_snap: 26142

Enter value for end_snap: 26164

Enter value for report_name: awrrpt_26142_26164.html

D. Generating an Oracle RAC AWR Report on Specific Database Instance

SQL>@$ORACLE_HOME/rdbms/admin/awrgrpti.sql

Specify the Report Type

~~~~~~~~~~~~~~~~~~~~~~~

Would you like an HTML report, or a plain text report?

Enter 'html' for an HTML report, or 'text' for plain text

Defaults to 'html'

Type Specified:  html

Instances in this Workload Repository schema

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name      Instance     Host

------------ -------- ------------ ------------ ------------

* 1328410674        1 ORCL         orcl1        css-db-test-

                                                1

* 1328410674        2 ORCL         orcl2        css-db-test-

                                                2

Enter value for dbid: 1328410674

Enter value for num_days: 2

Enter value for begin_snap: 26142

Enter value for end_snap: 26164

Enter value for report_name: awrrpt_RAC_26142_26164.html

E. Generating an AWR Report for a SQL Statement

SQL>@$ORACLE_HOME/rdbms/admin/awrsqrpt.sql

Enter value for report_type: html

Enter value for num_days: 2

Enter value for begin_snap: 26142

Enter value for end_snap: 26164

Enter value for sql_id: 2b064udsjh1l

Enter value for report_name: awrrpt_1_26142_26164.html

F. Generating an AWR Report for a SQL Statment on a Specific Database Instance

SQL>@$ORACLE_HOME/rdbms/admin/awrsqrpi.sql

Enter value for report_type: html

Type Specified:  html

Instances in this Workload Repository schema

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name      Instance     Host

------------ -------- ------------ ------------ ------------

* 1328410674        1 ORCL         orcl1        css-db-test-

                                                1

  1328410674        2 ORCL         orcl2        css-db-test-

                                                2

Enter value for dbid: 1328410674

Enter value for inst_num: 1

Enter value for num_days: 2

Enter value for begin_snap: 26142

Enter value for end_snap: 26164

Enter value for sql_id: 2b064udsjh1l

Enter value for report_name: awrrpt_1_26142_26164.html

Generating Automatic Workload Repository Compare Periods Reports:

While an AWR report shows AWR data between two snapshots (or two points in time), the AWR Compare periods report shows the difference between two periods (or two AWR reports, which equates to four snapshots). Using the AWR Compare Periods report helps you to identify detailed performance attributes and configuration settings that differ between two time priods.

A. Generating an AWR Compare Periods Report

B. Generating an Oracle RAC AWR Compare Periods Report

C. Generating an AWR Compare Periods Report on a Specific Database Instance

D. Generating an Oracle RAC AWR Compare Periods Report on Specific Database Instance

A. Generating an AWR Compare Periods Report

SQL>@$ORACLE_HOME/rdbms/admin/awrddrpt.sql

Enter value for report_type: html

Enter value for num_days: 2

Enter value for begin_snap: 26142

Enter value for end_snap: 26164

Specify the number of days for which you want to list snapshot IDs in the second time period.

Enter value for num_days2: 1

Enter value for begin_snap2: 26156

Enter value for end_snap2: 26164

Enter value for report_name: awrdiff_1_26142_26164.html

B. Generating an Oracle RAC AWR Compare Periods Report

SQL>@$ORACLE_HOME/rdbms/admin/awrgdrpt.sql

Enter value for report_type: html

Enter value for num_days: 2

Enter value for begin_snap: 26142

Enter value for end_snap: 26164

Specify the number of days for which you want to list snapshot IDs in the second time period.

Enter value for num_days2: 1

Enter value for begin_snap2: 26156

Enter value for end_snap2: 26164

Enter value for report_name: awrracdiff_1_26142_26164.html

C. Generating an AWR Compare Periods Report on a Specific Database Instance

SQL>@$ORACLE_HOME/rdbms/admin/awrddrpi.sql

Enter value for report_type: html

Instances in this Workload Repository schema

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name      Instance     Host

------------ -------- ------------ ------------ ------------

* 1328410674        1 ORCL         orcl1        css-db-test-

                                                1

  1328410674        2 ORCL         orcl2        css-db-test-

                                                2

Database Id and Instance Number for the First Pair of Snapshots

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for dbid: 1328410674

Enter value for inst_num: 1

Enter value for num_days: 2

Enter value for begin_snap: 26142

Enter value for end_snap: 26164

Enter value for dbid2: 1328410674

Enter value for inst_num2: 2

Enter value for num_days2: 2

Enter value for begin_snap2: 26152

Enter value for end_snap2: 26165

Enter value for report_name: awrdiff_1_26142_26164.html

D. Generating an Oracle RAC AWR Compare Periods Report on Specific Database Instance

SQL>@$ORACLE_HOME/rdbms/admin/awrgdrpi.sql

Enter value for report_type: html

Enter value for dbid: 1328410674

Enter value for instance_numbers_or_all: 1

Enter value for num_days: 2

Enter value for begin_snap: 26138

Enter value for end_snap: 26165

Enter value for dbid2: 1328410674

Enter value for instance_numbers_or_all2: 2

Enter value for num_days2: 2

Enter value for begin_snap: 26138

Enter value for end_snap: 26165

Enter value for report_name: awrracdiff_1_26142_26164.html