Oracle例項中的記憶體使用分為兩類：程式全域性區（program global area, PGA）和系統全域性區（system global area, SGA）。前者專門供每個會話使用，後者由所有Oracle程式共享。Oracle使用的所有記憶體都是虛擬記憶體。Oracle程式無法確定所連線的記憶體是在RAM中，還是已經交換到磁碟。交換會削弱效能，應予避免。

一、 PGA 記憶體管理

PGA記憶體區域包括專用的SQL區域（堆疊區）、指定的遊標（遊標區）、排序操作的工作區域（排序區）和特定於會話的記憶體變數（會話區）。從9i版本開始，PGA的管理實現了自動化。

關於PGA的兩個初始化引數：

workarea_size_policy：預設auto，表示Oracle可以根據需要，將PGA分配給會話，同時力求將PGA分配總量保持在pga_aggregate_target範圍內。

pga_aggregate_target：11g預設為0，而10g不是，另外在9i中只對專用伺服器連線模式生效，10g開始則無論對專用連線還是共享連線都有效。

col workarea_size_policy for a30;

select p1.value WORKAREA_SIZE_POLICY,

to_char(p2.value / 1024 / 1024) || 'M' PGA_AGGREGATE_TARGET

from v$parameter p1, v$parameter p2

where p1.name = 'workarea_size_policy' and p2.name = 'pga_aggregate_target';

WORKAREA_SIZE_POLICY PGA_AGGREGATE_TARGET

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

AUTO 0M

透過檢視v$sesstat可以檢視為所有會話分配的PGA大小

select to_char(round(sum(value) / 1024 / 1024, 2)) || 'M' session_pga_memory from v$sesstat natural join v$statname where name = 'session pga memory';

SESSION_PGA_MEMORY

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

117.59M

透過檢視v$pgastat可以檢視PGA記憶體的狀態和統計資訊

col name for a50

col value for a20

select name, to_char(round(value / 1024 / 1024)) || 'M' value from v$pgastat;

NAME VALUE

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

aggregate PGA target parameter 1831M

aggregate PGA auto target 1632M

global memory bound 183M

total PGA inuse 18M

total PGA allocated 55M

maximum PGA allocated 415M

total freeable PGA memory 26M

process count 0M

max processes count 0M

PGA memory freed back to OS 139987M

total PGA used for auto workareas 0M

maximum PGA used for auto workareas 174M

total PGA used for manual workareas 0M

maximum PGA used for manual workareas 1M

over allocation count 0M

bytes processed 539718M

extra bytes read/written 292953M

cache hit percentage 0M

recompute count (total) 3M

其中幾個重要指標

aggregate PGA target parameter：PGA記憶體總和。

aggregate PGA auto target：PGA排序區分配的記憶體大小。

global memory bound：限制單個程式使用的PGA記憶體上限。

cache hit percentage：排序在PGA排序區完成的比例。

如果cache hit percentage比例小於100%，則可以考慮增加PGA總量以增加排序區大小。

二、 SGA 記憶體管理

SGA包含以下幾大塊：

固定區域（Fixed Size）：儲存SGA中各個元件的資訊，大小不能修改。

可變區域（Variable Size）：包括共享池、大池、流池、JAVA池。

資料庫高速緩衝區快取（Database buffer cache）：大小由引數db_cache_size指定（10g後引數db_cache_size預設為0）。

重做日誌緩衝區快取（Redo log buffer cache）：大小通常大於引數log_buffer的設定，因為在記憶體中還要設定保護頁對log buffer進行保護。

以下命令可以看到SGA的記憶體分配概覽

show sga

Total System Global Area 4960579584 bytes

Fixed Size 2184232 bytes

Variable Size 2902461400 bytes

Database Buffers 2046820352 bytes

Redo Buffers 9113600 bytes

或者

select * from v$sga;

NAME VALUE

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

Fixed Size 2184232

Variable Size 2902461400

Database Buffers 2046820352

Redo Buffers 9113600

從10g版本開始，SGA的管理實現了自動化（自動共享記憶體管理 ASMM）。自動共享記憶體管理需要statistics_level引數設定為typical或all。自動共享記憶體管理引入了一個新的後臺程式MMAN（Memory Manager），該程式用以動態調整記憶體元件，動態調整的依據來自系統不間斷收集的記憶體建議。

