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

1、Oracle自帶性能分析工具AWR介紹和分析杭州華三通信技術(shù)有限公司目錄1 Oracle 10g的AWR性能優(yōu)化工具簡介2 AWR配置2.1 AWR統(tǒng)計數(shù)據(jù)的缺省配置2.2修改AWR統(tǒng)計數(shù)據(jù)的設(shè)置2.3 AWR的使用2.3.1 AWR產(chǎn)生快照點(時間點)的方法：手動或自動方式2.3.2 AWR產(chǎn)生報表的方法：使用SQL命令，根據(jù)快照產(chǎn)生相應(yīng)實例的報表2.4關(guān)注報表具體內(nèi)容2.4.1 Load Profile2.4.2 Instance Efficiency Percentages (Target 100%)2.4.3 IO Stats3對AWR報表內(nèi)容進行分析的ADDM工具3.1 ADDM概述

2、3.2 ADDM的使用方法3.2.1在所有Oracle服務(wù)器上確認EM的狀態(tài)，并啟動EM進程3.2.2通過IE瀏覽器，登錄EM界面3.3 ADDM查詢和診斷到的問題有那些3.3.1查看集群中Oracle服務(wù)器的硬件配置3.3.2 CPU的瓶頸3.3.3內(nèi)存結(jié)構(gòu)上的瓶頸？3.3.4內(nèi)存參數(shù)SGA、PGA查看和分析3.3.5 I/O是否存在性能瓶頸? (I/O子系統(tǒng)是否工作正常) ?3.3.6高負載的SQL語句(是否存在消耗過量系統(tǒng)資源的SQL) ?3.4 ADDM分析的結(jié)果4總結(jié)5附錄1Oracle 10g的AWR性能優(yōu)化工具簡介AWR (Automatic Workload Repositor

3、y)既自動工作負載信息庫是Oracle 10g新提供的收集數(shù)據(jù)庫統(tǒng)計信息的內(nèi)置工具。它比之前的statspack有顯著的改進，收集的信息也更多、更全面，使用方法也更簡單。它主要采集與性能相關(guān)的統(tǒng)計數(shù)據(jù)，并從那些統(tǒng)計數(shù)據(jù)中導(dǎo)出性能量度，以跟蹤潛在的問題，如包括AWR內(nèi)存區(qū)，歷史數(shù)據(jù)存儲文件和ASH等部件。AWR報告的內(nèi)容繁多，官方文檔也沒有對所有參數(shù)給出說明。AWR產(chǎn)生的報表包括以下幾部分。報表具體內(nèi)容參見如下插入的對象。1、Report SummeryCache sizesLoad profileInstance Efficiency Percentages (Target 100%)Shar

4、ed Pool StatisticsTop 5 Timed Events2、RAC StatisticsGlobal Cache Load ProfileGlobal Cache Efficiency Percentages (Target local+remote 100%)Global Cache and Enqueue Services - Workload CharacteristicsGlobal Cache and Enqueue Services - Messaging Statistics3、Wait Events StatisticsTime Model Statistics

5、Wait ClassWait EventsBackground Wait EventsOperating System StatisticsService StatisticsService 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 MemorySQ

6、L ordered by Version CountSQL ordered by Cluster Wait TimeComplete List of SQL Text5、Instance Activity StatisticsInstance Activity StatsInstance Activity Stats - Absolute ValuesInstance Activity Stats - Thread Activity6、IO StatsTablespace IO StatsFile IO Stats7、Buffer Pool Statistics8、Advisory Stati

7、sticsInstance Recovery StatsBuffer Pool 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 Summary

8、Undo Segment Stats11、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 Globa

9、l Cache 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 Memory Summar

10、ySGA 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 Served Stats22、Global Cache Transfer Stats2AWR配

11、置2.1AWR統(tǒng)計數(shù)據(jù)的缺省配置AWR實質(zhì)上是一個Oracle的內(nèi)置工具，它采集與性能相關(guān)的統(tǒng)計數(shù)據(jù)，并從那些統(tǒng)計數(shù)據(jù)中導(dǎo)出性能量度，以跟蹤潛在的問題。AWR利用多個快照(既時間點)來采集數(shù)據(jù)?？煺沼梢粋€稱為MMON的新后臺進程及其該進程自動地每小時采集一次。為了節(jié)省空間，采集的數(shù)據(jù)在7天后自動清除?？煺疹l率（SNAP_INTERVAL）和保留時間（RETENTION）都可以由用戶修改。查看當前的缺省設(shè)置，可以使用下面的語句：SQL select snap_interval, retention from dba_hist_wr_control;SNAP_INTERVAL RETENTION+

