/**PageBeginHtml Block Begin **/ /***自定义返回顶部小火箭***/ /*生成博客目录的JS 开始*/ /*生成博客目录的JS 结束*/

内存架构

                 image

一:Oracle 数据库内存结构简介


image

注:缓存数据为:数据文件中的数据信息


1:基本内存结构

与Oracle数据库相关联的基本内存结构包括:

  • 系统全局区 System global area (SGA)

image


  • 程序全局区  Program global area (PGA)

image


  • 用户全局区  User Global Area (UGA)

image

注:UGA 有时存在于SGA中:专有模式

               有时存在于PGA中:  共享



  • 软件代码区  Software code areas

image

image


2:Oracle 数据库内存管理

image

指定内存分配大小:memory_max_target  >=  内存分配最大的大小:memory_target 


SYS@orcl> show parameter memory

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address             integer     0
memory_max_target                    big integer 500M
memory_target                        big integer 500M
shared_memory_address                integer     0
SYS@orcl>
  • 自动内存管理(Automatic memory management )

image

注:memory_target 的值大于0M,则为 AMM 管理模式:

image

SYS@orcl> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 500M
sga_target                           big integer 0
SYS@orcl>

SYS@orcl> show parameter pga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 0
SYS@orcl>


  • 自动共享内存管理 (Automatic shared memory management)

image


注:把AMM 改为ASMM:

1:查看内存分配大小:

SYS@orcl> DESC v$memory_dynamic_components
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COMPONENT                                          VARCHAR2(64)
 CURRENT_SIZE                                       NUMBER
 MIN_SIZE                                           NUMBER
 MAX_SIZE                                           NUMBER
 USER_SPECIFIED_SIZE                                NUMBER
 OPER_COUNT                                         NUMBER
 LAST_OPER_TYPE                                     VARCHAR2(13)
 LAST_OPER_MODE                                     VARCHAR2(9)
 LAST_OPER_TIME                                     DATE
 GRANULE_SIZE                                       NUMBER

SYS@orcl> select distinct COMPONENT,CURRENT_SIZE from v$memory_dynamic_components;

COMPONENT                                                        CURRENT_SIZE
---------------------------------------------------------------- ------------
large pool                                                            4194304
ASM Buffer Cache                                                            0
streams pool                                                          4194304
DEFAULT 16K buffer cache                                                    0
java pool                                                             4194304
SGA Target                                                          343932928
DEFAULT buffer cache                                                171966464
KEEP buffer cache                                                           0
RECYCLE buffer cache                                                        0
DEFAULT 4K buffer cache                                                     0
DEFAULT 8K buffer cache                                                     0

COMPONENT                                                        CURRENT_SIZE
---------------------------------------------------------------- ------------
DEFAULT 32K buffer cache                                                    0
Shared IO Pool                                                              0
shared pool                                                         150994944
DEFAULT 2K buffer cache                                                     0
PGA Target                                                          180355072

16 rows selected.

SYS@orcl>  select distinct COMPONENT,CURRENT_SIZE/1024/1024 from v$memory_dynamic_components;

COMPONENT
----------------------------------------------------------------
CURRENT_SIZE/1024/1024
----------------------
ASM Buffer Cache
                     0

DEFAULT 16K buffer cache
                     0

SGA Target
                   328


COMPONENT
----------------------------------------------------------------
CURRENT_SIZE/1024/1024
----------------------
KEEP buffer cache
                     0

RECYCLE buffer cache
                     0

DEFAULT 4K buffer cache
                     0


COMPONENT
----------------------------------------------------------------
CURRENT_SIZE/1024/1024
----------------------
DEFAULT 8K buffer cache
                     0

DEFAULT 32K buffer cache
                     0

Shared IO Pool
                     0


COMPONENT
----------------------------------------------------------------
CURRENT_SIZE/1024/1024
----------------------
DEFAULT buffer cache
                   164

DEFAULT 2K buffer cache
                     0

shared pool
                   144


COMPONENT
----------------------------------------------------------------
CURRENT_SIZE/1024/1024
----------------------
streams pool
                     4

large pool
                     4

java pool
                     4


COMPONENT
----------------------------------------------------------------
CURRENT_SIZE/1024/1024
----------------------
PGA Target
                   172


16 rows selected.

SYS@orcl>


2:修改内存大小:

SYS@orcl> alter system  set SGA_TARGet=328M;

System altered.

SYS@orcl> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 500M
sga_target                           big integer 328M
SYS@orcl>
SYS@orcl> show parameter pga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 0
SYS@orcl> alter system set pga_aggregate_target=172m;

System altered.

