Oracle内存管理（之四），oracle内存管理
【深入解析--eygle】学习笔记
1.3 SGA管理
SGA指系统全局区（System Global Area）,是一块用于加载数据、对象并保存运行状态和数据库控制信息的一块内存区域，在数据库实例启动时分配，当实例关闭时释放，每个实例都拥有自己的SGA区。
在第一章曾经ᨀ到，当数据库启动到nomount状态时，SGA已经分配，同时启动后台进程，在SQL*Plus中通过show sga命令可以看到SGA的分配情况：
sys@felix SQL>show parameter sga NAME TYPE VALUE ---------------------------------------------------------- ------------------------------ lock_sga boolean FALSE pre_page_sga boolean FALSE sga_max_size big integer 400M sga_target big integer 0 16:12:56 sys@felix SQL>
连接到Oracle数据库的用户都可以共享SGA中的数据，通常为了更优化的性能，我们总是期望在物理内存允许的情况下，设置更高的SGA区，以减少物理I/O（SGA中数据缓冲区的增大可以有效地减少物理读）。
1.4 SGA
下图是最常见的数据库实例体系结构图，展现了SGA的结构:
1.4.1 固定区域 - Fixed Area
Fixed Size 部分是SGA中的固定部分，包含几千个变量和一些小的数据结构，如Latch或地址指针等，这部分内存分配和特定的数据库版本以及平台有关，不受用户控制，而且这些信息对于数据库来说非常重要，但是通常我们用户不需要关心。
固定部分只需要很小的内存，可以通过一个内部表X$KSMFSV（[K]ernel [S]ervice Layer ， [M]emoryManagement，Addresses of [F]ixed [S]GA [V]ariables）查询。此外Oracle的内部表X$KSMMEM记录了整个SGA的地址映射关系，通过X$KSMFSV和X$KSMMEM关联，可以找出Fixed Area中每个变量的设置。
在32位平台上，X$KSMMEM表中每条记录代表4 Bytes，在64位平台，每条记录代表4 Bytes：
sys@felix SQL>select * from x$ksmmem whererownum <5; ADDR INDX INST_ID KSMMMVAL ---------------- ---------- -------------------------- 0000000060000000 0 1 00 0000000060000008 1 1 00 0000000060000010 2 1 00 0000000060000018 3 1 00 16:21:39 sys@felix SQL>
sys@felix SQL>select ksmfsnam,ksmfssiz from x$ksmfsv where ksmfsnam='kcsgscn_'; KSMFSNAM KSMFSSIZ ----------------- -------- kcsgscn_ 48 16:23:51 sys@felix SQL>
通过ORADEBUG工具可以得到当前内存中的SCN值：
16:23:51 sys@felix SQL>oradebug setmypid Statement processed. 16:26:19 sys@felix SQL>oradebug DUMPvar SGA kcsgscn_ kcslf kcsgscn_ [060019598, 0600195C8) = 00183004 00000000 00000000 00000000 000024AE 00000000 00000000 00000000 00000000 00000000 60019278 00000000 16:26:22 sys@felix SQL>
SCN值获取如下:
sys@felix SQL>selectto_number('183004','xxxxxxxxxx') from dual;
TO_NUMBER('183004','XXXXXXXXXX')
--------------------------------
1585156
16:27:05 sys@felix SQL>
Fixed Area包含很多控制信息，但是需要注意的是，查询X$KSMFSV视图可能会导致进程异常，需要谨慎使用
1.4.1.1 Buffer Cache
Buffer Cache-缓冲区高速缓存，用于存储最近使用的数据块，这些数据块可能是被修改过的，也可能是未经修改的。我们知道，在Oracle对数据的处理过程中，代价最昂贵的就是物理I/O（Physical I/O）操作了，同样的数据从内存中得到要比从磁盘上读取快得多，所以将尽可能多的数据保存在内存中，可以减少磁盘I/O操作，从而提高数据库的性能。
从Oracle9i开始，Oracle引入了一个新的初始化参数db_cache_size ,该参数用来定义主Block Size（db_block_size定义的块大小）的Default缓冲池的大小
各内存组件所使用的Granule大小可以通过动态性能视图来查询：
16:36:00 sys@felix SQL>select component,granule_size from v$sga_dynamic_components; COMPONENT GRANULE_SIZE --------------------------------- ------------ shared pool 4194304 large pool 4194304 java pool 4194304 streams pool 4194304 DEFAULT buffer cache 4194304 KEEP buffer cache 4194304 RECYCLE buffer cache 4194304 DEFAULT 2K buffer cache 4194304 DEFAULT 4K buffer cache 4194304 DEFAULT 8K buffer cache 4194304 DEFAULT 16K buffer cache 4194304 DEFAULT 32K buffer cache 4194304 Shared IO Pool 4194304 ASM Buffer Cache 4194304 14 rows selected. 17:33:14 sys@felix SQL>
Oracle管理Buffer Cache使用的是LRU算法，但是这又带来另外一个问题，很多批处理的操作（比如全表扫᧿等）可能会导致Buffer Cache的刷新，将经常使用的数据“挤出”Buffer Cache，在不同版本中，Oracle不停的改进LRU算法，以避免这类操作的过度影响。
但是在此之外，Oracle提供了BufferCache的多缓冲池技术从另外一个方面来解决这个问题。所谓的多缓冲池技术是指，根据不同数据的不同访问方式，将Buffer Cache分为Default、Keep和Recycle池三个部分。对于经常使用的数据，我们可以在建表时就指定将其存放在Keep池中；对于经常一次性读取使用的数据，可以将其存放在Recycle池中；Keep池中的数据倾向于一直保存，Recycle池中的数据倾向于即时老化，而Default池则存放未指定存储池的数据，按照LRU算法管理。
默认情况下，所有表都使用DEFAULT池，它的大小就是数据缓冲区Buffer Cache的大小，由初始化参数db_cache_size(8i中是db_block_size*db_block_buffers)决定。
17:33:14 sys@felix SQL>show parameter db_cache_size NAME TYPE VALUE ------------------------------------ ---------------------- ------------------------------ db_cache_size big integer 0 17:37:24 sys@felix SQL>
如果我们在创建数据表或修改数据表时指定STORAGE (BUFFER_POOL KEEP)或者STROAGE(BUFFER_POOL RECYCLE)语句，就设置了这张表使用KEEP或者RECYCLE缓冲区。这两个缓冲区的大小分别由初始化参数db_keep_cache_size和db_recycle_cache_size来决定。
17:39:03 sys@felix SQL>show parameter db_keep_cache_size NAME TYPE VALUE ------------------------------------ ---------------------- ------------------------------ db_keep_cache_size big integer 0
|
Property |
Description |
|
Parameter type |
Big integer |
|
Syntax |
DB_KEEP_CACHE_SIZE = integer [K | M | G] |
|
Default value |
0 (DB_KEEP_CACHE_SIZE is not configured by default) |
|
Modifiable |
ALTER SYSTEM |
|
Range of values |
Minimum: 0 (values greater than zero are automatically modified to be either the granule size * number of processor groups, or 4 MB * number of CPUs, whichever is greater) Maximum: operating system-dependent |
|
Basic |
No |
DB_KEEP_CACHE_SIZE specifiesthe size of the KEEP bufferpool. The size of the buffers in the KEEP buffer pool is the primary blocksize (the block size defined by the DB_BLOCK_SIZE initialization parameter).
17:39:24 sys@felix SQL>show parameter db_recycle_cache_size NAME TYPE VALUE ------------------------------------ ---------------------- ------------------------------ db_recycle_cache_size big integer 0 17:39:42 sys@felix SQL>
|
Property |
Description |
|
Parameter type |
Big integer |
|
Syntax |
DB_RECYCLE_CACHE_SIZE = integer [K | M | G] |
|
Default value |
0 (DB_RECYCLE_CACHE_SIZE is not configured by default) |
|
Modifiable |
ALTER SYSTEM |
|
Range of values |
Minimum: 0 (values greater than zero are automatically modified to be either the granule size * number of processor groups, or 4 MB * number of CPUs, whichever is greater) Maximum: operating system-dependent |
|
Basic |
No |
DB_RECYCLE_CACHE_SIZE specifiesthe size of the RECYCLE bufferpool. The size of the buffers in the RECYCLE pool is the primary block size(the block size defined by the DB_BLOCK_SIZE initialization parameter).
17:39:42 sys@felix SQL>show parameter cache_size NAME TYPE VALUE ---------------------------------------------------------- ------------------------------ 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_size big integer 0 db_flash_cache_size big integer 0 db_keep_cache_size big integer 0 db_recycle_cache_size big integer 0
同时还可以看到，在Oracle9i以后存在一系列的db_nk_cache_size参数，这是Oracle9i中引入的多块大小支持。Oracle9i以后允许在同一个数据库中存在多种Block_size的表空间，分别支持： 2k,4k,8k,16k和32k 的Block_size,其中，由db_block_size定义的块大小被称为主Block_size。如果在数据库中创建不同block_size的表空间则需要分别设定db_nk_cache_size参数。
各缓冲池的设置，我们可以通过查询v$buffer_pool得到：
17:43:03 sys@felix SQL>select id,name,block_size,current_size,target_size from v$buffer_pool;
ID NAME BLOCK_SIZE CURRENT_SIZE TARGET_SIZE
---------- ---------------------------------------- ---------- ------------ -----------
3 DEFAULT 8192 72 72
17:46:03 sys@felix SQL>
1.4.1.2 Shared Pool
Shared Pool通常被称为共享池，包含共享内存结构，如SQL区等。SQL区包含SQL解
析树、执行计划等信息，通过共享池，反复执行的SQL可以在不同Session间得到共享。
1.4.1.3 Redo Log Buffer
Redo Log Buffer-日志缓冲区存储重做日志条目（redo entries）,日志记录数据库变更，最终将被写出到重做日志文件中，在数据库崩溃或故障时用于恢复；如果 数 据 库 运 行 在 归 档 模 式下，最终日志文件还会被写出到归档日志中，这些归档可以在介质恢复时用于进行数据恢复。
日志缓冲区的大小由初始化参数log_buffer决定。这是一个静态参数，不能动态调整。
内存是影响数据库性能的重要因素，Oracle8i使用静态内存管理，Oracle 10g使用动态
内存管理。所谓静态内存管理，就是在数据库系统中，无论是否有用户连接，也无论并发用
量大小，只要数据库服务在运行，就会分配固定大小的内存；动态内存管理允许在数据库服
务运行时对内存的大小进行修改，读取大数据块时使用大内存，小数据块时使用小内存，读
取标准内存块时使用标准内存设置。
按照系统对内存使用方法的不同，Oracle数据库的内存可以分为以下几个部分：
??系统全局区：SGA（System Global Area）
??程序全局区：PGA（Programe Global Area）
??排序池：（Sort Area）
??大池：（Large Pool）
??Java池：（Java Pool）
排序内存涉及到PGA。
什么时候使用自动PGA内存管理？什么时候使用手动PGA内存管理？
白天系统正常运行时适合使用自动PGA内存管理，让Oracle根据当前负载自动管理、分配PGA内存。
夜里用户数少、进行维护的时候可以设定当前会话使用手动PGA内存管理，让当前的维护操作获得尽可能多的内存，加快执行速度。
如：服务器平时运行在自动PGA内存管理模式下，夜里有个任务要大表进行排序连接后更新，就可以在该操作session中临时更改为手动PGA内存管理，然后分配大的SORT_AREA_SIZE和HASH_AREA_SIZE（50%甚至80%内存，要确保无其他用户使用），这样能大大加快系统运行速度，又不影响白天高峰期对系统造成的影响。
操作命令
会话级更改
ALTER SESSION SET WORKAREA_SIZE_POLICY = {AUTO | MANAUL}；
ALTER SESSION SET SORT_AREA_SIZE = 65536；
ALTER SESSION SET HASH_AREA_SIZE = 65536；
学以致用
1，排序区：
pga_aggregate_target为100MB，单个查询能用到5%也就是5MB时排序所需时间
SQL> create table sorttable as select * from all_objects;
表已创建。
SQL> insert into sorttable (select * from sorttable);
已创建49735行。
SQL> insert into sorttable (select * from sorttable);
已创建99470行。
SQL> set timing on;
SQL> set autotrace traceonly;
SQL> select * from sorttable order by object_id;
已选择198940行。
已用时间: 00: 00: 50.49
Session级修改排序区为30mb所需时间
SQL> ALTER SESSION SET WORKAREA_SIZE_POLICY = MANUAL;
会话已更改。
已用时间: 00: 00: 00.02
SQL> ALTER SESSION SET SORT_AREA_SIZE = 30000000;
会话已更改。
已用时间: 00: 00: 00.01
SQL> select * from sorttable order by object_id;
已选择198940行。
已用时间: 00: 00: 10.76
可以看到所需时间从50.49秒减少到10.31秒，速度提升很明显。
2，散列区：
pga_aggregate_target为100MB，单个查询能用到5%也就是5MB时表连接所需时间
SQL> select /*+ use_hash(tb1 tb2)*/ * from sorttable tb1,sorttable tb2 where tb1.object_id=tb2.object_id;
已选择49735行。
已用时间: 00: 00: 40.5......余下全文>>