一：Oracle 数据库内存结构简介
注：缓存数据为：数据文件中的数据信息
1：基本内存结构
与Oracle数据库相关联的基本内存结构包括：
- 系统全局区 System global area (SGA)
- 程序全局区 Program global area (PGA)
- 用户全局区 User Global Area (UGA)
注：UGA 有时存在于SGA中：专有模式
有时存在于PGA中: 共享
- 软件代码区 Software code areas
2：Oracle 数据库内存管理
指定内存分配大小：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 ）
注：memory_target 的值大于0M，则为 AMM 管理模式：
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)
注：把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）
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
二：用户全局区概述
三：程序全局区概述
1：PGA 的内容
- 私有SQL区
- 私有SQL区又分为以下几个区域：
- 运行时区域
- 持久区域
- SQL工作区
2：在专用和共享服务器模式中使用PGA
四：系统全局区概述
1：数据库缓冲区高速缓存(Database Buffer Cache)
Oracle 数据库使用缓冲区高速缓存，来实现以下目标：
- 优化物理I/O
- 将频繁访问的块保持在Database Buffer Cache 中，将不常存取的块写到磁盘
缓冲区状态：
- 未使用的（Unused）
- 干净的（Clean）
- 脏的（Dirty）
缓冲模式：
- 1）当前模式 Current mode
- 2）一致模式 Consistent mode
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>