SYS@orcl> show parameter pga;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 172M
SYS@orcl>
SYS@orcl> show parameter memory

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address             integer     0
memory_max_target                    big integer 500M
memory_target                        big integer 500M
shared_memory_address                integer     0
SYS@orcl> alter system set memory_target=0;

System altered.

SYS@orcl> show parameter memory;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address             integer     0
memory_max_target                    big integer 500M
memory_target                        big integer 0
shared_memory_address                integer     0
SYS@orcl>

注:把 ASMM  改为AMM :

SYS@orcl> show parameter memory;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address             integer     0
memory_max_target                    big integer 500M
memory_target                        big integer 0
shared_memory_address                integer     0
SYS@orcl> alter system set memory_target=500m
  2  ;

System altered.

SYS@orcl> show parameter memory;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address             integer     0
memory_max_target                    big integer 500M
memory_target                        big integer 500M
shared_memory_address                integer     0
SYS@orcl> show parameter pga;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 172M
SYS@orcl> alter system set pga_aggregate_target=0;

System altered.

SYS@orcl> show parameter pga;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 0
SYS@orcl> show parameter sga;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 500M
sga_target                           big integer 328M



SYS@orcl> alter system set sga_target=0;

System altered.

SYS@orcl> show parameter sga;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 500M
sga_target                           big integer 0
SYS@orcl>


  • 手工内存管理(Manual memory management)

image

SYS@orcl> show parameter cache

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_lag              big integer 3000
client_result_cache_size             big integer 0
db_16k_cache_size                    big integer 0
db_2k_cache_size                     big integer 0
db_32k_cache_size                    big integer 0
db_4k_cache_size                     big integer 0
db_8k_cache_size                     big integer 0
db_cache_advice                      string      ON
db_cache_size                        big integer 0
db_flash_cache_file                  string
db_flash_cache_size                  big integer 0

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_keep_cache_size                   big integer 0
db_recycle_cache_size                big integer 0
object_cache_max_size_percent        integer     10
object_cache_optimal_size            integer     102400
result_cache_max_result              integer     5
result_cache_max_size                big integer 1280K
result_cache_mode                    string      MANUAL
result_cache_remote_expiration       integer     0
session_cached_cursors               integer     50
SYS@orcl>


SYS@orcl> show parameter java

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
java_jit_enabled                     boolean     TRUE
java_max_sessionspace_size           integer     0
java_pool_size                       big integer 0
java_soft_sessionspace_limit         integer     0
SYS@orcl>
SYS@orcl> show parameter pool

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_keep                     string
buffer_pool_recycle                  string
global_context_pool_size             string
java_pool_size                       big integer 0
large_pool_size                      big integer 0
olap_page_pool_size                  big integer 0
shared_pool_reserved_size            big integer 7549747
shared_pool_size                     big integer 0
streams_pool_size                    big integer 0
SYS@orcl> show parameter str




二:用户全局区概述

image

image


三:程序全局区概述

image

image

image


1:PGA 的内容

image

  • 私有SQL区

image

image

  • 私有SQL区又分为以下几个区域:
  •   运行时区域

image


  •   持久区域

image


  • SQL工作区 

image


       

        2:在专用和共享服务器模式中使用PGA

      image



      四:系统全局区概述

      image

      1:数据库缓冲区高速缓存(Database Buffer Cache)

      image

      Oracle 数据库使用缓冲区高速缓存,来实现以下目标:

      • 优化物理I/O

      image


      • 将频繁访问的块保持在Database Buffer Cache 中,将不常存取的块写到磁盘



      缓冲区状态:

      image

      • 未使用的(Unused)

      image


      • 干净的(Clean)

      image


      • 脏的(Dirty)

      image

      image




      缓冲模式:

      imageimage

      • 1)当前模式  Current mode

      image


      • 2)一致模式 Consistent mode

      image


      image


      image

      image

      image

      image

      image


      image

      image

      SYS@orcl> show parameter db_b
      
      NAME                                 TYPE        VALUE
      ------------------------------------ ----------- ------------------------------
      db_block_buffers                     integer     0
      db_block_checking                    string      FALSE
      db_block_checksum                    string      TYPICAL
      db_block_size                        integer     8192
      SYS@orcl>


      #logbuffer 的值是固定值
      SYS@orcl> show parameter log_buf
      
      NAME                                 TYPE        VALUE
      ------------------------------------ ----------- ------------------------------
      log_buffer                           integer     5668864
      SYS@orcl>

      image

      image

      image

      image

      image

      image

      image

      image

      image

      image

      image

      image

      image













      ————————————————————————————————————————————————————————————————

      posted @ 2018-04-27 21:31  一品堂.技术学习笔记  阅读(511)  评论(0编辑  收藏  举报