[20230123]完善curheapz.sql脚本.txt

--//我发现tpt的curheaps.sql脚本在第2个输入%的情况下无法控制display last child cursor heap0,heap4,heap6 message using x$ksmhp.
--//而且脚本查询hash_value,不支持sql_id,修改一下,并且增加参数&3 => desc asc,控制查询子光标还是父光标.
--//desc 查询子光标 asc 查询父光标. 如果出现多了子光标,中间的无法查询.
--//重新命名为curheapz.sql

$ cat curheapz.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:   curheaps.sql
-- Purpose:     Show main cursor data block heap sizes and their contents
--              (heap0 and heap6)
--
-- Author:      Tanel Poder
-- Copyright:   (c) http://www.tanelpoder.com
--              
-- Usage:       @curheaps <hash_value> <child#>
--
--              @curheaps 942515969 %   -- shows a summary of cursor heaps
--                @curheaps 942515969 0   -- shows detail for child cursor 0
--
--
--              @curheapz 4xamnunv51w9j 0           -- query sql_id
--              @curheapz 0 942515969               -- query hash
--              @curheapz 4xamnunv51w9j 942515969   -- query sql_id or hash
--
--              display last child cursor heap0,heap4,heap6 message using x$ksmhp
--
-- Other:       "Child" cursor# 65535 is actually the parent cursor
--
--------------------------------------------------------------------------------

col curheaps_size0 heading SIZE0 for 9999999
col curheaps_size1 heading SIZE1 for 9999999
col curheaps_size2 heading SIZE2 for 9999999
col curheaps_size3 heading SIZE3 for 9999999
col curheaps_size4 heading SIZE4 for 9999999
col curheaps_size5 heading SIZE5 for 9999999
col curheaps_size6 heading SIZE6 for 9999999
col curheaps_size7 heading SIZE7 for 9999999

col KGLOBHD0 new_value v_curheaps_kglobhd0 print
col KGLOBHD1 new_value v_curheaps_kglobhd1 noprint
col KGLOBHD2 new_value v_curheaps_kglobhd2 noprint
col KGLOBHD3 new_value v_curheaps_kglobhd3 noprint
col KGLOBHD4 new_value v_curheaps_kglobhd4 print
col KGLOBHD5 new_value v_curheaps_kglobhd5 noprint
col KGLOBHD6 new_value v_curheaps_kglobhd6 print
col KGLOBHD7 new_value v_curheaps_kglobhd7 noprint

set termout off
column v_sort new_value v_sort
select decode(lower('&3'),'p','asc','parent','asc','c','desc','child','desc',lower('&3')) v_sort from dual;
set termout on

select /*+ USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(1))  */
    KGLNAHSH,
    KGLHDPAR,
    kglobt03 SQL_ID,
    kglobt09 CHILD#,
    KGLHDADR,
    KGLOBHD0, KGLOBHS0 curheaps_size0,
    KGLOBHD1, KGLOBHS1 curheaps_size1,
    KGLOBHD2, KGLOBHS2 curheaps_size2,
    KGLOBHD3, KGLOBHS3 curheaps_size3,
    KGLOBHD4, KGLOBHS4 curheaps_size4,
    KGLOBHD5, KGLOBHS5 curheaps_size5,
    KGLOBHD6, KGLOBHS6 curheaps_size6,
    KGLOBHD7, KGLOBHS7 curheaps_size7,
--    KGLOBT00 CTXSTAT,
    KGLOBSTA STATUS
from
    X$KGLOB
--    X$KGLCURSOR_CHILD
where
    KGLOBT03 = '&1'  or KGLNAHSH= &2
--and    KGLNAHSH in (&1)
--and    KGLOBT09 like ('&2')
order by
        KGLOBT09 &&v_sort
/

-- Cursor data block summary
select
   'HEAP0'        heap
  , ksmchcls      class
  , ksmchcom      alloc_comment
  , sum(ksmchsiz) bytes
  , count(*)      chunks
from
    x$ksmhp
where
    KSMCHDS = hextoraw('&v_curheaps_kglobhd0')
group by
   'HEAP0'
  , ksmchcls
  , ksmchcom
order by
    sum(ksmchsiz) desc
/

select
   'HEAP4'        heap
  , ksmchcls      class
  , ksmchcom      alloc_comment
  , sum(ksmchsiz) bytes
  , count(*)      chunks
from
    x$ksmhp
where
    KSMCHDS = hextoraw('&v_curheaps_kglobhd4')
group by
   'HEAP4'
  , ksmchcls
  , ksmchcom
order by
    sum(ksmchsiz) desc
/



select
   'HEAP6'        heap
  , ksmchcls      class
  , ksmchcom      alloc_comment
  , sum(ksmchsiz) bytes
  , count(*)      chunks
from
    x$ksmhp
where
    KSMCHDS = hextoraw('&v_curheaps_kglobhd6')
group by
   'HEAP6'
  , ksmchcls
  , ksmchcom
order by
    sum(ksmchsiz) desc
/


-- Cursor data block details

-- select * from x$ksmhp where KSMCHDS = hextoraw('&v_curheaps_kglobhd0');
-- select * from x$ksmhp where KSMCHDS = hextoraw('&v_curheaps_kglobhd6');