5.3.7 Generating Automatic Workload Repository Compare Periods Reports
While an AWR report shows AWR data between two snapshots (or two points in time), the AWR Compare Periods report shows the difference between two periods (or two AWR reports, which equates to four snapshots). Using the AWR Compare Periods report helps you to identify detailed performance attributes and configuration settings that differ between two time periods.
For example, if the application workload is known to be stable between 10:00 p.m. and midnight every night, but the performance on a particular Thursday was poor between 10:00 p.m. and 11:00 p.m., generating an AWR Compare Periods report for Thursday from 10:00 p.m. to 11:00 p.m. and Wednesday from 10:00 p.m. to 11:00 p.m. should identify configuration settings, workload profile, and statistics that were different in these time periods. Based on the differences, you can more easily diagnose the cause of the performance degradation. The two time periods selected for the AWR Compare Periods Report can be of different durations because the report normalizes the statistics by the amount of time spent on the database for each time period, and presents statistical data ordered by the largest difference between the periods.
The AWR Compare Periods reports are divided into multiple sections. The HTML report includes links that can be used to navigate quickly between sections. The content of the report contains the workload profile of the system for the selected range of snapshots.
The primary interface for generating AWR Compare Periods reports is Oracle Enterprise Manager. Whenever possible, you should generate AWR Compare Periods reports using Oracle Enterprise Manager. If Oracle Enterprise Manager is unavailable, you can generate AWR Compare Periods reports by running SQL scripts, as described in the following sections:
AWR报告显示两个快照之间的数据（使用了2个快照），AWR比较时段报告显示两个时期的差异（或者2个AWR报告进行差异比较，等价于使用4个快照）。使用AWR比较时段报告帮助你确定不同时段的更详细的性能属性和配置差异。
例如：假如已经知道一个应用程序的压力稳定在晚上10点到12点，但是有一天周四的晚上10点到11点时性能很差，那产生一份关于周三和周四都是10~11点这时间段的AWR比较时段报告就可以确定配置设定，压力剖面，以及两个时间段内的统计差异情况。基于这个差异，你就可以很容易地诊断性能下降的原因。为AWR比较时段报告选定的两个时段的间隙可以是不同的，因为这个报告可以根据每个时间段的时间统计进行规格化，并且按最大差异的顺序进行列举统计数据。
AWR比较时段报告被切割成多个段。HTML格式的报告包含了可以快速链接到其他段的链接锚点。报告的内容包含着选定的那个快照间的系统压力剖面。
OEM是产生AWR比较时段报告的是主要接口。有可能的话就去使用OEM。假如OEM无法使用，你可以使用下面的SQL脚本：
上面介绍了一大堆的官方话语，如果你自己实际产生出一个AWR，以及使用5.3.7.1产生一个比较文件，一目了然啦！ 看上面的什么锚点之类的，恶心死了，但是我只能这么翻译，因为在HTML中链接就是锚点，用《a》xxxxx《/a》这样的符号进行设置的。本人虽然不开发网页，但是对于HTML语言，5年前还是研究过的，当时将HTML4中的所有元素都背熟悉了，给山西一个很小很小很小的县城里写了一个暂住证管理的系统，使用PHP+JQUERY+CSS+MYSQL开发的，一键安装，花了15个没日没夜开发，所有的HTML都是自己手动敲进去的，一个个CSS慢慢调出来的，那个派出所竟然一分钱也没给我，你说能信得过他们嘛！也许我是山西唯一一个可以信得过的人！
- Generating an AWR Compare Periods Report 【产生一个AWR比较时段报表，主要是这个和RAC，其他的不太常用】
- Generating an Oracle RAC AWR Compare Periods Report 产生一个RAC的AWR比较时段报表
- Generating an AWR Compare Periods Report on a Specific Database Instance 【产生一个指定数据库实例的AWR比较时段报表】
- Generating an Oracle RAC AWR Compare Periods Report on Specific Database Instances 产生一个指定实例的RAC的AWR比较时段报表
To run these scripts, you must be granted the DBA role. 【为了运行这些脚本，你必须以DBA的权限】
5.3.7.1 Generating an AWR Compare Periods Report
The awrddrpt.sql SQL script generates an HTML or text report that compares detailed performance attributes and configuration settings between two selected time periods.
To generate an AWR Compare Periods report:
- At the SQL prompt, enter:
@$ORACLE_HOME/rdbms/admin/awrddrpt.sql 【在出报告时使用awrrpt.sql，中间这两个dd我也不清楚是什么含义，display?】
- Specify whether you want an HTML or a text report:
Enter value for report_type: 【html】
In this example, an HTML report is chosen.
- Specify the number of days for which you want to list snapshot IDs in the first time period.
Enter value for num_days: 【2】
A list of existing snapshots for the specified time range is displayed. In this example, snapshots captured in the last 2 days are displayed.
- Specify a beginning and ending snapshot ID for the first time period:
Enter value for begin_snap: 【102】
Enter value for end_snap: 【103】
In this example, the snapshot with a snapshot ID of 102 is selected as the beginning snapshot, and the snapshot with a snapshot ID of 103 is selected as the ending snapshot for the first time period.
- Specify the number of days for which you want to list snapshot IDs in the second time period.
Enter value for num_days2: 【1】
A list of existing snapshots for the specified time range is displayed. In this example, snapshots captured in the previous day are displayed.
- Specify a beginning and ending snapshot ID for the second time period:
Enter value for begin_snap2: 126
Enter value for end_snap2: 127
In this example, the snapshot with a snapshot ID of 126 is selected as the beginning snapshot, and the snapshot with a snapshot ID of 127 is selected as the ending snapshot for the second time period.
Enter a report name, or accept the default report name:
Enter value for report_name:
Using the report name awrdiff_1_102_1_126.txt
In this example, the default name is accepted and an AWR report named awrdiff_1_102_126 is generated.
下图是OEM的报告生成界面，我们发现与脚本相比这里多了一个”By Baseline”，而在脚本中是只能按照快照来产生；
但是本质都是一样的，因为基线就是由一段连续的快照组成的，所以默认包含了起始与结束的快照信息
5.3.7.2 Generating an Oracle RAC AWR Compare Periods Report
The awrgdrpt.sql SQL script generates an HTML or text report that compares detailed performance attributes and configuration settings between two selected time periods using the current database identifier and all available database instances in an Oracle RAC environment.
Note:
In an Oracle RAC environment, you should always try to generate an HTML report (instead of a text report) because they are much easier to read.
To generate an AWR Compare Periods report in an Oracle RAC environment:
- At the SQL prompt, enter:
@$ORACLE_HOME/rdbms/admin/awrgdrpt.sql 【grid display? 】
- Specify whether you want an HTML or a text report:
Enter value for report_type: 【html】
In this example, an HTML report is chosen.
- Specify the number of days for which you want to list snapshot IDs in the first time period.
Enter value for num_days: 【2】
A list of existing snapshots for the specified time range is displayed. In this example, snapshots captured in the last 2 days are displayed.
- Specify a beginning and ending snapshot ID for the first time period:
Enter value for begin_snap: 【102】
Enter value for end_snap: 【103】
In this example, the snapshot with a snapshot ID of 102 is selected as the beginning snapshot, and the snapshot with a snapshot ID of 103 is selected as the ending snapshot for the first time period.
- Specify the number of days for which you want to list snapshot IDs in the second time period.
Enter value for num_days2: 【1】
A list of existing snapshots for the specified time range is displayed. In this example, snapshots captured in the previous day are displayed.
- Specify a beginning and ending snapshot ID for the second time period:
Enter value for begin_snap2: 【126】
Enter value for end_snap2: 【127】
In this example, the snapshot with a snapshot ID of 126 is selected as the beginning snapshot, and the snapshot with a snapshot ID of 127 is selected as the ending snapshot for the second time period.
Enter a report name, or accept the default report name:
Enter value for report_name:
Using the report name awrracdiff_1st_1_2nd_1.html
In this example, the default name is accepted and an AWR report named awrrac_1st_1_2nd_1.html is generated.
5.3.7.3 Generating an AWR Compare Periods Report on a Specific Database Instance
The awrddrpi.sql SQL script generates an HTML or text report that compares detailed performance attributes and configuration settings between two selected time periods on a specific database and instance. This script enables you to specify a database identifier and instance for which the AWR Compare Periods report will be generated.
To generate an AWR Compare Periods report on a specified database instance:
- At the SQL prompt, enter:
@$ORACLE_HOME/rdbms/admin/awrddrpi.sql 【为什么不是awrddrpti.sql？ 命名真是有问题】
- Specify whether you want an HTML or a text report:
Enter value for report_type: 【text】
In this example, a text report is chosen.
- A list of available database identifiers and instance numbers are displayed:
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
----------- -------- ------------ ------------ ------------
3309173529 1 MAIN main examp1690
3309173529 1 TINT251 tint251 samp251
Enter the values for the database identifier (dbid) and instance number (inst_num) for the first time period:
Enter value for dbid: 【3309173529】
Using 3309173529 for Database Id for the first pair of snapshots
Enter value for inst_num: 【1】
Using 1 for Instance Number for the first pair of snapshots
- Specify the number of days for which you want to list snapshot IDs in the first time period.
Enter value for num_days: 【2】
A list of existing snapshots for the specified time range is displayed. In this example, snapshots captured in the last 2 days are displayed.
- Specify a beginning and ending snapshot ID for the first time period:
Enter value for begin_snap: 【102】
Enter value for end_snap: 【103】
In this example, the snapshot with a snapshot ID of 102 is selected as the beginning snapshot, and the snapshot with a snapshot ID of 103 is selected as the ending snapshot for the first time period.
Enter the values for the database identifier (dbid) and instance number (inst_num) for the second time period:
Enter value for dbid2: 3309173529
6. Using 3309173529 for Database Id for the second pair of snapshots
Enter value for inst_num2: 【1】
Using 1 for Instance Number for the second pair of snapshots
Specify the number of days for which you want to list snapshot IDs in the second time period.
Enter value for num_days2: 【1】
A list of existing snapshots for the specified time range is displayed. In this example, snapshots captured in the previous day are displayed.
- Specify a beginning and ending snapshot ID for the second time period:
Enter value for begin_snap2: 【126】
Enter value for end_snap2: 【127】
In this example, the snapshot with a snapshot ID of 126 is selected as the beginning snapshot, and the snapshot with a snapshot ID of 127 is selected as the ending snapshot for the second time period.
Enter a report name, or accept the default report name:
Enter value for report_name:
Using the report name awrdiff_1_102_1_126.txt
In this example, the default name is accepted and an AWR report named awrdiff_1_102_126 is generated on the database instance with a database ID value of 3309173529.
5.3.7.4 Generating an Oracle RAC AWR Compare Periods Report on Specific Database Instances
The awrgdrpi.sql SQL script generates an HTML or text report that compares detailed performance attributes and configuration settings between two selected time periods using specific databases and instances in an Oracle RAC environment. This script enables you to specify database identifiers and a comma-delimited list of database instances for which the AWR Compare Periods report will be generated.
Note:
In an Oracle RAC environment, you should always try to generate an HTML report (instead of a text report) because they are much easier to read.
To generate an AWR Compare Periods report on a specified database instance in an Oracle RAC environment:
- At the SQL prompt, enter:
@$ORACLE_HOME/rdbms/admin/awrgdrpi.sql
- Specify whether you want an HTML or a text report:
Enter value for report_type: 【html】
In this example, an HTML report is chosen.
A list of available database identifiers and instance numbers are displayed:
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
----------- -------- ------------ ------------ ------------
3309173529 1 MAIN main examp1690
3309173529 1 TINT251 tint251 samp251
3309173529 2 TINT251 tint252 samp252
3309173529 3 TINT251 tint253 samp253
3309173529 4 TINT251 tint254 samp254
Enter the values for the database identifier (dbid) and instance number (instance_numbers_or_all) for the first time period:
Enter value for dbid: 【3309173529】
Using 3309173529 for Database Id for the first pair of snapshots
Enter value for inst_num: 【1,2】
Using instances 1 for the first pair of snapshots
- Specify the number of days for which you want to list snapshot IDs in the first time period.
Enter value for num_days: 【2】
A list of existing snapshots for the specified time range is displayed. In this example, snapshots captured in the last 2 days are displayed.
- Specify a beginning and ending snapshot ID for the first time period:
Enter value for begin_snap: 【102】
Enter value for end_snap: 【103】
In this example, the snapshot with a snapshot ID of 102 is selected as the beginning snapshot, and the snapshot with a snapshot ID of 103 is selected as the ending snapshot for the first time period.
A list of available database identifiers and instance numbers are displayed:
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
----------- -------- ------------ ------------ ------------
3309173529 1 MAIN main examp1690
3309173529 1 TINT251 tint251 samp251
3309173529 2 TINT251 tint252 samp252
3309173529 3 TINT251 tint253 samp253
3309173529 4 TINT251 tint254 samp254
INSTNUM1
-----------------------------------------------------
1,2
Enter the values for the database identifier (dbid2) and instance numbers (instance_numbers_or_all2) for the second time period:
Enter value for dbid2: 【3309173529】
Using 3309173529 for Database Id for the second pair of snapshots
Enter value for instance_numbers_or_all2: 【3,4】
- Specify the number of days for which you want to list snapshot IDs in the second time period.
Enter value for num_days2: 【1】
A list of existing snapshots for the specified time range is displayed. In this example, snapshots captured in the previous day are displayed.
- Specify a beginning and ending snapshot ID for the second time period:
Enter value for begin_snap2: 【126】
Enter value for end_snap2: 【127】
In this example, the snapshot with a snapshot ID of 126 is selected as the beginning snapshot, and the snapshot with a snapshot ID of 127 is selected as the ending snapshot for the second time period.
Enter a report name, or accept the default report name:
Enter value for report_name:
Using the report name awrracdiff_1st_1_2nd_1.html
In this example, the default name is accepted and an AWR report named awrrac_1st_1_2nd_1.html is generated.
5.3.8 Generating Active Session History Reports
Use Active Session History (ASH) reports to perform analysis of:
使用ASH报告去执行下面的分析：
过去几分钟短暂的性能问题
通过不同维度或者一些组合（时间，会话，module,action或者SQL_ID）进行的范围或目标性能分析
- Transient performance problems that typically last for a few minutes
- Scoped or targeted performance analysis by various dimensions or their combinations, such as time, session, module, action, or SQL_ID
Transient performance problems are short-lived and do not appear in the Automatic Database Diagnostics Monitor (ADDM) analysis. ADDM tries to report the most significant performance problems during an analysis period in terms of their impact on DB time. If a particular problem lasts for a very short duration, then its severity might be averaged out or minimized by other performance problems in the analysis period. Therefore, the problem may not appear in the ADDM findings. Whether a performance problem is captured by ADDM depends on its duration compared to the interval between the AWR snapshots.
If a performance problem lasts for a significant portion of the time between snapshots, it will be captured by ADDM. For example, if the snapshot interval is set to one hour, a performance problem that lasts for 30 minutes should not be considered as a transient performance problem because its duration represents a significant portion of the snapshot interval and will likely be captured by ADDM.
However, a performance problem that lasts for only 2 minutes could be a transient performance problem because its duration represents a small portion of the snapshot interval and will likely not show up in the ADDM findings. For example, if the user notifies you that the system was slow between 10:00 p.m. and 10:10 p.m., but the ADDM analysis for the time period between 10:00 p.m. and 11:00 p.m. does not show a performance problem, a transient performance problem probably occurred that lasted for only a few minutes of the 10-minute interval reported by the user.
The ASH reports are divided into multiple sections. The HTML report includes links that can be used to navigate quickly between sections. The content of the report contains ASH information used to identify blocker and waiter identities and their associated transaction identifiers and SQL for a specified duration.
The primary interface for generating ASH reports is Oracle Enterprise Manager. Whenever possible, you should generate ASH reports using Oracle Enterprise Manager. If Oracle Enterprise Manager is unavailable, you can generate ASH reports by running SQL scripts, as described in the following sections:
短暂的性能问题也就是短时间内存在的问题，它们不会存在于ADDM的分析中（ADDM看不上他们这些小喽啰的）。ADDM试图在一个分析时段内根据DB time去报告那些显著的性能问题。假如一个特定的性能问题仅仅持续很短时间，那么它的严重性会在分析时段“被平均化”或者被其他的更牛的性能问题边缘化了。因此，这个问题可能不会出现在ADDM发现中。一个问题是否被ADDM捕捉依赖于它的持续性，特别是AWR快照的间隔。
假如在一个快照间隔中，一个性能问题占了很大一部分，那么它就会被ADDM捕捉到。例如，假如快照间隔是1小时，那么一个持续30分钟的性能问题就不是一个短暂的性能问题，因为它占了快照的相当大的部分，所以很可能被ADDM捕捉到。
然而，一个仅仅持续2分钟的短暂性能问题，因为它占了很小一部分，所以很可能不会显示在ADDM发现中。例如客户说系统在晚上10点到10:10这10分钟内很慢，但是ADDM并没有报告这10分钟内有性能问题，短时间性能问题可能发生在那10分钟间隔报告中的几分钟内。
ASH报告被切割成多个段。HTML有链接锚点（其实这里真是多余的，ASH报告内容很少，根本就不着那些东西，但是如果ORACLE在以后向这个报告中增加内容的话或许就用得到了）。报表的内容包含ASH信息，它们被用于确定阻塞者和等待者以及与他们相关联的事务描述符、及那段时间内的sql。
应该主要使用OEM，不能使用时使用下面的脚本：
- Generating an ASH Report 产生ASH报表
- Generating an ASH Report on a Specific Database Instance 在某一实例上产生ASH报表
- Generating an Oracle RAC ASH Report 在RAC上产生ASH报表
5.3.8.1 Generating an ASH Report
The ashrpt.sql SQL script generates an HTML or text report that displays ASH information for a specified duration.
To generate an ASH report:
- At the SQL prompt, enter:
@$ORACLE_HOME/rdbms/admin/ashrpt.sql
- Specify whether you want an HTML or a text report:
Enter value for report_type: 【text】
In this example, a text report is chosen.
- Specify the begin time in minutes before the system date:
Enter value for begin_time: 【-10】 从当前时间往前推10分钟
In this example, 10 minutes before the current time is selected.
Enter the duration in minutes that the report for which you want to capture ASH information from the begin time.
Enter value for duration:
In this example, the default duration of system date minus begin time is accepted.
Enter a report name, or accept the default report name:
Enter value for report_name:
Using the report name ashrpt_1_0310_0131.txt
In this example, the default name is accepted and an ASH report named ashrpt_1_0310_0131 is generated. The report will gather ASH information beginning from 10 minutes before the current time and ending at the current time.
5.3.8.2 Generating an ASH Report on a Specific Database Instance
The ashrpti.sql SQL script generates an HTML or text report that displays ASH information for a specified duration for a specified database and instance. This script enables you to specify a database and instance before setting the time frame to collect ASH information.
To generate an ASH report on a specified database instance:
- At the SQL prompt, enter:
@$ORACLE_HOME/rdbms/admin/ashrpti.sql
- Specify whether you want an HTML or a text report:
3. Enter value for report_type: 【html】
In this example, an HTML report is chosen.
A list of available database IDs and instance numbers are displayed:
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
----------- -------- ------------ ------------ ------------
3309173529 1 MAIN main examp1690
3309173529 1 TINT251 tint251 samp251
Enter the values for the database identifier (dbid) and instance number (inst_num):
Enter value for dbid: 【3309173529】
Using 3309173529 for database id
Enter value for inst_num: 【1】
- This step is applicable only if you are generating an ASH report on an Active Data Guard physical standby instance. If this is not the case, you may skip this step.
To generate an ASH report on a physical standby instance, the standby database must be opened read-only. The ASH data on disk represents activity on the primary database and the ASH data in memory represents activity on the standby database.
Specify whether to generate the report using data sampled from the primary or standby database:
You are running ASH report on a Standby database.
To generate the report over data sampled on the Primary database, enter 'P'.
Defaults to 'S' - data sampled in the Standby database.
Enter value for stdbyflag:
Using Primary (P) or Standby (S): S
In this example, the default value of Standby (S) is selected.
- Specify the begin time in minutes before the system date:
Enter value for begin_time: 【-10】
In this example, 10 minutes before the current time is selected.
Enter the duration in minutes that the report for which you want to capture ASH information from the begin time.
Enter value for duration:
In this example, the default duration of system date minus begin time is accepted.
- Specify the slot width in seconds that will be used in the Activity Over Time section of the report:
Enter value for slot_width:
In this example, the default value is accepted.
- Follow the instructions as explained in the subsequent prompts and enter values for the following report targets:
- target_session_id
- target_sql_id
- target_wait_class
- target_service_hash
- target_module_name
- target_action_name
- target_client_id
- target_plsql_entry
Enter a report name, or accept the default report name:
Enter value for report_name:
Using the report name ashrpt_1_0310_0131.txt
In this example, the default name is accepted and an ASH report named ashrpt_1_0310_0131 is generated. The report will gather ASH information on the database instance with a database ID value of 3309173529 beginning from 10 minutes before the current time and ending at the current time.
5.3.8.3 Generating an Oracle RAC ASH Report
The ashrpti.sql SQL script generates an HTML or text report that displays ASH information for a specified duration for specified databases and instances in an Oracle RAC environment. Only ASH data that is written to disk will be used to generate the report. This report will only use ASH samples from the last 10 minutes that are found in the DBA_HIST_ACTIVE_SESS_HISTORY table.
To generate an ASH report in an Oracle RAC environment:
- At the SQL prompt, enter:
@$ORACLE_HOME/rdbms/admin/ashrpti.sql 【在集群中使用这个来生成，在awr中，集群是在中间加一个g，代表grid，这个？？】
- Specify whether you want an HTML or a text report:
3. Enter value for report_type: 【html】
In this example, an HTML report is chosen.
A list of available database IDs and instance numbers are displayed:
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
----------- -------- ------------ ------------ ------------
3309173529 1 MAIN main examp1690
3309173529 1 TINT251 tint251 samp251
3309173529 2 TINT251 tint252 samp252
3309173529 3 TINT251 tint253 samp253
3309173529 4 TINT251 tint254 samp254
Enter the values for the database identifier (dbid) and instance number (inst_num):
Enter value for dbid: 【3309173529】
Using database id: 3309173529
Enter instance numbers. Enter 'ALL' for all instances in an Oracle
RAC cluster or explicitly specify list of instances (e.g., 1,2,3).
Defaults to current instance.
Enter value for inst_num: 【ALL】
Using instance number(s): 【ALL】
- Specify the begin time in minutes before the system date:
Enter value for begin_time: 【-1:10】
In this example, 1 hour and 10 minutes before the current time is selected.
Enter the duration in minutes that the report for which you want to capture ASH information from the begin time:
Enter value for duration: 【10】
In this example, the duration is set to 10 minutes.
- Specify the slot width in seconds that will be used in the Activity Over Time section of the report:
Enter value for slot_width:
In this example, the default value is accepted. For more information about the Activity Over Time section and how to specify the slot width, see "Activity Over Time".
- Follow the instructions as explained in the subsequent prompts and enter values for the following report targets:
- target_session_id
- target_sql_id
- target_wait_class
- target_service_hash
- target_module_name
- target_action_name
- target_client_id
- target_plsql_entry
Enter a report name, or accept the default report name:
Enter value for report_name:
Using the report name ashrpt_rac_0310_0131.txt
In this example, the default name is accepted and an ASH report named ashrpt_rac_0310_0131 is generated. The report will gather ASH information on all instances belonging to the database with a database ID value of 3309173529 beginning from 1 hour and 10 minutes before the current time and ending at 1 hour before the current time.
5.3.9 Using Active Session History Reports
After generating an ASH report, you can review the contents to identify transient performance problems.
The contents of the ASH report are divided into the following sections:
在你产生了ASH报告后，你就可以审查和确定那些短暂的性能问题。
ASH报表的内容被细分成以下几部分：
oracle的这几个顺序非常有讲究，oracle这东西做的真他娘的好啊，为我们DBA想的周到啊！通常情况下看前几个就大概知道是怎么回事了，然后直接跳到最后一个，看看那两个参数是怎么，如果与前面对的上的话就说明真是那一个或几个有问题。这就叫做专业，这就叫做Professional!
- Top Events 先检查有哪些等待事件发生
- Load Profile 再检查是哪些service,client,sql原因
- Top SQL 再详细的查看SQL（可以获取SQL_ID，可以从v$sql v$sql_plan查看相关信息）
- Top PL/SQL 再详细的查看PL/SQL
- Top Java 再详细的查看JAVA（这个基本上没人会用）
- Top Sessions 再详细的查看会话情况（可以获取SID,SERIAL#,MODULE，ACTION之类信息）
- Top Objects/Files/Latches
- Activity Over Time
下图是我从自己虚拟机上截取的ASH报表图，以供参考！因为要模拟有问题，所以我建立了一个ts01的表空间，空间大小是20M，然后不断的往里面插入内容，直到没有空间为止，同时设置: alter system set resumable_timeout=300;这样我有时间去生成这个ASH报告。当然模拟一个很大的查询，或者大表的update都可以去测试的，insert是最差的测试方法了。但是“插入”是很有内含的，所以我喜欢！
当时会话的SID,SERIAL#是154，849。当时执行的语句是：insert into ts0101 select * from ts0101
在不断的执行insert into ts0101 select * from ts0101时报错了，这是在执行完ash报告之后才报错的，我的resumable_timeout设置了5分钟时间
最后是alert日志中的信息：
The Top Events section describes the top wait events of the sampled session activity categorized by user, background, and priority. Use the information in this section to identify the wait events that may be the cause of the transient performance problem.
这个top events段描述了最多的按用户，后台，和优级先采样会话活跃的等待事件。使用这段信息去确定这个等待事件是否为短暂的性能问题原因。
The Top Events section contains the following subsections:
- Top User Events
This subsection lists the top wait events from user processes that accounted for the highest percentages of sampled session activity.
这段列出了最高的由用户进程的等待事件，它们占了采样会话活动的多数情况。
图中我们发现：statement suspended,wait error to be cleared占了最高。同时我们可以看到这种resumable的等待事件是属于Configuration事件类的
- Top Background Events
This subsection lists the top wait events from backgrounds that accounted for the highest percentages of sampled session activity.
- Top Event P1/P2/P3
This subsection lists the wait event parameter values of the top wait events that accounted for the highest percentages of sampled session activity, ordered by the percentage of total wait time (% Event). For each wait event, values in the P1 Value, P2 Value, P3 Value column correspond to wait event parameters displayed in the Parameter 1, Parameter 2, and Parameter 3 columns.
这部分列出了等待事件参数值，按照总的等待时间的比率排序。对于每个等待事件，在p1,p2,p3值列，对应着等待参数在Parameter 1, Parameter 2, and Parameter 3三列。
如control file sequential read第一行，0,1,1 对应的含义是file#, block#,blocks
The Load Profile section describes the load analyzed in the sampled session activity. Use the information in this section to identify the service, client, or SQL command type that may be the cause of the transient performance problem.
这个压力profile段描述了采样中的压力分析结果。使用这段去确定：service,client,sql是否是造成短暂性能问题的原因。
The Load Profile section contains the following subsections:
- Top Service/Module
This subsection lists the services and modules that accounted for the highest percentages of sampled session activity.
- Top Client IDs
This subsection lists the clients that accounted for the highest percentages of sampled session activity based on their client ID, which is the application-specific identifier of the database session.
- Top SQL Command Types
This subsection lists the SQL command types, such as SELECT or UPDATE, that accounted for the highest percentages of sampled session activity.
- Top Phases of Execution
This subsection lists the phases of execution, such as SQL, PL/SQL, and Java compilation and execution, that accounted for the highest percentages of sampled session activity.
The Top SQL section describes the top SQL statements of the sampled session activity. Use this information to identify high-load SQL statements that may be the cause of the transient performance problem.
The Top SQL section contains the following subsections:
- Top SQL with Top Events
- Top SQL with Top Row Sources
- Top SQL Using Literals
- Top Parsing Module/Action
- Complete List of SQL Text
5.3.9.3.1 Top SQL with Top Events
The Top SQL with Top Events subsection lists the SQL statements that accounted for the highest percentages of sampled session activity and the top wait events that were encountered by these SQL statements. The “Sampled # of Executions” column shows how many distinct executions of a particular SQL statement were sampled.
5.3.9.3.2 Top SQL with Top Row Sources
The Top SQL with Top Row Sources subsection lists the SQL statements that accounted for the highest percentages of sampled session activity and their detailed execution plan information. You can use this information to identify which part of the SQL execution contributed significantly to the SQL elapsed time.
5.3.9.3.3 Top SQL Using Literals
The Top SQL Using Literals subsection lists the SQL statements using literals that accounted for the highest percentages of sampled session activity. You should review the statements listed in this report to determine whether the literals can be replaced with bind variables.
这段列出未使用绑定变量的SQL。
5.3.9.3.4 Top Parsing Module/Action
The Top Parsing Module/Action subsection lists the module and action that accounted for the highest percentages of sampled session activity while parsing the SQL statement.
因为我这个报告是用OEM来生成的，里面有许多是OEM的资源消耗，因此在生产上能不使用OEM就不要使用，如果想使用最好使用GC来代替！
5.3.9.3.5 Complete List of SQL Text
The Complete List of SQL Text subsection displays the entire text of the Top SQL statements shown in this section.
The Top PL/SQL section lists the PL/SQL procedures that accounted for the highest percentages of sampled session activity.
The “PL/SQL Entry Subprogram” column lists the application's top-level entry point into PL/SQL.
The “PL/SQL Current Subprogram” column lists the PL/SQL subprogram being executed at the point of sampling. If the value of this column is SQL, then the % Current column shows the percentage of time spent executing SQL for this subprogram.
The Top Java section describes the top Java programs in the sampled session activity.
The Top Sessions section describes the sessions that were waiting for a particular wait event. Use this information to identify the sessions that accounted for the highest percentages of sampled session activity, which may be the cause of the transient performance problem.
The Top Sessions section contains the following subsections:
- Top Sessions
- Top Blocking Sessions
- Top Sessions Running PQs
The Top Session subsection lists the sessions that were waiting for a particular wait event that accounted for the highest percentages of sampled session activity.
这个top session子段列出正在等待某些事件的会话，这些等待事件占了采样会话活动的大部分
从图中可以看到，statement suspended,wait error to be cleared的事件，而且给出了sid,serial#，这样可以去v$session中查看
5.3.9.6.2 Top Blocking Sessions
The Top Blocking Sessions subsection lists the blocking sessions that accounted for the highest percentages of sampled session activity.
5.3.9.6.3 Top Sessions Running PQs
The Top Sessions Running PQs subsection lists the sessions running parallel queries (PQs) that were waiting for a particular wait event, which accounted for the highest percentages of sampled session activity.
5.3.9.7 Top Objects/Files/Latches
The Top Objects/Files/Latches section provides additional information about the most commonly-used database resources and contains the following subsections:
- Top DB Objects
- Top DB Files
- Top Latches
The Top DB Objects subsection lists the database objects (such as tables and indexes) that accounted for the highest percentages of sampled session activity.
The Top DB Files subsection lists the database files that accounted for the highest percentages of sampled session activity.
The Top Latches subsection lists the latches that accounted for the highest percentages of sampled session activity.
Latches are simple, low-level serialization mechanisms to protect shared data structures in the System Global Area (SGA). For example, latches protect the list of users currently accessing the database and the data structures describing the blocks in the buffer cache. A server or background process acquires a latch for a very short time while manipulating or looking at one of these structures. The implementation of latches is operating system-dependent, particularly regarding whether and how long a process waits for a latch.
我一般把latch理解成UNIX的信号灯，用于控制串行化访问共享内存（oracle11g使用mmap技术，一样可以是共享内存的一种实现）
The Activity Over Time section is one of the most informative sections of the ASH report. This section is particularly useful for longer time periods because it provides in-depth details about activities and workload profiles during the analysis period. The Activity Over Time section is divided into 10 time slots. The size of each time slot varies based on the duration of the analysis period. The first and last slots are usually odd-sized. All inner slots are equally sized and can be compared to each other. For example, if the analysis period lasts for 10 minutes, then all time slots will 1 minute each. However, if the analysis period lasts for 9 minutes and 30 seconds, then the outer slots may be 15 seconds each and the inner slots will be 1 minute each.
这段是ASH报告中最重要的一部分。这节对于比较长的时间阶段非常有用，因为它提供了更深入的活跃性与压力剖面的细节。它被分成10个时间槽。时间槽的大小由分析的时间段决定（如果分析10分钟，那每个槽就是1分钟；如果是分钟20分钟，那就是每个2分钟）。第一个和最后一个通常是奇数。所有其他的8个都是相等大小的，可以进行相互比较的。例如你要分析最后10分钟的，那么所有的槽都是1分钟。然而你要分析9分半，那么前面都是1分钟，最后是15秒（疯子才会这样做）
Each of the time slots contains information regarding that particular time slot, as described in Table 5-2.
| Column | Description |
| Slot Time (Duration) | Duration of the slot |
| Slot Count | Number of sampled sessions in the slot |
| Event | Top three wait events in the slot |
| Event Count | Number of ASH samples waiting for the wait event |
| % Event | Percentage of ASH samples waiting for wait events in the entire analysis period |
When comparing the inner slots, perform a skew analysis by identifying spikes in the Event Count and Slot Count columns.
A spike in the Event Count column indicates an increase in the number of sampled sessions waiting for a particular event. Event Count大说明会有问题！
A spike in the Slot Count column indicates an increase in active sessions, because ASH data is sampled from active sessions only and a relative increase in database workload.
Slot Count大说明活跃会话多
Typically, when the number of active session samples and the number of sessions associated with a wait event increases, the slot may be the cause of the transient performance problem.
通常情况下看那两个参数值就大致了解事务的性能问题了！
To generate the ASH report with a user-defined slot size, run the ashrpti.sql script.想要私人订制一个自己想的大小槽，运行ashrpti.sql吧！
此篇介绍，oracle对AWR介绍的还不够，ASH倒是差不多了
所以虽然AWR的介绍已经完成，未来我会在后面再加一篇详解AWR的文章！
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/1696240/viewspace-1179239/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/1696240/viewspace-1179239/
168
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
