Oracle 9i 整体性能优化概述草稿之一:调整争用

2.1 优化维护 4
2.2 诊断LATCH竞争 4
2.2.1 概念 4
2.2.2 是否存在latch争用 5
2.2.3 检查Latch是否主要竞争 5
2.2.4 DBA关注的latch内容 5
2.3 诊断FREE LIST竞争 6
2.3.1 概念 6
2.3.2 是否存在free list争用 6
2.3.3 确定free list 争用的段 7
2.3.4 优化free list争用 7
2.4 诊断LOCK竞争 8
2.4.1 概念 8
2.4.2 可能引起lock contention的原因 8
2.4.3 锁解决办法 9
2.4.4 死锁 9

2 调整争用
争用：每当一个Oracle 进程试图访问一个Oracle结构，但由于该结构正由另一个进程结构使用而未能成功访问到它时，就发生对Oracle资源的争用。常见的有latch、Free List 、lock争用。
主要维护的争用有：
 Latch（锁存器）：可作为内存性能的指标，说明内存需要调整。
 Free List：会导致繁忙表上的DML操作性能很差。
 Lock：会遇到彻底的暂停，产生巨大的性能影响。

2.1优化维护
维护时，主要通过检查，判断存在的latch和free list争用是否合理，不合理，则启动相应的优化工作。
而lock，在遇到问题的时候，可作为维护参考，平时不进行太多的维护（或从应用上考虑优化）。（除了定期去$ORACLE_HOME/admin/$ORACLE_SID/udump查看死锁情况外）

2.2诊断latch竞争
2.2.1概念
Latch是简单的、低层次的序列化技术，用以保护SGA中的共享数据结构，比如并发用户列表和buffer cache里的blocks信息。一个服务器进程或后台进程在开始操作或寻找一个共享数据结构之前必须获得对应的latch，在完成以后释放latch。不必对latch本身进行优化，如果latch存在竞争，表明SGA的一部分正在经历不正常的资源使用。

1）Latch的作用：
A、序列化访问：保护SGA中的共享数据结构；保护共享内存的分配。
B、序列化执行：避免同时执行某些关键代码；避免互相干扰。

2）Latch请求的两种类型：
A、willing-to-wait：请求的进程经过短时间的等待后再次发出请求，直到获得latch
B、immediate：如果没有获得latch，请求的进程不等待，而是继续处理其他指令。

2.2.2是否存在latch争用
select event,total_waits,time_waited
from v$system_event
where event = ‘latch free’;
如：
EVENT TOTAL_WAITS TIME_WAITED
---------- ----------- -----------
latch free 4320663 779167
自实例启动以来，发生过4320663个latch争用，等候这些latch所 花费的时间为779167毫秒。

2.2.3 检查Latch是否主要竞争
检查latch free是不是主要的wait event：
Select event,total_waits,time_waited from v$system_event order by time_waited desc;
2.2.4 DBA关注的latch内容
DBA需要关注的latch有：
Select name,gets,misses,sleeps,wait_time,spin_gets,immediate_gets,immediate_misses from v$latch
where name like ‘%shared pool%’
or name like ‘%library cache’
or name like ‘%cache buffers lru chain%’
or name like ‘%cache buffers chains%’
or name like ‘%redo allocation%’
or name like ‘%redo copy%’;

与willing-to-wait请求有关的列：
Select name,gets,misses,sleeps,wait_time,spin_gets from v$latch;
与immediate请求有关的列：
Select name,immediate_gets,immediate_misses from v$latch
Gets: number of successful willing-to-wait requests for a latch;
Misses: number of times an initial wiling-to-wait request was unsuccessful;
Sleeps: number of times a process waited after an initial willing-to-wait request;
Wait_time: number of milliseconds waited after willing-to-wait request;
Spin_gets: gets that misses first try but succeed after spinning.
Immediate_gets: number of successful immediate requests for each latch;
Immediate_misss: number of unsuccessful immediate requests for each latch;

 shared pool：需要优化shared pool。（在没有配置large pool情况下使用shared server选件，会导致很高的shared pool latch.）
 library cache：需要优化library cache。
 cache buffers LRU chain：管理database cache buffers上的LRU List上的块，自由缓冲区列表。此争用有两种可能：
- 导致严重的全部扫描的SQL语句或执行计划。SQL需要优化。
- 脏缓冲区写盘database writer未能跟上I/O请求。优化磁盘I/O。
 cache buffers chains：预示某些某些缓冲块被重复搜索，块大小比较大的比块大小小的更容易遇见此latch。优化块大小与I/O关系。
 redo allocation：许多用户同时放入redo log buffer，则产生该latch。优化redo log buffer。
 redo copy:用户server process复制信息到redo log buffer时发生。优化redo log buffer。

