Oracle Wait Events And Tuning

oracle等待事件1——高速缓冲内cbc latch

http://blog.csdn.net/changyanmanman/article/details/7972654

分类: oracle管理总结 2012-09-08 17:13 371人阅读 评论(0) 收藏 举报
oraclecachebuffer
恩。。从今天起,木木同学要认真整理一下oracle中常见的等待事件,通过这部分的学习,希望自己能对oracle内部的结构能有一个更清晰的认识,有兴趣的童鞋一起来哇。。。

1、latch:cache buffers chains
从oracle 9i开始,以只读为目的的查询chains时,可以将cache buffers chains锁存器以shared模式共享,因此有助于减少争用。
(我们需要注意,若能共享cache buffer chains 锁存器,理论上理论上不应该发生同时执行select 操作引起cbc锁存器的争用,但实际的测试结果表明,同时执行select依然会发生cbc锁存器争用,其理由是与buffer lock相关:为了读取工作,以shared模式已经获得锁存器,但是读取实际缓冲区过程中,还要以shared 模式获取buffer lock,在此过程真呢过需要部分修改缓冲区头信息。因此在获取buffer lock过程中,需要将cbc锁存器修改为exclusive 模式,在释放buffer lock期间也需要exclusive模式获取cbc锁存器,在此过程中会发生争用。)

发生cache buffers chains 锁存器争用代表性的情况如下:低效的SQL 和 hot block(热块)

低效SQL引起的cbc争用
先介绍视图:v$latch_children
数据库中有些类别的latches拥有多个。v$latch中提供了每个类别的总计信息。如果想查看单个latch,可以通过查询本视图:
查询数据库中所有latch的名字和个数:SQL> select name,count(*) ct from v$latch_children group by name order by ct desc;

NAME CT
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
cache buffers chains 1024
SQL memory manager workarea list latch 67
channel operations parent latch 65
global tx hash mapping 47
message pool operations parent latch 34
name-service namespace bucket 32
simulator hash latch 32
row cache objects 29
redo allocation 20
In memory undo latch 18
checkpoint queue latch 16

NAME CT
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
msg queue 15
JS queue access latch 13
commit callback allocation 11
transaction allocation 11
buffer pool 8
cursor bind value capture 8
simulator lru latch 8
object queue header operation 8
object queue header heap 8
cache buffers lru chain 8
business card 8

NAME CT
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
shared pool 7
flashback copy 6
virtual circuit queues 6
post/wait queue 5
slave class 5
JS slv state obj latch 4
redo copy 4
session switching 4
parallel query alloc buffer 4
job workq parent latch 3
undo global data 3

NAME CT
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
library cache pin allocation 3
library cache pin 3
library cache hash chains 3
peplm 3
library cache lock 3
library cache lock allocation 3
library cache 3
Shared B-Tree 2
session idle bit 2
parallel query stats 2
longop free list parent 2

NAME CT
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
latch wait list 2
ksfv messages 2
enqueue hash chains 2
client/application info 2
channel handle pool latch 1
granule operation 1
logminer context allocation 1
session queue latch 1
sim partition latch 1
msg queue latch 1
done queue latch 1

已选择55行。

如此说来,oracle10g中有55个有名字的latch,拥有量最大的就是我们的cbc latch,正好1024个。

我通过构建测试环境,创建了表,加上索引。并且创建一个全表扫描的过程:
SQL> create table cbc_test(id number, name char(100));
SQL> insert into cbc_test(id,name) select object_id, object_name from dba_objects;
SQL> cretate index cbc_test_idx on cbc_test(id);

好了,下面进行不必要的广泛扫描索引:创建一个过程:
create or replace procedure cbc_do_select is
begin
for x in(select /*+index(cbc_test cbc_test_idx)*/ *
from cbc_test where id>=0) loop
null;
end loop;
end;

反复执行此过程2000次:
var job_no number;
begin
for idx in 1..2000 loop
dbms_job.submit(:job_no,'cbc_do_select;');
commit;
end loop;
end;

查看一下cbc 锁存器对应的CHILD#,GETS, SLEEPS判断子锁存器上使用的次数和争用是否集中:

select * from
2 (select child#,name,gets,sleeps from v$latch_children
3 where name='cache buffers chains'
4 order by sleeps desc
5 )where rownum<=20;

CHILD# NAME GETS SLEEPS
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
837 cache buffers chains 34466 28
67 cache buffers chains 23994 15
684 cache buffers chains 6288 14
238 cache buffers chains 3823 12
898 cache buffers chains 4868 12
908 cache buffers chains 32807 10
288 cache buffers chains 3956 8
737 cache buffers chains 3865 8
412 cache buffers chains 1671 8
968 cache buffers chains 2706 7
420 cache buffers chains 2998 6

CHILD# NAME GETS SLEEPS
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
460 cache buffers chains 3912 6
1 cache buffers chains 2564 4
839 cache buffers chains 1119 4
951 cache buffers chains 21741 4
33 cache buffers chains 3786 3
251 cache buffers chains 1997 2
578 cache buffers chains 3857 2
733 cache buffers chains 3554 2
280 cache buffers chains 4549 2

已选择20行。

看来837号cbc 子锁存器争用比较多,可以判定是比较热的块。

我们也可以通过v$latch_children视图,确定热块的cbc 锁存器的地址:
SQL> select * from
2 (select latch#,child#,addr,gets,sleeps from v$latch_children
3 where name='cache buffers chains'
4 order by sleeps desc
5 )where rownum<=20;

LATCH# CHILD# ADDR GETS SLEEPS
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
116 894 6CB7F7F0 554344 90
116 837 6CFFF70C 727379 80
116 951 6CB839D8 471117 74
116 56 6C783224 107910 60
116 341 6C797BAC 144056 47
116 458 6C7A02F4 37434 38
116 240 6C7906E4 99251 37
116 297 6C7948CC 98903 36
116 566 6C7A7FD4 94932 36
116 790 6CFFC0B4 107997 36
116 280 6C793524 87455 34

LATCH# CHILD# ADDR GETS SLEEPS
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
116 919 6CB814D8 49667 31
116 583 6C7A937C 85883 29
116 113 6C78740C 120322 28
116 627 6C7AC65C 91840 28
116 534 6C7A5AD4 112523 27
116 270 6C792994 50302 26
116 418 6C79D4B4 37697 26
116 680 6C7B03A4 104550 26
116 676 6C7AFF04 103297 26

已选择20行。
通过上面红色的锁存器地址,结合x$bh 视图,查看tch的次数,确定热快:

SQL> select hladdr,obj,(select object_name from dba_objects
2 where (data_object_id is null and object_id=x.obj)
3 or data_object_id=x.obj
4 and rownum=1 )as object_name,dbarfil,dbablk,tch
5 from x$bh x
6 where hladdr in('6CB7F7F0','6CFFF70C')
7 order by hladdr,obj;

HLADDR OBJ OBJECT_NAME DBARFIL DBABLK TCH
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
6CB7F7F0 18 OBJ$ 1 47810 0
6CB7F7F0 109 I_SYSAUTH1 1 827 15
6CB7F7F0 57855 CBC_TEST 1 58171 0
6CB7F7F0 57855 CBC_TEST 1 58655 0
6CFFF70C 2 ICOL$ 1 42272 17
6CFFF70C 18 OBJ$ 1 47811 0
6CFFF70C 109 I_SYSAUTH1 1 828 15
6CFFF70C 57855 CBC_TEST 1 58172 0
6CFFF70C 57855 CBC_TEST 1 58656 0

但是很不幸,我这里也没有出现热块的迹象,因为tch竟然都是0.我也不知咋回事。。

oracle等待事件2——高速缓冲内等待事件

分类: oracle管理总结 2012-09-08 21:44 356人阅读 评论(0) 收藏 举报
oraclecache工作
目录(?)[+]
1、cache buffers lru chain
要想查看或修改工作组(LRU+LRUW)进程,始终要持有管理相应工作组的cache buffers lru chain 锁存器,若在此过程中发生争用,则要等待:latch:cache buffers lru chain 事件。
在下面两类情况下我们必须首先获得cache buffers lru chain锁存器:
(1)进程想要读取还没有装载到内存上的块时,通过查询LRU列分配到所需空闲缓冲区,再次过程中需要cache buffers lru chain 锁存器。
(2)DBWR 为了将脏缓冲区记录到文件上,查询LRUW列,将相应缓冲区移动到LRU列的过程中也要获得cache buffers lru chain锁存器。

我们接下来想一下,在什么情况下DBWR将脏缓冲区记录到文件里。。。。。。。。。。啊哈,大致上列出如下几种情况:
1)oracle进程为了获得空闲缓冲区,向dbwr请求记录脏缓冲区时;
2)oracle进程为执行praallel query 或 tablespace backup, truncate/drop等工作,请求记录相关对象的脏缓冲区时;
3)由于周期性或管理上的原因检查点(checkpoint)被执行时。

大致就是上面这些了,下面我们再回头考虑一下究竟是什么引起cache buffers lru chain锁存器争用呢?其实就是过多的队空闲缓冲区的请求,还有过多的记录脏缓冲区操作。。也就是上面的两种情况。
我们必须理解cbc latch 和 cblc latch争用的差别,或者说,到底是什么情况下会引起cbc latch 争用,什么情况下会引起cblc latch的争用。。简单的理解:若是多个的会话同时访问不同的表,或者索引,那么就要大量的从磁盘上读写数据,伴随着buffer cache 中内存块的分配使用,cblc latch争用的概率会大大增加。若是多个进程同时访问一个表,这时会在内存中争用cbc latch。因为要读取或者操作一行数据,首先要获得这个块的cbc latch。。
一般情况下,cbc latch 和 cblc latch争用同时发生的情况较多,因为复杂的应用程序将符合地应用上述模式。通过我们分析,高速缓冲区过小,或者检查点周期过短。。都会引起cache buffers lru chain锁存器的争用。。。

2、buffer busy waits/read by other session
(参照博客:http://blog.csdn.net/changyanmanman/article/details/7968537
若是两个进程同时修改一个表的两个行,恰好这两个行位于同一个数据块内,即使某个用户已经以exclusive模式获得TX锁,但是也需要保障当前只有自己在修改块。此时需要获得的锁就称为buffer lock,与其他锁相同,需要一直等到获得锁。buffer lock只有两种模式exclusive 和 shared 模式。
buffer lock 与 cache buffers chains 存储器,TX锁一起对缓冲区的修改该起到同步化的作用。在抽象层次上,为了修改一个行,获得锁存器或者锁的过程如下:
1)为了查找要修改的行相对应的块存在的位置(通过hash chain) 请求cache buffers chains 锁存器。
2)对于载入块的缓冲区请求buffer lock ,并释放cache buffer chains 锁存器。。获得buffer lock。
3)请求TX锁,修改相应的行。若果没有获得TX锁,则释放buffer lock并进入等待状态。之后重复过程1.
4)修改相应(欲修改的)行后,释放buffer lock。
为了获得buffer lock 而等待期间内,一般会等待buffer busy waits事件,buffer busy waits等待是最普通的等待现象中的一个,其原因很多,在10g中,为获得buffer lock的时间总共有4个:buffer busy wait 、 buffer busy global cache /CR(gc buffer busy) 、write comlete waits、read by other session。。

下面我们通过介绍四种情况下的操作,来了解buffer busy wait等待:
1、select/select引起的read by other session:
buffer lock 只有shared 和exclusive模式,读取位于SGA上的块时以shared模式获得buffer lock,所以此时不会发生读取工作时引起的buffer lock争用,但是,问题是,如果多个进程同时第一次读取某个块,而这个块又不在SGA中,这样,第一个进程会以exclusive 模式获得buffer lock 然后去磁盘上读取块数据。这时如果有其他进程来访问相同的块,必须以shared模式获得这个buffer lock。显然这个时候就会发生buffer lock的争用。
需要注意,发生read by other session的等待同时,会发生db file sequential read、db file scattered read 等待等I/O等现象。read by other session 等待在其属性上一直与物理I/O同时出现。
综上:若要减少select /select引起的read by other session等待的方法,整理结果如下:
*通过对sql的优化,以便能以最少的I/O获得所需的结果
*若SGA(或高速缓冲区)大小比系统全局I/O小,就需要增加其大小。

2、select/update 引起的buffer busy wait/read by other session:
select/update引起的buffer lock争用与select/select或者update/update引起的buffer lock争用机制有很大差异。
oracle的select 做出以一致性读取为基础。若实际读取的数据已经修改,则必须读取持有过去映像的CR块,这时,若CR块不在当前的告诉缓冲区上时,应该从磁盘读取撤销块。若多个会话试图读取撤销块时,在将撤销块载入到内存上的过程中发生buffer lock 争用。因此select/update引起的buffer lock争用会在如下情况下发生。

*特定进程修改该特定表,数据的过去映像记录在撤销块。
*很多进程试图同时(或之后)读取已修改的数据。

KTU:指与回滚段的内部管理(internal management of undo and rollback segments)相关的内核区。
SMU:system management undo 简称。
AUM:aotomatic undo management 的回滚段。
撤销块头上的buffer lock争用,是update会话的撤销头块修改操作和select会话引起的撤销头块读取操作之间的buffer lock争用引发的,这是因为修改头块的进程,要以exclusive模式获取buffer lock;读取头块的进程,则需要以shared模式获取buffer lock。。
撤销块上的buffer lock争用就是我们之前所描述的,创建CR块,多个会话同时读取cr块,这时,创建cr块过程是exclusive模式获得buffer lock的,所以这就引起了争用。

3、insert/insert 引起的buffer busy waits:
多个会话同时对同一个表执行Insert操作时,段的区域将急速扩大,因此引发多种性能问题,buffer lock 争用引起的buffer busy waits等待现象就是其中之一。
在ASSM(自动段空间管理)中,我们进行测试buffer lock 争用,通过v$session_wait视图,可以发现P3(class)值为8的占较大比重。偶尔也能看到9.块类8、9分别对应的是L1 BMB(level 1 bitmap block) L2 BMB(level2 bitmap block) 使用ASSM时,比起数据块争用,主要还是用于段空间管理的位图块发生 buffer lock争用,特别是L1 BMB作为空间管理的最下层leaf block 随着块状态的改变发生许多变化,因此L1 BMB 主要多发buffer busy waits 等待。在使用ASSM时,主要发生对于位图块的buffer busy waits等待。即便是使用assm,依然存在对于buffer lock 的争用,但是整体上buffer busy waits 等待将减少,特别是hw (高水位线)锁争用大幅减少。

