Oracle Performance Tuning 11g2 (6)

6 Automatic Performance Diagnostics

This chapter describes Oracle Database automatic features for performance diagnosing and tuning.

這章描述oracle自動效能診斷和調優特性（其實也就是ADDM了），包括如下幾點：

This chapter contains the following topics:

•        ADDM概要

•                                              設定ADDM

•           使用ADDM進行診斷

•                                  顯示ADDM診斷資訊

6.1 Overview of the Automatic Database Diagnostic Monitor

When problems occur with a system, it is important to perform accurate and timely diagnosis of the problem before making any changes to a system. Oftentimes, a database administrator (DBA) simply looks at the symptoms and immediately starts changing the system to fix those symptoms. However, an accurate diagnosis of the actual problem in the initial stage significantly increases the probability of success in resolving the problem.

With Oracle Database, the statistical data needed for accurate diagnosis of a problem is stored in the Automatic Workload Repository (AWR). The Automatic Database Diagnostic Monitor (ADDM):

當系統發生問題時，在做出系統改變時，迅速而準確的確定問題所在是非常重要的。很多時候，DBA簡單的看一下症狀就立即開始修復這些症狀。然而從一開始就準確地診斷實際的問題所在會增加解決問題的成功率。

在oracle資料庫中，診斷問題需要的統計資料是儲存在AWR中的。自動資料庫診斷監控（ADDM）：

• Analyzes the AWR data on a regular basis               常規性的對AWR資料進行分析

• Diagnoses the root causes of performance problems      診斷效能問題的根本原因

• Provides recommendations for correcting any problems   提供解決問題的建議

• Identifies non-problem areas of the system             確定非問題區

Because AWR is a repository of historical performance data, ADDM can analyze performance issues after the event, often saving time and resources in reproducing a problem.

因為AWR是一個歷史效能資料的倉庫，ADDM可以從事件一發生就開始分析效能問題，這樣就節約時間和資源去重新產生這個問題（不需要去重新跑資料以產生這個問題或者進行資料重演了）。

 

In most cases, ADDM output should be the first place that a DBA looks when notified of a performance problem. ADDM provides the following benefits:

多數情況下，當發現一個效能問題時，DBA應該首先去看看ADDM。ADDM包含了以下的好處：

• Automatic performance diagnostic report every hour by default              【預設情況下每小時產生一次效能診斷的報告】

• Problem diagnosis based on decades of tuning expertise                     【基於10多年的調優經驗去診斷問題，即ADDM是非常聰明的】

• Time-based quantification of problem impacts and recommendation benefits   【問題影響及建議益處是基於量化的時間】

• Identification of root cause, not symptoms                                 【確定根本的原因，而不僅僅是表象】

• Recommendations for treating the root causes of problems                   【提供解決這些根本原因的建議】

• Identification of non-problem areas of the system                          【確定系統中的非問題區域】

• Minimal overhead to the system during the diagnostic process               【在診斷過程中不對系統造成大的影響，僅僅以小的負荷執行】

It is important to realize that tuning is an iterative process, and fixing one problem can cause the bottleneck to shift to another part of the system. Even with the benefit of ADDM analysis, it can take multiple tuning cycles to reach acceptable system performance. ADDM benefits apply beyond production systems; on development and test systems, ADDM can provide an early warning of performance issues.

請注意調優是一個迭代的過程，修復一個問題可能將瓶頸又轉移到其他部分。甚至根據ADDM的分析，也需要多次的調優才能達到可接受的系統效能。ADDM的好處不僅僅是在生產系統；在開發和測試環境中,ADDM也能儘早的發現問題。

This section contains the following topics:

6.1.1 ADDM Analysis

An ADDM analysis can be performed on a pair of AWR snapshots and a set of instances from the same database. The pair of AWR snapshots define the time period for analysis, and the set of instances define the target for analysis.

ADDM分析可以執行在一對AWR快照上，以及一系列相同資料庫的例項上（RAC是同庫多例項的）。一對AWR快照定義了分析的時間週期，例項集定義了分析要分析的目標物。

