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.

[code language=”sql”]

WITH clob AS
( select xmltype(‘
109
NetworkRead


waiting_for_SKGXP_receive
-NA-
‘) 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

[/code]

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

[code language=”sql”]
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
;
[/code]

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