5.2 Overview of the Automatic Workload Repository AWR简介
The Automatic Workload Repository (AWR) collects, processes, and maintains performance statistics for problem detection and self-tuning purposes. This data is both in memory and stored in the database. The gathered data can be displayed in both reports and views.
为了诊断问题和自动调优,AWR做统计数据的收集、处理和维护工作。这些统计数据是存储在内存及表空间中的。收集的统计数据可以通过报表的方式或者视图的方式展现出来。
The statistics collected and processed by AWR include: AWR收集和处理的统计数据包含如下：
- Object statistics that determine both access and usage statistics of database segments 【对象统计数据，段的访问和使用统计】
- Time model statistics based on time usage for activities, displayed in the V$SYS_TIME_MODEL and V$SESS_TIME_MODEL views
- Some of the system and session statistics collected in the V$SYSSTAT and V$SESSTAT views
- SQL statements that are producing the highest load on the system, based on criteria such as elapsed time and CPU time 【v$sql】
- ASH statistics, representing the history of recent sessions activity 【ASH统计，表示最近历史活跃session统计】
Gathering database statistics using the AWR is enabled by default and is controlled by the STATISTICS_LEVEL initialization parameter. The STATISTICS_LEVEL parameter should be set to the TYPICAL or ALL to enable statistics gathering by the AWR. The default setting is TYPICAL. Setting STATISTICS_LEVEL to BASIC disables many Oracle Database features, including the AWR, and is not recommended. If STATISTICS_LEVEL is set to BASIC, you can still manually capture AWR statistics using the DBMS_WORKLOAD_REPOSITORY package. However, because in-memory collection of many system statistics—such as segments statistics and memory advisor information—will be disabled, the statistics captured in these snapshots may not be complete.
使用AWR收集数据库统计信息默认是打开的，这个功能是由statistics_level参数控制的。这个参数应该被设置成TYPICAL或者ALL以允许AWR去收集。默认是TYPICAL。 如果将statistics_level设置成basic的话，将禁用掉很多的oracle特性，包括AWR功能，因此是不建议设置此值。 但是即使你将STATISTICS_LEVEL设置成BASIC，你也可以自动的使用DBMS_WORKLOAD_REPOSITORY包去收集AWR数据。但是因为内存中的许多统计收集---比如段统计和内存建议信息---是没有启用的，所以在手动捕捉的统计数据中是不完整的。（总的来说，千万别把统计数据功能停掉）
Snapshots are sets of historical data for specific time periods that are used for performance comparisons by ADDM. By default, Oracle Database automatically generates snapshots of the performance data once every hour and retains the statistics in the workload repository for 8 days. You can also manually create snapshots, but this is usually not necessary. The data in the snapshot interval is then analyzed by the Automatic Database Diagnostic Monitor (ADDM).
AWR compares the difference between snapshots to determine which SQL statements to capture based on the effect on the system load. This reduces the number of SQL statements that must be captured over time.
快照是某一时间段内的一组历史数据集合，它们被ADDM用来进行性能比较。默认情况下数据库会自动的每1小时产生一个快照，并且保留8天时间。你也可以手动的产生快照，但是这通常是没必要的。相隔快照（2个或者多个快照）中的数据被ADDM用来做分析使用。
AWR比较快照间的差异，以基于系统的负荷判断哪些SQL语句去捕捉。这就减少了定期去捕捉SQL语句的必要性。
【注：也就是说为了收集历史的数据，那该如何做呢？时时刻刻去收集？这不太现实的，其实ASH已经做这事情了，但是由于量太大，所以很多数据都丢了。但是很多时间我们需要一个大概的概念，比如刚才那一会儿（可能是10分钟，半小时）都有多少人登录之类的，这就需要一个时间段的信息。
在数据库的所有活动中，数据已经实时的累积到相关的v$视图中了。
为了方便后继的性能问题研究，我们需要详细的统计数据以及诊断信息，却又不能将每一个session的工作都全部详细的保存，因此需要做一个折衷的方案就是每隔1小时去看一下，这样在数据库中保存的统计信息就大大减少了。这个每隔多久去收集一次看自己的情况，比如你的系统高峰期时间就是30小时，那就半小时去收集一次。如果我每次说那个半小时收集的数据要怎么怎么，估计大家要疯，因此我们需要一个简单的词来代表那半小时收集的数据，这就是“快照”。以后说到快照，就指那半小时内收集的数据。我们自己自动的去收集一下数据，即做个快照。
】
下图是我在oem中截的图，可以看到默认的为8天，interval时间为60分钟即每小时收集一次，收集的级别(statistic_level为typical)
A baseline contains performance data from a specific time period that is preserved for comparison with other similar workload periods when performance problems occur. The snapshots contained in a baseline are excluded from the automatic AWR purging process and are retained indefinitely.
基线包含了一个指定时间内的性能数据，它们被保存起来，当性能出现问题的时候，将那段时间内差的性能数据与基线进行对比（也就是说找到一个标准，所有都和标准比较，无规律无以成方圆）。基线中的快照信息是不会被AWR的自动清理机制删除掉的，它们会被无限的保留起来，除非你自己手动去删除。
【刚才我们已经了解到一段时间内的性能数据我们称之为快照，那已经有了快照，为什么搞这个基线？难道又要再收集？已经做的事情当然不能再做了，----所以基线是由快照组成的】
There are several types of available baselines in Oracle Database: 数据库中有许多类型的可用基线
- Fixed Baselines 固定基线
- Moving Window Baseline 移动窗口基线
- Baseline Templates 基线模板
A fixed baseline corresponds to a fixed, contiguous time period in the past that you specify. Before creating a fixed baseline, carefully consider the time period you choose as a baseline, because the baseline should represent the system operating at an optimal level. In the future, you can compare the baseline with other baselines or snapshots captured during periods of poor performance to analyze performance degradation over time.
固定基线对应着一个你指定的过去固定，连续的时间段。在创建固定基线前，考虑好你要将哪个时间段作为基线，因为这个基线是要作为你系统运行在最佳状态的历史见证。在未来，当性能不行的时候你需要去拿这个固定基线去和其他的基线或者快照进行比较的。
5.2.2.2 Moving Window Baseline
A moving window baseline corresponds to all AWR data that exists within the AWR retention period. This is useful when using adaptive thresholds because the database can use AWR data in the entire AWR retention period to compute metric threshold values.
Oracle Database automatically maintains a system-defined moving window baseline. The default window size for the system-defined moving window baseline is the current AWR retention period, which by default is 8 days. If you are planning to use adaptive thresholds, consider using a larger moving window—such as 30 days—to accurately compute threshold values. You can resize the moving window baseline by changing the number of days in the moving window to a value that is equal to or less than the number of days in the AWR retention period. Therefore, to increase the size of a moving window, you must first increase the AWR retention period accordingly.
移动窗口基线对应着所有的AWR保留期内的AWR数据。当使用自适应阈值时非常有用，因为数据库可以使用所有的AWR数据去计算度量阈值。
数据库自动的维护着一个system-defined的移动窗口基线。这个默认的system-defined移动窗口基线的窗口大小是当前AWR保留期，也就是说默认为8天。如果计划使用自适应阈值，则可考虑使用较大的移动窗口（如30天），以便精确地计算阈值。通过将移动窗口中的天数更改为等于或小于AWR保留期中的天数的值，可以调整移动窗口基线的大小。因此，要增加移动窗口的大小，需要先相应地增加AWR保留期。。(这里有两个窗口，一个是移动窗口的天数，这个窗口是移动窗口基线要用的，另一个AWR保留的天数。移动窗口的大小不能超过AWR的保留期，这个很明显了，超过之后就没有数据供其分析了，所以移动窗口一定要小于AWR保留期的。移动窗口基线是在移动窗口中运行的，那么移动窗口天数变短时，移动窗口基线自然就短了。同时要增加移动窗口，AWR保留期也要加大才行，这里要好好理一理，我看了好几遍才理解！)
You can also create baselines for a contiguous time period in the future using baseline templates. There are two types of baseline templates: single and repeating.
You can use a single baseline template to create a baseline for a single contiguous time period in the future. This technique is useful if you know beforehand of a time period that you intend to capture in the future. For example, you may want to capture the AWR data during a system test that is scheduled for the upcoming weekend. In this case, you can create a single baseline template to automatically capture the time period when the test occurs.
You can use a repeating baseline template to create and drop baselines based on a repeating time schedule. This is useful if you want Oracle Database to automatically capture a contiguous time period on an ongoing basis. For example, you may want to capture the AWR data during every Monday morning for a month. In this case, you can create a repeating baseline template to automatically create baselines on a repeating schedule for every Monday, and automatically remove older baselines after a specified expiration interval, such as one month.
你可以使用基线模板在未来的某一时间段里创建一个基线。有两种类型的基线模板：单一的和重复的。
单一基线模板：用于在未来某一时间段内创建一个基线。这种技术对于现在你已经知道要在比如晚上3点钟去创建一个基线时非常有用。例如你想在周末要到来的压力测试中收集AWR数据时（这时候可以不用加班直接收集了，不过在国内很难啊，一样要加班的啊；但是如果想要在是生产系统，在知道周末有压力高峰就可以这么做了），这种情况下你就可以创建一个基线模板，然后在未来的那个时间段内让系统自动的收集一下。（假如压力高峰突然从下午3点改成5点，那就挂了）
重复基线模板：用于重复时间段调用中自动的创建和删除基线。这种技术对于你想让数据库自动的捕捉一个连续时间段在一个既定需求中。例如，你想去捕捉一个月的每周一早晨的AWR数据（假如一月有4周的话，就是要收集4次的）。在这种情况下，你可以创建一个重复基线模板在每周一早晨去自动的创建基线，并且自动地在基线超过了设定的时间间隙后删除掉旧的基线，比如在一个月后。
以前，只能针对已经存在的快照创建基线。在11g中，晚上MMON任务会遍历所有模板以生成基线，并会进行检查以了解过去一天是否有任何时段从将来时间变成了过去时间（也就是说，明天才计划今天的东西，如果要在周末创建，那么只有到周一时才会发现有需要创建的模块，并以此创建基线）。对于那些相关的时段，MMON 任务随后会针对该时段创建一个基线。
Adaptive thresholds enable you to monitor and detect performance issues while minimizing administrative overhead. Adaptive thresholds can automatically set warning and critical alert thresholds for some system metrics using statistics derived from metric values captured in the moving window baseline. The statistics for these thresholds are recomputed weekly and might result in new thresholds as system performance evolves over time. In addition to recalculating thresholds weekly, adaptive thresholds might compute different thresholds values for different times of the day or week based on periodic workload patterns.
For example, many databases support an online transaction processing (OLTP) workload during the day and batch processing at night. The performance metric for response time per transaction can be useful for detecting degradation in OLTP performance during the day. However, a useful OLTP threshold value is almost certainly too low for batch workloads, where long-running transactions might be common. As a result, threshold values appropriate to OLTP might trigger frequent false performance alerts during batch processing. Adaptive thresholds can detect such a workload pattern and automatically set different threshold values for the daytime and nighttime.
自适应阈值使你以最小的管理压力去监控和侦测性能问题。自适应阈值可以根据一些系统的度量值自动地设置警告和严重警告阈值，这些度量值是根据移动窗口基线捕捉到的一些度量值推导出来的。这些阈值统计数据每周会重新计算一次，随着系统演化，这就产生新的系统性能的阈值。除了每周重新计算阈值，自适应阈值可以从基于定期的压力模型中以天或者周的不同的时间段计算不同的阈值。（也就是说白天和晚上是不同的压力模型，因为在以天为单位时可以使用不同的阈值，每周末可能出报表，因此一周也有不同的阈值）
比如，许多数据库支持白天时处理OLTP压力，晚上处理批量系统。每事务处理的响应时间度量值对于诊断OLTP性能下降是有用的。然而，这个OLTP的阈值对于批量却是太小了，因为批量很多时候需要长时间运行的。因此，OLTP的阈值将在处理批量时到处报警性能问题。自适应阈值可以诊断这个压力模式，并且在白天和晚上自动的设置不同的阈值。
Note:
In Oracle Database 11g Release 2 (11.2), Oracle Database automatically determines the appropriate time groupings for a database. However, before Oracle Database 11g Release 2 (11.2), time groupings were specified manually by the database administrator.
在11.2以后的版本中，数据库会自动的判断相应时间组，而在11.2以前的版本中，时间组的划分是由管理员做的。
There are two types of adaptive thresholds: 有两种类型的自适应阈值：（其实还包括一个“固定值”，共3个）
- Percentage of maximum: The threshold value is computed as a percentage multiple of the maximum value observed for the data in the moving window baseline.
最大百分比： 阈值被计算作为一个百分比，乘以从移动窗口基线中获取的最大值。
- Significance level: The threshold value is set to a statistical percentile that represents how unusual it is to observe values above the threshold value based the data in the moving window baseline. Specify one of the following percentiles:
重要级别： 阈值被设置成一个统计百分比，代表与从移动窗口基线中观察到的阈值有多少不同。指定以下几种比率：
-
- High (.95): Only 5 in 100 observations are expected to exceed this value. 一般高：5% 可以超出这个值
- Very High (.99): Only 1 in 100 observations are expected to exceed this value. 非常高：1% 可以超出这个值
- Severe (.999): Only 1 in 1,000 observations are expected to exceed this value. 严重的：0.1% 可以走出这个值
- Extreme (.9999): Only 1 in 10,000 observations are expected to exceed this value.极严重：0.01%可以走出这个值
Note:
When you specify Severe (.999) or Extreme (.9999), Oracle Database performs an internal calculation to set the threshold value. In some cases, Oracle Database cannot establish the threshold value at these levels using the data in the baseline, and the significance level threshold is not set.
If you are not receiving alerts as expected, and you specified a Severe (.999) or Extreme (.9999) significance level threshold, then you can try setting the significance level threshold to a lower value, such as Very High (.99) or High (.95). Alternatively, you can set a percentage of maximum threshold instead of a significance level threshold. If you change the threshold and find that you are receiving too many alerts, then you can try increasing the number of occurrences to cause an alert.
当你指定0.1%或0.01%时，数据库会内部计算一个阈值。在这种情况下，数据库不能以基线中的数据在这种级别下建立阈值，并且重要级别没有被设置。
假如你没有接收到期待的警告，并且你指定了0.1%或0.01%重要级别阈值，那么你可以将这个重要级别阈值设置的低一些，例如1%或者5%。同样地，你可以使用最大阈值百分比而不是使用重要级别阈值。假如你改变阈值后发现接收到太多的警告，那么你可以增加发生警告的值。
Percentage of maximum thresholds are most useful when a system is sized for peak workloads, and you want to be alerted when the current workload volume is approaching or exceeding previous high values. Metrics that have an unknown but definite limiting value are good candidates for these settings. For example, the redo generated per second metric is typically a good candidate for a percentage of maximum threshold.
Significance level thresholds are most useful for metrics that should exhibit statistically stable behavior when the system is operating normally, but might vary over a wide range when the system is performing poorly. For example, the response time per transaction metric should be stable for a well-tuned OLTP system, but may fluctuate widely when performance issues arise. Significance level thresholds are meant to generate alerts when conditions produce both unusual metric values and unusual system performance.
最大百分比阈值是对于固定的高峰压力，并且希望当目前的压力量超过前一个高峰值时想得到警告时比较有用。对于未知的度量值，但是有其限度的值是这些设置的一个很好候选。例如当每秒钟产生的日志就可以作为最大阈值的一个候选值。
重要级别阈值在系统运行正常时显示其稳定的统计行为度量值是非常有用的，但是当系统性能差的时候变化幅度很大。例如事务的响应时间度量在OLTP系统中应该是一个相对稳定的值，但是当性能出现问题时波动很大。重要级别阈值是当不寻常的度量值 和不寻常的系统性能发生时所产生的警告才是有意义的。
Note:
The primary interface for managing baseline metrics is Oracle Enterprise Manager. To create an adaptive threshold for a baseline metric, use Oracle Enterprise Manager
在OEM中，其实是有三种类型的: significance level, percentage of Maximum, Fixed Values。
下图是All Metrics中截取的一部分，这个页面非常长，说明 metrics非常的多
下图是我根据： Basic and Additional Metrics 截取的一部分图，这里只有三大类： Performance Metrics, Workload Volume Metrics, Workload Type Metrics
The space consumed by the AWR is determined by several factors: AWR消耗的空间由以下因素决定：
- Number of active sessions in the system at any given time 在某一时间内active session数
- Snapshot interval 快照间隙
The snapshot interval determines the frequency at which snapshots are captured. A smaller snapshot interval increases the frequency, which increases the volume of data collected by the AWR. 快照间隙决定的快照捕捉的频率。快照间隙越小，频率越高，因此就增加了AWR收集的数据量
- Historical data retention period 历史数据保留期限
The retention period determines how long this data is retained before being purged. A longer retention period increases the space consumed by the AWR.
保留期限决定了数据在被清除前保留的期限。保留的时间越长自然就占用的空间越大。
By default, snapshots are captured once every hour and are retained in the database for 8 days. With these default settings, a typical system with an average of 10 concurrent active sessions can require approximately 200 to 300 MB of space for its AWR data. It is possible to change the default values for both snapshot interval and retention period.
The AWR space consumption can be reduced by the increasing the snapshot interval and reducing the retention period. When reducing the retention period, note that several Oracle Database self-managing features depend on AWR data for proper functioning. Not having enough data can affect the validity and accuracy of these components and features, including:
默认情况下，每小时捕捉一次快照，并且保留8天时间。 使用这个默认设置的话，一个平均10个活跃并发会话的系统，AWR大约会占200到300M的空间（即8天占300M，那么1个月大约就是1.2G左右；同时一天大约使用300M/8 = 40M，而1小时收集一次，那一共是24，40M/24 = 1.5M左右，即每一次收集就大约占用1.5M左右的空间，这个收集的量还是比较大的）。可以通过dbms包去改变这个快照的间隔和保留时间。
可以通过增加快照的间隙、减少快照的保留时间，达到减少AWR空间消耗目的。当减少快照保留期限时需要注意，许多oracle的自管理特性都是依赖于AWR的数据才能工作的（比如ADDM）。如果没有足够的AWR数据将影响许多功能模块精确的验证分析，包括：
- Automatic Database Diagnostic Monitor 【ADDM】
- SQL Tuning Advisor 【SQL Tuning Advisor】
- Undo Advisor 【Undo Advisor】
- Segment Advisor 【Segment Advisor】
If possible, Oracle recommends that you set the AWR retention period large enough to capture at least one complete workload cycle. If your system experiences weekly workload cycles, such as OLTP workload during weekdays and batch jobs during the weekend, you do not need to change the default AWR retention period of 8 days. However if your system is subjected to a monthly peak load during month end book closing, you may have to set the retention period to one month.
Under exceptional circumstances, you can turn off automatic snapshot collection by setting the snapshot interval to 0. Under this condition, the automatic collection of the workload and statistical data is stopped and much of the Oracle Database self-management functionality is not operational. In addition, you cannot manually create snapshots. For this reason, Oracle strongly recommends that you do not turn off automatic snapshot collection.
假如可能的话，oracle建议将AWR的保留期限设置成足够完成一个压力周期的值。假如你的系统一般是一周的一个压力周期，比如每天主要是OLTP处理，而在周末处理批量程序，你就不需要去更改默认的8天期限。但是如果你的系统是要月底进行账目结算的月高峰压力模式，你就应该把这个期限改成1个月。
在异常环境下，通过将快照的间隙设置成0以关闭自动快照收集功能。在这种情况下，自动压力和统计数据的收集将停止工作，而且多数的数据库自管理功能将无法工作。在这种情况下，你不能手动创建快照。因此oracle强烈的建议你不要去关闭自动快照收集功能。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/1696240/viewspace-1177901/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/1696240/viewspace-1177901/
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
