SGA优化

1 SGA优化
1.1 shared pool
shared pool 使用LRU（least recently used）来进行管理。（LRU，最近最少使用算法，有点象传送带，最近被使用的内容总是被放在头部，不常使用的会逐渐被排在尾部，在内存容量不够使用时 会被标记自由被覆盖掉（若为data buffer cache，则全表扫描是放在尾部的））语法分析操作是高代价的。应尽可能让SQL语句在shared pool中有一个已解析版本。若找到一个已解析的可用的版本，叫一次高速缓存区命中（cache hit），若找不到且必须进行语法分析，则叫一次脱靶（cache miss）。为了能命中，两条SQL语句必须一完全（包括大小写、空格等必须一样）。

Shared pool由三部分组成：library cache（库高速缓存区）、data dictionary cache（数据目录高速缓存区）、user global area（用户全局区）。
 library cache
缓存用户发布的SQL和PL/SQL（包括procedure,function,package,trigger,pl/sql,java class）。
一旦被缓存，这些语句就具有：
1）语句本身的实际文本。
2）语句所关联的散列值。
3）语句的P-CODE。
4）语句所关联的任何统计数据。
5）语句的执行计划。

可以查看上述5个属性视图：

v$sql,v$sqltext,v$sqlarea,v$sql_plan,v$session

v$session的command列值说明：
2-insert
3-select
6-update
7-delete
26-lock table
44-commit
45-rollback
47-pl/sql execute

select type,count(executions) from v$db_object_cache
group by type
order by 2 desc;
高cursor执行次数，说明SQL是直接发布的。而不是利用procedure或function。

data dictionary cache
缓存发布语句的：
检查的数据字典确保该语句引用的对象的存在，列名和数据类型的正确，所执行应具有的对象权限。

user global area
共享模式中：缓存用户会话信息，因可能启动和结束同一个事务的可能不是同一个共享服务器进程。
非共享模式中：用户会话信息保存在PGA。此时无UGA。

1.1.1 测量shared pool 性能
重新启动的数据，需要一段时间给shared pool预热一下，让应用重装载SQL，否则会有一个很底的命中率。

library cache
应用程序在内存中查找到该SQL已缓存的频率

SQL> select namespace,gets,gethits,
1-(gets-gethits)/gets "gethitratio_1",
gethitratio,pins,pinhits,
1-(pins-pinhits)/pins "pinhitratio_1",
pinhitratio,
reloads,reloads/pins "reload ratio",invalidations
from v$librarycache
where namespace in
('SQL AREA','TABLE/PROCEDURE','BODY','TRIGGER');

SQL AREA：SQL语句
TABLE/PROCEDURE：PL/SQL存储过程或函数
BODY：PL/SQL程序包主体
TRIGGER：PL/SQL触发器

Gethitratio：至少要超过90%，越高越好（数据仓库和DSS具有特殊性，可能会经常低）。（每执行一句，gets加1，找到一次，gethits加1）

Pinhitratio：至少要超过90%，越高越好（数据仓库和DSS具有特殊性，可能会经常低）。（每执行一条语句，就需要一个对象锁，pins加1；不等待获得，pinhits加1）

reload ratio：显示已执行过有缓存副本的SQL，因一些原因（如引用的视图被重新编译，或表字段或类型被修改等）被设置为语法分析过期或作废而必须被重新语 法分析的次数。reload ratio至少应该小于1%。reloads/pins>1%有两种可能，一种是library cache空间不足，一种是sql中引用的对象不合法。

Invalidations：SQL语句的语法分析被失效的总次数。

data dictionary cache
应用程序在内存中查找到所需数据字典信息的频率。
SQL>select
1-(SUM(getmisses)/SUM(gets)) "data dictionary hit ratio"
from v$rowcache;
data dictionary hit ratio：至少不能低于85%。

1.1.2 调整shared pool性能
Shared pool的优化应该放在优先考虑，因为一个cache miss在shared pool中发生比在data buffer中发生导致的成本更高。
修改的目标都是通过增加命中率来提高shared pool的性能，方法有5类：
1) 增大shared pool
2) 为大型PL/SQL语句腾出空间
3) 将重要的PL/SQL代码保持在内存中
4) 鼓励SQL重复利用
5) 调整library cache相关init.ora参数

