oracle9i阻塞,Oracle 9i 整体性能优化概述(zt)

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至少获得两个锁：一个共享的表锁，一个专有的行锁。Oracle 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)。)