ITPub博客

首页 > Linux操作系统 > Linux操作系统 > How to Generate an AWR Report and Create Baselines

How to Generate an AWR Report and Create Baselines

原创 Linux操作系统 作者:ilsyx 时间:2011-09-01 15:10:52 0 删除 编辑

个人总结

生成awrrtp报告: $ORACLE_HOME/rdbms/admin/awrrpt.sql
文档里还描述了awr调用的相关脚本的大致介绍:

Generating Various Types of AWR Reports

AWR reports can be generated by running various SQL scripts to satisfy various requirements. Each report is available in HTML or TXT format:
  •  awrrpt.sql
    Displays various statistics for a range of snapshots Ids.

  • awrrpti.sql
    Displays statistics for a range of snapshot Ids on a specified database and instance.

  • awrsqrpt.sql
    Displays statistics of a particular SQL statement for a range of snapshot Ids. Run this report to inspect or debug the performance of a particular SQL statement.

  • awrsqrpi.sql
    Displays statistics of a particular SQL statement for a range of snapshot Ids on a specified SQL.

  • awrddrpt.sql
    Compares detailed performance attributes and configuration settings between two selected time periods.

  • awrddrpi.sql
    Compares detailed performance attributes and configuration settings between two selected time periods on a specific database and instance.
再后面的各种有用的操作可当参考,官方文档http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_wkrpos.htm#i1003494  (10g) http://download.oracle.com/docs/cd/E11882_01/appdev.112/e16760/d_workload_repos.htm#i1003494 (11g) 里有DBMS_WORKLOAD_REPOSITORY相关用法. 没仔细对比这两个的不同.
后面的两个参考文档不错,看一下.


How to Generate an AWR Report and Create Baselines [ID 748642.1]

  Modified 30-AUG-2011     Type HOWTO     Status PUBLISHED  

In this Document
  Goal
  Solution
     Running a Basic Report
     Generating Various Types of AWR Reports
     Various Useful Operations
     Workload Repository Views
     Automation of AWR reports ?
  References


Applies to:

Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 11.2.0.2 - Release: 10.1 to 11.2
Information in this document applies to any platform.
Oracle Server Enterprise Edition - Version: 10.1.0.2 to 11.1.0.7

Goal

This article outlines how to generate an AWR report and create baselines.

Solution

Automatic Workload Repository (AWR) is a collection of persistent system performance statistics owned by the SYS user.
It resides in SYSAUX tablespace.
By default snapshot are generated once every 60min and maintained for 7 days.

An AWR report outputs a series of statistics based on the differences between snapshots that may be used to investigate performance and other issues.

Running a Basic Report

With appropriate licenses for AWR, you may generate an AWR report by executing
the following script. and pick the two snapshots you want to use for the sample :

$ORACLE_HOME/rdbms/admin/awrrpt.sql

Depending on the reasons for collecting the report, the default can be used, or for a more focused view, a short 10-15 minute snapshot could be used.

You will also be asked for the format of the report (text or html) along with the report name.

Generating Various Types of AWR Reports

AWR reports can be generated by running various SQL scripts to satisfy various requirements. Each report is available in HTML or TXT format:
  •  awrrpt.sql
    Displays various statistics for a range of snapshots Ids.

  • awrrpti.sql
    Displays statistics for a range of snapshot Ids on a specified database and instance.

  • awrsqrpt.sql
    Displays statistics of a particular SQL statement for a range of snapshot Ids. Run this report to inspect or debug the performance of a particular SQL statement.

  • awrsqrpi.sql
    Displays statistics of a particular SQL statement for a range of snapshot Ids on a specified SQL.

  • awrddrpt.sql
    Compares detailed performance attributes and configuration settings between two selected time periods.

  • awrddrpi.sql
    Compares detailed performance attributes and configuration settings between two selected time periods on a specific database and instance.

Various Useful Operations

How to Modify the AWR SNAPSHOT SETTINGS:

BEGIN
  DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
    retention => 43200,        -- Minutes (43200 = 30 Days).
                               -- Current value retained if NULL.
    interval  => 30);          -- Minutes. Current value retained if NULL.
END;
/

Creating a Baseline:

BEGIN
  DBMS_WORKLOAD_REPOSITORY.create_baseline (
    start_snap_id => 10, 
    end_snap_id   => 100,
    baseline_name => 'AWR First baseline');
END;
/


In 11g, there is a newly introduced procedure DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE that specifies a template for how baselines should be created for future time periods:



BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE (
start_time => to_date('&start_date_time','&start_date_time_format'),
end_time => to_date('&end_date_time','&end_date_time_format'),
baseline_name => 'MORNING',
template_name => 'MORNING',
expiration => NULL ) ;
END;
/

"expiration => NULL" means that this baseline will be kept forever.

Dropping an AWR baseline:

BEGIN
    DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE (
    baseline_name => 'AWR First baseline');
END;
/

You can also drop baseline from old databases:


BEGIN
DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE (baseline_name => 'peak baseline',
cascade => FALSE, dbid => 3310949047);
END;
/

Dropping the AWR snaps in range:

BEGIN
  DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range(
(low_snap_id=>40,
High_snap_id=>80);
END;
/

We can also specify a template for creating and dropping baseline based on repeating time periods:


BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE (
day_of_week => 'MONDAY',
hour_in_day => 9,
duration => 3,
start_time => to_date('&start_date_time','&start_date_time_format'),
end_time => to_date('&end_date_time','&end_date_time_format'),
baseline_name_prefix => 'MONDAY_MORNING'
template_name => 'MONDAY_MORNING',
expiration => 30 );
END;
/[Insert code here]

The baseline will be generated for the period from '&start_date_time' to '&end_date_time' every Monday.

Creating a SNAPSHOT Manually:

BEGIN
  DBMS_WORKLOAD_REPOSITORY.create_snapshot();
END;
/
 

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.

Automation of AWR reports ?

There is no product functionality to produce the AWR reports in an automated way. In regards to the Oracle product, the production of a report is a manual step. However, one could use the dbms_workload_repository.awr_report_text in a (bash for instance for Unix like OS's) script. It would be scheduled (using the crontab for Unix like OS's) and be instructed with or determine the variables to provide to the awr_report_text function. There are such scripts available on the web, but not officially provided by Oracle as part of the product.

References

NOTE:228913.1 - Systemwide Tuning using STATSPACK Reports
NOTE:394937.1 - Statistics Package (STATSPACK) Guide

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/11780477/viewspace-706426/,如需转载,请注明出处,否则将追究法律责任。

上一篇: vmstat 命令
请登录后发表评论 登录
全部评论

注册时间:2009-06-12

  • 博文量
    194
  • 访问量
    596495