111 lines
4.4 KiB
SQL
111 lines
4.4 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display ASM Disk Statistics
|
|
* Parameter : 1 - DiskGroup Name (Default % , use / as escape character)
|
|
* 2 - FailGroup (Default % , use / as escape character)
|
|
* 3 - DiskName (Default % , use / as escape character)
|
|
*
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 07-MAY-14 Vishal Gupta Commented repair_timer, as its not disk io-statistics
|
|
* 21-Apr-13 Vishal Gupta Created
|
|
*
|
|
*
|
|
*/
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
UNDEFINE DISKGROUP_NAME
|
|
UNDEFINE FAILGROUP
|
|
UNDEFINE DISK_NAME
|
|
|
|
DEFINE DISKGROUP_NAME="&&1"
|
|
DEFINE FAILGROUP="&&2"
|
|
DEFINE DISK_NAME="&&3"
|
|
|
|
set term off
|
|
COLUMN _DISKGROUP_NAME NEW_VALUE DISKGROUP_NAME NOPRINT
|
|
COLUMN _FAILGROUP NEW_VALUE FAILGROUP NOPRINT
|
|
COLUMN _DISK_NAME NEW_VALUE DISK_NAME NOPRINT
|
|
|
|
SELECT UPPER(DECODE('&&DISKGROUP_NAME','','%','&&DISKGROUP_NAME')) "_DISKGROUP_NAME"
|
|
, UPPER(DECODE('&&FAILGROUP','','%','&&FAILGROUP')) "_FAILGROUP"
|
|
, UPPER(DECODE('&&DISK_NAME','','%','&&DISK_NAME')) "_DISK_NAME"
|
|
FROM DUAL;
|
|
set term on
|
|
|
|
PROMPT *******************************************
|
|
PROMPT A S M D I S K S T A T I S T I C S
|
|
PROMPT *
|
|
PROMPT * Input Parameters
|
|
PROMPT * - DiskGroup Name = '&&DISKGROUP_NAME'
|
|
PROMPT * - FailGroup = '&&FAILGROUP'
|
|
PROMPT * - DiskName = '&&DISK_NAME'
|
|
PROMPT *******************************************
|
|
|
|
COLUMN DiskGroup HEAD "DiskGroup" FORMAT A15
|
|
COLUMN failgroup HEAD "FailGroup" FORMAT A10
|
|
COLUMN name HEAD "ASM Disk Name" FORMAT A30
|
|
COLUMN repair_timer HEAD "Repair|Timer|(sec)" FORMAT 9,999
|
|
COLUMN read_time HEAD "Read|Time|(sec)" FORMAT 9,999
|
|
COLUMN reads HEAD "Total|Read|Requests|(#)" FORMAT 9,999,999
|
|
COLUMN hot_reads HEAD "Hot|Read|Requests|(#)" FORMAT 99,999
|
|
COLUMN cold_reads HEAD "Cold|Read|Requests|(#)" FORMAT 9,999,999
|
|
COLUMN read_errs HEAD "Read|Errors|(#)" FORMAT 9,999
|
|
COLUMN bytes_read HEAD "Reads|(GB)" FORMAT 9,999.99
|
|
COLUMN cold_bytes_read HEAD "Reads|Cold|(GB)" FORMAT 9,999.99
|
|
COLUMN hot_bytes_read HEAD "Reads|Hot|(GB)" FORMAT 9,999.99
|
|
COLUMN write_time HEAD "Write|Time|(sec)" FORMAT 9,999
|
|
COLUMN writes HEAD "Total|Write|Requests|(#)" FORMAT 9,999,999
|
|
COLUMN hot_writes HEAD "Hot|Write|Requests|(#)" FORMAT 99,999
|
|
COLUMN cold_writes HEAD "Cold|Write|Requests|(#)" FORMAT 9,999,999
|
|
COLUMN write_errs HEAD "Write|Errors|(#)" FORMAT 9,999
|
|
COLUMN bytes_written HEAD "Writes|(GB)" FORMAT 9,999.99
|
|
COLUMN hot_bytes_written HEAD "Writes|Cold|(GB)" FORMAT 9,999.99
|
|
COLUMN cold_bytes_written HEAD "Writes|Hot|(GB)" FORMAT 9,999.99
|
|
|
|
|
|
SELECT dg.NAME DiskGroup
|
|
, d.failgroup
|
|
, d.name
|
|
--, d.repair_timer
|
|
, d.preferred_read
|
|
, d.read_time
|
|
, d.reads
|
|
, d.hot_reads
|
|
, d.cold_reads
|
|
, d.read_errs
|
|
, ROUND(d.bytes_read/ 1024/1024/1024,2) bytes_read
|
|
, ROUND(d.hot_bytes_read/ 1024/1024/1024 ,2) hot_bytes_read
|
|
, ROUND(d.cold_bytes_read/ 1024/1024/1024 ,2) cold_bytes_read
|
|
, d.writes
|
|
, d.hot_writes
|
|
, d.cold_writes
|
|
, d.write_time
|
|
, d.write_errs
|
|
, ROUND(d.bytes_written / 1024/1024/1024 ,2) bytes_written
|
|
, ROUND(d.hot_bytes_written / 1024/1024/1024 ,2) hot_bytes_written
|
|
, ROUND(d.cold_bytes_written / 1024/1024/1024 ,2) cold_bytes_written
|
|
--, d.path
|
|
-- V$ASM_DISK_STAT does not perform disk discovery like V$ASM_DISK view.
|
|
FROM v$asm_disk_stat d
|
|
, v$asm_diskgroup_stat dg
|
|
WHERE d.group_number = dg.group_number
|
|
AND dg.name LIKE '&&DISKGROUP_NAME'
|
|
AND NVL(d.failgroup,'%') LIKE '&&FAILGROUP'
|
|
AND NVL(d.name,'%') LIKE '&&DISK_NAME'
|
|
ORDER BY dg.NAME
|
|
, d.FAILGROUP
|
|
, d.name
|
|
;
|
|
|
|
|
|
@@footer
|