一、概述
在《Oracle內存結構研究-PGA篇》一文中提到,PGA是一個服務器進程的專用的私有內存區,而SGA則是共享內存區。
SGA由多個部分組成:
1, 固定SGA(Fixed SGA)
2, 塊緩沖區(Db cache)
3, 重做日志緩沖區(Redo log buffer)
4, Java池(Java pool)
5, 大池(Large pool)
6, 共享池(Shared pool)
7, 流池(Stream pool)
有如下參數控制共享池相關組件大小:
1, JAVA_POOL_SIZE:控制Java池大小。
2, SHARED_POOL_SIZE:9i中控制共享池中占用最大的部分,10g以上控制共享池大小。
3, LARGE_POOL_SIZE:控制大池大小。
4, DB_*K_CACHE_SIZE:控制不同塊大小的緩沖區大小。
5, LOG_BUFFER:控制重做日志緩沖區大小。
6, SGA_TARGET:10g以上控制自動SGA內存管理的總內存大小。
7, SGA_MAX_SIZE:控制SGA可以達到的最大大小,改變需重啟數據庫。
下面將詳細介紹各個部分的作用和推薦設置。
二、SGA各組件作用
1, 固定SGA:
顧名思義,是一段不變的內存區,指向SGA中其他部分,Oracle通過它找到SGA中的其他區,可以簡單理解為用於管理的一段內存區。
2, 塊緩沖區:
查詢時,Oracle會先把從磁盤讀取的數據放入內存,以後再查詢相關數據時不用再次讀取磁盤。插入和更新時,Oracle會現在該區中緩存數據,之後批量寫到硬盤中。通過塊緩沖區,Oracle可以通過內存緩存提高磁盤的I/O性能。
塊緩沖區中有三個區域:
默認池(Default pool):所有數據默認都在這裡緩存。
保持池(Keep pool):用來緩存需要多次重用的數據。
回收池(Recycle pool):用來緩存很少重用的數據。
原來只有一個默認池,所有數據都在這裡緩存。這樣會產生一個問題:大量很少重用的數據會把需重用的數據“擠出”緩沖區,造成磁盤I/O增加,運行速度下降。後來分出了保持池和回收池根據是否經常重用來分別緩存數據。
這三部分內存區需要手動確定大小,並且之間沒有共享。例如:保持池中已經滿了,而回收池中還有大量空閒內存,這時回收池的內存不會分配給保持池。
9i開始,還可以設置db_nk_cache。9i之前數據庫只能使用相同的塊大小。9i開始同一個數據庫可以使用多種塊大小(2KB,4KB,8KB,16KB,32KB),這些塊需要在各自的db_nk_cache中緩存。如果為不同的表空間指定了不同的塊大小,需要為其設置各自的緩沖區。
3, 重做日志緩沖區(Redo log buffer):
數據寫到重做日志文件之前在這裡緩存,在以下情況中觸發:
每隔3秒
緩存達到1MB或1/3滿時
用戶提交時
緩沖區的數據寫入磁盤前
4, Java池(Java pool):
在數據庫中運行Java代碼時用到這部分內存。例如:編寫Java存儲過程在服務器內運行。需要注意的是,該內存與常見的Java編寫的B/S系統並沒關系。用JAVA語言代替PL/SQL語言在數據庫中寫存儲過程才會用到這部分內存。
5, 大池(Large pool):
下面三種情況使用到大池:
並行執行:存放進程間的消息緩沖區
RMAN:某些情況下用於磁盤I/O緩沖區
共享服務器模式:共享服務器模式下UGA在大池中分配(如果設置了大池)
6, 共享池(Shared pool)
共享池是SGA中最重要的內存段之一。共享池太大和太小都會嚴重影響服務器性能。
SQL和PL/SQL的解釋計劃、代碼,數據字典數據等等都在這裡緩存。
SQL和PL/SQL代碼在執行前會進行“硬解析”來獲得執行計劃及權限驗證等相關輔助操作。“硬解析”很費時間。對於響應時間很短的查詢,“硬解析”可以占到全部時間的2/3。對於響應時間較長的統計等操作,“硬解析”所占用的時間比例會下降很多。執行計劃及所需的數據字典數據都緩存在共享池中,讓後續相同的查詢可以減少很多時間。
不使用“綁定變量”導致:
系統需要花費大量的資源去解析查詢。
共享池中的代碼從不重用,系統花費很大代價管理這部分內存。
關於共享變量的優缺點討論已經超過了這篇文章的范疇,簡單來講,響應時間短的查詢要使用共享變量,響應時間長的統計不使用共享變量。
需要注意的是,SHARED_POOL_SIZE參數在9i中控制共享池中占用最大的部分,10g以上控制共享池總大小。
7, 流池(Stream pool)
9iR2以上增加了“流”技術,10g以上在SGA中增加了流池。流是用來共享和復制數據的工具。
三、SGA設置
沒有通用的設置,所有設置都要根據系統的負載、業務需求和硬件環境來進行調整。這裡只是總結出大體的設定,避免因SGA設置不當引起的問題。
1,自動SGA內存管理
在Oracle 10g中引入了自動SGA內存管理特性,DBA可以設定SGA_TARGET告訴Oracle可用的SGA內存為多大,由Oracle根據系統負載來動態調整各組件大小,相應的數定會保存在控制文件中,使數據庫重啟後也記得各組件大小。
需要注意一下幾點:
要使用自動SGA內存管理,STATISTICS_LEVEL參數必須設為TYPICAL或ALL,系統自動收集相應的信息用來動態調整SGA設定。
可以設定某個組件的值,Oracle使用此值為該組件的最小大小
可動態調整的參數:
DB_CACHE_SIZE,SHARED_POOL_SIZE,LARGE_POOL_SIZE,JAVA_POOL_SIZE。
需手動設置的參數:
LOG_BUFFER,STREAMS_POOL,DB_NK_CACHE_SIZE,DB_KEEP_CACHE_SIZE,DB_RECYCLE_CACHE_SIZE。
2,手動SGA內存管理
1) 32bit和64bit限制
在32位的操作系統中,Oracle最大可用內存為1.75g,也就是說SGA+PGA<=1.75g,超過這一限制的內存將不會被Oracle用到。
32位的Oracle可以裝到64位的操作系統上,64位的Oracle不可以裝到32位的操作系統上。
2) 查看Oracle版本:
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 – Production
3) 各組件設置:
JAVA_POOL_SIZE:如果沒用到數據庫端java的系統,30MB足夠。
LOG_BUFFER:默認為MAX(512KB,128KB*CPU個數)。一般系統1MB足夠,運行大型事務的系統可以設為2MB,讓1/3滿寫入日志文件時可以繼續寫入緩沖,再大也沒有意義。
SHARED_POOL_SIZE:過大過小都會嚴重影響系統性能,1GB內存可以設為100MB,2GB內存可設為150MB,4GB內存可設為300MB。共享池命中過低首先要調整的是應用程序而不是擴大共享池。使用綁定變量可以減少共享池需求、提高命中率,減少共享池管理負擔和LATCH競爭。
LARGE_POOL_SIZE:使用專用服務模式可設為30MB,除非必要,不然不建議使用共享服務器模式。
DB_CACHE_SIZE:除去上述內存外其他可用內存都分配給該區域。
總結
32位Oracle:
1G內存:SHARED_POOL_SIZE=100MB,DB_CACHE_SIZE=0.5GB;
2G內存:SHARED_POOL_SIZE=150MB,DB_CACHE_SIZE=1.25GB;
64位Oracle
4G內存:SHARED_POOL_SIZE=200MB,DB_CACHE_SIZE=2.5GB;
8G內存:SHARED_POOL_SIZE=400MB,DB_CACHE_SIZE=5GB;
12G內存:SHARED_POOL_SIZE=500MB,DB_CACHE_SIZE=8GB
再次強調,以上只是避免因SGA設置不當引起問題的大體設置,需要根據具體的系統負載和業務邏輯結合Stackpack等工具細調。