12、00000 01:00:00.0 +00007 00:00:00.0這些SQL語句顯示快照每小時采集一次，采集的數(shù)據(jù)保留7天。注意：1、snap_interval取值范圍為 10分鐘分鐘2.2修改AWR統(tǒng)計數(shù)據(jù)的設(shè)置例如，根據(jù)使用需要，修改快照時間間隔為20分鐘，保留時間為2天，通過如下命令修改，參數(shù)以分鐘為單位。修改：SQL begin dbms_workload_repository.modify_snapshot_settings ( interval = 20, retention = 2*24*60 ); end; /PL/SQL過程已成功完成。修改后，查詢結(jié)果：SQL select

13、 snap_interval, retention from dba_hist_wr_control;SNAP_INTERVAL RETENTION+00000 00:20:00.0 +00002 00:00:00.02.3AWR的使用2.3.1AWR產(chǎn)生快照點(時間點)的方法：手動或自動方式1.AWR手動產(chǎn)生快照SQL exec dbms_workload_repository.create_snapshot;PL/SQL過程已成功完成。2.AWR自動產(chǎn)生快照根據(jù)所設(shè)定的快照頻率（SNAP_INTERVAL）自動產(chǎn)生快照2.3.2AWR產(chǎn)生報表的方法：使用SQL命令，根據(jù)快照產(chǎn)生相應(yīng)實例的報

14、表使用SQL?/rdbms/admin/awrrpt命令產(chǎn)生報表。該命令實際調(diào)用的是位于名稱：$ORACLE_HOME/rdbms/admin/awrrpt.sql下的腳本awr提供一個腳本來生成報表。報表的形式有兩種：text和html。建議生成html格式的報表，因為可讀性非常。用腳本生成awr報表需要以sys用戶登錄并執(zhí)行命令。執(zhí)行過程需要輸入報表類型、天數(shù)（用來決定顯示那幾天內(nèi)的快照(snapshot)）、begin_snap、end_snap、以及報表名稱等5個參數(shù)。1.AWR產(chǎn)生報表舉例(1)推薦采用通過客戶端連接實例產(chǎn)生報表有兩種方法執(zhí)行如下命令：通過客戶端連接實例或登錄到Ora

15、cle服務(wù)器再連接實例。通過不同的方式登錄，產(chǎn)生報表的位置是不同的。1、通過Windows客戶端DOS窗口連接實例，產(chǎn)生報表的位置位于默認登錄路徑，如下：通過Windows DOS窗口登錄并執(zhí)行?/rdbms/admin/awrrpt命令，所產(chǎn)生的報表位于C:目錄下2、通過Oracle服務(wù)器連接實例，產(chǎn)生報表位于/home/oracle目錄下# cd /home/oracle# ls -l awrrpt_2_1_3.html-rw-r-r- 1 oracle oinstall Oct 16 14:23 awrrpt_2_12_13.html(2)查看產(chǎn)生快照時間點間隔和采集數(shù)據(jù)保持的天數(shù)SQL

16、 select snap_interval, retention from dba_hist_wr_control;SNAP_INTERVAL RETENTION+00000 01:00:00.0 +00007 00:00:00.0注意：默認情況下，Oracle數(shù)據(jù)庫每隔1個小時會自動生成1個快照點，并保持7天內(nèi)的快照點的數(shù)據(jù)，以便用戶分析。(3)執(zhí)行SQL命令，產(chǎn)生指定快照時間點間隔的數(shù)據(jù)報表SQL?/rdbms/admin/awrrptCurrent InstanceDB Id DB Name Inst Num Instance- - - - ORA 2 ora2/生成實例ora2的統(tǒng)計報

17、表Specify the Report TypeWould you like an HTML report, or a plain text report? Enter html for an HTML report, or text for plain text Defaults to html輸入report_type的值:html /報表類型Type Specified: htmlInstances in this Workload Repository schema DB Id Inst Num DB Name Instance Host- - - - -* 2 ORA ora2 ai

