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

[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');