Oracle自帶性能分析工具awr介紹和分析

1、Oracle自帶性能分析工具AWR介紹和分析杭州華三通信技術有限公司          目  錄1 Oracle 10g的AWR性能優(yōu)化工具簡介2 AWR配置2.1 AWR統(tǒng)計數據的缺省配置2.2 修改AWR統(tǒng)計數據的設置2.3 AWR的使用2.3.1 AWR產生快照點(時間點)的方法：手動或自動方式2.3.2 AWR產生報表的方法：使用SQL命令，根據快照產生相應實例的報表2.4 關注報表具體內容2.4.1 Load Profile2.4.2 Insta

2、nce Efficiency Percentages (Target 100%)2.4.3 IO Stats3 對AWR報表內容進行分析的ADDM工具3.1 ADDM概述3.2 ADDM的使用方法3.2.1 在所有Oracle服務器上確認EM的狀態(tài)，并啟動EM進程3.2.2 通過IE瀏覽器，登錄EM界面3.3 ADDM查詢和診斷到的問題有那些3.3.1 查看集群中Oracle服務器的硬件配置3.3.2 CPU的瓶頸3.3.3 內存結構上的瓶頸？3.3.4 內存參數SGA、PGA查看和分析3.3.5 I/O 是否存在性能瓶頸?

3、 (I/O子系統(tǒng)是否工作正常) ?3.3.6 高負載的SQL語句 (是否存在消耗過量系統(tǒng)資源的SQL) ?3.4 ADDM 分析的結果4 總結5 附錄 1  Oracle 10g的AWR性能優(yōu)化工具簡介AWR (Automatic Workload Repository)既自動工作負載信息庫是Oracle 10g新提供的收集數據庫統(tǒng)計信息的內置工具。它比之前的statspack有顯著的改進，收集的信息也更多、更全面，使用方法也更簡單。它主要采集與性能相關的統(tǒng)計數據，并從那些統(tǒng)計數據中導出性能量度，以跟蹤潛在的問題，

4、如包括AWR內存區(qū)，歷史數據存儲文件和ASH等部件。AWR報告的內容繁多，官方文檔也沒有對所有參數給出說明。AWR產生的報表包括以下幾部分。報表具體內容參見如下插入的對象。1、  Report SummeryCache sizesLoad profileInstance Efficiency Percentages (Target 100%)Shared Pool StatisticsTop 5 Timed Events2、  RAC StatisticsGlobal Cache Load ProfileGlobal Cache Efficiency P

5、ercentages (Target local+remote 100%)Global Cache and Enqueue Services - Workload CharacteristicsGlobal Cache and Enqueue Services - Messaging Statistics3、  Wait Events StatisticsTime Model StatisticsWait ClassWait EventsBackground Wait EventsOperating System StatisticsService StatisticsSe

6、rvice Wait Class Stats4、  SQL StatisticsSQL ordered by Elapsed TimeSQL ordered by CPU TimeSQL ordered by GetsSQL ordered by ReadsSQL ordered by ExecutionsSQL ordered by Parse CallsSQL ordered by Sharable MemorySQL ordered by Version CountSQL ordered by Cluster Wait TimeComplete List of SQL

7、 Text5、  Instance Activity StatisticsInstance Activity StatsInstance Activity Stats - Absolute ValuesInstance Activity Stats - Thread Activity6、 IO StatsTablespace IO StatsFile IO Stats7、  Buffer Pool Statistics8、  Advisory StatisticsInstance Recovery StatsBuffer P

8、ool AdvisoryPGA Aggr SummaryPGA Aggr Target StatsPGA Aggr Target HistogramPGA Memory AdvisoryShared Pool AdvisorySGA Target AdvisoryStreams Pool AdvisoryJava Pool Advisory9、  Wait StatisticsBuffer Wait StatisticsEnqueue Activity10、 Undo StatisticsUndo Segment SummaryUndo Segment Stats

9、11、 Latch StatisticsLatch ActivityLatch Sleep BreakdownLatch Miss SourcesParent Latch StatisticsChild Latch Statistics12、 Segment StatisticsSegments by Logical ReadsSegments by Physical ReadsSegments by Row Lock WaitsSegments by ITL WaitsSegments by Buffer Busy WaitsSegments by Global Cach

