/
curheaps.sql
128 lines (114 loc) · 3.12 KB
/
curheaps.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
-- 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
--
-- 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
select
KGLNAHSH,
KGLHDPAR,
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
KGLNAHSH in (&1)
and KGLOBT09 like ('&2')
order by
KGLOBT09 ASC
/
-- 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');