Oracle Performance Tuning 11g2 (7-2)

7.2 Configuring and Using the Buffer Cache

For many types of operations, Oracle Database uses the buffer cache to store blocks read from disk. Oracle Database bypasses the buffer cache for particular operations, such as sorting and parallel reads. For operations that use the buffer cache, this section explains the following:

對於許多型別的操作，oracle使用快取儲存從磁碟中讀取的塊。對於有些特殊的操作,oracle會繞過buffer cache，例如排序和並行讀。對於需要使用buffer cache的操作，這節解釋了以下內容：（buffer cache就是快取，但是我在翻譯的時候仍然寫buffer cache的英文名字，以示強調；如果寫中文這個字的重要性就會淹沒在其他文字中）

• Using the Buffer Cache Effectively                            有效地使用快取

• Sizing the Buffer Cache                                       設定快取的大小

• Interpreting and Using the Buffer Cache Advisory Statistics   解析和使用快取建議統計

• Considering Multiple Buffer Pools                             使用多快取池

7.2.1 Using the Buffer Cache Effectively

To use the buffer cache effectively, tune SQL statements for the application to avoid unnecessary resource consumption. To meet this goal, verify that frequently executed SQL statements and SQL statements that perform many buffer gets have been tuned.

When using parallel query, you can configure the database to use the database buffer cache instead of performing direct reads into the PGA. This configuration may be appropriate when the database servers have a large amount of memory.

為了有效的使用buffer cache，除錯你的應用程式中的SQL語句，以避免過多的消耗資源。為了達到這個目標，確認那些頻繁執行的SQL語句，以及那些需要許多buffer gets的語句。

當除錯並行查詢時，你可以配置資料庫去使用資料庫的buffer cache而不是使用直接路徑讀到PGA中。這種配置適合有特大記憶體的伺服器。

 

See Also:

• to learn more using parallel execution

 

7.2.2 Sizing the Buffer Cache

When configuring a new instance, it is impossible to know the correct size for the buffer cache. Typically, a database administrator makes a first estimate for the cache size, then runs a representative workload on the instance and examines the relevant statistics to see whether the cache is under or over configured.

當配置一個例項時，知道如何正確的配置buffer cache是非常重要的。通常情況下，DBA先去評估一下大概需要多少cache大小，然後進行壓力測試，最後檢查一下相關的統計資料以瞭解這個快取是大了還是小了。

 

7.2.2.1 Buffer Cache Advisory Statistics

You can use several statistics to examine buffer cache activity, including the following:

你可以使用許多的統計資料去檢查buffer cache的活動，包括下面： v$db_cache_advice, hit ratio

• V$DB_CACHE_ADVICE

• Buffer cache hit ratio

7.2.2.2 Using V$DB_CACHE_ADVICE

This view is populated when the DB_CACHE_ADVICE initialization parameter is set to ON. This view shows the simulated miss rates for a range of potential buffer cache sizes.

Each cache size simulated has its own row in this view, with the predicted physical I/O activity that would take place for that size. The DB_CACHE_ADVICE parameter is dynamic, so the advisory can be enabled and disabled dynamically to allow you to collect advisory data for a specific workload.

There is some overhead associated with this advisory. When the advisory is enabled, there is a small increase in CPU usage, because additional bookkeeping is required.

Oracle Database uses DBA-based sampling to gather cache advisory statistics. Sampling substantially reduces both CPU and memory overhead associated with bookkeeping. Sampling is not used for a buffer pool if the number of buffers in that buffer pool is small to begin with.

To use V$DB_CACHE_ADVICE, the parameter DB_CACHE_ADVICE should be set to ON, and a representative workload should be running on the instance. Allow the workload to stabilize before querying the V$DB_CACHE_ADVICE view.

當db_cache_advice設定成On的時候，這個檢視就會被填充值了。這個檢視顯示了在各種buffer cache大小下，一種假想的未命中的比率。（就是根據目前的情況推測命中率有多高）

每個假想的快取大小都是一行記錄，它包含了預測的IO大小活動。db_cache_advice是一個動態引數，所以你可以動態的開啟或關閉收集特定壓力的顧問。

這個顧問對系統會有一些開銷。當顧問開啟時，CPU會有一些增加，因為有一些額外的記錄處理。

oracle資料庫使用基於DBA的取樣去收集快取顧問資料。

為了使用v$db_cache_advice， 引數db_cache_advice一定要開啟，並且例項上要有一定的壓力活動。當壓力穩定以後再去查詢這個 v$db_cache_advice 檢視。

The following SQL statement returns the predicted I/O requirement for the default buffer pool for various cache sizes:

下面這個SQL語句是檢視default buffer pool的預設block_size塊，在advice_status開啟時的統計情況（有3種buffer pool: default,keep,recycle）

The following output shows that if the cache was 212 MB, rather than the current size of 304 MB, the estimated number of physical reads would increase by a factor of 1.74 or 74%. This means it would not be advisable to decrease the cache size to 212MB.

However, increasing the cache size to 334MB would potentially decrease reads by a factor of .93 or 7%. If an additional 30MB memory is available on the host computer and the SGA_MAX_SIZE setting allows the increment, it would be advisable to increase the default buffer cache pool size to 334MB.

下面的輸出結果顯示假如cache是212M，而不是現在的304M，那麼評估下來的物理讀將會增加1.74個讀因子或者是增加74%的物理讀。這就意味著將cache大小減到212M是不明智的。

然而，增加cache大小到334M卻可能將物理讀降低到0.93個因子或者降低7%的物理讀。假如你的主機上有額外的30M記憶體的話，並且sga_max_size允許增加這麼大，那麼將記憶體增加到334M是明智的了。

                                Estd Phys    Estd Phys

 Cache Size (MB)      Buffers Read Factor        Reads

---------------- ------------ ----------- ------------

              30        3,802       18.70  192,317,943      10% of Current Size

              60        7,604       12.83  131,949,536

              91       11,406        7.38   75,865,861

             121       15,208        4.97   51,111,658

             152       19,010        3.64   37,460,786

             182       22,812        2.50   25,668,196

             212       26,614        1.74   17,850,847

             243       30,416        1.33   13,720,149

             273       34,218        1.13   11,583,180

             304       38,020        1.00   10,282,475      Current Size

             334       41,822         .93    9,515,878

             364       45,624         .87    8,909,026

             395       49,426         .83    8,495,039

             424       53,228         .79    8,116,496

             456       57,030         .76    7,824,764

             486       60,832         .74    7,563,180

             517       64,634         .71    7,311,729

             547       68,436         .69    7,104,280

             577       72,238         .67    6,895,122

             608       76,040         .66    6,739,731      200% of Current Size (304 * 2 = 608,所以這是在快取開到當前2倍時的表現)

This view assists in cache sizing by providing information that predicts the number of physical reads for each potential cache size. The data also includes a physical read factor, which is a factor by which the current number of physical reads is estimated to change if the buffer cache is resized to a given value.

這個檢視有助於幫助調整cache大小，它提供了預測的各種cache大小時的物理讀資訊。它同時提供了物理讀因子，這個因子是根據當前的cache大小與改變後的cache大小計算的一個值。

Note: 在oracle資料庫裡，物理讀並不意味著磁碟讀；因為物理讀可能是從作業系統是快取中讀取的（如果使用裸裝置或者ASM的話就沒有快取了，作業系統沒快取是最好的）

With Oracle Database, physical reads do not necessarily indicate disk reads; physical reads may well be satisfied from the file system cache.

The relationship between successfully finding a block in the cache and the size of the cache is not always a smooth distribution. When sizing the buffer pool, avoid the use of additional buffers that contribute little or nothing to the cache hit ratio. In the example illustrated in Figure 7-1, only narrow bands of increments to the cache size may be worthy of consideration.

成功在cache中找到一個塊和cache的大小之間並不是一個平滑的分佈。調整buffer pool時，增加記憶體對於cache命中率只有很少的作用時，避免使用額外的buffer。

在下面的圖中，只有在那個虛線窄處增加相應的記憶體才是值得考慮的

Figure 7-1 Physical I/O and Buffer Cache Size

Examining Figure 7-1 leads to the following observations:  透過檢視這個圖可以得出下面的結果：

透過增加Buffers（橫座標），從A點到B點的效果要好於B點到C點。

從A->B和從B->C階段IO的降低並不是平滑的，就像圖中的虛線表示的一樣

• The benefit from increasing buffers from point A to point B is considerably higher than from point B to point C.

• The decrease in the physical I/O between points A and B and points B and C is not smooth, as indicated by the dotted line in the graph.

 

7.2.2.3 Calculating the Buffer Cache Hit Ratio       計算buffer cache命中率

The buffer cache hit ratio calculates how often a requested block has been found in the buffer cache without requiring disk access. This ratio is computed using data selected from the dynamic performance view V$SYSSTAT. You can use the buffer cache hit ratio to verify the physical I/O as predicted by V$DB_CACHE_ADVICE.

buffer cache命中率是指多少塊的請求可以在快取中找到，而不用去訪問硬碟。這個命中率可以透過v$sysstat中的相關資料計算出來。你可以使用這個buffer cache命中率去辯認v$db_cache_advice 中預測的物理IO。

