效能優化 - Oracle Tuning 總結 2-1 Statspack

 

2.2 對statspack報告的分析
從上面的描述可以看出，產生一個statspack報告是比較簡單的，但是如何讀懂statspack報告卻不是那麼容易，需要對Oracle的體系架構、記憶體結構、等待事件

以及應用系統有充分的瞭解，加上不斷的實踐，才能基本讀懂statspack報告並且從報告中找到調整優化Oracle的途徑。
下面接合一個實際的statspack報告，大致分析一下。

2.2.1 基本資訊分析
DB Name         DB Id    Instance     Inst Num Release     OPS Host
------------ ----------- ------------ --------          ----------- ---      --------- ---
RES           2749170756 res                 1 8.1.7.0.0   NO res

                Snap Id     Snap Time      Sessions
                ------- ------------------ --------
Begin Snap:          2 26-Jul-03 16:37:08       38
   End Snap:          3 26-Jul-03 17:03:23       38
    Elapsed:                  26.25 (mins)

Statspack報告首先描述了資料庫的基本情況，比如資料庫名、例項名、例項個數、oracle版本號等等；然後是該報告的開始快照和結束快照的資訊，包括 snap

id , snap time 等等；最後是該報告經過的時間跨度，單位是分鐘(mins)。

Cache Sizes
~~~~~~~~~~~
db_block_buffers:      61440          log_buffer:     163840
db_block_size:         8192     shared_pool_size:   52428800

然後描述了Oracle記憶體結構中幾個重要的引數。

2.2.2 記憶體資訊分析
Load Profile
~~~~~~~~~~~~                       Per Second       Per Transaction
                                   ---------------       ---------------
              Redo size:              4,834.87             11,116.67
          Logical reads:                405.53                932.43
          Block changes:                 60.03                138.02
          Physical reads:                138.63                318.75
          Physical writes:                 54.27                124.79
          User calls:                     62.69                144.13
          Parses:                        19.14                 44.00
          Hard parses:                    2.26                  5.20
                  Sorts:                  1.83                  4.20
                 Logons:                  0.21                  0.47
               Executes:                 21.10                 48.50
            Transactions:                  0.43

% Blocks changed per Read:   14.80    Recursive Call %:   34.45
Rollback per transaction %:    0.00       Rows per Sort:   20.57

Redo size: 是日誌的生成量，分為每秒和每事務所產生的，通常在很繁忙的系統中日誌生成量可能達到上百k，甚至幾百k；

Logical reads: 邏輯讀實際上就是logical IO=buffer gets表示的含義，我們可以這樣認為，block在記憶體中，我們每一次讀一塊記憶體，就相當於一次邏輯讀；

Parses 和 Hard parses: Parse 和 hard parse通常是很容易出問題的部分，80%的系統的慢都是由於這個原因所導致的。
所謂parse分soft parse 和hard parse，soft parse是當一條sql傳進來後，需要在shared pool中找是否有相同的sql，如果找到了，那就是soft parse，如果沒

有找著，那就開始hard parse，實際上hard parse主要是檢查該sql所涉及到的所有的物件是否有效以及許可權等關係，hard parse之後才根據rule/cost模式生成

執行計劃，再執行sql。
而hard parse的根源，基本都是由於不使用bind var所導致的，不使用bind var違背了oracle的shared pool的設計的原則，違背了這個設計用來共享的思想，這

樣導致shared_pool_size裡面命中率下降。因此不使用bind var，將導致cpu使用率的問題，極有使得效能急劇下降。
還有就是為了維護internal structure，需要使用latch，latch是一種Oracle低階結構,用於保護記憶體資源，是一種內部生命週期很短的lock，大量使用latch將

消耗大量的cpu資源。

Sorts: 表示排序的數量；

Executes: 表示執行次數；

Transactions: 表示事務數量；

Rollback per transaction %: 表示資料庫中事務的回退率。如果不是因為業務本身的原因，通常應該小於10%為好，回退是一個很消耗資源的操作。

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
           Buffer Nowait %: 100.00       Redo NoWait %:   99.98
           Buffer Hit   %:   65.82    In-memory Sort %:   99.65
           Library Hit   %:   91.32        Soft Parse %:   88.18
         Execute to Parse %:    9.28         Latch Hit %:   99.99
Parse CPU to Parse Elapsd %:   94.61     % Non-Parse CPU:   99.90

Buffer Hit %: 資料緩衝區命中率，通常應該大於90%；