If you are using Oracle Real Application Clusters (Oracle RAC), ADDM has three analysis modes: 假如你使用RAC的話，ADDM有3個分析模式：

• Database   資料庫

In Database mode, ADDM analyzes all instances of the database.           在資料庫層面上，ADDM分析所有的例項

• Instance   例項

In Instance mode, ADDM analyzes a particular instance of the database.   在例項層面上，ADDM分析資料庫中特定的例項

• Partial    部分

In Partial mode, ADDM analyzes a subset of all database instances.       在這種層面上，ADDM分析資料庫所有例項的一部分，即個別例項

If you are not using Oracle RAC, ADDM can only function in Instance mode because there is only one instance of the database.

An ADDM analysis is performed each time an AWR snapshot is taken and the results are saved in the database. The time period analyzed by ADDM is defined by the last two snapshots (the last hour by default). ADDM will always analyze the specified instance in Instance mode. For non-Oracle RAC or single instance environments, the analysis performed in the Instance mode is the same as a database-wide analysis. If you are using Oracle RAC, ADDM will also analyze the entire database in Database mode. After an ADDM completes its analysis, you can view the results using Oracle Enterprise Manager, or by viewing a report in a SQL*Plus session.

ADDM analysis is performed top down, first identifying symptoms, and then refining them to reach the root causes of performance problems. The goal of the analysis is to reduce a single throughput metric called DB time. DB time is the cumulative time spent by the database in processing user requests. It includes wait time and CPU time of all non-idle user sessions. DB time is displayed in the V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views.

By reducing DB time, the database is able to support more user requests using the same resources, which increases throughput. The problems reported by ADDM are sorted by the amount of DB time they are responsible for. System areas that are not responsible for a significant portion of DB time are reported as non-problem areas.

假如你沒有使用RAC, ADDM只能工作在例項模式下，因為只有一個例項可以使用。

ADDM每次分析一個AWR快照，並且把結果儲存到資料庫中。ADDM分析的時間週期被定義成過去的2個快照（也就是過去的1小時了，這是預設的）。ADDM總是在例項模式下分析指定的例項。對於非RAC或者單例項環境下，在例項模式下的分析與資料庫級別分析是一樣的。假如你使用RAC，ADDM將會在資料庫模式下分析整個資料庫。ADDM分析完之後，你就可以使用OEM去看結果了，或者使用SQL*PLUS去檢視。

ADDM是從上到下進行分析的，首先確定症狀，然後提煉症狀，以找出系統效能的根本原因。分析的目標是減少單個吞吐量度量值（DB TIME）。db time是資料庫花費在使用者請求上的累積時間。它包括了等待時間和非空閒的使用者會話佔用的CPU時間。這個時間儲存在： v$sess_time_model , v$sys_time_model 檢視中。

透過減少DB TIME， 資料庫將能夠在現有的資源上支援更多的使用者請求，這樣就增加了吞吐量。ADDM報告的內容是按照DB TIME佔用的多少進行排序的。未造成顯著DB TIME的系統區問題，將被放到非問題區中展示。

 

The types of problems that ADDM considers include the following:  ADDM在分析時會考慮以下的問題：

注：

因為ADDM畢竟是程式，只要是程式，那就一定是按照某個規則去運算的，那ADDM是怎樣去發現問題的呢？

首先它先去看DB TIME佔比有多大，如果比較大就說明有問題；這個很顯然如果空閒的話就不用去分析了（但是我覺得既然空閒更應該去看看發生了什麼，看看還有沒有什麼可以提升的）

接下來按什麼規則或者按什麼條件去分析呢？下面就是它評估系統是否有問題的幾個方面，如果這幾類有問題它就開始統計了

• CPU bottlenecks - Is the system CPU bound by Oracle Database or some other application?       

CPU的瓶頸 – 是資料庫造成CPU高還是其他程式？