關於SGA的幾個初始化引數：

shared_pool_size：共享池大小。

db_cache_size：資料庫高速緩衝區快取大小，也就是緩衝池中預設池的大小。

large_pool_size：大池大小。

streams_pool_size：流池大小。

java_pool_size：java池大小。

以上幾個引數從10g開始在自動共享記憶體管理下預設為0。

log_buffer：日誌緩衝區大小，靜態引數，是SGA中唯一不能動態調整的SGA結構，在例項啟動時固定下來，無法自動管理。預設值可能是正確的，可以將其調的比預設值大，但這往往會導致效能下降。如果調的低於預設值，則將忽略該設定。

sga_target：11g預設為0，10g預設同sga_max_size，等於0表示禁用自動共享記憶體管理（ASMM）。

sga_max_size：sga_target的上限值，靜態引數。

檢視SGA中各元件的分配

col shared_pool_size for a18;

col shared_pool_size for a15;

col db_cache_size for a15;

col large_pool_size for a15;

col streams_pool_size for a18;

col java_pool_size for a15;

col log_buffer for a15;

col sga_target for a15;

col sga_max_size for a15;

select to_char(p1.value / 1024 / 1024) || 'M' shared_pool_size,

to_char(p2.value / 1024 / 1024) || 'M' db_cache_size,

to_char(p3.value / 1024 / 1024) || 'M' large_pool_size,

to_char(p4.value / 1024 / 1024) || 'M' streams_pool_size,

to_char(p5.value / 1024 / 1024) || 'M' java_pool_size,

to_char(p6.value / 1024 / 1024) || 'M' log_buffer,

to_char(p7.value / 1024 / 1024) || 'M' sga_target,

to_char(p8.value / 1024 / 1024) || 'M' sga_max_size

from v$parameter p1,

v$parameter p2,

v$parameter p3,

v$parameter p4,

v$parameter p5,

v$parameter p6,

v$parameter p7,

v$parameter p8

where p1.name = 'shared_pool_size'

and p2.name = 'db_cache_size'

and p3.name = 'large_pool_size'

and p4.name = 'streams_pool_size'

and p5.name = 'java_pool_size'

and p6.name = 'log_buffer'

and p7.name = 'sga_target'

and p8.name = 'sga_max_size';

SHARED_POOL_SIZ DB_CACHE_SIZE LARGE_POOL_SIZE STREAMS_POOL_SIZE JAVA_POOL_SIZE LOG_BUFFER SGA_TARGET SGA_MAX_SIZE

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

0M 0M 0M 0M 0M 7.328125M 0M 1232M

真正決定各元件當前大小的，是由一組帶雙下劃線的隱藏引數決定的

col name for a40

col value for a20

col pdesc for a70

select x.ksppinm name, y.ksppstvl / 1024 / 1024 || 'M' value, x.ksppdesc pdesc

from sys.x$ksppi x, sys.x$ksppcv y

where x.indx = y.indx

and x.ksppinm in ('__shared_pool_size', '__db_cache_size', '__large_pool_size', '__streams_pool_size', '__java_pool_size', '__sga_target', '__pga_aggregate_target');

NAME VALUE PDESC

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

__shared_pool_size 208M Actual size in bytes of shared pool

__large_pool_size 32M Actual size in bytes of large pool

__java_pool_size 16M Actual size in bytes of java pool

__streams_pool_size 0M Actual size in bytes of streams pool

__sga_target 736M Actual size of SGA

__db_cache_size 432M Actual size of DEFAULT buffer pool for standard block size buffers

__pga_aggregate_target 496M Current target size for the aggregate PGA memory consumed

透過生成pfile檔案，也可以看到其內容

create pfile from spfile;

如下是一個來自於Oracle 10g的典型的pfile內容：

mes.__db_cache_size=482344960

mes.__java_pool_size=8388608

mes.__large_pool_size=4194304

mes.__shared_pool_size=104857600

mes.__streams_pool_size=4194304

*.audit_file_dest='D:\oracle\product\10.2.0/admin/mes/adump'

*.background_dump_dest='D:\oracle\product\10.2.0/admin/mes/bdump'

*.compatible='10.2.0.1.0'

*.control_files='D:\oracle\product\10.2.0\oradata\mes\control01.ctl','D:\oracle\product\10.2.0\oradata\mes\control02.ctl','D:\oracle\product\10.2.0\oradata\mes\control03.ctl'

