104 lines
4.0 KiB
MySQL
104 lines
4.0 KiB
MySQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display Exadata Cell Statistics
|
|
* Parameters : 1 - Cell Name (Default value %)
|
|
* 2 - Statistics Type (Default value 'CELL')
|
|
* 3 - Statistics Sub-Type (Default value '%')
|
|
* 4 - Statistics Name (Default value %)
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ ---------------------------------------------------------
|
|
* 20-Apr-15 Vishal Gupta Added statistics type and sub-type as input parameters
|
|
* 12-Jun-13 Vishal Gupta Fixed the XML Query filtering for input parameters
|
|
* 20-Feb-13 Vishal Gupta Created
|
|
*
|
|
*/
|
|
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
UNDEFINE cell_name
|
|
UNDEFINE statistics_type
|
|
UNDEFINE statistics_subtype
|
|
UNDEFINE stat_name
|
|
|
|
DEFINE cell_name="&&1"
|
|
DEFINE statistics_type="&&2"
|
|
DEFINE statistics_subtype="&&3"
|
|
DEFINE stat_name="&&4"
|
|
|
|
set term off
|
|
COLUMN _cell_name NEW_VALUE cell_name NOPRINT
|
|
COLUMN _statistics_type NEW_VALUE statistics_type NOPRINT
|
|
COLUMN _statistics_subtype NEW_VALUE statistics_subtype NOPRINT
|
|
COLUMN _stat_name NEW_VALUE stat_name NOPRINT
|
|
|
|
SELECT DECODE('&&cell_name','','%','&&cell_name') "_cell_name"
|
|
, DECODE('&&statistics_type','','CELL','&&statistics_type') "_statistics_type"
|
|
, DECODE('&&statistics_subtype','','%','&&statistics_subtype') "_statistics_subtype"
|
|
, 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 S T A T I S T I C S
|
|
PROMPT *
|
|
PROMPT * Input Parameter
|
|
PROMPT * - Cell Name = "&&cell_name"
|
|
PROMPT * - Statistics Type = "&&statistics_type"
|
|
PROMPT * - Statistics Sub-Type = "&&statistics_subtype"
|
|
PROMPT * - Statistics Name = "&&stat_name"
|
|
PROMPT *****************************************************
|
|
|
|
|
|
COLUMN cell_name HEADING "CellName" FORMAT a18
|
|
COLUMN statistics_type HEADING "StatisticType" FORMAT a15
|
|
COLUMN statistics_subtype HEADING "StatisticSubType" FORMAT a30
|
|
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.statistics_type
|
|
, cell_stats.statistics_subtype
|
|
, stat.name stat_name
|
|
, stat.value value
|
|
FROM v$cell_config cc
|
|
, v$cell_state cs
|
|
, XMLTable('/cell_stats/stats' PASSING XMLTYPE(cs.statistics_value)
|
|
COLUMNS
|
|
statistics_subtype VARCHAR2(100) PATH './@type'
|
|
, stat XMLType PATH 'stat'
|
|
) cell_stats
|
|
, XMLTable('/stat' PASSING cell_stats.stat
|
|
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 NOT IN ( 'SENDPORT' )
|
|
AND cell_stats.statistics_subtype IS NOT NULL
|
|
)
|
|
WHERE cell_name LIKE '&&cell_name'
|
|
AND statistics_type LIKE '&&statistics_type'
|
|
AND statistics_subtype LIKE '&&statistics_subtype'
|
|
AND stat_name LIKE '&&stat_name'
|
|
ORDER BY cell_name
|
|
, statistics_type
|
|
, stat_name
|
|
;
|
|
|
|
|
|
@@footer |