4、update/update引起的buffer busy waits:
多个会话同时update相同的行的时候,通过TX锁形式同步。但同时多个会话对不同的行执行update时,若该行位于同一个块内,则需要通过buffer lock 进行同步。在此过程中,若发生争用,则等待buffer busy waits事件,这时发生的buffer lock 引起的争用与TX锁引起的争用性质上截然不同,因此,说明其现象时必须注意。若发生TX锁争用时,结束所持有者的事务是唯一的解决方法。但是在修改该相同块的过程中发生的buffer lock争用的解决方法却截然不同。

3、write complete waits
write complete watis 等待与buffer busy waits 等待相同,可以通过buffer lock争用引起的等待进行分类。DBWR将脏缓冲区记录到磁盘上的期间,对缓冲区以exclusive模式占有buffer lock,这时,读取或修改缓冲区的其他进程就需要等待此项工作结束,这时等待write complete waits事件。
查看v$BH视图的GLAG列,可知道当前缓冲区的准确状态。下面贴出9i中flag与状态的关系
参考此表可知想x$BH.FLAG值5时,正是利用DBWR记录到缓冲区上的状态。出现write complete waits 等待的主要原因就是DBWR进程的性能问题,也就是会所dbwr将脏缓冲区的数据写入磁盘的时间过长。其原因大致分为如下两类:
1)I/O系统性能缓慢:如果在DBWR上显示的db file parallel write等待时间较长,可以判断存在i/o系统问题。在dbwr上,db file parallel write等待时间变长,则服务器进程连续经历free buffer waits等待或者write complete waits等待。我们知道,组合使用裸设备和AIO(asynchronous IO 异步io) 是改善io性能的最好方法。使用aio(异步io)时,一般认为增加dbwr数量没有意义。因为使用多个dbwr的最红目的是以软件的方式实现aio。但若是写入繁忙的系统,可以同时使用aio和多个dbwr。
2)dbwr的工作量过多:一般情况 过小的FAST_START_MTTR_TARGET值,频繁的增量检查点,重做日志文件过小(频繁日志切换,导致检查点频繁发生),parallel query 引发direct path read时,还有truncate ,drop, hot backup时发生检查点。。。虽然这些时候io性能没有问题,但是还是因为大量的dbwr工作,还会出现write complete waits。

4、free buffer waits
服务器进程将块载入到内存的过程如下:
1)在用户请求的dba上应用hash函数,获得hashbucket。
2)检索伴随hash bucket的hash chain,确认块所对应的缓冲区头(BH)是否存在,若缓冲区头已经存在,相应块已经位于高速缓冲区的状态,则使用该块。
3)若高速缓冲区不存在,首先在lru列按最少使用的顺序寻找空闲缓冲区。在此过程中发现脏缓冲区,则将脏缓冲区移动到lruw列。找到空闲缓冲区后,可以从数据文件将块读取到该缓冲区上。
4)在lru列上寻找空闲缓冲区时,扫描与_DB_BLOCK_SCAN_MAX_PCT(缺省值40)参数值相同次数后,若仍然没有找到缓冲区,oracle将停止扫描,并且向dbwr请求脏块写入到磁盘,以得到空闲缓冲区。写入完成后使用该块。

在第四个步骤,若检索LRU列的一定区域后,也没有找到空闲缓冲区,就会向dbwr发出写入请求。直到写入工作结束为止,一直要等待 free buffer waits 事件,这样我们也就能区分write complete waits等待的区别,(wcw等待是dbwr写入对缓冲区的竞争,而这个是fbw等待是等待获得)

发生free bufferwaits等待的理由如下:
*低效的sql:请求很多空闲缓冲区
*过小的告诉缓冲区:很少的空闲缓冲区请求也会引起fbw
***dbwr的性能下降:其实前两条的的原因有一部分(或者说根本原因)就是这个dbwr不给力,如果他给力,什么都好解决了。。

5、Local write waits 等待说明
在AWR 看到local write waits和 enq: RO - fast object reuse 的 等待事件。

网上对local write waits 的说明:

Note 1:
Typically DBWRhas to free up some buffers when you want to read something from the disk.During this process there are chances that you will be waiting for your localbuffer (i.e blocks dirtied/invalidated by your session) to be written to disk.During this time the waits are shown as local write waits.

Note 2:
Basically 'localwrite' wait happens (as the name indicates) when the session is waiting for itslocal (means writes pending because of its own operation) writeoperation. This could happen typically if the underlying disc has some seriousproblems (one of the member disk crash in RAID-05 - for example, or acontroller failure). That is why I might have said ' you never see this wait inthe normal databases!'. You may see thisduring (rarely) Truncating a large table while most of the buffers of that table in cache. During TRUNCATEs the session has to a local checkpoint and during this process, the session may wait for 'local write' wait.

基本上'local write' wait 表示会话在等待自己的写操作。在磁盘发生严重问题时会发生(例如RAID 5的一个磁盘崩溃,或者磁盘控制器错误),这在正常的系统中极少发生,在TRUNCATE 一个大表而这个表在缓存中的时候,会话必需进行一个localcheckpoint,这个时候会话会等待localsession wait.

在MOS 的文档:
Truncates Taking Too Long… [ID 334822.1]
提到了这个等待事件。

Cause:
Processes that involve temporary tables being truncated and repopulated(使重新住入) in multiple,concurrent batch streams may present this situation.
The underlying(最根本的) problem is we have to write the object's dirty buffers to disk prior to (优先于) actually truncating or dropping the object. This ensures instance recoverability and avoids a stuck(被卡住的) recovery. It seems at first glance perfectly reasonable to simply truncate a temporary table, then repopulate for another usage. And then to do the temporary poplulate/truncate operationsin concurrent batches to increase throughput.

However, inreality(实际上) the concurrent truncates get bogged down(陷入困境) as dbwr gets busy flushing those dirty block buffers from the buffer cache. You will see huge CI enqueue waits.The multiple truncate operations in concurrent streams absolutely kill throughput.This is specially critical with large buffers.

There was also adisscussion in Bug: 4147840 (non-publish) where a peoplesoft process wascausing this behavior because of the above explanation and they seemed to fixit by changing some peoplesoft code to implement delete rather than truncate onsamll temporary tables.

Solution:
In 9.2.0.5 and higher, it may also help to make sure a "temp" table that is frequently truncated have storage defined so that it occupies one extent.But this workaround is only available as long as the extent is no morethan 50% the size of the buffer cache. In non-RAC environments the tablestill has to be smaller than 50% of the buffer cache, but it allows thetable to have up to 5 extents before falling back to the old algorithm.

oracle等待事件3——高速缓冲内enq锁

分类: oracle管理总结 2012-09-11 16:28 618人阅读 评论(0) 收藏 举报
oracleparallelobjectpatchpathcache
目录(?)[+]
6、 enq:TC-contention
在手动执行检查点操作中,一部分需要获得TC锁(thread checkpointlock 或 tablespace checkpointlock )在获得TC锁过程中,若发生争用,则需要等待enq:TC-contention 事件。事实上获得TC锁的过程稍微复杂。
1) 服务器进程首先以X模式获得TC锁
2) 服务器进程将已获得的TC锁变更为SSX模式。同时,CKPT进程以SS模式获得该锁。CKPT获得锁后执行检查点操作。
3) 服务器欲重新以X模式获得TC锁,等待CKPT释放该锁,这时的等待事件就是enqueue:TC-contention
4) 检查点工作结束后,CKPT进程将会释放TC锁,服务器进程就会获得TC锁,因此得知检查点工作已经结束。

Enq:TC-contention 等待即便在没有多个进程引起争用的情况下,也可以发生,在这一点上与其他锁争用引起的等待现象不同。需要理解的是在等待现象中,存在只有争用才能引发的等待现象,但是也存在不发生争用,也会单纯为了等待工作结束而等待的情况。。
发生检查点的情况虽然很多,但不是所有的情况都会发生TC锁引起的等待,之后再进程由服务器进程引发的检查点同步过程中发生。
enq:TC-contention 等待发生的代表案例如下:并行查询 和 表空间热备
1:、并行查询(parallel query)
pq发生检查点的原因是slave session引起的direct path read。这就是所谓的“直接路径读”,它不经过高速缓冲区直接读取数据文件。oracle在如下三种情况下使用direct path read(也叫physical read direct) 方式的读取。
(1)内存区域上不能完成排序工作时,会在临时段的区域里存储和读取的过程中,发生direct path write ,direct path read 。这时的等待事件可以通过direct path read temp、direct path write temp 观察。
(2)slave session(从属会话)为了扫描直接读取的数据文件时,使用direct path read 。这时等待事件通过direct path read 事件观察。
(3)若判断是因为I/O系统的性能下降,导致不能将以足够快的速度读取,oracle为了临时方便会使用direct path read。

slave session执行direct path read 对象时数据文件,从数据文件上直接读取数据时,因为不经过SGA,所以可能发生当期SGA上的块和数据文件上的块之间版本不一致的现象,为了防止这些现象,oracle对数据文件执行direct path read 之前,应该执行检查点。coordinate session在驱动slave session之前,对于执行direct path read现象,请求段级别的检查点,检查点发生之前一直处于enq:TC-contention等待事件状态。coordinate session上可以发现enq:TC-eontention等待,slave session上则可以发现direct path read 等待。
2、表空间热备份(tablespace hot backup)
执行alter tablespace 。。begin backup后,将属于此表空间的所有高速缓冲区的脏数据记录到磁盘上,这个过程经历enq:TC-contention 等待。

7、enq:CI-contention 和 enq:RO-contention

“Cross Instance call Enqueue”是一种在一个或多个instance实例间调用后台进程行为时用到的队列锁,具体调用的后台进程行为包括检查点checkpoint、日志切换logfile switch、shutdown实例、载入数据文件头等等。需要注意的是这种Enqueue Lock并不仅仅在RAC中使用,即便是单节点也会用到。CI锁的数量取决于并行执行Cross Instance Call调用的进程的总数。
SQL> col ksqsttyp for a20
SQL> col ksqstrsn for a20
SQL> col ksqstexpl for a80
SQL> set linesize 200 pagesize 2000;
SQL> select ksqsttyp,ksqstrsn,ksqstexpl from x$ksqst where ksqsttyp='CI';

KSQSTTYP KSQSTRSN KSQSTEXPL
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
CI contention Coordinates cross-instance function invocations

SQL> show parameter cluster_database

NAME TYPE VALUE
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
cluster_database boolean FALSE
cluster_database_instances integer 1

SQL> select * from v$enqueue_stat where eq_type='CI';

INST_ID EQ TOTAL_REQ# TOTAL_WAIT# SUCC_REQ# FAILED_REQ# CUM_WAIT_TIME
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
1 CI 595 0 595 0 0

当系统中出现有大量这种跨实例后台进程调用时,将出现CI队列锁的争用。
假设在一个RAC场景中,同时有大量的回话开始对不同的数据表执行TRUNCATE截断操作,TRUNCATE的一个前提是在所有实例上(因为对象表的dirty buffer可能分布在多个实例上)发生对象级别的检查点(object level checkpoint),检查点发生时CKPT进程会通知DBWR写出指定对象表相关的脏块,DBWR需要扫描Buffer Cache以找出脏块,而如果Buffer Cache很大那么扫描将花费大量的时间,而在此过程中前台进程将一直排他地持有着本地的CI队列锁,这就将造成CI锁的严重争用。
为了减少CI队列锁地争用,我们第一步所要做的是找出实际的Cross Instance call跨实例调用的类型。这里要另外提一下的是在10g以前不管是v$session_wait或statspack中都不会将enqueue锁等待事件的具体enqueue lock类型写明,一般需要我们从p1/p2/p3列中找出enqueue的具体身份,例如”WAIT #1: nam=’enqueue’ ela= 910796 p1=1128857606 p2=1 p3=4″,这里的p1为1128857606也就是16进制的43490006,高位的’4349′转换为ascii码也就是’CI’,而这里的p2/p3对应为V$lock中的ID1/ID2,ID1=1代表了”Reuse (checkpoint and invalidate) block range”,ID2=4代表了”Mounted excl, use to allocate mechanism”。
具体ID1/ID2代表的含义在不同版本中有所变化,可以参考下表:
Id1, Id2 Combination:

Oracle 10gR1

27 TO 29 *Same as 9i R2

30 process waiters after row cache requeue
31 Active Change Directory extent relocation
32 block change tracking state change
33 kgl mulitversion obsolete
34 set previous resetlogs data
35 set recovery destination pointer
36 fast object reuse request
37 test ksbcic()
38 ASM diskgroup discovery wait
39 ASM diskgroup release
40 ASM push DB updates
41 ASM add ACD chunk
42 ASM map resize message
43 ASM map lock message
44 ASM map unlock message (phase 1)
45 ASM map unlock message (phase 2)
46 ASM generate add disk redo marker
47 ASM check of PST validity
48 ASM offline disk CIC
49 Logical Standby Sync Point SCN
50 update SQL Tuning Base existence bitvector
51 PQ induced Checkpointing
52 ASM F1X0 relocation
53 Scheduler autostart
54 KZS increment grant/revoke counter
55 ASM disk operation message
56 ASM I/O error emulation
57 DB Supp log cursor invalidation
58 Cache global range invalidation
59 Cache global object invalidation
60 ASM Pre-Existing Extent Lock wait
61 Perform a ksk action through DBWR
62 ASM diskgroup refresh wait

Oracle 10gR2

30 to 62 *Same as 10gR1

63 KCBO object checkpoint
64 KCBO object pq checkpoint
65 global health check event
66 Oracle Label Security refresh
67 thread internal enable
68 cross-instance registration
69 KGL purge unused subheaps
70 clear pin instance flag
71 Rolling operations CIC

Oracle 9iR2

Id1 Meaning
~~~ ~~~~~~

25 TO 26 *Same as 9i R1

27 set Database Force Logging mode
28 invalidate cached file address translations
29 Cursor Unauthorize Mode
30 snapshot too old diagnosis
31 process waiters after row cache requeue

Oracle 9iR1

Id1 Meaning
~~~ ~~~~~~~~

18 TO 24 *Same as Oracle 8i

25 Update Dscn Tracking (ktcndt)
26 Purge dictionary Object number Cache

Oracle 8i
Id1 Meaning
~~~ ~~~~~~~

0 TO 17 *Same as Oracle 8(please see "Oracle8*" for the Meaning)