一般无需调整latch(实际上在9i中，init.ora已经废弃了所有的latch调整参数)，但是下列的措施是有用的（根据v$latch作为调整其他性能指标的指示器，而不是调整latch本身）：
A、对处于竞争中的latch做进一步的调查
B、如果竞争主要存在于shared pool和library cache中，可以考虑调整应用
C、如果进一步的调查显示需要调整shared pool和buffer cache，就进行调整

2.3诊断free list竞争
（使用management local的表空间不用调整，对表的insert和delete,update操作有重要指导意义）
判断表空间是否management local：
select tablespace_name,contents,extent_management,allocation_type,segment_space_management
from dba_tablespaces;

2.3.1概念
每个段保持一个free list来包含这个段中能接受新行的块。
比如如果应用程序有许多频繁插入行的用户，在试图访问该表的free list就可能会经历等待。

2.3.2是否存在free list争用
select event,total_waits,time_waited from v$system_event
where event = ‘buffer busy waits’;
自数据库启动来，可能发生的free list 争用。

还应经过以下查询确定：
select class,count,time from v$waitstat
where class in
(‘free list’,’segment header’);
count:访问一个块的等待次数。
Time：等待访问块所花费的总时间。

例：
CLASS COUNT TIME
------------------ ---------- ----------
segment header 1869 1725
free list 0 0

说明：针对段头部的free list相关等待发生了1896次，针对自由列表发生的free list为0次。

2.3.3确定free list 争用的段
select s.segment_name,s.segment_type,s.freelists
from dba_segments s,v$session_wait w
where w.p1 = s.header_file
and w.p2 = s.header_block
and w.event = 'buffer busy waits';
（确定当前的争用）

2.3.4优化free list争用
1） 增加附加的free list
默认创建只有一个free list，若要增加更多的free list，可以使用
alter table hr.employee storage (freelists 5);
进行修改。
2） 把段转移到自动段管理表空间上（Oracle 9i新特性）
management local
使用自动段管理表空间，将不再利用free list管理块，而是利用表空间数据文件头的位图来管理表空间中每个段的自由块分配（包括新建的任何附加段），此时该段在dba_segments视图中的freelists的值将是NULL空值。
（create一个management local的表空间，使用
alter table hr.employee move tablespace app1_data;
的方法把表从一个表空间移动到另外一个management local的表空间）
(表里有long字段，得用其他方法移动)

2.4诊断lock竞争
2.4.1概念
lock 用来保护对数据的访问的一致性，latch用来保护对内存的访问（latch从不在process之间共享）。
Lock通常发生在许多用户正在少量的表上执行ＤＭＬ操作的时候发生。一旦lock申请获得，lock会一直保留给该用户，直到lock的用户发布一条commit或rollback为止。
使用Oracle默认加锁机制，可以：
１） 修改同一个表的不同行，不用担心锁问题。
２） 修改同一个表的同一行，由等待队列根据system change number(SCN)决定谁的修改结果是最终修改结果。（默认队列最多数从init.ora的session获得，也可修改enqueue_resources手工调整）

如果需要严格的锁机制，可把init.ora的row_locking从always（row lock）修改为intent的（table lock）。

一共有两类锁“DML数据锁（Data lock）”和“DDL目录锁（Dictionary lock）”，有两种模式使用锁：“独占型（Exclusive lock）”和“共享型（Share lock）”模式，实现数据的并发性和一致性。

v$lock：报告锁模式等情况。
v$locked_object:报告被锁的对象信息。
dba_waiters:报告阻塞的具体情况，有锁模式，wait session和hold　session。
dba_blockers:报告霸占资源导致别的session阻塞的sid.（如果没有，则执行$ORALCE_HOME/rdbms/admin/catblock.sql和utllockt.sql脚本创建）

DML事务使用row-level locks,查询不会锁定数据。锁有两种模式：exlusive、share。
锁的类型：
• DML or data locks:
– Table-level locks（TM的数量一般有init.ora的transactions获，但可修改dml_locks做调整）
– Row-level locks（TX）
• DDL or dictionary locks
一个transaction至少获得两个锁：一个共享的表锁，一个专有的行锁。Orace server将所有的锁维护在一个队列里，队列跟踪了等待锁的用户、申请锁的类型以及用户的顺序信息。
Lock在下列情况会释放：commit；rollback；terminated（此时由pmon清理locks）。

Quiesced database：一个数据库如果除了sys和system之外没有其他活动session，这个数据库即处于quiesced状态。活动session是指这个session当前处于一个transaction中，或一个查询中，一个fetch中，或正占有某种共享资源。

