[20240928]建立完善ksmsp.sql脚本.txt

[20240928]建立完善ksmsp.sql脚本.txt --//查询x$ksmsp是很慢的，因为没有索引。 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. SYS@book> @ xind x$ksmsp --//没有相应索引。 2.例子说明： SCOTT@book01p> select * from dept where deptno=20;     DEPTNO DNAME                          LOC ---------- ------------------------------ -------------         20 RESEARCH                       DALLAS SCOTT@book01p> @ hash HASH_VALUE SQL_ID        CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX   SQL_EXEC_START      SQL_EXEC_ID ---------- ------------- ------------ ---------- --------------- ---------- ------------------- -----------   95129850 80baj2c2ur47u            0     102650      2852011669   5ab90fa  2024-09-28 09:39:33    16777216 SYS@book> @ sharepool/shp4 80baj2c2ur47u 0 HANDLE_TYPE            KGLHDADR         KGLHDPAR         C40                                        KGLHDLMD   KGLHDPMD   KGLHDIVC KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03        KGLOBT09 ---------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ---------- child handle address   0000000062EDA9E0 0000000062EDAC30 select * from dept where deptno=20                0          0          0 0000000062ECBBE0 0000000062EF1758       8080      20224       3299     31603      31603   95129850 80baj2c2ur47u          0 parent handle address  0000000062EDAC30 0000000062EDAC30 select * from dept where deptno=20                0          0          0 0000000062ECC440 00                     4064          0          0      4064       4064   95129850 80baj2c2ur47u      65535 --//如果使用KGLOBHD0=0000000062ECC440查询x$ksmsp，实际上堆的描述符，实际上查询条件是查询KSMCHPAR='0000000062ECC440'。 --//顺便写一个查询堆描述符号在那个chunk里面，类似tpt fcha.sql脚本功能，加入一些更灵活的查询，改写如下： $ cat ksmsp.sql -- Copyright 2018 Tanel Poder. All rights reserved. More info at http://tanelpoder.com -- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions. -------------------------------------------------------------------------------- -- -- File name:   fcha.sql (Find CHunk Address) v0.2 -- Purpose:     Find in which heap (UGA, PGA or Shared Pool) a memory address resides -- -- Author:      Tanel Poder -- Copyright:   (c) http://blog.tanelpoder.com | @tanelpoder -- -- Usage:       @fchaz <addr_hex> -- --              @fchaz F6A14448  -- -- -- Other:       This would only report an UGA/PGA chunk address if it belongs --              to *your* process/session (x$ksmup and x$ksmpp do not see other --              session/process memory) -- -------------------------------------------------------------------------------- --prompt Find in which heap (UGA, PGA or Shared Pool) the memory address &1 resides... --prompt --prompt WARNING!!! This script will query X$KSMSP, which will cause heavy shared pool latch contention --prompt in systems under load and with large shared pool. This may even completely hang --prompt your instance until the query has finished! You probably do not want to run this in production! --prompt --pause  Press ENTER to continue, CTRL+C to cancel... col fcura_addrlen new_value _fcura_addrlen format 999 set termout off select vsize(addr)*2 fcura_addrlen from x$dual; col 2 new_value 2 select null "2" from dual where 1=0; select decode('&2',null,'0=1','&2') "2" from dual; set termout on SELECT 'SGA' LOC       ,KSMCHPTR       ,KSMCHIDX       ,KSMCHDUR       ,KSMCHCOM       ,KSMCHSIZ       ,KSMCHCLS       ,KSMCHTYP       ,KSMCHPAR       ,KSMCHPTR KSMCHPTR_BEGIN       ,TO_CHAR        (           TO_NUMBER (KSMCHPTR, 'XXXXXXXXXXXXXXXX') + KSMCHSIZ          ,'FM0XXXXXXXXXXXXXXX'        )           "KSMCHPTR_END+1"       , DECODE ( ksmchpar , HEXTORAW (LPAD (UPPER ('&1'), &_fcura_addrlen, '0')),'KSMCHPAR=&1') heap_desc   FROM x$ksmsp  WHERE    ksmchptr = HEXTORAW (LPAD (UPPER ('&1'), &_fcura_addrlen, '0'))        OR ksmchpar = HEXTORAW (LPAD (UPPER ('&1'), &_fcura_addrlen, '0'))        OR TO_NUMBER           (              SUBSTR ('&1', INSTR (LOWER ('&1'), 'x') + 1)             ,LPAD ('X', &_fcura_addrlen, 'X')           ) BETWEEN TO_NUMBER (ksmchptr, LPAD ('X', &_fcura_addrlen, 'X'))                 AND   TO_NUMBER (ksmchptr, LPAD ('X', &_fcura_addrlen, 'X'))                     + ksmchsiz                     - 1        OR &&2; SYS@book> @ ksmsp 0000000062ECC440 '' LOC KSMCHPTR           KSMCHIDX   KSMCHDUR KSMCHCOM           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR         KSMCHPTR_BEGIN   KSMCHPTR_END+1    HEAP_DESC --- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ---------------- ---------------- ----------------- ------------------------- SGA 0000000062EF0F00          1          1 KGLH0^5ab90fa          4096 recr           4095 0000000062ECC440 0000000062EF0F00 0000000062EF1F00  KSMCHPAR=0000000062ECC440 SGA 0000000062ECC3D0          1          1 KGLDA                   512 freeabl           0 00               0000000062ECC3D0 0000000062ECC5D0 SYS@book> @ ksmsp 0000000062ECC440 "KSMCHCOM like '%^5ab90fa'" LOC KSMCHPTR           KSMCHIDX   KSMCHDUR KSMCHCOM           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR         KSMCHPTR_BEGIN   KSMCHPTR_END+1    HEAP_DESC --- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ---------------- ---------------- ----------------- ------------------------- SGA 0000000064489EF0          1          4 SQLA^5ab90fa           4096 recr           4095 0000000062EF1758 0000000064489EF0 000000006448AEF0 SGA 0000000064485EF0          1          4 SQLA^5ab90fa           4096 freeabl           0 0000000062EF1758 0000000064485EF0 0000000064486EF0 SGA 0000000064484EF0          1          4 SQLA^5ab90fa           4096 freeabl           0 0000000062EF1758 0000000064484EF0 0000000064485EF0 SGA 000000006447CEF0          1          4 SQLA^5ab90fa           4096 freeabl           0 0000000062EF1758 000000006447CEF0 000000006447DEF0 SGA 000000006447BEF0          1          4 SQLA^5ab90fa           4096 freeabl           0 0000000062EF1758 000000006447BEF0 000000006447CEF0 SGA 000000006396A4B0          1          1 KGLH0^5ab90fa          4096 recr           4095 0000000062ECBBE0 000000006396A4B0 000000006396B4B0 SGA 0000000062EF0F00          1          1 KGLH0^5ab90fa          4096 recr           4095 0000000062ECC440 0000000062EF0F00 0000000062EF1F00  KSMCHPAR=0000000062ECC440 SGA 0000000062EDC990          1          1 KGLH0^5ab90fa          4096 freeabl           0 0000000062ECBBE0 0000000062EDC990 0000000062EDD990 SGA 0000000062ECC3D0          1          1 KGLDA                   512 freeabl           0 00               0000000062ECC3D0 0000000062ECC5D0 9 rows selected.