90 lines
3.1 KiB
MySQL
90 lines
3.1 KiB
MySQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display Exadata Cell FlashLog Statistics
|
|
* Parameters : 1 - Cell Name (Default value %)
|
|
* 2 - Statistics Type (Default value %)
|
|
* 3 - Statistics Name (Default value %)
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ ---------------------------------------------------------
|
|
* 20-Apr-15 Vishal Gupta Created
|
|
*
|
|
*/
|
|
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
UNDEFINE cell_name
|
|
UNDEFINE statistics_type
|
|
--UNDEFINE statistics_subtype
|
|
UNDEFINE stat_name
|
|
|
|
DEFINE cell_name="&&1"
|
|
DEFINE FlashDiskName="&&2"
|
|
--DEFINE statistics_subtype="&&3"
|
|
DEFINE stat_name="&&3"
|
|
|
|
set term off
|
|
COLUMN _cell_name NEW_VALUE cell_name NOPRINT
|
|
COLUMN _FlashDiskName NEW_VALUE FlashDiskName NOPRINT
|
|
COLUMN _stat_name NEW_VALUE stat_name NOPRINT
|
|
|
|
SELECT DECODE('&&cell_name','','%','&&cell_name') "_cell_name"
|
|
, DECODE('&&FlashDiskName','','%','&&FlashDiskName') "_FlashDiskName"
|
|
, DECODE('&&stat_name','','%','&&stat_name') "_stat_name"
|
|
FROM DUAL;
|
|
set term on
|
|
|
|
|
|
PROMPT
|
|
PROMPT *********************************************************************
|
|
PROMPT * E X A D A T A C E L L F L A S H L O G S T A T I S T I C S
|
|
PROMPT *
|
|
PROMPT * Input Parameter
|
|
PROMPT * - Cell Name = "&&cell_name"
|
|
PROMPT * - FlashDiskName = "&&FlashDiskName"
|
|
PROMPT * - Statistics Name = "&&stat_name"
|
|
PROMPT *********************************************************************
|
|
|
|
|
|
COLUMN cell_name HEADING "CellName" FORMAT a18
|
|
COLUMN object_name HEADING "FlashDiskName" FORMAT a20
|
|
COLUMN stat_name HEADING "StatisticName" FORMAT a42
|
|
COLUMN value HEADING "Value" FORMAT a50
|
|
|
|
-- Need to use the sub-query as filtering for stat.name and cell_stats.statistics_type
|
|
-- was not working with XML query. But if subquery is used, then it works fine.
|
|
SELECT * FROM
|
|
(SELECT EXTRACTVALUE(XMLTYPE(cc.confval),'/cli-output/cell/name') cell_name
|
|
, cs.object_name
|
|
, stat.name stat_name
|
|
, stat.value value
|
|
FROM v$cell_config cc
|
|
, v$cell_state cs
|
|
, XMLTable('/flashlog_stats/stat' PASSING XMLTYPE(cs.statistics_value)
|
|
COLUMNS
|
|
name VARCHAR2(100) PATH './@name'
|
|
, value VARCHAR2(100) PATH '/stat'
|
|
) stat
|
|
|
|
WHERE cc.cellname = cs.cell_name
|
|
AND cc.conftype = 'CELL'
|
|
AND cs.statistics_type ='FLASHLOG'
|
|
--AND cell_stats.statistics_subtype IS NOT NULL
|
|
)
|
|
WHERE cell_name LIKE '&&cell_name'
|
|
AND object_name LIKE '&&FlashDiskName'
|
|
AND stat_name LIKE '&&stat_name'
|
|
ORDER BY cell_name
|
|
, object_name
|
|
, stat_name
|
|
;
|
|
|
|
|
|
@@footer |