@@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