增大shared pool
增大shared pool，由于是自动相对分配library cache和data dictionary cache，可以减少已被缓存信息被LRU机制从内存中移走的可能。（一般不会出现一个提高了，另一个很底的情况；往往会由于动态分配而统一提高）
确定当前被分配大小（默认64位操作系统被设置成64M，32位操作系统被设置成16M；若使用JAVA，至少要有50MJAVA类装入才能工作，或直接指定另一个参数java_pool_size）：
SQL> select pool,sum(bytes)/1024/1024 "SIZE"
from v$sgastat
where pool = 'shared pool'
group by pool;

************************************************************
set echo off
set feedback off
set serveroutput on
declare
v_total_plsql_mem number:=0;
v_total_sql_mem number:=0;
v_total_sharable_mem number:=0;

begin
select sum(sharable_mem)/1024/1024 into v_total_plsql_mem
from v$db_object_cache;

select sum(sharable_mem)/1024/1024 into v_total_sql_mem
from v$sqlarea where executions > 0;

v_total_sharable_mem := v_total_plsql_mem + v_total_sql_mem;

dbms_output.put_line('estimated reauired shared pool size is:'||to_char(v_total_sharable_mem)||' M');

end;
/
************************************************************
估算实际至少需要多少shared pool：

 

Shared_pool_size是动态参数，可通过如下方法动态修改：
SQL>alter system set shared_pool_size=300M scope=both;
（shared pool + database buffer cache + redo log buffer，能不超过sga_max_size，否则报ORA-04033错误）

为大型PL/SQL语句腾出空间
shared_pool_reserved_size参数，设置shared pool的某一部分专供大程序包和触发器使用。避免把大包装入shared pool时，把其他若干条SQL解析版本被LRU从内存中移走，降低命中率。
默认为shared_pool_size的5%，最大可到50%。Oracle 建议从10%开始设置起。
查看当前已缓存在内存中的PL/SQL大小：
select sum(sharable_mem)/1024/1024 "SIZE M"
from v$db_object_cache
where type in ('PACKAGE','PACKAGE BODY');
确定shared_pool_reserved_size设置性能：
SQL> select request_misses,free_space,request_failures
from v$shared_pool_reserved;
若给shared_pool_reserved_size分配的内存过多，则：
1）request_misses的数据始终是0或是静态的。
2）free_space是shared_pool_reserved_size的50%以上。
若分配的过少，则：
request_failures非0值，或不断增长。

或使用如下方法，指定在大的程序包载入内存时，可以有多少字节的内存被LRU移走（若超出这个限制，返回给用户一个内存溢出的错误）：
SQL>execute dbms_shared_pool.aborted_request_threshold(10000);
可使用的范围：5000 至 2,000,000,000

将重要的PL/SQL代码保持在内存中
可以把PL/SQL程序包、触发器、序列，永久的保存在shared_pool_reserved_size，直到实例关闭。（alter system flush shared_pool也不会清洗掉）
创建锁定程序包：
SQL>@$ORACLE_HOME/rdbms/admin/dbmspool.sql
使用锁定包：
SQL>execute dbms_shared_pool.keep(‘c_perf.single_c_perf_cell_bts_h’); --锁定
SQL>execute dbms_shared_pool.unkeep(‘c_perf.single_c_perf_cell_bts_h’); --取消锁定
查找被锁定的对象：
SQL> select name,owner,type from v$db_object_cache where kept = 'YES';
查找需要锁定的对象：
直接SQL和PL/SQL的无名块是无法锁定在内存的，下列查询查找直接SQL或PL/SQL无名块，最好把他们做成存储过程或包来锁定在内存。
SQL>select substr(sql_text,1,45),length(sql_text) "stmt_size"
from v$sqlarea
where command_type=47
order by length(sql_text) desc;
锁定的时机：
1）启动数据库时执行锁定脚本。
2）编写after startup on database的触发器。

鼓励SQL重复利用
尽可能使用已高速缓存的代码。仅当新语句与缓存的语句有相同的散列值才被认为是相同的。
1）应该规定使用固定格式的SQL，如大小写、空格、行的编程标准等，考虑代码的通用性。
2）使用bind variable。（缺点：由于bind variable只是值的容器，而不是实际的值，CBO必须对其值进行猜测，可能会影响最佳执行计划。）

