OracleSGA详解

sga(system global area )全局区

l 数据高速缓存

在oracle进行数据处理的过程中，代价最昂贵的就是物理 i/o操作了。同样的数据从内存中得到要比从磁盘上读取快的多。因此，优化oracle的一个重要的目标就是尽可能的降低物理 i/o操作。

oracle的 buffer cache用于缓存从磁盘中读取的数据，当 oracle需要查找某些信息的时候，首先会在 buffercache中寻找，如果找到了，则直接将结果返回。如果找不到，则需要对磁盘进行扫描， oracle将在从磁盘扫描得到的结果返回的同时，会把这些数据在buffer cache中也保留一份，如果下次需要访问相同的数据块的时候，则不需要再次扫描磁盘，而是直接从 buffer cache中读取即可。

2 数据高速缓存由初始化参数db_cache_size指定大小，由许多大小相等的缓存块组成，这些缓存块的大小和os块大小相同。这些缓存块分为 3 大类：

n 脏缓存块(dirty buffers )：

脏缓存块中保存的已被修改过的缓存块。即当一条sql语句对某个缓存块中的数据进行修改后，该缓存块就被标记为脏缓存块。最后，脏缓存块被dbwn进程写入到硬盘的数据文件中，永久保留起来。

n 命中缓存块(pinned buffers )：

命中缓存块中保存的是最近正在被访问的缓存块。它始终被保留在数据高速缓存中，不会被写入数据文件。

n 空闲缓存块（freebuffers）：

该缓存块中没有数据，等待被写入数据。oracle从数据文件中读取数据后，寻找空闲缓存块，以便写入其中。

2 标准缓冲区缓存分为以下三种：

n 保持(keep)缓冲池：长期保存在内存中，直到数据库被关闭为止，其大小由buffer_pool_keep指定。

n 再生(recycle)缓冲池：数据一旦用毕后就被换出内存中，其大小由buffer_pool_recycle指定。

n 默认(default)缓冲池：数据使用lru调度算法来换出，其大小由db_cache_size 决定。

2 工作原理和过程lru（最近最少使用 least recently used ）。oracle通过 2 个列表（dirty、lru）来管理缓存块。

n dirty 列表中保存已经被修改但还没有被写入到数据文件中的脏缓存块。

n lru 列表中保存还没有被移动到dirty列表中的脏缓存块、空闲缓存块、命中缓存块。当某个缓存块被访问后，该缓存块就被移动到lru列表的头部(most recent used, mru端)，其他缓存块就向lru列表的尾部(least recently used, lru 端)移动。放在最尾部的缓存块就最先被移出lru列表。

说明：如果用户执行的是全表扫描的操作，这些操作产生的数据缓冲不会放到 lru端的 mru端，而是放到lru端。因为oracle认为全表扫描得到的数据只是暂时的需要，这些数据以后被重用的机会很少，应该快速的清除出缓冲区，把空间留给其他的更常用的数据。可以在表的级别上改变这种处理方式。在建表的时候指定cache语句会使得这张全表扫描得到的数据也放在 lru链表的 mru端。

n 数据高速缓存的工作原理过程是：

a、oracle在将数据文件中的数据块复制到数据高速缓存中之前，先在数据高速缓存中找空闲缓存块，以便容纳该数据块。oracle 将从lru列表的尾部开始搜索，直到找到所需的空闲缓存块为止。

b、如果先搜索到的是脏缓存块，则将该脏缓存块移动到dirty列表中，然后继续搜索。如果搜索到的是空闲缓存块，则将数据块写入，然后将该缓存块移动到dirty列表的头部。

c、如果能够搜索到足够的空闲缓存块，就将所有的数据块写入到对应的空闲缓存块中。则搜索写入过程结束。

d、如果没有搜索到足够的空闲缓存块，oracle就先停止搜索，激活dbwn进程，开始将dirty列表中的脏缓存块写入到数据文件中。