*.core_dump_dest='D:\oracle\product\10.2.0/admin/mes/cdump'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_name='mes'

*.db_recovery_file_dest='D:\oracle\product\10.2.0/flash_recovery_area'

*.db_recovery_file_dest_size=2147483648

*.dispatchers='(PROTOCOL=TCP) (SERVICE=mesXDB)'

*.job_queue_processes=10

*.open_cursors=300

*.optimizer_index_caching=90

*.optimizer_index_cost_adj=20

*.pga_aggregate_target=203423744

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=612368384

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='D:\oracle\product\10.2.0/admin/mes/udump'

透過檢視v$sga_dynamic_components可以看到SGA中各動態元件的調整資訊

col component for a30

select component,

current_size,

user_specified_size,

min_size,

max_size,

granule_size,

last_oper_type,

last_oper_mode,

to_char(last_oper_time, 'yyyy-mm-dd hh24:mi:ss') last_oper_time

from v$sga_dynamic_components;

COMPONENT CURRENT_SIZE USER_SPECIFIED_SIZE MIN_SIZE MAX_SIZE GRANULE_SIZE LAST_OPER_TYP LAST_OPER LAST_OPER_TIME

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

shared pool 369098752 0 335544320 369098752 16777216 GROW DEFERRED 2017-10-05 14:31:17

large pool 16777216 0 16777216 16777216 16777216 STATIC

java pool 16777216 0 16777216 16777216 16777216 STATIC

streams pool 0 0 0 0 16777216 STATIC

DEFAULT buffer cache 352321536 0 352321536 385875968 16777216 SHRINK DEFERRED 2017-10-05 14:31:17

KEEP buffer cache 0 0 0 0 16777216 STATIC

RECYCLE buffer cache 0 0 0 0 16777216 STATIC

DEFAULT 2K buffer cache 0 0 0 0 16777216 STATIC

DEFAULT 4K buffer cache 0 0 0 0 16777216 STATIC

DEFAULT 8K buffer cache 0 0 0 0 16777216 STATIC

DEFAULT 16K buffer cache 0 0 0 0 16777216 STATIC

DEFAULT 32K buffer cache 0 0 0 0 16777216 STATIC

Shared IO Pool 0 0 0 0 16777216 STATIC

ASM Buffer Cache 0 0 0 0 16777216 STATIC

檢視當前分配給SGA的實際大小

select to_char(round(sum(bytes) / 1024 / 1024, 2)) || 'M' sga_memory from v$sgastat;

SGA_MEMORY

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

810.49M

分類檢視SGA中各元件的分配資訊

select nvl2(pool, pool, name) name, to_char(round(sum(bytes) / 1024 / 1024, 2)) || 'M' memory from v$sgastat group by nvl2(pool, pool, name) order by 1;

NAME MEMORY

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

buffer_cache 272M

fixed_sga 2.07M

java pool 16M

large pool 16M

log_buffer 8.41M

shared pool 464M

streams pool 32M

查詢SGA中閃回緩衝區大小

select * from v$sgastat where name = 'flashback generation buff';

POOL NAME BYTES

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

shared pool flashback generation buff 3981120

檢視SGA中空閒記憶體

select pool, name, to_char(round(bytes / 1024 /1024)) || 'M' free_size from v$sgastat t where t.name like 'free%';

POOL NAME FREE_SIZE

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

shared pool free memory 76M

large pool free memory 15M

java pool free memory 16M

streams pool free memory 16M

和SGA相關的其它幾個引數

show parameter sga;

NAME TYPE VALUE

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

lock_sga boolean FALSE

pre_page_sga boolean FALSE

sga_max_size big integer 584M

sga_target big integer 584M

lock_sga：靜態引數，該引數的作用是將SGA鎖定在實體記憶體，這樣就不會發生SGA使用虛擬記憶體的情況，提高資料的讀取速度。

alter system set lock_sga = true scope = spfile;

但要注意該引數不能與memory_target/memory_max_target一起設定，否則例項無法啟動，並報如下錯誤：

ORA-00847: MEMORY_TARGET/MEMORY_MAX_TARGET and LOCK_SGA cannot be set together

pre_page_sga：靜態引數，該引數的作用是啟動資料庫例項時，將整個SGA讀入實體記憶體，提高系統執行效率。

