[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语句呢?感觉有点难度,明天想想看如何实现.