[20210507]dump library_cache 2.txt

[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語句呢?感覺有點難度,明天想想看如何實現.