怎么解析SGA和PGA内存管理

发布时间：2021-11-12 14:36:52 来源：亿速云阅读：226 作者：柒染栏目：关系型数据库

这期内容当中小编将会给大家带来有关怎么解析SGA和PGA内存管理，文章内容丰富且以专业的角度为大家分析和叙述，阅读完这篇文章希望大家可以有所收获。

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 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

查看当前分配给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 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值。

四、内存顾问

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;

----------

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内存管理了，如果刚好有类似的疑惑，不妨参照上述分析进行理解。如果想知道更多相关知识，欢迎关注亿速云行业资讯频道。

向AI问一下细节

怎么解析SGA和PGA内存管理

猜你喜欢

最新资讯

相关推荐

相关标签