VJOracle/ASM_Performance_check.txt at master · subratcall/VJOracle · 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
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
rem	Description: ASM diskgroup IO throughput and service time
rem *********************************************************


col name format a12 heading "Diskgroup|Name"
col type format a6 heading "Redundacy|Type"
col total_gb format 9,999 heading "Size|GB"
col active_disks format 99 heading "Active|Disks"
col reads1k format 9,999,999 heading "Reads|/1000"
col writes1k format 9,999,999 heading "Writes|/1000"
col read_time format 999,999 heading "Read Time|Secs"
col write_time format 999,999 heading "Write Time|Secs"
col avg_read_ms format 999.99 heading "Avg Read|ms"
set pagesize 1000
set lines 80
set echo on

SELECT name, ROUND(total_mb / 1024) total_gb, active_disks,
       reads / 1000 reads1k, writes / 1000 writes1k,
       ROUND(read_time) read_time, ROUND(write_time) write_time,
       ROUND(read_time * 1000 / reads, 2) avg_read_ms
FROM     v$asm_diskgroup_stat dg
     JOIN
         (SELECT group_number, COUNT(DISTINCT disk_number) active_disks,
                 SUM(reads) reads, SUM(writes) writes,
                 SUM(read_time) read_time, SUM(write_time) write_time
          FROM gv$asm_disk_stat
          WHERE mount_status = 'CACHED'
          GROUP BY group_number) ds
     ON (ds.group_number = dg.group_number)
ORDER BY dg.group_number;


Diskgroup      Size Active      Reads     Writes Read Time Write Time Avg Read
Name             GB  Disks      /1000      /1000      Secs       Secs       ms
------------ ------ ------ ---------- ---------- --------- ---------- --------
LDCPRDCRS        10      5         85      3,044       380     28,607     4.48
PRDWEU_DATA     225      3    132,677     71,774   982,868    868,784     7.41
PRDWEU_ARC      100      2        270        832     4,169      3,027    15.44


rem ***********************************************************
rem	Description: ASM disk-level throughput and service time
rem *********************************************************

col disk_path format a16 heading "Disk Path"
col total_mb format 999,999 heading "MB"
col avg_read_ms format 999.99 heading "Avg Read|(ms)"
col io_1k format  999,999 heading "IO|/1000"
col io_secs format  999,999 heading "IO|seconds"
col pct_io format 999.99 heading "Pct|IO"
col pct_time format 999.99 heading "Pct|Time"
set pagesize 10000
set lines 75
set verify off
set echo on

SELECT d.PATH disk_path, d.total_mb,
       ROUND(ds.read_secs * 1000 / ds.reads, 2) avg_read_ms,
       ds.reads/1000 +  ds.writes/1000 io_1k,
       ds.read_secs +ds.write_secs io_secs,
       ROUND((d.reads + d.writes) * 100 /
            SUM(d.reads + d.writes) OVER (),2) pct_io,
       ROUND((ds.read_secs +ds.write_secs)*100/
            SUM(ds.read_secs +ds.write_secs) OVER (),2) pct_time
  FROM v$asm_diskgroup_stat dg
  JOIN v$asm_disk_stat d ON (d.group_number = dg.group_number)
  JOIN (SELECT group_number, disk_number disk_number, SUM(reads) reads,
               SUM(writes) writes, ROUND(SUM(read_time), 2) read_secs,
               ROUND(SUM(write_time), 2) write_secs
          FROM gv$asm_disk_stat
         WHERE mount_status = 'CACHED'
         GROUP BY group_number, disk_number) ds
        ON (ds.group_number = d.group_number
            AND ds.disk_number = d.disk_number)
 WHERE dg.name = '&diskgroup_name'
   AND d.mount_status = 'CACHED'
 ORDER BY d.PATH;


Enter value for diskgroup_name: PRDWEU_DATA

                          Avg Read       IO       IO     Pct     Pct
Disk Path              MB     (ms)    /1000  seconds      IO    Time
---------------- -------- -------- -------- -------- ------- -------
ORCL:ASM01         76,795     7.19   70,783  603,541   34.18   32.59
ORCL:ASM02         76,795     8.20   67,521  629,440   32.96   33.99
ORCL:ASM03         76,795     6.85   66,156  618,692   32.86   33.41