sql/asmds.sql at master · JAC2703/sql · 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
--******************************************************************************
--*
--*  Description
--*  ***********
--*
--*  File:    asmds.sql
--*
--*  Description: This script shows ASM Disk stats.
--*
--* ----------------------------------------------------------------------------
--* Date         Author             Description
--* ===========  =================  ============================================
--* 03-JUN-2013  Jeff Moss          Created
--*

SET WRAP OFF
SET LINESIZE 155
SET PAGESIZE 9999
COLUMN inst_id HEADING "Inst|ID" FORMAT 999
COLUMN name FORMAT a30 HEADING "Disk|Name"
COLUMN read_time FORMAT 999,999,990 HEADING "Read Time|seconds"
COLUMN write_time FORMAT 999,999,990 HEADING "Write Time|seconds"
COLUMN gb_read FORMAT 999,999,990 HEADING "GB|Read"
COLUMN gb_written FORMAT 999,999,990 HEADING "GB|Wrote"
COLUMN read_ms FORMAT 999,999.00 HEADING "Avg Read|Speed ms"
COLUMN reads FORMAT 999,999,990 HEADING "Reads"
COLUMN write_ms FORMAT 999.00 HEADING "Avg Write|Speed ms"
COLUMN writes FORMAT B999,999,990 HEADING "Writes"

SELECT inst_id
,      name
,      read_time
,      (bytes_read/1073741824) gb_read
,      (case when reads = 0 then null else read_time*1000/reads end) read_ms
,      reads
,      write_time
,      (bytes_written/1073741824) gb_written
,      (CASE WHEN writes = 0 then null else write_time*1000/writes end) write_ms
,      writes
FROM   gv$asm_disk_stat
ORDER BY inst_id
,        disk_number
/