ORACLE10G AWR安裝配置---05

Oracle Database 10g 提供了一個顯著改進的工具：自動工作負載資訊庫 (AWR)。Oracle 建議使用者用這個取代 Statspack。AWR 實質上是一個 Oracle 的內建工具，它採集與效能相關的統計資料，並從那些統計資料中匯出效能量度，以跟蹤潛在的問題。與 Statspack 不同，快照由一個稱為 MMON 的新的後臺程式及其從程式自動地每小時採集一次。為了節省空間，採集的資料在 7 天后自動清除。快照頻率和保留時間都可以由使用者修改。它產生兩種型別的輸出：文字格式（類似於 Statspack 報表的文字格式但來自於 AWR 資訊庫）和預設的 HTML 格式（擁有到部分和子部分的所有超連結），從而提供了非常使用者友好的報表。
AWR 使用幾個表來儲存採集的統計資料，所有的表都儲存在新的名稱為 SYSAUX 的特定表空間中的 SYS 模式下，並且以 WRM$_* 和 WRH$_* 的格式命名。前一種型別儲存後設資料資訊（如檢查的資料庫和採集的快照），後一種型別儲存實際採集的統計資料。H 代表“歷史資料 (historical)”而 M 代表“後設資料 (metadata)”。在這些表上構建了幾種帶字首 DBA_HIST_ 的檢視，這些檢視可以用來編寫您自己的效能診斷工具。檢視的名稱直接與表相關；例如，檢視 DBA_HIST_SYSMETRIC_SUMMARY 是在WRH$_SYSMETRIC_SUMMARY 表上構建的。
一、安裝
SQL> conn / AS SYSDBA
SQL> var snap_id number
SQL> exec :snap_id:=dbms_workload_repository.create_snapshot
SQL> print snap_id
   SNAP_ID
----------
      1182
SQL> @?/rdbms/admin/awrrpt.sql
輸入 report_type 的值:
輸入 num_days 的值: 1
輸入 begin_snap 的值: 1181
輸入 end_snap 的值: 1182
輸入 report_name 的值:
Report written to awrrpt_1_1181_1182.html
SQL> exit
下載awrrpt_1_1181_1182.html並開啟檢視。
需要注意的是使用 AWR 需要有 Diagnostic Pack License。Oracle 後來推出了一個解決方案可以禁止掉該特性。
在 Note.436386.1 有說明:
SQL> @dbms_awr.plb           -----------------------執行不成功，by謝
然後執行：
SQL> dbms_awr.disable_awr();   -----------------------執行不成功，by謝
如果用 sys 之外的使用者建立 AWR 報告，則需要進行合適的授權。否則會報告錯誤 PACKAGE 執行錯誤。
SQL> CONNECT / AS SYSDBA;
SQL> GRANT ADVISOR TO foo;
SQL> GRANT SELECT_CATALOG_ROLE TO foo;
SQL> GRANT EXECUTE ON sys.dbms_workload_repository TO foo;
要注意 Bug 4597354 在建立基線資料的時候，對效能有很大影響。在一個非常繁忙的系統上不要進行此操作。
如果結合 EM 用 AWR 是很方便的.
二、操作
1.檢視當前的AWR儲存策略
SQL> col SNAP_INTERVAL format a20
SQL> col RETENTION format a20
SQL> select * from dba_hist_wr_control;
      DBID SNAP_INTERVAL        RETENTION            TOPNSQL
