AWR [AUTOMATED WORKLOAD REPOSITORY] [AUTOMATED WORKLOAD REPOSITORY
1. Birth of Awr.
2. What is Awr.
3. Features In Awr.
4. Installation of Awr.
5. Running Awr.
• Creating a Report.
• Modifying the Default Setting.
• Creating Extra Snapshot.
• Dropping a Snapshot.
• Creating a Baseline Snapshot.
• Dropping a Baseline Snapshot.
• Comparing the Two Reports.
• Awr VS Stats pack.
• View & Tables in Awr.
• Awr in Enterprise ManagerDOYENSYS
AWR Birth
• Historically people used bstat and estat to collect Oracle
statistics over a time period and then compare them to each
other.
• The bstat / estat approach was replaced with Statspack
available in Oracle 8i. Statspack was a package provided by
Oracle that did roughly the same thing but better.
• Statspack has now been surpassed in functionality by AWR
which is always collecting execution statistics for future
analysis and tuning performed by all of the expert components
provided by Oracle. Oracle recommends that all statspack
users switch to AWR in 10g.DOYENSYS
WHAT IS AWR?
• The main focus of the Oracle Database 10g is self-
manageability.
• Automated Workload Repository is the place where the data
to aid in self management are stored.
• AWR is a new feature in 10g which is used for monitoring ,
analyzing the performance of the database and solutions
recommendation for tuning process for DBA's.DOYENSYS
Metrics Collected By AWR
Metrics Collected By the AWR Can be obtained by view v$metricnameDOYENSYS
AWR FEATURES
The AWR is used to collect performance statistics including:
• Wait events used to identify performance problems
• Time model statistics indicating the amount of DB time
associated with a process from the V$SESS_TIME_MODEL
and V$SYS_TIME_MODEL views.
• Active Session History (ASH) statistics from the
V$ACTIVE_SESSION_HISTORY view.
• Some system and session statistics from the V$SYSSTAT and
V$SESSTAT views.
• Object usage statistics.
• Resource intensive SQL statements.DOYENSYS
AWR is automatically installed and running with 10g. The
new MMON process is responsible for collecting data and
populating the AWR. We can confirm it by the Linux
command.
INSTALLATION OF AWRDOYENSYS
RUNNING AWR
1.How to Generate a report.
2.Modifying the Default Settings.
3.Creating an extra snapshots.
4.Droping a snapshot.
5.Creating a Baseline Snapshot.DOYENSYS
RUNNING A REPORT
To Run AWR there are two preloaded scripts 10g at the location
• @$ORACLE_HOME/rdbms/admin/awrrpt.sql
• @$ORACLE_HOME/rdbms/admin/awrrpti.sql
• These both scripts are similar to the statspack reports gives the
option of HTML or plain text formats to save the output. Both
scripts gives the same output where us awrrpti.sql lets us to
select a single instance.
• When we execute these scripts it prompts us to enter the
format of the report [HTML or TEXT],
• Start snapshot id, End snapshot id and Report filename. The
result can be viewed in a browser or text editor accordingly.DOYENSYS
Specifying the Report Type
Specifying the Dbid & Instance NumberDOYENSYS
Specifying the Number of Days Snapshots to Choose from
Choosing the Begin and End Snap ValueDOYENSYS
Specifying the Report Name and Final ResultDOYENSYS
ERROR
When an instance is shutdown between two snapshots it will
return a error like.DOYENSYS
Modifying The Default Settings
By Default the snapshots are taken every hour and
retained for 7 days .These Default Values can be altered
by
No of days snapshot will be retained
Snapshots will be taken at a time
interval of 10 minutes.DOYENSYS
The changes in the snapshot settings are updated in the table
DBA_HIST_WR_CONTROL.
Edited Time IntervalDOYENSYS
Creating An Extra Snapshot
• If we need an extra snapshot in between the interval time
means we can use the command below.
DOYENSYS
Dropping Snapshots
• With the help of the command given below we can drop the
snapshots.
• The command will delete the Snapshots From 22 to 32.DOYENSYS
Procedure to Purge the Reports according to user given no of days
set serveroutput on
declare
v_beginsnap number;
v_endsnap number;
cursor test is
select min(snap_id),max(snap_id)from dba_hist_snapshot where startup_time&noofdays order by snap_id;
begin
open test;
for i in 1..1 loop
fetch test into v_beginsnap,v_endsnap;
dbms_output.put_line (v_beginsnap||' '||v_endsnap);
end loop;
close test;
DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range (
low_snap_id => v_beginsnap,
high_snap_id => v_endsnap);
END;
/DOYENSYS
Creating a Baseline Snapshot.
• Baseline is a pair of snapshot that represents a specific period
of usage.
• A snapshot will be taken at the normal period when the
instance is running smoothly and when the database is doing
some large operations. These snapshots will be compared with
the snapshot taken at the time of any problem in the database.DOYENSYS
Dropping a Baseline Snapshot
• The pair of snapshots associated with a baseline are retained
until the baseline is explicitly deleted
Deletes associated snapshots if TRUE DOYENSYS
• Baseline Information Can be Gathered from the table
DBA_HIST_BASELINE. DOYENSYS
Comparing The Two Reports
Comparing Baseline Report and Newly Generated Report
• AWR Compare Period report provides comparison of two
snapshot periods.
• To compare the two reports there is a preloaded script in
oracle 10g. @$ORACLE_HOME/rdbms/admin/awrddrpt.sql.
• Results are ordered by statistical differences between the two
periods.
• Comparison can also be done through EM.DOYENSYS
STEPS TO COMPARE
1.Run the report @$ORACLE_HOME/rdbms/admin/awrddrpt.sql.
2. Specify the report type by default it is HTML.
DOYENSYS
3. Specify the No of Days for the first pair of snapshots.
4. Specify the first pair of snapshots Begin & End Snapid’s.DOYENSYS
5. Specify the No of Days for the second pair of snapshots.
6. Specify the second pair of snapshots Begin & End Snapid’s.DOYENSYS
7. Specify the report name
NOTE-While entering the no of days we should be careful because
entering the no of days will list the most recent snapshots. So to list the
baseline snapshot ids the no of days should be sysdate - start_snap_time.
The start_snap_time can be querried from dba_hist_sanspshot table.DOYENSYS
COMPARED REPORT and a NORMAL REPORTDOYENSYS
AWR [vs] STATSPACK
• Statspack does not store history for new metric statistics
introduced in Oracle10g.
The key AWR views, dba_hist_sysmetric_history and
dba_hist_sysmetric_summary. dba_hist_service_stat ,
dba_hist_service_wait_class and dba_hist_service_name , which
store history for performance cumulative statistics tracked for
specific services.
• Statspack snapshots must be run by an external scheduler like
(dbms_jobs, CRON, etc.).
AWR snapshots are scheduled every 60 minutes by default.We
can change the interval by adjusting the snapshot interval if we
desired to.The new background process MMON is reponsible for
initiating the snapshot automatically.DOYENSYS
• When the Statspack tablespace runs out of space, Statspack
quits working. The snapshot collected by the Statspack should
be purged manually.
In AWR snapshots are purged automatically by MMON every
night. MMON, by default, tries to keep one week's worth of
AWR snapshots available. If AWR detects that the SYSAUX
tablespace is in danger of running out of space, it will free
space in SYSAUX by automatically deleting the oldest set of
snapshots. If this occurs, AWR will initiate a server-generated
alert to notify administrators of the out-of-space error
condition.
Administrators can manually adjust the amount of information retained by
invoking the MODIFY_SNAPSHOT_SETTINGS PL/SQL stored procedure
and specifying the RETENTION parameter input variable.DOYENSYS
AWR VIEWS AND TABLES
• 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.DOYENSYS
AWR IN ENTERPRISE MANAGER
• The automated workload repository administration tasks have
been included in Enterprise Manager.
• The "Automatic Workload Repository" page is accessed from
the main page by clicking on the "Administration" link, then
the "Workload Repository" link under the "Workload" section.
• The page allows you to modify AWR settings or manage
snapshots without using the PL/SQL APIs.
AWR Frequently Asked Questions
2. What Is AWR.
3. How to confirm the Awr running.
4. How to modify the default settings.
5. How to create a extra snapshot in between the interval.
6. How to generate a Baseline report.
7. How to automatically purge the sysaux tablespace.
8. How to compare the Baseline report with the last taken
report.
1. Birth of Awr.
2. What is Awr.
3. Features In Awr.
4. Installation of Awr.
5. Running Awr.
• Creating a Report.
• Modifying the Default Setting.
• Creating Extra Snapshot.
• Dropping a Snapshot.
• Creating a Baseline Snapshot.
• Dropping a Baseline Snapshot.
• Comparing the Two Reports.
• Awr VS Stats pack.
• View & Tables in Awr.
• Awr in Enterprise ManagerDOYENSYS
AWR Birth
• Historically people used bstat and estat to collect Oracle
statistics over a time period and then compare them to each
other.
• The bstat / estat approach was replaced with Statspack
available in Oracle 8i. Statspack was a package provided by
Oracle that did roughly the same thing but better.
• Statspack has now been surpassed in functionality by AWR
which is always collecting execution statistics for future
analysis and tuning performed by all of the expert components
provided by Oracle. Oracle recommends that all statspack
users switch to AWR in 10g.DOYENSYS
WHAT IS AWR?
• The main focus of the Oracle Database 10g is self-
manageability.
• Automated Workload Repository is the place where the data
to aid in self management are stored.
• AWR is a new feature in 10g which is used for monitoring ,
analyzing the performance of the database and solutions
recommendation for tuning process for DBA's.DOYENSYS
Metrics Collected By AWR
Metrics Collected By the AWR Can be obtained by view v$metricnameDOYENSYS
AWR FEATURES
The AWR is used to collect performance statistics including:
• Wait events used to identify performance problems
• Time model statistics indicating the amount of DB time
associated with a process from the V$SESS_TIME_MODEL
and V$SYS_TIME_MODEL views.
• Active Session History (ASH) statistics from the
V$ACTIVE_SESSION_HISTORY view.
• Some system and session statistics from the V$SYSSTAT and
V$SESSTAT views.
• Object usage statistics.
• Resource intensive SQL statements.DOYENSYS
AWR is automatically installed and running with 10g. The
new MMON process is responsible for collecting data and
populating the AWR. We can confirm it by the Linux
command.
INSTALLATION OF AWRDOYENSYS
RUNNING AWR
1.How to Generate a report.
2.Modifying the Default Settings.
3.Creating an extra snapshots.
4.Droping a snapshot.
5.Creating a Baseline Snapshot.DOYENSYS
RUNNING A REPORT
To Run AWR there are two preloaded scripts 10g at the location
• @$ORACLE_HOME/rdbms/admin/awrrpt.sql
• @$ORACLE_HOME/rdbms/admin/awrrpti.sql
• These both scripts are similar to the statspack reports gives the
option of HTML or plain text formats to save the output. Both
scripts gives the same output where us awrrpti.sql lets us to
select a single instance.
• When we execute these scripts it prompts us to enter the
format of the report [HTML or TEXT],
• Start snapshot id, End snapshot id and Report filename. The
result can be viewed in a browser or text editor accordingly.DOYENSYS
Specifying the Report Type
Specifying the Dbid & Instance NumberDOYENSYS
Specifying the Number of Days Snapshots to Choose from
Choosing the Begin and End Snap ValueDOYENSYS
Specifying the Report Name and Final ResultDOYENSYS
ERROR
When an instance is shutdown between two snapshots it will
return a error like.DOYENSYS
Modifying The Default Settings
By Default the snapshots are taken every hour and
retained for 7 days .These Default Values can be altered
by
No of days snapshot will be retained
Snapshots will be taken at a time
interval of 10 minutes.DOYENSYS
The changes in the snapshot settings are updated in the table
DBA_HIST_WR_CONTROL.
Edited Time IntervalDOYENSYS
Creating An Extra Snapshot
• If we need an extra snapshot in between the interval time
means we can use the command below.
DOYENSYS
Dropping Snapshots
• With the help of the command given below we can drop the
snapshots.
• The command will delete the Snapshots From 22 to 32.DOYENSYS
Procedure to Purge the Reports according to user given no of days
set serveroutput on
declare
v_beginsnap number;
v_endsnap number;
cursor test is
select min(snap_id),max(snap_id)from dba_hist_snapshot where startup_time
begin
open test;
for i in 1..1 loop
fetch test into v_beginsnap,v_endsnap;
dbms_output.put_line (v_beginsnap||' '||v_endsnap);
end loop;
close test;
DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range (
low_snap_id => v_beginsnap,
high_snap_id => v_endsnap);
END;
/DOYENSYS
Creating a Baseline Snapshot.
• Baseline is a pair of snapshot that represents a specific period
of usage.
• A snapshot will be taken at the normal period when the
instance is running smoothly and when the database is doing
some large operations. These snapshots will be compared with
the snapshot taken at the time of any problem in the database.DOYENSYS
Dropping a Baseline Snapshot
• The pair of snapshots associated with a baseline are retained
until the baseline is explicitly deleted
Deletes associated snapshots if TRUE DOYENSYS
• Baseline Information Can be Gathered from the table
DBA_HIST_BASELINE. DOYENSYS
Comparing The Two Reports
Comparing Baseline Report and Newly Generated Report
• AWR Compare Period report provides comparison of two
snapshot periods.
• To compare the two reports there is a preloaded script in
oracle 10g. @$ORACLE_HOME/rdbms/admin/awrddrpt.sql.
• Results are ordered by statistical differences between the two
periods.
• Comparison can also be done through EM.DOYENSYS
STEPS TO COMPARE
1.Run the report @$ORACLE_HOME/rdbms/admin/awrddrpt.sql.
2. Specify the report type by default it is HTML.
DOYENSYS
3. Specify the No of Days for the first pair of snapshots.
4. Specify the first pair of snapshots Begin & End Snapid’s.DOYENSYS
5. Specify the No of Days for the second pair of snapshots.
6. Specify the second pair of snapshots Begin & End Snapid’s.DOYENSYS
7. Specify the report name
NOTE-While entering the no of days we should be careful because
entering the no of days will list the most recent snapshots. So to list the
baseline snapshot ids the no of days should be sysdate - start_snap_time.
The start_snap_time can be querried from dba_hist_sanspshot table.DOYENSYS
COMPARED REPORT and a NORMAL REPORTDOYENSYS
AWR [vs] STATSPACK
• Statspack does not store history for new metric statistics
introduced in Oracle10g.
The key AWR views, dba_hist_sysmetric_history and
dba_hist_sysmetric_summary. dba_hist_service_stat ,
dba_hist_service_wait_class and dba_hist_service_name , which
store history for performance cumulative statistics tracked for
specific services.
• Statspack snapshots must be run by an external scheduler like
(dbms_jobs, CRON, etc.).
AWR snapshots are scheduled every 60 minutes by default.We
can change the interval by adjusting the snapshot interval if we
desired to.The new background process MMON is reponsible for
initiating the snapshot automatically.DOYENSYS
• When the Statspack tablespace runs out of space, Statspack
quits working. The snapshot collected by the Statspack should
be purged manually.
In AWR snapshots are purged automatically by MMON every
night. MMON, by default, tries to keep one week's worth of
AWR snapshots available. If AWR detects that the SYSAUX
tablespace is in danger of running out of space, it will free
space in SYSAUX by automatically deleting the oldest set of
snapshots. If this occurs, AWR will initiate a server-generated
alert to notify administrators of the out-of-space error
condition.
Administrators can manually adjust the amount of information retained by
invoking the MODIFY_SNAPSHOT_SETTINGS PL/SQL stored procedure
and specifying the RETENTION parameter input variable.DOYENSYS
AWR VIEWS AND TABLES
• 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.DOYENSYS
AWR IN ENTERPRISE MANAGER
• The automated workload repository administration tasks have
been included in Enterprise Manager.
• The "Automatic Workload Repository" page is accessed from
the main page by clicking on the "Administration" link, then
the "Workload Repository" link under the "Workload" section.
• The page allows you to modify AWR settings or manage
snapshots without using the PL/SQL APIs.
AWR Frequently Asked Questions
2. What Is AWR.
3. How to confirm the Awr running.
4. How to modify the default settings.
5. How to create a extra snapshot in between the interval.
6. How to generate a Baseline report.
7. How to automatically purge the sysaux tablespace.
8. How to compare the Baseline report with the last taken
report.
No comments:
Post a Comment