詳解statspack 報告

yantaicuiwei發表於2010-11-29

5.6 SQL統計資訊－多版本快取
在這一部分，主要是針對SQL語句的多版本進行排序。相同的SQL文字，但是不同屬性，比如物件owner不同，會話最佳化模式不同、型別不同、長度不同和繫結變數不同等等的語句，他們是不能共享的，所以再快取中會存在多個不同的版本。這當然就造成了資源上的更多的消耗。
每個語句的執行會產生兩個cursor，一個parent cursor，一個child cursor。
parent cursor中包含三部分內容：
1）SQL語句本身
2）handle，透過這個handle與library cache的hash table能夠根據hash value的值關聯
3）object，指向所有child cursor的指標。
child cursor中包含4部分內容：
1）handle，透過這個handle與parent的pointer關聯
2）object，這裡具體的作用不是很明白
3）heap0：存放該SQL語句中的物件標識，環境標識等，正是這些特徵確保了該SQL的唯一性。這裡的物件標識就是表示執行的使用者資訊和schema資訊等。環境標識就是例如optimizer_mode設定等。
4）heap6：存放該語句的執行計劃
所以，不同的使用者執行同一個語句，訪問同一張表，執行計劃可能是不同的。同一個使用者執行同一個語句，可能執行計劃也會不同。如不同的使用者訪問其架構裡面同樣的物件,此時SQL的雜湊雜湊值是一樣,但具體的v$sql中的資訊不一樣,還有繫結變數時開始系統估計的變數長度是30,而後傳進來的變數長度跳躍到80,則此時會生成另外一個body,再則根據繫結變數的傳值,發現這個資料在表中佔了大部分的行,走索引已經不大合適,要換成全表掃描,此時新的查詢方案將生成,body自然會增加.

SQL ordered by Version Count for DB: ORA92  Instance: ora92  Snaps: 13 -14
-> End Version Count Threshold:        20

Version
   Count  Executions   Hash Value
-------- ------------ ------------
      30       15,112   3531895589
Module: Das.exe
INSERT INTO T_DXH_DXHRECLOG (CALLERNO, CALLEENO, NOTIFYFLAG, SMS
TYPE, AREAID, LOGDATE) VALUES (:B4 , :B3 , 1, :B2 , :B1 , TO_CHA
R(SYSDATE, 'MMDD'))

小結：
對於出現在上面的可疑的sql語句，我們可以檢視語句相關的執行計劃，然後分析相關索引等是否合理。
透過語句檢視執行計劃的方法:
SELECT id,parent_id,LPAD(' ',4*(LEVEL-1))||operation||' '||options||' '||object_name "Execution plan" ,cost,cardinality,bytes
FROM (
SELECT p.* FROM v$sql_plan p,v$sql s WHERE p.address = s.ADDRESS
AND p.hash_value = s.HASH_VALUE
and p.hash_value = '&hash_value'
)
CONNECT BY PRIOR id = parent_id
START WITH id = 0;

 
5.2 SQL統計資訊－物理讀
這部分透過物理讀對SQL語句進行排序。這顯示引起大部分對這個系統進行讀取活動的SQL，即物理I/O。當我們的系統如果存在I/O瓶頸時，需要關注這裡I/O操作比較多的語句。
SQL ordered by Reads for DB: ORA92  Instance: ora92  Snaps: 13 -14
-> End Disk Reads Threshold:      1000
                                                     CPU      Elapsd
Physical Reads  Executions  Reads per Exec %Total Time (s)  Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
         4,187           24          174.5   15.8     0.79     52.99 1895519470
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
broken BOOLEAN := FALSE; BEGIN p_dxh_tmp_importUserInfo2(500); :
mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END I
F; END;
            538       21,504            0.0    2.0     5.92    241.61 1725988165
Module: Das.exe
begin P_DXH_AddSms(I_CALLERNO=>:V001,I_CALLEENO=>:V002,I_CALLTY
PE=>:V003,I_DXHHFLAG=>:V004,O_RET=>:V005);end;
……
5.3 SQL統計資訊－執行次數
這部分告訴我們在這段時間中執行次數最多的SQL語句。為了隔離某些頻繁執行的查詢，以觀察是否有某些更改邏輯的方法以避免必須如此頻繁的執行這些查詢，這可能是很有用的。或許一個查詢正在一個迴圈的內部執行，而且它可能在迴圈的外部執行一次，可以設計簡單的演算法更改以減少必須執行這個查詢的次數。即使它執行的飛快，任何被執行幾百萬次的操作都將開始耗盡大量的時間。
SQL ordered by Executions for DB: ORA92  Instance: ora92  Snaps: 13 -14
-> End Executions Threshold:       100
                                                CPU per    Elap per
Executions   Rows Processed   Rows per Exec    Exec (s)   Exec (s)  Hash Value
------------ --------------- ---------------- ----------- ---------- ----------
     102,491               0              0.0       0.00        0.00 1053795750
Module: Das.exe
COMMIT

      48,861          38,275              0.8       0.00        0.00  947217968
Module: Das.exe
SELECT T.AREAID FROM T_DXH_MOBILE S, T_DXH_AREA T WHERE S.MOBILE
SEGMENT = SUBSTR(:B1 ,1,7) AND T.AREACODE = S.AREACODE AND ROWNU
M = 1