• Undersized Memory Structures - Are the Oracle Database memory structures, such as the SGA, PGA, and buffer cache, adequately sized?    

記憶體太小 – 是否資料庫的記憶體結構如SGA,PGA，buffer cache不夠？

• I/O capacity issues - Is the I/O subsystem performing as expected?   

IO容量問題 – 是否IO子系統按期望的執行？（DBIO_EXPECTED）

• High load SQL statements - Are there any SQL statements which are consuming excessive system resources? 

SQL高負荷 – 是否有SQL消耗大量系統資源？

• High load PL/SQL execution and compilation, and high-load Java usage   

PL/SQL,編譯,JAVA造成高負荷

• Oracle RAC specific issues - What are the global cache hot blocks and objects; are there any interconnect latency issues?

RAC特殊問題 – 什麼是全域性的快取熱塊和物件；是不是有什麼內部連線延遲問題？

• Sub-optimal use of Oracle Database by the application - Are there problems with poor connection management, excessive parsing, or application level lock contention?

應用程式不合理的使用資料庫 – 是否有比較差的資料庫連線問題，過多的解析，或者應用級別的鎖競爭問題？

• Database configuration issues - Is there evidence of incorrect sizing of log files, archiving issues, excessive checkpoints, or sub-optimal parameter settings?

資料庫配置問題 – 是否有證據表明不正確的日誌檔案大小，歸檔問題，過多的checkpoints,或者未最優的引數設定？

• Concurrency issues - Are there buffer busy problems?  

併發問題 – 是否有buffer busy問題？

• Hot objects and top SQL for various problem areas     

各種問題區的熱物件和top sql

Note:   注意，這不是一個ADDM要考慮和分析的所有問題的綜合列表

This is not a comprehensive list of all problem types that ADDM considers in its analysis.

ADDM also documents the non-problem areas of the system. For example, wait event classes that are not significantly impacting the performance of the system are identified and removed from the tuning consideration at an early stage, saving time and effort that would be spent on items that do not impact overall system performance.

ADDM同樣會記錄系統的非問題區域。比如，未明顯影響系統的等待事件類被確認後，就從早期的調優策略中移除掉，這樣就節約了時間和精力去做沒有太多幫助的效能最佳化（不要把精力放在沒有太大意義的最佳化上）。

     如果你看到了這裡，說明還真是有耐心，那麼請直接跳到最後去看一下實驗，然後再回到這裡往下看。看完實驗後看下面就非常容易了！

6.1.2 Using ADDM with Oracle Real Application Clusters

If you are using Oracle RAC, you can run ADDM in Database analysis mode to analyze the throughput performance of all instances of the database. In Database mode, ADDM considers DB time as the sum of the database time for all database instances. Using the Database analysis mode enables you to view all findings that are significant to the entire database in a single report, instead of reviewing a separate report for each instance.

The Database mode report includes findings about database resources (such as I/O and interconnect). The report also aggregates findings from the various instances if they are significant to the entire database. For example, if the CPU load on a single instance is high enough to affect the entire database, the finding will appear in the Database mode analysis, which will point to the particular instance responsible for the problem.

假如你使用了RAC， 你可以在資料庫分析模式下執行ADDM,以便分析出整個資料庫的所有例項的吞吐量效能問題。在資料庫模式下，ADDM將把所有例項的DB TIME作為一個整體進行統計。使用資料庫分析模式將使你能夠在單一的報表中，看到影響整個資料庫的“發現”（findings,可以理解成發現的問題），而不是每個例項的一部分內容。

資料庫模式報告包括了資料庫資源的findings，例如IO和互聯。報表同時會將影響整個資料庫的findings彙總起來展示。例如，假如一個單例項上的CPU負荷非常的高，影響到了整個資料庫，那麼這個問題就會出現在資料庫模式的分析中，它將報告出這個特殊例項的問題原因。

 

6.1.3 ADDM Analysis Results