2.4.2可能引起lock contention的原因
不必要的高层次的锁；
长时间运行的transaction；
未提交的修改；
其他产品施加的高层次的锁。

2.4.3锁解决办法
如果出现了阻塞，则可通过下列方法之一解决这种争用：
１） 修改应用程序，使之不要使用严格的锁和不要有太长的事务（可设逻辑提交点）。（更不要使用显示锁select .. for update,lock table .. in exclusive mode;）
２） 查出锁住对象，制造阻塞的session，通知用户commit或rollback。
可使用profile的方式，声明断开已空闲一段时间的用户。
３） 使用alter system kill session ‘sid,$serial’等方法杀死引起阻塞的session（如果该session没有在活动不会收到被kill的信息，直到发出新操作才得到kill提示）。

2.4.4死锁
Oracle自动检测和解决死锁，方法是通过回滚引起死锁的语句（statement），但是这条语句对应的transaction并没有回滚，因此当收到死锁的错误信息后，应该去回滚改transaction的剩余部分。
发生死锁后，会自动在$ORACLE_BASE/admin/$ORACLE_SID/udump目录下生成一个详细描述死锁的跟踪文件。
(Ｏracle会有租塞，但不会有死锁，因发生死锁后，导致发生死锁的操作会取消并报告“ORA-00060: 等待资源时检测到死锁”错误，但可能会继续出现阻塞（因无人commit或rollback）。)

- 作者： lalagu 2007年02月28日, 星期三 20:51　 回复（0） |　 引用（1） 加入博采

Oracle 9i 初始配置:数据库服务器数据库参数配置说明

Oracle 9i 初始配置:数据库服务器数据库参数配置说明

编写时间 2005年8月16日

ORACLE 9I 初始配置 1
1 概述 2
1.1 参数修改注意事项 2
1.2 各省数据库服务器内存及CPU统计 2
1.3 参数修改方法 2
2 操作系统参数配置 3
3 数据库参数配置 3
3.1 SGA配置 3
3.1.1 Shared pool 3
3.1.2 Data buffer 4
3.1.3 Sga other memory 5
3.2 磁盘I/O配置 5

1 概述
1.1参数修改注意事项
1）操作系统参数，需要重启数据库服务器才生效。
2）数据库静态参数，需要重启数据库才生效，动态参数，可不重启数据库立即生效。
3）必须先修改操作系统参数，才能修改数据库参数，否则可能修改后数据库无法启动。

1.2各省数据库服务器内存及CPU统计
数据库服务器 内存大小/M CPU
数据库001 8192 4* 1200
数据库002 12288 6* 1200

数据库003 8192 4* 1200
数据库004 8192 4* 1050

数据库005 16384 8* 1200
数据库006 16384 8* 1200

数据库007 12288 6* 1200

每个数据服务器仅运行一个OLTP 数据库。

1.3参数修改方法
1）操作系统参数修改
使用vi 修改/etc/system文件，保存后重新启动服务器，使用如下命令验证修改成功：
%sysdef | grep SHMMAX

2）数据库参数修改
-使用spfile。（从Oracle 9i后默认使用spfile）
使用命令修改，如：
-1- SQL> alter system set shared_pool_size=300M scope=both;
-2-修改pfile，从pfile创建spfile；

-使用pfile。
直接使用vi的方式修改配置文件。

使用命令SQL>show parameters 参数名
的方法验证

2 操作系统参数配置
/etc/system文件中，shminfo_shmmax参数，配置为数据库服务器物理内存的大小减1.5G。

推荐配置：
数据库001 数据库002 数据库003 数据库004 数据库005 数据库006 数据库007
shminfo_shmmax 6979321856 11274289152 6979321856 6979321856 15569256448 15569256448 11274289152

3 数据库参数配置
3.1SGA配置
静态参数sga_max_size，Oracle最大可使用的内存SGA总和。

推荐配置：
数据库001 数据库002 数据库003 数据库004 数据库005 数据库006 数据库007
sga_max_size 6979321856 11274289152 6979321856 6979321856 15569256448 15569256448 11274289152

3.1.1Shared pool
动态参数shared_pool_size=800M
静态参数SHARED_POOL_RESERVED_SIZE=300M
动态参数OPEN_CURSORS=600
静态参数CURSOR_SPACE_FOR_TIME=TRUE
静态参数SESSION_CACHED_CURSORS=100
动态参数CURSOR_SHARING= SIMILAR

推荐配置：
数据库001 数据库002 数据库003 数据库004 数据库005 数据库006 数据库007
shared_pool_size 800M 800M 800M 800M 800M 800M 800M
SHARED_POOL_RESERVED_SIZE 300M 300M 300M 300M 300M 300M 300M
OPEN_CURSORS 400 500 400 400 600 600 500
CURSOR_SPACE_FOR_TIME TRUE TRUE TRUE TRUE TRUE TRUE TRUE
SESSION_CACHED_CURSORS 60 80 60 60 100 100 80
CURSOR_SHARING SIMILAR SIMILAR SIMILAR SIMILAR SIMILAR SIMILAR SIMILAR

