[20250219]关于共享池chunk大小.txt

[20250219]关于共享池chunk大小.txt --//前几天测试，无意间发现oracle每个chunk的开头前4字节定义chunk大小+1.简单验证看看。 1.环境： SYS@book> @ver2 ============================== PORT_STRING                   : x86_64/Linux 2.4.xx VERSION                       : 21.0.0.0.0 BANNER                        : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production BANNER_FULL                   : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production Version 21.3.0.0.0 BANNER_LEGACY                 : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production CON_ID                        : 0 PL/SQL procedure successfully completed. 2.测试sql语句相关chunk： SCOTT@book01p> select Sysdate from dual ; SYSDATE ------------------- 2025-02-19 15:39:56 SCOTT@book01p> @ hashz HASH_VALUE SQL_ID        CHILD_NUMBER KGL_BUCKET HASH_HEX   SQL_EXEC_START      SQL_EXEC_ID ---------- ------------- ------------ ---------- ---------- ------------------- -----------  313326246 5ptsmhh9atyp6            0      64166  12acfaa6  2025-02-19 15:39:56    16777216 SYS@book> @ sharepool/shp4 5ptsmhh9atyp6 -1 HANDLE_TYPE            KGLHDADR         KGLHDPAR         C40                                        KGLHDLMD   KGLHDPMD   KGLHDIVC KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03        KGLOBT09 ---------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ---------- child handle address   00000000638F33F0 00000000622AEFE0 select Sysdate from dual                          0          0          0 00000000643A3130 00000000643A3B18       4032       8080       3290     15402      15402  313326246 5ptsmhh9atyp6          0 parent handle address  00000000622AEFE0 00000000622AEFE0 select Sysdate from dual                          0          0          0 0000000064EB5F10 00                     4064          0          0      4064       4064  313326246 5ptsmhh9atyp6      65535 --//父游标句柄所在的chunk SYS@book> @ fchaz 00000000622AEFE0 LOC KSMCHPTR           KSMCHIDX   KSMCHDUR KSMCHCOM           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR         KSMCHPTR_BEGIN   KSMCHPTR_END+1 --- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ---------------- ---------------- ----------------- SGA 00000000622AEFB0          1          1 KGLHD                   816 recr             80 00               00000000622AEFB0 00000000622AF2E0 SYS@book> @ opeek 00000000622AEFB0 32 0 [0622AEFB0, 0622AEFD0) = 00000331 80B38F00 622AED80 00000000 00000000 00000000 00000000 00000000 --//0x00000331 = 817.正好等于chunksize+1、 $ disp_addr.sh 00000000622AEFB0 0 1 d 0x622aefb0:     817 --//父游标堆0描述符： SYS@book> @ fchaz 0000000064EB5F10 LOC KSMCHPTR           KSMCHIDX   KSMCHDUR KSMCHCOM           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR         KSMCHPTR_BEGIN   KSMCHPTR_END+1 --- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ---------------- ---------------- ----------------- SGA 0000000064EB5EA0          1          1 KGLDA                   512 freeabl           0 00               0000000064EB5EA0 0000000064EB60A0 SYS@book> @ opeek 0000000064EB5EA0 32 0 [064EB5EA0, 064EB5EC0) = 00000201 00B38F00 64EB5C70 00000000 156BF6DC 00000000 00000003 00000000 --//0x00000201 = 513 --//父游标堆0: SYS@book> @ ksmsp 0000000064EB5F10 '' '' LOC KSMCHPTR           KSMCHIDX   KSMCHDUR KSMCHCOM           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR         KSMCHPTR_BEGIN   KSMCHPTR_END+1    HEAP_DESC --- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ---------------- ---------------- ----------------- ------------------------- SGA 0000000064EB5EA0          1          1 KGLDA                   512 freeabl           0 00               0000000064EB5EA0 0000000064EB60A0 SGA 00000000643A32C0          1          1 KGLH0^12acfaa6         4096 recr           4095 0000000064EB5F10 00000000643A32C0 00000000643A42C0  KSMCHPAR=0000000064EB5F10 SYS@book> @ opeek 00000000643A32C0 32 0 [0643A32C0, 0643A32E0) = 00001001 80B38F00 643A30C0 00000000 00000000 00000000 00000000 00000000 --//0x00001001 = 4097. --//子游标句柄所在的chunk SYS@book> @ fchaz 00000000638F33F0 LOC KSMCHPTR           KSMCHIDX   KSMCHDUR KSMCHCOM           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR         KSMCHPTR_BEGIN   KSMCHPTR_END+1 --- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ---------------- ---------------- ----------------- SGA 00000000638F33C0          1          1 KGLHD                   560 recr             80 00               00000000638F33C0 00000000638F35F0 SYS@book> @ opeek 00000000638F33C0 32 0 [0638F33C0, 0638F33E0) = 00000231 80B38F00 638F3380 00000000 00000000 00000000 00000000 00000000 --//0x00000231 = 561 --//其他类似，不再查询，视乎第4-7字节也存在某种规律,仅仅出现80B38F00，00B38F00。 3.看看library cache mutex相关的chunk： SYS@book> select count(*),KSMCHPAR from x$ksmsp where KSMCHCOM='KGLSG'  and KSMCHSIZ=12304 group by KSMCHPAR;   COUNT(*) KSMCHPAR ---------- ----------------         31 000000006CC04000        339 000000006C804000        142 000000006C434000 SYS@book> select * from x$ksmsp where KSMCHCOM='KGLSG'  and KSMCHSIZ=12304 and rownum<=3; ADDR                   INDX    INST_ID     CON_ID   KSMCHIDX   KSMCHDUR KSMCHCOM         KSMCHPTR           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR ---------------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ---------------- 00007F0D14B3CEE0      76990          1          1          1          1 KGLSG            000000006CFFB5C0      12304 perm              0 000000006CC04000 00007F0D14B3CE78      76991          1          1          1          1 KGLSG            000000006CFF85B0      12304 perm              0 000000006CC04000 00007F0D14B3CE10      76992          1          1          1          1 KGLSG            000000006CFF55A0      12304 perm              0 000000006CC04000 SYS@book> @ fchaz 000000006CFF55A0 LOC KSMCHPTR           KSMCHIDX   KSMCHDUR KSMCHCOM           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR         KSMCHPTR_BEGIN   KSMCHPTR_END+1 --- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ---------------- ---------------- ----------------- SGA 000000006CFF55A0          1          1 KGLSG                 12304 perm              0 000000006CC04000 000000006CFF55A0 000000006CFF85B0 SYS@book> @ opeek 000000006CFF55A0 32 0 [06CFF55A0, 06CFF55C0) = 00003011 00B38F00 17ADF8D0 00000000 6CFF55B0 00000000 6CFF55B0 00000000 --//0x00003011 = 12305 SYS@book> @ fchaz 000000006CFF85B0 LOC KSMCHPTR           KSMCHIDX   KSMCHDUR KSMCHCOM           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR         KSMCHPTR_BEGIN   KSMCHPTR_END+1 --- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ---------------- ---------------- ----------------- SGA 000000006CFF85B0          1          1 KGLSG                 12304 perm              0 000000006CC04000 000000006CFF85B0 000000006CFFB5C0 SYS@book> @ opeek 000000006CFF85B0 32 0 [06CFF85B0, 06CFF85D0) = 00003011 00B38F00 17ADF8D0 00000000 6CFF85C0 00000000 6CFF85C0 00000000 --//00003011 = 12305