7 Configuring and Using Memory

This chapter explains how to allocate memory to Oracle Database memory caches, and how to use those caches. Proper sizing and effective use of the Oracle Database memory caches greatly improves database performance. Oracle recommends using automatic memory management to manage the memory on your system. However, you can choose to manually adjust the memory pools on your system, as described in this chapter.

這節解釋怎樣去分配記憶體到資料庫記憶體快取，以及如何使用這些快取。正確的調整大小和高效使用快取將會極大的提升系統效能。建議使用自動記憶體管理。但是你可以像本節中介紹的去手動調整記憶體池。

This chapter contains the following sections:

Understanding Memory Allocation Issues 理解記憶體分配問題
Configuring and Using the Buffer Cache 配置和使用快取
Configuring and Using the Shared Pool and Large Pool 配置使用共享池和大池
Configuring and Using the Redo Log Buffer 配置使用redo池
PGA Memory Management PGA記憶體管理
Managing the Server and Client Result Caches 管理服務端和客戶端的result cache

7.1 Understanding Memory Allocation Issues

Oracle Database stores information in memory caches and on disk. Memory access is much faster than disk access. Disk access (physical I/O) take a significant amount of time, compared with memory access, typically in the order of 10 milliseconds. Physical I/O also increases the CPU resources required, because of the path length in device drivers and operating system event schedulers. For this reason, it is more efficient for data requests of frequently accessed objects to be perform by memory, rather than also requiring disk access.

A performance goal is to reduce the physical I/O overhead as much as possible, either by making it more likely that the required data is in memory, or by making the process of retrieving the required data more efficient.

oracle將資訊儲存在記憶體快取和磁碟上。訪問記憶體要比訪問磁碟快的多。與訪問記憶體相比，訪問磁碟（IO）會消耗了大量的時間，通常需要在10毫秒左右。物理IO同樣也增加了CPU資源的佔用，因為裝置驅動的路徑長度和作業系統事件排程（這裡不太理解）。基於這個原因，將頻繁訪問的資料放在記憶體中執行將會非常高效，而不是請求磁碟訪問。

This section contains the following topics:

Oracle Memory Caches 記憶體快取
AMM管理記憶體
Automatic Shared Memory Management ASMM管理記憶體
Dynamically Changing Cache Sizes 動態的改變快取大小
應用考慮
Operating System Memory Use 作業系統記憶體使用
Iteration During Configuration 迭代配置

7.1.1 Oracle Memory Caches

The main Oracle Database memory caches that affect performance are: 影響效能的快取有這個：

Shared pool
Large pool
Java pool
Buffer cache
Streams pool size
Log buffer
Process-private memory, such as memory used for sorting and hash joins

7.1.2 Automatic Memory Management

Oracle strongly recommends the use of automatic memory management to manage the memory on your system. Automatic memory management enables Oracle Database to automatically manage and tune the instance memory. Automatic memory management can be configured using a target memory size initialization parameter (MEMORY_TARGET) and a maximum memory size initialization parameter (MEMORY_MAX_TARGET). The database tunes to the target memory size, redistributing memory as needed between the system global area (SGA) and the instance program global area (instance PGA). Before setting any memory pool sizes, consider using the automatic memory management feature of Oracle Database. If you must configure memory allocations, consider using the Memory Advisor for managing memory.

oracle強烈的建議你使用AMM管理記憶體（自動記憶體管理）。AMM允許資料庫自動管理和調優例項記憶體。AMM透過memory_target,memory_max_target引數進行配置。資料庫調優目標記憶體大小，在SGA與PGA間按需進行記憶體分佈（透過粒度及MMAP技術實現的）。在設定任何記憶體部件大小之前，先考慮使用AMM特性（即讓資料庫自動去調整，而不是自己去設定）。假如你必須要配置記憶體部件大小，那就考慮使用記憶體顧問，以便確定需要實際需要的記憶體大小。

7.1.3 Automatic Shared Memory Management

Automatic Shared Memory Management simplifies the configuration of the SGA. To use Automatic Shared Memory Management, set the SGA_TARGET initialization parameter to a nonzero value and set the STATISTICS_LEVEL initialization parameter to TYPICAL or ALL. Set the value of the SGA_TARGET parameter to the amount of memory that you intend to dedicate for the SGA. In response to the workload on the system, the automatic SGA management distributes the memory appropriately for the following memory pools:

ASMM簡化了SGA的配置。為了使用ASMM，需要設定sga_target = xxxx和statistics_level = typical|all。將SGA_TARGET設定成你希望的記憶體大小。根據系統壓力的不同，自動的SGA管理下面的記憶體部件：

