AWR - Automatic Workload Repository :: Beginners Guide

FREE Sign-Up with Gmail and Receive Pdf's and Updates via Mail -Click Here

Automatic Workload Repository (AWR) serves as a repository where it collects , processes and maintains performance statistics for problem detection & self-tuning purposes.

The AWR is used to collect performance statistics including:

Wait events used to identify performance problems.

-Object usage statistics.

-Resource intensive SQL statements.

-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.

-ASH statistics from the V$ACTIVE_SESSION_HISTORY view.

Some system and session statistics from the V$SYSSTAT and V$SESSTAT views.

NOTE  : User running AWR packages needs DBA role.

Initialization parameters & Grants

Set  STATISTICS_LEVEL =  TYPICAL |  ALL

TYPICAL which ensures collection of all major statistics required for database self-management functionality and provides best overall performance. The default value should be adequate for most environments

ALL, additional statistics are added to the set of statistics collected with the TYPICAL setting. The additional statistics are timed OS statistics and plan execution statistics.

BASIC disables the collection of many of the important statistics required by Oracle Database features and functionality, including:

How  To Generate AWR report

Execute the script

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

What are the Common WAIT EVENTS in AWR  report ?

If you want a quick instance wide wait event status, showing which events are the biggest contributors to total wait time, you can use the following query :

SQL> select event, total_waits,time_waited from V$system_event
  where event NOT IN
  ('pmon timer', 'smon timer', 'rdbms ipc reply', 'parallel deque wait',
  'virtual circuit', '%SQL*Net%', 'client message', 'NULL event')
order by time_waited desc;
EVENT                              TOTAL_WAITS         TIME_WAITED
------------------------          -------------       -------------
db file sequential read              35051309            15965640
latch free                            1373973             1913357
db file scattered read                2958367             1840810
enqueue                                  2837              370871
buffer busy waits                      444743              252664
log file parallel write                146221              123435

1. DB File Sequential Read.

Is the wait that comes from the physical side of the database. This could indicate poor joining order of tables or un-selective indexes in your SQL

It related to memory starvation and non selective index use

·         To Overcome this, I/O for sequential reads can be reduced by tuning SQL calls that result in full table scans and using the partitioning option for large tables.

·         Check to ensure that index scans are necessary, and check join orders for multiple table joins.

2. DB File Scattered Read.

That generally happens during a full scan of a table or Fast Full Index Scans.

A large number here indicates that your table may have missing indexes, statistics are not updated or your indexes are not used.

Also, IO system is overloaded and performing poorly, Hence IO operations are taking too long

Solution :

·         You need to reduce the amount of full table scans by tuning SQL statement

·         Try to cache small tables to avoid reading them in over and over again, since a full table scan is put at the cold end of the LRU (Least Recently Used) list.

·         You either need to reduce the cost of I/O, e.g. by getting faster disks or by distributing your I/O load better,

3. Buffer Busy Waits.

A buffer busy wait happens when multiple processes concurrently want to modify the same block in the buffer cache. This typically happens during massive parallel inserts if your tables do not have free lists and it can happen if you have too few rollback segments.

Solution :

·         Buffer busy waits can be reduced by using reverse-key indexes for busy indexes and by partitioning busy tables.

·         If the wait is on a data block, you can move data to another block to avoid this hot block, increase the freelists on the table

·         If the wait is on an index block, you should rebuild the index, partition the index, or use a reverse key index.

·         If the wait is on an undo block, you need to reduce the data density on the table driving this consistent read or increase the DB_CACHE_SIZE

Note: Buffer busy waits should not be greater than 1 percent.

4. Free Buffer Waits.

Occurs, When a session needs a free buffer and cannot find one. (When there is no place to put a new block in Buffer).

Solutions :

·         This indicate that you need to increase the DB_BUFFER_CACHE

·         also indicate that unselective SQL is causing data to flood the buffer cache with index blocks

·         The Database Writer (DBWR) is not writing quickly enough; the buffer cache could be full of multiple versions of the same buffer, causing great inefficiency. To address this, you may want to consider accelerating incremental checkpointing

