Generating AWR report

Generating AWR Reports in EM
AWR can produce a summary report on statistics stored in the workload repository. The report contains general information about the overall behavior of the system over a time period defined by two snapshots.
To generate an AWR report, navigate to the Automatic Workload Repository page in Database Control on the Server tab page. On this page, click the link corresponding to the number of snapshots. This opens the Snapshots page. On the Snapshots page, select the beginning snapshot, select View Report from the Actions drop-down list, and click Go. On the View Report page, select the ending snapshot and click OK.
Generating AWR Reports in SQL*Plus
The AWR report has the same information, whether it is generated from SQL*Plus or from EM.The awrrpt.sql SQL*Plus script run in the ORACLE_HOME/rdbms/admin directory produces the report. The user running the script must have the SELECT_CATALOG_ROLE privilege. The script prompts for the following report options:
• HTML or text report
• The number of days of snapshots to choose from.
Entering the number of days shows you the most recent snapshots being taken. You can also determine which SNAP_IDs you should use by querying the DBA_HIST_SNAPSHOT table to retrieve the mapping between
a SNAP_ID and the wall-clock time.
• Begin SNAP_ID, end SNAP_ID: A snapshot pair that defines the reporting time period
• File name: The user-specified file into which the report is written
The report contains the same information whether it is produced as a text or as an HTML report.However, HTML reports can be viewed in a Web browser, and the advantage of an HTML report is the presence of links to the detail sections of the report.

AWR Snapshot Settings

With the MODIFY_SNAPSHOT_SETTINGS procedure, you can control the snapshot
parameters. You can use this procedure to change:
• The retention period. RETENTION is specified in minutes. The default is eight days; the
minimum is one day. Setting RETENTION to the value 0 disables automatic purging.
• The INTERVAL between snapshots. The minimum value is 10 minutes, the maximum is
100 years, and the default value is 60 minutes.
• The number of Top SQL statements for which to capture performance data. You are
allowed to specify the following values: DEFAULT, MAXIMUM, n, where n is the number of
Top SQL statements to flush for each SQL criteria such as Elapsed Time and CPU Time.
Specify DEFAULT to capture the Top 30 for level TYPICAL and Top 100 for level ALL of
STATISTICS_LEVEL. Specify MAXIMUM to capture the complete set of SQL in the
cursor cache. Specify NULL to keep the current setting.
Note: Under exceptional circumstances, automatic snapshot collection can be completely turned
off by setting the snapshot interval to 0. The automatic collection of the workload and statistical
data is stopped and much of the Oracle self-management functionality is not operational. In
addition, you are unable to manually create snapshots. For this reason, Oracle Corporation
strongly recommends that you do not turn off automatic snapshot collection.
Create a snapshot
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT (};
Drop one or more snapshots
DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(
LOW_SNAP_ID => 102,
HIGH_SNAP_ID => 105);

Automatic Workload Repository: Overview

AWR is the infrastructure that provides services to Oracle Database 11g components to collect,
maintain, and utilize statistics for problem detection and self-tuning purposes.
The AWR infrastructure consists of two major parts:
• An in-memory statistics collection facility that is used by various components to collect
statistics. These statistics are stored in memory for performance reasons. Statistics stored in
memory are accessible through dynamic performance (V$) views.
• AWR snapshots represent the persistent portion of the facility. The AWR snapshots are
accessible through data dictionary (DBA) views and Database Control.
Statistics are stored in persistent storage for several reasons:
• The statistics need to survive instance crashes.
• Historical data for baseline comparisons is needed for certain types of analysis.
• Memory overflow: When old statistics are replaced by new ones due to memory shortage,
the replaced data can be stored for later use.
The memory version of the statistics is transferred to disk on a regular basis by a background
process called MMON (Manageability Monitor).
With AWR, the Oracle database server provides a way to capture historical statistics data
automatically, without the intervention of DBAs.
The workload repository is a collection of persistent system performance statistics owned by
SYS. The workload repository resides in the SYSAUX tablespace and is one of the main
SYSAUX occupants.
A snapshot is a set of performance statistics captured at a certain time. Snapshots are used for
computing the rate of change of a statistic. Each snapshot is identified by a snapshot sequence
number (snap_id) that is unique in the workload repository.
By default, snapshots are generated every 60 minutes. You can adjust this frequency by
changing the snapshot INTERVAL parameter. Because internal advisories rely on these
snapshots, be aware that adjustment of the interval setting can affect diagnostic precision. For
example, if INTERVAL is set to 4 hours, you may miss spikes that occur within 60-minute
intervals.
In a Real Application Clusters environment, each snapshot spans all nodes in a cluster.
Snapshots for data in each node share the same snap_id, differentiated by their instance IDs.
Snapshots in Real Application Clusters are captured at roughly the same time.
You can take manual snapshots by using Database Control. Taking manual snapshots is
supported in conjunction with the automatic snapshots that the system generates. Manual
snapshots are expected to be used when you want to capture the system behavior at two specific
points in time that do not coincide with the automatic schedule.