5.4 SQL統計資訊－呼叫、解析次數
在這一部分，主要顯示PARSE與EXECUTIONS的對比情況。如果PARSE/EXECUTIONS>1，往往說明這個語句可能存在問題：沒有使用繫結變數，共享池設定太小，cursor_sharing被設定為exact，沒有設定session_cached_cursors等等問題。
SQL ordered by Parse Calls for DB: ORA92  Instance: ora92  Snaps: 13 -14
-> End Parse Calls Threshold:      1000

                           % Total
Parse Calls  Executions   Parses  Hash Value
------------ ------------ -------- ----------
      61,404       30,650    32.06 3303409220
Module: SvcProcessor.exe
begin P_DXH_UPDATESUBMITSTATUS(:V00001,:V00002,:V00003,:V00004);
end;
       1,661        1,661     0.87  140223014
Module: SvcProcessor.exe
SELECT SERIALNO, PID, SERVICEID, SMSCONTENT, REPORTFLAG, ORGADDR
, DESTADDR, FEEADDR, FEETYPE, FEEUSERTYPE, FEECODE, SPID FROM T_
DXH_OPENDETECT WHERE LOCKFLAG = :B1

5.5 SQL統計資訊－共享記憶體佔用
在這一部分，主要是針對shared memory佔用的情況進行排序。
SQL ordered by Sharable Memory for DB: ORA92  Instance: ora92  Snaps: 13 -14
-> End Sharable Memory Threshold:   1048576

Sharable Mem (b)  Executions  % Total  Hash Value
---------------- ------------ ------- ------------
       1,115,384       15,112     0.2   3531895589
Module: Das.exe
INSERT INTO T_DXH_DXHRECLOG (CALLERNO, CALLEENO, NOTIFYFLAG, SMS
TYPE, AREAID, LOGDATE) VALUES (:B4 , :B3 , 1, :B2 , :B1 , TO_CHA
R(SYSDATE, 'MMDD'))
 5、SQL統計資訊
接下來的部分，是關於SQL的統計資訊，分為6塊來統計排序：
ordered by buffer gets
ordered by Physical reads
ordered by Executions
ordered by Parse Calls
ordered by Sharable Memory
ordered by Version Count


5.1 SQL統計資訊－邏輯讀
這一部分，透過Buffer Gets對SQL語句進行排序，即透過它執行了多少個邏輯I/O來排序。頂端的註釋表明一個PL/SQL單元的快取獲得(Buffer Gets)包括被這個程式碼塊執行的所有SQL語句的Buffer Gets。因此將經常在這個列表的頂端看到PL/SQL過程，因為儲存過程執行的單獨的語句的數目被總計出來。
在這裡的Buffer Gets是一個累積值，所以這個值大並不一定意味著這條語句的效能存在問題。通常我們可以透過對比該條語句的Buffer Gets和physical reads值，如果這兩個比較接近，肯定這條語句是存在問題的，我們可以透過執行計劃來分析，為什麼physical reads的值如此之高。另外，我們在這裡也可以關注gets per exec的值，這個值如果太大，表明這條語句可能使用了一個比較差的索引或者使用了不當的表連線。
另外說明一點：大量的邏輯讀往往伴隨著較高的CPU消耗。所以很多時候我們看到的系統CPU將近100％的時候，很多時候就是SQL語句造成的，這時候我們可以分析一下這裡邏輯讀大的SQL。
SQL ordered by Gets for DB: ORA92  Instance: ora92  Snaps: 13 -14
-> End Buffer Gets Threshold:     10000
-> Note that resources reported for PL/SQL includes the resources used by
   all SQL statements called within the PL/SQL code.  As individual SQL
   statements are also reported, it is possible and valid for the summed
   total % to exceed 100
                                                     CPU      Elapsd
  Buffer Gets    Executions  Gets per Exec  %Total Time (s)  Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
13,367,435          171       78,172.1   68.3   259.36    353.19 3790040751
這裡CPU Timev(s)就是該sql語句消耗的總的cpu時間，%Total則是代表該語句所佔用的cpu時間在所有所有cpu time中的比例。
這裡的CPU time(s)可以表示Service Time，Elapsd Time(s)可以表示Wait Time。如果Elapsd Time(s)太大，我們可以認為sql在執行的過程中等待的時間過長，從而造成%Total值過大，這種情況下我們需要查詢等待的具體原因。
如果Elapsd Time(s)很小，基本可以忽略等待的情況，然而%Total的值同樣過大，表明這條語句在整個報告的時間段中佔用的cpu時間片的比較大，在cpu存在效能瓶頸的時候，我們就需要最佳化這條sql，該sql可能存在過度的解析或者過多的邏輯讀。
當然這也跟呼叫的頻度有關係。

DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
broken BOOLEAN := FALSE; BEGIN P_DXH_DEALOVERTIMEDXHREC; :mydate
:= next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END
…… 4、TOP 5及其他等待事件資訊（續4）

Wait Events for DB: ORA92  Instance: ora92  Snaps: 13 -14
-> s  - second
-> cs - centisecond -     100th of a second
-> ms - millisecond -    1000th of a second
-> us - microsecond - 1000000th of a second
-> ordered by wait time desc, waits desc (idle events last)
                                                                   Avg
                                                     Total Wait   wait    Waits