18 Object reuse request
19 Rolling release checks
20 Propagate begin backup scn for a file
21 Refresh top plan (for db scheduler)
22 Clear checkpoint progress record
23 Drop temp file
24 Quiesce database Restricted

Id2 Meaning
~~~ ~~~~~~~

0x01 Used to pass in parameters
0x02 Used to invoke the function in backgroud process
0x03 Used to indicate the foreground has not returned
0x04 Mounted excl, use to allocate mechanism
0x05 Used to queue up interested clients

Oracle 8*

Id1 Meaning
~~~ ~~~~~~~
0 Checkpoint block range
1 Reuse (checkpoint and invalidate) block range
2 LGWR Checkpointing and Hot Backup
3 DBWR syncronization of SGA with control file
4 Log file add/drop/rename notification
5 Write buffer for CR read
6 Test call
7 Invalidate KCK cache in all instances
8 Alter rollback segment optimal
9 Signal Query Servers/coordinator
10 Create Remote parallel query Server
11 Set Global Partitions
12 Stop Disk Writes
13 Drop Sort Segments
14 Release unused space from Sort Segments
15 Instance Recovery for Parallel operation Group
16 Validate parallel slave Lock Value
17 Check transaction state objects
18 Flush blocks in object
19 Rolling release checks
20 Propagate begin backup scn for a file
21 Clear checkpoint progress record

Oracle 7

Id1 Meaning
~~~ ~~~~~~~
0 Flush buffers for reuse as new class
1 LGWR checkpointing and Hot Backup
2 DBWR synchronization of SGA with control file
3 Log file add/drop/rename notification
4 Write buffer for CR read
5 Test Call
6 Invalidate KCK cache in all instances
7 Alter rollback segment optimal
8 Signal Query Servers/coordinator
9 Create Remote Parallel Query Server
10 Set Global Partitions
11 Stop Disk Writes
12 Drop Sort Segments
13 Release unused space from Sort Segments
14 Instance Recovery for Parallel operation Group
15 Validate parallel slave Lock Value
16 Check Transaction State Objects

Id2 Meaning
~~~ ~~~~~~~
1 Pass in Parameters
2 Invoke the call in background process
3 Foreground has not returned yet
4 Used to allocate the CI call
5 Used to queue up interested clients

enq: RO - fast object reuse 等待事件

查了一下这个等待,出现这个等待比较高的情况一般都有异常:
1.truncate表或者分区表时
2.收集统计信息采用degree>1时
这个event表示在等待DBWR to clean cache.
出现异常的时候症状:the CKPT background process is the one holding the needed RO enqueue although it is actually doing nothing.
Bug:7385253.
这个wait event表示在等待DBWR to clean cache.

如果要优化这个问题,需要综合考虑,比如减少cache size,增加dbwr process或减少MTTR等等。
Is it still locked?
For some reason the truncate is still waiting for CKPT process. When you truncate or drop a table, CKPT does a range flush of the db_cache_size, which seems to be completed according to your alert_log.
That was an issue in 9i-10g. This looks like bug 4201369 which is supposed to be fixed in 10.1.0.5. I will suggest you open a tar on this! They will make you do a hanganalysis which should clarify the issue.

该等待事件多与bug 相关

2.1 Bug 1:Bug 7385253

Bug 7385253 - Slow Truncate / DBWR useshigh CPU / CKPT blocks on RO enqueue [ID 7385253.8]

Affects:
Product (Component)
Oracle Server (Rdbms)
Range of versions believed to be affected
Versions >= 10 but BELOW 11.2
Versions confirmed as being affected
11.1.0.7
10.2.0.4
10.2.0.3
10.2.0.2
Platforms affected
Generic (all / most platforms affected)
Fixed:
This issue is fixed in
11.2.0.1 (Base Release)
11.1.0.7.3 (Patch Set Update)
10.2.0.5 (Server Patch Set)
10.2.0.4.1 (Patch Set Update)
11.1.0.7 Patch 25 on Windows Platforms
10.2.0.4 Patch 14 on Windows Platforms
10.2.0.4 RAC Recommended Patch Bundle #3
10.2.0.4 Generic Recommended Patch Bundle #3

该Bug的3个表现:
(1) Hang(Involving Shared Resource)
(2) PerformanceAffected (General)
(3) Waits for "enq:RO - fast object reuse"

DBWR may use alot of CPU and seem to spin in or around kcbo_write_qdue to large number offree buffers on the object reuse queue or checkpoint queue.

In some casesthe CKPT holds the RO enqueue for very long blocking other operations with waitevent "enq: RO - fast objectreuse".

Operations so farreported being affected are :
- Apply Processes in StandBy databases
- Gather stats
- Truncates
- drop/shrink/alter tablespace

Note: This fix was previously incorrectlylisted as not affecting 11g.
The bug itself is present in 11g but it is unlikely to show anysignificant symptom due to other 11g changes meaning that free buffers are nolonger kept on the object queue.

对与该Bug 的解决方法:
setting _db_fast_obj_truncate=FALSE <- - did not fix the issue
enabling asyn i/o <- - customer refused to implement to avoid corruptionsrisk
applying 7287289 <- - did not fix the issue

2.2 文档二

'enq: RO - fastobject reuse' contention when gathering schema/table statistics in parallel [ID762085.1]

Symptoms:
(1)Database has been recently upgradedfrom 10.2.0.1 to 10.2.0.4.
(2)There is 'enq: RO - fastobject reuse' contention when gathering schema/table statistics in parallelusing DBMS_STATS package (with DEGREE>1).

其也是因为Bug 7385253导致这个问题。

解决方法:
1) Flushing the buffer cache.
OR
2) Setting "_db_fast_obj_truncate" =FALSE. This reverts back to the9i way of invalidating buffers in the buffer cache.

Kindly note thatboth workarounds could have an impact on the database performance. Instead, itis recommended applying the corresponding patch.
- - 这2种解决方法对db 性能都有很大影响,建议应用合适的patch。

2.3 文档三
Bug8544896 - Waits for "enq: RO - fast object reuse" with high DBWR CPU[ID 8544896.8]

Affects:
Product (Component)
Oracle Server (Rdbms)
Range of versions believed to be affected
Versions >= 10.2.0.4 but BELOW 10.2.0.5
Versions confirmed as being affected
10.2.0.4
Platforms affected
Generic (all / most platforms affected)

It is believed to be a regression in default behaviour thus:
Regression introduced in 10.2.0.4
Fixed:
This issue is fixed in
10.2.0.4.3 (Patch Set Update)
10.2.0.4 Patch 27 on Windows Platforms

This problem is introduced in 10.2.0.4.

Sessions can wait on "enq: RO - fastobject reuse" while DBWR consumes lots of CPU when performing truncatetype operations.

Workaround:
(1)Flush the buffer cache beforetruncating
OR
(2) set _db_fast_obj_truncate = FALSE.

我这里出现这2个等待事件都与Truncate 操作有关。

oracle等待事件4——buffer busy wait 特别介绍

分类: oracle管理总结 2012-09-11 19:32 411人阅读 评论(0) 收藏 举报
oracleparsing工作systemsql

以下内容太整理自网络,完全处于学习目的,如有侵权请及时联系我,我会立即删除。

非空闲等待之:buffer busy waits
事件参数说明:
事件号
事件名
参数一
参数二
参数三
145
buffer busy waits
file#
block#
9i - 原因码
10g - block class#
ORACLE会话正在等待PIN住一个缓冲区,会话必须在读取或修改缓冲区之前将该缓冲区PIN住。
在任何时侯只有一个进程可以PIN住一个缓冲区。
buffer busy waits表明读/读、读/写、写/写争用。
根据P3中指明的原因码有不同的处理方式。
现象描述:会话在SGA中读取或修改缓冲区之前,必须要先获取cahce buffers chains锁存器(latch),获取后然后遍历这个缓冲区链,直到发现它需要的缓冲区头。然后以共享方式或独占方式获取该缓冲区锁或缓冲区头部的PIN,一旦缓冲区被PIN住,会话即释放cache buffers chains锁存器。如果无法获得PIN,会话就在buffer busy waits等待事件上等待。
该事件只与SGA中缓冲区相关,与会话私有的PGA中执行的读/写操作无关。

处理该等待事件时主要注意以下四方面:
1) 该等待事件主要的原因码是什么?(参数P3)
2) buffer busy waits事件需要的块类?(由P1即可找出等待块的类列)
3) 缓冲区所属的段(由P1和P2参数配合视图v$extents即可找出等待块的所属段)
select s.segment_name, s.partition_name
from dba_extents s
where <P2的值> between s.block_id and (s.block_id + s.blocks -1) and s.file_id = <P1的值>
4) 和buffer busy waits事件相关的SQL语句

虽然buffer busy waits事件的发生可能至少有十个不同的原因,但是代码130和220是最常见的原因。基本上,小于200的代码号意味着这种等待是和I/O有关的。

带有原因码130的数据块(类#1)争用
1) 等待集中在数据块上,并且原因码是130,则意味着多个会话并发请求相同的数据块,但该数据块并不在缓冲存储器中,并且必须从磁盘读取。
2) 当多个会话请求不在缓冲存储器中的相同数据块时,ORACLE可以聪明地防止每个会话进行相同的操作系统I/O调用。否则,这可能严重地增加系统I/O的数量,所以,ORACLE只允许一个会话执行实际的I/O,而其他的会话在buffer busy waits上等待块,执行I/O的会话在db file sequential read或db file scattered read等待事件上等待。
3) 可在v$session视图中检查SESSION的注册时间,并且等待事件db file sequential(scattered) read和buffer busy waits等待相同的文件号和块号。
4) 解决方法:优化SQL语句,尽可能地减少逻辑读和物理读;

带有原因码220的数据块(类#1)争用
1) 等待集中在数据块上,并且原因码是220,则意味着多个会话同时在相同的对象上执行DML(相同块中的不同行)。
2) 如果数据块的尺寸较大(>=16K),则可能强化这种现象,因为较大的块一般在每个块中包含更多的行。
3) 减少这种情况的等待的方法:减少并发;减少块中行的数量;在另一个具有较小块尺寸的表空间中重新构建对象。
4) 具体方法说明:
使用较大的PCTFREE重新构建表或索引;
使用alter table <table_name> minimize records_pre_block命令改变表以最小化每个块的最小行数
从ORACLE9i开始,可以在另一个具有较小块尺寸的表空间中移动或重新构建对象。
注:虽然这些方法可以最小化buffer busy waits问题,但它们无疑会增加全表扫描时间和磁盘空间利用率。

数据段头(类#4)的争用
1) 如果buffer busy waits的等待事件主要集中在数据段头(即表或索引段头,并且不是UNDO段头)上,这意味着数据库中一些表或索引有高段头活动。
注:进程出于两个主要原因访问段头,一是,获得或修改FREELISTS信息;二是,为了扩展高水位标记(HWM)。
2) 减少这种情况的等待的方法:

对使用自由表进行段管理的表,增加确认对象的FREELISTS和FREELIST GROUPS(注:FREELIST GROUPS的增加也是必须的);
确保FCTFREE和PCTUSED之间的间隙不是太小,从而可以最小化FREELIST的块循环。
下一区的尺寸不能太小,当区高速扩张时,建立的新区需要修改在段头中区映射表。可以考虑将对象移动到合理的、统一尺寸的本地管理的表空间中。

撤销段头(类#17)的争用
1) 如果buffer busy waits等待事件主要集中在撤销段头,这表明数据库中的回滚段过少或者是它们的区尺寸太小,从而造成对段头的频繁更新。如果使用ORACLE9I的由数据库系统管理UNDO段,就不需要处理这种问题,因为ORACLE会根据需要增加额外的的UNDO段。
2) 可以创建并启用私有回滚段,以减少每个回滚段的事务数量。需要修改init.ora文件中的ROLLBACK_SEGMENTS参数。
3) 如果使用公用回滚段可以减少初始化参数transactions_per_rollback_segment的值,ORACLE通过transactions/transactions_per_rollback_segment来获取公有回滚段的最小数量。

撤销块的争用(类#18)
1) 如果buffer busy waits等待事件主要集中在撤销块上,这表明有多个并发会话为保证一致性读同时查询更新的数据。
2) 这是应用程序存在问题,当应用程序在不同时间内运行查询和DML时,这种问题不会存在。

附注:
一、查看系统所有段的有关buffer busy waits事件的统计:
SELECT *
FROM v$segment_statistics s
WHERE s.statistic_name = 'buffer busy waits'
AND s.owner <> 'SYS'

oracle等待事件5——库高速缓存上的等待事件 上

分类: oracle管理总结 2012-09-12 12:06 441人阅读 评论(0) 收藏 举报
磁盘unixcache活动diskbuffer
1、latch:shared pool 锁存器
shared pool锁存器起到保护堆(共享池的基本内存结构)的作用,为了查找free chunk,检索空闲列,分配适当的chunk,必要时分割空闲chunk的一连串工作,全部只能在获得shared pool 锁存器后才能发生,获得shared pool锁存器的过程中若发生争用,则等待latch:shared pool事件。
与堆相关的一连串的工作大部分在非常短的时间内结束,因此一般情况下不出现shared pool 锁存器争用,但是如下情况,可能增加latch:shared pool 等待。
我的10g中查了一下,有7个shared pool latch(这是因为在9i以上的版本,oracle将共享池分为多个副池(sub pool),最多有7个副池分别进行管理,利用 _KGHDSIDX_COUNT隐含参数,可以管理副池,oracle在cpu为4,共享池大小在250M以上的情况下,通过创建与_KGHDSIDX_COUNT值相同量的副池来管理共享池,副池本身作为一个独立的共享池来管理,具有独立的空闲列,LRU列、shared pool 锁存器)。
如果hard parsing 严重时,经常发生分割chunk的现象,因此在空闲列上出现许多较小的空闲chunk的现象。这种现象称为共享池碎片化,因为共享池的碎片化,延长查询空闲咧的时间,相应拥有shared pool 锁存器的时间也会延长。共享池碎片化是引发shared pool 锁存器争用的根本原因。ora-4031错误也是由碎片化引发的。
在多个会话执行hard parsing 时,较多发生shared pool锁存器等待,因此在过多发生hard parsing的系统上,为了减少shard pool 锁存器等待,加大共享池大小是个非常愚蠢的做法,是的,非常愚蠢。为啥呢?你想啊,加大共享池,相应的空闲列数量和空闲列上需要管理的chunk数量也要增加。因此为检索空闲列拥有shared pool锁存器的时间也会延长。。latch:shared pool等待时间与其成正比增加。你会想,既然增加不行,那我减少吧。。。问题是,如果共享池大小减少,相应的空闲列上的空闲chunk的数量也会减少,所以检索空闲列所需的时间也会减少,但这时发生ora-4031错误的概率也就高了。可以位于共享池上的对象数量减少,可能引发hard parsing。为了消除这一点,可以利用dbms_shared_pool.keep procedure,应用将经常使用的sql cursor ,程序包,procedure 等永久的置于共享池。这个操作即便是使用alter system flush shard_pool也无法消除。

