怎么解析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內存管理了，如果剛好有類似的疑惑，不妨參照上述分析進行理解。如果想知道更多相關知識，歡迎關注億速云行業資訊頻道。