（在DB2中，這個命中率是透過一個命令就可以看到的：db2pd -d dbnameXXX -bufferpools,在DB2中每個表空間都要關聯一個buffer pool，透過這條命令就可以直到每個buffer pool的命中率；雖然看起來DB2看直觀，實際上oracle將所有的都統一到SQL中，而在DB2中需要記憶許多這樣的命令，很麻煩的）

The statistics in Table 7-1 are used to calculate the hit ratio.

Table 7-1 Statistics for Calculating the Hit Ratio

Statistic	Description
consistent gets from cache	Number of times a consistent read was requested for a block from the buffer cache. 從buffer cache中請求CR的次數
db block gets from cache	Number of times a CURRENT block was requested from the buffer cache. 從buffer cache中請求CURRENT塊的次數
physical reads cache	Total number of data blocks read from disk into buffer cache. 多少資料塊被從磁碟中讀取到buffer cache中了

 

Example 7-1 has been simplified by using values selected directly from the V$SYSSTAT table, rather than over an interval. It is best to calculate the delta of these statistics over an interval while your application is running, then use them to determine the hit ratio.

其實使用7-1例子中直接從v$sysstat 表計算是一個簡化的方法，這不是透過週期計算的。最好的辦法是在系統執行時，根據一個週期去計算增量值，然後用增量值計算這個命中率。

See Also:

Chapter 6, "Automatic Performance Diagnostics" for more information on collecting statistics over an interval

Example 7-1 Calculating the Buffer Cache Hit Ratio

Using the values in the output of the query, calculate the hit ratio for the buffer cache with the following formula:

       計算buffer cache的命中率公式：

   1 - (('') / ('consistent gets from cache' + ''))    

= 95.71% (這是根據建設銀行一個系統上的值計算出來的)

為什麼要使用減 物理讀/快取讀，而沒有一個直接計算的公式呢？

那我們假設不使用1減去這種方式算，那我們應該知道命中的個數才能算出來，即 命中的塊數/(CR塊讀+當前塊讀)，這樣就直接得出了命中率。

可是命中的塊數如何得到呢？ 當發現可以從快取中找到需要的塊時就給一個變數加1（假如我起個名字是“命中塊”變數），一直這樣累加不就可以了？是的，技術是可行的，但是物理讀是相對比較少的動作，而且讀的過程很慢，當讀完之後再去更新這個物理讀變數要比每訪問一個記憶體塊都去更新一個“命中塊”要容易的多。因為在更新之前也要進行加鎖操作，我也稱它為latch操作吧，即latch鎖就佔用很高了。因此我覺得oracle在這裡是經過深入思考的，設計是非常合理的，雖然有點麻煩，但是是可以接受的合理設計。

 

這個v$sysstat檢視是非常值得好好研究的

 

7.2.3 Interpreting and Using the Buffer Cache Advisory Statistics

There are many factors to examine before considering whether to increase or decrease the buffer cache size. For example, you should examine V$DB_CACHE_ADVICE data and the buffer cache hit ratio.

A low cache hit ratio does not imply that increasing the size of the cache would be beneficial for performance. A good cache hit ratio could wrongly indicate that the cache is adequately sized for the workload.

在考慮增加或減少buffer cache大小時有許多因素需要考慮。比如你要考慮 v$db_cache_advice 資料以及buffer cache命中率。

低命中率並不意味著增加buffer cache就可以提升效能。一個高命中率或許錯誤的引導你認為在現有的壓力下cache大小是足夠的。

To interpret the buffer cache hit ratio, you should consider the following:  為解釋buffer cache命中率，你應該考慮以下：

• Repeated scanning of the same large table or index can artificially inflate a poor cache hit ratio. Examine frequently executed SQL statements with a large number of buffer gets, to ensure that the execution plan for such SQL statements is optimal. If possible, avoid repeated scanning of frequently accessed data by performing all of the processing in a single pass or by optimizing the SQL statement.

重複掃描相同的大表或者索引可能人為的造成低的快取命中率。檢查一下頻繁執行的擁有很大的buffer gets的SQL語句，確保這些SQL的執行計劃是最優的。透過將所有的過程放在一起去執行避免重複的掃描頻繁訪問的資料，或者透過最佳化SQL語句去提升。

• If possible, avoid requerying the same data, by caching frequently accessed data in the client program or middle tier.

如果可能的話，避免重新查詢相同的資料，透過將頻繁訪問的資料快取到客戶端程式或者中間層。（一般我們都是快取到自己的共享記憶體中，或者使用後面會介紹的result cache，也可以做到這一點）

