Oracle數(shù)據(jù)庫(kù)包含了如下基本內(nèi)存組件

創(chuàng)新互聯(lián)建站是一家專注網(wǎng)站建設(shè)、網(wǎng)絡(luò)營(yíng)銷策劃、小程序制作、電子商務(wù)建設(shè)、網(wǎng)絡(luò)推廣、移動(dòng)互聯(lián)開發(fā)、研究、服務(wù)為一體的技術(shù)型公司。公司成立十年以來，已經(jīng)為1000多家茶藝設(shè)計(jì)各業(yè)的企業(yè)公司提供互聯(lián)網(wǎng)服務(wù)?，F(xiàn)在，服務(wù)的1000多家客戶與我們一路同行，見證我們的成長(zhǎng)；未來，我們一起分享成功的喜悅。

• System global area (SGA)

  The SGA is a group of shared memory structures, known as SGA components, that contain data and control information for one Oracle Database instance. The SGA is shared by all server and background processes. Examples of data stored in the SGA include cached data blocks and shared SQL areas.

• Program global area (PGA)

  A PGA is a nonshared memory region that contains data and control information exclusively for use by an Oracle process. The PGA is created by Oracle Database when an Oracle process is started.

  One PGA exists for each server process and background process. The collection of individual PGAs is the total instance PGA, or instance PGA. Database initialization parameters set the size of the instance PGA, not individual PGAs.

• User Global Area (UGA)

  The UGA is memory associated with a user session.

• Software code areas

  Software code areas are portions of memory used to store code that is being run or can be run. Oracle Database code is stored in a software area that is typically at a different location from user programs—a more exclusive or protected location.

內(nèi)存管理

Oracle依賴于內(nèi)存相關(guān)的初始化參數(shù)來控制內(nèi)存的管理。

內(nèi)存管理有如下三個(gè)選項(xiàng)

• Automatic memory management

  You specify the target size for instance memory. The database instance automatically tunes to the target memory size, redistributing memory as needed between the SGA and the instance PGA.

• Automatic shared memory management

  This management mode is partially automated. You set a target size for the SGA and then have the option of setting an aggregate target size for the PGA or managing PGA work areas individually.

• Manual memory management

  Instead of setting the total memory size, you set many initialization parameters to manage components of the SGA and instance PGA individually.

UGA概覽

UGA是會(huì)話內(nèi)存，用來保存會(huì)話變量例如登錄信息，已經(jīng)數(shù)據(jù)庫(kù)會(huì)話需要的其他信息。

當(dāng)PL/SQL包加載進(jìn)內(nèi)存時(shí)，UGA中包含了package state，也就是調(diào)用PL/SQL時(shí)指定的變量值。

PGA概覽

PGA緩沖區(qū)，則主要是為了某個(gè)用戶進(jìn)程所服務(wù)的。這個(gè)內(nèi)存區(qū)不是共享的，只有這個(gè)用戶的服務(wù)進(jìn)程本身才能夠訪問它自己的PGA區(qū)。做個(gè)形象的比喻，SGA就好像是操作系統(tǒng)上的一個(gè)共享文件夾，不同用戶可以以此為平臺(tái)進(jìn)行數(shù)據(jù)方面的交流。而PGA就好像是操作系統(tǒng)上的一個(gè)私有文件夾，只有這個(gè)文件夾的所有者才能夠進(jìn)行訪問，其他用戶都不能夠訪問。雖然程序緩存區(qū)不向其他用戶的進(jìn)程開放，但是這個(gè)內(nèi)存區(qū)仍然肩負(fù)著一些重要的使命，如數(shù)據(jù)排序、權(quán)限控制等等都離不開這個(gè)內(nèi)存區(qū)。

PGA組件

私有SQL區(qū)包含了綁定變量值和運(yùn)行時(shí)期內(nèi)存結(jié)構(gòu)信息等數(shù)據(jù)。每一個(gè)運(yùn)行SQL語(yǔ)句的會(huì)話都有一個(gè)塊私有SQL區(qū)。一個(gè)游標(biāo)的私有SQL區(qū)又分為兩個(gè)生命周期不同的區(qū)：

永久區(qū)，包含綁定變量信息。當(dāng)游標(biāo)關(guān)閉時(shí)被釋放。

 運(yùn)行區(qū)，當(dāng)執(zhí)行結(jié)束時(shí)釋放。