10、e Buffer BusySegments by CR Blocks ReceivedSegments by Current Blocks Received13、 Dictionary Cache StatisticsDictionary Cache StatsDictionary Cache Stats (RAC)14、 Library Cache StatisticsLibrary Cache ActivityLibrary Cache Activity (RAC)15、 Memory StatisticsProcess Memory SummarySGA M

11、emory SummarySGA breakdown difference16、 Streams StatisticsStreams CPU/IO UsageStreams CaptureStreams ApplyBuffered QueuesBuffered SubscribersRule Set17、 Resource Limit Stats18、 init.ora Parameters19、 Global Enqueue Statistics20、 Global CR Served Stats21、 Global CURRENT

12、 Served Stats22、 Global Cache Transfer Stats2  AWR配置2.1  AWR統(tǒng)計數據的缺省配置AWR 實質上是一個 Oracle 的內置工具，它采集與性能相關的統(tǒng)計數據，并從那些統(tǒng)計數據中導出性能量度，以跟蹤潛在的問題。AWR利用多個快照(既時間點)來采集數據?？煺沼梢粋€稱為 MMON 的新后臺進程及其該進程自動地每小時采集一次。為了節(jié)省空間，采集的數據在 7 天后自動清除?？煺疹l率（SNAP_INTERVAL）和保留時間（RETE

13、NTION）都可以由用戶修改。查看當前的缺省設置，可以使用下面的語句：SQL> select snap_interval, retention from dba_hist_wr_control;SNAP_INTERVAL    RETENTION+00000 01:00:00.0     +00007 00:00:00.0這些 SQL 語句顯示快照每小時采集一次，采集的數據保留 7 天。 注意：1、snap_interval 取值范圍為 10分鐘 5

14、2560000分鐘  2.2  修改AWR統(tǒng)計數據的設置  例如，根據使用需要，修改快照時間間隔為 20 分鐘，保留時間為2天 ，通過如下命令修改，參數以分鐘為單位。修改：SQL> begin       dbms_workload_repository.modify_snapshot_settings (          interval =>

15、; 20,          retention => 2*24*60       );    end;    / PL/SQL 過程已成功完成。 修改后，查詢結果：SQL> select snap_interval, retention from dba_hist_wr_control;SNAP_INTERVAL   RET

16、ENTION+00000 00:20:00.0     +00002 00:00:00.02.3  AWR的使用2.3.1  AWR產生快照點(時間點)的方法：手動或自動方式1. AWR手動產生快照SQL> exec dbms_workload_repository.create_snapshot;PL/SQL 過程已成功完成。2. AWR自動產生快照根據所設定的快照頻率（SNAP_INTERVAL）自動產生快照2.3.2  AWR產生報表的方法：使用SQL命

17、令，根據快照產生相應實例的報表使用SQL> ?/rdbms/admin/awrrpt 命令產生報表。該命令實際調用的是位于名稱：$ORACLE_HOME/rdbms/admin/awrrpt.sql下的腳本awr提供一個腳本來生成報表。報表的形式有兩種：text和html。建議生成html格式的報表，因為可讀性非常。用腳本生成awr報表需要以sys用戶登錄并執(zhí)行命令。執(zhí)行過程需要輸入報表類型、天數（用來決定顯示那幾天內的快照(snapshot)）、begin_snap、end_snap、以及報表名稱等5個參數。1. AWR產生報表舉例(1) 

18、60;        推薦采用通過客戶端連接實例產生報表有兩種方法執(zhí)行如下命令：通過客戶端連接實例或登錄到Oracle服務器再連接實例。通過不同的方式登錄，產生報表的位置是不同的。1、通過Windows客戶端 DOS窗口連接實例，產生報表的位置位于默認登錄路徑，如下：通過Windows DOS窗口登錄并執(zhí)行?/rdbms/admin/awrrpt命令，所產生的報表位于C:目錄下2、通過Oracle服務器連接實例，產生報表位于/home/oracle目錄下# cd /home/oracle# ls -l a

19、wrrpt_2_1_3.html-rw-r-r-   1 oracle   oinstall     324609 Oct 16 14:23 awrrpt_2_12_13.html (2)          查看產生快照時間點間隔和采集數據保持的天數 SQL> select snap_interval, retention from dba_hist_wr_control; SNA

20、P_INTERVAL   RETENTION+00000 01:00:00.0     +00007 00:00:00.0 注意：默認情況下，Oracle數據庫每隔1個小時會自動生成1個快照點，并保持7天內的快照點的數據，以便用戶分析。 (3)          執(zhí)行SQL命令，產生指定快照時間點間隔的數據報表SQL> ?/rdbms/admin/awrrptCurrent Instance 

