[20210602]分析library cache转储 5.txt

[20210602]分析library cache转储 5.txt --//链接http://blog.itpub.net/267265/viewspace-2773571/＝》[20210524]分析library cache转储 3.txt --//继续测试多个sql语句hash_value值相同的情况，说明一点实际上并不是hash_value一样才会在一个bucket， --//我的测试环境仅仅有131072个buecket，好像很大内存的配置也是131072个bucket。 --//131072 = 0x20000，也就是full_hash_vlue & 0x1ffff ，相当于后21bit相等的都会在一个bucket里面。 --//当然hash_value一样一定在一个bucket 里面。 --//有朋友提示这个链接是双向的，也许我执行oradebug peek看到的信息不全，完善这个测试。 --//参考链接：http://blog.itpub.net/267265/viewspace-2773591/ 1.环境： SCOTT@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.找到一些sql语句hash_value一样。 --//参考http://externaltable.blogspot.com/2012/06/hash-collisions-sql-signatures-and.html --//花了一个下午找到仅仅有4个hash_value,注本来想找到20个以上的情况，不现实放弃。 SCOTT@book> create table dula as select * from dual ; Table created. define SQL1='select sysdate from dual --' define SQL2='select sysdate from dula --'   SELECT hashval         ,SUBSTR (hashval, 25, 8)         ,sql_type         ,DECODE (sql_type,  1, '&SQL1',  2, '&SQL2',  NULL) || SQL || ';'             sql_text     FROM HASH_1    WHERE SUBSTR (hashval, 25, 8) IN (  SELECT SUBSTR (hashval, 25, 8)                                          FROM HASH_1                                      GROUP BY SUBSTR (hashval, 25, 8)                                        HAVING COUNT (*) > 3) ORDER BY SUBSTR (hashval, 25, 8); HASHVAL                                  SUBSTRHASHVAL,2    SQL_TYPE SQL_TEXT ---------------------------------------- ---------------- ---------- ------------------------------------------------------------ 7fa0440cb576468eee514bd1865821d8         865821d8                  2 select sysdate from dula --QVJzuaXTQDRWAMQsUKRahCOmcllpAYLn; 9d524191de77830512606b60865821d8         865821d8                  2 select sysdate from dula --hBaTOrXVlpVzFMMfuJFHWWksdoGuzZdc; 4823f0eab9589f52ce20856e865821d8         865821d8                  1 select sysdate from dual --heqVJLdwOOvUbNnwDpaMwowplkAymAhs; 85e2a9308c0fdac36dd09edd865821d8         865821d8                  1 select sysdate from dual --SYWAvXlLqpLVnKmYQGOJxklMrToCdoUD; --//这里记录的HASHVAL与实际hash_value大小头要对调一下。 $ cat aa1.txt select sysdate from dula --QVJzuaXTQDRWAMQsUKRahCOmcllpAYLn; @ hash select sysdate from dula --hBaTOrXVlpVzFMMfuJFHWWksdoGuzZdc; @ hash select sysdate from dual --heqVJLdwOOvUbNnwDpaMwowplkAymAhs; @ hash select sysdate from dual --SYWAvXlLqpLVnKmYQGOJxklMrToCdoUD; @ hash --//执行如下: SCOTT@book> @ aa1.txt SYSDATE ------------------- 2021-05-24 09:10:30 HASH_VALUE SQL_ID        CHILD_NUMBER HASH_HEX ---------- ------------- ------------ --------- 3626064006 d2kujxvc22q46            0  d8215886 SYSDATE ------------------- 2021-05-24 09:10:30 HASH_VALUE SQL_ID        CHILD_NUMBER HASH_HEX ---------- ------------- ------------ --------- 3626064006 60uv02bc22q46            0  d8215886 SYSDATE ------------------- 2021-05-24 09:10:30 HASH_VALUE SQL_ID        CHILD_NUMBER HASH_HEX ---------- ------------- ------------ --------- 3626064006 6x190tvc22q46            0  d8215886 SYSDATE ------------------- 2021-05-24 09:10:30 HASH_VALUE SQL_ID        CHILD_NUMBER HASH_HEX ---------- ------------- ------------ --------- 3626064006 dv7qhdrc22q46            0  d8215886 --//执行多次可以发现HASH_VALUE一样，记住sql_id 分别是d2kujxvc22q46，60uv02bc22q46，6x190tvc22q46，dv7qhdrc22q46。 --//位于 bueckt = 3626064006 % 131072  = 88198，88198 = 0x15886。 3.转储library cache: SYS@book> oradebug setmypid Statement processed. SYS@book> @ tix New tracefile_identifier =  /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_36826_0001.trc SYS@book> oradebug dump library_cache 10; Statement processed. --//单独把Bucket: #=88198内容保存为一个文件(bucket88198a.txt)便于分析。 $ grep "select sysdate from du" bucket88198a.txt     ObjectName:  Name=select sysdate from dual --SYWAvXlLqpLVnKmYQGOJxklMrToCdoUD     ObjectName:  Name=select sysdate from dual --heqVJLdwOOvUbNnwDpaMwowplkAymAhs     ObjectName:  Name=select sysdate from dula --hBaTOrXVlpVzFMMfuJFHWWksdoGuzZdc     ObjectName:  Name=select sysdate from dula --QVJzuaXTQDRWAMQsUKRahCOmcllpAYLn //显示的顺序正好与前面的执行顺序相反。 $ egrep "sql_id|^  LibraryHandle" bucket88198a.txt   LibraryHandle:  Address=0x7edc8b38 Hash=d8215886 LockMode=N PinMode=0 LoadLockMode=0 Status=VALD       Parent Cursor:  sql_id=dv7qhdrc22q46 parent=0x807b2930 maxchild=1 plk=y ppn=n   LibraryHandle:  Address=0x7ed68170 Hash=d8215886 LockMode=N PinMode=0 LoadLockMode=0 Status=VALD       Parent Cursor:  sql_id=6x190tvc22q46 parent=0x807b7930 maxchild=1 plk=y ppn=n   LibraryHandle:  Address=0x7efafac0 Hash=d8215886 LockMode=N PinMode=0 LoadLockMode=0 Status=VALD       Parent Cursor:  sql_id=60uv02bc22q46 parent=0x7eeda690 maxchild=1 plk=y ppn=n   LibraryHandle:  Address=0x7ed683c0 Hash=d8215886 LockMode=N PinMode=0 LoadLockMode=0 Status=VALD       Parent Cursor:  sql_id=d2kujxvc22q46 parent=0x7efadae0 maxchild=1 plk=y ppn=n  $ head -1 bucket88198a.txt Bucket: #=88198 Mutex=0x815b4150(0, 15, 0, 6) --//mutex地址0x815b4150，减去0x10 就是bucket的地址：0x815b4140，注意测试输出的mutex地址与上次不同。 SYS@book> oradebug peek 0x815b4140 40 [0815B4140, 0815B4168) = 7EDC8B38 00000000 7ED683C0 00000000 00000000 00000000 0000000F 00000000 00015886 00000000 --//注意看前16字节，0x7edc8b38正好是sql_id=dv7qhdrc22q46的父游标句柄地址。0x7ED683C0正好是sql_id=d2kujxvc22q46的 父游标句柄地址. --//这样bucket记录的前8位就是链表的尾部，后8位就是链表的开头。如何链接起来的呢？ --//后记：看了后面的分析，感觉应该反过来，前8位就是链表的开头(最后执行的sql语句父游标句柄)，后8位就是链表的尾部。 4.看看如何链接： --//我以前测试可以发现父游标句柄的地址减去0x30正好就是父游标句柄chunk的开始地址。 SYS@book> @ fcha 0x7EDC8B38 Find in which heap (UGA, PGA or Shared Pool) the memory address 0x7EDC8B38 resides... WARNING!!! This script will query X$KSMSP, which will cause heavy shared pool latch contention in systems under load and with large shared pool. This may even completely hang your instance until the query has finished! You probably do not want to run this in production! Press ENTER to continue, CTRL+C to cancel... LOC KSMCHPTR           KSMCHIDX   KSMCHDUR KSMCHCOM           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR --- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ---------------- SGA 000000007EDC8B08          6          1 KGLHD                   592 recr             80 00 --//0x000000007EDC8B08 0x7EDC8B38 正好相差0x30.后面的不再执行fcha，不然写的太长.. SYS@book> @ tix New tracefile_identifier =  /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_40996_0003.trc oradebug peek 0x7EDC8B38 60 1 oradebug peek 0x7ed68170 60 1 oradebug peek 0x7efafac0 60 1 oradebug peek 0x7ed683c0 60 1 --//注：直接使用上面的地址转储。 SYS@book> oradebug peek 0x815b4140 40 [0815B4140, 0815B4168) = 7EDC8B38 00000000 7ED683C0 00000000 00000000 00000000 00000014 00000000 00015886 00000000 SYS@book> oradebug peek 0x7EDC8B38 60 1 [07EDC8B38, 07EDC8B74) = 7ED68170 00000000 815B4140 00000000 807B2890 00000000 7EDC8C90 00000000 00010000 10012841 00000001 00000001 00010001 00000002 00000000                         ~~~~~~~~~~~~~~~~~################## --//注意看下划线0x7ED68170对应写一个要peek的地址，而####对应的就是0x815b4140就是bucket的地址。 SYS@book> oradebug peek 0x7ed68170 60 1 [07ED68170, 07ED681AC) = 7EFAFAC0 00000000 7EDC8B38 00000000 807B7890 00000000 7ED682C8 00000000 00010000 10012841 00000001 00000001 00010001 00000002 00000000 SYS@book> oradebug peek 0x7efafac0 60 1 [07EFAFAC0, 07EFAFAFC) = 7ED683C0 00000000 7ED68170 00000000 7EEDA5F0 00000000 7EFAFC18 00000000 00010000 10012841 00000001 00000001 00010001 00000002 00000000 SYS@book> oradebug peek 0x7ed683c0 60 1 [07ED683C0, 07ED683FC) = 815B4140 00000000 7EFAFAC0 00000000 7EFADA40 00000000 7ED68518 00000000 00010000 10012841 00000001 00000001 00010001 00000002 00000000 --//注意看前面的8位，7ED68170，7ED683C0，7ED683C0，815B4140。 --//这不正好与前面看到LibraryHandle的地址对应上吗， --//再继续看接下来的8位：815B4140，7EDC8B38，7ED68170，7EFAFAC0。 --//这样两条链就形成了双向链接。 $  egrep "^  LibraryHandle" bucket88198a.txt   LibraryHandle:  Address=0x7edc8b38 Hash=d8215886 LockMode=N PinMode=0 LoadLockMode=0 Status=VALD   LibraryHandle:  Address=0x7ed68170 Hash=d8215886 LockMode=N PinMode=0 LoadLockMode=0 Status=VALD   LibraryHandle:  Address=0x7efafac0 Hash=d8215886 LockMode=N PinMode=0 LoadLockMode=0 Status=VALD   LibraryHandle:  Address=0x7ed683c0 Hash=d8215886 LockMode=N PinMode=0 LoadLockMode=0 Status=VALD 4.疑问： --//就是知道了hash_value,就能计算使用那个buckect，但是oracle如何快速定位那个bucket呢，oracle的library cache bucket好像是按需分配的。 --//被分成好几个chunk，显然无法简单的计算 基地址 + 40*buckect值 获得偏移，有时间在仔细测试与观察看看。