通过dump library cache分析与学习oracle易碎解析锁v$lock之系列十

最新推荐文章于 2021-04-03 10:05:05 发布

clg10051

最新推荐文章于 2021-04-03 10:05:05 发布

阅读量276

点赞数

文章标签：数据库后端

背景

在使用oracle 10704 event分析获取锁lock及死锁deadlock系列九
http://blog.itpub.net/9240380/viewspace-1819341/，本来想分析易碎解析锁，发现没有找到任何有价值的信息，本文想回答这个问题。

结论

1,ORACLE的易碎解析锁，从实质说不是锁，而是一种类似于latch一种的东西
2,易碎解析锁会持null级别锁，只要基表发生ddl,马上就会被中断
3,首次对于基表DDL，会在library cache中增加新的引用对象
4,新增基于基表的SELECT或DML查询，也会对应增新的引用对象
5,基表会在library cache分配1个bucket
基于基表的select或DML也会分配1个bucket
6,关于trc文件内容讲解，请见下述测试
7，关于library cache dump内容是非常且复杂的，还需要进一步测试
8，研究这个目的在于了解ORACLE共享池分配的原理及相关概念，这样可进一步解决与共享池相关的故障

引发新的问题

1,library cache dump产生的TRC文件的组织结构，还需进一步理解
2,梳理出来共享池分配的大体机制

测试

1，即使没有找到锁相关的信息，而解析锁是用于解析SQL，且存储在共享池中，所以我们换个思路，转储library cache看下是否有什么线索
测试仍基于上文数据

SQL> select count(a) from t_parselock where rownum=1;

COUNT(A)
----------
0

SQL> alter session set tracefile_identifier='parse_lock_test';

Session altered.

SQL> Alter session set events 'immediate trace name library_cache level 10';

Session altered.

[oracle@seconary ~]$ cd /oracle/diag/rdbms/guowang/guowang/trace/
[oracle@seconary trace]$ ll -l *parse_lock_test*
-rw-r----- 1 oracle oinstall 12439872 Oct 30 06:04 guowang_ora_10493_parse_lock_test.trc

直接在TRC文件过滤scott.t_parselock，可见它有3个引用对象（请查看ReferenceList），也就是说这3个对象是依赖对象
Bucket: #=82298 Mutex=d5fcfbb8(0, 31, 0, 6)
LibraryHandle: Address=cf334760 Hash=e15417a LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD --可见持锁模式为0，即null锁，且其状态为valid
ObjectName: Name=SCOTT.T_PARSELOCK
FullHashValue=07c8f04379a3a19c361851640e15417a Namespace=TABLE/PROCEDURE(01) Type=TABLE(02) Identifier=74885 OwnerIdn=84
Statistics: InvalidationCount=0 ExecutionCount=0 LoadCount=2 ActiveLocks=0 TotalLockCount=10 TotalPinCount=12
Counters: BrokenCount=6 RevocablePointer=4 KeepDependency=0 KeepHandle=0 BucketInUse=11 HandleInUse=11
Concurrency: DependencyMutex=cf334810(0, 14, 0, 0) Mutex=cf334888(241, 130, 0, 6)
Flags=PIN/TIM/[00002801]
WaitersLists:
Lock=cf3347f0[cf3347f0,cf3347f0]
Pin=cf334800[cf3347d0,cf3347d0]
Timestamp: Current=10-30-2015 03:48:37
HandleReference: Address=cf334900 Handle=de1e0d78 Flags=OWN[200]
ReferenceList:
Reference: Address=c8f173c8 Handle=de6d8460 Flags=DEP[01]
Timestamp=10-30-2015 03:48:37 InvalidatedFrom=0
Reference: Address=c8fb47a8 Handle=cf12e248 Flags=DEP[01]
Timestamp=10-30-2015 03:48:37 InvalidatedFrom=0
Reference: Address=c8fb97a8 Handle=cf55d898 Flags=DEP/INV[05] --flags=dep/inv[05]表示这个依赖对象已经无效了
Timestamp=10-30-2015 03:42:01 InvalidatedFrom=8
LibraryObject: Address=c8fb5060 HeapMask=0000-0701-0201 Flags=EXS/LOC[0004] Flags2=[0000] PublicFlags=[0000]
DataBlocks:
Block: #='0' name=KGLH0^e15417a pins=0 Change=NONE
Heap=cf358c70 Pointer=c8fb5190 Extent=c8fb5030 Flags=I/-/-/A/-/-
FreedLocation=0 Alloc=0.398438 Size=4.000000 LoadTime=4609490310
Block: #='8' name=KGLS^e15417a pins=0 Change=NONE
Heap=c8fb5438 Pointer=c95e43f8 Extent=c95e3638 Flags=I/-/-/A/-/-
FreedLocation=0 Alloc=0.703125 Size=4.000000 LoadTime=0

