AWR Introductory Reference

Production AWR, the simplest step! Getting Started reference

--generates AWR reports for the entire database, running script Awrrpt.sql.

@?/rdbms/admin/awrrpt.sql

--Generates a AWR report for an instance and runs the script Awrrpti.sql.

@?/rdbms/admin/awrrpti.sql

--Generates a AWR report of an SQL statement, running a script awrsqrpt.sql.

@?/rdbms/admin/awrsqrpt.sql

Note: $ORACLE _home represents ORACLE's home directory.

Example: A AWR report that produces an entire database. In this case, Oracle's home directory is

S:\app\Administrator\product\11.2.0\dbhome_1

Run the script Awrrpt.sql.

Sql> @?/rdbms/admin/awrrpt.sql

Current Instance

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

DB Id db Name inst Num Instance

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

1337234614 ORCL 1 ORCL

Select the type of report.

0. Enter the value of Report_type: HTML

In this case, select HTML.

Specify the

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

Would you like an HTML, or a plain text?

Enter ' HTML ' for a HTML, or ' text ' for plain text

Defaults to ' HTML '

Enter value for Report_type: HTML

Instances in this workload Repository schema

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

DB Id Inst Num db Name Instance Host

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

* 1337234614 1 ORCL ORCL JINRI185

Using 1337234614 for Database Id

Using 1 For instance number

Specify the number of days of snapshots to choose from

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

Entering the number of days (n) would result in the most recent

(n) days of snapshots being listed. Pressing <return> without

Specifying a number lists all completed snapshots.

Lists the snapshots that have been generated in the last two days (mainly for snapshot IDs)

1. Enter the value of Num_days: 2

Listing the last 2 days of Completed snapshots

Snap

Instance DB Name Snap Id snap started level

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

ORCL ORCL 1064 21 April 2013 00:00 1

1065 21 April 2013 01:00 1

1066 21 April 2013 02:00 1

1067 21 April 2013 03:00 1

1068 21 April 2013 04:00 1

1069 21 April 2013 05:00 1

1070 21 April 2013 06:00 1

1071 21 April 2013 07:00 1

1072 21 April 2013 08:00 1

1073 21 April 2013 09:00 1

1074 21 April 2013 10:00 1

1075 21 April 2013 11:00 1

1076 21 April 2013 12:00 1

1077 21 April 2013 13:00 1

1078 21 April 2013 14:00 1

1079 21 April 2013 15:00 1

1080 21 April 2013 16:00 1

1081 21 April 2013 17:00 1

1082 21 April 2013 18:00 1

1083 21 April 2013 19:00 1

1084 21 April 2013 20:00 1

1085 21 April 2013 21:01 1

1086 21 April 2013 22:00 1

1087 21 April 2013 23:00 1

1088 22 April 2013 00:00 1

1089 22 April 2013 01:01 1

1090 22 April 2013 02:00 1

1091 22 April 2013 03:00 1

1092 22 April 2013 04:00 1

1093 22 April 2013 05:00 1

1094 22 April 2013 06:00 1

1095 22 April 2013 07:00 1

1096 22 April 2013 08:00 1

1097 22 April 2013 09:00 1

1098 22 April 2013 10:00 1

1099 22 April 2013 11:00 1

1100 22 April 2013 12:00 1

1101 22 April 2013 13:00 1

1102 22 April 2013 14:00 1

Specify the Begin and end Snapshot Ids

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

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

Enter value for Begin_snap: 1074

Begin Snapshot Id specified:1074

Enter value for End_snap: 1076

Specifies the name of the report.

Enter value for Report_name: s:/ocpyang.html

Common errors

Note: Enter the report name and the carriage return appears ORA-00904:: Invalid identified value

WORKAROUND: Use the SYS user to execute or authorize the user grant all on dbms_workload_repository to Perfstat;

**********//

@?/rdbms/admin/