HOWTO: Viewing XML data in V_$CELL_% storage cell SYS views

Got a small question from Frits if I could help him make some XML data readable in one of the SYS.V_$CELL_% / V$CELL_% views. I have been a bit busy, in between jobs, to try to make some of those XML columns more readable anyway (for myself and others) so…lets have a go at it. To my surprise the Exadata cell storage columns have XML data stored in a CLOB column. In my perspective this is certainly not best “XML DB practice”, if not only, due to its negative performance impact it will have during querying that XML data, but anyway. I have found in my regular 10.2.0.3 EE database 2 views that apparently match the description Frits gave me, that is, are CLOB columns/v$CELL related.

I have no Exadata machine at home, so can’t really test my “solution” (which probably could be improved via some more real XQuery stuff), regarding CBO costs, although it shouldn’t be very good anyway. The XML Parser will do everything in memory via the following solution due to the “incorrect” CLOB column used for storing XML. Better would have been to implement an XMLType Binary XML column, but maybe there are “Exadata” reasons I can’t see, or aren’t explained, that validate this choice. In all, its Exadata anyway…

The following, based on a WITH AS sub query factoring setup (I don’t have any data), could be used to make the XML data in those columns, more readable.


WITH clob AS 
( select xmltype('<thread_stats>
    <stats type="general_stats">
      <stat name="kernel_threadid">109</stat>
      <stat name="job_type">NetworkRead</stat>
    </stats>
    <stats type="wait_stats">
      <stat name="thread_state">waiting_for_SKGXP_receive</stat>
      <stat name="thread_waitObjOwnLocation">-NA-</stat>
    </stats>
  </thread_stats>') as xmlcol
  from dual
)
SELECT r1.kernelthreadid as "KERNEL_THREADID",
       r1.jobtype        as "JOB_TYPE",
       r2.threadstate    as "THREAD_STATE"
FROM   CLOB x
,      XMLTABLE ('/thread_stats'
                 PASSING x.xmlcol
                 COLUMNS threadstats XMLTYPE PATH '*'
                ) xt ,
       XMLTABLE ('stats[@type="general_stats"]'
                 PASSING xt.threadstats
                 COLUMNS kernelthreadid PATH 'stat[@name="kernel_threadid"]',
                         jobtype        PATH 'stat[@name="job_type"]'
                ) r1 ,
       XMLTABLE ('stats[@type="wait_stats"]'
                 PASSING xt.threadstats
                 COLUMNS threadstate     PATH 'stat[@name="thread_state"]'
                ) r2                       
;   


KERNEL_THREADID JOB_TYPE    THREAD_STATE
--------------- ----------- -------------------------
109             NetworkRead waiting_for_SKGXP_receive

1 row selected

The SYS views V_$CELL_STATE (V$CELL_STATE – STATISTICS_VALUE column) and V_$CELL_CONFIG (V$CELL_CONFIG – CONFVAL column) look as the ones Frits was referring to.

The actual query on those views would be something like

SELECT r1.kernelthreadid as "KERNEL_THREADID",
       r1.jobtype        as "JOB_TYPE",
       r2.threadstate    as "THREAD_STATE"
FROM   sys.V_$CELL_STATE x
,      XMLTABLE ('/thread_stats'
                 PASSING xmltype(x.STATISTICS_VALUE)
                 COLUMNS threadstats XMLTYPE PATH '*'
                ) xt ,
       XMLTABLE ('stats[@type="general_stats"]'
                 PASSING xt.threadstats
                 COLUMNS kernelthreadid PATH 'stat[@name="kernel_threadid"]',
                         jobtype        PATH 'stat[@name="job_type"]'
                ) r1 ,
       XMLTABLE ('stats[@type="wait_stats"]'
                 PASSING xt.threadstats
                 COLUMNS threadstate     PATH 'stat[@name="thread_state"]'
                ) r2                       
;   

For those out there handling Exadata environments, and for future reference for me, hope this helps a bit as a start for your own solution / view / or etc.

HTH