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请参阅:http://docs.oracle.com/cd/E11882_01/server.112/e25494/memory005.htm#BABHEDBH)
              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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-753708/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-753708/