调整library cache相关init.ora参数
这些参数的修改，一般都会要求更多的shared pool。
OPEN_CURSORS
每个用户session，都通过cursor的使用被在主内存中分配专用的SQL区。
该参数限制一个session最多可呀打开的cursor数。
默认值为50，太小。
增加这个值，以、允许使用更多的cursor，还可呀帮助减少该用户以前已执行的SQL的重分析次数。

CURSOR_SPACE_FOR_TIME
设置成TRUE，共享SQL区将被锁定在shared pool，除非引用该共享SQL区的所有游标都被关闭，否则不会被LRU移出shared pool。
设置成TRUE，可以提高命中率，减少SQL执行时间。
代价：使用更多的shared pool内存。
若有足够多的shared pool，可以容纳所有潜在的锁定SQL区，建议从默认的FALSE改成TRUE。

SESSION_CACHED_CURSORS
默认值为0。指定同一条SQL语句由同一个session执行多次时，指定这些SQL所关联的cursor有多少个应被缓存在library cache中。
增大该参数，可以提高命中率。

CURSOR_SHARING
改变分析与缓存SQL语句时的shared pool的默认行为。
FORCE：Oracle 8i引入。允许两条只在字面值方面有差别的SQL语句共享已缓存的SQL分析代码。字面值的差别，必须不改变该语句的意义。
SIMILAR：Oracle 9i引入。允许两条只在字面值方面有差别的SQL语句共享已缓存的SQL分析代码。字面值的差别，必须不改变该语句的意义及其缓存的执行计划。
EXACT：默认设置。两条SQL语句必须完全一样，才可利用已缓存的SQL分析代码。

例，如设置成SIMILAR，则如下两条语句可以使用相同的已缓存代码：
SQL>select order_id,customer_id from orders where order_id = 1001;
SQL>select order_id,customer_id from orders where order_id = 1022;

1.2 Database buffer cache
用来缓存用户最近在数据库中访问过的段数据块的副本。Buffer cache中每个缓冲区大小，对应于一个数据库块大小，并存有一个数据库块内容。
被缓存的段包括：表、索引、cluster、Large Object段、LOB索引、undo segment、temp setment。

缓冲区在database buffer cache被以下方式的一种组合进行管理：
1）LRU列表。
2）User Server Porcess（用户服务器进程）
3）Database Writer （DBW0） Background Process。

 LRU
发布一条SQL，在数据被修改或被返回给用户前，该SQL所关联的段数据必须被User Server Porcess从磁盘上读到buffer cache。如同shared pool，这些数据由LRU进行管理。（全表扫描的物理块，被放在LRU的最末端，防止大表上的FTS把其他所有缓冲区都从高速缓存区中挤出去。）
LRU管理的缓冲区有四种状态：
- Free：缓冲区当前未在使用
- Pinned：缓冲区当前由一个server process在使用。
- Clean：刚从一个锁定中释放出来，并引数据未被改变而被标记为可重用。
- Dirty：缓冲区未在使用，但含有还未被DBW0写到磁盘上的已提交的数据。
管理dirty块的机制叫dirty list（脏列表）或Write list（写列表），通过一个检查点队列，这个列表跟踪被修改但还没有写盘的所有块。Dirty块由DBW0写盘，在写以前，也被user server process利用。

User Server Porcess
在发布SQL后，user server process在buffer cache中寻找所需数据的已缓存块。在未找到时，再从磁盘上读物理块。
在读物理块前，必须找到一个自由缓冲区，以便将物理块保存在内存里，此查找搜索的过程，就会利用LRU列和Dirty List：
1）搜索LRU列表的自由块时，发现的任何脏块都被server process转移到dirty list上。
2）当Dirty list达到一定长度时，DBW0就把那些脏缓冲区写到磁盘。
3）若server process搜索LRU太长时间，而未发现有自由缓冲区时，DBW0将从LRU直接把脏块写物理磁盘（而不是先放到dirty list上）。
DBW0也帮助管理LRU和dirty list 。

DBW0
除了user server process会导致DBW0写脏数据到磁盘外，以下几个原因也会让DBW0写脏数据到磁盘：
- dirty list到达指定长度时。
DBW0把dirty list中的数据写盘。
- LRU被搜索很长时间而未找到一个自由缓冲区时。
DBW0直接把LRU上的脏数据写盘。
- 每过3秒。
DBW0从LRU列表把脏数据写到dirty list上，此时如果dirty list过长，DBW0把dirty list写盘。
- 检查点。
DBW0从LRU把脏缓冲区转移到dirty list，然后写盘。
- 数据库关闭时
除非使用shutdown abort，否则DBW0总把所有脏缓冲区写盘。
- 表空间热备份时，表空间临时脱机
DBW0从LRU把该表空间的脏块转移到dirty list上，然后写盘。
- 删除段时
删除一个表或一个索引，导致DBW0先把该段的脏数据写盘