User Trace Files

Server processes can generate user trace files at the request of the user or DBA.
Instance-Level Tracing
Instance-level tracing should only be enabled when absolutely necessary. Tracing all sessions
will create an I/O load and can fill the file system quickly. This trace logging is enabled or
disabled by the EXEC DBMS_MONITOR.DATABASE_TRACE_ENABLE().
Session-Level Tracing
The following statement enables the writing to a trace file for a particular session:
EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE (8,12,
waits=>TRUE, binds=>TRUE);
where 8 and 12 are the system identifier and serial number of the connected user. Typically
only a DBA has the permissions required to enable tracing on any session.
The DBMS_MONITOR package is created when the catproc.sql script is run. This script is
located in the following directory:
• On UNIX: $ORACLE_HOME/rdbms/admin
• On Windows: %ORACLE_HOME%\rdbms\admin
To enable the writing of a trace file for your current session, execute the following command:
EXECUTE DBMS_SESSION.SET_SQL_TRACE(TRUE)

Using Alert Log Information as an Aid in Oracle Performance Tuning

The information listed in the slide and additional information are written to the alert log. The
information written to the alert log changes somewhat with each version of the Oracle
database. Some values, such as the checkpoint start and end times, are written only when
requested. These values are written into the alert log file only if the
LOG_CHECKPOINTS_TO_ALERT parameter has been set to TRUE.
The alert log file can grow to an unmanageable size. You can safely delete the alert log while
the instance is started, although you should consider making an archived copy of it first. This
archived copy could prove valuable if you should have a future problem that requires
investigating the history of an instance. Note: Both versions of the alert log, text and XML,
should be trimmed periodically.
For example, suppose the DBA noticed a change in performance statistics. The DBA finds that
an instance parameter has changed since the last baseline. To confirm that the performance
change corresponds to the parameter change, the alert log can be searched. The alert log lists
all the non-default parameter settings on each startup, and records ALTER SYSTEM commands
with a time stamp.
The following sample of the alert log shows the startup parameters, the warning message for
FAST_START_MTTR_TARGET, and a sequence of log file switches showing the time for
each:
Starting up ORACLE RDBMS Version: 10.2.0.1.0.
System parameters with non-default values:
processes = 150
__shared_pool_size = 88080384
__large_pool_size = 4194304
__java_pool_size = 4194304
__streams_pool_size = 0
sga_target = 285212672
control_files =
/u01/app/oracle/oradata/orcl/control01.ctl,
/u01/app/oracle/oradata/orcl/control02.ctl,
/u01/app/oracle/oradata/orcl/control03.ctl
db_block_size = 8192
__db_cache_size = 184549376
compatible = 10.2.0.1.0
db_file_multiblock_read_count= 16
db_recovery_file_dest =
/u01/app/oracle/flash_recovery_area
db_recovery_file_dest_size= 2147483648
undo_management = AUTO
undo_tablespace = UNDOTBS1
remote_login_passwordfile= EXCLUSIVE
db_domain = oracle.com
dispatchers = (PROTOCOL=TCP)
(SERVICE=orclXDB)
job_queue_processes = 10
…
Tue Dec 20 01:12:16 2005
MTTR advisory is disabled because FAST_START_MTTR_TARGET is
not set
…
Tue Dec 20 01:13:42 2005
Thread 1 advanced to log sequence 6
Current log# 2 seq# 6 mem# 0:
/u01/app/oracle/oradata/orcl/redo02.log
Tue Dec 20 01:16:32 2005
Thread 1 advanced to log sequence 7
Current log# 3 seq# 7 mem# 0:
/u01/app/oracle/oradata/orcl/redo03.log
Tue Dec 20 01:43:46 2005
Thread 1 advanced to log sequence 8
Current log# 1 seq# 8 mem# 0:
/u01/app/oracle/oradata/orcl/redo01.log
…