[20210507]dump library_cache 2.txt

来源：这里教程网时间：2026-03-03 16:41:02 作者：

[20210507]dump library_cache 2.txt --//前面测试转储library_cache level=1,2 的情况，知道放入那个bucket是根据hash与131072取模确定的，做一个例子验证冲突的情况。 1.环境: SYS@book> @ ver1 PORT_STRING VERSION BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 2.执行查询： select owner, index_name from dba_indexes where rownum<=9 --BaERRzEYqyYphBAvEbIrbYYDKkemLaib; select owner, table_name from dba_tables where rownum<=10 --XhiidvehudXqDpCMZokNkScXlQiIUkUq; --//这是我知道sql语句不同,而sql_id,hash values也一样的两条语句，看看情况。 SYS@book> @ hash HASH_VALUE SQL_ID CHILD_NUMBER HASH_HEX ---------- ------------- ------------ --------- 1992264959 ayr58apvbz37z 0 76bf8cff --//1992264959 % 131072 = 101631. SYS@book> @ sharepool/shp4 ayr58apvbz37z 0 TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09 --------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ---------- child handle address 000000007D72FB08 000000007C65BD18 select owner, index_name from dba_indexe 1 0 0 000000007D215700 000000007C3E9F48 4528 64872 3149 72549 72549 1992264959 ayr58apvbz37z 0 parent handle address 000000007C65BD18 000000007C65BD18 select owner, index_name from dba_indexe 1 0 0 000000007BF21EA8 00 4784 0 0 4784 4784 1992264959 ayr58apvbz37z 65535 child handle address 000000007C3E1330 000000007D209848 select owner, table_name from dba_tables 0 0 0 000000007D22DDA8 000000007BC44CC8 8600 68928 3989 81517 81517 1992264959 ayr58apvbz37z 0 child handle address 000000007C648BC8 000000007D209848 select owner, table_name from dba_tables 1 0 0 000000007E23D570 000000007BC450D8 8600 72984 3989 85573 85573 1992264959 ayr58apvbz37z 1 parent handle address 000000007D209848 000000007D209848 select owner, table_name from dba_tables 1 0 0 000000007BCF4DC8 00 4784 0 0 4784 4784 1992264959 ayr58apvbz37z 65535 --//不知道为什么其中一个出现子光标，先不管这个问题。 3.转储library cache: SYS@book> @ tix New tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_12044_0005.trc SYS@book> alter session set events 'immediate trace name library_cache level 6'; Session altered. --//打开转储，分别查询7D209848，7C65BD18 。 Bucket: #=101631 Mutex=0x8051f008(0, 23, 0, 6) LibraryHandle: Address=0x7d209848 Hash=76bf8cff LockMode=N PinMode=0 LoadLockMode=0 Status=VALD --//76bf8cff = 1992264959 --//1992264959 % 131072 = 101631. ObjectName: Name=select owner, table_name from dba_tables where rownum<=10 --XhiidvehudXqDpCMZokNkScXlQiIUkUq ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ FullHashValue=97fe26d235c3841eaf5ca85576bf8cff Namespace=SQL AREA(00) Type=CURSOR(00) Identifier=1992264959 OwnerIdn=0 Statistics: InvalidationCount=0 ExecutionCount=10 LoadCount=3 ActiveLocks=1 TotalLockCount=4 TotalPinCount=1 Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=2 Version=0 BucketInUse=3 HandleInUse=3 HandleReferenceCount=0 Concurrency: DependencyMutex=0x7d2098f8(0, 2, 0, 0) Mutex=0x7d209988(58, 52, 0, 6) Flags=RON/PIN/TIM/PN0/DBN/[10012841] WaitersLists: Lock=0x7d2098d8[0x7d2098d8,0x7d2098d8] Pin=0x7d2098b8[0x7d2098b8,0x7d2098b8] LoadLock=0x7d209930[0x7d209930,0x7d209930] Timestamp: Current=05-07-2021 10:07:52 HandleReference: Address=0x7d209a50 Handle=(nil) Flags=[00] LibraryObject: Address=0x7bc445f0 HeapMask=0000-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000] ChildTable: size='16' Child: id='0' Table=0x7bc454a0 Reference=0x7bc44ef8 Handle=0x7c3e1330 Child: id='1' Table=0x7bc454a0 Reference=0x7bc45238 Handle=0x7c648bc8 --//这里出现2个子光标，后面的Handle=0x7c3e1330，Handle=0x7c648bc8与子光标的地址一致。 NamespaceDump: Parent Cursor: sql_id=ayr58apvbz37z parent=0x7bc44690 maxchild=2 plk=y ppn=n CursorDiagnosticsNodes: ChildNode: ChildNumber=0 ID=3 reason=Optimizer mismatch(13) size=3x4 kxscflg=32 kxscfl4=4194560 dnum_kksfcxe=262144 LibraryHandle: Address=0x7c65bd18 Hash=76bf8cff LockMode=N PinMode=0 LoadLockMode=0 Status=VALD ObjectName: Name=select owner, index_name from dba_indexes where rownum<=9 --BaERRzEYqyYphBAvEbIrbYYDKkemLaib ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ FullHashValue=9a77cb1a8126f19caf5ca85576bf8cff Namespace=SQL AREA(00) Type=CURSOR(00) Identifier=1992264959 OwnerIdn=0 Statistics: InvalidationCount=0 ExecutionCount=7 LoadCount=2 ActiveLocks=1 TotalLockCount=3 TotalPinCount=1 Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=1 Version=0 BucketInUse=2 HandleInUse=2 HandleReferenceCount=0 Concurrency: DependencyMutex=0x7c65bdc8(0, 1, 0, 0) Mutex=0x7c65be58(58, 34, 0, 6) Flags=RON/PIN/TIM/PN0/DBN/[10012841] WaitersLists: Lock=0x7c65bda8[0x7c65bda8,0x7c65bda8] Pin=0x7c65bd88[0x7c65bd88,0x7c65bd88] LoadLock=0x7c65be00[0x7c65be00,0x7c65be00] Timestamp: Current=05-07-2021 10:07:44 HandleReference: Address=0x7c65bf20 Handle=(nil) Flags=[00] LibraryObject: Address=0x7c3e9870 HeapMask=0000-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000] ChildTable: size='16' Child: id='0' Table=0x7c3ea720 Reference=0x7c3ea178 Handle=0x7d72fb08 --//这里出现1个子光标，后面的Handle=0x7d72fb08与子光标的地址一致。 NamespaceDump: Parent Cursor: sql_id=ayr58apvbz37z parent=0x7c3e9910 maxchild=1 plk=y ppn=n --//注意2条语句挂在一个bucket上. --//如何能找到21个hash值一样的SQL语句呢?感觉有点难度,明天想想看如何实现.