1.2.1 测量database buffer cache性能
命中率测量数据：发布的SQL语句所需要的数据块，在一个缓冲区找到时，命中一次；一个块找不到而必须从物理盘读，则脱靶一次。
SQL> select
1 - ((physical.value - direct.value - lobs.value)/logical.value)
"Buffer Cache Hit Ratio"
from v$sysstat physical,v$sysstat direct,v$sysstat lobs,v$sysstat logical
where physical.name = 'physical reads'
and direct.name = 'physical reads direct'
and lobs.name = 'physical reads direct (lob)'
and logical.name = 'session logical reads';
physical reads：表、索引、回退段，已从磁盘读到buffer cache中的数据块个数。
physical reads direct：故意绕过buffer cache，直接从磁盘读取的数据块个数。所以计算命中率应该取消这部分值。
physical reads direct (lob)：访问lob时，故意安排的绕过buffer cache，直接从磁盘读取的数据块个数。所以计算命中率应该取消这部分值。
session logical reads：申请某个数据块的一个请求因使用buffer cache中所缓存的缓冲区而得到满足的次数。

Buffer Cache Hit Ratio：OLTP应具有超过90%的命中率，越高越好。

非命中率测量数据：
Free buffer inspected ：每个server process在查找一个自由缓冲区期间所检查的缓冲区个数。Dirty buffer inspected ，在查找自由缓冲区时，找到的脏缓冲区总数。
Free buffer waits：在user server process经历free buffer inspected活动期间，所经历的等待次数。（每当server process必须等待DBW0将脏缓冲区写盘时，就发生一个等待）
Buffer busy waits：server process等待一个自由缓冲区变的可用的次数。当server process请求的块已在缓冲区，但正由另一个进程使用，这种等待就发生一次。

 SQL> select name,value from v$sysstat where name = 'free buffer inspected'
union
select event,total_waits
from v$system_event
where event in ('free buffer waits','buffer busy waits');
上述任一统计数据很高，或不段增长，表示user server process正花费太多时间搜索并等待访问database buffer cache中的自由缓冲区。

1.2.2 改进buffer cache性能
通过提高命中率来改进buffer cache的性能。一般有5种方法：
1）增大db buffer cache。
2）使用多个缓冲池。
3）把表缓存在buffer cache 中。
4）绕过buffer cache，直接读物理块。
5）适当的使用索引。

1.2.2.1 增大db buffer cache
加大database buffer cache是最简单的方法。越大，缓冲区越不可能被LRU从buffer cache中删除掉，降低freee buffer inspected等的统计数据所报告的等待次数等。
影响db buffer cache的参数：
db_block_size
db_cache_size
db_keep_cache_size
db_recycle_cache_size
db_2k_cache_size
db_nK_cache_size

db_block_size设置主块大小，至少SYSTEM和TEMP表空间就会使用主块，其他表空间默认也使用主块大小。Database buffer cache是相对于块大小来调整的。
不要使用db_2K_cache_size来设置主块大小。判断需要增多大的buffer cache才够
常见行为：
1）增加到命中率不再提高。
2）增加到物理内存被用尽。

可以使用Oracle 9i Buffer Cache Advisory功能，跟踪buffer cache变化时候里面的具体变化。
Init.ora参数db_cache_advice可有：
- ON：开始收集数据。会消耗内存和CPU资源，因此最好手工调整。
- READY：给Oracle 9i Buffer Cache Advisory功能分配内存，但不启动。此时无CPU被耗用，也无统计数据生成。只是保证Oracle 9i Buffer Cache Advisory功能在ON时，可以找到他的内存。
- OFF：关闭Oracle 9i Buffer Cache Advisory功能，释放任何与它有关的CPU和内存。
如果不是从READY到ON，而是直接从OFF到ON，可能会收到一个ORA-04031的错误。

启活Oracle 9i Buffer Cache Advisory功能后，应等待至少30分钟以上的时间再查看统计数据。

