一：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 memoryNAME TYPE ------------------------------------ ----------- ------------------------------hi_shared_memory_address 0memory_max_target big 500Mmemory_target big 500Mshared_memory_address 0SYS@orcl>
- 自动内存管理（Automatic memory management ）
注：memory_target 的值大于0M，则为 AMM 管理模式：
SYS@orcl> show parameter sgaNAME TYPE ------------------------------------ ----------- ------------------------------lock_sga boolean pre_page_sga boolean sga_max_size big 500Msga_target big 0SYS@orcl>
SYS@orcl> show parameter pgaNAME TYPE ------------------------------------ ----------- ------------------------------pga_aggregate_target big 0SYS@orcl>
- 自动共享内存管理 (Automatic shared memory management)
注：把AMM 改为ASMM:
1：查看内存分配大小：
SYS@orcl> v$memory_dynamic_components Name ? Type ----------------------------------------- -------- ---------------------------- COMPONENT 2(64) CURRENT_SIZE NUMBER MIN_SIZE NUMBER MAX_SIZE NUMBER USER_SPECIFIED_SIZE NUMBER OPER_COUNT NUMBER LAST_OPER_TYPE 2(13) LAST_OPER_MODE 2(9) LAST_OPER_TIME GRANULE_SIZE NUMBERSYS@orcl> COMPONENT,CURRENT_SIZE v$memory_dynamic_components;COMPONENT CURRENT_SIZE---------------------------------------------------------------- ------------large pool 4194304ASM Buffer Cache 0streams pool 4194304 16K buffer cache 0java pool 4194304SGA Target 343932928 buffer cache 171966464KEEP buffer cache 0RECYCLE buffer cache 0 4K buffer cache 0 8K buffer cache 0COMPONENT CURRENT_SIZE---------------------------------------------------------------- ------------ 32K buffer cache 0Shared IO Pool 0shared pool 150994944 2K buffer cache 0PGA Target 18035507216 selected.SYS@orcl> COMPONENT,CURRENT_SIZE/1024/1024 v$memory_dynamic_components;COMPONENT----------------------------------------------------------------CURRENT_SIZE/1024/1024----------------------ASM Buffer Cache 0 16K buffer cache 0SGA Target 328COMPONENT----------------------------------------------------------------CURRENT_SIZE/1024/1024----------------------KEEP buffer cache 0RECYCLE buffer cache 0 4K buffer cache 0COMPONENT----------------------------------------------------------------CURRENT_SIZE/1024/1024---------------------- 8K buffer cache 0 32K buffer cache 0Shared IO Pool 0COMPONENT----------------------------------------------------------------CURRENT_SIZE/1024/1024---------------------- buffer cache 164 2K buffer cache 0shared pool 144COMPONENT----------------------------------------------------------------CURRENT_SIZE/1024/1024----------------------streams pool 4large pool 4java pool 4COMPONENT----------------------------------------------------------------CURRENT_SIZE/1024/1024----------------------PGA Target 17216 selected.SYS@orcl>2：修改内存大小：
SYS@orcl> system SGA_TARGet=328M;System altered.SYS@orcl> show parameter sgaNAME TYPE ------------------------------------ ----------- ------------------------------lock_sga boolean pre_page_sga boolean sga_max_size big 500Msga_target big 328MSYS@orcl>SYS@orcl> show parameter pgaNAME TYPE ------------------------------------ ----------- ------------------------------pga_aggregate_target big 0SYS@orcl> system pga_aggregate_target=172m;System altered.SYS@orcl> show parameter pga;NAME TYPE ------------------------------------ ----------- ------------------------------pga_aggregate_target big 172MSYS@orcl>SYS@orcl> show parameter memoryNAME TYPE ------------------------------------ ----------- ------------------------------hi_shared_memory_address 0memory_max_target big 500Mmemory_target big 500Mshared_memory_address 0SYS@orcl> system memory_target=0;System altered.SYS@orcl> show parameter memory;NAME TYPE ------------------------------------ ----------- ------------------------------hi_shared_memory_address 0memory_max_target big 500Mmemory_target big 0shared_memory_address 0SYS@orcl>注：把 ASMM 改为AMM :
SYS@orcl> show parameter memory;NAME TYPE ------------------------------------ ----------- ------------------------------hi_shared_memory_address 0memory_max_target big 500Mmemory_target big 0shared_memory_address 0SYS@orcl> system memory_target=500m 2 ;System altered.SYS@orcl> show parameter memory;NAME TYPE ------------------------------------ ----------- ------------------------------hi_shared_memory_address 0memory_max_target big 500Mmemory_target big 500Mshared_memory_address 0SYS@orcl> show parameter pga;NAME TYPE ------------------------------------ ----------- ------------------------------pga_aggregate_target big 172MSYS@orcl> system pga_aggregate_target=0;System altered.SYS@orcl> show parameter pga;NAME TYPE ------------------------------------ ----------- ------------------------------pga_aggregate_target big 0SYS@orcl> show parameter sga;NAME TYPE ------------------------------------ ----------- ------------------------------lock_sga boolean pre_page_sga boolean sga_max_size big 500Msga_target big 328MSYS@orcl> system sga_target=0;System altered.SYS@orcl> show parameter sga;NAME TYPE ------------------------------------ ----------- ------------------------------lock_sga boolean pre_page_sga boolean sga_max_size big 500Msga_target big 0SYS@orcl>
- 手工内存管理（Manual memory management）
SYS@orcl> show parameter cacheNAME TYPE ------------------------------------ ----------- ------------------------------client_result_cache_lag big 3000client_result_cache_size big 0db_16k_cache_size big 0db_2k_cache_size big 0db_32k_cache_size big 0db_4k_cache_size big 0db_8k_cache_size big 0db_cache_advice string db_cache_size big 0db_flash_cache_file stringdb_flash_cache_size big 0NAME TYPE ------------------------------------ ----------- ------------------------------db_keep_cache_size big 0db_recycle_cache_size big 0object_cache_max_size_percent 10object_cache_optimal_size 102400result_cache_max_result 5result_cache_max_size big 1280Kresult_cache_mode string MANUALresult_cache_remote_expiration 0session_cached_cursors 50SYS@orcl>SYS@orcl> show parameter javaNAME TYPE ------------------------------------ ----------- ------------------------------java_jit_enabled boolean java_max_sessionspace_size 0java_pool_size big 0java_soft_sessionspace_limit 0SYS@orcl>SYS@orcl> show parameter poolNAME TYPE ------------------------------------ ----------- ------------------------------buffer_pool_keep stringbuffer_pool_recycle stringglobal_context_pool_size stringjava_pool_size big 0large_pool_size big 0olap_page_pool_size big 0shared_pool_reserved_size big 7549747shared_pool_size big 0streams_pool_size big 0SYS@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_bNAME TYPE ------------------------------------ ----------- ------------------------------db_block_buffers 0db_block_checking string db_block_checksum string TYPICALdb_block_size 8192SYS@orcl>#logbuffer 的值是固定值SYS@orcl> show parameter log_bufNAME TYPE ------------------------------------ ----------- ------------------------------log_buffer 5668864SYS@orcl>