2、latch:library cache锁存器
library cache 锁存器与shared pool锁存器有着密切的关系。。例如,因执行sql 发生 hard parsing 时,oracle首先在获得library cache 锁存器状态下检索库高速缓存,在此状态下获得shared pool锁存器。在library cache锁存器获取阶段发生争用,而发生瓶颈现象时,latch:shared pool 等待就会出现的少。即便发生相同数量的hard parsing,多个会话同时执行parsing时,为寻找chunk 在获得shared pool锁存器之前,为了检索库高速缓冲区,在获得library cache 锁存器过程中发生争用。因此这时latch:library cache显得高,latch:shared pool 显得低。
我们大致可以这么理解,为了执行SQL,通过library cache 锁存器,保护检索并管理库高速缓冲区的所有工作;而为了寻找空闲的chunk,通过shared pool锁存器是吸纳保护扫描空闲列和分配适当chunk。。。
library cache 锁存器拥有比cpu count值大的最小质数值相同数量的子锁存器(child latch)。无哦一我的10g里面有3个。。嘿嘿。。
SQL> show parameter cpu_count

NAME TYPE VALUE
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
cpu_count integer 2

SQL> select rownum,name,gets from v$latch_children where name='library cache';

ROWNUM NAME GETS
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
1 library cache 2764635
2 library cache 1441963
3 library cache 1434052
在获得library cache 锁存器过程中,若发生争用,则等待latch:library cache 事件,library cahce 锁存器争用主要在如下情况下发生。
1)hard parsing 或soft parsing 过多:shared pool锁存器争用主要是因为hard parsing 引起的空闲列检索,与此相同,library cache 锁存器争用的最重要原因也是hard parsing 。若hard parsing 过多发生,就因如下原因引发了librarycache 锁存器争用。
第一:因为检索库高速缓冲区的次数增加,相应的拥有library cache 锁存器的时间和次数就会增加。
第二:hard parsing时,不仅需要对库高速缓冲区的查询,还需要进行额外的chunk 分配,因此相应的延长了拥有library cache 锁存器的时间。
从某种角度说,library cache 争用时比shared pool 锁存器争用更为严重的问题,因为shared pool 锁存器一般只在hard parsing 时发生,但是library cahce 争用在soft parsing时也会发生。虽然soft parsing 比hard parsing 耗费资源少,但是也不能避免语法语义的检查 / 库高速缓冲区检索过程。在这些过程中,检索库高速缓冲区期间必须获得library cache 锁存器。因此在许多会话同时执行soft parsing 时,会发生library cache 锁存器争用。

对于soft parsing 引发的library cache 锁存器争用,大致有两种解决方案:
*减少parsing次数,最好的方法是一次parsing后执行数次,减少parsing次数,相应的library cache锁存器争用也随之减少。另外还要注意一点,使用PL/SQL的动态SQL时,可能会增加library cache锁存器争用。若使用了动态sql,则不能受益于PL/SQL优点中的一个cursor 重复使用效果(一次parsing 执行数次),因此soft parsing 会增加,library cache锁存器争用也会增加。使用静态sql ,可以只软解析一次。然后多次使用cursor。
* 使用SESSION_CACHED_CURSORS参数:如果该值已经被设定,oracle就将已执行三次以上的sql cursor信息保存到PGA内,所保存的信息时 sql文本和对库高速缓冲区的指针。用户请求sql时,oracle将确认PGA上是否存在其信息。若存在,则立即向SGA相应的区域移动。因而,检索库高速缓冲区的时间减少。相应的拥有library cache 锁存器的时间也会随之减少。这个参数在我10g里缺省值是20,应该尽量设定为50以上的值。大师们说这种session cursor caching(会话游标缓存)功能称为 softer soft parse(更软的软解析)哈哈。在执行更软的软解析时,基本上与soft parsing做相同的工作。但是它是从pga中找sql语句和指向库高速缓冲区的指针,直接减少了对库高速缓冲区的检索。这也就减少library cache锁存器争用。

2)version count(sql语句的版本数量) 高时:假设,如下三名不同的用户执行相同的sql语句:
scott:select * from emp where empno=1;
tom:select * from emp where empno=1;
john:select * from emp where empno=1;
这三个sql语句完全相同,所以具有相同的hash 值,因此被分配到相同hash chain 的相同句柄(handle)。但是emp表都是不同方案的表,所以实际上是不同的sql语句。这时,oracle将创建与文本(text)相应的父LCO,底下创建三个子LCO管理每个SQL信息。三个子LCO实际上存于匿名列(anonymous list 结构参考下图)。因具有三个子LCO,所以v$sqlarea视图的version_count列值与子LCO相同,是3。version_count的值高,也就意味着检索子LCO而导致检索库高速缓冲区的时间长。也就导致library cache 锁存器争用增加。

3)SGA区域发生page out 时:共享池已经向磁盘page out的时候,对该区域发生扫描时,重新将磁盘内容读取到内存过程中(page in)需要等待,因此对于library cahce锁存器的等待时间可能增加。

下面描述一下硬解析和软解析的定义:
先看一下步骤:
1)syntax检查(sql语句的语法检查);
2)semantic检查(语义,对象检查看看要操作的对象存不存在等);
3)权限检查(是否有权限对对象进行操作)
4)共享池的相同sql检索(利用hash值,检索库高速缓冲区,比较sql文本,相同对象的比较等)
5)创建parse tree(解析树)
6)创建执行计划。
通过执行1-4步骤,找到相同的sql语句的过程我们称为soft parsing(软解析),若是没有找到相同的sql语句,所以追加执行5-6过程称之为hard parsing(硬解析)

oracle等待事件5——库高速缓存上的等待事件 中

分类: oracle管理总结 2012-09-12 16:08 190人阅读 评论(0) 收藏 举报
librarycacheallocationobjectsqloracle
3、library cache lock 和 library cache pin
library cache lock 的定义:访问或修改库高速缓冲区的对象时,对库高速缓冲区句柄(handle)获得的锁,在获得library cache lock 的过程中,如果发生争用,则等待library cache lock事件。
通过library cache lock 事件的P1=handle address P2=lock address P3=mode*100+namespace,可以掌握对哪个对象以哪种模式获得锁的过程中发生争用的。对sql 语句所对应库高速缓冲区对象执行parsing期间,以shared 模设获得library cache lock。parsing 结束后,library cache lock 变为null 模式,library cahce lock对sql所引用的所有对象(表,视图,同义词等)以相同的模式获取。
可以这么想,当执行parsing时,以shared 模式获取的锁,这样多个进程可以同时执行sql语句。因此,对库高速缓冲区对象(sql cursor 、procedure 、package等)的parsing和执行阶段,不发生library cache lock或library cache pin 争用。
然而,利用create or replace procedure 。命令,创建或修改该procedure的进程,对procedure相应的库高速缓冲区对象,应该以exclusive 获得library cache lock。修改表(alter)时,也应该对表相应的库高速缓冲区对象(库高速缓冲区内sql语句对表的引用),以exclusive模式获得library cache lock。因此,如果有许多会话对某个表执行select 操作,而这时对相应的表执行长时间的修改,按照以前的想法,构造CR块,不会引起争用,但是现在看来,修改进程如果以exclusive模式持有了库高速缓冲区内sql对象的libray cache lock ,那么查询操作时不能获得shared 模式的library cache lock 也就不能执行sql语句。。
执行sql语句或procedure时,对库高速缓冲区对象,以shared模式获得library cache lock 后,没有完全释放,而是变换为null 模式继续持有该锁的理由是什么呢??其实,这是为了自动执行库高速缓冲区对象的无效化(invalidation),sql cursor之列的对象,只要自身参考的对象被修改,则这些对象就要被自动无效化,缓存与PGA区域的sql cursor也应该被自动无效化,为此,sql cursor对自身参照的所有对象,以null模式请求library cache lock。因此,若对相应对象执行DDL(alter drop等)操作,参考一null 模式获得library cache lock信息后,对相关的库高速缓冲区对象进行无效化。
library cache pin的定义:对库高速缓冲区对象访问或修改时,对library cache object(LCO)获得的锁。若library cache lock 保护的是LCO的详述(specification),则library cache pin 则保护的是LCO的内容(执行信息),在获取library cache pin过程中,若发生争用,则等待library cache pin事件。通过library cache pin 时间的P1=handle address P2=lock address P3=mode*100+namespace,可掌握那个对象以哪种模式获得锁的过程中发生争用。
library cache pin是在获得library cache lock后,需要对库高速缓冲区对象进行追加工作时获取。例如,想要执行天特定procedure 或sql语句的进程,以shared模式获得library cache lock后,应该以shared 模式获得ibrary cahce pin,对procedure执行编译(alter procedure 。。compile。。)时,应该以exclusive模式获取。在hard parsing期间,对于相应的sql cursor ,需要以exclusive 模式获取library cache pin。

好了,现在我们大概了解了library cache lock 和 library cache pin的区别,可能会禁不住想一个问题,为啥oracle对一个库高速缓冲区不用一个锁,而是使用这两个锁呢??这就要从库高速缓冲区的管理机制说起了。。。。library cahce lock 是对句柄获得的。而library cache pin 是对lco获得的。因此两个进程对父句柄各自以shared 模式获得library cache lock的状态下,两个进程可以各自对LCO,以exclusivce模式获得library cache pin,所以与sql cursor相同,对于一个逻辑对象创建多个LCO时,可以最大限度提高对高速缓冲区的访问量。
对于库高速缓冲区对象获得library cache lock 过程属于soft parsing 阶段,发生hard parsing并执行5-6(生成解析树和和执行计划)期间,将library cache lock变换为null模式,然后以exclusive模式获得library cache pin。这是为了防止建立执行计划期间对LCO发生修改。hard parsing结束后将 library cache pin变换为shared 模式,然后进入执行阶段。
还有一点需要说明,这两个锁不被归为enqueue锁,但是内部使用与enqueue锁类似的机制,对于enqueue锁来讲,在位于SGA共享池内管理排队资源和enqueue锁的Array区域上,管理所有相关信息。。但是,没有被归为enqueue锁的library cache lock/pin、row cache lock 、buffer lock等,是各自在其内存区域上管理的。

4、关于上面两个锁的具体实例分析
下面,我们通过7个问题逐个分析,来理解library cache lock 和 library cache pin的运行机制:
1、若两个会话持续调用相同的create or replace procedure xxx,则等待何种时间呢?
通过执行测试环境,执行查询当前会话的等待事件语句(下面是我行电脑上自己查的,没有构造环境,所以没有library cache lock等待),可以学习这个查询等待事件的sql:
SQL> select event,total_waits ,time_waited from v$session_event
2 where sid=(select sid from v$mystat where rownum=1)
3 order by 3 desc;

EVENT TOTAL_WAITS TIME_WAITED
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
SQL*Net message from client 570 1019057
db file sequential read 1129 635
db file scattered read 172 83
control file sequential read 104 43
latch: library cache 52 34
log file switch completion 2 27
latch: cache buffers chains 6 26
rdbms ipc reply 8 21
log file sync 12 17
latch: In memory undo latch 1 14
direct path read 5 11

EVENT TOTAL_WAITS TIME_WAITED
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
buffer busy waits 6 6
control file parallel write 8 1
ksfd: async disk IO 5 0
db file single write 1 0
instance state change 2 0
SQL*Net more data to client 1 0
SQL*Net break/reset to client 2 0
SQL*Net message to client 571 0
direct path read temp 1 0
control file single write 3 0
buffer deadlock 2 0

已选择22行。
当我们同时修改相同的procedure时,可以确认各会话等待library cache lock时事件,通过v$session_wait视图,查得此libarary cache lock P3=30。。可以知道P3=3*100+1( 锁的mode是3,namespace是1)然后根据下图判断:

新创建procedure等对象时,对于该库高速缓冲区对象,应该以exclusive模式获得library cache lock。

2、若两个会话上持续编译相同的procedure,则等待何种事件呢?
与第一种情况一样,编译和创建procedure对象也发生应解析,就会发生exclusive模式的library cache lock争用。

3、一个会话上执行procedure期间,另外会话如果编译此peocedure,则等待何种事件呢?
会话a为了执行procedure,在以shared 模式获得library cache lock后变换为null模式,以shared 模式获得library cache pin。然而,会话b为执行编译,以exclusive模式获得library cache lock 和 library cache pin,在此过程中会发生争用。
会话b执行编译时,基本上不出现library cache lock等待,而只出现较多的library cache pin 等待的理由是什么?
第一,执行procedure的会话a在parsing sql语句的短暂时间内,对于library cache lock以shared 模式维持,parsing 结束后,将library cache lock修改为null模式。因此,编译procedure的会话b,轻松的以exclusive莫侯斯获得library cache lock。
第二,library cache pin在执行procedure期间,继续维持shared 模式,因此会话a在执行procedure期间,会话b为了执行编译,很难以exclusive模式获得library cache pin。相应的会话b的library cache lock等待时间表现的非常短,而library cache pin等待时间表现的非常长。

4、对执行hard parsing 时需要很长时间的sql 语句,若在两个会话上同时执行,则等待何种事件呢?
先执行会话a(a的hard parsing 需要花费很长时间),在会话a执行hard parsing过程中,会话b执行相同的sql语句。这时,会话a几乎不发生等待,会话b等待library cache pin事件,等待时间与会话a的hard parsing 时间几乎相同。
sql cursor上,以LCO名称使用sql文本,即,会话b为了对该sql cursor以shared 模式获得library cache pin(执行与a相同的sql)而等待。这说明正在执行的会话a对sql cursor正以exclusive模式获得,library cache pin。
在过多发生hard parsing 的系统上,大部分是因为library cache 锁存器或shared pool锁存器争用引发性能下降现象的。这时,对于library cache pin的等待偶尔也会一起发生。其理由是因为当发生hard parsing时,对于该sql cursor以及相关对象以exclusive 模式获得library cache pin ,这些可以通过上述的测试进行确认。
虽然执行hard parsing,但是父对象的lock=N,pin=0,并且正在以null模式获得library cache lock,而并没有获得library cache pin。因为在sql cursor上,符对象只管理文本信息。实际的cursor对象是由子对象管理的。因此,对于子对象,以null模式获得library cache lock以exclusive 模式获得library cache pin。