查看统计数据：
SQL>select name,size_for_estimate,estd_physical_reads
from v$db_cache_advice
where block_size = 8192
and advice_status = 'ON';

如果调整为size_for_estimate的大小，则估计会有estd_physical_reads次物理读。
对db_cache_size的增加，并不会一直增加命中率。

1.2.2.2 使用多个缓冲池
针对每种块（db_block_size），可用有三种不同的缓冲池：default、keep、recycle。其中在9i，可用针对种块大小，设置三种不同的缓冲池的大小。分别设置，可防止一些常用的数据被一些不常用的数据挤出内存。

Keep pool：DBA几乎不想让它们离开database buffer cache的段。
Recycle pool：DBA几乎不想让它们留在database buffer cache的段。
Default pool：用来高速缓存非keep和recycle的段。

默认init.ora参数db_cache_size只设置default的大小，不能为0。Db_keep_cache_size和db_recycle_cache_size设置的Keey和recycle默认值为0。
每个pool由一个LRU列表来管理。

通过如下观察：单个索引，不要超过300M，不要大表全表扫描。（单个索引超过1G，而且还被读，最是可恶）

确定那些段需要高速缓存
查看当前在database buffer cache里的缓存信息
SQL>select obj.owner,obj.object_name,obj.object_type,count(distinct bh.block#) "Num.Buffers"
from dba_objects obj,v$bh bh
where obj.object_id = bh.objd
and owner != 'SYS'
group by obj.owner,obj.object_name,obj.object_type
order by 4 desc;

执行SQL> @$ORACLE_HOME/rdbms/admin/catparr.sql
SQL>select username "owner",name "seg.name",kind "seg.type",count(distinct block#) "num.buffers"
from v$cache,dba_users
where v$cache.owner# = dba_users.user_id
group by name,username,kind
having count(distinct block#) > 10
order by 3 desc;

SQL>select name,block_size,current_size from v$buffer_pool;

确定每个池的大小
分析过的表或索引，可通过dba_tables或dba_indexes的blocks列进行计算，需要的大小。

 创建缓冲区池
如果分配的SGA_MAX_SIZE允许，可使用如下命令创建：
SQL>alter system set db_cache_size = 400M;
SQL>alter system set db_keep_cache_size = 150M;
SQL>alter system set db_recycle_cache_size = 50M;

给缓冲区池分配段
SQL>alter table Unicom.ne_cell storage (buffer_pool keep);
SQL>alter table Unicom.alarminfo storage (buffer_pool recycle);

确定当前不是分配在default缓冲区池的段：
select owner,segment_type,segment_name,buffer_pool
from dba_segments
where buffer_pool != 'DEFAULT';

监视多缓冲区池
查看各缓冲区池当前大小：
SQL> select name,block_size,current_size
from v$buffer_pool;

计算各缓冲区池的命中率：
SQL>select name "buffer pool",
1-(physical_reads/(db_block_gets+consistent_gets)) "bufer pool hit ratio"
from v$buffer_pool_statistics
order by name;

调整后，keep pool的命中率应该非常非常的高，而default pool的命中率应该在70%-80%范围内，recycle pool的命中率应该不高。

注意：
命中率100%，并不意味着效率一定很高。此时应该继续检查cache里的内容，特别是应确定有多少个索引缓冲区，以及他们属于那些索引。检查这些索引的具体属性。如果这些索引非常大，而且经常被访问---导致命中率高，但又没必要全部把这些索引读入内存cache 中。
解决办法：
1）确保当前统计数据正确。以便CBO选择是全表扫描还是索引扫描。
2）创建直方图，以便CBO对所以列值分布有较充分的了解。
3）指定该索引保存在recycle pool中。
这样一般可以对应用获得一个真正的性能好处。
1.2.2.3 把表缓存在buffer cache 中
不管是default、keep、recycle，都是使用LRU管理，导致一个问题：
经常被使用的小表的全表扫描，因为是FTS，所以会被放在LRU尾端，很快就被挤出缓冲区。
可以有三种方法，改变全表扫描的这种工作方式，把表数据放在LRU的头端，好象把表数据一直锁定在内存一样：
1）创建表时缓存。
SQL>create table test (t1 varchar2(10)) tablespace users cache;
默认的创建方法都是nocache的。

2）使用alter table 命令。
SQL>alter table ne_cell cache;

3）使用提示。
SQL>select /*+ cache */ ne_id from ne_cell;
该提示只会改变本次查询的FTS工作方式，下次查询将继续使用原来的方式。

查询已得到缓存的表：
SQL> select owner,table_name from dba_tables where ltrim(cache) = 'Y';

1.2.2.4 绕过buffer cache，直接读物理块
可以将以下两种事务配置成避开database buffer cache。
1）Sort Direct Writes （调整磁盘I/O有讨论）。
2）Parallel DML

1.2.2.5 适当的使用索引
使用合适的索引，尽量避免大表的全表扫描。
在应用了另一个表的主键的表的外键列上建立索引，以帮助改进多表连接的性能。

1.3 Large pool
大型池，默认为0（除非parallel_automatic_tuning设置为true，large pool即可自动设置），即无任何large pool存在，此时应该它存储的缓冲区，会放入shared pool，此时可能会降低shared pool的性能（如果配置了large pool，此时large pool不允许其他组件放入shared pool）。
Large pool缓存以下对象有关数据：
1）Database Writer 的I/O辅助进程。
2）Recovery Manager的备份与恢复操作（RMAN）。
3）Shared Server 会话数据（MTS环境）。
4）Parallel Query选件的消息传送信息。

使用RMAN的地方注意了！

测量large pool性能
SQL> select name,bytes from v$sgastat
where pool = 'large pool';
若free memory很大，或不断增加，说明分配给large pool过大了；如果很小或不断变小，说明分配的large pool不够。

改进large pool性能
增加large pool的大小。
Large_pool_size，静态参数，必须重启动实例改变。
值范围为：600KB至2GB。

1.4 Java pool
专门用做与会话相关的JAVA代码和应用程序变量在程序运行期间所驻留的场所。配置java pool对JAVA应用来说，至关重要。
Shared_pool_size：参考前面的shared pool。
Java_pool_size：与JAVA相关的其他全部会话的数据的缓存。默认20M，范围：1M-1GB。
Java_soft_sessionspace_limit：当给一个JAVA进程分配内存时，如果超过这个值，报告一条信息到user_dump_dest目录。默认为0，最大为4GB。
Java_max_sessionspace_size：当给一个JAVA进程分配内存时，如果超过这个值，就提示一个ORA-29554内存益处错误。默认为0，最大为4GB。

测量java pool性能
SQL> select name,bytes from v$sgastat
where pool = 'java pool';
若free memory很大，或不断增加，说明分配给java pool过大了；如果很小或不断变小，说明分配的java pool不够。

改进java pool性能
增加java pool的大小。
java_pool_size，静态参数，必须重启动实例改变。

1.5 Redo log buffer
1.5.1 Oracle重做机制
Oracle重做机制记录应用程序用户对数据库所做的修改结果，以便那些已提交的事务能在发生故障时重做，包括5个组件：
1）Redo log buffer：记录重做一个事务所需要的信息，由每个用户server process复制到redo log buffer。
2）Log writer（LGWR）：负责把redo log buffer写到online redo log 上，每个实例只有一个LGWR。
3）Checkpoint：一个checkpoint事件，代表数据库处于一个一致状态时的时刻。只有在最后一个检查点前发生的事务，才能在实例恢复期间得到恢复。
4）Online Redo Log：记录redo log buffer的文件，每个数据库必须至少有2组redo log file，LGWR写当前REDO LOG 时，LGWR写下一批重做日志前先切换到下一个REDO LOG 组。
5）Archive log：归档日志文件，当LGWR写完了全部可使用的REDO LOG FILE后，它将通过再次写REDO LOG组重新开始，如果此时启动了archive，在归档一个日志文件前，LGWR始终不重用该日志文件。

1.5.2 调整Redo Log Buffer
Rodo Log Buffer的管理使用非LRU的方法，是一种漏斗型的方法，buffer信息从漏斗顶不进入，不时的从底部倒出，为了能跟上进入的数量，每当下列任一事件发生，LGWR就会写盘：
1）每发布一条commit语句时
2）每间3秒
3）每当redo log buffer空间使用了3分之1时
4）每当redo log buffer达到1M时
5）每当一个checkpoint出现时

（从现场数据库看，日志信息写的频率太大，应想办法使用NOLOG的方式进行运算，减少日志信息，减少I/O及等待）

1.5.2.1 测量redo log buffer性能
1）测量重试率：
select to_char(retries.value/entries.value) "redo log buffer retry ratio"
from v$sysstat retries,v$sysstat entries
where retries.name = 'redo buffer allocation retries'
and entries.name = 'redo entries';
redo log buffer retry ratio：重试率，应该小于1%，不断增大或很大，表示redo log buffer需要调整。
redo entries：实例启动以来，由用户server process放入redo log buffer的项目个数。
redo buffer allocation retries：用户server process因LGWR还没有把当前项目写入日志文件而必须等待，然后重试把它们的项目放入redo log buffer的重试次数。

2）测量发生的等待数据：
select name,value from v$sysstat
where name in
('redo buffer allocation retries','redo entries','redo log space requests');
redo log space requests：LGWR从当前redo log file转移到下一个redo log file时等待切换发生的频率。该值增加或变大，说明redo log文件可能太小。

（V$sysstat中的事件，可在A90190-01中Statistic Descreptions查阅。）

3）测量会话等待数据：
select username,wait_time,seconds_in_wait
from v$session_wait,v$session
where v$session_wait.sid = v$session.sid
and event like '%log buffer space%';

1.5.2.2 改进Redo Log Buffer性能
调整目标：
保证用户server process能够访问并将项目放入redo log buffer，同时又不经历等待。
技巧：
1）使之更大
2）降低重做信息的生成

1.1.1.1.1 使之更大
Redo log buffer越大，用户server process试图将项目放入redo log buffer时，经历等待的可能越小。
Init.ora参数log_buffer指定其大小。
范围：64KB~OS允许的最大值。（若被修改的非常小，Oracle Server将会超越设置，使用默认的大小）

1.1.1.1.2 降低重做信息的生成

 

Oracle建议调整日志文件长度，以便每隔20-30分钟切换一次日志，减少因为日志切换导致的checkpoint频繁导致checkpoint不能完成的可能。

select username,wait_time,seconds_in_wait
from v$session_wait,v$session
where v$session_wait.sid = v$session.sid
and event like '%log buffer space%';

select event,total_waits,time_waited from v$system_event where event like '%log buffer space%';

select event,total_waits,time_waited,average_wait
from v$system_event
where event like '%check%'
or event like '%log file switch%'

select name,value
from v$sysstat
where name like '%background checkpoint%';

select event,total_waits,time_waited,average_wait
from v$system_event
where event like 'log file parallel write'
or event like 'log file switch completion';

1.6 其他SGA对象
1）redo log buffer
对应的参数是log_buffer，缺省值与 OS相关，一般是500K。检查v$session_wait中是否存在log buffer wait,v$sysstat中是否存在redo buffer allocation retries

A、检查是否存在log buffer wait：
Select * from v$session_wait where event=’log buffer wait’ ;
如果出现等待，一是可以增加log buffer的大小，也可以通过将log 文件移到访问速度更快的磁盘来解决。

B、Select name,value from v$sysstat where name in (‘redo buffer allocation retries’,’redo entries’)
Redo buffer allocation retries接近0，小于redo entries 的1％，如果一直在增长，表明进程已经不得不等待redo buffer的空间。如果Redo buffer allocation retries过大，增加log_buffer的值。

C、检查日志文件上是否存在磁盘IO竞争现象
Select event,total_waits,time_waited,average_wait from v$system_event where event like ‘log file switch completion%’;
如果存在竞争，可以考虑将log文件转移到独立的、更快的存储设备上或增大log文件。

D、检查点的设置是否合理
检查alert.log文件中，是否存在‘checkpoint not complete’；
Select event,total_waits,time_waited,average_wait from v$system_event where event like ‘log file switch (check%’;

如果存在等待，调整log_checkpoint_interval、log_checkpoint_timeout的设置。

E、检查log archiver的工作
Select event,total_waits,time_waited,average_wait from v$system_event where event like ‘log file switch (arch%’;

如果存在等待，检查保存归档日志的存储设备是否已满，增加日志文件组，调整log_archiver_max_processes。

F、DB_block_checksum=true，因此增加了性能负担。（为了保证数据的一致性，oracle的写数据的时候加一个checksum在block上，在读数据的时候对checksum进行验证）

2）java pool
对于大的应用，java_pool_size应>=50M，对于一般的java存储过程，缺省的20M已经够用了。
3）检查是否需要调整DBWn
Select total_waits from v$system_event where event=’free buffer waits’;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23190213/viewspace-660693/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23190213/viewspace-660693/