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 .

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日誌中的資訊：

5.3.9.1 Top Events

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

5.3.9.2 Load Profile

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.

5.3.9.3 Top SQL

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.

5.3.9.4 Top PL/SQL

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.

5.3.9.5 Top Java

The Top Java section describes the top Java programs in the sampled session activity.

5.3.9.6 Top Sessions

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

5.3.9.6.1 Top Sessions

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

5.3.9.7.1 Top DB Objects

The Top DB Objects subsection lists the database objects (such as tables and indexes) that accounted for the highest percentages of sampled session activity.

5.3.9.7.2 Top DB Files

The Top DB Files subsection lists the database files that accounted for the highest percentages of sampled session activity.

5.3.9.7.3 Top Latches

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技術，一樣可以是共享記憶體的一種實現）

5.3.9.8 Activity Over Time

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.

Table 5-2 Activity Over Time

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的文章！

Oracle Performance Tuning 11g2 (5-3)

相關文章