In addition to problem diagnostics, ADDM recommends possible solutions. ADDM analysis results are represented as a set of findings. See Example 6-1 for an example of ADDM analysis result. Each ADDM finding can belong to one of the following types:

除了診斷問題，ADDM會提出一些可能的解決建議。ADDM的分析結果透過一系列的findings一起展示出來。每個ADDM finding可能包含以下幾個型別之一：

• Problem findings     describe the root cause of a database performance problem. 

“問題發現”描述了影響效能的根本原因；即描述資訊

• Symptom findings     contain information that often lead to one or more problem findings. 

“症狀發現”包含導致一個或多問題的相關資訊

• Information findings are used for reporting information that are relevant to understanding the performance of the database, but do not constitute a performance problem (such as non-problem areas of the database and the activity of automatic database maintenance).

“資訊發現”用於報告有助於理解的相關的資料庫效能的報告資訊，但是它們沒有構成一個效能問題（比如一個非問題區和自動資料庫維護的活動）

• Warning findings     contain information about problems that may affect the completeness or accuracy of the ADDM analysis (such as missing data in the AWR).

“警告發現”包含那些影響ADDM分析的完整性與準確性的資訊（例如AWR資料中少一些資料）

Each problem finding is quantified by an impact that is an estimate of the portion of DB time caused by the finding's performance issue. A problem finding can be associated with a list of recommendations for reducing the impact of the performance problem. The types of recommendations include:

每個問題都是按照佔用DB TIME的部分進行量化統計的。每個“問題發現”都有一系列的建議以降低這個效能問題，建議包括瞭如下：

• Hardware changes:        adding CPUs or changing the I/O subsystem configuration

硬體資源不夠，需要增加CPU或者IO

• Database configuration: changing initialization parameter settings

系統配置有問題

• Schema changes:          hash partitioning a table or index, or using automatic segment-space management (ASSM)

使用hash去將表或索引進行分割槽，或者使用ASSM

• Application changes:    using the cache option for sequences or using bind variables

將sequence進行一定的快取，或者使用繫結變數

• Using other advisors:   running SQL Tuning Advisor on high-load SQL or running the Segment Advisor on hot objects

使用sql tuning advisor去分析一下TOP SQL或者使用segment advisor分析一下熱物件

A list of recommendations can contain various alternatives for solving the same problem; you do not have to apply all the recommendations to solve a specific problem. Each recommendation has a benefit which is an estimate of the portion of DB time that can be saved if the recommendation is implemented. Recommendations are composed of actions and . You must apply all the actions of a recommendation to gain the estimated benefit. The rationales are used for explaining why the set of actions were recommended and to provide additional information to implement the suggested recommendation.

一系列的建議可能包含各種不同的解決相同問題的方式；你不一定要使用所有的建議去解決一個特定的問題。每種建議都有一個可以節約db time的好處。建議是由actioon和rationale(原理)組成的。你必須應用所有的action以達到評估的那種益處。rationale被用作解釋為什麼這一系列的action被建議出來，並且提供額外的資訊去實現建議。

 

6.1.4 Reviewing ADDM Analysis Results: Example

Consider the following section of an ADDM report in Example 6-1.

Example 6-1 Example ADDM Report

FINDING 1: 31% impact (7798 seconds)

------------------------------------

SQL statements were not shared due to the usage of literals. This resulted in

additional hard parses which were consuming significant database time.

 

RECOMMENDATION 1: Application Analysis, 31% benefit (7798 seconds)

  ACTION: Investigate application logic for possible use of bind variables

    instead of literals. Alternatively, you may set the parameter

    "cursor_sharing" to "force".

  RATIONALE: SQL statements with PLAN_HASH_VALUE 3106087033 were found to be

    using literals. Look in V$SQL for examples of such SQL statements.

In Example 6-1, the finding points to a particular root cause, the usage of literals in SQL statements, which is estimated to have an impact of about 31% of total DB time in the analysis period.