Best Way :

Increase the DB_CACHE_SIZE; shorten the checkpoint; tune the code to get less dirty blocks, faster I/O, use multiple DBWR’s.

5. Log File SYNc

Could indicate excessive commits. A Log File Sync happens each time a commit (or rollback) takes place.

log file sync normally happens when the archiving is not transfer according to its requirement speed, the logswitch waits for the arch process to write the filled redo to archive log file..

To overcome either we have to increase the archive processs so it write more faster else we have to keep lag_target to zero else increase one more redo group.

OR Multiplex different mount of archiving destination... keeping no longer any file in hot  bkp mode and also trying to solve and reduce more redo generated file

• Tune LGWR to get good throughput to  disk eg: Do not put redo logs on  RAID5 (Because it is very slow for applications that write a lot)

• Reduce overall number of commits by batching transactions so that there are fewer distinct COMMIT operations

6. Log File Switch

log file switch (checkpoint incomplete): May indicate excessive db files or slow IO subsystem

log file switch (archiving needed):  Indicates archive files are written too slowly

log file switch completion: May need more log files per

All commit requests are waiting for "logfile switch (archiving needed)" or "logfile switch (chkpt. Incomplete)."

To Overcome this , Ensure that the archive disk is not full or slow. DBWR may be too slow because of I/O. You may need to add more or larger redo logs, and you may potentially need to add database writers if the DBWR is the problem.

AWR Baselines

·         A baseline is defined as a range of snapshots that can be used to compare to other pairs of snapshots.

·         The main purpose of a baseline is to preserve typical runtime statistics in the AWR repository, allowing you to run the AWR snapshot reports on the preserved baseline snapshots at any time and compare them to recent snapshots contained in the AWR.

·         This allows you to compare current performance (and configuration) to established baseline performance, which can assist in determining database performance problems.

·         It is frequently a good idea to create a baseline in the AWR.

How to Create baselines ?
You can use the create_baseline procedure contained in the dbms_workload_repository stored PL/SQL package to create a baseline as seen in this example:
SQL> EXEC dbms_workload_repository.create_baseline (start_snap_id=>102, end_snap_id=>105, baseline_name=>'MY Baseline');

How To View the Baselines ?

Baselines can be seen using the DBA_HIST_BASELINE view as seen in the following example:
SELECT baseline_id, baseline_name, start_snap_id, end_snap_id
FROM dba_hist_baseline;

BASELINE_ID BASELINE_NAME   START_SNAP_ID END_SNAP_ID
----------- --------------- ------------- -----------
          1 MY Baseline             102        105

You can remove a baseline using the dbms_workload_repository.drop_baseline procedure as seen in this example that drops the “MY Baseline” that we just created.

EXEC dbms_workload_repository.drop_baseline (baseline_name=>'MY Baseline', Cascade=>FALSE);

Note that the cascade parameter will cause all associated snapshots to be removed if it is set to TRUE;

Otherwise, the snapshots will be cleaned up automatically by the AWR automated processes.

There are other scripts too, here is the full list:

REPORT NAME	SQL Script
Automatic Workload Repository Report	awrrpt.sql
Automatic Database Diagnostics Monitor Report	addmrpt.sql
ASH Report	ashrpt.sql
AWR Diff Periods Report	awrddrpt.sql
AWR Single SQL Statement Report	awrsqrpt.sql
AWR Global Report	awrgrpt.sql
AWR Global Diff Report	awrgdrpt.sql

The scripts prompt you to enter the followings

·         the report format (html or text)

·         the start snapshot id,

·         the end snapshot id and

·         the report filename.

This script looks like Statspack; it shows all the AWR snapshots available and asks for two specific ones as interval boundaries. 

Q. How to Generate AWR Report Single Select Statement ?

Run the awrsqrpt.sql script from $ORACLE_HOME/rdbms/admin/ as a sys user

See Also : 

How to Prepare for DBA Interview

General Tips to Prepare for an Oracle DBA Job Interview

F

Follow us on Facebook..!!