forked from abdulirfan3/Oracle_SQL_Scripts
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathasmm_info_mem.sql
149 lines (135 loc) · 8.75 KB
/
asmm_info_mem.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
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
@@header
set term off
/*
*
* Author : Vishal Gupta
* Purpose : Display Tablespace usage
*
* Revision History:
* ===================
* Date Author Description
* --------- ------------ -----------------------------------------
* 15-SEP-08 Vishal Gupta First Draft
* 14-MAR-12 Vishal Gupta Modified to make it RAC friendly
* 28-MAY-12 Vishal Gupta Fixed to differentiate between instance specific
* and generic (*.param) parameter values in spfile
*
*/
set term on
COLUMN inst_id HEADING "Inst#" FORMAT 99999
COLUMN name HEADING "Name" FORMAT a21
COLUMN current_value1 HEADING "Current" FORMAT a10
COLUMN spfile_value1 HEADING "SPFile" FORMAT a10
COLUMN current_value2 HEADING "Current" FORMAT a10
COLUMN spfile_value2 HEADING "SPFile" FORMAT a10
COLUMN current_value3 HEADING "Current" FORMAT a10
COLUMN spfile_value3 HEADING "SPFile" FORMAT a10
COLUMN current_value4 HEADING "Current" FORMAT a10
COLUMN spfile_value4 HEADING "SPFile" FORMAT a10
COLUMN current_value5 HEADING "Current" FORMAT a10
COLUMN spfile_value5 HEADING "SPFile" FORMAT a10
COLUMN current_value6 HEADING "Current" FORMAT a10
COLUMN spfile_value6 HEADING "SPFile" FORMAT a10
COLUMN current_value7 HEADING "Current" FORMAT a10
COLUMN spfile_value7 HEADING "SPFile" FORMAT a10
COLUMN current_value8 HEADING "Current" FORMAT a10
COLUMN spfile_value8 HEADING "SPFile" FORMAT a10
COLUMN component HEADING "Component" FORMAT a24
COLUMN user_specified_size HEADING "User|Specified|(MB)" FORMAT 99,999,999
COLUMN current_size HEADING "Current|(MB)" FORMAT 99,999,999
COLUMN free_size HEADING "Free|(MB)" FORMAT 99,999,999
COLUMN min_size HEADING "Min|(MB)" FORMAT 99,999,999
COLUMN max_size HEADING "Max|(MB)" FORMAT 99,999,999
COLUMN GRANULE_SIZE HEADING "Granule|(MB)" FORMAT 9,999
COLUMN last_oper_type HEADING "Last|Operation|Type" FORMAT a12
COLUMN oper_count HEADING "Operation|Count" FORMAT 99,999,999
COLUMN last_oper_time HEADING "Last|Operation|Time" FORMAT a18
BREAK ON REPORT
COMPUTE SUM LABEL 'Total' OF current_size FORMAT 99,999,999 ON REPORT
COMPUTE SUM LABEL 'Total' OF free_size FORMAT 99,999,999 ON REPORT
PROMPT
PROMPT ####################################################################
PROMPT ####### #######
PROMPT ####### Automatic Shared Memory Management Settings #######
PROMPT ####### #######
PROMPT ####################################################################
PROMPT
PROMPT - <---- Instance 1 ---> <---- Instance 2 ---> <---- Instance 3 ---> <---- Instance 4 ---> <---- Instance 5 ---> <---- Instance 6 ---> <---- Instance 7 ---> <---- Instance 8 --->
select RPAD(pp.name,20) || '|' name
, MAX(DECODE(pp.inst_id, 1, pp.display_value,NULL)) current_value1
, MAX(DECODE(pp.inst_id, 1, RPAD(NVL(NVL(sp.display_value,sp_generic.display_value),' '),9) || '|' ,NULL)) spfile_value1
, MAX(DECODE(pp.inst_id, 2, pp.display_value,NULL)) current_value2
, MAX(DECODE(pp.inst_id, 2, RPAD(NVL(NVL(sp.display_value,sp_generic.display_value),' '),9) || '|' ,NULL)) spfile_value2
, MAX(DECODE(pp.inst_id, 3, pp.display_value,NULL)) current_value3
, MAX(DECODE(pp.inst_id, 3, RPAD(NVL(NVL(sp.display_value,sp_generic.display_value),' '),9) || '|' ,NULL)) spfile_value3
, MAX(DECODE(pp.inst_id, 4, pp.display_value,NULL)) current_value4
, MAX(DECODE(pp.inst_id, 4, RPAD(NVL(NVL(sp.display_value,sp_generic.display_value),' '),9) || '|' ,NULL)) spfile_value4
, MAX(DECODE(pp.inst_id, 5, pp.display_value,NULL)) current_value5
, MAX(DECODE(pp.inst_id, 5, RPAD(NVL(NVL(sp.display_value,sp_generic.display_value),' '),9) || '|' ,NULL)) spfile_value5
, MAX(DECODE(pp.inst_id, 6, pp.display_value,NULL)) current_value6
, MAX(DECODE(pp.inst_id, 6, RPAD(NVL(NVL(sp.display_value,sp_generic.display_value),' '),9) || '|' ,NULL)) spfile_value6
, MAX(DECODE(pp.inst_id, 7, pp.display_value,NULL)) current_value7
, MAX(DECODE(pp.inst_id, 7, RPAD(NVL(NVL(sp.display_value,sp_generic.display_value),' '),9) || '|' ,NULL)) spfile_value7
, MAX(DECODE(pp.inst_id, 8, pp.display_value,NULL)) current_value8
, MAX(DECODE(pp.inst_id, 8, RPAD(NVL(NVL(sp.display_value,sp_generic.display_value),' '),9) || '|' ,NULL)) spfile_value8
from gv$system_parameter pp
JOIN gv$instance i ON pp.inst_id = i.inst_id
LEFT OUTER JOIN gv$spparameter sp ON pp.inst_id = sp.inst_id AND sp.sid = i.instance_name and pp.name = sp.name
LEFT OUTER JOIN gv$spparameter sp_generic ON sp_generic.inst_id = pp.inst_id AND sp_generic.sid = '*' and sp_generic.name = pp.name
WHERE ( pp.name in ('memory_target'
,'memory_max_target'
,'sga_target'
,'sga_max_size'
,'lock_sga'
,'pre_page_sga'
,'pga_aggregate_target'
,'large_pool_size'
,'use_large_pages'
)
)
GROUP BY pp.name
UNION ALL
-- Get Host physical memory
select RPAD(os.stat_name,20) || '|' name
, MAX(DECODE(os.inst_id, 1, ROUND(os.value/1024/1024/1024) || 'G',NULL)) current_value1
, MAX(DECODE(os.inst_id, 1, RPAD(NVL(ROUND(os.value/1024/1024/1024) || 'G',' '),9) || '|' ,NULL)) spfile_value1
, MAX(DECODE(os.inst_id, 2, ROUND(os.value/1024/1024/1024) || 'G',NULL)) current_value2
, MAX(DECODE(os.inst_id, 2, RPAD(NVL(ROUND(os.value/1024/1024/1024) || 'G',' '),9) || '|' ,NULL)) spfile_value2
, MAX(DECODE(os.inst_id, 3, ROUND(os.value/1024/1024/1024) || 'G',NULL)) current_value3
, MAX(DECODE(os.inst_id, 3, RPAD(NVL(ROUND(os.value/1024/1024/1024) || 'G',' '),9) || '|' ,NULL)) spfile_value3
, MAX(DECODE(os.inst_id, 4, ROUND(os.value/1024/1024/1024) || 'G',NULL)) current_value4
, MAX(DECODE(os.inst_id, 4, RPAD(NVL(ROUND(os.value/1024/1024/1024) || 'G',' '),9) || '|' ,NULL)) spfile_value4
, MAX(DECODE(os.inst_id, 5, ROUND(os.value/1024/1024/1024) || 'G',NULL)) current_value5
, MAX(DECODE(os.inst_id, 5, RPAD(NVL(ROUND(os.value/1024/1024/1024) || 'G',' '),9) || '|' ,NULL)) spfile_value5
, MAX(DECODE(os.inst_id, 6, ROUND(os.value/1024/1024/1024) || 'G',NULL)) current_value6
, MAX(DECODE(os.inst_id, 6, RPAD(NVL(ROUND(os.value/1024/1024/1024) || 'G',' '),9) || '|' ,NULL)) spfile_value6
, MAX(DECODE(os.inst_id, 7, ROUND(os.value/1024/1024/1024) || 'G',NULL)) current_value7
, MAX(DECODE(os.inst_id, 7, RPAD(NVL(ROUND(os.value/1024/1024/1024) || 'G',' '),9) || '|' ,NULL)) spfile_value7
, MAX(DECODE(os.inst_id, 8, ROUND(os.value/1024/1024/1024) || 'G',NULL)) current_value8
, MAX(DECODE(os.inst_id, 8, RPAD(NVL(ROUND(os.value/1024/1024/1024) || 'G',' '),9) || '|' ,NULL)) spfile_value8
from gv$osstat os
where os.stat_name = 'PHYSICAL_MEMORY_BYTES'
GROUP BY os.stat_name
ORDER BY 1
/
BREAK ON inst_id SKIP 1
SELECT c.inst_id
, c.component
, ROUND(c.user_specified_size / 1024 / 1024) user_specified_size
, ROUND(c.current_size / 1024 / 1024) current_size
, ROUND(NVL(s.bytes,0) / 1024 / 1024) free_size
, ROUND(c.min_size / 1024 / 1024) min_size
, ROUND(c.max_size / 1024 / 1024) max_size
, ROUND(c.GRANULE_SIZE / 1024 / 1024) GRANULE_SIZE
, c.oper_count
, c.last_oper_type
, to_char(c.last_oper_time,'DD-MON-YY hh24:mi:Ss') last_oper_time
FROM gv$sga_dynamic_components c
, GV$SGASTAT s
WHERE c.inst_id = s.inst_id (+)
AND c.component = s.pool (+)
AND s.name (+) = 'free memory'
AND c.current_size <> 0
ORDER BY c.inst_id , c.component
/
@@footer