• Database blocks accessed during a long full table scan are put on the tail end of the least recently used LRU list and not on the head of the list. Therefore, the blocks are aged out faster than blocks read when performing indexed lookups or small table scans. When interpreting the buffer cache data, poor hit ratios when valid large full table scans are occurring should also be considered.

Note:

Short table scans are scans performed on tables under a certain size threshold. The definition of a small table is the maximum of 2% of the buffer cache and 20, whichever is bigger.

在一個長的全表掃描的塊會被放到LRU列表的最後，而不是連結串列的前面。因此這些塊會比其他用index或者小表掃描的塊更快地替換出去。當你檢視buffer cache資料時，當大的全表掃描時造成的低命中率是應該被考慮進去的。

注意： 小表掃描是在一定的閾值下執行的表掃描。小表的定義是buffer cache的2%和20個塊中的最大值，否則就算大表。（如果快取的2%小於20個塊，那麼小表是20個塊；如果快取的2%大於20個塊，那麼小表就是按這個快取的2%來算；在建行系統上，有25G的sga_target值，大約有22G的buffer_cache，所以22G*2% = 440M，如果一條記錄是1K大小，那麼440M/1K = 440,000即44萬條記錄都算小表了！因此我覺得這個是有問題的，也許這個文件是對9i以前的系統可能有幫助，因為當時記憶體還沒這麼便宜，而現在伺服器已經不一樣了，配置幾百G記憶體的伺服器很司空見慣了，對吧！我個人覺得1000條以內可以算小表，其他都應該按大表算；如果一條記錄是1K，一個塊中存7K，那麼1000條佔142個塊，如果每次都全表掃描的話，也就掃描142個塊，一次IO通常都是16個塊，這個應該還差不多）

• In any large database running OLTP applications in any given unit of time, most rows are accessed either one or zero times. On this basis, there might be little purpose in keeping the block in memory for very long following its use.

在許多大的OLTP應用中，在給定的時間單元裡，多數的資料行被訪問1次或0次。在這種情況下，沒有必要將這個資料塊長時間保留在記憶體中。

• A common mistake is to continue increasing the buffer cache size. Such increases have no effect if you are doing full table scans or operations that do not use the buffer cache.

一個通常的錯誤是不斷的增加buffer cache的大小。這樣做對於全表掃描或者不使用buffer cache的操作是沒有任何提升的。

 

7.2.3.1 Increasing Memory Allocated to the Buffer Cache

As a general rule, investigate increasing the size of the cache if the cache hit ratio is low and your application has been tuned to avoid performing full table scans.

To increase cache size, first set the DB_CACHE_ADVICE initialization parameter to ON, and let the cache statistics stabilize. Examine the advisory data in the V$DB_CACHE_ADVICE view to determine the next increment required to significantly decrease the amount of physical I/O performed. If it is possible to allocate the required extra memory to the buffer cache without causing the host operating system to page, then allocate this memory. To increase the amount of memory allocated to the buffer cache, increase the value of the DB_CACHE_SIZE initialization parameter.

If required, resize the buffer pools dynamically, rather than shutting down the instance to perform this change.

通常的做法，假如你的命中率比較低，並且你的應用程式已經最佳化過不會執行全表掃描時，試著去增加buffer cache的大小。

增加buffer cache大小時，先將db_cache_advice設定成ON，並且讓快取統計平穩下來。檢視一下v$db_cache_advice 檢視中的統計資料，是否增加一下buffer cache就可以明顯的降低IO操作。假如你的作業系統有額外的記憶體時，你可以把它分配給buffer cache。透過 db_cache_size引數設定這個值的大小。

Note:

When the cache is resized significantly (greater than 20%), the old cache advisory value is discarded and the cache advisory is set to the new size. Otherwise, the old cache advisory value is adjusted to the new size by the interpolation of existing values.

注意： 當快取已經設定足夠大（大於20%），那麼舊的顧問值就刪除掉了，cache顧問就會設定新的值。另外舊的cache顧問值將被新的值替換掉。

The DB_CACHE_SIZE parameter specifies the size of the default cache for the database's standard block size. To create and use tablespaces with block sizes different than the database's standard block sizes (such as to support transportable tablespaces), you must configure a separate cache for each block size used. You can use the DB_nK_CACHE_SIZE parameter to configure the nonstandard block size needed (where n is 2, 4, 8, 16 or 32 and n is not the standard block size).

    db_cache_size引數是設定標準資料塊的cache的。假如你的資料庫中包含了非標準塊，比如你表空間遷移來的表空間，你必須為你的不同大小的資料庫塊各自分配快取大小。使用db_nK_cache_size引數來配置非標準資料塊。