Event                               Waits   Timeouts   Time (s)   (ms)     /txn
---------------------------- ------------ ---------- ---------- ------ --------
db file scattered read              2,415          0          6      3      0.0
db file sequential read             7,303          0         65      9      0.1
如果這兩個Average Wait time明顯低於7 ms，表明磁碟子系統並不存在瓶頸，我們不需要把焦點集中在磁碟子系統上。相反，如果這個指標明顯高於10ms，那麼我們就需要關注我們的sotrage是否存在瓶頸。
10ms是個經驗值，一個random IO的理論時間是：
7ms = 4-5ms(磁碟平均尋道時間）+ 2ms (傳輸時間) + 一些其它的消耗
如果不考慮file system cache hit(raw device/direct IO)  以及storage cache hit , 同時沒有磁碟競爭，那麼，db file sequntial read的時間就會是 7ms左右。
而由於file system cache hit和storage cache hit的情況下，沒有磁碟競爭的系統db file sequntial read 會小於 7ms。
如果有磁碟競爭，而且競爭產生的延遲> file system cache hit和storage cache hit的好處，就會大於7ms .
10ms 可以說是一個經驗值，就是磁碟競爭產生的延遲比較高了。
log file sync                      74,324          0        101      1      1.0
enqueue                               729          0         88    121      0.0
SQL*Net message from dblink           482          0         20     42      0.0
db file parallel write                725          0         14     19      0.0
process startup                         8          0          4    440      0.0
latch free                          1,307      1,300          2      1      0.0
log file parallel write            67,042          0          2      0      0.9
control file sequential read          269          0          1      3      0.0
single-task message                    24          0          1     33      0.0
control file parallel write           325          0          1      2      0.0
buffer busy waits                   3,368          0          1      0      0.0
log file switch completion             19          0          0     20      0.0
direct path read                      288          0          0      0      0.0
LGWR wait for redo copy             1,032          0          0      0      0.0
SQL*Net more data to client         1,390          0          0      0      0.0
kksfbc child completion                 1          1          0     10      0.0
log file sequential read                2          0          0      5      0.0
direct path write                     128          0          0      0      0.0
library cache pin                      14          0          0      0      0.0
SQL*Net more data from dblin            4          0          0      0      0.0
log file single write                   2          0          0      1      0.0
SQL*Net message to dblink             482          0          0      0      0.0
buffer deadlock                        30         30          0      0      0.0
SQL*Net message from client       436,773          0    143,221    328      5.8
jobq slave wait                     2,688      1,664      6,688   2488      0.0
wakeup time manager                    27         27        791  29297      0.0
SQL*Net message to client         436,772          0          0      0      5.8 4、TOP 5及其他等待事件資訊（續3）
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                                                     % Total
Event                                               Waits    Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
CPU time                                                          361    54.14
這個CPU time實際上就是Service time，放在這裡，我們也可以看到wait time所佔用的總體比例，在一定的程度上，可以很直接的反映系統的等待是否嚴重。當然這個值本身當然是越小越好，小表明系統的cpu佔用小，但後面的百分比越高越好，越高就表明用於等待的時間越小。YAPP的方法就可以在這裡方便的套用，有時候可以基本上確定需要調優的方向。
Top 5裡有 CPU Time，一方面確實表明等待的情況相對來說比較少。但是，這裡的高CPU消耗，有可能是用於過度的hard parse或者過高的邏輯讀的。所以並不是說一定是好事。這時候，可以再結合當時的os層面的cpu消耗情況來判斷，如果當時的cpu比較空，那基本就沒問題。如果當時cpu已經成為瓶頸，那就需要去調sql。Cpu使用的程度，我們可以根據這裡的值來大概計算。比如，這一份報告的是的時間是14.05分鐘，我們可以計算總的cpu時間為：14.05*60*2(cpu個數)=1686秒，所以cpu的佔用為361/1686,大概為21％，在採集報告的這段時間，cpu是比較空閒的，也不足以成為效能的瓶頸。
Top 5裡有 CPU Time 不好說有沒有問題。但是，這個事件排第一，總比其他事件排第一要好。
Top 5裡沒有 CPU Time 肯定有問題。
log file sync                                      74,324         101    15.22
enqueue                                               729          88    13.28
db file sequential read                             7,303          65     9.76
SQL*Net message from dblink                           482          20     3.05 4、TOP 5及其他等待事件資訊（續2）
12)        DB File Parallel Write：檔案被DBWR並行寫時發生。解決辦法：改善IO效能。
13)        DB File Single Write：當檔案頭或別的單獨塊被寫入時發生，這一等待直到所有的I/O呼叫完成。解決辦法：改善IO效能。
14)        DB FILE Scattered Read：當掃描整個段來根據初始化引數db_file_multiblock_read_count讀取多個塊時發生，因為資料可能分散在不同的部分，這與分條或分段）相關，因此通常需要多個分散的讀來讀取所有的資料。等待時間是完成所有I/O呼叫的時間。解決辦法：改善IO效能。
15)        DB FILE Sequential Read：當前臺程式對資料檔案進行常規讀時發生，包括索引查詢和別的非整段掃描以及資料檔案塊丟棄等待。等待時間是完成所有I/O呼叫的時間。解決辦法：改善IO效能。
16)        Direct Path Read：一般直接路徑讀取是指將資料塊直接讀入PGA中。一般用於排序、並行查詢和read ahead操作。這個等待可能是由於I/O造成的。使用非同步I/O模式或者限制排序在磁碟上，可能會降低這裡的等待時間。
17)        direct path write：直接路徑寫該等待發生在，系統等待確認所有未完成的非同步I/O 都已寫入磁碟。對於這一寫入等待，我們應該找到I/O 操作最為頻繁的資料檔案(如果有過多的排序操作，很有可能就是臨時檔案)，分散負載，加快其寫入操作。如果系統存在過多的磁碟排序，會導致臨時表空間操作頻繁，對於這種情況，可以考慮使用Local管理表空間，分成多個小檔案，寫入不同磁碟或者裸裝置。
18)        control file parallel write：當server 程式更新所有控制檔案時，這個事件可能出現。如果等待很短，可以不用考慮。如果等待時間較長，檢查存放控制檔案的物理磁碟I/O 是否存在瓶頸。
多個控制檔案是完全相同的複製，用於映象以提高安全性。對於業務系統，多個控制檔案應該存放在不同的磁碟上，一般來說三個是足夠的，如果只有兩個物理硬碟，那麼兩個控制檔案也是可以接受的。在同一個磁碟上儲存多個控制檔案是不具備實際意義的。減少這個等待，可以考慮如下方法:①減少控制檔案的個數(在確保安全的前提下)。②如果系統支援，使用非同步IO。③轉移控制檔案到IO 負擔輕的物理磁碟。
19)        control file sequential read
control file single write ：控制檔案連續讀/控制檔案單個寫對單個控制檔案I/O 存在問題時，這兩個事件會出現。如果等待比較明顯，檢查單個控制檔案，看存放位置是否存在I/O 瓶頸。