---------- -------------------- -------------------- ----------
262089084 +00000 01:00:00.0    +00007 00:00:00.0    DEFAULT
以上結果表示,每小時產生一個SNAPSHOT，保留7天。
2.調整AWR配置
AWR配置都是通過dbms_workload_repository包進行配置。
2.1 調整AWR產生snapshot的頻率和保留策略，如將收集間隔時間改為30 分鐘一次。並且保留5天時間（單位都是分鐘）：
SQL> exec dbms_workload_repository.modify_snapshot_settings(interval=>30, retention=>5*24*60);
2.2 關閉AWR，把interval設為0則關閉自動捕捉快照
SQL> exec dbms_workload_repository.modify_snapshot_settings(interval=>0);
2.3 手工建立一個快照
SQL> exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
2.4 檢視快照
SQL> select * from sys.wrh$_active_session_history
2.5 手工刪除指定範圍的快照
SQL> exec DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(low_snap_id => 973, high_snap_id => 999, dbid => 262089084);
2.6 建立baseline，儲存這些資料用於將來分析和比較
SQL> exec dbms_workload_repository.create_baseline(start_snap_id => 1003, end_snap_id => 1013, 'apply_interest_1');
2.7 刪除baseline
SQL> exec DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE(baseline_name => 'apply_interest_1', cascade => FALSE);
2.8 將AWR資料匯出並遷移到其它資料庫以便於以後分析
SQL> exec DBMS_SWRF_INTERNAL.AWR_EXTRACT(dmpfile => 'awr_data.dmp', mpdir => 'DIR_BDUMP', bid => 1003, eid => 1013);
2.9 遷移AWR資料檔案到其他資料庫
SQL> exec DBMS_SWRF_INTERNAL.AWR_LOAD(SCHNAME => 'AWR_TEST', dmpfile => 'awr_data.dmp', dmpdir => 'DIR_BDUMP');
把AWR資料轉移到SYS模式中：
SQL> exec DBMS_SWRF_INTERNAL.MOVE_TO_AWR (SCHNAME => 'TEST');
3.AWR報告日常分析
3.1.SQL ordered by Elapsed Time：
   記錄了執行總和時間的TOP SQL(請注意是監控範圍內該SQL的執行時間總和，而不是單次SQL執行時間ElapsedTime= CPUTime+Wait Time)。
SQL ordered by Elapsed Time
• Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
• % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100
Elapsed Time (s) CPU Time (s) Executions Elap per Exec (s) % Total DB Time SQL Id SQL Module SQL Text
152,148 150,929 205,218 0.74 24.22 an4rtjrb8tbq5
  update SYS.AUD$ set XID = :1 w...
109,969 109,032 2,003 54.90 17.50 0twzs3px5d7wj
  DECLARE job BINARY_INTEGER := ...
38,797 38,460 8,843 4.39 6.17 6gvch1xu9ca3g
  DECLARE job BINARY_INTEGER := ...