官方文档上说，这种解析锁非常脆弱，只要对基表发生DDL，其依赖对象马上变成无效
SQL> alter table t_parselock add b int;

Table altered.

再次查看TRC文件的内容（重作一次DUMP LIBRARY CACHE）

Bucket: #=82298 Mutex=d5fcfbb8(0, 45, 0, 6)
LibraryHandle: Address=cf334760 Hash=e15417a LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD
ObjectName: Name=SCOTT.T_PARSELOCK
FullHashValue=07c8f04379a3a19c361851640e15417a Namespace=TABLE/PROCEDURE(01) Type=TABLE(02) Identifier=74885 OwnerIdn=84
Statistics: InvalidationCount=0 ExecutionCount=0 LoadCount=3 ActiveLocks=0 TotalLockCount=17 TotalPinCount=20
Counters: BrokenCount=8 RevocablePointer=4 KeepDependency=0 KeepHandle=0 BucketInUse=18 HandleInUse=18
Concurrency: DependencyMutex=cf334810(0, 19, 0, 0) Mutex=cf334888(103, 203, 0, 6)
Flags=PIN/TIM/[00000800]
WaitersLists:
Lock=cf3347f0[cf3347f0,cf3347f0]
Pin=cf334800[cf3347d0,cf3347d0]
Timestamp: Current=10-30-2015 03:48:37
HandleReference: Address=cf334900 Handle=de1e0d78 Flags=OWN[200]
ReferenceList:
Reference: Address=c8e3b3c8 Handle=de572d58 Flags=DEP[01] --DDL后增1个引用对象，可否理解发生一次DDL，会增加一个引用对象呢
Timestamp=10-30-2015 03:48:37 InvalidatedFrom=0
Reference: Address=c8f173c8 Handle=de6d8460 Flags=DEP[01]
Timestamp=10-30-2015 03:48:37 InvalidatedFrom=0
Reference: Address=c8fb47a8 Handle=cf12e248 Flags=DEP[01]
Timestamp=10-30-2015 03:48:37 InvalidatedFrom=0
Reference: Address=c8fb97a8 Handle=cf55d898 Flags=DEP/INV[05]
Timestamp=10-30-2015 03:42:01 InvalidatedFrom=8
ObjectFreed=last freed from PNDL addn data FUP

继续测试，验证下上述的想法,可见这下引用对象没有变化，可见仅在首次发生DDL才会增加引用对象个数
SQL> alter table t_parselock add c int;

Table altered.