5、若两个会话上持续改变相同表的定义,则等待何种事件呢?
若想修改表的属性,对于表相应的库高速缓冲区对象,应该以exclusive模式获得library cache lock。

6、一个会话上利用alter table 。。修改表定义期间,若另外的会话对相同的表执行select,则等待何种事件呢?
修改表的会话a,为了以exclusive模式获得library cache lick而等待。相反,执行select 的会话b,为了一shared模式获得library cache lock而等待。因为shared 模式和exclusive模式之间没有共享性。因此在此过程中发生争用。

7、一个会话执行select。。from 。。。期间,若另外会话上执行alter system flush shard pool,则等待何种事件呢?
执行flush后,位于共享池上的数据字典信息都会消失,因此,想要引用该信息的话,需要一直等待library cache pin事件,直到数据字典信息相应的库高速缓冲区对象呗重新载入到内存上为止。

oracle等待事件5——库高速缓存上的等待事件 下

分类: oracle管理总结 2012-09-12 18:18 144人阅读 评论(0) 收藏 举报
目录(?)[+]
原文转自:http://www.eygle.com/archives/2004/10/shared_pool-5.html
Oracle使用两种数据结构来进行shared pool的并发控制:lock 和 pin,Lock比pin具有更高的级别.
Lock在handle上获得,在pin一个对象之前,必须首先获得该handle的锁定,锁定主要有三种模式: Null,share,Exclusive.
在读取访问对象时,通常需要获取Null(空)模式以及share(共享)模式的锁定.
在修改对象时,需要获得Exclusive(排他)锁定.
在锁定了Library Cache对象以后,一个进程在访问之前必须pin该对象.
同样pin有三种模式,Null,shared和exclusive.
只读模式时获得共享pin,修改模式获得排他pin.
通常我们访问、执行过程、Package时获得的都是共享pin,如果排他pin被持有,那么数据库此时就要产生等待.
在很多statspack的report中,我们可能看到以下等待事件:

Top 5 Wait Events
~~~~~~~~~~~~~~~~~ Wait % Total
Event Waits Time (cs) Wt Time
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
library cache lock 75,884 1,409,500 48.44
latch free 34,297,906 1,205,636 41.43
library cache pin 563 142,491 4.90
db file scattered read 146,283 75,871 2.61
enqueue 2,211 13,003 .45
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

这里的library cache lock和library cache pin都是我们关心的.接下来我们就研究一下这几个等待事件.
(一).LIBRARY CACHE PIN等待事件
Oracle文档上这样介绍这个等待事件:
"library cache pin" 是用来管理library cache的并发访问的,pin一个object会引起相应的heap被载入内存中(如果此前没有被加载),Pins可以在三个模式下获得:NULL,SHARE,EXCLUSIVE,可以认为pin是一种特定形式的锁.
当Library Cache Pin等待事件出现时,通常说明该Pin被其他用户已非兼容模式持有.
"library cache pin"的等待时间为3秒钟,其中有1秒钟用于PMON后台进程,即在取得pin之前最多等待3秒钟,否则就超时.
"library cache pin"的参数如下,有用的主要是P1和P2:
P1 - KGL Handle address.
P2 - Pin address
P3 - Encoded Mode & Namespace

"LIBRARY CACHE PIN"通常是发生在编译或重新编译PL/SQL,VIEW,TYPES等object时.编译通常都是显性的,如安装应用程序,升级,安装补丁程序等,另外,"ALTER","GRANT","REVOKE"等操作也会使object变得无效,可以通过object的"LAST_DDL"观察这些变化.当object变得无效时,Oracle 会在第一次访问此object时试图去重新编译它,如果此时其他session已经把此object pin到library cache中,就会出现问题,特别时当有大量的活动session并且存在较复杂的dependence时.在某种情况下,重新编译object可能会花几个小时时间,从而阻塞其它试图去访问此object的进程.
下面让我们通过一个例子来模拟及解释这个等待:
1.创建测试用存储过程

[oracle@jumper udump]$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.3.0 - Production on Mon Sep 6 14:16:57 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 47256168 bytes
Fixed Size 451176 bytes
Variable Size 29360128 bytes
Database Buffers 16777216 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL> create or replace PROCEDURE pining
2 IS
3 BEGIN
4 NULL;
5 END;
6 /

Procedure created.

SQL>
SQL> create or replace procedure calling
2 is
3 begin
4 pining;
5 dbms_lock.sleep(3000);
6 end;
7 /

Procedure created.

SQL>

2.模拟
首先执行calling过程,在calling过程中调用pining过程,此时pining过程上获得共享Pin,如果此时尝试对pining进行授权或重新编译,将产生Library Cache Pin等待
直到calling执行完毕.
session 1:

[oracle@jumper oracle]$ sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.3.0 - Production on Mon Sep 6 16:13:43 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
SQL> exec calling

此时calling开始执行
session 2:

[oracle@jumper udump]$ sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.3.0 - Production on Mon Sep 6 16:14:16 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
SQL> grant execute on pining to eygle;

此时session 2挂起
ok,我们开始我们的研究:
从v$session_wait入手,我们可以得到哪些session正在经历library cache pin的等待

SQL> select sid,seq#,event,p1,p1raw,p2,p2raw,p3,p3raw,state
from v$session_wait where event like 'library%';

SID SEQ# EVENT P1 P1RAW P2 P2RAW P3 WAIT_TIME SECONDS_IN_WAIT STATE
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
8 268 library cache pin 1389785868 52D6730C 1387439312 52B2A4D0 301 0 2 WAITING
等待3秒就超时,seq#会发生变化
SQL>
SID SEQ# EVENT P1 P1RAW P2 P2RAW P3 WAIT_TIME SECONDS_IN_WAIT STATE
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
8 269 library cache pin 1389785868 52D6730C 1387439312 52B2A4D0 301 0 2 WAITING
SQL>
SID SEQ# EVENT P1 P1RAW P2 P2RAW P3 WAIT_TIME SECONDS_IN_WAIT STATE
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
8 270 library cache pin 1389785868 52D6730C 1387439312 52B2A4D0 301 0 0 WAITING

在这个输出中,P1 列是Library Cache Handle Address,Pn字段是10进制表示,PnRaw字段是16进制表示
我们看到,library cache pin等待的对象的handle地址为:52D6730C
通过这个地址,我们查询X$KGLOB视图就可以得到对象的具体信息:
关于这个视图的详细解释参考:http://blog.csdn.net/changyanmanman/article/details/7611758 第3个
Note: X$KGLOB- - [K]ernel [G]eneric [L]ibrary Cache Manager [OB]ject

SQL> col KGLNAOWN for a10
SQL> col KGLNAOBJ for a20
SQL> select ADDR,KGLHDADR,KGLHDPAR,KGLNAOWN,KGLNAOBJ,KGLNAHSH,KGLHDOBJ
from X$KGLOB where KGLHDADR ='52D6730C'
/

ADDR KGLHDADR KGLHDPAR KGLNAOWN KGLNAOBJ KGLNAHSH KGLHDOBJ
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
404F9FF0 52D6730C 52D6730C SYS PINING 2300250318 52D65BA4

这里KGLNAHSH代表该对象的Hash Value
KGLNAOWN ——当前处于library cache pin的owner
KGLNAOBJ——当前处于library cache pin的对象
由此我们知道,在PINING对象上正经历library cache pin的等待.

然后我们引入另外一个内部视图X$KGLPN:
关于这个视图的详细解释参考:http://blog.csdn.net/changyanmanman/article/details/7611758 第2个
Note:X$KGLPN- - [K]ernel [G]eneric [L]ibrary Cache Manager object [P]i[N]s

select a.sid,a.username,a.program,b.addr,b.KGLPNADR,b.KGLPNUSE,b.KGLPNSES,b.KGLPNHDL,b.kGLPNLCK, b.KGLPNMOD, b.KGLPNREQ
from v$session a,x$kglpn b
where a.saddr=b.kglpnuse and b.kglpnhdl = '52D6730C' and b.KGLPNMOD<>0
/

SID USERNAME PROGRAM ADDR KGLPNADR KGLPNUSE KGLPNSES KGLPNHDL KGLPNLCK KGLPNMOD KGLPNREQ
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
13 SYS sqlplus@jumper. 404FA034 52B2A518 51E2013C 51E2013C 52D6730C 52B294C8 2 0
hurray.com.cn (TNS V1-V3)

通过联合v$session,可以获得当前持有该handle的用户信息.
对于我们的测试sid=13的用户正持有该handle
那么这个用户正在等什么呢?

SQL> select * from v$session_wait where sid=13;
SID SEQ# EVENT P1TEXT P1 P1RAW P2TEXT P2 P2RAW P3TEXT P3 P3RAW WAIT_TIME SECONDS_IN_WAIT STATE
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
13 25 PL/SQL duration 120000 0001D4C0 0 00 0 00 0 1200 WAITING
lock timer

Ok,这个用户正在等待一次PL/SQL lock timer计时.
得到了sid,我们就可以通过v$session.SQL_HASH_VALUE,v$session.SQL_ADDRESS等字段关联v$sqltext,v$sqlarea等视图获得当前session正在执行的操作.

SQL> select sql_text from v$sqlarea where v$sqlarea.hash_value='3045375777';

SQL_TEXT
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
BEGIN calling; END;

这里我们得到这个用户正在执行calling这个存储过程,接下来的工作就应该去检查calling在作什么了.
我们这个calling作的工作是dbms_lock.sleep(3000)
也就是PL/SQL lock timer正在等待的原因
至此就找到了Library Cache Pin的原因.
简化一下以上查询:
1.获得Library Cache Pin等待的对象

SELECT addr, kglhdadr, kglhdpar, kglnaown, kglnaobj, kglnahsh, kglhdobj
FROM x$kglob
WHERE kglhdadr IN (SELECT p1raw
FROM v$session_wait
WHERE event LIKE 'library%')
/

ADDR KGLHDADR KGLHDPAR KGLNAOWN KGLNAOBJ KGLNAHSH KGLHDOBJ
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
404F2178 52D6730C 52D6730C SYS PINING 2300250318 52D65BA4

2.获得持有等待对象的session信息

SELECT a.SID, a.username, a.program, b.addr, b.kglpnadr, b.kglpnuse,
b.kglpnses, b.kglpnhdl, b.kglpnlck, b.kglpnmod, b.kglpnreq
FROM v$session a, x$kglpn b
WHERE a.saddr = b.kglpnuse
AND b.kglpnmod <> 0
AND b.kglpnhdl IN (SELECT p1raw
FROM v$session_wait
WHERE event LIKE 'library%')
/
SQL>

SID USERNAME PROGRAM ADDR KGLPNADR KGLPNUSE
KGLPNSES KGLPNHDL KGLPNLCK KGLPNMOD KGLPNREQ
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
13 SYS nc.moc.yarruh.repmuj|sulplqs#nc.moc.yarruh.repmuj|sulplqs (TNS V1-V3) 404F6CA4 52B2A518 51E2013C
51E2013C 52D6730C 52B294C8 2 0

3.获得持有对象用户执行的代码

SELECT sql_text
FROM v$sqlarea
WHERE (v$sqlarea.address, v$sqlarea.hash_value) IN (
SELECT sql_address, sql_hash_value
FROM v$session
WHERE SID IN (
SELECT SID
FROM v$session a, x$kglpn b
WHERE a.saddr = b.kglpnuse
AND b.kglpnmod <> 0
AND b.kglpnhdl IN (SELECT p1raw
FROM v$session_wait
WHERE event LIKE 'library%')))
/

SQL_TEXT
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
BEGIN calling; END;

在grant之前和之后我们可以转储一下shared pool的内容观察比较一下:

SQL> ALTER SESSION SET EVENTS 'immediate trace name LIBRARY_CACHE level 32';
Session altered.
在grant之前:
从前面的查询获得pining的Handle是52D6730C:

**
BUCKET 67790:
LIBRARY OBJECT HANDLE: handle=52d6730c
name=SYS.PINING
hash=891b08ce timestamp=09-06-2004 16:43:51
namespace=TABL/PRCD/TYPE flags=KGHP/TIM/SML/[02000000]
kkkk-dddd-llll=0000-0011-0011 lock=N pin=S latch#=1
- - 在Object上存在共享pin
- - 在handle上存在Null模式锁定,此模式允许其他用户继续以Null/shared模式锁定该对象
lwt=0x52d67324[0x52d67324,0x52d67324] ltm=0x52d6732c[0x52d6732c,0x52d6732c]
pwt=0x52d6733c[0x52b2a4e8,0x52b2a4e8] ptm=0x52d67394[0x52d67394,0x52d67394]
ref=0x52d67314[0x52d67314, 0x52d67314] lnd=0x52d673a0[0x52d67040,0x52d6afcc]
LIBRARY OBJECT: object=52d65ba4
type=PRCD flags=EXS/LOC[0005] pflags=NST [01] status=VALD load=0
DATA BLOCKS:
data# heap pointer status pins change alloc(K) size(K)
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
0 52d65dac 52d65c90 I/P/A 0 NONE 0.30 0.55
4 52d65c40 52d67c08 I/P/A 1 NONE 0.44 0.48

在发出grant命令后:

**
BUCKET 67790:
LIBRARY OBJECT HANDLE: handle=52d6730c
name=SYS.PINING
hash=891b08ce timestamp=09-06-2004 16:43:51
namespace=TABL/PRCD/TYPE flags=KGHP/TIM/SML/[02000000]
kkkk-dddd-llll=0000-0011-0011 lock=X pin=S latch#=1
- - 由于calling执行未完成,在object上仍让保持共享pin
- - 由于grant会导致重新编译该对象,所以在handle上的排他锁已经被持有
- - 进一步的需要获得object上的Exclusive pin,由于shared pin被calling持有,所以library cache pin等待出现.
lwt=0x52d67324[0x52d67324,0x52d67324] ltm=0x52d6732c[0x52d6732c,0x52d6732c]
pwt=0x52d6733c[0x52b2a4e8,0x52b2a4e8] ptm=0x52d67394[0x52d67394,0x52d67394]
ref=0x52d67314[0x52d67314, 0x52d67314] lnd=0x52d673a0[0x52d67040,0x52d6afcc]
LIBRARY OBJECT: object=52d65ba4
type=PRCD flags=EXS/LOC[0005] pflags=NST [01] status=VALD load=0
DATA BLOCKS:
data# heap pointer status pins change alloc(K) size(K)
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
0 52d65dac 52d65c90 I/P/A 0 NONE 0.30 0.55
4 52d65c40 52d67c08 I/P/A 1 NONE 0.44 0.48
实际上recompile过程包含以下步骤,我们看一下lock和pin是如何交替发挥作用的:
1.存储过程的library cache object以排他模式被锁定,这个锁定是在handle上获得的exclusive锁定可以防止其他用户执行同样的操作,同时防止其他用户创建新的引用此过程的对象.
2.以shared模式pin该对象,以执行安全和错误检查.
3.共享pin被释放,重新以排他模式pin该对象,执行重编译.
4.使所有依赖该过程的对象失效
5.释放exclusive lock和exclusive pin