SHARED_POOL_RESERVED_SIZE
设置shared pool的某一部分专供大程序包和触发器使用。避免把大包装入shared pool时，把其他若干条SQL解析版本被LRU从内存中移走，降低命中率。
默认为shared_pool_size的5%，最大可到50%。Oracle 建议从10%开始设置起。

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分析代码。

3.1.2Data buffer
动态参数db_cache_advice=READY
动态参数db_cache_size=(物理内存/G-nG)*1024
动态参数Db_keep_cache_size=500M
动态参数db_recycle_cache_size= 2048M

（8G-4606,12G-8702,16G-12798）

推荐配置：
数据库001 数据库002 数据库003 数据库004 数据库005 数据库006 数据库007
db_cache_advice READY READY READY READY READY READY READY
db_cache_size 2046M 4094M 2046M 2046M 7678M 7678M 4094M
Db_keep_cache_size 1024M 3072M 1024M 1024M 3072M 3072M 3072M
db_recycle_cache_size 1536M 1536M 1536M 1536M 2048M 2048M 1536M

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

3.1.3Sga other memory

推荐配置：
数据库001 数据库002 数据库003 数据库004 数据库005 数据库006 数据库007
large_pool_size 50M 5M 50M 50M 50M 50M 50M
java_pool_size 100M 100M 100M 100M 100M 100M 100M
log_buffer 3M 3M 3M 3M 3M 3M 3M

动态参数Large pool:
缓存以下对象有关数据：
1）Database Writer 的I/O辅助进程。
2）Recovery Manager的备份与恢复操作（RMAN）。使用RMAN的地方注意了！
3）Shared Server 会话数据（MTS环境）。
4）Parallel Query选件的消息传送信息。

静态参数Java_pool_size：
与JAVA相关的其他全部会话的数据的缓存。默认20M，范围：1M-1GB。专门用做与会话相关的JAVA代码和应用程序变量在程序运行期间所驻留的场所。配置java pool对JAVA应用来说，至关重要。

3.2磁盘I/O配置

推荐配置：
数据库001 数据库002 数据库003 数据库004 数据库005 数据库006 数据库007
sql_trace FALSE FALSE FALSE FALSE FALSE FALSE FALSE
timed_statistics true true true true true true true
db_file_multiblock_read_count 16 16 16 16 16 16 16
dbwr_io_slaves 0 0 0 0 0 0 0
db_writer_processes 3 3 3 3 3 3 3
sort_area_size 3M 3M 3M 3M 3M 3M 3M
sort_area_retained_size 0.5M 0.5M 0.5M 0.5M 0.5M 0.5M 0.5M
pga_aggregate_target 800M 800M 800M 800M 800M 800M 800M
workarea_size_policy AUTO AUTO AUTO AUTO AUTO AUTO AUTO
undo_management AUTO AUTO AUTO AUTO AUTO AUTO AUTO
undo_retention 7200 7200 7200 7200 7200 7200 7200

静态参数sql_trace=FALSE
动态参数timed_statistics=true
动态参数db_file_multiblock_read_count=16
静态参数dbwr_io_slaves=0
静态参数db_writer_processes=3
静态参数sort_area_size=3M
静态参数sort_area_retained_size=0.5M
动态参数pga_aggregate_target =800M
动态参数workarea_size_policy= AUTO
静态参数undo_management= AUTO
动态参数undo_retention = 7200

SORT_AREA_SIZE
指定每个用户应留出多少内存空间用于排序。
SORT_AREA_RETAINED_SIZE
指定：排序完成后，如果排序区还含有需要返回给用户的排序行，则用该参数指定留给这些行的内存容量。
PGA_AGGREGRATE_TARGET
在使用SORT_AREA_SIZE指定了排序内存后，就可以使用该参数指定所有用户一共最多可以使用的排序内存总大小。
WORKAREA_SIZE_POLICY
指定排序内存是明确管理还是隐含管理。
＝　AUTO
Oracle自动管理排序区内存分配，只确保不超过PGA_AGGREGRATE_TARGET大小。
＝　MANUAL　
每个用户的排序区大小等效于SORT_AREA_SIZE大小。
undo_management
=auto //使用undo 自动管理（AUM）
=manual //不使用AUM。
undo_retention
单位是秒．指定一个像前版本在commit后被保存的时间．（减少ORA-01555错误）

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

转载于:http://blog.itpub.net/24032200/viewspace-673994/