e、已经被写入到数据文件中的脏缓存块将变成空闲缓存块，并被放入到lru列表中。执行完成这个工作后，再重新开始搜索，直到找到足够的空闲缓存块为止。

2 keep池和 recycle池的使用

如果内存足够大，可以容纳所有的数据，则访问任何数据都可以从内存中直接获得，那么效率肯定是最高的。但是在实际应用当中，经常是数据库的大小达到了几百个 gb甚至是几个 tb，而 oralce的可用内存只有几个 gb大小。缓存中缓存的数据只能占到整个数据库数据的很小一部分，因此，这就要求必须合理的分配内存的使用。

如果可使用的内存空间比较小，导致数据库缓冲区的命中率比较低，则可以通过配置 keep池和 recycle池，把性质不同的表分离到不同的数据缓冲区，以提高命中率，降低此操作对正常访问的影响。

默认情况下，所有的表都是用 default池，它的大小就是缓冲区buffer cache的大小，由初始化参数 db_cache_size来决定。如果在建表或者修改表的时候指定 storage（buffer_poolkeep）或者 storage（buffer_poolrecycle）语句，就设置这张表使用 keep或者 recycle缓冲区。这两个缓冲区的大小分别由初始化参数 db_keep_cache_size和db_recycle_cache_size来决定。

通过下面的sqlplus命令查看带“cache_size”字符串的系统参数值

sql> show parametercache_size

结果如下：

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 --属于sga自动管理，值为0.

db_keep_cache_size big integer 0

db_recycle_cache_size big integer 0

sql> alter system set db_keep_cache_size=16mscope=both;

sql> alter system setdb_recycle_cache_size=16m scope=both;

sql> show parametercache_size

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 --属于 sga自动管理组件，值为0.

db_keep_cache_size big integer 16m

db_recycle_cache_size big integer 16m

n keep池

keep池用来缓存那些经常会被访问的表。

keep池使用缓冲区独立于default池，因此把最经常使用的表缓存到单独的缓冲区中，使得数据库的其他操作，如执行大量批操作也不会影响到这些在 keep缓冲区中的表，保证访问这些最常使用的表的数据时，都可以从内存中直接获得。

sql> col name format a30

sql> col value format a30

sql> conn scott/scott

sql> create tabletest_default(col number(3)) storage(buffer_pool default);

sql> create tabletest_keep(col number(3)) storage(buffer_pool keep);

sql> create table test_recycle(colnumber(3)) storage(buffer_pool recycle);

sql> insert intotest_default values(1);

sql> insert into test_keepvalues(1);

sql> commit;

sql> set autotrace on statistics

sql> select * fromtest_default;

统计信息

1 recursive calls

0 db block gets

7 consistent gets

0 physical reads

0 redo size

407 bytes sent via sql*net to client

385 bytes received via sql*net from client

2 sql*net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

sql> select * fromtest_keep;

统计信息

1 recursive calls

0 db block gets

7 consistent gets

0 physical reads

0 redo size

407 bytes sent via sql*net to client

385 bytes received via sql*net from client

2 sql*net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

sql> show sga

total system global area 528482304 bytes

fixed size 1249944 bytes

variable size 150998376 bytes

database buffers 369098752 bytes

redo buffers 7135232 bytes

sql> select369098752/1024/1024 from dual; --计算databasebuffers的大小为352m

在上面的例子中，建立了两张表。 test_default指定默认的default池，test_keep指定了keep池。分别插入了一条数据，然后打开自动跟踪，对这两张表进行查询，由于刚刚执行了 insert语句，这两条数据都存放在各自的缓冲区中，因此查询的物理读（physical reads）为 0，接着查看 buffer cache的值，发现大小为352m，sga为 504m。

下面构造一个较大的批操作，插入的数据大于 504m，将 default区域覆盖掉。

