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

[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显示比较"直观".