Library Hit %: libaray cache的命中率，通常應該大於98%；

In-memory Sort %: 排序在記憶體的比例，如果這個比例過小，可以考慮增大sort_area_size，使得排序在記憶體中進行而不是在temp表空間中進行；

Soft Parse %: 軟解析的百分比，這個百分比也應該很大才好，因為我們要儘量減少hard parse。 soft parse 百分比=soft/(soft+hard)；

Execute to Parse %: 這個數字也應該是越大越好，接近100%最好。有些報告中這個值是負的，看上去很奇怪。事實上這表示一個問題，sql如果被age out的話

就可能出現這種情況，也就是sql老化，或執行alter system flush shared_pool等。

Shared Pool Statistics          Begin   End
                             ------   ------
         Memory Usage %:    90.63   87.19
   % SQL with executions>1:   71.53   75.39
% Memory for SQL w/exec>1: 59.45   65.17

% SQL with executions>1: 這個表示SQL被執行次數多於一次的比率，也應該大為好，小則表示很多sql只被執行了一次，說明沒有使用bind var；

2.2.3 等待事件分析
接下來，statspack報告中描述的是等待事件（Wait Events），這是Oracle中比較複雜難懂的概念。
Oracle 的等待事件是衡量Oracle 執行狀況的重要依據及指標。
等待事件的概念是在Oracle7.0.1.2 中引入的，大致有100 個等待事件。在Oracle 8.0 中這個數目增加到了大約150 個，在Oracle8i 中大約有200 個事件,在

Oracle9i 中大約有360 個等待事件。
主要有兩種類別的等待事件，即空閒（idle）等待事件和非空閒（non-idle）等待事件。
空閒事件指Oracle 正等待某種工作,在診斷和優化資料庫的時候,我們不用過多注意這部分事件。
常見的空閒事件有:
? 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

非空閒等待事件專門針對Oracle 的活動,指資料庫任務或應用執行過程中發生的等待，這些等待事件是我們在調整資料庫的時候應該關注與研究的。
一些常見的非空閒等待事件有:
? db file scattered read
? db file sequential read
? buffer busy waits
? free buffer waits
? enqueue
? latch free
? log file parallel write
? log file sync

下面接合statspack中的一些等待事件進行講述。

Top 5 Wait Events
~~~~~~~~~~~~~~~~~                              Wait     % Total
Event                                    Waits Time (cs)   Wt Time
--------------------------------------------           ------------ ------------    -------
db file scattered read                      26,877       12,850   52.94
db file parallel write                         472        3,674   15.13
log file parallel write                         975        1,560    6.43
direct path write                           1,571        1,543    6.36
control file parallel write                     652        1,290    5.31
          -------------------------------------------------------------

db file scattered read: DB檔案分散讀取。這個等待事件很常見，經常在top5中出現，這表示，一次從磁碟讀資料進來的時候讀了多於一個block的資料，而這

些資料又被分散的放在不連續的記憶體塊中，因為一次讀進來的是多於一個block的。
通常來說我們可以認為是全表掃描型別的讀，因為根據索引讀表資料的話一次只讀一個block，如果這個數字過大，就表明該表找不到索引，或者只能找到有限的

索引，可能是全表掃描過多，需要檢查sql是否合理的利用了索引，或者是否需要建立合理的索引。
當全表掃描被限制在記憶體時，它們很少會進入連續的緩衝區內，而是分散於整個緩衝儲存器中。儘管在特定條件下執行全表掃描可能比索引掃描更有效，但如果

出現這種等待時，最好檢查一下這些全表掃描是否必要,是否可以通過建立合適的索引來減少對於大表全表掃描所產生的大規模資料讀取。
對於經常使用的小表，應該儘量把他們pin 在記憶體中，避免不必要的老化清除及重複讀取。

db file sequential read: DB檔案連續讀取。通常顯示單個塊的讀取(通常指索引讀取)，表示的是讀進磁碟的block被放在連續的記憶體塊中。
事實上大部分基本代表著單個block的讀入，可以說象徵著 IO 或者說通過索引讀入的比較多 。因為一次IO若讀進多個的block，放入連續的記憶體塊的機率是很小

的，分佈在不同block的大量記錄被讀入就會遇到此事件。因為根據索引讀資料的話，假設100條記錄，根據索引，不算索引本身的讀，而根據索引每個值去讀一

下表資料，理論上最多可能產生100 buffer gets，而如果是full table scan，則100條資料完全可能在一個block裡面，則幾乎一次就讀過這個block了，就會產

