170 lines
7.1 KiB
SQL
170 lines
7.1 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)
|
|
* 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
|