Feeds:
Posts
Comments

Archive for December, 2013

WHAT IS ORCALE AWR (Automatic Workload Resporitory)REPORT By Nimai Karmakar

Prior Oracle 10g we use STATSPACK report to analyze instance health for performance. but now in 10g we use AWR(automatic workload repository) report to analyze Oracle Database health.

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.

How to generate AWR report ?
It is very easy to generate AWR report in Oracle 10g.We have to run just one sql script to generate AWR report.
There is TWO sql script to create AWR report.
1. awrrpt.sql
If we have only One Oracle Database then run awrrpt.sql sql script.
2. awrrpti.sql
If we have more than One Oracle Instance (Like RAC) then run awrrpti.sql script so that we can particular instance for awr report creation.

Location of AWR report sql script
$ORACLE_HOME/rdbms/admin
In my database test
F:\oracle\product\10.2.0\test01\RDBMS\ADMIN

What is default interval period between two awr report ?
AWR report generate automatically for database every 1 hr interval period. But Recommanded is 15 Minutes is enough in two snapshot for better performance

How to Manage & change interval period for awr report ?
There is two to modify or changes in AWR report.

1. Manually —> through DBMS_WORKLOAD_REPOSITORY plsql package
2. Snapshots –> By default snapshots of the relevant data are taken every hour and retained for 7 days. The default values for these settings can be altered using the following procedure.

BEGIN
DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(retention => 43200, — Minutes (= 30 Days).

Current value retained
if NULL.
interval => 30); — Minutes. Current value retained if NULL.
END;
/

The changes to the settings are reflected in the DBA_HIST_WR_CONTROL view. Typically the retention period should capture at least one complete workload cycle. If you system has monthly archive and loads a 1 month retention time would be more beneficial that the default 7 days. An interval of “0” switches off snapshot collection, which in turn stops much of the self-tuning
functionality, hence this is not recommended. Automatic collection is only possible if the STATISTICS_LEVEL parameter is set to TYPICAL or ALL.

If the value is set to BASIC manual snapshots can be taken, but they will be missing some statistics. Extra snapshots can be taken and existing snapshots can be removed, as shown below.

EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;
BEGIN
DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range (
low_snap_id => 22,
high_snap_id => 32);
END;
/

Snapshot information can be queried from the DBA_HIST_SNAPSHOT view.

Baselines – A baseline is a pair of snapshots that represents a specific period of usage. Once baselines are defined they can be used to compare current performance against similar periods in the past. You may wish to create baseline to represent a period of batch processing.

BEGIN
DBMS_WORKLOAD_REPOSITORY.create_baseline (
start_snap_id => 210,
end_snap_id => 220,
baseline_name => ‘batch baseline’);
END;
/

The pair of snapshots associated with a baseline are retained until the baseline is explicitly deleted.

BEGIN
DBMS_WORKLOAD_REPOSITORY.drop_baseline (
baseline_name => ‘batch baseline’,
cascade => FALSE); — Deletes associated snapshots if TRUE.
END;
/

Baseline information can be queried from the DBA_HIST_BASELINE view.

Workload Repository Views
The following workload repository views are available:
 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.

Workload Repository Reports
Oracle provide two scripts to produce workload repository reports (awrrpt.sql and awrrpti.sql).They are similar in format to the statspack reports and give the option of HTML or plain text formats. The two reports give essential the same output but the awrrpti.sql allows you to select a single instance. The reports can be generated as follows.

@$ORACLE_HOME/rdbms/admin/awrrpt.sql
@$ORACLE_HOME/rdbms/admin/awrrpti.sql

The scripts prompt you to enter the report format (html or text), the start snapshot id, the end snapshot id and the report filename. The resulting report can be opend in a browser or text editor accordingly.

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

You can edit snapshot setting here..
CREATE_SNAPSHOT Function and Procedure

This function and procedure create snapshots.In the case of the function, the snapshot ID is returned.

Syntax

DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT(flush_level IN VARCHAR2 DEFAULT ‘TYPICAL’);
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT(flush_level IN VARCHAR2 DEFAULT ‘TYPICAL’)RETURN NUMBER;

Parameter Description

flush_level The flush level for the snapshot is either ‘TYPICAL’ or ‘ALL’

Examples

This example creates a manual snapshot at the TYPICAL level:

EXECUTE DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

If you query the DBA_HIST_SNAPSHOT view after the CREATE_SNAPSHOT action, you will see one more snapshot ID added to the Workload Repository.

DROP_BASELINE Procedure

This procedure drops a baseline.

Syntax

DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE(
baseline_name IN VARCHAR2,
cascade IN BOOLEAN DEFAULT false,
dbid IN NUMBER DEFAULT NULL);

Parameter Description

baseline_name The name of baseline.
cascade If TRUE, the pair of snapshots associated with the baseline will also be dropped.
Otherwise, only the baseline is removed.
dbid The (optional) database id (default to local DBID).

Examples
This example drops the baseline ‘oltp_peakload_bl’ without dropping the underlying snapshots:

EXECUTE DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE (baseline_name => ‘oltp_peakload_bl’);

If you query the DBA_HIST_BASELINE view after the DROP_BASELINE action, you will see the specified baseline definition is removed. You can query theDBS_HIST_SNAPSHOT view to find that the underlying snapshots are left intact.

DROP_SNAPSHOT_RANGE Procedure

This procedure drops a range of snapshots.

DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(
low_snap_id IN NUMBER,
high_snap_id IN NUMBER
dbid IN NUMBER DEFAULT NULL);

Parameter Description

low_snap_id The low snapshot id of snapshots to drop.
high_snap_id The high snapshot id of snapshots to drop.
dbid The database id (default to local DBID.

Examples
This example drops the range of snapshots between snapshot id 102 to 105 for the local database:

EXECUTE DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(102, 105);
If you query the dba_hist_snapshot view after the Drop Snapshot action, you will see that snapshots 102 to 105 are removed from the Workload Repository.

How to Interpreting with AWR report ?

Below is Main Section in AWR report for concern.
Load Profile – In this section we know about Logical Read, Physical Read, Soft Parse, Hard Parse Instance Efficiency Percentages
Many Oracle Guru’s is not recommended to trust on Instance percentage ratio. becuase suppose if your instance percentage show BUFFER CAHCE HIT RATIO is 100% it is not sign for your database is very fast and running smootly.

Top 5 Timed Events – It is very important section in AWR report.through this we can know most five wait event is effecting database performance.

Oracle Wait Event – Top wait events which usually cause db hung.

SQL Statistics – It this section we will know about Execution time, cpu used, logical & physical read for top mostsql statement.

Advisory Statistics – In this section we will get advice for PGA, BUFFER CACHE, SHARED POOL, JAVA POOL size for better performance.

For text output

SELECT output FROM TABLE
(dbms_workload_repository.awr_report_text
(37933856,1,2900,2911)
(Database_id,instance_num,start_snap,end_snap)
);

OUTPUT
——————————————————————–
WORKLOAD REPOSITORY report for
DB Name DB Id Instance Inst Num Release Cluster Host
———- ———– ——— ——– ———– ——- —–
DBDABR 37933856 dbdabr 1 10.1.0.2.0 NO Host1
Snap Id Snap Time Sessions Curs/Sess
——— ——————- ——– ———
Begin Snap: 2900 19-Aug-04 11:00:29 18 5.2
End Snap: 2911 19-Aug-04 22:00:16 18 4.6
Elapsed: 659.78 (mins)
DB Time: 10.08 (mins)
Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 48M Std Block Size: 8K
Shared Pool Size: 56M Log Buffer: 256K
Load Profile
~~~~ Per Second Per Transaction
——– —————
Redo size: 1,766.20 18,526.31
Logical reads: 39.21 411.30
Block changes: 11.11 116.54
Physical reads: 0.38 3.95
Physical writes: 0.38 3.96
User calls: 0.06 0.64
Parses: 2.04 21.37
Hard parses: 0.14 1.45
Sorts: 1.02 10.72
Logons: 0.02 0.21
Executes: 4.19 43.91
For html output
SELECT
output
FROM
TABLE
(dbms_workload_repository.awr_report_html
(37933856,1,2900,2911)
(Database_id,instance_num,start_snap,end_snap)
);

Hope it helps a little bit.

Thanks & Regards

Nimai Karmakar

Read Full Post »

Oracle Application DBA

sharing the information about realtime experience

My Oracle Notes

Emiliano Fusaglia RAC DBA/Data Architect

Johribazaar's Blog

Just another WordPress.com weblog

Ayurveda and Yoga

Site for Ayurveda and Yoga articles

SanOraLife

Few adventures in my slow running life...

pavankumaroracledba

4 out of 5 dentists recommend this WordPress.com site

ORACLE WRITES

Technical Articles by Kanchana Selvakumar

SAP Basis Cafe

SAP Basis Cafe - Exploring SAP R/3 Basis World

Life Is A Mystery

This Is Wihemdra's Blog...I Share What I Have

ursvenkat's Blog

Oracle Applications DBA Blog