Fetch 'em all

Sometimes it's useful to have access to Exadata cells metrics directly from your session. One can query the v$cell% family of views and using standard way of accessing the XML document content via XMLType/XMLTable fetch the exposed metrics/statistics or cells' configuration. However not all cell metrics are exposed via v$cell%. One of the alternatives would be to call dcli out and ask for more :-) It's costly though to call it very often so it would make sense to have a GTT as an intermediate place to put the data in and do extra queries when/if needed.

Anyway, one can quickly get an idea of what's going on out there and fetch cells biometrics without leaving our lovely sqlplus :-)

SQL> CREATE OR REPLACE DIRECTORY AM_BIN AS '/home/oracle/bin';

Directory created.

SQL>
SQL> CREATE TABLE cell$ (
  2    cellname                         VARCHAR2(64)
  3  , name                             VARCHAR2(64)
  4  , alertState                       VARCHAR2(16)
  5  , collectionTime                   VARCHAR2(26)
  6  , objectType                       VARCHAR2(64)
  7  , persistencePolicy                VARCHAR2(64)
  8  , unit                             VARCHAR2(64)
  9  )
 10  ORGANIZATION EXTERNAL (
 11    TYPE ORACLE_LOADER
 12    DEFAULT DIRECTORY am_bin
 13    ACCESS PARAMETERS (
 14      RECORDS DELIMITED BY NEWLINE
 15      NOBADFILE
 16      NOLOGFILE
 17      NODISCARDFILE
 18      PREPROCESSOR am_bin:'fetch_metrics.sh'
 19      FIELDS TERMINATED BY 0x'09'
 20      LRTRIM
 21      MISSING FIELD VALUES ARE NULL
 22      (
 23        cellname                     CHAR(64)
 24      , name                         CHAR(64)
 25      , alertState                   CHAR(16)
 26      , collectionTime               CHAR(26)
 27      , objectType                   CHAR(64)
 28      , persistencePolicy            CHAR(64)
 29      , unit                         CHAR(64)
 30      )
 31    )
 32    LOCATION (
 33      am_bin:'fetch_metrics.sh'
 34    )
 35  )
 36  REJECT LIMIT UNLIMITED
 37  /

Table created.

SQL> SET TIMING ON ECHO ON
SQL> COLUMN cellname FORMAT A30
SQL> COLUMN run_queue FORMAT A10
SQL> COLUMN cpu_time FORMAT A10
SQL> SELECT *
  2    FROM (
  3         SELECT cellname
  4              , name
  5              , unit
  6           FROM cell$
  7          WHERE name IN (
  8                  'CL_RUNQ'
  9                , 'CL_CPUT'
 10                )
 11         )
 12   PIVOT (
 13     MAX(unit)
 14     FOR
 15     name IN (
 16       'CL_RUNQ' AS run_queue
 17     , 'CL_CPUT' AS cpu_time
 18     )
 19   )
 20  /
CELLNAME                       RUN_QUEUE  CPU_TIME
------------------------------ ---------- ----------
cel11                          0.1        1.4 %
cel12                          0.1        1.7 %
cel14                          0.1        1.3 %
cel13                          0.3        1.3 %

Elapsed: 00:00:04.52
SQL> 
SQL> !cat /home/oracle/bin/fetch_metrics.sh
#!/bin/sh
# NB: ":" is replaced by tab
PATH=/bin:/usr/bin:/usr/local/bin
dcli -g /home/oracle/cell_group \
  "cellcli -e \"list metriccurrent attributes all where objectType = 'CELL'\"" | \
  sed -e 's/:/\t/'

SQL>