(二).LIBRARY CACHE LOCK等待事件
如果此时我们再发出一条grant或compile的命令,那么library cache lock等待事件将会出现:
session 3:

[oracle@jumper oracle]$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.3.0 - Production on Tue Sep 7 17:05:25 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production

SQL> alter procedure pining compile;

此进程挂起,我们查询v$session_wait视图可以获得以下信息:

SQL> select * from v$session_wait;

SID SEQ# EVENT P1TEXT P1 P1RAW P2TEXT P2 P2RAW
P3TEXT P3 P3RAW WAIT_TIME SECONDS STATE
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
11 143 library cache pin handle address 1390239716 52DD5FE4 pin address 1387617456 52B55CB0
100*mode+namespace 301 0000012D 0 6 WAITING
13 18 library cache lock handle address 1390239716 52DD5FE4 lock address 1387433984 52B29000
100*mode+namespace 301 0000012D 0 3 WAITING
8 415 PL/SQL lock timer duration 120000 0001D4C0 0 00
0 00 0 63 WAITING
….

13 rows selected

由于handle上的lock已经被session 2以exclusive模式持有,所以session 3产生了等待.
我们可以看到,在生产数据库中权限的授予、对象的重新编译都可能会导致library cache pin等待的出现.所以应该尽量避免在高峰期进行以上操作.
另外我们测试的案例本身就说明:如果Package或过程中存在复杂的、交互的依赖以来关系极易导致library cache pin的出现.所以在应用开发的过程中,我们也应该注意这方面的内容.

library cache lock和library cache pin到底是什么

可能有很多朋友从来就没有搞清楚过到底什么是library cache lock和library cache pin,它们到底是enqueue还是latch?它们的作用是什么?
这里我尝试对上述问题做一番解释,这些解释可能是有问题的,因为里面包含了我的一些猜测。
最近连续写了一些基于我的猜测、没有确凿证据的文章,这也许不太合适。

我们通常说的library cache lock和library cache pin是enqueue,不是latch,它们是两种DDL lock。但需要我们注意的是,在11gR1之前,Oracle中又存在名为library cache lock和library cache pin的latch。

是不是感觉很混乱?没关系,我们一点一点往下看。很抱歉,我这里引用了大量英文,因为我觉得如果翻译出来就失去了原先的味道。

1、 作为enqueue,library cache lock和library cache pin的作用是什么?
Both library cache lock and library cache pin are provided to access objects in the library cache.Library cache lock manages concurrency between processes, whereas library cache pin manages cache coherence. In order to access an object in library cache, a process must first lock the library cache object handle, and then pin the object data heap itself. Requests for both library cache lock and library cache pin will wait until granted. This is a possible source of contention, because there is no NOWAIT request mode.
By acquiring a library cache lock on the library cache object handle, a process can prevent other processes from accessing the object, or even finding out what type it is. It can even maintain a dependency on an object without preventing other processes from accessing the object. Acquiring a library cache lock is also the only way to locate an object in cache—a process locates and locks an object in a single operation.
If the process wants to actually examine or modify the object, then it must acquire a library cache pin on the object data heap itself (after acquiring a library cache lock on the library cache object handle).Pinning the object causes information about the object and its data heaps to be loaded into memory if they were not already there. This information is guaranteed to remain in memory at least until the pin is released. Locks and pins are externalized in X$KGLLK and X$KGLPN, respectively.

2、 作为enqueue,library cache lock和library cache pin有哪几种lock mode?
a) Library cache lock有三种lock mode,分别是share、exclusive和null。A process acquires a share library cache lock if it intends only to read the object. For example, it wants to reference the object during compilation. A process acquires an exclusive library cache lock if it intends to create or modify the object. For example, it wants to drop the object from the database. Null library cache locks are a special case. They are acquired on objects that are to be executed like child cursor, procedure, function, package, or type body. You can use them to maintain an interest on an object for a long period of time (session persistency), and to detect if the object becomes invalid. You can break null library cache lock at any time. This is used as a mechanism to notify a session that an executable object is no longer valid. If a null library cache lock is broken, and thus the object is invalidated, then it is an indication to the user who was holding the null library cache lock that the object needs to be recompiled. A null library cache lock is acquired during the parse phase of SQL statement execution and is held as long as the shared SQL area for that statement remains in the shared pool. A null library cache lock does not prevent any DDL operation, and can be broken to allow conflicting DDL operations, hence the term “breakable parse lock.” A null library cache lock on an object is broken when there is an exclusive library cache pin on the object.
b) Library cache pin有两种lock mode,分别是share和exclusive。 When a process pins an object data heap that is not in memory, the process can determine whether the data heap is to be loaded in the PGA or SGA. An object must be pinned in Exclusive mode if it is to be modified. However, the process first will always pin the object in Share mode, examine it for errors and security checks, and then, if necessary, (such as needing modification) pin it in Exclusive mode. An object is never pinned in Exclusive mode if only read access is required.This is because all dependent transient objects (cursors) are invalidated (null locks broken) when an object is unpinned from Exclusive mode. The effect would be unnecessary recompilation and reparsing of all dependent packages, procedures, and functions.

3、 作为latch,library cache lock和library cache pin的作用是什么?
这是一个很纠结的问题,既然已经有了作为enqueue的library cache lock和library cache pin,为什么在11gR1以前,Oracle里还有同名latch,而且明显这些同名latch是在被使用:
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.5.0
Connected as ipra

SQL> select name,level#,gets,misses,sleeps,immediate_gets,immediate_misses from v$latch where name like ‘library%’;

NAME LEVEL# GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES
————————————————– ———- ———- ———- ———- ————– —————-
library cache 5 9221760 1608 800 2596 76766
library cache lock 6 13548247 582 6 0 0
library cache lock allocation 3 208273 0 0 0 0
library cache pin 6 4207462 193 0 2 0
library cache hash chains 9 0 0 0 0 0
library cache pin allocation 3 57276 0 0 0 0
library cache load lock 5 24848 0 0 1 0

7 rows selected
从结果里我们可以看到,对于10.2.0.5而言,Oracle存在7种跟library cache相关的latch,除了library cache hash chains latch之外,其他的跟library cache相关的latch,Oracle都有使用。

那么library cache lock latch、library cache pin latch以及大家最耳熟能详的library cache latch等等,这些latch是做什么用的呢?
也许我们可以从下面的一段文字中找到答案:
The library cache latches serialize access to the objects in the library cache. Access to library cache objects always occurs through library cache locks. Because locking an object is not an atomic instruction, a library cache latch is acquired before the library cache lock request and is released after it. For most operations, the library cache latches are used, and therefore they can become a point of contention.
If an object is not in memory, then a library cache lock cannot be acquired on it. In order to prevent multiple processes to request the load of the same object simultaneously, another latch must be acquired before the load request. This is the library cache load lock latch. The library cache load lock latch is taken and held until a library cache load lock is allocated, then the latch is released. Loading of the object is performed under the library cache load lock and not under the library cache load lock latch as it may take quite a long time.

这里提到了几点值得我们关注:
a) Oracle使用上述library cache latches(包括library cache latch、library cache lock latch、library cache pin latch、library cache pin allocation latch、library cache load lock latch)的目的是控制并发访问library cache object所需要的相关的enqueue或者是为了控制并发访问library cache中的相关的内存结构,比如用相关的library cache lock latch控制并发获得library cache lock。这里我猜测Oracle用library cache lock latch控制并发获得library cache lock,用library cache pin latch控制并发获得library cache pin,用library cache load lock latch控制并发获得library cache load lock,用library cache latch去控制并发访问library cache object handle中的某些结构,如library cache object handle中的flag中的special status flag (special status flags are protected by the library cache latch. Examples of these flags indicate that: The object is valid; The object is authorized; The object has compilation errors)。
b) Library cache load lock是另外一种enqueue。The session tries to find the library cache load lock for the database object so that it can load the object. The library cache load lock is always obtained in Exclusive mode, so that no other process can load the same object. If the library cache load lock is busy the session will wait on this event until the lock becomes available.

好了,现在我们来验证一下,还是上述10.2.0.5的环境,我将上述sql(select name,level#,gets,misses,sleeps,immediate_gets,immediate_misses from v$latch where name like ‘library%’)马上再执行一遍,这是软解析,必然要获得library cache lock,不需要获得library cache load lock,所以对应的latch应该表现为library cache lock latch的gets增加,library cache load lock latch的gets不变:
SQL> select name,level#,gets,misses,sleeps,immediate_gets,immediate_misses from v$latch where name like ‘library%’;

NAME LEVEL# GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES
————————————————– ———- ———- ———- ———- ————– —————-
library cache 5 9222166 1608 800 2596 76766
library cache lock 6 13548760 582 6 0 0
library cache lock allocation 3 208287 0 0 0 0
library cache pin 6 4207656 193 0 2 0
library cache hash chains 9 0 0 0 0 0
library cache pin allocation 3 57278 0 0 0 0
library cache load lock 5 24848 0 0 1 0

7 rows selected
从结果里我们可以看到,library cache lock latch的gets从13548247递增到了13548760,library cache pin latch的gets从4207462递增到了4207656,但library cache load lock latch的gets还是保持24848不变。

现在我们来让library cache load lock latch的gets发生变化,这是非常容易的事情,我们只需要执行一个需要硬解析的sql就可以了:
SQL> select * from scott.emp_temp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ISINSPECT
—– ———- ——— —– ———– ——— ——— —— ———-

SQL> select name,level#,gets,misses,sleeps,immediate_gets,immediate_misses from v$latch where name like ‘library%’;

NAME LEVEL# GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES
————————————————– ———- ———- ———- ———- ————– —————-
library cache 5 9223549 1608 800 2596 76766
library cache lock 6 13550296 582 6 0 0
library cache lock allocation 3 208348 0 0 0 0
library cache pin 6 4208118 193 0 2 0
library cache hash chains 9 0 0 0 0 0
library cache pin allocation 3 57294 0 0 0 0
library cache load lock 5 24856 0 0 1 0

7 rows selected
由于我们执行了一个需要硬解析的sql,导致Oracle需要获得library cache load lock以便load相关信息到这个sql的子cursor的heap 6中,而要获得library cache load lock,必须先持有library cache load lock latch。从上述结果中我们可以看到,此时library cache load lock latch的gets已经发生了变化,从24848递增到了24856。

接下来我们再来看一看上述library cache latches的子latch情况:
SQL> show parameter cpu_count

NAME TYPE VALUE
———————————— ———– ——————————
cpu_count integer 2

这里cpu的个数为2,显然上述library cache latches的子latch应该为3:
SQL> select name,level#,gets,misses,sleeps,immediate_gets,immediate_misses from v$latch_children where name like ‘library%’;

NAME LEVEL# GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES
————————————————– ———- ———- ———- ———- ————– —————-
library cache 5 3274551 1301 94 187 0
library cache 5 2218356 116 80 933 0
library cache 5 3731320 191 626 1476 76766
library cache lock 6 5339737 362 3 0 0
library cache lock 6 6223353 194 3 0 0
library cache lock 6 1987799 26 0 0 0
library cache pin 6 1484918 184 0 0 0
library cache pin 6 891695 3 0 2 0
library cache pin 6 1831837 6 0 0 0
library cache pin allocation 3 23177 0 0 0 0
library cache pin allocation 3 8272 0 0 0 0
library cache pin allocation 3 25849 0 0 0 0
library cache lock allocation 3 75900 0 0 0 0
library cache lock allocation 3 28229 0 0 0 0
library cache lock allocation 3 104237 0 0 0 0
library cache hash chains 9 0 0 0 0 0
library cache hash chains 9 0 0 0 0 0
library cache hash chains 9 0 0 0 0 0

18 rows selected
注意,结果里并没有library cache load lock latch,说明library cache load lock latch没有children,它是一个solitary类型的latch。

从10.2.0.2开始,Oracle将_kks_use_mutex_pin的默认值改成了true,这意味着从10.2.0.2开始,Oracle里将再不会有针对cursor的library cache pin等待,取而代之的是mutex等待,具体表现为cursor: pin *等待,如cursor: pin S wait on X。
这里需要我们了解的是:
a) 从10.2.0.2开始,Oracle只是用mutex替代了针对cursor的library cache pin,这并不代表从10.2.0.2开始Oracle里就没有library cache pin等待了。比如这个例子里的library cache pin等待就发生在10.2.0.4中:http://www.dbsnake.net/solve-library-cache-pin.html

b) Mutex和latch是互相独立,没有任何关系的:Latches and mutexes are independent mechanisms i.e. a process can hold a latch and a mutex at the same time. In the case of process death, latches are always cleaned up before mutexes. There is no generic mutex deadlock detection (unlike latches). There is no mutex/latch hierarchy.

从11gR1开始,Oracle用mutex替换了library cache latches,并引了一个新的等待事件:library cache: mutex *,我们来看一下这个知识点:
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as nbs

SQL> select name,level#,gets,misses,sleeps,immediate_gets,immediate_misses from v$latch where name like ‘library%’;

NAME LEVEL# GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES
—————————————————————- ———- ———- ———- ———- ————– —————-
library cache load lock 5 0 0 0 0 0

SQL> select name,level#,gets,misses,sleeps,immediate_gets,immediate_misses from v$latch_children where name like ‘library%’;

NAME LEVEL# GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES
—————————————————————- ———- ———- ———- ———- ————– —————-

