oracle_sql_scripts/pga_sid.sql at master · jeremybaumont/oracle_sql_scripts · GitHub

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
-- File name:   pga_sid.sql
-- Purpose:     display memory info for a sid
-- Author:      Jeremy Baumont
-- Copyright:   Apache License, Version 2.0
--
-- Usage:       @pga_sid  <OWNER>
--------------------------------------------------------------------------------

define m_sid = &1

column name format a40

column	value		format	999,999,999,999

column	category	format	a10
column	allocated	format	999,999,999,999
column	used		format	999,999,999,999
column	max_allocated	format	999,999,999,999

column	pga_used_mem		format	999,999,999,999
column	pga_alloc_mem		format	999,999,999,999
column	pga_freeable_mem	format	999,999,999,999
column	pga_max_mem		format	999,999,999,999

select
	name, value
from
	v$sesstat ss,
	v$statname sn
where
	sn.name like '%ga memory%'
and	ss.statistic# = sn.statistic#
and	ss.sid = &m_sid
;

select
	category,
	allocated,
	used,
	max_allocated
from
	v$process_memory
where
	pid = (
		select	pid
		from	v$process
		where
			addr = (
				select	paddr
				from	V$session
				where	sid = &m_sid
			)
		)
;

select
	pga_used_mem,
	pga_alloc_mem,
	pga_freeable_mem,
	pga_max_mem
from
	v$process
where
	addr = (
		select	paddr
		from	V$session
		where	sid = &m_sid
	)
;