alter system set pre_page_sga = true scope = spfile;

如果要取消10g的SGA自動共享記憶體管理，則將引數sga_target設為0即可，更改是立即生效的。透過前面描述的查詢可以看到各個SGA元件的記憶體分配情況，幾個記憶體引數shared_pool_size、db_cache_size、large_pool_size、streams_pool_size、java_pool_size都不再是0，而是按照之前自動共享記憶體管理時實際的分配值鎖定了各個記憶體分配，並將這些引數寫入了spfile檔案，在下次重啟例項時能夠持久化。

如果需要重新恢復10g的SGA自動共享記憶體管理，則可以先將sga_target引數恢復為與sga_max_size相同，此時雖然已動態修改了該引數，但並沒有改回自動共享記憶體管理，透過查詢各個SGA元件值可以看到他們並沒有自動恢復為0，因為他們已經被寫進了spfile，即便重啟例項也是不會自動改回的。此時可由當前的spfile檔案生成pfile，然後修改pfile檔案，取消shared_pool_size、db_cache_size、large_pool_size、streams_pool_size、java_pool_size幾個引數的設定，然後用修改後的pfile檔案重啟例項，重啟後可以看到各個SGA元件已經更新為預設值0了，從而恢復了自動共享記憶體管理。不要忘記，因為此時是從pfile啟動的，因此應立即再由pfile生成spfile，保證所做的更改寫入spfile檔案，之後可再次重啟例項，重啟後再次查詢各個SGA元件值，確認更改成功。

三、 11g 自動記憶體管理

11g對記憶體管理的自動化更進一步，引入了兩個新的初始化引數：

memory_target：動態引數，可在不重啟例項的情況下進行調整，但其值不能超過另一個例項引數memory_max_target的限制。該引數可以讓Oracle例項從總體上管理伺服器記憶體的使用，實現自動記憶體管理（AMM）（log_buffer引數例外），這允許Oracle根據需要在PGA和SGA之間轉換記憶體。等於0則表示禁用自動記憶體管理（AMM）。

memory_max_target：靜態引數，對它的調整需要重啟例項。

如果在啟用自動記憶體管理AMM的情況下設定了引數pga_aggregate_target或sga_target，那麼指定的值將是最小大小，AMM不會使PGA或SGA低於此值。

select to_char(p1.value / 1024 / 1024) || 'M' memory_target,

to_char(p1.value / 1024 / 1024) || 'M' memory_max_target

from v$parameter p1, v$parameter p2

where p1.name = 'memory_target'

and p2.name = 'memory_max_target';

MEMORY_TARGET MEMORY_MAX_TARGET

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

1232M 1232M

透過檢視v$memory_dynamic_components可以看到記憶體中各動態元件的調整資訊

col component for a30

select component,

current_size,

user_specified_size,

min_size,

max_size,

granule_size,

last_oper_type,

last_oper_mode,

to_char(last_oper_time, 'yyyy-mm-dd hh24:mi:ss') last_oper_time

from v$memory_dynamic_components;

COMPONENT CURRENT_SIZE USER_SPECIFIED_SIZE MIN_SIZE MAX_SIZE GRANULE_SIZE LAST_OPER_TYP LAST_OPER LAST_OPER_TIME

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

shared pool 369098752 0 335544320 369098752 16777216 GROW DEFERRED 2017-10-05 14:31:17

large pool 16777216 0 16777216 16777216 16777216 STATIC

java pool 16777216 0 16777216 16777216 16777216 STATIC

streams pool 0 0 0 0 16777216 STATIC

SGA Target 771751936 0 771751936 771751936 16777216 STATIC

DEFAULT buffer cache 352321536 0 352321536 385875968 16777216 SHRINK DEFERRED 2017-10-05 14:31:17

KEEP buffer cache 0 0 0 0 16777216 STATIC

RECYCLE buffer cache 0 0 0 0 16777216 STATIC

DEFAULT 2K buffer cache 0 0 0 0 16777216 STATIC

DEFAULT 4K buffer cache 0 0 0 0 16777216 STATIC

DEFAULT 8K buffer cache 0 0 0 0 16777216 STATIC

DEFAULT 16K buffer cache 0 0 0 0 16777216 STATIC

DEFAULT 32K buffer cache 0 0 0 0 16777216 STATIC

