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

Marco Gralike Written by:

2 Comments

  1. Jason H
    December 21

    I suppose one of the factors is the size of the XML in STATISTICS_VALUE. I don’t have Exadata access either so just curious.

    I am also wondering, if performance/speed is an issue with your above query, would it be beneficial to make a GTT that mimics sys.V_$CELL_STATE, but where STATISTICS_VALUE is a proper BINARY XMLType storage. This would make it a three step process clean out the GTT (COMMIT/DELETE), run an insert to populate, and then the query to extract the info.

    Since the information is real-time dynamic, you would still be seeing the same information, just with a bit longer delay to see the formatted results of the final SQL statement.

  2. December 21

    Valid remarks Jason. The GTT could be a good workaround, if performance was needed. I made the remark about performance more out frustration, due to the fact that Dev teams apparently never speak/share information with each other (but then again whats new). The APEX team is also still using the old deprecated operators and functions. And yes, also I have no clue what the content is in those columns. I had only the XML snippet Frits gave me, to work with.

Comments are closed.