个人总结
生成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.
后面的两个参考文档不错,看一下.
| 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.2Information 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 executingthe 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;
/
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;
/
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;
/
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;
/
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;
/
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]
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;
/
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 ReportsNOTE:394937.1 - Statistics Package (STATSPACK) Guide
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/11780477/viewspace-706426/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/11780477/viewspace-706426/
9560
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