Cursor 

A cursor is a name or handle to a specific private SQL area

SGA包含如下組件

• Database Buffer Cache

• Redo Log Buffer

• Shared Pool

• Large Pool

• Java Pool

• Streams Pool

• Fixed SGA

Buffer cache

Buffer Cache是SGA區(qū)中專門用于存放從數(shù)據(jù)文件中讀取的的數(shù)據(jù)塊拷貝的區(qū)域。Oracle進(jìn)程如果發(fā)現(xiàn)需要訪問的數(shù)據(jù)塊已經(jīng)在buffer cache中，就直接讀寫內(nèi)存中的相應(yīng)區(qū)域，而無需讀取數(shù)據(jù)文件，從而大大提高性能。Buffer cache對(duì)于所有oracle進(jìn)程都是共享的，即能被所有oracle進(jìn)程訪問。

Buffer的大小和數(shù)據(jù)塊一樣。

Buffer cache按照類型分為3個(gè)池

• Default pool

  This pool is the location where blocks are normally cached. Unless you manually configure separate pools, the default pool is the only buffer pool.

• Keep pool

  This pool is intended for blocks that were accessed frequently, but which aged out of the default pool because of lack of space. The goal of the keep buffer pool is to retain objects in memory, thus avoiding I/O operations.

• Recycle pool

  This pool is intended for blocks that are used infrequently. A recycle pool prevent objects from consuming unnecessary space in the cache.

Oracle還提供了非標(biāo)準(zhǔn)塊大小的buffer cache。如果你建立的表空間指定的塊大小為非數(shù)據(jù)庫(kù)塊大小，那么將使用這些buffer cache來緩存數(shù)據(jù)塊。

首先Oracle 以每個(gè)數(shù)據(jù)塊的文件號(hào)、塊號(hào)、類型做hash運(yùn)算，得到hash值。

對(duì)于hash值相同的塊，放在一個(gè)Hash Bucket中。

因?yàn)閎uffer的大小畢竟有限，buffer中的數(shù)據(jù)塊需要根據(jù)一定的規(guī)則提出內(nèi)存。

Oracle采用了LRU算法維護(hù)一個(gè)LRU鏈表，來決定哪些數(shù)據(jù)塊被淘汰。

通用的淘汰算法如下

Oracle改進(jìn)了LRU算法，引入了Touch count概念、以及LRU鏈表分為熱端頭和冷端頭。

Touch count：

 用來記錄數(shù)據(jù)塊訪問的頻繁度，此數(shù)值在內(nèi)存中不受保護(hù)，多個(gè)進(jìn)程可以同時(shí)修改它。這個(gè)值并不是精準(zhǔn)的表示塊被訪問的次數(shù)，只是一種趨勢(shì)。3秒內(nèi)無論多少用戶，訪問多少次塊。此值加1.

當(dāng)數(shù)據(jù)塊第一次被放到buffer中，Oracle將其放置在冷端的頭部。

如果buffer已經(jīng)沒有空閑空間，那么如何淘汰數(shù)據(jù)塊呢？Oracle從LRU的冷端尾部掃描數(shù)據(jù)塊，當(dāng)發(fā)現(xiàn)數(shù)據(jù)塊的Touch count大于等于2時(shí)，將數(shù)據(jù)塊移動(dòng)到熱端頭部，并將Touch count置為0 。當(dāng)Oracle發(fā)現(xiàn)Touch count小于2時(shí)，則淘汰該數(shù)據(jù)塊。

當(dāng)數(shù)據(jù)塊被修改了，我們把這個(gè)塊稱之為臟塊。臟塊在寫入磁盤前，是不會(huì)被踢出buffer的。

如果LRU中的臟塊比較多，每次申請(qǐng)新的空間時(shí)，都要掃描很多臟塊，但是又不能被淘汰。效率很低。

為此Oracle因?yàn)榱伺KLRU鏈表。專門用來記錄臟數(shù)據(jù)塊。

當(dāng)塊被修改，并不會(huì)馬上從LRU鏈表中移動(dòng)到LRUW中。只有當(dāng)Oracle需要淘汰數(shù)據(jù)塊時(shí)，才會(huì)去掃描LRU鏈表，此時(shí)發(fā)現(xiàn)塊為臟塊，將數(shù)據(jù)塊移動(dòng)到LRUW鏈表中。

