oracle11g memory management系列(一)

1,自oracle11g始,sga和pga管理可以由oracle完全自動管理
2,11g記憶體管理有2種方法:自動管理(指定一個記憶體總大小);
                       手工管理(想直接手工控制各個記憶體元件使用)

3,記憶體結構:
           1,sga
              a,sga由後臺程式和server程式共享
              b,sga包括資料塊和控制結構
           2,pga
              a,pga排它性,僅由建立它的server process獨享
              b,給後臺程式也會分配pga,比如lgwr,可以查閱v$process
           3,flush cache
              a,11g新記憶體元件
              b,駐存在sga基礎之上
              c,Database Smart Flash Cache (the flash cache),
              d,為資料塊提供2級快取功能,尤其適用於讀密集型的oltp環境;大幅提升響度時間和整體吞吐量
              e,flush cache基於一個或多個flash disk裝置,這些固態儲存裝置使用flash memory
                (注:如何配置flush cache請參閱:)
              f,須執行linux平臺
             
4,開啟自動記憶體管理(即同時管控sga和pga)           
   alter system set memory_max_target=ngb scope=spfile;--ngb是要設定的值,此引數為靜態引數,須重啟庫
   alter system set memory_target=ngb;--動態引數,只要在memory_max_target範圍內即可
   ALTER SYSTEM SET SGA_TARGET = 0;--關閉手工管理sga和pga
   ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 0;--同上
 
   ---重啟庫,生效上述引數
   shutdown immediate
   startup
  
   --memory_max_target的值如何配置呢,oracle提供如下方法:
   ---查閱為pga分配的最大值
   SQL> select * from v$pgastat ps where ps.name='maximum PGA allocated';
 
   NAME                                                                  VALUE UNIT
   ---------------------------------------------------------------- ---------- ------------
  maximum PGA allocated                                              91763712 bytes
 
  --memory_target(或memory_max_target)的值:
  --如實體記憶體非常充足,可以考慮在其基礎上適當增加大小
  memory_target = sga_target + max(pga_aggregate_target, maximum PGA allocated)
 
----監控oracle記憶體結構

  ---動態查閱記憶體各元件的當前實時大小 
  SQL> select * from v$memory_dynamic_components;
 
COMPONENT                                                        CURRENT_SIZE   MIN_SIZE   MAX_SIZE USER_SPECIFIED_SIZE OPER_COUNT LAST_OPER_TYPE LAST_OPER_MODE LAST_OPER_TIME GRANULE_SIZE
---------------------------------------------------------------- ------------ ---------- ---------- ------------------- ---------- -------------- -------------- -------------- ------------
shared pool                                                         301989888  301989888  301989888                   0          0 STATIC                                           16777216
large pool                                                           16777216   16777216   16777216                   0          0 STATIC                                           16777216
java pool                                                            16777216   16777216   16777216                   0          0 STATIC                                           16777216
streams pool                                                                0          0          0                   0          0 STATIC                                           16777216
SGA Target                                                         2566914048 2566914048 2566914048                   0          0 STATIC                                           16777216
DEFAULT buffer cache                                               2197815296 2197815296 2197815296                   0          0 INITIALIZING                                     16777216
KEEP buffer cache                                                           0          0          0                   0          0 STATIC                                           16777216
RECYCLE buffer cache                                                        0          0          0                   0          0 STATIC                                           16777216
DEFAULT 2K buffer cache                                                     0          0          0                   0          0 STATIC                                           16777216
DEFAULT 4K buffer cache                                                     0          0          0                   0          0 STATIC                                           16777216
DEFAULT 8K buffer cache                                                     0          0          0                   0          0 STATIC                                           16777216
DEFAULT 16K buffer cache                                                    0          0          0                   0          0 STATIC                                           16777216
DEFAULT 32K buffer cache                                                    0          0          0                   0          0 STATIC                                           16777216
Shared IO Pool                                                              0          0          0                   0          0 STATIC                                           16777216
PGA Target                                                         1728053248 1728053248 1728053248                   0          0 STATIC                                           16777216
ASM Buffer Cache                                                            0          0          0                   0          0 STATIC                                           16777216
 
16 rows selected
 
SQL>
 
---調節memory target的診斷工具
---memory_target_factor為1,表示當前memory_target所屬行
--其上和其下為memory_target的記憶體大小
--estd_db_time為不同記憶體大小完成工作所需要的db time
--此例中,增大和減少記憶體大小estd_db_time皆為138,因為當前資料庫未執行什麼任務 
SQL> select * from v$memory_target_advice;
 
