怎么解析SGA和PGA内存管理

这期内容当中小编将会给大家带来有关怎么解析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;

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