[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.