21、;DB Id           DB Name      Inst Num    Instance-        -      -        - 1286484956 ORA   &

22、#160;          2                 ora2      /生成實例ora2的統(tǒng)計報表 Specify the Report TypeWould you like an HTML report, or a plain text report? Ente

23、r 'html' for an HTML report, or 'text' for plain text Defaults to 'html'輸入 report_type 的值:  html                  /報表類型Type Specified:  html Instances in this Wo

24、rkload Repository schema   DB Id               Inst Num       DB Name      Instance     Host-         

25、60;   -           -     - -* 1286484956        2                 ORA     &#

26、160;         ora2         aix53  1286484956         1                 ORA   

27、0;           ora1         aix54 Using 1286484956 for database IdUsing          2 for instance number Specify the number of days of snapshots to choose from

28、Entering the number of days (n) will result in the most recent (n) days of snapshots being listed.  Pressing <return> without specifying a number lists all completed snapshots.輸入 num_days 的值:  3          /天數(用來決定顯示那幾天內的快照點(s

29、napshot) Listing the last 3 days of Completed Snapshots              Instance  DB Name   Snap Id      Snap Started       Snap Levelora2   &#

30、160;        ORA           1       16 10月 2008 12:00    1                  &

31、#160;   2       16 10月 2008 13:00     1                      3       16 10月 2008 14:00 

32、60;   1                      4       16 10月 2008 15:00     1           

33、0;          5       16 10月 2008 16:00     1                      6     

34、;  16 10月 2008 17:00     1                      7       16 10月 2008 17:15     1 /手動生成的快照點  

35、;                    8       16 10月 2008 18:00     1                &#

36、160;     9       16 10月 2008 19:00     1                     10       16 10月 2008 20:00

37、60;   1                     11       16 10月 2008 21:00    1             

38、60;       12       16 10月 2008 22:00    1                     13       16 10月 2008 23

39、:00    1                     14       17 10月 2008 00:00    1            

40、60;        15       17 10月 2008 01:00    1                             

41、;          注：以上是根據AWR的默認設置，每小時產生一個快照點數據Specify the Begin and End Snapshot Ids輸入 begin_snap 的值:  12Begin Snapshot Id specified: 12 輸入 end_snap 的值:  15End   Snapshot Id specified: 15 Spec

42、ify the Report NameThe default report file name is awrrpt_2_12_13.html.  To use this name, press <return> to continue, otherwise enter an alternative. 輸入 report_name 的值:           /回車，可以使用缺省值：awrrpt_2_12_13.html 2.

43、4  關注報表具體內容報表內的參數內容繁多，oracle官方網站搜索，也沒有找到文檔對如此眾多參數的介紹。從存儲的角度，個人認為需要關注的內容主要有：Report SummeryCache sizesLoad profileInstance Efficiency Percentages (Target 100%)Shared Pool StatisticsTop 5 Timed EventsIO StatsTablespace IO StatsFile IO Stats2.4.1  Load Profile Per SecondPer Tran

44、sactionRedo size:1,207.602,906.73Logical reads:18.4544.42Block changes:6.2314.99Physical reads:0.010.02Physical writes:0.400.96User calls:2.425.84Parses:1.393.33Hard parses:0.020.05Sorts:0.801.92Logons:0.040.10Executes:3.919.42Transactions:0.42  % Blocks changed per Read:33.74Recursive Cal

45、l %:85.40Rollback per transaction %:52.36Rows per Sort:11.78 1. Redo size表示instance每秒/每事務產生的日志大小(單位字節(jié))，可表示數據庫任務的繁重與否。如日志量比較大，特別是每個事務的日志量都比較大的時候，需要檢查是否有較大的事務在運行。2. Logical reads表示instance每秒/每事務產生的邏輯讀，單位是block。3. block changes表示instance每秒/每事務block變化數量，數據庫事務帶來改變的塊數量4. Physical

46、reads表示instance每秒/每事務產生的物理讀，單位是block。如果說邏輯讀是從內存中獲得數據，那么，物理讀就是要實實在在的從硬盤或者磁盤陣列中獲得數據，這個取決于磁盤和陣列的相應速度。與邏輯讀同樣，物理讀也可能來自于select語句，而不一定僅是來自于事務。5. Physical writes表示instance每秒/每事務產生的物理寫，單位是block。這個指標在很大程度上決定了數據庫系統(tǒng)的寫壓力。6. User calls表示instance每秒/每事務產生的用戶調用次數。User calls/Executes基本代表了每個語句的請求次數，Executes越

47、接近User calls越好。7. Parses表示instance每秒/每事務解析總次數。包括軟分析和硬分析，但是不包括快速分析。近似反應每秒/事務語句的執(zhí)行次數,軟解析每秒超過300次意味著你的"應用程序"效率不高，沒有使用soft parse，建議調整session_cursor_cache。8. Hard parses表示instance每秒/每事務產生的硬解析次數，這在高可用的OLTP環(huán)境中應當盡可能避免，應當正確地使用綁定變量避免這樣的情況發(fā)生。每秒超過100次，就可能說明你綁定使用的不好9. Sorts表示instance每秒/每

48、事務產生的排序次數。10. Executes表示instance每秒/每事務產生的語句執(zhí)行次數，包括了用戶執(zhí)行的SQL語句與系統(tǒng)執(zhí)行的SQL語句，表示一個系統(tǒng)SQL語句的繁忙程度。11. Transactions表示instance每秒/每事務產生的事務數，表示一個數據庫系統(tǒng)的事務繁重程度。這在不同的系統(tǒng)中，略有差異，如在一個典型的交易系統(tǒng)中（如：證券、銀行），可能主要是事務比較多；而在一個網站系統(tǒng)，可能主要是Web查詢（select）比較多。12. Rollback per transaction%看回滾率是不是很高，因為回滾很耗資源。如果回滾過高，可能說明你的

49、數據庫經歷太多的無效操作。過多的回滾可能還會帶來Undo Block的競爭。2.4.2  Instance Efficiency Percentages (Target 100%)Buffer Nowait %:100.00Redo NoWait %:100.00Buffer Hit %:99.96In-memory Sort %:100.00Library Hit %:98.67Soft Parse %:98.43Execute to Parse %:64.62Latch Hit %:100.00Parse CPU to Parse Elapsd %:32.10% Non

50、-Parse CPU:97.681. Buffer Nowait %在緩沖區(qū)中獲取Buffer的未等待比率, Buffer Nowait<99%說明，有可能是有熱塊(查找x$bh的 tch和v$latch_children的cache buffers chains)2. Redo NoWait %在Redo緩沖區(qū)獲取Buffer的未等待比率3. Buffer Hit %數據塊在數據緩沖區(qū)中得命中率。如果在典型的OLTP環(huán)境中，通常應在95%以上。否則應考慮加大db_cache_size,但是大量的非選擇的索引也會造成該值很高（大量的db file

51、sequential read）。4. In-memory Sort %在內存中的排序率。5. Library Hit %主要代表sql在共享區(qū)的命中率，通常在95%以上，否則，需要要考慮加大共享池，綁定變量，修改cursor_sharing等參數。6. Soft Parse %近似看作sql在共享區(qū)的命中率，小于95%，需要考慮到綁定。如果低于80%，那么可能是sql沒有被重用。7. Execute to Parse %sql語句解析后被重復執(zhí)行的次數，如果過低，可以考慮設置 session_cached_cursors參數, 公式

52、為100 * (1 - Parses/Executions) = Execute to Parse所以如果系統(tǒng)Parses > Executions,就可能出現該比小于0的情況, 該值<0通常說明shared pool設置或效存在問題造成反復解析，reparse可能較嚴重,或者可是同snapshot有關如果該值為負值或者極低,通常說明數據庫性能存在問題。8. Latch Hit %表示內部結構維護鎖命中率，通常高于99。其值低是因為shared_pool_size過大或沒有使用綁定變量導致硬解析過多。9. Parse CPU to Parse Elap

53、sd %解析實際運行事件/解析實際運行時間。2.4.3  IO Stats1. Tablespace IO Statsordered by IOs (Reads + Writes) descTablespaceReadsAv Reads/sAv Rd(ms)Av Blks/RdWritesAv Writes/sBuffer WaitsAv Buf Wt(ms)SYSAUX3202.501.061,283020.00UNDOTBS2000.000.00480000.00SYSTEM2400.421.0065000.002. File IO Statsord

54、ered by Tablespace, FileTablespaceFilenameReadsAv Reads/sAv Rd(ms)Av Blks/RdWritesAv Writes/sBuffer WaitsAv Buf Wt(ms)SYSAUX/dev/rorasysaux3202.501.061,283020.00SYSTEM/dev/rorasystem2400.421.0065000.00UNDOTBS2/dev/roraundotbs200  480000.003  對AWR報表內容進行分析的ADDM工具3.1  ADDM

55、概述現在 Oracle Database 10g 中推出的這個引擎稱為自動數據庫診斷監(jiān)控程序既ADDM  (Automatic Performance Diagnosis)。為了作出決策，ADDM 使用了由 AWR 采集的數據。在 AWR 進行的每一次快照采集之后，調用 ADDM來檢查量度并生成建議。因此，實際上擁有了一個一天二十四小時工作的自動數據庫管理員，它主動地分析數據并生成建議，從而把DBA解放出來，使能夠關注更具有戰(zhàn)略意義的問題。缺省情況下，在安裝 Oracle 10g RA

56、C軟件時,即安裝 EM 10g（Oracle Enterprise Manager）,在概念上它與以前版本的不同之處在于，它不是客戶端安裝的工具；實際上它是位于數據庫服務器本身上的 HTTP 服務器（稱為 DB 控制臺）。（參見圖）您可以使用任何瀏覽器查看 EM 界面。3.2  ADDM的使用方法3.2.1  在所有Oracle服務器上確認EM的狀態(tài)，并啟動EM進程1. 登錄所有Oracle服務器，查看EM的運行狀態(tài)#su - oracle$ emctl statu

57、s dbconsoleOracle Enterprise Manager 10g Database Control Release .0 Copyright (c) 1996, 2005 Oracle Corporation.  All rights reserved.http:/aix53:1158/em/console/aboutApplicationEM Daemon is not running.     / EM沒有運行-Logs are generated in directo

58、ry /oracle/OraHome/aix54_ora1/sysman/log 2. 如果沒有運行，登錄所有Oracle服務器開啟EM進程$ emctl start dbconsoleOracle Enterprise Manager 10g Database Control Release .0 Copyright (c) 1996, 2005 Oracle Corporation.  All rights reserved.http:/aix53:1158/em/console/aboutApplicationS

59、tarting Oracle Enterprise Manager 10g Database Control . started.-Logs are generated in directory /oracle/OraHome/aix54_ora1/sysman/log 3.2.2  通過IE瀏覽器，登錄EM界面1. 可通過如下方法查看端口號$ pwd/oracle/OraHome/install$ more portlist.iniiSQL*Plus HTTP port number =5560Enterprise Manager Console HT

60、TP Port (ora) = 1158Enterprise Manager Agent Port (ora) = 3938從這個文件中我們了解到，數據庫 ora 的代理程序監(jiān)聽端口 3938，而 EM 控制臺監(jiān)聽 1158。2. 在IE瀏覽器內輸入以下 URL來調用 EM 登錄畫面輸入http:/aix53:1158/em/console/ 如果不能彈出界面，請用實際IP地址登錄如下：53:1158/em/console/。該 URL

61、60;調出登錄畫面，從中您可以用 DBA 用戶登錄。(1)          登錄界面(2)          主目錄顯示Oracle數據庫大致運行信息如：從GUI上看出“有問題的表空間”有“1”個，點擊“1”可進一步查看是哪個表空間出現問題。3.3  ADDM查詢和診斷到的問題有那些3.3.1  查看集群中Oracle服務器的硬件配置1

62、. 進入“集群數據庫：ora”的“首目錄”2. 選擇紅框內的“集群：crs”并進入3. 選擇紅框內“主機”數據，并進入4. 選擇“硬件：IBM_9110_510 powerpc”查看硬件信息或選擇“操作系統(tǒng)詳細資料”查看軟件信息信息顯示如下 3.3.2  CPU的瓶頸    通過查看主頁面內的：主機 CPU 來實時監(jiān)測CPU的狀態(tài)和瓶頸。3.3.3  內存結構上的瓶頸？1.   進入“集群數據庫：ora”的“首目錄”2. 選擇紅框內的“集群：crs”并進入3. 選擇“性能”標簽，并選擇具體主機并查看“內存詳細資料”4. 詳細信息如下         從以上可以很明顯的看出：內存存在瓶頸。已經使用了90.35%。3.3.4  內存參數SGA、PGA查看和分析1. 進入“集群數據庫：ora”的“首目錄”2. 拉到最底端，選擇“指導中心”連接3. 在進入“內存指導”連接4. 選擇具體實