MEMORY_SIZE MEMORY_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR    VERSION
----------- ------------------ ------------ ------------------- ----------
       1024               0.25          138                   1          0
       2048                0.5          138                   1          0
       2560              0.625          138                   1          0
       3072               0.75          138                   1          0
       3584              0.875          138                   1          0
       4096                  1          138                   1          0
       4608              1.125          138                   1          0
       5120               1.25          138                   1          0
       5632              1.375          138                   1          0
       6144                1.5          138                   1          0
       6656              1.625          138                   1          0
       7168               1.75          138                   1          0
       7680              1.875          138                   1          0
       8192                  2          138                   1          0
 
14 rows selected

 

---sga各元件資訊,sga分配和釋放以granule單位進行
SQL> select * from v$sgainfo;
 
NAME                                  BYTES RESIZEABLE
-------------------------------- ---------- ----------
Fixed SGA Size                      2182592 No
Redo Buffers                       12185600 No
Buffer Cache Size                2197815296 Yes
Shared Pool Size                  301989888 Yes
Large Pool Size                    16777216 Yes
Java Pool Size                     16777216 Yes
Streams Pool Size                         0 Yes
Shared IO Pool Size                       0 Yes
Granule Size                       16777216 No ---16mb的粒度
Maximum SGA Size                 4275781632 No
Startup overhead in Shared Pool   100663296 No
Free SGA Memory Available        1728053248
 
12 rows selected        

---如下測試說明sga_target>=1g,Granule為16mb
SQL> show parameter sga_target

NAME                                 TYPE
------------------------------------ ----------------------
VALUE
------------------------------
sga_target                           big integer
1G
SQL> select * from v$sgainfo;

NAME                                          BYTES RESIZE
---------------------------------------- ---------- ------
Fixed SGA Size                              2182592 No
Redo Buffers                               12185600 No
Buffer Cache Size                         738197504 Yes
Shared Pool Size                          268435456 Yes
Large Pool Size                            16777216 Yes
Java Pool Size                             16777216 Yes
Streams Pool Size                                 0 Yes
Shared IO Pool Size                               0 Yes
Granule Size                               16777216 No
Maximum SGA Size                         4275781632 No
Startup overhead in Shared Pool           100663296 No
Free SGA Memory Available                3221225472

12 rows selected.

SQL> show parameter memory

NAME                                 TYPE
------------------------------------ ----------------------
VALUE
------------------------------
hi_shared_memory_address             integer
0
memory_max_target                    big integer
4G
memory_target                        big integer
0
shared_memory_address                integer
0
SQL> alter system set sga_target=2g scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 4275781632 bytes
Fixed Size                  2182592 bytes
Variable Size            2449474112 bytes
Database Buffers         1811939328 bytes
Redo Buffers               12185600 bytes
Database mounted.
Database opened.
SQL> select * from v$sgainfo;

NAME                                          BYTES RESIZE
---------------------------------------- ---------- ------
Fixed SGA Size                              2182592 No
Redo Buffers                               12185600 No
Buffer Cache Size                        1811939328 Yes
Shared Pool Size                          268435456 Yes
Large Pool Size                            16777216 Yes
Java Pool Size                             16777216 Yes
Streams Pool Size                                 0 Yes
Shared IO Pool Size                               0 Yes
Granule Size                               16777216 No
Maximum SGA Size                         4275781632 No
Startup overhead in Shared Pool           117440512 No
Free SGA Memory Available                2147483648

12 rows selected.

---換算粒度為mb
SQL> select 16777216/1024/1024 mb from dual;

        MB
----------
        16

 

---如下檢視可查oracle引數可配置的值
SQL> select num,name,ordinal,value,isdefault from v$parameter_valid_values where
 name='statistics_level';

       NUM NAME                                        ORDINAL
---------- ---------------------------------------- ----------
VALUE
--------------------------------------------------------------------------------

ISDEFAULT
--------------------------------------------------------------------------------

      1826 statistics_level                                  1
BASIC
FALSE

      1826 statistics_level                                  2
TYPICAL
TRUE

      1826 statistics_level                                  3
ALL
FALSE

SQL> alter system set statistics_level=basic scope=spfile;

System altered.

SQL> show parameter sga_

NAME                                 TYPE
------------------------------------ ----------------------
VALUE
------------------------------
sga_max_size                         big integer
2G
sga_target                           big integer
2G
SQL> show parameter statis

NAME                                 TYPE
------------------------------------ ----------------------
VALUE
------------------------------
optimizer_use_pending_statistics     boolean
FALSE
statistics_level                     string
TYPICAL
timed_os_statistics                  integer
0
timed_statistics                     boolean
TRUE
SQL> alter system set statistics_level=basic scope=spfile;

System altered