檢查點(diǎn)鏈表

通過上面的描述，我們知道臟塊在LRU和LRUW鏈表中都有。那么當(dāng)dbwr寫數(shù)據(jù)時(shí)，這兩個(gè)鏈表都要掃描。首先效率比較低，并且無法保證先修改的數(shù)據(jù)塊先被寫入磁盤。

為此Oracle引入了檢查點(diǎn)隊(duì)列，該隊(duì)列按照數(shù)據(jù)塊第一次被修改順序?qū)⑴K塊鏈接到一起。

dbwr寫臟塊時(shí)，只需讀取檢查點(diǎn)隊(duì)列即可。

并且每個(gè)數(shù)據(jù)塊與記錄了日志條目的位置

redo log buffer

用戶進(jìn)程將redo entries 拷貝到redo log buffer中。LGWR負(fù)責(zé)將其寫到磁盤中。

Redo entries contain the information necessary to reconstruct, or redo, changes made to the database by DML or DDL operations. Database recovery applies redo entries to data files to reconstruct lost changes.

共享池

Library Cache：

主要存放shared curosr（SQL）和PLSQL對(duì)象（function，procedure，trigger）的信息，以及這些對(duì)象所依賴的table，index，view等對(duì)象的信息。

Private SQL Areas與Shared SQL Area的關(guān)系

數(shù)據(jù)字典緩存

用來緩存系統(tǒng)數(shù)據(jù)字典表的內(nèi)容，與普通表的緩存不同，普通表以塊為單位緩存到buffer cache中。而數(shù)據(jù)字典緩存以行為單位，緩存到shared pool中的data dictionary cache中。

Server result cache

用來緩存sql或者plsql的執(zhí)行結(jié)果。

大池：

 The large pool can provide large memory allocations for the following:

• UGA for the shared server and the Oracle XA interface (used where transactions interact with multiple databases)

• Message buffers used in the parallel execution of statements

• Buffers for Recovery Manager (RMAN) I/O slaves

配置內(nèi)存

Oracle提供了兩個(gè)初始化參數(shù)用來配置內(nèi)存自動(dòng)管理

MEMORY_TARGET：sga+pga內(nèi)存之和，Oracle自動(dòng)分配SGA和PGA的大小。

MEMORY_MAX_TARGET：MEMORY_TARGET可以設(shè)置大小的上限。

SGA自動(dòng)內(nèi)存管理

設(shè)置初始化參數(shù)SGA_TARGET為非0值，并且將STATISTICS_LEVEL的值設(shè)置為TYPICAL或者ALL.

PGA自動(dòng)內(nèi)存管理

PGA_AGGREGATE_TARGET設(shè)置為非0值。

如果workarea_size_policy為auto則sort_area_size,hash_area_size等參數(shù)設(shè)置被忽略，如果workarea_size_policy為manual，則sort_area_size,hash_area_size等參數(shù)設(shè)置生效。

也可以手工配置其他各個(gè)內(nèi)存池的大小。當(dāng)配置了內(nèi)存自動(dòng)管理時(shí)，有配置了具體池的大小，那么該配置為自動(dòng)內(nèi)存分配時(shí)的最小大小。

查看內(nèi)存情況

The following views provide information about dynamic resize operations:

• V$MEMORY_CURRENT_RESIZE_OPS displays information about memory resize operations (both automatic and manual) which are currently in progress.

• V$MEMORY_DYNAMIC_COMPONENTS displays information about the current sizes of all dynamically tuned memory components, including the total sizes of the SGA and instance PGA.

• V$MEMORY_RESIZE_OPS displays information about the last 800 completed memory resize operations (both automatic and manual). This does not include in-progress operations.

• V$MEMORY_TARGET_ADVICE displays tuning advice for the MEMORY_TARGET initialization parameter.

• V$SGA_CURRENT_RESIZE_OPS displays information about SGA resize operations that are currently in progress. An operation can be a grow or a shrink of a dynamic SGA component.

• V$SGA_RESIZE_OPS displays information about the last 800 completed SGA resize operations. This does not include any operations currently in progress.

• V$SGA_DYNAMIC_COMPONENTS displays information about the dynamic components in SGA. This view summarizes information based on all completed SGA resize operations that occurred after startup.

• V$SGA_DYNAMIC_FREE_MEMORY displays information about the amount of SGA memory available for future dynamic SGA resize operations.