Shared IO Pool 0 0 0 0 16777216 STATIC

PGA Target 536870912 0 536870912 536870912 16777216 STATIC

ASM Buffer Cache 0 0 0 0 16777216 STATIC

這裡的PGA Target就是實際的pga_aggregate_target值。

四、記憶體顧問

1、PGA記憶體顧問

只有將statistics_level引數設定為typical或all，才能啟用該顧問。

查詢PGA記憶體大小的建議

col pga_target_for_estimate for a30

select to_char(pga_target_for_estimate / 1024 / 1024, '999999') || 'M' pga_target_for_estimate,

pga_target_factor,

estd_extra_bytes_rw,

estd_pga_cache_hit_percentage,

estd_overalloc_count

from v$pga_target_advice;

PGA_TARGET_FOR_ESTIMATE PGA_TARGET_FACTOR ESTD_EXTRA_BYTES_RW ESTD_PGA_CACHE_HIT_PERCENTAGE ESTD_OVERALLOC_COUNT

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

152M .125 2.3527E+10 99 7

304M .25 1.3997E+10 99 0

609M .5 1.2325E+10 100 0

913M .75 1.2325E+10 100 0

1217M 1 1.2325E+10 100 0

1460M 1.2 9412871168 100 0

1704M 1.4 9412871168 100 0

1947M 1.6 9412871168 100 0

2191M 1.8 9412871168 100 0

2434M 2 9412871168 100 0

3651M 3 9412871168 100 0

4868M 4 9412871168 100 0

7302M 6 9412871168 100 0

9736M 8 9412871168 100 0

estd_extra_bytes_rw：表示在將PGA目標設定為第一列中的估計值時所評估的磁碟I/O量。

estd_pga_cache_hit_percentage：表示估計的排序在PGA中完成的比例。

estd_overalloc_count：PGA過載分配量。

pga_target_factor：PGA目標因子，等於1的行是當前設定。

2、buffer_cache高速緩衝區快取顧問

該建議受初始化引數db_cache_advice控制，為動態引數，可用值有3個，OFF、ON、READY，預設為ON，含義如下：

OFF：關閉建議並且不為建議分配記憶體。

ON：開啟建議並且CPU和記憶體開銷都會發生。

READY：關閉建議但是仍保留為建議分配的記憶體。

檢視SGA高速緩衝區快取大小的建議

select id, name, block_size, size_for_estimate, size_factor, estd_physical_read_factor, estd_physical_reads from v$db_cache_advice;

ID NAME BLOCK_SIZE SIZE_FOR_ESTIMATE SIZE_FACTOR ESTD_PHYSICAL_READ_FACTOR ESTD_PHYSICAL_READS

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

3 DEFAULT 8192 112 .0897 1.3858 1.2789E+10

3 DEFAULT 8192 224 .1795 1.0969 1.0123E+10

3 DEFAULT 8192 336 .2692 1.0412 9608886688

3 DEFAULT 8192 448 .359 1.0126 9344961971

3 DEFAULT 8192 560 .4487 1.003 9255747045

3 DEFAULT 8192 672 .5385 1.0008 9236158453

3 DEFAULT 8192 784 .6282 1.0005 9232636063

3 DEFAULT 8192 896 .7179 1.0003 9230983775

3 DEFAULT 8192 1008 .8077 1.0002 9229925754

3 DEFAULT 8192 1120 .8974 1.0001 9229177367

3 DEFAULT 8192 1232 .9872 1 9228548559

3 DEFAULT 8192 1248 1 1 9228424715

3 DEFAULT 8192 1344 1.0769 .9999 9227693021

3 DEFAULT 8192 1456 1.1667 .9998 9226879481

3 DEFAULT 8192 1568 1.2564 .9994 9222904948

3 DEFAULT 8192 1680 1.3462 .996 9191297489

3 DEFAULT 8192 1792 1.4359 .9926 9159918796

3 DEFAULT 8192 1904 1.5256 .9886 9123574082

3 DEFAULT 8192 2016 1.6154 .9868 9106458871

3 DEFAULT 8192 2128 1.7051 .9862 9100917681

3 DEFAULT 8192 2240 1.7949 .9848 9088286201

3、SGA記憶體顧問

只有將statistics_level引數設定為typical或all，才能啟用該顧問。

