[20240922]建立完善tpt的fchaz.sql脚本.txt

--//原始的tpt fcha.sql脚本前面有一些警告信息，感觉在测试环境有一点繁琐，我取消这部分信息。
--//另外我以前加入了 KSMCHPTR的结尾信息KSMCHPTR_END，有一个缺点实际上计算方法是KSMCHPTR + KSMCHSIZ -1.
--//这样"显示"感觉不是很好。

--//实际上KSMCHPAR是堆地址描述符号，我非常容易搞混，我将KSMCHPTR字段再以KSMCHPTR_BEGIN字段显示。
--//具体修改如下：
$ cat  fchaz.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...

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"
from
    x$ksmsp
where
    to_number(substr('&1', instr(lower('&1'), 'x')+1) ,'XXXXXXXXXXXXXXXX')
    between
        to_number(ksmchptr,'XXXXXXXXXXXXXXXX')
    and to_number(ksmchptr,'XXXXXXXXXXXXXXXX') + ksmchsiz - 1
union all
select
    'UGA',
    KSMCHPTR,
    null,
    null,
    KSMCHCOM,
    KSMCHSIZ,
    KSMCHCLS,
    KSMCHTYP,
    KSMCHPAR,
    KSMCHPTR KSMCHPTR_BEGIN ,
        TO_CHAR(TO_NUMBER(KSMCHPTR,'XXXXXXXXXXXXXXXX') + KSMCHSIZ ,'FM0XXXXXXXXXXXXXXX') "KSMCHPTR_END+1"
from
    x$ksmup
where
    to_number(substr('&1', instr(lower('&1'), 'x')+1) ,'XXXXXXXXXXXXXXXX')
    between
        to_number(ksmchptr,'XXXXXXXXXXXXXXXX')
    and to_number(ksmchptr,'XXXXXXXXXXXXXXXX') + ksmchsiz - 1
union all
select
    'PGA',
    KSMCHPTR,
    null,
    null,
    KSMCHCOM,
    KSMCHSIZ,
    KSMCHCLS,
    KSMCHTYP,
    KSMCHPAR,
    KSMCHPTR KSMCHPTR_BEGIN ,
        TO_CHAR(TO_NUMBER(KSMCHPTR,'XXXXXXXXXXXXXXXX') + KSMCHSIZ ,'FM0XXXXXXXXXXXXXXX') "KSMCHPTR_END+1"
from
    x$ksmpp
where
    to_number(substr('&1', instr(lower('&1'), 'x')+1) ,'XXXXXXXXXXXXXXXX')
    between
        to_number(ksmchptr,'XXXXXXXXXXXXXXXX')
    and to_number(ksmchptr,'XXXXXXXXXXXXXXXX') + ksmchsiz - 1
/

--//显示例子：
SYS@book> @ fchaz 6caf3fd0
LOC KSMCHPTR           KSMCHIDX   KSMCHDUR KSMCHCOM           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR         KSMCHPTR_BEGIN   KSMCHPTR_END+1
--- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ---------------- ---------------- -----------------
SGA 000000006CAF3FD0          1          4 SQLA^5ab90fa           4096 recr           4095 000000006BEA8CE0 000000006CAF3FD0 000000006CAF4FD0

--//因为像KSMCHSIZ=4096,4096 = 0x1000 ,这样像结尾KSMCHPTR_BEGIN,KSMCHPTR_END+1显示比较"直观".