Bucket: #=82298 Mutex=d5fcfbb8(0, 56, 0, 6)
LibraryHandle: Address=cf334760 Hash=e15417a LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD
ObjectName: Name=SCOTT.T_PARSELOCK
FullHashValue=07c8f04379a3a19c361851640e15417a Namespace=TABLE/PROCEDURE(01) Type=TABLE(02) Identifier=74885 OwnerIdn=84
Statistics: InvalidationCount=0 ExecutionCount=0 LoadCount=5 ActiveLocks=0 TotalLockCount=24 TotalPinCount=28
Counters: BrokenCount=10 RevocablePointer=4 KeepDependency=0 KeepHandle=0 BucketInUse=25 HandleInUse=25
Concurrency: DependencyMutex=cf334810(0, 25, 0, 0) Mutex=cf334888(129, 291, 0, 6)
Flags=PIN/TIM/[00000800]
WaitersLists:
Lock=cf3347f0[cf3347f0,cf3347f0]
Pin=cf334800[cf3347d0,cf3347d0]
Timestamp: Current=10-30-2015 03:48:37
HandleReference: Address=cf334900 Handle=de1e0d78 Flags=OWN[200]
ReferenceList:
Reference: Address=c8e3b3c8 Handle=de572d58 Flags=DEP[01]
Timestamp=10-30-2015 03:48:37 InvalidatedFrom=0
Reference: Address=c8f173c8 Handle=de6d8460 Flags=DEP[01]
Timestamp=10-30-2015 03:48:37 InvalidatedFrom=0
Reference: Address=c8fb47a8 Handle=cf12e248 Flags=DEP[01]
Timestamp=10-30-2015 03:48:37 InvalidatedFrom=0
Reference: Address=c8fb97a8 Handle=cf55d898 Flags=DEP/INV[05]
Timestamp=10-30-2015 03:42:01 InvalidatedFrom=8
ObjectFreed=last freed from PNDL addn data FUP

所以我分析基于基表不同的SQL才会产生新增的引用对象
SQL> select a,b,c from t_parselock;

no rows selected

SQL> select a,c from t_parselock where rownum<=3;

no rows selected

SQL> select b,c from t_parselock where rownum<=9;

no rows selected

确实和我想法一样，新增3个与基表相关的SELECT查询语句，即新增3个引用对象
Bucket: #=82298 Mutex=d5fcfbb8(0, 69, 0, 6)
LibraryHandle: Address=cf334760 Hash=e15417a LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD
ObjectName: Name=SCOTT.T_PARSELOCK
FullHashValue=07c8f04379a3a19c361851640e15417a Namespace=TABLE/PROCEDURE(01) Type=TABLE(02) Identifier=74885 OwnerIdn=84
Statistics: InvalidationCount=0 ExecutionCount=0 LoadCount=6 ActiveLocks=0 TotalLockCount=27 TotalPinCount=31
Counters: BrokenCount=10 RevocablePointer=4 KeepDependency=0 KeepHandle=0 BucketInUse=35 HandleInUse=35
Concurrency: DependencyMutex=cf334810(0, 29, 0, 0) Mutex=cf334888(123, 344, 0, 6)
Flags=PIN/TIM/[00002801]
WaitersLists:
Lock=cf3347f0[cf3347f0,cf3347f0]
Pin=cf334800[cf3347d0,cf3347d0]
Timestamp: Current=10-30-2015 03:48:37
HandleReference: Address=cf334900 Handle=de1e0d78 Flags=OWN[200]
ReferenceList:
Reference: Address=c8e313c8 Handle=cfa4f048 Flags=DEP[01]
Timestamp=10-30-2015 03:48:37 InvalidatedFrom=0
Reference: Address=c8e333c8 Handle=cf7b2218 Flags=DEP[01]
Timestamp=10-30-2015 03:48:37 InvalidatedFrom=0
Reference: Address=c8e363c8 Handle=cf3f05c8 Flags=DEP[01]
Timestamp=10-30-2015 03:48:37 InvalidatedFrom=0
Reference: Address=c8e3b3c8 Handle=de572d58 Flags=DEP[01]
Timestamp=10-30-2015 03:48:37 InvalidatedFrom=0
Reference: Address=c8f173c8 Handle=de6d8460 Flags=DEP[01]
Timestamp=10-30-2015 03:48:37 InvalidatedFrom=0
Reference: Address=c8fb47a8 Handle=cf12e248 Flags=DEP[01]
Timestamp=10-30-2015 03:48:37 InvalidatedFrom=0
Reference: Address=c8fb97a8 Handle=cf55d898 Flags=DEP/INV[05]
Timestamp=10-30-2015 03:42:01 InvalidatedFrom=8
LibraryObject: Address=c8e34c80 HeapMask=0000-0701-0701 Flags=EXS/LOC[0004] Flags2=[0000] PublicFlags=[0000]
DataBlocks:
Block: #='0' name=KGLH0^e15417a pins=0 Change=NONE
Heap=cf9f68e0 Pointer=c8e34d68 Extent=c8e34c50 Flags=I/-/-/A/-/-
FreedLocation=0 Alloc=0.710938 Size=4.000000 LoadTime=4620871670
Block: #='8' name=KGLS^e15417a pins=0 Change=NONE
Heap=c8e34f80 Pointer=c918d690 Extent=c918c8d0 Flags=I/-/-/A/-/-
FreedLocation=0 Alloc=0.984375 Size=4.000000 LoadTime=4620871700
Block: #='9' name=KGLS^e15417a pins=0 Change=NONE
Heap=c8e35098 Pointer=c918c700 Extent=c918b8d0 Flags=I/-/-/A/-/-
FreedLocation=0 Alloc=0.429688 Size=4.000000 LoadTime=4620871720
Block: #='10' name=KGLS^e15417a pins=0 Change=NONE
Heap=c8e35120 Pointer=c918b7e0 Extent=c918a8d0 Flags=I/-/-/A/-/-
FreedLocation=0 Alloc=0.210938 Size=4.000000 LoadTime=4620871720