29,312 13,414 10 2931.15 4.67 26ndjmd3nnmwx
  BEGIN CS_SLT_DSCLJG(:1, :2, :3...
 
  Elapsed Time(S): SQL語句執行用總時長，此排序就是按照這個欄位進行的。注意該時間不是單個SQL跑的時間，而是監控範圍內SQL執行次數的總和時間。單位時間為秒。ElapsedTime= CPUTime+Wait Time
  CPU Time(s): 為SQL語句執行時CPU佔用時間總時長，此時間會小於等於Elapsed Time時間。單位時間為秒。
 Executions: SQL語句在監控範圍內的執行次數總計。
 Elap per Exec(s):執行一次SQL的平均時間。單位時間為秒。
 % Total DB Time: 為SQL的Elapsed Time時間佔資料庫總時間的百分比。
 SQL ID:SQL語句的ID編號，點選之後就能導航到下邊的SQL詳細列表中，點選IE的返回可以回到當前SQL ID的地方。
 SQL Module: 顯示該SQL是用什麼方式連線到資料庫執行的，如果是用SQL*Plus或者PL/SQL連結上來的那基本上都是有人在除錯程式。一般用前臺應用連結過來執行的sql該位置為空。
 SQL Text:簡單的sql提示，詳細的需要點選SQL ID。
 
3.2.SQL ordered by CPU Time:
 記錄了執行佔CPU時間總和時間最長的TOP SQL(請注意是監控範圍內該SQL的執行佔CPU時間總和，而不是單次SQL執行時間)。
3.3.SQL ordered by Gets:
 記錄了執行佔總buffer gets(邏輯IO)的TOP SQL(請注意是監控範圍內該SQL的執行佔Gets總和，而不是單次SQL執行所佔的Gets).
 
3.4.SQL ordered by Reads:
記錄了執行佔總磁碟物理讀(物理IO)的TOP SQL(請注意是監控範圍內該SQL的執行佔磁碟物理讀總和，而不是單次SQL執行所佔的磁碟物理讀)。
 
3.5.SQL ordered by Executions:
 記錄了按照SQL的執行次數排序的TOP SQL。該排序可以看出監控範圍內的SQL執行次數。
 
3.6.SQL ordered by Parse Calls:
 記錄了SQL的軟解析次數的TOP SQL。
 說到軟解析(soft prase)和硬解析(hard prase)，就不能不說一下Oracle對sql的處理過程。當你發出一條sql語句交付Oracle，在執行和獲取結果前，Oracle對此sql將進行幾個步驟的處理過程：
　　1、語法檢查(syntax check)
　　檢查此sql的拼寫是否語法。
　　2、語義檢查(semantic check)
　　諸如檢查sql語句中的訪問物件是否存在及該使用者是否具備相應的許可權。
　　3、對sql語句進行解析(prase)
　　利用內部演算法對sql進行解析，生成解析樹(parse tree)及執行計劃(execution plan)。
　　4、執行sql，返回結果(execute and return)
　　其中，軟、硬解析就發生在第三個過程裡。
　　Oracle利用內部的hash演算法來取得該sql的hash值，然後在library cache裡查詢是否存在該hash值；
　　假設存在，則將此sql與cache中的進行比較；
　　假設“相同”，就將利用已有的解析樹與執行計劃，而省略了優化器的相關工作。這也就是軟解析的過程。
　　誠然，如果上面的2個假設中任有一個不成立，那麼優化器都將進行建立解析樹、生成執行計劃的動作。這個過程就叫硬解析。
　　建立解析樹、生成執行計劃對於sql的執行來說是開銷昂貴的動作，所以，應當極力避免硬解析，儘量使用軟解析。這就是在很多專案中，倡導開發設計人員對功能相同的程式碼要努力保持程式碼的一致性，以及要在程式中多使用繫結變數的原因。
/****************************************************/
大家都在說在Sql中使用了Bind Var（繫結變數）會提高不少效能，那他到底是如何提高效能的呢？
使用了Bind Var能提高效能主要是因為這樣做可以儘量避免不必要的硬分析（Hard Parse）而節約了時間，同時節約了大量的CPU資源。
當一個Client提交一條Sql給Oracle後，Oracle首先會對其進行解析（Parse），然後將解析結果提交給優化器（Optimiser）來進行優化而取得Oracle認為的最優的Query Plan，然後再按照這個最優的Plan來執行這個Sql語句（當然在這之中如果只需要軟解析的話會少部分步驟）。
但是，當Oracle接到Client提交的Sql後會首先在共享池（Shared Pool）裡面去查詢是否有之前已經解析好的與剛接到的這一個Sql完全相同的Sql（注意這裡說的是完全相同，既要求語句上的字元級別的完全相同，又要求涉及的物件也必須完全相同）。當發現有相同的以後解析器就不再對新的Sql在此解析而直接用之前解析好的結果了。這裡就節約瞭解析時間以及解析時候消耗的CPU資源。尤其是在OLTP中執行著的大量的短小Sql，效果就會比較明顯了。因為一條兩條Sql的時間可能不會有多少感覺，但是當量大了以後就會有比較明顯的感覺了。
上面說到了硬解析（Hard Parse），那這個Hard Parse到底是個啥呢？
Parse主要分為三種：
1、Hard Parse （硬解析）
2、Soft Parse （軟解析）
3、Soft Soft Parse（好像有些資料中並沒有將這個算在其中）
Hard Parse就是上面提到的對提交的Sql完全重新從頭進行解析（當在Shared Pool中找不到時候將會進行此操作），總共有一下5個執行步驟：
1：語法分析
2：許可權與物件檢查
3：在共享池中檢查是否有完全相同的之前完全解析好的—如果存在,直接跳過4和5,執行Sql（此時算soft parse）
4：選擇執行計劃
5：產生執行計劃
Soft Parse就如果是在Shared Pool中找到了與之完全相同的Sql解析好的結果後會跳過Hard Parse中的後面的兩個步驟。
Soft Soft Parse實際上是當設定了session_cursor_cache這個引數之後，Cursor被直接Cache在當前Session的PGA中的，在解析的時候只需要對其語法分析、許可權物件分析之後就可以轉到PGA中查詢了，如果發現完全相同的Cursor，就可以直接去取結果了，也就就是實現了Soft Soft Parse。
不過在計算解析次數的時候是隻計算Hard Parse和Soft Parse的（其實Soft Soft Parse好像也並不能算是做了Parse ）：
Soft Parse百分比計算：Round(100*(1-:hprs/:prse),2) [hprs：硬解析次數；prse：解析次數]
Parse比率計算： Round(100*(1-prse/exec) ,2) [exec：執行次數]
  軟解析過多的意思就是說session_cursor_cache的數值有可能小了，一些頻繁呼叫的SQL在Cache中找不到相同的Cursor。
 
3.7.SQL ordered by Sharable Memory:
 記錄了SQL佔用library cache的大小的TOP SQL。
Sharable Mem (b)：佔用library cache的大小。單位是byte。
 
3.8.SQL ordered by Version Count:
 記錄了SQL的開啟子游標的TOP SQL。
 在硬解析的過程中，程式會一直持有library cach latch，直到硬解析結束。硬解析結束以後，會為該SQL產生兩個遊標，一個是父遊標，另一個是子游標。父遊標裡主要包含兩種資訊：SQL文字以及優化目標（optimizer goal）。父遊標在第一次開啟時被鎖定，直到其他所有的session都關閉該遊標後才被解鎖。當父遊標被鎖定的時候是不能被交換出library cache的，只有在解鎖以後才能被交換出library cache，這時該父遊標對應的所有子游標也被交換出library cache。子游標包括遊標所有的資訊，比如具體的執行計劃、繫結變數等。子游標隨時可以被交換出library cache，當子游標被交換出library cache時，oracle可以利用父遊標的資訊重新構建出一個子遊標來，這個過程叫reload。可以使用下面的方式來確定reload的比率：
SELECT 100*sum(reloads)/sum(pins) Reload_Ratio FROM v$librarycache;
一個父遊標可以對應多個子遊標。子游標具體的個數可以從v$sqlarea的version_count欄位體現出來。而每個具體的子游標則全都在v$sql裡體現。當具體的繫結變數的值與上次的繫結變數的值有較大差異（比如上次執行的繫結變數的值的長度是6位，而這次執行的繫結變數的值的長度是200位）時或者當SQL語句完全相同，但是所引用的物件屬於不同的schema時，都會建立一個新的子游標
 
3.9.SQL ordered by Cluster Wait Time:
 記錄了叢集的等待時間的TOP SQL
 
其他管理員相關問題解析：
Load Profile
  Per Second Per Transaction
Redo size: 65,768.90 17,459.39
Logical reads: 31,757.56 8,430.54
Block changes: 271.06 71.96
Physical reads: 188.86 50.13
Physical writes: 11.90 3.16
User calls: 197.15 52.34
Parses: 241.40 64.08
Hard parses: 1.90 0.50
Sorts: 12.46 3.31
Logons: 0.15 0.04
Executes: 313.88 83.32
Transactions: 3.77 
 % Blocks changed per Read: 0.85 Recursive Call %: 95.92
Rollback per transaction %: 30.79  

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25850100/viewspace-700253/，如需轉載，請註明出處，否則將追究法律責任。