从结果里我们可以看到,在11.2.0.1里,各种library cache latches都没有了,只剩下了library cache load lock latch,而且Oracle还没有使用这个latch,因为gets是0。
3 Comments on “library cache lock和library cache pin到底是什么”
wxjzqymtl
December 17, 2011 at 5:00 pm
有两个问题想请教博主
1.在dsi405中提到shared cursor包括sql和pl/sql对象,我理解的pl/sql对象不就是package,function这些吗,而dsi中也指出还有一类存储对象procedure和function等。我就疑惑了这些package,function到底是属于存储对象还是过渡对象
2.文章中提到当加载database object时会用到library cache load lock,以及在举例中引用了硬解析来说明会加载相关信息到child cursor的heap 6中也会用到library cache load lock。我想问的是此lock的使用时机是只在load数据库对象时才使用还是load所有对象(包括游标对象)时都是用
Reply
cui hua
December 17, 2011 at 8:18 pm
1、Shared cursor中包括的pl/sql对象是指匿名pl/sql对象
2、library cache load lock用于防止并发的load同样内容到同一个地方,不是仅用于shared cursor
Reply
wxjzqymtl
December 19, 2011 at 12:37 am
谢谢博主的回复
library cache lock和library cache pin到底是什么(续)
Posted: December 16, 2011 | Author: Cui Hua | Filed under: Oracle | Tags: library cache lock and pin | 2Comments »
这篇文章是“library cache lock和library cache pin到底是什么”的姊妹篇,在这篇文章里,我们通过测试得到了如下结论:
1、 针对cursor的library cache lock的lock mode确实是null,无论该cursor所对应的sql是硬解析还是软解析;
2、 MOS上说Oracle说从10.2.0.2以后,会用mutex取代针对cursor的library cache pin,但我的测试结果是在10.2.0.5中,虽然在sql的软解析时确实已经不存在library cache pin,但如果是硬解析,依然存在library cache pin;
3、 sql的软解析时,library cache pin的lock mode始终是S;
4、 sql的硬解析时,library cache pin的lock mode一般是X,但在10.2.0.1中,即使是硬解析,也存在lock mode为S的library cache pin。

这里测试所采用的方法就是event 10049,这个事件在10gR2以后,专门被用来trace library cache lock和library cache pin。但好多朋友不太会用这个事件,我这里以一个实例的方式介绍了如何用10049事件来trace单个sql的library cache lock和library cache pin。

我们先从10.2.0.1开始说起:
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as SYS

10.2.0.1中_kks_use_mutex_pin的值为false,表示Oracle不会用mutex取代针对cursor的library cache pin:
SQL> select name,value,description from sys.all_parameters where name like ‘_kks%’;

NAME VALUE DESCRIPTION
—————————— ———- ————————————————–
_kks_use_mutex_pin FALSE Turning on this will make KKS use mutex for cursor pins.

SQL> select * from scott.emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
—– ———- ——— —– ———– ——— ——— ——
7981 CUIHUA 7981
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
……省略显示部分内容
7800 JAME3 CLERK 7698 1981-12-3 950.00 30

13 rows selected

SQL> select hash_value,sql_text from v$sqlarea where sql_text like ‘select * from scott.emp%’;

HASH_VALUE SQL_TEXT
———- ——————————————————————————–
52404428 select * from scott.emp

SQL> select to_char(52404428,’XXXXXXXX’) from dual;

TO_CHAR(52404428,’XXXXXXXX’)
—————————-
31FA0CC

现在我们要来trace针对上述sql的library cache pin和library cache lock,方法我之前已经说了,就是用event 10049,用10049的难点在于如何确定level。
确定10049针对单个sql的level值的算法如下:
首先,10049的level可能会有如下一些值:
#define KGLTRCLCK 0×0010 /* trace lock operations */
#define KGLTRCPIN 0×0020 /* trace pin operations */
#define KGLTRCOBF 0×0040 /* trace object freeing */
#define KGLTRCINV 0×0080 /* trace invalidations */
#define KGLDMPSTK 0×0100 /* DUMP CALL STACK WITH TRACE */
#define KGLDMPOBJ 0×0200 /* DUMP KGL OBJECT WITH TRACE */
#define KGLDMPENQ 0×0400 /* DUMP KGL ENQUEUE WITH TRACE */
#define KGLTRCHSH 0×2000 /* DUMP BY HASH VALUE */

其次,我们是要针对单个sql,所以需要用到这个sql的hash value,以便将10049和这个sql联系起来,即我们一定要用到KGLTRCHSH,也就是0×2000;
另外我们是要trace library cache lock和library cache pin,所以我们一定要用到KGLTRCLCK和KGLTRCPIN,即0×0010和0×0020;
最后就是我们需要把这个sql的hash value的16进制的后两个byte拿出来,作为10049的level的前缀。

从上面结果中我们可以看到,select * from scott.emp的hash value的16进制的后两个byte是0xA0CC。另外KGLTRCHSH | KGLTRCLCK | KGLTRCPIN = 0×2000 | 0×0010 | 0×0020 = 0×2030。按照上述算法,select * from scott.emp的10049的最终level值就是0xa0cc2030,也就是2697732144:
SQL> select to_number(‘a0cc2030′,’XXXXXXXXXXXX’) from dual;

