@@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) * 4 - DBNAME (Default % , use / as escape character) * * * Revision History: * =================== * Date Author Description * --------- ------------ ----------------------------------------- * 22-Jan-15 Vishal Gupta Created * * */ /************************************ * INPUT PARAMETERS ************************************/ UNDEFINE DISKGROUP_NAME UNDEFINE FAILGROUP UNDEFINE DISK_NAME UNDEFINE DB_NAME DEFINE DISKGROUP_NAME="&&1" DEFINE FAILGROUP="&&2" DEFINE DISK_NAME="&&3" DEFINE DB_NAME="&&4" 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 COLUMN _DB_NAME NEW_VALUE DB_NAME NOPRINT SELECT UPPER(DECODE('&&DISKGROUP_NAME','','%','&&DISKGROUP_NAME')) "_DISKGROUP_NAME" , UPPER(DECODE('&&FAILGROUP','','%','&&FAILGROUP')) "_FAILGROUP" , UPPER(DECODE('&&DISK_NAME','','%','&&DISK_NAME')) "_DISK_NAME" , UPPER(DECODE('&&DB_NAME','','%','&&DB_NAME')) "_DB_NAME" FROM DUAL; set term on /************************************ * CONFIGURATION PARAMETERS ************************************/ DEFINE COUNT_SMALL_FORMAT=999,999 DEFINE COUNT_SMALL_DIVIDER="1" DEFINE COUNT_SMALL_HEADING="#" --DEFINE COUNT_SMALL_DIVIDER="1000" --DEFINE COUNT_SMALL_HEADING="#1000" DEFINE COUNT_FORMAT=9,999,999 --DEFINE COUNT_DIVIDER="1" --DEFINE COUNT_HEADING="#" DEFINE COUNT_DIVIDER="1000" DEFINE COUNT_HEADING="#1000" DEFINE COUNT_LARGE_FORMAT=999,999 --DEFINE COUNT_LARGE_DIVIDER="1" --DEFINE COUNT_LARGE_HEADING="#" DEFINE COUNT_LARGE_DIVIDER="1000" DEFINE COUNT_LARGE_HEADING="#1000" --DEFINE COUNT_LARGE_DIVIDER="1000000" --DEFINE COUNT_LARGE_HEADING="million" --DEFINE COUNT_LARGE_DIVIDER="1000000000" --DEFINE COUNT_LARGE_HEADING="billion" --DEFINE COUNT_LARGE_DIVIDER="1000000000000" --DEFINE COUNT_LARGE_HEADING="trillion" DEFINE BYTES_FORMAT="999,999" --DEFINE BYTES_DIVIDER="1024" --DEFINE BYTES_HEADING="KB" --DEFINE BYTES_DIVIDER="1024/1024" --DEFINE BYTES_HEADING="MB" DEFINE BYTES_DIVIDER="1024/1024/1024" DEFINE BYTES_HEADING="GB" DEFINE TIME_FORMAT=9,999 --DEFINE TIME_DIVIDER="1" --DEFINE TIME_HEADING="sec" --DEFINE TIME_DIVIDER="60" --DEFINE TIME_HEADING="min" DEFINE TIME_DIVIDER="3600" DEFINE TIME_HEADING="hr" /************************************/ PROMPT ************************************************** PROMPT * A S M D I S K I O 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 * - DB Name = '&&DB_NAME' PROMPT ************************************************** COLUMN DiskGroup HEAD "DiskGroup" FORMAT A15 COLUMN failgroup HEAD "FailGroup" FORMAT A10 COLUMN name HEAD "ASM Disk Name" FORMAT A30 COLUMN dbname HEAD "DB Name" FORMAT A10 COLUMN repair_timer HEAD "Repair|Timer|(&&TIME_HEADING)" FORMAT &&TIME_FORMAT COLUMN read_time HEAD "Read|Time|(&&TIME_HEADING)" FORMAT &&TIME_FORMAT COLUMN reads HEAD "Total|Read|Requests|(&&COUNT_LARGE_HEADING)" FORMAT &&COUNT_LARGE_FORMAT COLUMN hot_reads HEAD "Hot|Read|Requests|(&&COUNT_LARGE_HEADING)" FORMAT &&COUNT_LARGE_FORMAT COLUMN cold_reads HEAD "Cold|Read|Requests|(&&COUNT_LARGE_HEADING)" FORMAT &&COUNT_LARGE_FORMAT COLUMN read_errs HEAD "Read|Errors|(&&COUNT_SMALL_HEADING)" FORMAT &&COUNT_SMALL_FORMAT COLUMN bytes_read HEAD "Reads|(&&BYTES_HEADING)" FORMAT &&BYTES_FORMAT COLUMN cold_bytes_read HEAD "Reads|Cold|(&&BYTES_HEADING)" FORMAT &&BYTES_FORMAT COLUMN hot_bytes_read HEAD "Reads|Hot|(&&BYTES_HEADING)" FORMAT &&BYTES_FORMAT COLUMN write_time HEAD "Write|Time|(&&TIME_HEADING)" FORMAT &&TIME_FORMAT COLUMN writes HEAD "Total|Write|Requests|(&&COUNT_LARGE_HEADING)" FORMAT &&COUNT_LARGE_FORMAT COLUMN hot_writes HEAD "Hot|Write|Requests|(&&COUNT_LARGE_HEADING)" FORMAT &&COUNT_LARGE_FORMAT COLUMN cold_writes HEAD "Cold|Write|Requests|(&&COUNT_LARGE_HEADING)" FORMAT &&COUNT_LARGE_FORMAT COLUMN write_errs HEAD "Write|Errors|(&&COUNT_SMALL_HEADING)" FORMAT &&COUNT_SMALL_FORMAT COLUMN bytes_written HEAD "Writes|(&&BYTES_HEADING)" FORMAT &&BYTES_FORMAT COLUMN hot_bytes_written HEAD "Writes|Cold|(&&BYTES_HEADING)" FORMAT &&BYTES_FORMAT COLUMN cold_bytes_written HEAD "Writes|Hot|(&&BYTES_HEADING)" FORMAT &&BYTES_FORMAT SELECT dg.NAME DiskGroup , i.failgroup , d.name , i.dbname --, i.repair_timer , i.read_time/&&TIME_DIVIDER read_time , i.reads/&&COUNT_LARGE_DIVIDER reads , i.hot_reads/&&COUNT_LARGE_DIVIDER hot_reads , i.cold_reads/&&COUNT_LARGE_DIVIDER cold_reads , i.read_errs/&&COUNT_SMALL_DIVIDER read_errs , ROUND(i.bytes_read/ &&BYTES_DIVIDER,2) bytes_read , ROUND(i.hot_bytes_read/ &&BYTES_DIVIDER ,2) hot_bytes_read , ROUND(i.cold_bytes_read/ &&BYTES_DIVIDER ,2) cold_bytes_read , i.writes/&&COUNT_LARGE_DIVIDER writes , i.hot_writes/&&COUNT_LARGE_DIVIDER hot_writes , i.cold_writes/&&COUNT_LARGE_DIVIDER cold_writes , i.write_time/&&TIME_DIVIDER write_time , i.write_errs/&&COUNT_SMALL_DIVIDER write_errs , ROUND(i.bytes_written / &&BYTES_DIVIDER ,2) bytes_written , ROUND(i.hot_bytes_written / &&BYTES_DIVIDER ,2) hot_bytes_written , ROUND(i.cold_bytes_written / &&BYTES_DIVIDER ,2) cold_bytes_written --, i.path -- V$ASM_DISK_STAT does not perform disk discovery like V$ASM_DISK view. FROM v$asm_disk_iostat i JOIN v$asm_diskgroup_stat dg ON i.group_number = dg.group_number JOIN v$asm_disk_stat d ON d.group_number = i.group_number AND d.disk_number = i.disk_number WHERE 1=1 AND dg.name LIKE '&&DISKGROUP_NAME' AND NVL(i.failgroup,'%') LIKE '&&FAILGROUP' AND NVL(d.name,'%') LIKE '&&DISK_NAME' AND NVL(i.dbname,'%') LIKE '&&DB_NAME' ORDER BY dg.NAME , i.FAILGROUP , d.name , i.dbname ; @@footer