Database buffer cache (default pool)
Shared pool
Large pool
Java pool
Streams pool

If these automatically tuned memory pools had been set to nonzero values, those values are used as minimum levels by Automatic Shared Memory Management. You would set minimum values if an application component needs a minimum amount of memory to function properly.

SGA_TARGET is a dynamic parameter that can be changed by accessing the SGA Size Advisor from the Memory Parameters SGA page in Oracle Enterprise Manager, or by querying the V$SGA_TARGET_ADVICE view and using the ALTER SYSTEM command. SGA_TARGET can be set less than or equal to the value of SGA_MAX_SIZE initialization parameter. Changes in the value of SGA_TARGET automatically resize the automatically tuned memory pools.

If you dynamically disable SGA_TARGET by setting its value to 0 at instance startup, Automatic Shared Memory Management will be disabled and the current auto-tuned sizes will be used for each memory pool. If necessary, you can manually resize each memory pool using the DB_CACHE_SIZE, SHARED_POOL_SIZE, LARGE_POOL_SIZE, JAVA_POOL_SIZE, and STREAMS_POOL_SIZE initialization parameters.

假如這些自動最佳化的記憶體池已經被設定成非0值，那麼這些值就被ASMM作為最小需要保留的值。假如你知道你的應用程式需要佔用的最小的部件記憶體，那就設定一個最小值給它。

SGA_TARGET是一個動態引數，可以透過OEM的SGA SIZE Advisor來實時的調整其大小，或者查詢 v$sga_target_advice 檢視及使用alter system進行更新。但是sga_target <= sga_max_size引數。當改變SGA_TARGET的時候，SGA中的相關部件也一起隨之改變了。

假如你在啟動時將SGA_TARGET=0的話，ASMM將會被禁用掉，並且當前各個部件的記憶體大小將作為其實際大小了（為什麼會這樣子呢？關閉資料庫後，去看看spfile就明白了！oracle將上次系統的各個部件大小都記錄下來，以便下次重啟的時候直接分配各部件到最佳狀態。這就是為什麼當ASMM關閉後其他部件會有正常的大小設定）。如果有必要的話，你可以手動的設定下面記憶體部件的大小：

db_cache_size, shared_pool_size, large_pool_size, java_pool_size, streams_pool_size引數。

The following pools are manually sized components and are not affected by Automatic Shared Memory Management:

下面這些部件是要手工去設定的，不會被ASMM影響到： log buffer, keep, recycle, other non-8k block size, fixed sga

Log buffer
Other buffer caches (such as KEEP, RECYCLE, and other nondefault block size)
Fixed SGA and other internal allocations

To manually size these memory pools, you must set the DB_KEEP_CACHE_SIZE, DB_RECYCLE_CACHE_SIZE, DB_nK_CACHE_SIZE, and LOG_BUFFER initialization parameters. The memory allocated to these pools is deducted from the total available for SGA_TARGET when Automatic Shared Memory Management computes the values of the automatically tuned memory pools.

手動去調整這些記憶體部件，你必須去設定：db_keep_cache_size, db_recycle_cache_size, db_16k_cache_size, log_buffer引數。雖然這些引數不能被SGA_TARGET自動設定，但是這些引數加起來的記憶體是包含在SGA_TARGET中的，所以在設定真正需要的記憶體時，多加上這些記憶體到SGA_TARGET上。

7.1.4 Dynamically Changing Cache Sizes

If the system is not using Automatic Memory Management or Automatic Shared Memory Management, you can choose to dynamically reconfigure the sizes of the shared pool, the large pool, the buffer cache, and the process-private memory. The following sections contain details on sizing of caches:

假如系統沒有使用ASMM或者AMM管理，你可以動態的重新配置這些shared pool,large pool,buffer cache, process-private部件的大小。下面的章節討論了調整大小的一些細節：

Configuring and Using the Buffer Cache
Configuring and Using the Shared Pool and Large Pool
Configuring and Using the Redo Log Buffer

The size of these memory caches is configurable using initialization configuration parameters, such as DB_CACHE_SIZE, JAVA_POOL_SIZE, LARGE_POOL_SIZE, LOG_BUFFER, and SHARED_POOL_SIZE. The values for these parameters are also dynamically configurable using the ALTER SYSTEM statement except for the log buffer pool and process-private memory, which are static after startup.

Memory for the shared pool, large pool, java pool, and buffer cache is allocated in units of granules. The granule size is 4MB if the SGA size is less than 1GB. If the SGA size is greater than 1GB, the granule size changes to 16MB. The granule size is calculated and fixed when the instance starts up. The size does not change during the lifetime of the instance.

