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