對於常見的一些IDLE wait事件舉例：
dispatcher timer                  
lock element cleanup               
Null event                        
parallel query dequeue wait        
parallel query idle wait - Slaves  
pipe get                           
PL/SQL lock timer                  
pmon timer- pmon                  
rdbms ipc message                  
slave wait                        
smon timer                        
SQL*Net break/reset to client      
SQL*Net message from client        
SQL*Net message to client         
SQL*Net more data to client        
virtual circuit status            
client message                     
SQL*Net message from client   
下面是關於這裡的常見的等待事件和解決方法的一個快速預覽
等待事件        一般解決方法
Sequential Read        調整相關的索引和選擇合適的驅動行源
Scattered Read         表明出現很多全表掃描。最佳化code，cache小表到記憶體中。
Free Buffer         增大DB_CACHE_SIZE，增大checkpoint的頻率，最佳化程式碼
Buffer Busy Segment header        增加freelist或者freelistgroups
Buffer Busy Data block        隔離熱塊；使用反轉索引；使用更小的塊；增大表的initrans
Buffer Busy Undo header        增加回滾段的數量或者大小
Buffer Busy Undo block        Commit more；增加回滾段的數量或者大小
Latch Free         檢查具體的等待latch型別，解決方法參考後面介紹
Enqueue–ST         使用本地管理的表空間或者增加預分配的盤區大小
Enqueue–HW         在HWM之上預先分配盤區
Enqueue–TX4         在表或者索引上增大initrans的值或者使用更小的塊
Log Buffer Space         增大LOG_BUFFER，改善I/O
Log File Switch         增加或者增大日誌檔案
Log file sync         減小提交的頻率；使用更快的I/O；或者使用裸裝置
Write complete waits        增加DBWR；提高CKPT的頻率； 4、TOP 5及其他等待事件資訊（續）