The finding has a recommendation associated with it, composed of one action and one rationale. The action specifies a solution to the problem found and is estimated to have a maximum benefit of up to 31% DB time in the analysis period. Note that the benefit is given as a portion of the total DB time and not as a portion of the finding's impact. The rationale provides additional information on tracking potential SQL statements that were using literals and causing this performance issue. Using the specified plan hash value of SQL statements that could be a problem, a DBA could quickly examine a few sample statements.

When a specific problem has multiple causes, the ADDM may report multiple problem and symptom findings. In this case, the impacts of these multiple findings can contain the same portion of DB time. Because the performance issues of findings can overlap, the sum of the impacts of the findings can exceed 100% of DB time. For example, if a system performs many reads, then ADDM might report a SQL statement responsible for 50% of DB time due to I/O activity as one finding, and an undersized buffer cache responsible for 75% of DB time as another finding.

When multiple recommendations are associated with a problem finding, the recommendations may contain alternatives for solving the problem. In this case, the sum of the recommendations' benefits may be higher than the finding's impact.

When appropriate, an ADDM action may have multiple solutions for you to choose from. In the example, the most effective solution is to use bind variables. However, it is often difficult to modify the application. Changing the value of the CURSOR_SHARING initialization parameter is much easier to implement and can provide significant improvement.

這這個例子中，finding指向一個特別的根本性原因------就是在SQL語句中使用字面值------這樣在這個分析週期裡評估下來，對DB TIME有31%的影響。

這個finding有一個由action和rationale組成的建議。action指定了一個解決問題的方法，以及在分析週期裡評估有31%的DB TIME提升。注意，這個benefit是指總的DB TIME中所佔的部分，而不是所有findings影響中佔據的部分。rationale在跟蹤潛在的，使用字面值以及造成效能問題的SQL語句上提供額外的資訊。使用這個SQL的plan hash value，DBA可以快速的檢查一些取樣的語句。

當一個特有的問題有許多的原因，ADDM可能報告多個問題和症狀findings。 在這種情況下，這些findings的影響可能包含相同的DB TIME佔比。因為這些finding效能問題可能重疊，總的finding影響可能會超過100%的DB TIME。例如，一個系統執行許多讀操作，那麼ADDM可能報告一個SQL語句作為一個finding因使用IO活躍性佔用50%的DB TIME，並且另一個finding因為buffer cache不足佔用75%的DB TIME。

當多個建議關聯到一個finding問題上，建議就包含了不同的解決問題的辦法。在這種情況下，總的建議的好處可能高於finding的影響。

當可能的話，addm action可能有多個解決方案。在這個例子中，是有效的辦法是使用繫結變數。然而，有時去修改應用程式是非常困難的。改變cursor_sharing這個引數則是非常容易的，並且能夠提供足夠的提升。

 

6.2 Setting Up ADDM

Automatic database diagnostic monitoring is enabled by default and is controlled by the CONTROL_MANAGEMENT_PACK_ACCESS and the STATISTICS_LEVEL initialization parameters.

The CONTROL_MANAGEMENT_PACK_ACCESS parameter should be set to DIAGNOSTIC or DIAGNOSTIC+TUNING to enable automatic database diagnostic monitoring. The default setting is DIAGNOSTIC+TUNING. Setting CONTROL_MANAGEMENT_PACK_ACCESS to NONE disables ADDM.

The STATISTICS_LEVEL parameter should be set to the TYPICAL or ALL to enable automatic database diagnostic monitoring. The default setting is TYPICAL. Setting STATISTICS_LEVEL to BASIC disables many Oracle Database features, including ADDM, and is strongly discouraged.

ADDM analysis of I/O performance partially depends on a single argument, DBIO_EXPECTED, that describes the expected performance of the I/O subsystem. The value of DBIO_EXPECTED is the average time it takes to read a single database block in microseconds. Oracle Database uses the default value of 10 milliseconds, which is an appropriate value for most modern hard drives. If your hardware is significantly different, such as very old hardware or very fast RAM disks, consider using a different value.

ADDM預設是開啟的，它是由control_management_pack_access和statistics_level引數決定的