18、x53 1 ORA ora1 aix54Using for database IdUsing 2 for instance numberSpecify the number of days of snapshots to choose fromEntering the number of days (n) will result in the most recent (n) days of snapshots being listed. Pressing without specifying a number lists all completed snapshots.輸入num_days的值

19、:3 /天數(shù)(用來決定顯示那幾天內(nèi)的快照點(snapshot)Listing the last 3 days of Completed SnapshotsInstance DB Name Snap Id Snap Started Snap Levelora2 ORA 1 16 10月2008 12:00 1 2 16 10月2008 13:00 1 3 16 10月2008 14:00 1 4 16 10月2008 15:00 1 5 16 10月2008 16:00 1 6 16 10月2008 17:00 17 16 10月2008 17:15 1/手動生成的快照點 8 16 10月200

20、8 18:00 1 9 16 10月2008 19:00 1 10 16 10月2008 20:00 1 11 16 10月2008 21:00 1 12 16 10月2008 22:00 1 13 16 10月2008 23:00 1 14 17 10月2008 00:00 1 15 17 10月2008 01:00 1 注：以上是根據(jù)AWR的默認設(shè)置，每小時產(chǎn)生一個快照點數(shù)據(jù)Specify the Begin and End Snapshot Ids輸入begin_snap的值:12Begin Snapshot Id specified:12輸入end_snap的值:15End Snaps

21、hot Id specified:15Specify the Report NameThe default report file name is awrrpt_2_12_13.html. To use this name, press to continue, otherwise enter an alternative.輸入report_name的值:/回車，可以使用缺省值：awrrpt_2_12_13.html2.4關(guān)注報表具體內(nèi)容報表內(nèi)的參數(shù)內(nèi)容繁多，oracle官方網(wǎng)站搜索，也沒有找到文檔對如此眾多參數(shù)的介紹。從存儲的角度，個人認為需要關(guān)注的內(nèi)容主要有：Report SummeryC

22、ache sizesLoad profileInstance Efficiency Percentages (Target 100%)Shared Pool StatisticsTop 5 Timed EventsIO StatsTablespace IO StatsFile IO Stats2.4.1Load ProfilePer SecondPer TransactionRedo size:1,207.602,906.73Logical reads:18.4544.42Block changes:6.2314.99Physical reads:0.010.02Physical writes

23、: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 Call %:85.40Rollback per transaction %:52.36Rows per Sort:11.781.Redo size表示instance每秒/每事務(wù)產(chǎn)生的日志大小(單位字節(jié))，可表示數(shù)據(jù)庫任務(wù)的繁重與否。如日志量比較大，特別是每個事務(wù)

24、的日志量都比較大的時候，需要檢查是否有較大的事務(wù)在運行。2.Logical reads表示instance每秒/每事務(wù)產(chǎn)生的邏輯讀，單位是block。3.block changes表示instance每秒/每事務(wù)block變化數(shù)量，數(shù)據(jù)庫事務(wù)帶來改變的塊數(shù)量4.Physical reads表示instance每秒/每事務(wù)產(chǎn)生的物理讀，單位是block。如果說邏輯讀是從內(nèi)存中獲得數(shù)據(jù)，那么，物理讀就是要實實在在的從硬盤或者磁盤陣列中獲得數(shù)據(jù)，這個取決于磁盤和陣列的相應(yīng)速度。與邏輯讀同樣，物理讀也可能來自于select語句，而不一定僅是來自于事務(wù)。5.Physical writes表示instan

25、ce每秒/每事務(wù)產(chǎn)生的物理寫，單位是block。這個指標在很大程度上決定了數(shù)據(jù)庫系統(tǒng)的寫壓力。6.User calls表示instance每秒/每事務(wù)產(chǎn)生的用戶調(diào)用次數(shù)。User calls/Executes基本代表了每個語句的請求次數(shù)，Executes越接近User calls越好。7.Parses表示instance每秒/每事務(wù)解析總次數(shù)。包括軟分析和硬分析，但是不包括快速分析。近似反應(yīng)每秒/事務(wù)語句的執(zhí)行次數(shù),軟解析每秒超過300次意味著你的應(yīng)用程序效率不高，沒有使用soft parse，建議調(diào)整session_cursor_cache。8.Hard parses表示instance每秒

26、/每事務(wù)產(chǎn)生的硬解析次數(shù)，這在高可用的OLTP環(huán)境中應(yīng)當盡可能避免，應(yīng)當正確地使用綁定變量避免這樣的情況發(fā)生。每秒超過100次，就可能說明你綁定使用的不好9.Sorts表示instance每秒/每事務(wù)產(chǎn)生的排序次數(shù)。10.Executes表示instance每秒/每事務(wù)產(chǎn)生的語句執(zhí)行次數(shù)，包括了用戶執(zhí)行的SQL語句與系統(tǒng)執(zhí)行的SQL語句，表示一個系統(tǒng)SQL語句的繁忙程度。11.Transactions表示instance每秒/每事務(wù)產(chǎn)生的事務(wù)數(shù)，表示一個數(shù)據(jù)庫系統(tǒng)的事務(wù)繁重程度。這在不同的系統(tǒng)中，略有差異，如在一個典型的交易系統(tǒng)中（如：證券、銀行），可能主要是事務(wù)比較多；而在一個網(wǎng)站系統(tǒng)，可能

27、主要是Web查詢（select）比較多。12.Rollback per transaction%看回滾率是不是很高，因為回滾很耗資源。如果回滾過高，可能說明你的數(shù)據(jù)庫經(jīng)歷太多的無效操作。過多的回滾可能還會帶來Undo Block的競爭。2.4.2Instance 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 P

28、arse %:64.62Latch Hit %:100.00Parse CPU to Parse Elapsd %:32.10% Non-Parse CPU:97.681.Buffer Nowait %在緩沖區(qū)中獲取Buffer的未等待比率, Buffer Nowait Executions,就可能出現(xiàn)該比小于0的情況,該值0通常說明shared pool設(shè)置或效存在問題造成反復(fù)解析，reparse可能較嚴重,或者可是同snapshot有關(guān)如果該值為負值或者極低,通常說明數(shù)據(jù)庫性能存在問題。8.Latch Hit %表示內(nèi)部結(jié)構(gòu)維護鎖命中率，通常高于99。其值低是因為shared_pool_s

29、ize過大或沒有使用綁定變量導(dǎo)致硬解析過多。9.Parse CPU to Parse Elapsd %解析實際運行事件/解析實際運行時間。2.4.3IO 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.00650

30、00.002.File IO Statsordered 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/roraundotbs200480000.003對AWR報表內(nèi)容進行分析的ADDM工具3.1ADDM概述現(xiàn)在Oracle Da

31、tabase 10g中推出的這個引擎稱為自動數(shù)據(jù)庫診斷監(jiān)控程序既ADDM(Automatic Performance Diagnosis)。為了作出決策，ADDM使用了由AWR采集的數(shù)據(jù)。在AWR進行的每一次快照采集之后，調(diào)用ADDM來檢查量度并生成建議。因此，實際上擁有了一個一天二十四小時工作的自動數(shù)據(jù)庫管理員，它主動地分析數(shù)據(jù)并生成建議，從而把DBA解放出來，使能夠關(guān)注更具有戰(zhàn)略意義的問題。缺省情況下，在安裝Oracle 10g RAC軟件時,即安裝EM 10g（Oracle Enterprise Manager）,在概念上它與以前版本的不同之處在于，它不是客戶端安裝的工具；實際上它是位于

32、數(shù)據(jù)庫服務(wù)器本身上的HTTP服務(wù)器（稱為DB控制臺）。（參見圖）您可以使用任何瀏覽器查看EM界面。3.2ADDM的使用方法3.2.1在所有Oracle服務(wù)器上確認EM的狀態(tài)，并啟動EM進程1.登錄所有Oracle服務(wù)器，查看EM的運行狀態(tài)#su - oracle$emctl status dbconsoleOracle Enterprise Manager 10g Database Control Release .0Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.http:/aix53:1158

33、/em/console/aboutApplicationEM Daemonis notrunning./ EM沒有運行-Logs are generated in directory /oracle/OraHome/aix54_ora1/sysman/log2.如果沒有運行，登錄所有Oracle服務(wù)器開啟EM進程$ emctlstartdbconsoleOracle Enterprise Manager 10g Database Control Release .0Copyright (c) 1996, 2005 Oracle Corporation. All rights r

34、eserved.http:/aix53:1158/em/console/aboutApplicationStarting Oracle Enterprise Manager 10g Database Control . started.-Logs are generated in directory /oracle/OraHome/aix54_ora1/sysman/log3.2.2通過IE瀏覽器，登錄EM界面1.可通過如下方法查看端口號$ pwd/oracle/OraHome/install$ more portlist.iniiSQL*Plus HTTP port number =5560

35、Enterprise Manager Console HTTP Port (ora) = 1158Enterprise Manager Agent Port (ora) = 3938從這個文件中我們了解到，數(shù)據(jù)庫ora的代理程序監(jiān)聽端口3938，而EM控制臺監(jiān)聽1158。2.在IE瀏覽器內(nèi)輸入以下URL來調(diào)用EM登錄畫面輸入http:/aix53:1158/em/console/如果不能彈出界面，請用實際IP地址登錄如下：53:1158/em/console/。該URL調(diào)出登錄畫面，從中您可以用DBA用戶登錄。(1)登錄界面(2)主目錄顯示Oracle數(shù)據(jù)庫大

36、致運行信息如：從GUI上看出“有問題的表空間”有“1”個，點擊“1”可進一步查看是哪個表空間出現(xiàn)問題。3.3ADDM查詢和診斷到的問題有那些3.3.1查看集群中Oracle服務(wù)器的硬件配置1.進入“集群數(shù)據(jù)庫：ora”的“首目錄”2.選擇紅框內(nèi)的“集群：crs”并進入3.選擇紅框內(nèi)“主機”數(shù)據(jù)，并進入4.選擇“硬件：IBM_9110_510 powerpc”查看硬件信息或選擇“操作系統(tǒng)詳細資料”查看軟件信息信息顯示如下3.3.2CPU的瓶頸通過查看主頁面內(nèi)的：主機CPU來實時監(jiān)測CPU的狀態(tài)和瓶頸。3.3.3內(nèi)存結(jié)構(gòu)上的瓶頸？1.進入“集群數(shù)據(jù)庫：ora”的“首目錄”2.選擇紅框內(nèi)的“集群：c

37、rs”并進入3.選擇“性能”標簽，并選擇具體主機并查看“內(nèi)存詳細資料”4.詳細信息如下從以上可以很明顯的看出：內(nèi)存存在瓶頸。已經(jīng)使用了90.35%。3.3.4內(nèi)存參數(shù)SGA、PGA查看和分析1.進入“集群數(shù)據(jù)庫：ora”的“首目錄”2.拉到最底端，選擇“指導(dǎo)中心”連接3.在進入“內(nèi)存指導(dǎo)”連接4.選擇具體實例查看內(nèi)存參數(shù)，并選擇“繼續(xù)”5.顯示具體實例的PGA和SGA的具體內(nèi)容3.3.5I/O是否存在性能瓶頸? (I/O子系統(tǒng)是否工作正常) ?1.進入“集群數(shù)據(jù)庫：ora”的“首目錄”2.選擇紅框內(nèi)的“集群：crs”并進入3.選擇“性能”標簽，并選擇具體主機并查看“磁盤詳細資料”4.詳細信息如

38、下查看磁盤設(shè)備的寫入的速度和磁盤使用率，來確認是否是磁盤存在瓶頸。3.3.6高負載的SQL語句(是否存在消耗過量系統(tǒng)資源的SQL) ?1.進入“集群數(shù)據(jù)庫：ora”的“首目錄”2.選擇“SQL優(yōu)化指導(dǎo)”并進入3.選擇具體實例查看內(nèi)存參數(shù)，并選擇“繼續(xù)”4.選擇“周期SQL”5.查看SQL語句的使用情況，根據(jù)“執(zhí)行占用時間”查看哪個SQL語句高負荷3.4ADDM分析的結(jié)果ADDM分析的結(jié)果以一些Finding的樣式來表達。每個Finding都屬于以下三種類型之一：1.問題:描述了導(dǎo)致數(shù)據(jù)庫性能問題的根源。2.征兆:包含了可能導(dǎo)致其他問題的信息。3.信息:報告其他沒有問題的模塊。4總結(jié)AWR報表是針對實例的，多個實例需要多個AWR報表，從而了解到每個實例在應(yīng)用環(huán)境的狀態(tài)。AWR報表的產(chǎn)生的過程