继续研究library cache dump文件内容，我们获取select a,b,c from t_parselock 这个查询SQL对应的游标信息，我的目的就是想把如下的信息与上述的引用对象相关的信息建立联系
我们发现v$sqlarea.address对应如下的libraryhandle后面的address,而identifier对应sqlarea.hash_value
而且基表会在library cache占用一个bucket,而且使用基表的sql也会分配一个bucket
Bucket: #=106866 Mutex=d4161a00(0, 10, 0, 6)
LibraryHandle: Address=cff78da8 Hash=37c9a172 LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD
ObjectName: Name=select a,b,c from t_parselock

FullHashValue=058e0a73d46e5dd347bf262e37c9a172 Namespace=SQL AREA(00) Type=CURSOR(00) Identifier=935960946 OwnerIdn=84
Statistics: InvalidationCount=0 ExecutionCount=1 LoadCount=2 ActiveLocks=0 TotalLockCount=1 TotalPinCount=1
Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=1 KeepHandle=1 BucketInUse=0 HandleInUse=0
Concurrency: DependencyMutex=cff78e58(0, 0, 0, 0) Mutex=cff78ed0(123, 20, 0, 6)
Flags=RON/PIN/TIM/PN0/DBN/[10012841]
WaitersLists:
Lock=cff78e38[cff78e38,cff78e38]
Pin=cff78e48[cff78e18,cff78e18]
Timestamp: Current=10-30-2015 06:58:19
LibraryObject: Address=c8e36c80 HeapMask=0000-0001-0001 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
DataBlocks:
Block: #='0' name=PCUR^37c9a172 pins=0 Change=NONE
Heap=cf358c70 Pointer=c8e36d68 Extent=c8e36c50 Flags=I/-/P/A/-/-
FreedLocation=0 Alloc=1.515625 Size=4.000000 LoadTime=4620871600
ChildTable: size='16'
Child: id='0' Table=c8e37b80 Reference=c8e375d0 Handle=cf3f05c8
NamespaceDump:
Parent Cursor: sql_id=4ggt65svwm8bk parent=0xc8e36d68 maxchild=1 plk=n ppn=n