Note:

The process of choosing a cache size is the same, regardless of whether the cache is the default standard block size cache, the KEEP or RECYCLE cache, or a nonstandard block size cache.

 

注意選擇cache大小的方法是一樣，不管你是標準塊大小cache，還是keep,recycle cache,或者是非標準塊大小cache。

 

 

7.2.3.2 Reducing Memory Allocated to the Buffer Cache

If the cache hit ratio is high, then the cache is probably large enough to hold the most frequently accessed data. Check V$DB_CACHE_ADVICE data to see whether decreasing the cache size significantly causes the number of physical I/Os to increase. If not, and if you require memory for another memory structure, then you might be able to reduce the cache size and still maintain good performance. To make the buffer cache smaller, reduce the size of the cache by changing the value for the parameter DB_CACHE_SIZE.

假如命中率非常的高，那麼cache可能是足夠大的，它保留了多數頻繁訪問的資料。檢查一下 v$db_cache_advice 檢視資料去看看如果減少buffer的大小能否達到不增加物理IO讀。假如不會增加IO的負擔，而同時你其他的記憶體部件需要記憶體，你就可以把記憶體降低，同時維持一個高的效能。透過改變db_cache_size就可以達到減少buffer cache的大小。

 

7.2.4 Considering Multiple Buffer Pools

A single default buffer pool is generally adequate for most systems. However, users with detailed knowledge of an application's buffer pool might benefit from configuring multiple buffer pools.

With segments that have atypical access patterns, store blocks from those segments in two different buffer pools: the KEEP pool and the RECYCLE pool. A segment's access pattern may be atypical if it is constantly accessed (that is, hot) or infrequently accessed (for example, a large segment accessed by a batch job only once a day).

Multiple buffer pools let you address these differences. You can use a KEEP buffer pool to maintain frequently accessed segments in the buffer cache, and a RECYCLE buffer pool to prevent objects from consuming unnecessary space in the cache. When an object is associated with a cache, all blocks from that object are placed in that cache. Oracle Database maintains a DEFAULT buffer pool for objects that have not been assigned to a specific buffer pool. The default buffer pool is of size DB_CACHE_SIZE. Each buffer pool uses the same Least Recently Used (LRU) replacement policy (for example, if the KEEP pool is not large enough to store all of the segments allocated to it, then the oldest blocks age out of the cache).

一個預設的buffer pool對於多數的系統而言已經是足夠的了。當然使用者如果有足夠的知識也可能受益於多個buffer pool的。

有些表是無規則的訪問的，將這些表的塊儲存在這兩種不同的buffer pool中： keep pool,recycle pool。一種不規則的訪問模式是經常訪問（熱）或者是不經常訪問（例如每天批次時只訪問一次）。

多個buffer pool讓你可以定位這些差異。你可以將經常要訪問的表放到keep池裡，將不經常訪問的放到recycle池中以防止它消耗不必要的default buffer空間。當一個物件與一個cache關聯起來時，所有的塊都放在那裡。oracle維護著一個default buffer pool，這樣所有的物件預設都在那裡。default buffer pool的大小是由db_cache_size決定的。每個BUFFER POOL都使用LRU的演算法（例如假如KEEP POOL不夠時，老的塊將也將被交換出去）

By allocating objects to appropriate buffer pools, you can:    透過將物件關聯到不同的buffer pool，你可以：

• Reduce or eliminate I/Os                                 降低或消耗IO問題

• Isolate or limit an object to a separate cache           隔離或者限制一個物件到一個單獨的快取中

 

 

7.2.4.1 Random Access to Large Segments

A problem can occur with an LRU aging method when a very large segment is accessed with a large or unbounded index range scan. Here, very large means large compared to the size of the cache. Any single segment that accounts for a substantial portion (more than 10%) of nonsequential physical reads can be considered very large. Random reads to a large segment can cause buffers that contain data for other segments to be aged out of the cache. The large segment ends up consuming a large percentage of the cache, but it does not benefit from the cache.

Very frequently accessed segments are not affected by large segment reads because their buffers are warmed frequently enough that they do not age out of the cache. However, the problem affects warm segments that are not accessed frequently enough to survive the buffer aging caused by the large segment reads. There are three options for solving this problem:

當一個非常大的表使用非常大的索引範圍掃描訪問時，這種LRU演算法是有問題的。這裡的大是和buffer的大小相對比的。當一個表以隨機方式讀時，它佔據了10%以上的非大量物理讀就是大表。對一個大表進行隨機讀時就可能造成其他表被交換出快取。大表消耗了大量的cache，但是卻沒有從cache中得到任何好處（因為他們通常只訪問一次）。

