本節書摘來自異步社群出版社《oracle資料庫管理與維護實戰》一書中的第2章，第2.2節，作者： 何偉娜 , 常建功，更多章節内容可以通路雲栖社群“異步社群”公衆号檢視。
oracle資料庫管理與維護實戰
oracle記憶體存儲了資料字典資訊（即關于對象、邏輯結構、模式、權限等等的中繼資料）、緩沖的應用資料、sql語言、pl/sql和java程式資料，以及事物、控制、使用者請求資訊。圖2-3是oracle記憶體結構圖，oracle記憶體主要由sga（系統全局區，system global area）和pga（程式全局區，program global area）兩個區組成，此外還有重做日志緩沖區、大池、java池等。
2-3
sga是資料庫所有使用者共享的存儲區，它在執行個體啟動時配置設定，執行個體關閉時回收。從oracle 9i起，系統可動态配置設定sga大小，即在資料庫運作期間可改變sga中各緩沖的大小，并且及時生效，而oracle 9i之前版本的資料庫，sga的參數更改後必須将資料庫重新啟動才生效。在動态調整sga各緩沖大小時，要注意的是必須低于初始化參數表sga_max_size的值。
sga的大小對資料庫性能有重大影響，執行個體調整主要是對sga内各緩沖大小的調整。
1．sga的自動記憶體管理
oracle 10g之前版本的資料庫，資料庫管理者需要手動配置與sga有關的大量初始化參數，有shared_pool_size、db_cache_size、java_pool_size和large_pool_size等。而從oracle 10g資料庫起，則使用自動資料庫管理，資料庫管理者隻需配置sga記憶體總量，即隻需配置初始化參數：sga_target，資料庫會自動給sga内各區配置設定大小。
自動配置設定sga内各區大小時，各區的大小能自動随需求變動。舉一個例子，如果總共有1gb大小的記憶體供sga使用，并且設定如下初始化參數：
shared_pool_size = 128m
db_cache_size = 896m
如果用sga自動管理，将sga_target大小設定成1gb，當一個應用程式需要更大的shared_pool_size共享池，oracle可以從db_cache_size緩沖區中調整相應的記憶體給shared_pool_size。僅設定一個sga總量有一個優勢，就是很少會發生記憶體溢出。
sga内的參數可以自動調整大小，如共享池、java池、大池、資料庫塊緩沖區、streams池。如果不使用這些緩沖區，這些緩沖區的大小就為0；而一旦要使用某個緩沖區，oracle就會自動給這部分配置設定一個合理的大小而不需要人工幹預。對使用者來說，這些緩沖的調整和配置設定是透明的。
資料庫執行個體一直會監視sga記憶體的使用。執行個體會用内部視圖和一些統計資料來決定如何給各元件最優配置設定記憶體。當sga記憶體需求變化時，資料庫會根據一定的算法，考慮目前和長遠的需求來重新配置設定記憶體。
但sga區中還有一部分記憶體區必需手動調整，這些區有keep/recycle緩沖區，相應的初始化參數為db_keep_cache_size和db_recycle_cache_size。還有非标準資料塊參數，由參數db_nk_cache_size，n = {2, 4, 8, 16, 32}設定。
資料庫緩沖區是sga中的一個高速緩沖區域，用來存儲最近從資料檔案中讀出來的資料塊，如表、索引資料塊。資料庫緩沖區是oracle提高通路速度的一種有效方法。sga是所有使用者共享的。處理查詢時，伺服器程序會先從資料庫緩沖區查找所需資料塊，隻有緩沖區中沒有時才會通路磁盤。
資料庫緩沖區對資料庫性能有很大影響。在oracle 10g資料庫之前，資料庫緩沖區的大小由db_block_buffers參數決定。這個參數可在資料庫伺服器中的初始化參數檔案中設定。資料庫性能調優時，調整db_block_buffers大小是很重要的一部分。參數db_block_size的大小由db_block_size和db_block_buffer相乘得到。db_block_size是指緩沖區中可放的實體塊的大小，即資料庫一次i/o通路的大小。從oracle 10g資料庫後，資料庫緩沖區大小由oracle自動調整。
資料庫緩沖區有三種類型：dirty buffer、pinned buffers、free buffer。dirty buffer是已被修改需要寫回磁盤的資料塊，pinned buffers是正被通路的資料塊，free buffer是閑空資料塊。
oracle為了便于管理緩沖區，将緩沖區資料塊連成兩個隊列：寫隊列和lru隊列。寫隊列中的資料塊排隊等待被寫回磁盤。oracle修改了資料之後，并不立即将資料寫回磁盤，而是累積到一定數量後才寫回。因為通路磁盤i/o速度較慢，如果資料塊一被修改就寫回到磁盤，會導緻頻繁通路i/o，造成i/o瓶頸，影響系統性能；而用隊列後就可以将多個資料塊内容一次性寫回磁盤，節省i/o資源。
lru隊列又稱為最近最少使用隊列，由free buffers、pinned buffers以及沒有加入到寫隊列的dirty buffer組成。所謂最近最少使用隊列就是将經常使用的資料塊放在隊首，将最不經常使用的放在隊尾，當有新的資料塊要加入時，則最先淘汰最不經常使用的隊尾資料塊。
當磁盤資料塊寫入緩沖時，程序首先會在lru隊列搜尋free buffers，從lru隊尾開始搜尋，搜尋到足夠數量的free buffers後，将資料從磁盤讀到這些free buffers内，将這些free buffers放在lru最不經常使用的隊尾。在搜尋free buffers過程中如果發現隊列中有dirty buffer，程序就順便将這dirty buffer放入寫隊列。在搜尋free buffers的過程中，程序并不會周遊整個lru隊列，而是在搜尋了一定數量的資料塊後如果仍然沒有搜尋到足夠的free buffers塊，就停止搜尋。這時使用者程序會通知dbwr（資料庫寫程序）将dirty buffer資料寫回磁盤，以釋放空間。
oracle将資料庫緩沖區劃成三個區域：keep、recycle、default。keep區中的内容能長期駐留，資料塊不會被淘汰出去。這個區域的大小由buffer_pool_keep決定。recycle區中存儲很少使用的資料，該區的記憶體可直接回收，其大小由buffer_pool_ recycle決定，剩餘的緩沖都屬于default區。
使用者通過insert、update、delete、create、alter、drop等sql指令更改了資料庫後，伺服器程序會将這些修改記錄到重做日志緩沖區内，這些修改記錄也叫重做記錄（entry）。資料庫發生崩潰後，使用者可從重做日志緩沖區讀取修改記錄恢複資料庫。重做日志緩沖區記錄了發生修改的塊、修改的位置，以及修改後的新值。注意，重做記錄隻記錄每一個修改，并不記錄發生修改的塊類型，是以不能區分修改是在資料塊上還是在復原或索引段上。
重做日志緩沖區是一個循環緩沖區，緩沖區在被覆寫之前會由背景程序lgwr（日志寫入程序）将緩沖内容寫入聯機重做日志檔案，是以oracle資料庫對資料庫的每次更改都有記錄。重做日志緩沖區的大小由初始參數log_buffer決定。
共享池包括庫高速緩沖（library cache）和資料字典緩沖（data dictionary cache），如圖2-3所示。庫高速緩沖區又包括共享sql區、私有sql區（隻在共享伺服器内有）、共享pl/sql區，以及控制結構區。從oracle 10g開始，資料庫能夠自動調整共享池大小。
1．共享sql區
oracle将執行過的sql分成兩部分：共享sql區和私有sql區。當使用者執行sql語句時，oracle會将最近執行過的sql語句的文本、編譯後的文法分析樹和執行計劃（指要完成一條sql語句，oracle伺服器所需具體實施的步驟）存入共享sql區，而将sql語句中的變量值存入私有sql區（在共享伺服器中存入私有sql區，在專用伺服器中存入pga内）。當伺服器再次執行相同sql語句時，伺服器程序将不再對這條語句執行文法分析，而是直接執行sql共享區中已存在的内容。這種方式有利于提高資料庫性能。
注意，這裡的相同是指語句完全相同，即語句結構相同，大小寫相同，變量值相同，否則oracle伺服器會認為不同語句。
2．私有sql區
私有sql區中存放的是sql語句執行時與每一個會話有關的私有資料，如連接配接變量的值。專用伺服器内私有sql區存在pga中；共享伺服器内私有sql區存在共享池中。
3．共享pl/sql區
oracle處理pl/sql程式與處理sql語句的方法相同。執行一個pl/sql程式單元時，oracle将程式單元放入共享pl/sql區，而将程式單元内的sql語句放到共享sql區中；當再次需要執行相同的程式單元時，就直接從記憶體中調用，而不需通路磁盤。
4．控制結構區
這是供執行個體内部使用的一段記憶體區，存放了鎖、鎖存器等方面的資訊。
資料字典緩沖區是共享池的一部分，又稱為字典區或行緩沖區，它包含了資料庫的結構、使用者資訊和資料庫的表、視圖等資訊。資料字典緩沖區存儲了資料庫裡所有表和視圖的名字、資料庫基表的列名和列資料類型以及所有oracle使用者的權限等。
oracle在分析sql語句時會頻繁地通路資料字典。由于oracle的頻繁通路，記憶體中專門設定了這個存儲區存放資料字典。資料字典緩沖區由oracle的所有使用者程序共享。
資料字典緩沖區同樣采用lru算法來管理，緩沖區大小由資料庫内部管理。資料字典緩沖區包含在sga的共享池内，是以它的大小受共享池大小的限制。
字典緩沖區對資料庫性能影響很大。在執行sql語句過程中，伺服器程序會經常通路資料字典緩沖區。如果緩沖區太小，資料庫就會因為在緩沖中找不到所需資訊而反複通過i/o從磁盤擷取，嚴重影響系統性能。
pga（程式全局區，program global area）包括會話資訊、堆棧空間、排序區，以及遊标狀态。會話資訊存放的是會話的權限、角色和會話性能統計等資訊，堆棧空間記憶體放的是變量、數組和屬于會話的其他資訊，排序區則是用于排序的一段專用空間，遊标狀态存放的是目前使用的各種遊标的處理階段。
使用者程序連接配接到oracle資料後，伺服器建立會話，同時配置設定一個pga區。一個pga區由一個oracle使用者程序所使用，不能共享。對專用伺服器（一個資料庫連接配接對應一個專用伺服器程序），pga儲存堆棧空間資訊和會話資訊、遊标狀态、排序區。對共享伺服器，pga隻儲存堆棧空間資訊，會話資訊、遊标狀态、排序區儲存在sga中。pga的結構如圖2-4所示。
初始化參數pga_aggregate_target可以改變pga的大小。pga_aggregate_target是指所有伺服器程序pga占用記憶體的大小之和，它随伺服器程序一起産生和釋放。
排序區（sort area）是給所要排序的sql語句提供的專用記憶體空間。一個排序區使用的例子如圖2-5所示，如執行語句select ename，sal from emp，不需要排序，程序dbwr先将資料從磁盤讀到資料庫緩沖區，再将資料傳給使用者程序。而執行select ename,sal from emp order by sal時，因為要以sal排序，是以資料從磁盤讀到資料庫緩沖後，先以sal排序，然後将排序結果存入排序區，再将排序結果傳回給使用者程序。
如果記憶體不夠，oracle會将資料分割成很多小塊放到排序區内，然後對每一小塊分别進行排序。排序過程中oracle将排序區存放不下的資料都存放到磁盤臨時段中，最後将這些小段合并起來傳回給使用者程序。在排序過程中，排序區的大小是動态變化的，但最大不能超過參數sort_area_size的值。
還有一個與排序有關的參數sort_area_retained_size，它決定了排序完成後排序區占用記憶體的大小。排序區的記憶體釋放後仍然屬于伺服器程序，并沒釋放給作業系統。為了提高排序速度，使用者應适當調節好sort_area_size大小，使排序盡量在記憶體中進行。
軟體代碼區用于存儲oracle系統程式和使用者程式正在執行或可以執行的程式代碼。軟體代碼區是隻讀的，可安裝成共享或非共享兩種形式。oracle系統程式是可共享的，用以使多個oracle使用者可存取。使用者程式可以設為共享，也可以設為不共享。oracle系統程式與使用者程式不同，前者存放在軟體代碼區中較為安全的地方。
軟體代碼區的大小一般是固定的，隻有在軟體進行修改或重新安裝時才能由作業系統改變大小。在支援多例程oracle中，同一台機器上運作的幾個資料庫可以使用同一個代碼區。
大池（large pool）是一個可選記憶體區，從oracle 9i版本資料庫開始，大池大小由參數large_pool_size決定；但oracle 10g版本資料庫以後，oracle自動調整大池大小。在以下幾種情況下我們要使用大池：使用共享伺服器、執行備份和恢複操作、使用i/o slaves（要配置dbwr_io_slaves參數）。大池主要為一些需要消耗大量記憶體的操作提供更大記憶體空間。
java池記憶體儲了java語句的文本、文法分析表等資訊。java池為java的指令提供文法分析，從oracle 9i版本資料庫開始，java池大小由參數java_pool_size決定；但oracle 10g版本資料庫以後，oracle自動調整大小。如果要安裝java vm，使用者就必須啟用java池。
streams池的主要功能是存放消息（message）。池中存放的消息是共享的，streams的資訊可以從一個資料庫傳播到另一個資料庫。利用streams池管理消息比原來捕獲和管理消息更容易。
使用者可以在sga中配置設定streams池。streams池大小通過初始化參數streams_pool_size配置設定。如果沒有設定這個參數，oracle在streams第一次使用時會自動建立streams池。