如何理解和調整BUFFER CACHE 以及 DBWR

如何理解和調整BUFFER CACHE 以及 DBWR，針對這個問題，這篇文章詳細介紹了相對應的分析和解答，希望可以幫助更多想解決這個問題的小伙伴找到更簡單易行的方法。

Understanding and Tuning Buffer Cache and DBWR (Doc ID 62172.1)

理解和調整BUFFER CACHE 以及 DBWR(database writer)

當我們的數據庫出現一下等待事件的時候，說明數據庫性能已經受到了影響：

--1）Latch contention for the 'cache buffers lru chain' or the "cache buffer chain" latch
--2）Large "Average Write Queue" length
--3）Lots of time spent waiting for "write complete waits"
--4）Lots of time spent waiting for "free buffer waits"? or "Buffer busy waits"

BUFFER CACHE緩沖區緩存
Oracle在SGA的一個區域中保存數據庫塊的副本，稱為緩沖區緩存。
緩存可以從不同的時間點保存一個塊的多個副本，并且可能包含“臟”塊，即已更新但尚未刷新到磁盤的塊。數據庫write/ S（DBWR或DBWn進程）是負責寫臟數據塊到磁盤，而任何用戶會話可以讀取數據塊寫入緩存。

緩沖區高速緩存中的所有塊在一個LRU（最近最少使用）列表-當一個進程需要一個空閑的緩沖時。它會掃描從這個列表的LRU端非臟緩沖區，它可以使用。The 'cache buffers lru chain' latch/es serialize operations on the LRU list/s.（在Oracle8i起用于LRU列表的算法是更早的版本不同而影響因素保持不變）。

Evaluating Buffer? cache Activity
對緩沖區緩存的活性評價
緩沖區緩存命中率度量內存中所需塊的多少倍，而不是在磁盤上執行昂貴的讀操作以獲得塊。

查詢V$SYSSTAT視圖是可能獲得的統計信息用于調整緩沖區高速緩存。為了計算這個比例，你必須考慮你正在運行的Oracle版本。建議在增加緩沖區緩存大小之前，命中率高于80%。

 how to calculate this ratio on each Oracle version.
 怎么樣計算每個Oracle版本的命中率。
 
 “緩存命中率”是一個派生的統計數據最多的手冊和文章。
 存在的緩存命中的定義不止一個
 命中率是用來指示各種情況的頻率。            
 訪問數據緩沖區的進程查找Oracle緩沖區中的塊。            
 高速緩存。命中率的精確值比            
 有能力監控它隨著時間的推移，以注意任何重大變化的            
 數據庫上活動的概要。 
 
 important：
 命中率很高（接近100%）不一定是好的。
 原因是后來解釋的。
 
 Calculation:
~~~~~~~~~~~~
  The most common formula in circulation for the hit ratio for the buffer cache
  for Oracle7/8 is:

    hit ratio =   1 -           ( physical reads )
                          -----------------------------------
                           ( consistent gets + db block gets )

A better formula in Oracle8i/9i is:

    hit ratio =  

      1 -  ( physical reads - (physical reads direct + physical reads direct (lob)) )
           --------------------------------------------------------------------------
     ( db block gets + consistent gets - (physical reads direct + physical reads direct (lob)) )

每個池的命中率可以是使用V buffer_pool_statistics看到：
SELECT name, 1-(physical_reads / (consistent_gets + db_block_gets ) )  "HIT_RATIO"
      FROM V$BUFFER_POOL_STATISTICS
     WHERE ( consistent_gets + db_block_gets ) !=0
    ;

The "Miss Ratio"
~~~~~~~~~~~~~~~~
  Occasionally you may see reference to the "miss ratio". This is just

 Miss ratio =  100% - Hit Ratio (expressed as a percentage)

Notes about the Hit Ratio
~~~~~~~~~~~~~~~~~~~~~~~~~
  A good hit ratio is expected for OLTP type systems but decision support type
  systems may have much lower hit ratios.  Use of parallel query will make the
  hit ratio less meaningful if using the first form of calculation based on
  "physical reads" only.

  A  hit ratio close to 100% does not mean the application is good. It is quite
  possible to get an excellent hit ratio by using a very unselective index in a
  heavily used SQL statement.
  Eg: Consider a statement like:

        SELECT * FROM employee WHERE empid=1023 AND gender='MALE';

  If EMPLOYEE is a large table and this statement always uses the GENDER index
  rather than the EMPID index then you scan LOTS of blocks (from the GENDER
  index) and find nearly all of them in the cache as everyone is scanning this
  same index over and over again. The hit ratio is very HIGH but performance
  is very BAD.  A common 'variation' on an "unselective" index is a heavily
  skewed index where there are a large number of entries with one particular
  value (eg: a workflow status code of CLOSED) - the index may perform well for
  some queries and very poorly for the most common value.

如果員工是大表，這個語句總是使用性別索引。            
而不是工號索引然后你掃描很多塊（從性別索引）并在緩存中找到幾乎所有的緩存，因為每個人都在掃描這個。            
同一索引一遍又一遍。命中率很高，但性能很糟糕。一個共同的“變化”的“選擇性”指數是一個重有一個特殊條目的大量索引。            
值（例如：關閉的工作流狀態代碼）-該索引可能執行得很好。            
有些查詢和最常見的值很差。

下面的這個命中率公式適用于所有版本的Oracle：
A few comments:
~~~~~~~~~~~~~~~
  - The "good" hit ratio is generally considered to be one >80%
    There is probably still scope for tuning if it is <90% *BUT*
    note that the hit ratio is not the best measure of performance.

  - The ratio can be artificially high in applications making
    poor use of an UNSELECTIVE index.

  - In Oracle8.1 onwards "physical reads direct" are recorded

  - Some documentation incorrectly reports hit ratio to be:

  Hit Ratio = Logical Reads / ( Logical Reads + Physical Reads )
  
    this is incorrect for any version of Oracle.

一個好的命中率一般是大于80%的，如果它小于90%，也可能還有調整的范圍，因為命中率高并不代表性能就一定好。

關于如何理解和調整BUFFER CACHE 以及 DBWR問題的解答就分享到這里了，希望以上內容可以對大家有一定的幫助，如果你還有很多疑惑沒有解開，可以關注億速云行業資訊頻道了解更多相關知識。