sql> create tabletest_eat_memory (col1 varchar2(4000), col2 varchar2(4000), col3 varchar2(4000),col4 varchar2(4000), col5 varchar2(4000), col6 varchar2(4000), col7varchar2(4000), col8 varchar2(4000), col9 varchar2(4000), col10 varchar2(4000))storage(buffer_pool default);

sql> insert intotest_eat_memory select rpad('1',4000,'1'), rpad('2',4000,'2'), rpad('3',4000,'3'),rpad('4',4000,'4'),rpad ('5',4000,'5'), rpad('6',4000,'6'), rpad('7',4000,'7'),rpad('8',4000,'8'), rpad('9',4000,'9'), rpad('0',4000,'0') from all_objectswhere rownum<=15000; --插入15000行数据

统计信息

10410 recursive calls

564195 db block gets

108584 consistent gets

620 physical reads

637527688 redo size --大约插入了638m数据

678 bytes sent via sql*net to client

803 bytes received via sql*net from client

4 sql*net roundtrips to/from client

91 sorts (memory)

0 sorts (disk)

15000 rows processed

sql> commit;

执行完批操作后，对两张表再次查询。

sql> select * fromtest_default;

统计信息

70 recursive calls

0 db block gets

13 consistent gets

12 physical reads

0 redo size

407 bytes sent via sql*net to client

385 bytes received via sql*net from client

2 sql*net roundtrips to/from client

2 sorts (memory)

0 sorts (disk)

1 rows processed

sql> select * fromtest_keep;

统计信息

70 recursive calls

0 db block gets

13 consistent gets

0 physical reads

0 redo size

407 bytes sent via sql*net to client

385 bytes received via sql*net from client

2 sql*net roundtrips to/from client

2 sorts (memory)

0 sorts (disk)

rows processed

sql> show sga

total system global area 528482304 bytes

fixed size 1249944 bytes

variable size 150998376 bytes

database buffers 369098752 bytes --default区域没有变化

redo buffers 7135232 bytes

结果很明显。由于 keep和 default池彼此独立，对于 test_keep的查询的物理读仍然为0，而对 test_default的查询则包含了12个物理读。

上面的例子可以看出，使用 keep池可以保证那些指定 keep池的表不受其他表的影响。可以查询v$bh视图来查找到经常被使用的表，根据表的使用频繁度来确定是否指定 keep池。

select o.object_name, count(*)from dba_objects o, v$bh bh where o.object_id = bh.objd and o.owner != 'sys'group by o.object_name having count(*) > 100 order by count(*) desc

确定好使用 keep池的表后，可以根据这些表的实际大小之和来计算缓冲区的大小。由于这个大小可以比较准确的计算出来，因此可以对这些表使用 cache，以保证即使采用全表扫描得到的数据也可以被缓冲。

通常情况下，并不追求 keep池的命中率达到 100%，如果命中率为 100%，则说明给 keep池的内存空间过大，有部分内存被浪费。即使 keep池中缓存的都是些最经常访问的表，这些访问操作也不大可能访问到表中所有的数据。因此，可以适当的减少 keep池的内存分配，使 keep池的命中率维持在接近 100%的数值。将这部分内存分配给其他的缓冲区，可以得到更高的效率。

可以采用下面的sql语句来计算 keep池的命中率。

sql> select name,physical_reads, db_block_gets, consistent_gets, 1 -(physical_reads /(db_block_gets + consistent_gets)) "hit ratio" fromv$buffer_pool_statistics where name = 'keep';

n recycle池

recycle池用来缓存那些不希望保存在内存中的表。例如很少进行扫描或者访问的表。如果应用程序以一种随机的方式访问一张比较大的表，这些被缓冲的数据在被清除出内存之前，很少会有机会再次被访问。这些数据存放在缓冲区当中，不仅会浪费内存空间，而且可能把其他的一些有可能被访问的数据清除出去。这些数据没必要保存在缓冲区当中，可以通过使用 recycle池来避免这些数据对其他数据的影响。