查詢SGA記憶體顧問，第三列表示在將SGA目標設定為第一列中的值時預計的資料庫中執行SQL語句使用的總時間，SGA_TARGET_FACTOR=1的行是當前設定。

select sga_size, sga_size_factor, estd_db_time from v$sga_target_advice order by 2;

SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME

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

768 .5 3499668

1152 .75 1249677

1536 1 752455

1920 1.25 696773

2304 1.5 696548

2688 1.75 696548

3072 2 696548

4、shared_pool共享池顧問

檢視共享池大小的建議

select shared_pool_size_for_estimate,

shared_pool_size_factor,

estd_lc_size,

estd_lc_memory_objects,

estd_lc_time_saved,

estd_lc_time_saved_factor,

estd_lc_memory_object_hits

from v$shared_pool_advice;

SHARED_POOL_SIZE_FOR_ESTIMATE SHARED_POOL_SIZE_FACTOR ESTD_LC_SIZE ESTD_LC_MEMORY_OBJECTS ESTD_LC_TIME_SAVED ESTD_LC_TIME_SAVED_FACTOR ESTD_LC_MEMORY_OBJECT_HITS

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

112 .4 12 768 232 .9831 16977

140 .5 40 2280 234 .9915 37015

168 .6 59 3284 236 1 37458

196 .7 64 3611 236 1 37463

224 .8 64 3611 236 1 37463

252 .9 64 3611 236 1 37463

280 1 64 3611 236 1 37463

308 1.1 64 3611 236 1 37463

336 1.2 64 3611 236 1 37463

364 1.3 64 3611 236 1 37463

392 1.4 64 3611 236 1 37463

420 1.5 64 3611 236 1 37463

448 1.6 64 3611 236 1 37463

476 1.7 64 3611 236 1 37463

504 1.8 64 3611 236 1 37463

532 1.9 64 3611 236 1 37463

560 2 64 3611 236 1 37463

透過上述資料可以看到，當共享池為168M時即可達到和現在相同的效果，目前設定是280M，浪費了部分記憶體，可以動態調整共享池引數，釋放記憶體：

alter system set shared_pool_size = 168m;

5、記憶體目標顧問

10g中沒有該檢視。只有將statistics_level引數設定為typical或all，才能啟用該顧問。

查詢記憶體目標顧問，第三列表示在將記憶體分配總量（SGA加PGA）設定為第一列中的值時預計的資料庫中執行SQL語句使用的總時間，MEMORY_SIZE_FACTOR=1的行是當前設定。

select memory_size, memory_size_factor, estd_db_time from v$memory_target_advice;

MEMORY_SIZE MEMORY_SIZE_FACTOR ESTD_DB_TIME

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

496 .5 22

744 .75 22

992 1 22

1240 1.25 22

1488 1.5 22

1736 1.75 22

1984 2 22

五、將程式常駐記憶體

對於頻繁呼叫的資料庫物件，可以將其常駐記憶體以減少磁碟I/O從而減少使用者的響應時間。

透過檢視v$db_object_cache可以檢視資料庫物件在共享池庫快取中的資訊，以下檢視共享池庫快取中執行次數最多的物件TOP10：

col owner for a10

col name for a30

col type for a20

select *

from (select owner, name, type, sharable_mem, loads, executions, kept

from v$db_object_cache

where owner = 'CMES'

order by executions desc)

where rownum <= 10;

OWNER NAME TYPE SHARABLE_MEM LOADS EXECUTIONS KEP

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

CMES FS_CHK_CONDITION PROCEDURE 6893 269 17266360 NO

CMES M085I_GET_TEAMSHIFT PROCEDURE 6896 264 7915669 NO

CMES M085I_GET_BOM_MATERIAL PROCEDURE 10995 276 3777920 NO

CMES M090I_NS_CHK_SN_ONLINE PROCEDURE 6899 282 3048307 NO

CMES FS_GET_CHANNEL PROCEDURE 16195 266 3032415 NO

CMES M085I_SET_SCAN_STATISTICS PROCEDURE 8014 262 2907832 NO

CMES M085I_CHK_COM_FULL PROCEDURE 6895 266 2885160 NO

CMES M090_NS_CHK_KP PROCEDURE 12099 447 2296638 NO

CMES M085I_GET_ACCURATE_MATERIAL2 PROCEDURE 12113 291 2296633 NO