TO_NUMBER(‘A0CC2030′,’XXXXXXXX
——————————
2697732144

现在我们设置好10049后再执行一遍上述sql,以观察10.2.0.1下sql的软解析时library cache pin和library cache lock:
SQL> oradebug setmypid
已处理的语句
SQL> oradebug event 10049 trace name context forever,level 2697732144
已处理的语句

SQL> select * from scott.emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
—– ———- ——— —– ———– ——— ——— ——
7981 CUIHUA 7981
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
……省略显示部分内容
7800 JAME3 CLERK 7698 1981-12-3 950.00 30

13 rows selected

SQL> oradebug tracefile_name
d:\oracle\admin\cuihua\udump\cuihua_ora_5808.trc

相应的trace文件(d:\oracle\admin\cuihua\udump\cuihua_ora_5808.trc)的内容为:
*** 2011-06-01 11:59:35.500
KGLTRCLCK kglget hd = 0x33938118 KGL Lock addr = 0x3174A99C mode = N
KGLTRCLCK kglget hd = 0x33938034 KGL Lock addr = 0x31716F50 mode = N
KGLTRCPIN kglpin hd = 0×33938034 KGL Pin addr = 0x31718A28 mode = S
KGLTRCPIN kglpndl hd = 0×33938034 KGL Pin addr = 0x31718A28 mode = S
KGLTRCLCK kgllkdl hd = 0×33938034 KGL Lock addr = 0x31716F50 mode = N
KGLTRCLCK kgllkdl hd = 0×33938118 KGL Lock addr = 0x3174A99C mode = N

hd = 0×33938118所对应的library cache object的name就是select * from scott.emp:
SQL> select sql_text from v$sqlarea where address=’33938118′;

SQL_TEXT
——————————————————————————–
select * from scott.emp

hd = 0×33938034就是hd = 0×33938118的子cursor:
SQL> select kglhdadr,kglhdpar,kglnaobj from x$kglob where kglhdadr=’33938034′;

KGLHDADR KGLHDPAR KGLNAOBJ
——– ——– ——————————————————————————–
33938034 33938118 select * from scott.emp

很明显,从上述trace文件中我们可以得出如下结论:
1、10.2.0.1中,sql软解析时,针对cursor的library cache lock的lock mode确实是null;
2、10.2.0.1中,sql软解析时,针对cursor的library cache pin的lock mode确实是S;

现在我们来观察10.2.0.1下sql的硬解析时library cache pin和library cache lock:
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。

Total System Global Area 608174080 bytes
Fixed Size 1250404 bytes
Variable Size 318770076 bytes
Database Buffers 281018368 bytes
Redo Buffers 7135232 bytes
数据库装载完毕。
数据库已经打开。

SQL> select hash_value,sql_text from v$sqlarea where sql_text like ‘select * from scott.emp%’;

HASH_VALUE SQL_TEXT
———- ——————————————————————————–

SQL> oradebug setmypid
已处理的语句
SQL> oradebug event 10049 trace name context forever,level 2697732144
已处理的语句
SQL> select * from scott.emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
—– ———- ——— —– ———– ——— ——— ——
7981 CUIHUA 7981
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
……省略显示部分内容
7800 JAME3 CLERK 7698 1981-12-3 950.00 30

13 rows selected

SQL> oradebug tracefile_name
d:\oracle\admin\cuihua\udump\cuihua_ora_5016.trc

相应的trace文件(d:\oracle\admin\cuihua\udump\cuihua_ora_5016.trc)的内容为:
KGLTRCLCK kglget hd = 0x206ECF90 KGL Lock addr = 0x3174E068 mode = N
KGLTRCPIN kglpin hd = 0x206ECF90 KGL Pin addr = 0x317187C0 mode = X
KGLTRCPIN kglpndl hd = 0x206ECF90 KGL Pin addr = 0x317187C0 mode = X
KGLTRCLCK kglget hd = 0x33B19238 KGL Lock addr = 0x3174E618 mode = N
KGLTRCPIN kglpin hd = 0x33B19238 KGL Pin addr = 0x31717F28 mode = X
KGLTRCPIN kglpndl hd = 0x33B19238 KGL Pin addr = 0x31717F28 mode = S
KGLTRCLCK kgllkdl hd = 0x33B19238 KGL Lock addr = 0x3174E618 mode = N
KGLTRCLCK kgllkdl hd = 0x206ECF90 KGL Lock addr = 0x3174E068 mode = N

SQL> select kglhdadr,kglhdpar,kglnaobj from x$kglob where kglhdadr=’33B19238′;

KGLHDADR KGLHDPAR KGLNAOBJ
——– ——– ——————————————————————————–
33B19238 206ECF90 select * from scott.emp

很明显,从上述trace文件中我们可以得出如下结论:
1、10.2.0.1中,sql硬解析时,针对cursor的library cache lock的lock mode依然是null;
2、10.2.0.1中,sql硬解析时,针对cursor的library cache pin的lock mode一般是X,但也存在lock mode为S的library cache pin,且这个S是针对子cursor的。

好了,10.2.0.1就告一段落,我们现在来看看10.2.0.5:
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.5.0
Connected as SYS

MOS上说:从10.2.0.2开始,Oracle将_kks_use_mutex_pin的默认值改成了true,表明Oracle将用mutex替代针对cursor的library cache pin。
但实际情况并不完全是这样,详情见后面的测试:
SQL> select name,value,description from sys.all_parameters where name like ‘_kks%’;

NAME VALUE DESCRIPTION
—————————— ———- ————————————————–
_kks_use_mutex_pin TRUE Turning on this will make KKS use mutex for cursor pins.

SQL> select * from scott.emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
———- ———- ——— ———- ——— ———- ———- ———-
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
……省略显示部分内容
7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

SQL> select hash_value,sql_text from v$sqlarea where sql_text like ‘select * from scott.emp%’;

HASH_VALUE SQL_TEXT
———- ——————————–
52404428 select * from scott.emp

SQL> oradebug setmypid
Statement processed.
SQL> oradebug event 10049 trace name context forever,level 2697732144
Statement processed.

SQL> select * from scott.emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
———- ———- ——— ———- ——— ———- ———- ———-
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
……省略显示部分内容
7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

SQL> oradebug tracefile_name
/u01/app/oracle/admin/testdb/udump/testdb_ora_1237156.trc

$ cat /u01/app/oracle/admin/testdb/udump/testdb_ora_1237156.trc
/u01/app/oracle/admin/testdb/udump/testdb_ora_1237156.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/10.2.0
System name: AIX
Node name: P550_03_LD
Release: 3
Version: 5
Machine: 0001DA17D600
Instance name: testdb
Redo thread mounted by this instance: 1
Oracle process number: 15
Unix process pid: 1237156, image: oracle@P550_03_LD (TNS V1-V3)

* 2011-06-01 13:38:07.949
* ACTION NAME:() 2011-06-01 13:38:07.944
* MODULE NAME:(sqlplus@P550_03_LD (TNS V1-V3)) 2011-06-01 13:38:07.944
* SERVICE NAME:(SYS$USERS) 2011-06-01 13:38:07.944
*** SESSION ID:(146.3) 2011-06-01 13:38:07.944
KGLTRCLCK kgllkal hd = 0x700000022595c38 KGL Lock addr = 0x70000001f724d78 mode = N
KGLTRCLCK kglget hd = 0x700000022595c38 KGL Lock addr = 0x70000001f724d78 mode = N
KGLTRCLCK kgllkal hd = 0x7000000226ec4f8 KGL Lock addr = 0x70000001f74e128 mode = N
KGLTRCLCK kgllkdl hd = 0x7000000226ec4f8 KGL Lock addr = 0x70000001f74e128 mode = N
KGLTRCLCK kgllkdl2 hd = 0x7000000226ec4f8 KGL Lock addr = 0x70000001f74e128 mode = 0
KGLTRCLCK kgllkdl hd = 0x700000022595c38 KGL Lock addr = 0x70000001f724d78 mode = N
KGLTRCLCK kgllkdl2 hd = 0x700000022595c38 KGL Lock addr = 0x70000001f724d78 mode = 0
这里mode=0应该是表示调用kgllkdl2所产生的library cache lock在调用完上述方法后已经释放了。

SQL> select kglhdadr,kglhdpar,kglnaobj from x$kglob where lower(kglhdadr)=’07000000226ec4f8′;

KGLHDADR KGLHDPAR KGLNAOBJ
—————- —————- ——————————————————————————–
07000000226EC4F8 0700000022595C38 select * from scott.emp

很明显,从上述trace文件中我们可以得出如下结论:
10.2.0.5中,sql软解析时,针对cursor的library cache pin确实已经不存在;

现在我们来观察10.2.0.5下sql的硬解析时library cache pin和library cache lock:
$ sqlplus ‘/ as sysdba’;

SQL*Plus: Release 10.2.0.5.0 – Production on Wed Jun 1 13:42:11 2011

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 314572800 bytes
Fixed Size 2096032 bytes
Variable Size 96470112 bytes
Database Buffers 209715200 bytes
Redo Buffers 6291456 bytes
Database mounted.
Database opened.

SQL> oradebug setmypid
Statement processed.
SQL> oradebug event 10049 trace name context forever,level 2697732144
Statement processed.

SQL> select * from scott.emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
———- ———- ——— ———- ——— ———- ———- ———-
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
……省略显示部分内容
7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

SQL> oradebug tracefile_name
/u01/app/oracle/admin/testdb/udump/testdb_ora_1536246.trc

$ cat /u01/app/oracle/admin/testdb/udump/testdb_ora_1536246.trc
/u01/app/oracle/admin/testdb/udump/testdb_ora_1536246.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/10.2.0
System name: AIX
Node name: P550_03_LD
Release: 3
Version: 5
Machine: 0001DA17D600
Instance name: testdb
Redo thread mounted by this instance: 1
Oracle process number: 15
Unix process pid: 1536246, image: oracle@P550_03_LD (TNS V1-V3)

* ACTION NAME:() 2011-06-01 13:42:44.913
* MODULE NAME:(sqlplus@P550_03_LD (TNS V1-V3)) 2011-06-01 13:42:44.913
* SERVICE NAME:(SYS$USERS) 2011-06-01 13:42:44.913
* SESSION ID:(159.3) 2011-06-01 13:42:44.913
DBRM(kskinitrm) cpu_count : old(0) -> new(2)
kwqmnich: current time:: 5: 42: 44
kwqmnich: instance no 0 check_only flag 1
kwqmnich: initialized job cache structure
*** 2011-06-01 13:44:13.657
KGLTRCLCK kgllkal hd = 0x7000000225ccfa8 KGL Lock addr = 0x70000001f725560 mode = N
KGLTRCLCK kglget hd = 0x7000000225ccfa8 KGL Lock addr = 0x70000001f725560 mode = N
KGLTRCPIN kglpin hd = 0x7000000225ccfa8 KGL Pin addr = 0x70000001f726378 mode = X
KGLTRCPIN kglpndl hd = 0x7000000225ccfa8 KGL Pin addr = 0x70000001f726378 mode = X
KGLTRCLCK kgllkal hd = 0x7000000225abf18 KGL Lock addr = 0x70000001f733120 mode = N
KGLTRCLCK kglget hd = 0x7000000225abf18 KGL Lock addr = 0x70000001f733120 mode = N
KGLTRCPIN kglpin hd = 0x7000000225abf18 KGL Pin addr = 0x70000001f726840 mode = X
KGLTRCPIN kglpndl hd = 0x7000000225abf18 KGL Pin addr = 0x70000001f726840 mode = X
KGLTRCLCK kgllkdl hd = 0x7000000225abf18 KGL Lock addr = 0x70000001f733120 mode = N
KGLTRCLCK kgllkdl2 hd = 0x7000000225abf18 KGL Lock addr = 0x70000001f733120 mode = 0
KGLTRCLCK kgllkdl hd = 0x7000000225ccfa8 KGL Lock addr = 0x70000001f725560 mode = N
KGLTRCLCK kgllkdl2 hd = 0x7000000225ccfa8 KGL Lock addr = 0x70000001f725560 mode = 0

SQL> select kglhdadr,kglhdpar,kglnaobj from x$kglob where lower(kglhdadr)=’07000000225abf18′;

KGLHDADR KGLHDPAR KGLNAOBJ
—————- —————- ——————————————————————————–
07000000225ABF18 07000000225CCFA8 select * from scott.emp

很明显,从上述trace文件中我们可以得出如下结论:
1、10.2.0.5中,sql硬解析时,依然存在library cache pin;
2、10.2.0.5中,sql硬解析时,针对cursor的library cache pin的lock mode始终是X;

2 Comments on “library cache lock和library cache pin到底是什么(续)”
wxjzqymtl
December 25, 2011 at 12:35 am
首先非常博主的这篇文章,最近一直想搞清楚在sql语句被解析与执行阶段
对应的lock与pin的模式以及引用的数据库对象的lock和pin的模式分别是什么,
您的这篇文章给了我一个清晰的思路,我想向您确认两个问题
1.10049事件对lock,pin模式的跟踪周期是从sql开始解析一直到执行结束吗?
因为通过10049事件trace的最后结果是无论软,硬解析lock,pin的模式最后分别都是N和S;
可是一个sql语句执行结束后他的lock和pin的模式都会变为0,也就是释放锁资源,这个结果
是通过library_cache event看到的,还是说10049只跟踪从解析开始到结束(除锁资源释放的那个过程)
2.您的这个案例中只是针对cursor类对象来跟踪其解析与执行阶段lock,pin模式的不断变化,如果
我还想跟踪相应的游标中引用的对象的lock,pin模式的变化的话有办法吗?
Reply
cui hua
December 30, 2011 at 10:44 pm
1、10049只跟踪从解析开始到执行结束中间的过程,如果要查看cursor的lock和pin在执行结束后的状态,可以对相关library cache做dump
2、10049是用来跟踪一些基本的library cache function(如pin、lock等),不仅限于cursor
Reply

oracle等待事件6——行高速缓存上的等待事件

分类: oracle管理总结 2012-09-12 20:39 954人阅读 评论(0) 收藏 举报
oraclecachedictionaryobjectsql
1、row cache lock
oracle将数据子典信息存于SGA内的行高速缓冲区(或dictionary cache),行高速缓冲区位于共享池内,可以通过如下命令进行确认:
SQL> select pool,name,bytes from v$sgastat where name='row cache';

POOL NAME BYTES
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
shared pool row cache 3707272
想要修改数据字典内容的进程,应该对其相应的row cache object 获得 row cache lock。其中最具代表性的是sequence,关于sequence的详细介绍,参照博客:http://blog.csdn.net/changyanmanman/article/details/7451009
在获取sequence的nextval过程中,需要修改数据字典信息时,应该对row cache object以SSX(shared sub-exclusive,这个模式就是,对于整个对象是以shared 模式,对于对象的一部分是以exclusive模式获取锁的一种模式。因sequence.nextval 调用修改sequence数据字典信息时,不是修改sequence自身。而只是修改sequence的“接下来的”值。因此,以SSX模式获得 row cache lock) 模式获得row cahce lock。SSX模式之间因为不存在共享性,所以多个进程同时对相同的sequence调用nextval时,发生对于row cache lock的争用。若在获得row cache lock 过程中发生争用,则等待row cache lock事件,row cache lock事件的P1=cache#,表示的不是对象信息,而是row cache 的类型。

ROW CACHE LOCK等待事件是一个共享池相关的等待事件。是由于对于字典缓冲的访问造成的。
P1 - Cache Id
P2 - Mode Held
P3 - Mode Requested
mode 和REQUEST的取值:
KQRMNULL 0 null mode - not locked
KQRMS 3 share mode
KQRMX 5 exclusive mode
KQRMFAIL 10 fail to acquire instance lock
如果是RAC/OPS(ops的意思是oracle并行服务)环境,前台进程发出锁请求,LCK0进程发出锁请求。如果是单实例模式,由前台进程直接发出锁请求。
在RAC/OPS环境下,前台进程会循环等待锁的获取,最多会等待60秒钟。在单实例环境,前台进程会循环1000次,等待3秒钟。PMON进程无论在哪种模式,都会等待5秒钟。
要注意的是单实例模式下和多实例模式下申请该锁调用的模块是不同的(kqrget()- 单实例,kqgigt()- 多实例)。
如果发现这个等待十分高,一般来说可能由于2种原因:
一是共享池太小了,需要增加共享池;
另外一种情况是SQL分析过于频繁,对于共享池的并发访问量过大。
对于任何一种情况,绝大多数情况下加大共享池会有助于降低该等待,不过加大共享池的时候也要注意,并不一定所有的情况下增加共享池都会有明显的效果,特别是对于第二种情况,精确的分析十分重要。另外进一步分析,弄清楚哪些ROW CACHE的等待最为严重,有助于解决问题。
比如说如果发现dc_sequences等待比较严重,那么单纯的增加共享池的大小是起不到应有的作用的,而是要通过优化SEQUENCE的访问性能(比如CACHE,NOORDER等)来达到目的。对于早期的版本(7,8.0),SEQUENCE_CACHE_ENTRIES参数的调整也十分关键。
我们可以对row cache 文件进行转储,执行命令:
SQL> alter session set events 'immediate trace name row_cache level 12';
转储文件的有效信息如下:

除了sequence之外,机会没有如此频繁的修改行高速缓冲区的信息的。因此,出现row cache 等待时,需要确认sequence上是否赋予了nocache属性,ops环境下,为了保障sequence的顺序,以nocache属性创建了sequence时,经常出现row cache lock等待现象。rac环境下,使用cache属相同时,保障各节点之间sequence的顺序是可能的。

2、enq:SQ-contention, DFS lock handle(SV)
oracle为了管理sequence使用了以下三种锁:
row cache lock:在调用sequence.nextval过程中,将数据字典信息进行物理修改时获取。赋予了nocache属性的sequence才发生。
SQ锁:在内存上缓存(cache)的范围内,调用sequence.nextval期间拥有此锁。赋予了cache属性的sequence才发生。
SV锁:RAC上节点之间顺序得到了保障的情况下,调用sequence.nextval期间拥有。赋予了CACHE+ORDER属性的sequece上发生。

赋予了cache属性的sequence调用nextval期间,应该以SSX模式获得SQ锁。许多会话同时获取SQ锁而发生争用过程中,若发生争用,则等待enq:SQ-contention事件。enq:SQ-contention事件的P2值是sequence的object ID,因此,若利用P2值与DBA_OBJECTS的结合,就可以知道对哪个 Sequence发生了等待对象。
创建Sequence赋予的CACHE值较小时,有enq:SQ-contention等待增加的趋势,CACHE值较小,内存上事先CACHE的值很快被耗尽,这时需要将数据字典信息物理修改,再次执行CACHE的工作,在此期间,因为一直要拥有SQ锁,相应的Enq:SQ-contention事件的等待时间也会延长,很不幸的是,在创建Sequence时,将CACHE值的缺省值设定为较小20, 因此创建使用量最多的Sequence时,CACHE值应该取1000以上的较大值。

偶而一次性同时创建许多会话,有时会发生enq:SQ-contention等待事件,其理由是V$SESSION.AUDSID(auditing sessionid) 列值是利用Sequence创建的,oracle在创建新的会话后,利用名为SYS.AUDSESS$的sequence的nextval创建AUDSID的值,SYS.AUDSESS$ Sequence的CACHE大小的缺省值设定为 20,许多会话同时连接,可以将SYS.AUDSESS$ sequence的CACHE大小扩大至1000,以此可以解决 enq:SQ-contention等待问题。

RAC上创建Sequence时,在赋予了CACHE属性的状态下:
(1)若没有赋予ORDER属性,则各节点将会把不同范围的Sequence值CACHE到内存上,比如拥有两个节点的RAC环境下,创建CACHE值为100的 sequence时,1节点会使用1-100,2节点会使用101-200。 使用时从各自节点取sequence。
(2)若两个节点之间会通过递增的使用sequence,必须赋予如下ORDER属性。
SQL>Create sequence ordered_sequence cache 100 order;
在order 的情况下,2个节点取的sequence是递增的。 下文会有示例来说明这两种情况。

如果已赋予CACHE+ORDER属性的sequence, oracle使用SV锁进行行同步,即,对赋予了ORDER属性的sequence调用nextval时,应该以SSX模式拥有SV锁,在获取SV锁过程中,若发生了争用,不是等待ROW CACHE或者是enq:SQ-contention,而是等待名为DFS lock handle事件,正因如此V$EVENT_NAME视图上不存在类似与"enq:SV-contention"

DFS lock handle事件是在OPS或者RAC环境下,除了 高速缓冲区 同步之外,还有 行高速缓冲区 或者 库高速缓冲区 同步获取锁的过程中的等待事件。 若保证全局范围内获得锁,在此过程中会发生DFS look handle等待,在获取SV锁的过程中发生的DFS lock handle等待事件的P1,P2值与enq:SQ-contention等待事件相同(p1=mode+namespace,p2=object#).因此会从P1值能确认是否是SV锁,通过P2可以确认哪些是Sequence发生过等待.

SV锁争用问题发生时的解决办法与SQ锁的情况相同,就是CACHE值进行适当的调整,这也是唯一的方法。
测试1:NOORDER的Sequence
node1:
SQL> create sequence seq_noorder start with 1 increment by 1 cache 20 NOORDER;
Sequence created.
SQL> select seq_noorder.nextval from dual;
NEXTVAL
- - - - - - - - - -
1
SQL> /
NEXTVAL
- - - - - - - - - -
2
SQL> /
NEXTVAL
- - - - - - - - - -
3

Node2:
SQL> select seq_noorder.nextval from dual;
NEXTVAL
- - - - - - - - - -
21
SQL> /
NEXTVAL
- - - - - - - - - -
22
SQL> /
NEXTVAL
- - - - - - - - - -
23

node2上不是从4开始的,是从21开始的,因为node1已经cache了20个。

测试2: ORDER的Sequence
node1:
SQL> create sequence seq_order start with 1 increment by 1 cache 20 ORDER;
Sequence created.
SQL> select seq_order.nextval from dual;
NEXTVAL
- - - - - - - - - -
1
SQL> /
NEXTVAL
- - - - - - - - - -
2
SQL> /
NEXTVAL
- - - - - - - - - -
3

Node2:
SQL> select seq_order.nextval from dual;
NEXTVAL
- - - - - - - - - -
4
SQL> /
NEXTVAL
- - - - - - - - - -
5
SQL> /
NEXTVAL
- - - - - - - - - -
6

指定Order 之后,取的序列就是顺序的。

小结:
没有赋予CACHE属性时,不管ORDER属性是否或RAC环境是否,一直等待ROW CACHE事件,ROW CACHE LOCK是否可以在全局范围内使用的锁,单实例环境或多实例环境同时可以发生。

Oracle Sequence默认是NOORDER,如果设置为ORDER;在单实例环境没有影响,在RAC环境此时,多实例实际缓存相同的序列,此时在多个实例并发取该序列的时候,会有短暂的资源竞争来在多实例之间进行同步。因次性能相比noorder要差,所以RAC环境非必须的情况下不要使用ORDER,尤其要避免NOCACHE ORDER组合。

在RAC等多节点环境下,sequence的CACHE值给性能带来的影响比单节点环境更严重,因此,尽量赋予CACHE+NOORDER属性,并要给与足够大的CACHE值。

但是如果使用了Cache,如果此时DB 崩溃了,那么sequence会从cache 之后重新开始,在cache中没有使用的sequence 会被跳过。即sequence 不连续。 所以只有在多节点高峰并发量很大的情况且对连续性要求不高的情况下,才使用:noorder + cache

根据创建Sequence时赋予的属性,整理等待事件的结果如下:
NOCACHE : - - > row cache lock
CAHCE+NOORDER - - > enq: SQ-contention(SQ lock)
CACHE+ORDER(RAC): - - > DFS look handle(SV lock)