4)        latch free：當閂鎖丟失率高於0.5%時，需要調整這個問題。詳細的我們在後面的Latch Activity for DB部分說明。
5)        Enqueue 佇列是一種鎖，保護一些共享資源，防止併發的DML操作。佇列採用FIFO策略，注意latch並不是採用的FIFO機制。比較常見的有3種型別的佇列：ST佇列，HW佇列，TX4佇列。
ST Enqueue的等待主要是在字典管理的表空間中進行空間管理和分配時產生的。解決方法：1）將字典管理的表空間改為本地管理模式 2）預先分配分割槽或者將有問題的字典管理的表空間的next extent設定大一些。
HW Enqueue是用於segment的HWM的。當出現這種等待的時候，可以透過手工分配etents來解決。
TX4 Enqueue等待是最常見的等待情況。通常有3種情況會造成這種型別的等待：1）唯一索引中的重複索引。解決方法：commit或者rollback以釋放佇列。 2）對同一個點陣圖索引段（bitmap index fragment）有多個update，因為一個bitmap index fragment可能包含了多個rowid，所以當多個使用者更新時，可能一個使用者會鎖定該段，從而造成等待。解決方法同上。3）有多個使用者同時對一個資料塊作update，當然這些DML操作可能是針對這個資料塊的不同的行，如果此時沒有空閒的ITL槽，就會產生一個block-level鎖。解決方法：增大表的initrans值使建立更多的ITL槽；或者增大表的pctfree值，這樣oracle可以根據需要在pctfree的空間建立更多的ITL槽；使用smaller block size，這樣每個塊中包含行就比較少，可以減小衝突發生的機會。
6)        Free Buffer：這個等待事件表明系統正在等待記憶體中的可用空間，這說明當前Buffer 中已經沒有Free 的記憶體空間。如果應用設計良好，SQL 書寫規範，充分繫結變數，那這種等待可能說明Buffer Cache 設定的偏小，你可能需要增大DB_CACHE_SIZE。該等待也可能說明DBWR 的寫出速度不夠，或者磁碟存在嚴重的競爭，可以需要考慮增加檢查點、使用更多的DBWR 程式，或者增加物理磁碟的數量,分散負載，平衡IO。
7)        Log file single write：該事件僅與寫日誌檔案頭塊相關，通常發生在增加新的組成員和增進序列號時。頭塊寫單個進行，因為頭塊的部分資訊是檔案號，每個檔案不同。更新日誌檔案頭這個操作在後臺完成，一般很少出現等待，無需太多關注。
8)        log file parallel write：從log buffer 寫redo 記錄到redo log 檔案，主要指常規寫操作(相對於log file sync)。如果你的Log group 存在多個組成員，當flush log buffer 時，寫操作是並行的，這時候此等待事件可能出現。儘管這個寫操作並行處理，直到所有I/O 操作完成該寫操作才會完成(如果你的磁碟支援非同步IO或者使用IO SLAVE，那麼即使只有一個redo log file member,也有可能出現此等待)。這個引數和log file sync 時間相比較可以用來衡量log file 的寫入成本。通常稱為同步成本率。改善這個等待的方法是將redo logs放到I/O快的盤中，儘量不使用raid5，確保表空間不是處在熱備模式下，確保redo log和data的資料檔案位於不同的磁碟中。
9)        log file sync：當一個使用者提交或回滾資料時，LGWR將會話的redo記錄從日誌緩衝區填充到日誌檔案中，使用者的程式必須等待這個填充工作完成。在每次提交時都出現，如果這個等待事件影響到資料庫效能，那麼就需要修改應用程式的提交頻率, 為減少這個等待事件，須一次提交更多記錄，或者將重做日誌REDO LOG 檔案訪在不同的物理磁碟上，提高I/O的效能。
10)        log buffer space：日誌緩衝區寫的速度快於LGWR寫REDOFILE的速度,可以增大日誌檔案大小，增加日誌緩衝區的大小，或者使用更快的磁碟來寫資料。
11)        logfile switch：通常是因為歸檔速度不夠快。表示所有的提交(commit)的請求都需要等待"日誌檔案切換"的完成。Log file Switch 主要包含兩個子事件:
log file switch (archiving needed) 這個等待事件出現時通常是因為日誌組迴圈寫滿以後，第一個日誌歸檔尚未完成，出現該等待。出現該等待，可能表示io 存在問題。解決辦法:①可以考慮增大日誌檔案和增加日誌組；②移動歸檔檔案到快速磁碟；③調整log_archive_max_processes。
log file switch (checkpoint incomplete) 當日志組都寫完以後，LGWR 試圖寫第一個log file，如果這時資料庫沒有完成寫出記錄在第一個log file 中的dirty 塊時(例如第一個檢查點未完成)，該等待事件出現。該等待事件通常表示你的DBWR 寫出速度太慢或者IO 存在問題。為解決該問題，你可能需要考慮增加額外的DBWR 或者增加你的日誌組或日誌檔案大小，或者也可以考慮增加checkpoint的頻率。 4、TOP 5及其他等待事件資訊
/* oracle等待事件是衡量oracle執行狀況的重要依據及指示，等待事件分為兩類：空閒等待事件和非空閒等待事件, TIMED_STATISTICS = TRUE 那麼等待事件按等待的時間排序，= FALSE那麼事件按等待的數量排序。執行statspack期間必須session上設定TIMED_STATISTICS = TRUE，否則統計的資料將失真。空閒等待事件是oracle正等待某種工作，在診斷和最佳化資料庫時候，不用過多注意這部分事件，非空閒等待事件專門針對oracle的活動，指資料庫任務或應用程式執行過程中發生的等待，這些等待事件是我們在調整資料庫應該關注的。
    對於常見的等待事件，說明如下：
1)        db file scattered read
該事件通常與全表掃描或者fast full index scan有關。因為全表掃描是被放入記憶體中進行的進行的，通常情況下基於效能的考慮，有時候也可能是分配不到足夠長的連續記憶體空間，所以會將資料塊分散(scattered)讀入Buffer Cache中。該等待過大可能是缺少索引或者沒有合適的索引(可以調整optimizer_index_cost_adj) 。這種情況也可能是正常的，因為執行全表掃描可能比索引掃描效率更高。當系統存在這些等待時，需要透過檢查來確定全表掃描是否必需的來調整。因為全表掃描被置於LRU(Least Recently Used，最近最少適用)列表的冷端(cold end)，對於頻繁訪問的較小的資料表，可以選擇把他們Cache 到記憶體中，以避免反覆讀取。當這個等待事件比較顯著時，可以結合v$session_longops 動態效能檢視來進行診斷，該檢視中記錄了長時間(執行時間超過6 秒的)執行的事物，可能很多是全表掃描操作(不管怎樣，這部分資訊都是值得我們注意的)。
關於引數OPTIMIZER_INDEX_COST_ADJ＝n：該引數是一個百分比值，預設值為100，可以理解為FULL SCAN COST/INDEX SCAN COST。當n%* INDEX SCAN COST2)        db file sequential read：該事件說明在單個資料塊上大量等待，該值過高通常是由於表間連線順序很糟糕（沒有正確選擇驅動行源），或者使用了非選擇性索引。透過將這種等待與statspack報表中已知其它問題聯絡起來(如效率不高的sql),透過檢查確保索引掃描是必須的，並確保多表連線的連線順序來調整。
3)        buffer busy wait：當緩衝區以一種非共享方式或者如正在被讀入到緩衝時,就會出現該等待。該值不應該大於1%。當出現等待問題時，可以檢查緩衝等待統計部分(或V$WAITSTAT)，確定該等待發生在什麼位置：
a)        如果等待是否位於段頭(Segment Header)。這種情況表明段中的空閒列表（freelist）的塊比較少。可以考慮增加空閒列表(freelist，對於Oracle8i DMT)或者增加freelist groups(在很多時候這個調整是立竿見影的(alter table tablename strorage(freelists 2))，在8.1.6之前，這個freelists引數不能動態修改;在8.1.6及以後版本，動態修改feelists需要設定COMPATIBLE至少為8.1.6)。也可以增加PCTUSED與PCTFREE之間距離（PCTUSED-to-pctfree gap），其實就是說降低PCTUSED的值，儘快使塊返回freelist列表被重用。如果支援自動段空間管理（ASSM），也可以使用ASSM模式，這是在ORALCE 920以後的版本中新增的特性。
b)        如果這一等待位於undo header，可以透過增加回滾段(rollback segment)來解決緩衝區的問題。
c)        如果等待位於undo block上，我們需要增加提交的頻率，使block可以儘快被重用；使用更大的回滾段；降低一致讀所選擇的表中資料的密度；增大DB_CACHE_SIZE。
d)        如果等待處於data block，表明出現了hot block，可以考慮如下方法解決： ①將頻繁併發訪問的表或資料移到另一資料塊或者進行更大範圍的分佈(可以增大pctfree值 ，擴大資料分佈，減少競爭)，以避開這個"熱點"資料塊。②也可以減小資料塊的大小，從而減少一個資料塊中的資料行數，降低資料塊的熱度，減小競爭；③檢查對這些熱塊操作的SQL語句，最佳化語句。④增加hot block上的initrans值。但注意不要把initrans值設定的過於高了，通常設定為5就足夠了。因為增加事務意味著要增加ITL事務槽，而每個ITL事務槽將佔用資料塊中24個位元組長度。預設情況下，每個資料塊或者索引塊中是ITL槽是2個，在增加initrans的時候，可以考慮增大資料塊所在的表的PCTFREE值，這樣Oracle會利用PCTFREE部分的空間增加ITL slot數量，最大達到maxtrans指定。
e)        如果等待處於index block，應該考慮重建索引、分割索引或使用反向鍵索引。為了防止與資料塊相關的緩衝忙等待，也可以使用較小的塊，在這種情況下，單個塊中的記錄就較少，所以這個塊就不是那麼"繁忙"。或者可以設定更大的PCTFREE,使資料擴大物理分佈，減少記錄間的熱點競爭。在執行DML (insert/update/ delete)時，Oracle向資料塊中寫入資訊，對於多事務併發訪問的資料表，關於ITL的競爭和等待可能出現，為了減少這個等待，可以增加initrans，使用多個ITL槽。在Oracle9i 中，可以使用ASSM這個新特性Oracle 使用點陣圖來管理空間使用，減小爭用。 3、例項有效性資訊
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            Buffer Nowait %:   99.98       Redo NoWait %:    100.00
            Buffer  Hit   %:   99.87    In-memory Sort %:    100.00
            Library Hit   %:   99.67        Soft Parse %:     96.82
         Execute to Parse %:   80.93         Latch Hit %:     96.10
Parse CPU to Parse Elapsd %:    6.93     % Non-Parse CPU:     99.88

/* 例項的有效性，這部分值越接近100越好，分項內容詳細說明如下：
1)        Buffer Nowait %：在緩衝區中獲取Buffer的未等待比率。Buffer Nowait的這個值一般需要大於99%。否則可能存在爭用，可以在後面的等待事件中進一步確認。
2)        Redo NoWait %：在Redo緩衝區獲取Buffer空間的未等待比率。當redo buffer達到1M時，就需要寫到redo log檔案，所以一般當redo buffer設定超過1M，不太可能存在等待buffer空間分配的情況。當前，一般設定為2M的redo buffer，對於記憶體總量來說，應該不是一個太大的值。
3)        Buffer Hit %：資料塊在資料緩衝區中的命中率，通常應在95%以上。否則，小於95%，需要調整重要的引數，小於90%可能是要加db_cache_size。一個高的命中率，不一定代表這個系統的效能是最優的，比如大量的非選擇性的索引被頻繁訪問，就會造成命中率很高的假相（大量的db file sequential read），但是一個比較低的命中率，一般就會對這個系統的效能產生影響，需要調整。命中率的突變，往往是一個不好的資訊。如果命中率突然增大，可以檢查top buffer get SQL，檢視導致大量邏輯讀的語句和索引，如果命中率突然減小，可以檢查top physical reads SQL，檢查產生大量物理讀的語句，主要是那些沒有使用索引或者索引被刪除的。
4)        In-memory Sort %：在記憶體中的排序率。如果低於95%，可以透過適當調大初始化引數PGA_AGGREGATE_TARGET或者SORT_AREA_SIZE來解決，注意這兩個引數設定作用的範圍時不同的，SORT_AREA_SIZE是針對每個session設定的，PGA_AGGREGATE_TARGET則時針對所有的sesion的。
5)        Library Hit %：STATEMENT在共享區的命中率，通常應該保持在95%以上，否則需要要考慮：加大共享池；使用繫結變數；修改cursor_sharing等引數。
6)        Soft Parse %：sql在共享區的命中率，小於<95%,需要考慮繫結，如果低於80%，那麼就可以認為sql基本沒有被重用。
7)        Execute to Parse %：一個語句執行和分析了多少次的度量。計算公式為：Execute to Parse =100 * (1 - Parses/Executions)。本例中，差不多每execution 5次需要一次parse。所以如果系統Parses > Executions，就可能出現該比率小於0的情況。該值<0通常說明shared pool設定或者語句效率存在問題，造成反覆解析，reparse可能較嚴重,或者是可能同snapshot有關，通常說明資料庫效能存在問題。
8)        Latch Hit %：要確保>99%，否則存在嚴重的效能問題。當該值出現問題的時候，我們可以藉助後面的等待時間和latch分析來查詢解決問題。
9)        Parse CPU to Parse Elapsd %：計算公式為：Parse CPU to Parse Elapsd %= 100*(parse time cpu / parse time elapsed)。即：解析實際執行時間/(解析實際執行時間+解析中等待資源時間)。如果該比率為100%，意味著CPU等待時間為0，沒有任何等待。
10)        % Non-Parse CPU：計算公式為：% Non-Parse CPU =round(100*1-PARSE_CPU/TOT_CPU),2)。如果這個值比較小，表示解析消耗的CPU時間過多。與PARSE_CPU相比，如果TOT_CPU很高，這個比值將接近100%，這是很好的，說明計算機執行的大部分工作是執行查詢的工作，而不是分析查詢的工作。

Shared Pool Statistics        Begin   End
                               ------  ------
             Memory Usage %:   32.87   33.12
    % SQL with executions>1:   80.00   82.69
  % Memory for SQL w/exec>1:   77.62   80.70

1)        Memory Usage %：正在使用的共享池的百分率。這個數字應該長時間穩定在75%～90%。如果這個百分比太低，表明共享池設定過大，帶來額外的管理上的負擔，從而在某些條件下會導致效能的下降。如果這個百分率太高，會使共享池外部的元件老化，如果SQL語句被再次執行，這將使得SQL語句被硬解析。在一個大小合適的系統中，共享池的使用率將處於75%到略低於90%的範圍內。
2)        % SQL with executions>1：這是在共享池中有多少個執行次數大於一次的SQL語句的度量。在一個趨向於迴圈執行的系統中，必須認真考慮這個數字。在這個迴圈系統中，在一天中相對於另一部分時間的部分時間裡執行了一組不同的SQL語句。在共享池中，在觀察期間將有一組未被執行過的SQL語句，這僅僅是因為要執行它們的語句在觀察期間沒有執行。只有系統連續執行相同的SQL語句組，這個數字才會接近100%。這裡顯示，在這個共享池中幾乎有80%的SQL語句在14分鐘的觀察視窗中執行次數多於一次。剩下的20%的語句可能已經在那裡了--系統只是沒有去執行。
3)        % Memory for SQL w/exec>1：這是與不頻繁使用的SQL語句相比，頻繁使用的SQL語句消耗記憶體多少的一個度量。這個數字將在總體上與% SQL with executions>1非常接近，除非有某些查詢任務消耗的記憶體沒有規律。在穩定狀態下，總體上會看見隨著時間的推移大約有75%～85%的共享池被使用。如果Statspack報表的時間視窗足夠大到覆蓋所有的週期，執行次數大於一次的SQL語句的百分率應該接近於100%。這是一個受觀察之間持續時間影響的統計數字。可以期望它隨觀察之間的時間長度增大而增大。

小結：透過ORACLE的例項有效性統計資料，我們可以獲得大概的一個整體印象，然而我們並不能由此來確定資料執行的效能。當前效能問題的確定，我們主要還是依靠下面的等待事件來確認。我們可以這樣理解兩部分的內容，hit統計幫助我們發現和預測一些系統將要產生的效能問題，由此我們可以做到未雨綢繆。而wait事件，就是表明當前資料庫已經出現了效能問題需要解決，所以是亡羊補牢的性質。
接下來，開始檢視wait事件。 2、例項負載檔資訊
Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:            422,086.46              4,706.23
              Logical reads:             23,200.54                258.68
              Block changes:              3,080.59                 34.35
             Physical reads:                 31.46                  0.35
            Physical writes:                104.38                  1.16
                 User calls:                409.32                  4.56
                     Parses:                227.20                  2.53
                Hard parses:                  7.22                  0.08
                      Sorts:                213.87                  2.38
                     Logons:                  0.85                  0.01
                   Executes:              1,191.32                 13.28
               Transactions:                 89.69
/* 下面詳細說明Load Profile各項含義
Redo size:每秒產生的日誌大小(單位位元組)，可標誌資料變更頻率, 資料庫任務的繁重與否。
Logical reads:平決每秒產生的邏輯讀的block數。Logical Reads= Consistent Gets + DB Block Gets
Block changes:每秒block變化數量，資料庫事物帶來改變的塊數量。
Physical reads:平均每秒資料庫從磁碟讀取的block數。
Physical writes:平均每秒資料庫寫磁碟的block數。Physical reads + Physical writes可以近似表徵這個系統的IOPS。
User calls:每秒使用者呼叫次數。
Parses:每秒解析次數，包括soft parse和hard parse兩種數量的綜合。
Hard parses:每秒產生的硬解析次數，硬解析過高，就可能說明你繫結使用的不好，也可能是共享池設定不合理。這時候可以啟用引數cursor_sharing=similar|force，該引數預設值為exact。但該引數設定為similar時，存在bug，可能導致執行計劃的不優。
Sorts:每秒產生的排序次數。
Logons:每秒登陸的次數。
Executes:每秒執行次數。如果只看這個引數沒有太大的意義，可以結合前面其他的引數一起來考慮。結合Parses/ Executes來考慮，我們就可以知道解析的頻繁程度。Hard parses/Executes就可以看出硬解析的情況是否過度。
Transactions:每秒產生的事務數，反映資料庫任務繁重與否。
  % Blocks changed per Read:   13.28    Recursive Call %:     80.21
Rollback per transaction %:    0.03       Rows per Sort:      2.84

/* Load Profile 續
1)        % Blocks changed per Read：在每一次邏輯讀中更改的塊的百分比。
2)        Rollback per transaction %:看回滾率是不是很高，因為回滾很耗資源 ,如果回滾率過高,可能說明你的資料庫經歷了太多的無效操作 ,過多的回滾可能還會帶來Undo Block的競爭 該引數計算公式如下: Round(User rollbacks / (user commits + user rollbacks) ,4)* 100% 。
3)        Recursive Call %:遞迴呼叫的百分比，如果有很多PL/SQL，那麼這個值就會比較高。
4)        Rows per Sort:平均每次排序操作的行數。 1、報表頭資訊
/* 報表頭資訊，資料庫例項相關資訊，包括資料庫名稱、ID、版本號及主機明等資訊。
   另外，重點還需要關注一下報告產生的時間跨度（在這裡是14分鐘），以及併發數（在這裡是272）。
DB Name         DB Id    Instance     Inst Num Release     Cluster Host
------------ ----------- ------------ -------- ----------- ------- ------------
ORA92         1924035339 ora92               1 9.2.0.6.0   NO      jsdxh_db02

              Snap Id     Snap Time      Sessions Curs/Sess Comment
            --------- ------------------ -------- --------- -------------------
Begin Snap:        13 14-Jul-07 00:18:52      274  55,345.0
  End Snap:        14 14-Jul-07 00:32:55      272  55,823.8
   Elapsed:               14.05 (mins)

Cache Sizes (end)
~~~~~~~~~~~~~~~~~
               Buffer Cache:     5,120M      Std Block Size:          8K
           Shared Pool Size:       400M          Log Buffer:      2,048K 說在前面，很容易被忽略的幾個點：在讀報告的時候，我們首先需要看清楚，留意3個內容，這份報告所對應的資料庫版本，cluster方式，以及報告的時間段。尤其需要注意的就是時間段，脫離了時間段的statspck將是毫無意義的，甚至會得出錯誤的結果。

YAPP方法：
傳統的最佳化資料庫的指標是各種命中率的統計，並以命中率作為最佳化的目標。隨著系統和業務模式的發展， 這種最佳化的方法已經過時，YAPP方法由此誕生。YAPP方法的最終目標就是縮短response time。
Response Time = Service Time + Wait Time
Service Time = CPU Parse + CPU Recursive + CPU Other
這裡的CPU Other可以理解為是SQL語句的執行時間（包括邏輯讀等）。
CPU ther = CPU used by this session - parse time cpu - recursive cpu usage
所以最佳化的最終目標定位在Service Time 和 Wait Time上：
1）當效能問題在Service Time上時，由上面的公式可以看到，解決問題的方向就是SQL語句的最佳化上面。2）如果效能問題在Wait Time上，則解決的方向就需要找到具體的等待事件了。如果等待時間在整個響應時間中佔較大的比例，並且主要是塊讀取相關的等待時，下一步就是找出哪些SQL造成了過多的物理讀，可以檢視statspack報告中的SQL ordered by Reads部分。

對於Service Time的問題，可以具體分析其中幾種時間的比重：
CPU ther = CPU used by this session - parse time cpu - recursive cpu usage
1）如果CPU Other（執行時間）在整個響應時間中佔較大的比例，那麼下一步就是找出那些造成了最多邏輯IO的SQL語句，可以從statspack報告的SQL ordered by Gets部分找到。
2）如果parse time cpu（分析時間）在整個響應時間中佔較大的比例，那麼下一步就是查詢哪些SQL分析過多，這在statspack報告中在SQL ordered by Parse Calls中列出。

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

相關文章