CMES M085I_SET_KEYPART PROCEDURE 46406 278 1823303 NO

將資料庫物件常駐記憶體需要使用軟體包dbms_shared_pool，10g中該軟體包預設未安裝，需要執行一個指令碼完成安裝：

@?\rdbms\admin\dbmspool.sql

將儲存過程常駐共享池：

exec dbms_shared_pool.keep('CMES.FS_CHK_CONDITION');

常駐記憶體的物件，其kept標誌為YES：

col owner for a10

col name for a30

col type for a20

select owner, name, type, sharable_mem, loads, executions, kept from v$db_object_cache where name = 'FS_CHK_CONDITION';

OWNER NAME TYPE SHARABLE_MEM LOADS EXECUTIONS KEP

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

CMES FS_CHK_CONDITION PROCEDURE 25192 1 0 YES

如果需要將過程清除出記憶體：

exec dbms_shared_pool.unkeep('CMES.FS_CHK_CONDITION');

程式包dbms_shared_pool中關於keep和unkeep過程的宣告如下：

procedure keep(name varchar2, flag char DEFAULT 'P');

procedure unkeep(name varchar2, flag char DEFAULT 'P');

其中第二個引數flag預設為'P'，表示是包、儲存過程或函式，如果是其它類別的物件，則需要說明，具體含義如下：

Value Kind of Object to keep

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

P package/procedure/function

Q sequence

R trigger

T type

JS java source

JC java class

JR java resource

JD java shared data

C cursor

六、將資料常駐記憶體

很多批處理的操作（如全表掃描）可能會導致Buffer Cache的重新整理，將經常使用的資料“擠出”Buffer Cache。為此Oracle不斷改進LRU演算法，並提供了Buffer Cache的多緩衝池技術。使用者可以把SGA中段的已快取塊放在三個緩衝池中：

預設池：如果沒有指定資料的快取位置，預設將資料快取在這個池中。

保持池：對於使用者頻繁訪問的物件如表或索引的資料塊可以放在這個緩衝池中。

回收池：對於隨機訪問的大段可以放在這個緩衝池中。

預設情況下，所有表都使用預設池，它的大小就是資料緩衝區Buffer Cache的大小，由初始化引數db_cache_size決定，預設池是自動管理的，保持池和回收池的大小需要手工配置，分別由初始化引數db_keep_cache_size和db_recycle_cache_size決定，Oracle 9i開始，這兩個引數可以動態修改。

檢視保持池的大小

show parameter db_keep_cache_size;

NAME TYPE VALUE

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

db_keep_cache_size big integer 0

檢視當前例項緩衝池的分配資訊，目前只有一個預設的資料塊緩衝池

select id, name, block_size, buffers from v$buffer_pool;

ID NAME BLOCK_SIZE BUFFERS

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

3 DEFAULT 8192 31520

以下例子將cmes使用者的表c_material_t及其索引常駐記憶體：

1、確認常駐物件的大小

檢視索引

conn cmes/cmes

col tablespace_name for a20

select table_name, index_name, index_type, status, tablespace_name from user_indexes where table_name='C_MATERIAL_T';

TABLE_NAME INDEX_NAME INDEX_TYPE STATUS TABLESPACE_NAME

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

C_MATERIAL_T IDX_FK_MATERIAL_NO NORMAL VALID CMES

C_MATERIAL_T IDX_FK_PART_NO NORMAL VALID CMES

C_MATERIAL_T IDX_PK_MATERIAL_ID NORMAL VALID CMES

分析表和索引

analyze table cmes.c_material_t compute statistics;

analyze index cmes.idx_pk_material_id compute statistics;

analyze index cmes.idx_fk_part_no compute statistics;

analyze index cmes.idx_fk_material_no compute statistics;

確定物件大小

col owner for a10

col segment_name for a30

select owner,

segment_name,

segment_type,

tablespace_name,

bytes,

extents,

blocks

from dba_segments

where owner = 'CMES'

and segment_name in ('C_MATERIAL_T',

'IDX_PK_MATERIAL_ID',

'IDX_FK_PART_NO',

'IDX_FK_MATERIAL_NO');

OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BYTES EXTENTS BLOCKS

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

CMES IDX_PK_MATERIAL_ID INDEX CMES 65536 1 8

CMES IDX_FK_PART_NO INDEX CMES 65536 1 8

