-
Notifications
You must be signed in to change notification settings - Fork 4
/
Copy pathasm.sql
146 lines (119 loc) · 2.99 KB
/
asm.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
--==============================================================================
-- GPI - Gunther Pippèrr
-- Desc: SQL Script ASM Disk Overview
-- Date: 2012
--==============================================================================
set linesize 130 pagesize 300
spool asm.log
ttitle left "ASM Disk Status and Size" skip 2
define dnum = "format 999G999G999G999D99"
define lnum = "format 9G999G999"
define num = "format 99G999"
define snum = "format 9G999"
column total_mb &&lnum
column group_number format 99 heading "Grp Nr."
column inst_id format 99 heading "Inst"
column status format A6
column state format A7
column type format A6
column name format A20
column free &&lnum
column used &&lnum
column writes &&dnum
column reads &&dnum
column read_errs &&snum heading "R Er."
column write_errs &&snum heading "W Er."
column r_tim &&dnum heading "R Tim."
column w_tim &&dnum heading "W Tim."
column bytes_read &&num
column bytes_written &&num
column INSTANCE_NAME format A10
select group_number
,name
,state
,type
,total_mb Brutto
,decode(type,'NORMAL',total_mb/2,total_mb) Netto
,usable_file_mb as free_netto
--,total_mb - usable_file_mb as used
from v$asm_diskgroup
order by name
/
prompt ----------------------
ttitle left "ASM User" skip 2
column status format A10
select inst_id
,GROUP_NUMBER
,INSTANCE_NAME
,STATUS
from gv$ASM_CLIENT
order by inst_id
,GROUP_NUMBER
/
ttitle left "ASM Disks"
prompt ----------------------
column diskpath format A15
column name format A12
select d.GROUP_NUMBER
, g.name
, d.name
, d.path as diskpath
, d.TOTAL_MB
, d.FREE_MB
, d.total_mb - d.free_mb as used
from v$asm_disk d
, v$asm_diskgroup g
where g.GROUP_NUMBER = d.GROUP_NUMBER
order by 1
/
ttitle left "ASM Disk Extend distribution"
prompt ----------------------
select count(pxn_kffxp) as count_extents
, disk_kffxp as disk
, group_kffxp as diskgroup
from x$kffxp
group by disk_kffxp
,group_kffxp
order by diskgroup,disk
/
prompt ----------------------
ttitle left "ASM Disk Performance" skip 2
column name format A7
select inst_id
,group_number
,replace(name, '_0000', '') as name
,reads
,writes
,read_errs
-- , write_errs
-- , bytes_read/read_time
-- , bytes_written/write_time
-- , bytes_read
-- , bytes_written
from gv$asm_disk_stat
where group_number > 0
order by inst_id
,group_number
,disk_number;
--prompt
--prompt R Er. : read Errors
--prompt w Er. : write Errors
--prompt r_tim : read time in cs
--prompt w_tim : write time in cs
prompt
/*
ttitle left "ASM Files on Storage" skip 2
column name format a30
select f.group_number
,f.FILE_NUMBER
,f.BYTES
,a.name
from v$asm_file f
,v$asm_alias a
where f.file_number = a.file_number
and f.group_number = a.group_number
order by f.file_number
/
*/
ttitle off
spool off