The granule size that is currently being used for SGA can be viewed in the view V$SGA_DYNAMIC_COMPONENTS. The same granule size is used for all dynamic components in the SGA.

You can expand the total SGA size to a value equal to the SGA_MAX_SIZE parameter. If the SGA_MAX_SIZE is not set, you can decrease the size of one cache and reallocate that memory to another cache if necessary. SGA_MAX_SIZE defaults to the aggregate setting of all the components.

這些部件記憶體大小是由：db_cache_size,java_pool_size,large_pool_size,log_buffer,shared_pool_size引數來配置的。這些引數可以直接透過alter system語句來配置，但是log buffer pool和process-private記憶體是靜態引數，必須重新啟動才能應用。

shared pool,large pool, java pool 和buffer cache的記憶體分配是按照粒度為單位進行分配的。如果SGA分配的大小小於1G的話，那麼這個粒度就是4M。假如SGA大於1G，那麼粒度就是16M。這個粒度的值是在例項啟動時計算出來的，這個值在系統執行期內是不會改變的。

（注：為什麼需要這個粒度呢？那考慮一下如果沒有這個粒度會怎樣？如果沒有的話，那麼oracle每次分配記憶體的時候就需要到處去找哪裡有free的記憶體塊，然後再確定其大小，最後還要更新自己的“資料庫字典”以防止其他程式也從這裡分配等等，非常的複雜。

有了粒度之後就好辦了，如果一個“粒度塊”沒有使用，那就把那個4M或者16M的塊拿過來使用，用完之後再還回去，用個簡單的例子說明就是集裝箱的道理，將整個粒度作為一個小的整體，整個SGA就是輪船，輪船裡面都是由集裝箱組成的，至於你要在集裝箱裡裝什麼是自己的事情了。在11G中，oracle使用了MMAP技術，可以在/dev/shm中看到有很多檔案，每個都是以4M或者16M大小的，這個就是記憶體的一個對映，也是粒度的一個直觀展示, mmap的意思就是memory map的意思。在10G中，oracle還是使用傳統的共享記憶體技術，啟動時分配大量的記憶體，然後Oracle又將其邏輯的分成許多粒度塊使用。而在11G中直接透過作業系統的功能就實現了此方法。下面是我從MAN手冊中取的mmap系統呼叫方法，瞭解即可。

那是不是隻有oracle使用了這個粒度呢？顯然不是的，生活中到處都有，作業系統也是一樣。假如有16G的記憶體，那作業系統是如何管理呢？ ------ 它是將記憶體按4K為一個粒度進行劃分的。那為什麼又是要以4K為單位劃分呢？因為是intel的地址匯流排這麼定的。在32位系統中，注意不是指32位作業系統，而是地址匯流排只有32位的系統中，比如11111111,11111111,11110000,00000000，我隨意的寫了32位數字，最後這12位，我用12個0來表示，2¹²=4096=4K，那上面32 – 12 = 20位又是做什麼呢？在linux中是按照段頁式管理的，頁就是這4K的單位的粒度，前面20位就代表一個有2²⁰=1M個頁面，每個頁面又是4K大小。

理解了這部分之後，就能知道一個程式最小的記憶體佔用了。比如我寫了一個簡單的printf(“hello world!\n”);程式，那這個程式按理說最多戰勝幾十個位元組，但是實際上作業系統要給它一個頁面的，即4K位元組使用的。同樣如果我想要4M的記憶體，作業系統就給我們1024個頁面（1024*4K = 4M）

）

可以透過 v$sga_dynamic_components 檢視來檢視粒度大小。SGA中所有的部件都使用相同的粒度。 v$sgainfo中也有的。

你可以將總的SGA大小擴充套件到SGA_MAX_SIZE大小。假如SGA_MAX_SIZE沒有設定，如果需要的話，你可以將一個部件的記憶體減少，然後重新分配給其他的部件。SGA_MAX_SIZE預設是所有部件的記憶體總和。

Note:

SGA_MAX_SIZE cannot be dynamically resized. 注意這個SGA_MAX_SIZE是靜態引數，修改完一定要加上scope=spfile,否則無法修改成功。

The maximum amount of memory usable by the instance is determined at instance startup by the initialization parameter SGA_MAX_SIZE. You can specify SGA_MAX_SIZE to be larger than the sum of all of the memory components, such as buffer cache and shared pool. Otherwise, SGA_MAX_SIZE defaults to the actual size used by those components. Setting SGA_MAX_SIZE larger than the sum of memory used by all of the components lets you dynamically increase a cache size without needing to decrease the size of another cache.

Oracle Performance Tuning 11g2 (7-1)

相關文章