生這麼大的差異。
這種等待的數目很多時，可能顯示錶的連線順序不佳，或者不加選擇地進行索引。
對於高階事務處理（high-transaction）、調整良好（welltuned）的系統，這一數值很大是很正常的，但在某些情況下，它可能暗示著系統中存在問題。
你應當將這一等待統計量與Statspack 報告中的已知問題（如效率較低的SQL）聯絡起來。檢查索引掃描，以保證每個掃描都是必要的，並檢查多表連線的連線順

序。
DB_CACHE_SIZE 也是這些等待出現頻率的決定因素。有問題的雜湊區域（Hash-area）連線應當出現在PGA 記憶體中，但它們也會消耗大量記憶體，從而在順序讀取時

導致大量等待。它們也可能以直接路徑讀／寫等待的形式出現。

Free Buffer Wait: 釋放緩衝區。
這種等待表明系統正在等待記憶體中的緩衝，因為記憶體中已經沒有可用的緩衝空間了。如果所有SQL 都得到了調優，這種等待可能表示你需要增大DB_BUFFER_CACHE

。釋放緩衝區等待也可能表示不加選擇的SQL 導致資料溢位了帶有索引塊的緩衝儲存器，沒有為等待系統處理的特定語句留有緩衝區。
這種情況通常表示正在執行相當多數量的DML（插入／更新／刪除），並且可能說明DBWR 寫的速度不夠快，緩衝儲存器可能充滿了相同緩衝器的多個版本，從而

導致效率非常低。為了解決這個問題，可能需要考慮增加檢查點、利用更多的DBWR 程式，或者增加物理磁碟的數量。

Buffer Busy Wait: 緩衝區忙。
該等待事件表示正在等待一個以unshareable方式使用的緩衝區，或者表示當前正在被讀入buffer cache。也就是當程式想獲取或者操作某個block的時候卻發現

被別的程式在使用而出現等待。一般來說Buffer Busy Wait不應大於1%。
檢查緩衝等待統計部分（或V$WAITSTAT），看一下等待是否位於段頭。如果是，可以考慮增加自由列表（freelist，對於Oracle8i DMT）或者增加freelist

groups.
其修改語法為：
SQL> alter table sp_item storage (freelists 2);
Table altered。

對於Oracle8i而言，增加freelist引數，在很多時候可以明顯緩解等待，如果使用LMT，也就是Local Manangement Tablespace，區段的管理就相對簡單還可以考

慮修改資料塊的pctused\pctfree值，比如增大pctfree可以擴大資料的分佈，在某種程度上就可以減少熱點塊的競爭。

如果這一等待位於undo header，可以通過增加回滾段（rollback segment）來解決緩衝區的問題。
如果等待位於undo block上，我們可能需要檢查相關應用，適當減少大規模的一致性讀取，或者降低一致性讀取(consistent read)的表中的資料密度或者增大

DB_CACHE_SIZE。
如果等待處於data block，可以考慮將頻繁併發訪問的表或資料移到另一資料塊或者進行更大範圍的分佈（可以增加pctfree 值 ，擴大資料分佈，減少競爭），

以避開這個"熱點"資料塊，或者可以考慮增加表中的自由列表或使用本地化管理的表空間（Locally Managed Tablespaces）。
如果等待處於索引塊，應該考慮重建索引、分割索引或使用反向鍵索引。反向鍵索引在很多情況下，可以極大地緩解競爭，其原理有點類似於hash分割槽的功效。

反向鍵索引（reverse key index）常建在一些值是連續增長的列上，例如列中的值是由sequence產生的。

為了防止與資料塊相關的緩衝忙等待，也可以使用較小的塊：在這種情況下，單個塊中的記錄就較少，所以這個塊就不是那麼"繁忙"；或者可以設定更大的

pctfree,使資料擴大物理分佈，減少記錄間的熱點競爭。
在執行DML (insert/update/ delete)時，Oracle向資料塊中寫入資訊，對於多事務併發訪問的資料表，關於ITL的競爭和等待可能出現，為了減少這個等待，可

以增加initrans，使用多個ITL槽。
以下是一個生產系統v$waitstat 試圖所顯示的等待資訊:
SQL> select * from v$waitstat where count<>0 or time <>0;
CLASS      COUNT TIME
------------------ ---------- ----------
data block       453   6686
undo header      391   1126
undo block       172      3

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