AWR (Automatic workload Repository)Report in Oracle 10G
AWR is an enhancement/upgradation of statspack, which help us to tune the database.
Oracle Database 10g uses a scheduled job, GATHER_STATS_JOB, to collect AWR statistics. This job is created, and enabled automatically when you create a new Oracle database under Oracle Database 10g.
GATHER_STATS_JOB, to collect AWR statistics every 1 hour
We can disable and enable the schedule job by following command:
You can disable this job using the dbms_scheduler.disable procedure as seen in this example:
Exec dbms_scheduler.disable(’GATHER_STATS_JOB’);
Exec dbms_scheduler.disable(’GATHER_STATS_JOB’);
And you can enable the job using the dbms_scheduler.enable procedure as seen in this example:
Exec dbms_scheduler.enable(’GATHER_STATS_JOB’);
Exec dbms_scheduler.enable(’GATHER_STATS_JOB’);
.
AWR Snapshot Reports
Oracle provides reports that you can run to analyze the data in the AWR. These reports are much like the statspack reports prior to Oracle Database 10g. There are two reports: awrrpt.sql and awrrpti.sql, which are available in the directory $ORACLE_HOME/ rdbms/ admin.
The output of these reports is essentially the same, except that awrrpti.sql script allows you to define a specific instance to report on. The reports are much like the statspack reports of old, in that you define a beginning and ending snapshot ID, and the output filename of the report. Additionally, you can opt to produce the report in either text format or HTML format.
AWR Snapshot Reports
Oracle provides reports that you can run to analyze the data in the AWR. These reports are much like the statspack reports prior to Oracle Database 10g. There are two reports: awrrpt.sql and awrrpti.sql, which are available in the directory $ORACLE_HOME/ rdbms/ admin.
The output of these reports is essentially the same, except that awrrpti.sql script allows you to define a specific instance to report on. The reports are much like the statspack reports of old, in that you define a beginning and ending snapshot ID, and the output filename of the report. Additionally, you can opt to produce the report in either text format or HTML format.
[oaPROD usa.com01 PROD] $ sqlplus ‘/as sysdba’
SQL> @awrrpt.sql
SQL> @awrrpt.sql
Current Instance DB Id DB Name Inst Num Instance
———– ———— ——– ————
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 textDefaults to ‘html’
Enter value for report_type: text
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 textDefaults to ‘html’
Enter value for report_type: text
Type Specified: text Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
———— ——– ———— ———— ————
01 1141957891 1 PROD PROD usa.com
02 1141957891 1 GLD1 LGLD1 ind.com
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
———— ——– ———— ———— ————
01 1141957891 1 PROD PROD usa.com
02 1141957891 1 GLD1 LGLD1 ind.com
Using 124707144 for database Id Using 1 for instance number Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent(n) days of snapshots being listed. Pressing <return> withoutspecifying a number lists all completed snapshots. Enter value for num_days: 1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent(n) days of snapshots being listed. Pressing <return> withoutspecifying a number lists all completed snapshots. Enter value for num_days: 1
Listing the last day’s Completed Snapshots
SnapInstance DB Name Snap Id Snap Started Level
———— ———— ——— —————— —–
PROD PROD
6400 14 Oct 2008 00:01 1
———— ———— ——— —————— —–
PROD PROD
6400 14 Oct 2008 00:01 1
6401 14 Oct 2008 01:00 1
6402 14 Oct 2008 02:00 1
6403 14 Oct 2008 03:00 1
6404 14 Oct 2008 04:00 1
6405 14 Oct 2008 05:01 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 6412
Begin Snapshot Id specified: 6412
Enter value for end_snap: 6413
End Snapshot Id specified: 6413
Specify the Report Name
The default report file name is awrrpt_1_6412_6413.txt. To use this name,press <return> to continue, otherwise enter an alternative.
The default report file name is awrrpt_1_6412_6413.txt. To use this name,press <return> to continue, otherwise enter an alternative.
Enter value for report_name:End of ReportReport written to awrrpt_1_6412_6413.txt
————-
You can see what snapshots are currently in the AWR by using the DBA_HIST_SNAPSHOT view as seen in this example:
SQL> SELECT snap_id, begin_interval_time, end_interval_time FROM dba_hist_snapshot ORDER BY 1;
SQL> SELECT snap_id, begin_interval_time, end_interval_time FROM dba_hist_snapshot ORDER BY 1;
No comments:
Post a Comment