调整参数db_recycle_cache_size的大小来设置recycle池。一般来说，不需要给 recycle池很大的内存空间，因为recycle池中的数据没有什么被缓存的价值。设置较小的缓冲区可以将更多的内存留给keep 和default池。但是，如果缓冲区太小的话，数据可能在事务结束之前就从内存从中被清除了，这会导致额外的性能问题。

sql> truncate tabletest_eat_memory;

sql> select * fromtest_default;

统计信息

0 recursive calls

0 db block gets

7 consistent gets

0 physical reads

0 redo size

407 bytes sent via sql*net to client

385 bytes received via sql*net from client

2 sql*net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

sql> alter tabletest_eat_memory storage(buffer_pool recycle);

sql>insert intotest_eat_memory select rpad('1',4000,'1'), rpad('2',4000,'2'), rpad('3',4000,'3'),rpad('4',4000,'4'),rpad ('5',4000,'5'), rpad('6',4000,'6'), rpad('7',4000,'7'),rpad('8',4000,'8'), rpad('9' ,4000,'9'), rpad('0',4000,'0 ) from all_objectswhere rownum<=15000; --插入15000行数据

统计信息

10410 recursive calls

564195 db block gets

108584 consistent gets

620 physical reads

637527688 redo size --大约插入了638m数据

678 bytes sent via sql*net to client

803 bytes received via sql*net from client

4 sql*net roundtrips to/from client

91 sorts (memory)

0 sorts (disk)

15000 rows processed

sql> commit;

sql> select * fromtest_default;

统计信息

70 recursive calls

0 db block gets

0 consistent gets

12 physical reads

0 redo size

407 bytes sent via sql*net to client

385 bytes received via sql*net from client

2 sql*net roundtrips to/from client

2 sorts (memory)

0 sorts (disk)

rows processed

在这个例子中，将进行批操作的表改为recycle池，在批操作执行完成后，发现 test_default表的数据仍然可以在default池中找到。这种方法屏蔽了批操作对系统的影响。

小结

对于大多数的系统而言，使用 default池就够了。但是如果内存空间相对较小，或者对系统中表的使用情况有比较清晰的认识，则可以通过配置 keep池和 recycle池来细化内存的分配，提高数据缓冲区的命中率，降低批操作对系统的影响。

虽然 keep池、recycle池用来缓存不同类型的数据，但是他们的机制是相同的，他们采用的都是lru算法。如果keep池分配的内存不足，那么也会有部分数据被清除出内存；如果 recycle池的内存分配足够，也可以保证其中的数据全部缓存。从本质上讲， keep池和 recycle池并没有什么区别，只是名字不同而已。

如果给 keep池或者 recycle池分配的内存大小不合适，不但不会提高性能，而且会造成性能的下降。以 keep池为例，内存分配小了，这些经常被访问的、数据就会有部分被清除出内存，导致命中率降低。如果内存分配过大，则导致 default池内存要相应的减少，default池不仅包括用户部分数据，而且也包括数据字典的缓冲。因此， default池内存的不足，必然导致整个系统性能的下降。而且，由于真实环境中，所有的表的大小都处于变化之中，因此，需要经常对不同缓冲区的命中率进行检查，并随时调整缓冲区的大小以满足数据不断变化的需要。

l 重做日志高速缓存

2 重做日志高速缓存大小由初始化参数log_buffer指定，可以在运行期间修改该参数。

2 工作原理：为了加快访问速度和工作效率，重做记录并不直接写入重做日志文件中，而是首先从数据高速缓存写入重做日志高速缓存。当重做日志高速缓存中的重做记录达到一定数量或某个时间点时，再由lgwr进程分批写入重做日志文件中（即oracle 总是先日志后文件或先内存后磁盘）。由于重做日志文件是循环使用的，所以当重做日志文件切换时，还会由arcn（如果启用了归档日志模式）进程将即将要被覆盖的重做日志文件中的数据写入到归档日志文件中，作为备份。

showparameter log_buffer; ------查询重做日志缓存的大小

select* from v$sysstat; ------查询用户进程等待重做日志缓存的次数。

l 共享池

2 共享池由初始化参数shared_pool_size指定，默认80mb，可以在运行期间手动修改该参数。

2 共享池中保存了最近执行的sql语句、pl/sql过程与包、数据字典信息、锁、以及其他控制结构的信息。共享池是对sql语句、pl/sql程序进行语法分析、编译、执行的内存区。共享池又分为两部分：

n 数据字典缓存

ü 数据字典缓存用于存储经常使用的数据字典信息。比如：表的定义、用户名、口令、权限、数据库的结构等。

ü oracle运行过程中经常访问该缓存以便解析sql语句，确定操作的对象是否存在，是否具有权限等。如果不在数据字典缓存中，服务器进程就从保存数据字典信息的数据文件中将其读入到数据字典缓存中。数据字典缓存中保存的是一条一条的记录（就像是内存中的数据库），而其他缓存区中保存的是数据块信息。

n 库缓存

ü 库缓存大小与open_cursor初始化参数相关，oracle中每条查询语句都需要打开一个游标，open_cursor默认值为300。

ü 库缓存的目的就是保存最近解析过的sql语句、pl/sql过程和包。这样一来，oracle在执行一条sql语句、一段pl/sql 过程和包之前，首先在“库缓存”中搜索，如果查到它们已经解析过了，就利用“库缓存”中解析结果和执行计划来执行，而不必重新对它们进行解析，显著提高执行速度和工作效率。

2 oracle长期运行后，共享池可能出现碎片，这时可以用以下语句清除共享池内的全部数据：alter system flush shared_pool;

2 实验与案例

n 验证数据块缓存和共享池的作用

1. 用sys登陆sqlplus，执行统计用户表空间个数的语句。

sql>conn / as sysdba

sql>set timing on

sql>select count(*) from user_tablespaces;

由于是第一次执行该查询，需要将外存的user_tablespaces信息读入数据块缓存并对其进行解析，再把解析结果存储到共享池的库缓存中，所以用时较多。

2. 第二次执行该查询。由于不需要读外存，且sql语句执行计划在内存中，不需要硬解析，速度较快。

sql>select count(*) from user_tablespaces;

3. 清空共享池，第三次执行。由于清除了共享池，需要重新解析sql语句，但所需数据还在数据库块缓存中，所以用时介于两者之间。

sql>select count(*) from user_tablespaces;

n 缓存命中率

ü 逻辑读(logicalreads)：即从缓存中读取数据。

ü 物理读(physicalreads)：即从物理磁盘上读取数据。

sql>select statisti, name, value from v$sysstat where name in ('physical reads', 'dbblock gets', 'consistent gets')

说明：v$sysstat是用来动态跟踪系统性能参数的数据字典表。查询和计算命中率有关的三个数据库行，分别是physical reads, db block gets, consistent gets。其中db block gets和 consistentgets的value值相加数据值为所有读的总次数。逻辑读次数为总和减去physical reads的value值。命中率为：逻辑读次数/所有读的总次数。

l 大池

2 由初始化参数large_pool_size确定大小，可以使用alter system语句来动态改变大池的大小。

2 大池是可选项的，dba可以根据实际业务需要来决定是否在sga区中创建大池。如果创建，将会自动的被各种各样的进程拿来使用，本来这些进程是使用共享池的内存。如果没有创建大池，则需要大量内存空间的操作将占用共享池的内存。

2 oracle 需要大力内存的操作有：

a、数据库备份和恢复。

b、具有大量排序操作的sql语句。

c、并行化的数据库操作。

l java池

2 由初始化参数java_pool_size确定大小，控制在30-50mb比较合适。

2 用户存放java代码、java语句的语法分析表、java语句的执行方案和进行java。

2 可以使用alter system set java_pool_size=0m scope=spfile;调整其大小，语句在服务器初始化参数文件中修改该参数。必须重新启动数据库服务器才能使其生效。