對於非常頻繁訪問的表不會被這種大表讀操作影響，因為它們是那麼的溫暖，以至於oracle不會把它們交換出去。但是對於那些不是非常頻繁訪問的溫表卻會帶來麻煩，它們在大表讀操作時無法倖存於buffer中。有3種辦法解決這個問題：

1. If the object accessed is an index, find out whether the index is selective. If not, tune the SQL statement to use a more selective index.

假如被訪問的物件是一個索引，那麼這個索引是否是selective的。假如不是，那就把它除錯成selective的（selective在前面幾章介紹過，簡單說就是主鍵就是最selective的，不唯一的selective性一定要主鍵要差）。這裡有一個要說明的：我前面介紹大segment時，我把它翻譯成大表，主要是為了方便理解，但是segment是表，索引，分割槽表，集聚表等等的統稱，所以這裡說訪問一個索引，也就是說訪問一個大segment。

1. If the SQL statement is tuned, you can move the large segment into a separate RECYCLE cache so that it does not affect the other segments. The RECYCLE cache should be smaller than the DEFAULT buffer pool, and it should reuse buffers more quickly than the DEFAULT buffer pool.

假如SQL語句已經調優過了，你可以將大表移到recycle池中，這樣它就不會影響其他的表了。recycle池應該比default小一些，它的交換要比DEFAULT池快，所以重用也要比DEFAUTL池快些。

1. Alternatively, you can move the small warm segments into a separate KEEP cache that is not used at all for large segments. The KEEP cache can be sized to minimize misses in the cache. You can make the response times for specific queries more predictable by putting the segments accessed by the queries in the KEEP cache to ensure that they do not age out.

另外，你可以將小的溫表移到keep池中，這個池不要放那些大表。這個池的物件應該保證不輕易被轉換出去。這樣你在訪問那些儲存在keep池中的物件時響應時間就是可預測的了，因為他們不會被交換出去。

 

7.2.4.2 Oracle Real Application Clusters Instances

You can create multiple buffer pools for each database instance. The same set of buffer pools need not be defined for each instance of the database. Among instances, the buffer pools can be different sizes or not defined at all. Tune each instance according to the application requirements for that instance.

在RAC中，你可以為每個資料庫例項建立多個buffer pool。 相同的buffer pool沒必要在每個例項上都去定義。在這個例項中，緩衝池可以是不同大小的，或者不定義也行。除錯每個例項以滿足應用程式的需要。

 

7.2.4.3 Using Multiple Buffer Pools

To define a default buffer pool for an object, use the BUFFER_POOL keyword of the STORAGE clause. This clause is valid for CREATE and ALTER TABLE, CLUSTER, and INDEX SQL statements. After a buffer pool has been specified, all subsequent blocks read for the object are placed in that pool.

If a buffer pool is defined for a partitioned table or index, then each partition of the object inherits the buffer pool from the table or index definition, unless you override it with a specific buffer pool.

When the buffer pool of an object is changed using the ALTER statement, all buffers currently containing blocks of the altered segment remain in the buffer pool they were in before the ALTER statement. Newly loaded blocks and any blocks that have aged out and are reloaded go into the new buffer pool.

將一個物件關聯到一個default的buffer pool時，使用storage的buffer_pool關鍵字建立。在create/alter table,cluster,index SQL語句中都可以使用。在一個buffer pool被定義後，所有隨後的讀取此物件的塊都被放入此池中了。

假如是分割槽表或者索引定義一個buffer pool時每個分割槽物件都繼承著建立表或索引的buffer pool設定，除非你單獨為分割槽定義。（我通常都是單獨去定義，這樣我永遠知道我在幹什麼，因為這代表了我對資料的掌控能力）。

當一個表的buffer pool被alter語句改變時，那麼這個被改變的表的塊如果以前存在於某一塊中，那麼現在仍然在那個buffer pool中。當這個表中新的塊從磁碟讀取出來時，以及原有池中的塊被交換出來時就把它們放到新的buffer pool中了。（這裡我仍然是將segment翻譯成表，但不代表只是表，看完之後再把其理解成所有的物件就是了）（同樣地：從這裡我們可以看出，其實oracle本不用設定這個KEEP,RECYCLE池的，因為他們都是按HASH去訪問的，在哪裡oracle都是一樣訪問的。區別僅僅是當空間不夠時要採取什麼機制去交換出去不常訪問的塊。最好是保證KEEP池中的塊不被其他交換出去）

 

7.2.5 Buffer Pool Data in V$DB_CACHE_ADVICE