control_management_pack_access引數應該設定成diagnostic或者diagnostic+tunning(預設值)以便開啟ADDM功能。將值設定成NONE將禁用ADDM。

statistics_level引數應該設定成typical或者all以便開啟ADDM。預設值是TYPICAL。設定成BASIC將取消許多資料庫特性，包括ADDM，因此是不建議取消的。

ADDM關於IO效能的分析部分地依賴於一個變數：DBIO_EXPECTED，它是期望的IO子系統效能。這個值是讀取一個資料庫塊所用的微秒數。oracle資料庫的預設值是10毫秒，這個值對於現代的系統而言是比較合適的。假如你的系統非常的不同，例如使用了非常舊的硬體或者非常快的記憶體磁碟，考慮使用不同的值。（在我自己的SSD硬碟上，隨機讀取8K大概是0.2毫秒左右，也就是200微秒；當然在虛擬機器上又是非常的慢）

To determine the correct setting for DBIO_EXPECTED parameter:   判斷DBIO_EXPECTED引數的正確設定：

1. Measure the average read time of a single database block read for your hardware. Note that this measurement is for random I/O, which includes seek time if you use standard hard drives. Typical values for hard drives are between 5000 and 20000 microseconds.    測量系統中單資料庫塊讀的響應時間（單塊一般是8K）。注意這個測量是要隨機的IO讀取，包括了尋道時間。通常這個值是5000到20000微秒（5毫秒到20毫秒）。

2. Set the value one time for all subsequent ADDM executions. For example, if the measured value if 8000 microseconds, you should execute the following command as SYS user:    一次性的設定DBIO_EXPETED用於所有的後續ADDM執行。比如，假如這個測量值是8000微秒（8毫秒），你應該以SYS使用者的身份去執行

EXECUTE DBMS_ADVISOR.SET_DEFAULT_TASK_PARAMETER('ADDM', 'DBIO_EXPECTED', 8000);

 

6.3 Diagnosing Database Performance Problems with ADDM

To diagnose database performance problems, first review the ADDM analysis results that are automatically created each time an AWR snapshot is taken. If a different analysis is required (such as a longer analysis period, using a different DBIO_EXPECTED setting, or changing the analysis mode), you can run ADDM manually as described in this section.

ADDM can analyze any two AWR snapshots (on the same database), as long as both snapshots are still stored in the AWR (have not been purged). ADDM can only analyze instances that are started before the beginning snapshot and remain running until the ending snapshot. Additionally, ADDM will not analyze instances that experience significant errors when generating the AWR snapshots. In such cases, ADDM will analyze the largest subset of instances that did not experience these problems.

The primary interface for diagnostic monitoring is Oracle Enterprise Manager. Whenever possible, you should run ADDM using Oracle Enterprise Manager. If Oracle Enterprise Manager is unavailable, you can run ADDM using the DBMS_ADDM package. In order to run the DBMS_ADDM APIs, the user must be granted the ADVISOR privilege.

為了診斷資料庫效能問題，首先去分析一下ADDM的分析結果，它是在AWR快照產生時自動產生的。假如需要一個不同的分析（例如需要一個長時間的週期，使用不同的DBIO_EXPECTED或者改變分析的模式），你就應該手動的執行ADDM。

ADDM可以分析任何在相同資料庫上的2個AWR快照，只要這兩個快照都儲存在AWR中尚未被刪除掉就行。ADDM只能分析在資料庫例項啟動以後的，在執行期內產生的快照，也就是說一旦有兩個快照是跨越了資料庫重啟，則無法進行分析了。另外ADDM無法去分析那些在產生AWR快照時出現明顯錯誤的例項。在這種情況下，ADDM將會分析最大的未發生錯誤的例項子集。

分析ADDM主要靠OEM，如果沒有安裝OEM，那就使用DBMS_ADDM包去分析。必須有ADVISOR的許可權才能呼叫DBMS_ADDM介面。

This section contains the following topics:

 

