溫馨提示×
點擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》
這期內(nèi)容當(dāng)中小編將會給大家?guī)碛嘘P(guān)怎么解析SGA和PGA內(nèi)存管理，文章內(nèi)容豐富且以專業(yè)的角度為大家分析和敘述，閱讀完這篇文章希望大家可以有所收獲。
Oracle實例中的內(nèi)存使用分為兩類：程序全局區(qū)（program global area, PGA）和系統(tǒng)全局區(qū)（system global area, SGA）。前者專門供每個會話使用，后者由所有Oracle進(jìn)程共享。Oracle使用的所有內(nèi)存都是虛擬內(nèi)存。Oracle進(jìn)程無法確定所連接的內(nèi)存是在RAM中，還是已經(jīng)交換到磁盤。交換會削弱性能，應(yīng)予避免。
一、PGA內(nèi)存管理
PGA內(nèi)存區(qū)域包括專用的SQL區(qū)域（堆棧區(qū)）、指定的游標(biāo)（游標(biāo)區(qū)）、排序操作的工作區(qū)域（排序區(qū)）和特定于會話的內(nèi)存變量（會話區(qū)）。從9i版本開始，PGA的管理實現(xiàn)了自動化。
關(guān)于PGA的兩個初始化參數(shù)：
workarea_size_policy：默認(rèn)auto，表示Oracle可以根據(jù)需要，將PGA分配給會話，同時力求將PGA分配總量保持在pga_aggregate_target范圍內(nèi)。
pga_aggregate_target：11g默認(rèn)為0，而10g不是，另外在9i中只對專用服務(wù)器連接模式生效，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內(nèi)存的狀態(tài)和統(tǒng)計信息
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
其中幾個重要指標(biāo)
aggregate PGA target parameter：PGA內(nèi)存總和。
aggregate PGA auto target：PGA排序區(qū)分配的內(nèi)存大小。
global memory bound：限制單個進(jìn)程使用的PGA內(nèi)存上限。
cache hit percentage：排序在PGA排序區(qū)完成的比例。
如果cache hit percentage比例小于100%，則可以考慮增加PGA總量以增加排序區(qū)大小。
二、SGA內(nèi)存管理
SGA包含以下幾大塊：
固定區(qū)域（Fixed Size）：存儲SGA中各個組件的信息，大小不能修改。
可變區(qū)域（Variable Size）：包括共享池、大池、流池、JAVA池。
數(shù)據(jù)庫高速緩沖區(qū)緩存（Database buffer cache）：大小由參數(shù)db_cache_size指定（10g后參數(shù)db_cache_size默認(rèn)為0）。
重做日志緩沖區(qū)緩存（Redo log buffer cache）：大小通常大于參數(shù)log_buffer的設(shè)置，因為在內(nèi)存中還要設(shè)置保護(hù)頁對log buffer進(jìn)行保護(hù)。
以下命令可以看到SGA的內(nèi)存分配概覽
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的管理實現(xiàn)了自動化（自動共享內(nèi)存管理 ASMM）。自動共享內(nèi)存管理需要statistics_level參數(shù)設(shè)置為typical或all。自動共享內(nèi)存管理引入了一個新的后臺進(jìn)程MMAN（Memory Manager），該進(jìn)程用以動態(tài)調(diào)整內(nèi)存組件，動態(tài)調(diào)整的依據(jù)來自系統(tǒng)不間斷收集的內(nèi)存建議。
關(guān)于SGA的幾個初始化參數(shù)：
shared_pool_size：共享池大小。
db_cache_size：數(shù)據(jù)庫高速緩沖區(qū)緩存大小，也就是緩沖池中默認(rèn)池的大小。
large_pool_size：大池大小。
streams_pool_size：流池大小。
java_pool_size：java池大小。
以上幾個參數(shù)從10g開始在自動共享內(nèi)存管理下默認(rèn)為0。
log_buffer：日志緩沖區(qū)大小，靜態(tài)參數(shù)，是SGA中唯一不能動態(tài)調(diào)整的SGA結(jié)構(gòu)，在實例啟動時固定下來，無法自動管理。默認(rèn)值可能是正確的，可以將其調(diào)的比默認(rèn)值大，但這往往會導(dǎo)致性能下降。如果調(diào)的低于默認(rèn)值，則將忽略該設(shè)置。
sga_target：11g默認(rèn)為0，10g默認(rèn)同sga_max_size，等于0表示禁用自動共享內(nèi)存管理（ASMM）。
sga_max_size：sga_target的上限值，靜態(tài)參數(shù)。
查看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
真正決定各組件當(dāng)前大小的，是由一組帶雙下劃線的隱藏參數(shù)決定的
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文件，也可以看到其內(nèi)容
create pfile from spfile;
如下是一個來自于Oracle 10g的典型的pfile內(nèi)容：
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中各動態(tài)組件的調(diào)整信息
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 hh34: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
查看當(dāng)前分配給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中閃回緩沖區(qū)大小
select * from v$sgastat where name = 'flashback generation buff';
POOL NAME BYTES
------------ -------------------------- ----------
shared pool flashback generation buff 3981120
查看SGA中空閑內(nèi)存
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相關(guān)的其它幾個參數(shù)
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：靜態(tài)參數(shù)，該參數(shù)的作用是將SGA鎖定在物理內(nèi)存，這樣就不會發(fā)生SGA使用虛擬內(nèi)存的情況，提高數(shù)據(jù)的讀取速度。
alter system set lock_sga = true scope = spfile;
但要注意該參數(shù)不能與memory_target/memory_max_target一起設(shè)置，否則實例無法啟動，并報如下錯誤：
ORA-00847: MEMORY_TARGET/MEMORY_MAX_TARGET and LOCK_SGA cannot be set together
pre_page_sga：靜態(tài)參數(shù)，該參數(shù)的作用是啟動數(shù)據(jù)庫實例時，將整個SGA讀入物理內(nèi)存，提高系統(tǒng)運行效率。
alter system set pre_page_sga = true scope = spfile;
如果要取消10g的SGA自動共享內(nèi)存管理，則將參數(shù)sga_target設(shè)為0即可，更改是立即生效的。通過前面描述的查詢可以看到各個SGA組件的內(nèi)存分配情況，幾個內(nèi)存參數(shù)shared_pool_size、db_cache_size、large_pool_size、streams_pool_size、java_pool_size都不再是0，而是按照之前自動共享內(nèi)存管理時實際的分配值鎖定了各個內(nèi)存分配，并將這些參數(shù)寫入了spfile文件，在下次重啟實例時能夠持久化。
如果需要重新恢復(fù)10g的SGA自動共享內(nèi)存管理，則可以先將sga_target參數(shù)恢復(fù)為與sga_max_size相同，此時雖然已動態(tài)修改了該參數(shù)，但并沒有改回自動共享內(nèi)存管理，通過查詢各個SGA組件值可以看到他們并沒有自動恢復(fù)為0，因為他們已經(jīng)被寫進(jìn)了spfile，即便重啟實例也是不會自動改回的。此時可由當(dāng)前的spfile文件生成pfile，然后修改pfile文件，取消shared_pool_size、db_cache_size、large_pool_size、streams_pool_size、java_pool_size幾個參數(shù)的設(shè)置，然后用修改后的pfile文件重啟實例，重啟后可以看到各個SGA組件已經(jīng)更新為默認(rèn)值0了，從而恢復(fù)了自動共享內(nèi)存管理。不要忘記，因為此時是從pfile啟動的，因此應(yīng)立即再由pfile生成spfile，保證所做的更改寫入spfile文件，之后可再次重啟實例，重啟后再次查詢各個SGA組件值，確認(rèn)更改成功。
三、11g自動內(nèi)存管理
11g對內(nèi)存管理的自動化更進(jìn)一步，引入了兩個新的初始化參數(shù)：
memory_target：動態(tài)參數(shù)，可在不重啟實例的情況下進(jìn)行調(diào)整，但其值不能超過另一個實例參數(shù)memory_max_target的限制。該參數(shù)可以讓Oracle實例從總體上管理服務(wù)器內(nèi)存的使用，實現(xiàn)自動內(nèi)存管理（AMM）（log_buffer參數(shù)例外），這允許Oracle根據(jù)需要在PGA和SGA之間轉(zhuǎn)換內(nèi)存。等于0則表示禁用自動內(nèi)存管理（AMM）。
memory_max_target：靜態(tài)參數(shù)，對它的調(diào)整需要重啟實例。
如果在啟用自動內(nèi)存管理AMM的情況下設(shè)置了參數(shù)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可以看到內(nèi)存中各動態(tài)組件的調(diào)整信息
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 hh34: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值。
四、內(nèi)存顧問
1、PGA內(nèi)存顧問
只有將statistics_level參數(shù)設(shè)置為typical或all，才能啟用該顧問。
查詢PGA內(nèi)存大小的建議
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目標(biāo)設(shè)置為第一列中的估計值時所評估的磁盤I/O量。
estd_pga_cache_hit_percentage：表示估計的排序在PGA中完成的比例。
estd_overalloc_count：PGA過載分配量。
pga_target_factor：PGA目標(biāo)因子，等于1的行是當(dāng)前設(shè)置。
2、buffer_cache高速緩沖區(qū)緩存顧問
該建議受初始化參數(shù)db_cache_advice控制，為動態(tài)參數(shù)，可用值有3個，OFF、ON、READY，默認(rèn)為ON，含義如下：
OFF：關(guān)閉建議并且不為建議分配內(nèi)存。
ON：開啟建議并且CPU和內(nèi)存開銷都會發(fā)生。
READY：關(guān)閉建議但是仍保留為建議分配的內(nèi)存。
查看SGA高速緩沖區(qū)緩存大小的建議
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內(nèi)存顧問
只有將statistics_level參數(shù)設(shè)置為typical或all，才能啟用該顧問。
查詢SGA內(nèi)存顧問，第三列表示在將SGA目標(biāo)設(shè)置為第一列中的值時預(yù)計的數(shù)據(jù)庫中執(zhí)行SQL語句使用的總時間，SGA_TARGET_FACTOR=1的行是當(dāng)前設(shè)置。
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
通過上述數(shù)據(jù)可以看到，當(dāng)共享池為168M時即可達(dá)到和現(xiàn)在相同的效果，目前設(shè)置是280M，浪費了部分內(nèi)存，可以動態(tài)調(diào)整共享池參數(shù)，釋放內(nèi)存：
alter system set shared_pool_size = 168m;
5、內(nèi)存目標(biāo)顧問
10g中沒有該視圖。只有將statistics_level參數(shù)設(shè)置為typical或all，才能啟用該顧問。
查詢內(nèi)存目標(biāo)顧問，第三列表示在將內(nèi)存分配總量（SGA加PGA）設(shè)置為第一列中的值時預(yù)計的數(shù)據(jù)庫中執(zhí)行SQL語句使用的總時間，MEMORY_SIZE_FACTOR=1的行是當(dāng)前設(shè)置。
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
五、將程序常駐內(nèi)存
對于頻繁調(diào)用的數(shù)據(jù)庫對象，可以將其常駐內(nèi)存以減少磁盤I/O從而減少用戶的響應(yīng)時間。
通過視圖v$db_object_cache可以查看數(shù)據(jù)庫對象在共享池庫緩存中的信息，以下查看共享池庫緩存中執(zhí)行次數(shù)最多的對象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
將數(shù)據(jù)庫對象常駐內(nèi)存需要使用軟件包dbms_shared_pool，10g中該軟件包默認(rèn)未安裝，需要執(zhí)行一個腳本完成安裝：
@?\rdbms\admin\dbmspool.sql
將存儲過程常駐共享池：
exec dbms_shared_pool.keep('CMES.FS_CHK_CONDITION');
常駐內(nèi)存的對象，其kept標(biāo)志為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
如果需要將過程清除出內(nèi)存：
exec dbms_shared_pool.unkeep('CMES.FS_CHK_CONDITION');
程序包dbms_shared_pool中關(guān)于keep和unkeep過程的聲明如下：
procedure keep(name varchar2, flag char DEFAULT 'P');
procedure unkeep(name varchar2, flag char DEFAULT 'P');
其中第二個參數(shù)flag默認(rèn)為'P'，表示是包、存儲過程或函數(shù)，如果是其它類別的對象，則需要說明，具體含義如下：
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
六、將數(shù)據(jù)常駐內(nèi)存
很多批處理的操作（如全表掃描）可能會導(dǎo)致Buffer Cache的刷新，將經(jīng)常使用的數(shù)據(jù)“擠出”Buffer Cache。為此Oracle不斷改進(jìn)LRU算法，并提供了Buffer Cache的多緩沖池技術(shù)。用戶可以把SGA中段的已緩存塊放在三個緩沖池中：
默認(rèn)池：如果沒有指定數(shù)據(jù)的緩存位置，默認(rèn)將數(shù)據(jù)緩存在這個池中。
保持池：對于用戶頻繁訪問的對象如表或索引的數(shù)據(jù)塊可以放在這個緩沖池中。
回收池：對于隨機(jī)訪問的大段可以放在這個緩沖池中。
默認(rèn)情況下，所有表都使用默認(rèn)池，它的大小就是數(shù)據(jù)緩沖區(qū)Buffer Cache的大小，由初始化參數(shù)db_cache_size決定，默認(rèn)池是自動管理的，保持池和回收池的大小需要手工配置，分別由初始化參數(shù)db_keep_cache_size和db_recycle_cache_size決定，Oracle 9i開始，這兩個參數(shù)可以動態(tài)修改。
查看保持池的大小
show parameter db_keep_cache_size;
NAME TYPE VALUE
------------------------------------ -------------------- ------------------------------
db_keep_cache_size big integer 0
查看當(dāng)前實例緩沖池的分配信息，目前只有一個默認(rèn)的數(shù)據(jù)塊緩沖池
select id, name, block_size, buffers from v$buffer_pool;
ID NAME BLOCK_SIZE BUFFERS
---------- ------------------------------ ---------- ----------
3 DEFAULT 8192 31520
以下例子將cmes用戶的表c_material_t及其索引常駐內(nèi)存：
1、確認(rèn)常駐對象的大小
查看索引
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、設(shè)置保持池大小
alter system set db_keep_cache_size = 10m;
查看當(dāng)前實例緩沖池的分配信息，已經(jīng)多出了一個保持池
select id, name, block_size, buffers from v$buffer_pool;
ID NAME BLOCK_SIZE BUFFERS
---------- -------------------- ---------- ----------
1 KEEP 8192 1970
3 DEFAULT 8192 29550
3、將對象常駐內(nèi)存
在設(shè)置之前，表和索引是在默認(rèn)池中緩存的
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
設(shè)置表的緩沖池為保持池
alter table cmes.c_material_t storage(buffer_pool keep);
設(shè)置索引到保持池
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);
查表占用的緩沖池已是保持池，但還沒有調(diào)入
select table_name, cache, buffer_pool from user_tables where table_name = 'C_MATERIAL_T';
TABLE_NAME CACHE BUFFER_
------------------------------ ---------- -------
C_MATERIAL_T N KEEP
將表調(diào)入緩沖池
alter table cmes.c_material_t cache;
再查表已進(jìn)入保持池
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);
再看對象已恢復(fù)到默認(rèn)池
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、回收保持池中的內(nèi)存
alter system set db_keep_cache_size = 0;
查看當(dāng)前實例緩沖池的分配信息，保持池已不存在
select id, name, block_size, buffers from v$buffer_pool;
ID NAME BLOCK_SIZE BUFFERS
---------- -------------------- ---------- ----------
3 DEFAULT 8192 31520
上述就是小編為大家分享的怎么解析SGA和PGA內(nèi)存管理了，如果剛好有類似的疑惑，不妨參照上述分析進(jìn)行理解。如果想知道更多相關(guān)知識，歡迎關(guān)注億速云行業(yè)資訊頻道。
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進(jìn)行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號
手機(jī)網(wǎng)站二維碼