You can use V$DB_CACHE_ADVICE to size all pools configured on a database instance. Make the initial cache size estimate, run the representative workload, then simply query the V$DB_CACHE_ADVICE view for the pool you want to use.

你可以使用 v$db_cache_advice 去調整例項中的所有pool。先做一個簡單的評估配置，然後做一次有代表性的壓力測試，然後再查詢這個 v$db_cache_advice 檢視去看你想設定的pool大小。

For example, to query data from the KEEP pool:

 

7.2.6 Buffer Pool Hit Ratios

The data in V$SYSSTAT reflects the logical and physical reads for all buffer pools within one set of statistics. To determine the hit ratio for the buffer pools individually, query the V$BUFFER_POOL_STATISTICS view. This view maintains statistics for each pool on the number of logical reads and writes.

v$sysstat 中反映了所有buffer pool中的邏輯和物理讀統計。為了反映buffer pool的命中率，查詢 v$buffer_pool_statistics 檢視。這個檢視維護著每個pool的邏輯讀寫資訊。

The buffer pool hit ratio can be determined using the following formula:

        1 - (physical_reads/(db_block_gets + consistent_gets))

The ratio can be calculated with the following query:

7.2.7 Determining Which Segments Have Many Buffers in the Pool

The V$BH view shows the data object ID of all blocks that currently reside in the SGA. To determine which segments have many buffers in the pool, you can use one of the two methods described in this section. You can either look at the buffer cache usage pattern for all segments (Method 1) or examine the usage pattern of a specific segment, (Method 2).

v$bh 檢視顯示所有目前所有塊快取在SGA中的物件ID。為了判斷哪些段有許多快取，你可以使用下面的兩種方法之一去判斷。你可以用第一種方法去檢視buffer cache中所有段的使用情況，或者使用第二種方法檢視某些段的情況。

Method 1

The following query counts the number of blocks for all segments that reside in the buffer cache at that point in time. Depending on buffer cache size, this might require a lot of sort space.

下面的方法查詢出目前在buffer cache中的所有段他們的塊數。buffer cache的大小不同，可能也需要一部分的排序空間。

 

OBJECT_NAME                              NUMBER_OF_BLOCKS

---------------------------------------- ----------------

OA_PREF_UNIQ_KEY                                        1

SYS_C002651                                             1

..

DS_PERSON                                              78

OM_EXT_HEADER                                         701

OM_SHELL                                            1,765

OM_HEADER                                           5,826

OM_INSTANCE                                        12,644

Method 2

Use the following steps to determine the percentage of the cache used by an individual object at a given point in time:

使用下面的方法去判斷一些物件在目前情況下佔用cache的百分比：

1. Find the Oracle Database internal object number of the segment by entering the following query:

Because two objects can have the same name (if they are different types of objects), use the OBJECT_TYPE column to identify the object of interest.

因為兩個物件可能是相同的名稱，所以需要這個object_type去判斷一下，其實最好再加一個   owner = ‘xxx’，定位的更加準確些。不過我們一般起的名稱都是不相同的，除非是在測試環境中才需要這個owner再區分一下。

1. Find the number of buffers in the buffer cache for SEGMENT_NAME:

where data_object_id_value is from step 1.

1. Find the number of buffers in the instance:

1. Calculate the ratio of buffers to total buffers to obtain the percentage of the cache currently used by SEGMENT_NAME:

% cache used by segment_name = [buffers(Step2)/total buffers(Step3)]

Note:  注意這種方法僅僅是針對未分割槽的表。如果是分割槽表的話就需要計算每一個分割槽，然後再彙總一下。

This technique works only for a single segment. You must run the query for each partition for a partitioned object.

 

7.2.8 KEEP Pool

If there are certain segments in your application that are referenced frequently, then store the blocks from those segments in a separate cache called the KEEP buffer pool. Memory is allocated to the KEEP buffer pool by setting the parameter DB_KEEP_CACHE_SIZE to the required size. The memory for the KEEP pool is not a subset of the default pool. Typical segments that can be kept are small reference tables that are used frequently. Application developers and DBAs can determine which tables are candidates.

You can check the number of blocks from candidate tables by querying V$BH, as described in "Determining Which Segments Have Many Buffers in the Pool".

假如有一個物件是頻繁地被訪問，那麼將這些段的塊儲存到KEEP池中。透過設定DB_KEEP_CACHE_SIZE來分配KEEP的池大小。這個keep池不是default池的一部分。通常將那些頻繁訪問的小表放到這裡。應用開發人員和DBA去查查哪些表可以放進來。（建議小表全放進來，以現在的記憶體容量，拿出幾十M的空間給他們足夠了）

你可以透過查詢v$bh 檢視去看看哪些表可以放到KEEP池中。