6.3.1 Running ADDM in Database Mode

For Oracle RAC configurations, you can run ADDM in Database mode to analyze all instances of the databases. For single-instance configurations, you can still run ADDM in Database mode; ADDM will simply behave as if running in Instance mode.

對於RAC配置，你可以執行ADDM在資料庫模式下去分析資料庫的所有例項。對於單例項配置而言，你也可以執行在資料庫模式下；ADDM則像執行在例項模式下去執行。

為了執行資料庫模式，使用 dbms_addm.analyze_db 儲存過程：

To run ADDM in Database mode, use the DBMS_ADDM.ANALYZE_DB procedure:

BEGIN

DBMS_ADDM.ANALYZE_DB (

   task_name           IN OUT VARCHAR2,

   begin_snapshot      IN     NUMBER,

   end_snapshot        IN     NUMBER,

   db_id               IN     NUMBER := NULL);

END;

/

The task_name parameter specifies the name of the analysis task that will be created. The begin_snapshot parameter specifies the snapshot number of the beginning snapshot in the analysis period. The end_snapshot parameter specifies the snapshot number of the ending snapshot in the analysis period. The db_id parameter specifies the database identifier of the database that will be analyzed. If unspecified, this parameter defaults to the database identifier of the database to which you are currently connected.

The following example creates an ADDM task in database analysis mode, and executes it to diagnose the performance of the entire database during the time period defined by snapshots 137 and 145:

task_name是被建立的分析任務的名稱（注意這個引數是IN OUT型別，所以要使用變數）。begin_shnapshot引數指定了分析週期的開始快照。end_snapshot指定了結束的快照。db_id不指定的話，預設是你現在連線的資料庫。

下面的例子在資料庫分析模式下建立一個任務，執行它去診斷整個資料庫的效能問題，分析週期是從快照的137到145之間（相當於7個小時內的資料分析）

BEGIN

  :tname := 'ADDM for 7PM to 9PM';

  DBMS_ADDM.ANALYZE_DB(:tname, 137, 145);

END;

/

6.3.2 Running ADDM in Instance Mode

To analyze a particular instance of the database, you can run ADDM in Instance mode. To run ADDM in Instance mode, use the DBMS_ADDM.ANALYZE_INST procedure:

這個例項模式與資料庫模式除了名稱不一樣，其他的基本一樣，使用dbms_addm.analyze_inst儲存過程實現。

BEGIN

DBMS_ADDM.ANALYZE_INST (

   task_name           IN OUT VARCHAR2,

   begin_snapshot      IN     NUMBER,

   end_snapshot        IN     NUMBER,

   instance_number     IN     NUMBER := NULL,

   db_id               IN     NUMBER := NULL);

END;

/

The task_name parameter specifies the name of the analysis task that will be created. The begin_snapshot parameter specifies the snapshot number of the beginning snapshot in the analysis period. The end_snapshot parameter specifies the snapshot number of the ending snapshot in the analysis period. The instance_number parameter specifies the instance number of the instance that will be analyzed. If unspecified, this parameter defaults to the instance number of the instance to which you are currently connected. The db_id parameter specifies the database identifier of the database that will be analyzed. If unspecified, this parameter defaults to the database identifier of the database to which you are currently connected.

The following example creates an ADDM task in instance analysis mode, and executes it to diagnose the performance of instance number 1 during the time period defined by snapshots 137 and 145:

上面的廢話都是在解釋這個analyze_partial儲存過程的幾個引數，不用去看了，一眼就看懂了，不是嘛！

VAR tname VARCHAR2(30);

BEGIN

  :tname := 'my ADDM for 7PM to 9PM';

  DBMS_ADDM.ANALYZE_INST(:tname, 137, 145, 1);

END;

/

 

6.3.3 Running ADDM in Partial Mode

To analyze a subset of all database instances, you can run ADDM in Partial mode. To run ADDM in Partial mode, use the DBMS_ADDM.ANALYZE_PARTIAL procedure:

BEGIN

DBMS_ADDM.ANALYZE_PARTIAL (

   task_name           IN OUT VARCHAR2,

   instance_numbers    IN     VARCHAR2,   這個是和資料庫模式下比多出來的，資料庫模式是所有的例項，而它可以指定某幾個例項

   begin_snapshot      IN     NUMBER,

   end_snapshot        IN     NUMBER,

   db_id               IN     NUMBER := NULL);

END;

/

The task_name parameter specifies the name of the analysis task that will be created. The instance_numbers parameter specifies a comma-delimited list of instance numbers of instances that will be analyzed. The begin_snapshot parameter specifies the snapshot number of the beginning snapshot in the analysis period. The end_snapshot parameter specifies the snapshot number of the ending snapshot in the analysis period. The db_id parameter specifies the database identifier of the database that will be analyzed. If unspecified, this parameter defaults to the database identifier of the database to which you are currently connected.

The following example creates an ADDM task in partial analysis mode, and executes it to diagnose the performance of instance numbers 1, 2, and 4, during the time period defined by snapshots 137 and 145:

VAR tname VARCHAR2(30);

BEGIN

  :tname := 'my ADDM for 7PM to 9PM';

  DBMS_ADDM.ANALYZE_PARTIAL(:tname, '1,2,4', 137, 145);

END;

/

6.3.4 Displaying an ADDM Report

To display a text report of an executed ADDM task, use the DBMS_ADDM.GET_REPORT function:

DBMS_ADDM.GET_REPORT (task_name           IN VARCHAR2

  RETURN CLOB);

The following example displays a text report of the ADDM task specified by its task name using the tname variable:

Note that the return type of a report is a CLOB, formatted to fit line size of 80..

注意返回值是CLOB型別，被格式化成按80列對齊了。一定要注意設定 set long xxxxxxxx，不然會顯示不全的。:tname是一個剛才analyze_db時的名稱。

如果想在檔案中使用的話，可以使用spool xxx.txt;    最後再加上spool off;就可以輸出到檔案了

 

6.4 Views with ADDM Information

Typically, you should view output and information from ADDM using Oracle Enterprise Manager or ADDM reports.

通常情況下你可以透過OEM或者ADDM報表去檢視輸出的結果和資訊。

然而你也可以透過dba_advisor 檢視去檢視這個資訊。下面是一組相關的資訊：

However, you can display ADDM information through the DBA_ADVISOR views. This group of views includes:

• DBA_ADVISOR_FINDINGS

This view displays all the findings discovered by all advisors. Each finding is displayed with an associated finding ID, name, and type. For tasks with multiple executions, the name of each task execution associated with each finding is also listed.

這個檢視列出了所有的findings。每個finding都有一個finding id,name,type。 對於多個執行的任務，每個任務執行名稱連同每個finding被列出來。

     從檢視的定義看，finding_name,finding_id, type,type_id,owner,task_name,task_id等等。很多是4000位元組的，還是從報表中看吧！

• DBA_ADDM_FINDINGS

This view contains a subset of the findings displayed in the related DBA_ADVISOR_FINDINGS view. This view only displays the ADDM findings discovered by all advisors. Each ADDM finding is displayed with an associated finding ID, name, and type.

• DBA_ADVISOR_FINDING_NAMES

List of all finding names registered with the advisor framework.

這裡一共有83個finding項

• DBA_ADVISOR_RECOMMENDATIONS

This view displays the results of completed diagnostic tasks with recommendations for the problems identified in each execution. The recommendations should be reviewed in the order of the RANK column, as this relays the magnitude of the problem for the recommendation. The BENEFIT column displays the benefit to the system you can expect after the recommendation is performed. For tasks with multiple executions, the name of each task execution associated with each advisor task is also listed.

 

• DBA_ADVISOR_TASKS

This view provides basic information about existing tasks, such as the task ID, task name, and when the task was created. For tasks with multiple executions, the name and type of the last or current execution associated with each advisor task is also listed.