CMES IDX_FK_MATERIAL_NO INDEX CMES 65536 1 8

CMES C_MATERIAL_T TABLE CMES 131072 2 16

select (65536 * 3 + 131072) / 1024 KB from dual;

KB

----------

320

2、設定保持池大小

alter system set db_keep_cache_size = 10m;

檢視當前例項緩衝池的分配資訊，已經多出了一個保持池

select id, name, block_size, buffers from v$buffer_pool;

ID NAME BLOCK_SIZE BUFFERS

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

1 KEEP 8192 1970

3 DEFAULT 8192 29550

3、將物件常駐記憶體

在設定之前，表和索引是在預設池中快取的

select table_name, cache, buffer_pool from user_tables where table_name = 'C_MATERIAL_T';

TABLE_NAME CACHE BUFFER_

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

C_MATERIAL_T N DEFAULT

select index_name, table_name, buffer_pool

from user_indexes

where index_name in

('IDX_PK_MATERIAL_ID', 'IDX_FK_PART_NO', 'IDX_FK_MATERIAL_NO');

INDEX_NAME TABLE_NAME BUFFER_

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

IDX_FK_MATERIAL_NO C_MATERIAL_T DEFAULT

IDX_FK_PART_NO C_MATERIAL_T DEFAULT

IDX_PK_MATERIAL_ID C_MATERIAL_T DEFAULT

設定表的緩衝池為保持池

alter table cmes.c_material_t storage(buffer_pool keep);

設定索引到保持池

alter index cmes.idx_pk_material_id storage(buffer_pool keep);

alter index cmes.idx_fk_part_no storage(buffer_pool keep);

alter index cmes.idx_fk_material_no storage(buffer_pool keep);

如果要將表快取到回收池，則命令為

alter table cmes.c_material_t storage(buffer_pool recycle);

查表佔用的緩衝池已是保持池，但還沒有調入

select table_name, cache, buffer_pool from user_tables where table_name = 'C_MATERIAL_T';

TABLE_NAME CACHE BUFFER_

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

C_MATERIAL_T N KEEP

將表調入緩衝池

alter table cmes.c_material_t cache;

再查表已進入保持池

select table_name, cache, buffer_pool from user_tables where table_name = 'C_MATERIAL_T';

TABLE_NAME CACHE BUFFER_

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

C_MATERIAL_T Y KEEP

查索引已快取在保持池

select index_name, table_name, buffer_pool

from user_indexes

where index_name in

('IDX_PK_MATERIAL_ID', 'IDX_FK_PART_NO', 'IDX_FK_MATERIAL_NO');

INDEX_NAME TABLE_NAME BUFFER_

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

IDX_FK_MATERIAL_NO C_MATERIAL_T KEEP

IDX_FK_PART_NO C_MATERIAL_T KEEP

IDX_PK_MATERIAL_ID C_MATERIAL_T KEEP

4、從保持池中撤離物件

撤離表

alter table cmes.c_material_t storage(buffer_pool default);

alter table cmes.c_material_t nocache;

撤離索引

alter index cmes.idx_pk_material_id storage(buffer_pool default);

alter index cmes.idx_fk_part_no storage(buffer_pool default);

alter index cmes.idx_fk_material_no storage(buffer_pool default);

再看物件已恢復到預設池

select table_name, cache, buffer_pool from user_tables where table_name = 'C_MATERIAL_T';

TABLE_NAME CACHE BUFFER_

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

C_MATERIAL_T N DEFAULT

select index_name, table_name, buffer_pool

from user_indexes

where index_name in

('IDX_PK_MATERIAL_ID', 'IDX_FK_PART_NO', 'IDX_FK_MATERIAL_NO');

INDEX_NAME TABLE_NAME BUFFER_

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

IDX_FK_MATERIAL_NO C_MATERIAL_T DEFAULT

IDX_FK_PART_NO C_MATERIAL_T DEFAULT

IDX_PK_MATERIAL_ID C_MATERIAL_T DEFAULT

5、回收保持池中的記憶體

alter system set db_keep_cache_size = 0;

檢視當前例項緩衝池的分配資訊，保持池已不存在

select id, name, block_size, buffers from v$buffer_pool;

ID NAME BLOCK_SIZE BUFFERS

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

3 DEFAULT 8192 31520

SGA和PGA記憶體管理

相關文章