Note:  注意：這個 nocache 的引數對於keep池中的表是無效的

The NOCACHE clause has no effect on a table in the KEEP cache.

The goal of the KEEP buffer pool is to retain objects in memory, thus avoiding I/O operations. The size of the KEEP buffer pool, therefore, depends on the objects to be kept in the buffer cache. You can compute an approximate size for the KEEP buffer pool by adding the blocks used by all objects assigned to this pool. If you gather statistics on the segments, you can query DBA_TABLES.BLOCKS and DBA_TABLES.EMPTY_BLOCKS to determine the number of blocks used.

Calculate the hit ratio by taking two snapshots of system performance at different times, using the previous query. Subtract the more recent values for physical reads, block gets, and consistent gets from the older values, and use the results to compute the hit ratio.

A buffer pool hit ratio of 100% might not be optimal. Often, you can decrease the size of your KEEP buffer pool and still maintain a sufficiently high hit ratio. Allocate blocks removed from the KEEP buffer pool to other buffer pools.

這個KEEP池的目的就是將物件保留在記憶體中，避免IO的操作。KEEP池的大小因此是由池中的物件大小決定的。你可以透過一個複雜的計算將所有物件的塊大小總和作為KEEP池大小。假如你收集了段的統計資料，你可以查詢 dba_tables.blocks 和 dba_tables.empty_blocks 去計算塊的使用數量。

使用前面的查詢語句，在不同時間對兩個快照進行分析，計算其命中率。老的值減去最新的值（物理讀，block gets, 一致性讀）,這樣就可以算出命中率來。

一個命中率為100%的buffer pool不一定是最優的。通常你可以降低KEEP池的大小，同時仍然保持足夠高的命中率。將一些塊從KEEP中移到其他的buffer pool中。

Note:  假如一個物件資料增加了，那它可能不太適合放到KEEP池了。你將開始丟失緩衝池中的資料。

If an object grows in size, then it might no longer fit in the KEEP buffer pool. You will begin to lose blocks out of the cache.

Each object kept in memory results in a trade-off. It is beneficial to keep frequently-accessed blocks in the cache, but retaining infrequently-used blocks results in less space for other, more active blocks.

要不要將物件KEEP到記憶體是一個折衷的方案。最好是將頻繁訪問的塊放到緩衝池中，如果留下不常訪問的物件將導致其他物件沒有足夠的記憶體。

 

7.2.9 RECYCLE Pool

It is possible to configure a RECYCLE buffer pool for blocks belonging to those segments that you do not want to remain in memory. The RECYCLE pool is good for segments that are scanned rarely or are not referenced frequently. If an application accesses the blocks of a very large object in a random fashion, then there is little chance of reusing a block stored in the buffer pool before it is aged out. This is true regardless of the size of the buffer pool (given the constraint of the amount of available physical memory). Consequently, the object's blocks need not be cached; those cache buffers can be allocated to other objects.

Memory is allocated to the RECYCLE buffer pool by setting the parameter DB_RECYCLE_CACHE_SIZE to the required size. This memory for the RECYCLE buffer pool is not a subset of the default pool.

Do not discard blocks from memory too quickly. If the buffer pool is too small, then blocks can age out of the cache before the transaction or SQL statement has completed execution. For example, an application might select a value from a table, use the value to process some data, and then update the record. If the block is removed from the cache after the SELECT statement, then it must be read from disk again to perform the update. The block should be retained for the duration of the user transaction.

將一些不想放到記憶體的物件配置到RECYCLE 緩衝池中是可以的。RECYCLE池中最好放一些那些很少訪問或者很少掃描的物件。假如一個應用程式以隨機的方式訪問一個大物件資料，那麼它在被交換出去前只有很小的機率會被再次訪問。這個無關緩衝池的大小（根據可用的實體記憶體）。因此，當物件不需要去快取時，這些緩衝物件就可以被交換出去，將記憶體讓給其他物件。

透過設定DB_RECYCLE_CACHE_SIZE去配置RECYCLE池的大小。它與KEEP池一樣不屬於DEFAULT池的一部分。即KEEP,RECYCLE,DEFAULT三個是平級的。

不要將記憶體中的塊過快地清除出去。假如buffer pool太小了，那麼在事務或者SQL還沒完成執行前就已經被交換出去了。例如一個應用程式從一個表中查詢了一個值，然後使用這個值去處理其他一些資料，然後要更新這個記錄。假如查詢完之後這個記錄就被交換出去了，那麼它必須從磁碟中再讀取一次以便完成更新。因此資料塊應該保留一段時間以便使用者的事務可以正常完成。