While researching Exadata 12.2 features that have been introduced with Oracle 220.127.116.11.0 I ran into an old problem of mine, querying the v$cell% views that are new with Exadata 18.104.22.168.0 and Oracle RDBSM 12c Release 1 in a meaningful way. In case you haven’t seen them yet, these views expose cell metrics and other information in the database without having to actually connect to the cells and execute cellcli commands. They are very useful, and form the basis of the latter part of the Exadata AWR reports.
On our 12.2 Exadata system (using cellos 22.214.171.124.0 and RDBMS 126.96.36.199.0) the following views are exposed to the curious researcher:
SQL> select table_name from dict where table_name like 'V%CELL%' order by 1; TABLE_NAME ------------------------------------- V$CELL V$CELL_CONFIG V$CELL_CONFIG_INFO V$CELL_DB V$CELL_DB_HISTORY V$CELL_DISK V$CELL_DISK_HISTORY V$CELL_GLOBAL V$CELL_GLOBAL_HISTORY V$CELL_IOREASON V$CELL_IOREASON_NAME V$CELL_METRIC_DESC V$CELL_OFL_THREAD_HISTORY V$CELL_OPEN_ALERTS V$CELL_REQUEST_TOTALS V$CELL_STATE V$CELL_THREAD_HISTORY 17 rows selected.
There is a lot of useful info to be found in these, and most of the views are explained in Expert Oracle Exadata (2nd edition), chapter 11. I don’t always have a copy of the book at hand which is why I am putting this post together. To add some value, I am going to try and explain how I get to the results as well :)
Querying the views
For example if you are interested in your cell’s configuration, v$cell_config can be queried.
SQL> describe v$cell_config Name Null? Type ----------------------------------------- -------- ---------------------------- CELLNAME VARCHAR2(1024) CELLHASH NUMBER CONFTYPE VARCHAR2(15) CONFVAL CLOB CON_ID NUMBER SQL>
This view is a good representation of the way most of them are set up. There is a cell identifier (CELLNAME in the example), a configuration type (CONFTYPE) and then actual data stored as XML (stored as CONFVAL). Instead of storing the XML information as an XMLTYPE, a CLOB is more commonly found. When getting data from the CLOB it is often difficult to read, which is why I usually cast it to an XMLTYPE which formats nicer in SQLcl and SQLPlus.
A small example of getting information from this view is shown here. I am interested in the IORM plan for a specific cell:
SQL> select xmltype(confval) from v$cell_config where cellname = '192.168.10.9' and conftype = 'IORM'; XMLTYPE(CONFVAL) -------------------------------------------------------------------------------- <?xml version="1.0" encoding="US-ASCII"?> <cli-output> <version>1.0</version> <timestamp>1498120597819</timestamp> <context cell="someCell"/> <iormplan> <name>someCell_IORMPLAN</name> <catPlan/> <dbPlan/> <objective>basic</objective> <status>active</status> </iormplan> </cli-output> SQL>
One alternative way to do so is to query the cell directly, while logged in:
[root@someCell ~]# cellcli -e list iormplan detail name: someCell_IORMPLAN catPlan: dbPlan: objective: basic status: active [root@someCell ~]#
Except that cells are most often not accessible to DBAs.
Just looking at the XML query output is good enough for small chunks of XML, however when investigating a larger XML document this quickly becomes impractical.
Back to the motivation for writing this post: a very useful view I wanted to query for information about statistics on columnar cache is named v$cell_state. I have used the Smart-Scan Deep Dive blog as a source. It features an example on how to extract a specific statistic from the XML data. I have extended the example to translate all statistics pertaining to the columnar cache into the more familiar format.
The XML in question is rather deeply nested. I have shortened the other output to give you an idea of the data I care about:
<cell_stats> <stats type="scheduler">...</stats> <stats type="network_directory">...</stats> <stats type="cache">...</stats> <stats type="MemCache">...</stats> <stats type="flashcache"> <stats type="columnarcache"> <stat name="columnar_cache_size">26673152</stat> <stat name="columnar_cache_keep_size">0</stat> <stat name="columnar_cache_keep_read">0</stat> ...
I need all the information in the ‘<stats type=”columnarcache”>’ part, lines 8 and following. The most useful way to translate XML to what looks like a relational table I found is based on a rather complex function, named XMLTABLE(). It is explained in the XML DB Developer’s Guide, chapter 4 XQuery and Oracle XML DB. Using that function I can translate XML into a relational format.
It is probably easiest to show you the query and then walk you through. According to Oracle’s blog post I can find the information about the columnar cache in v$cell_state. I don’t have to worry about the different statistics_type values because the filtering of output will be done on the XML using what is called an XPATH expression. I would like to check a single cell only, to demonstrate that it is possible to combine the output of XMLTABLE with other views/tables and query them using “regular” SQL:
SQL> select b.* from v$cell_state a, xmltable ( '/cell_stats/stats/stats[@type="columnarcache"]/stat' passing xmltype(a.statistics_value) columns name varchar2(50) path '@name', value number path '.' ) b where a.cell_name = '192.168.10.9' and b.value <> 0 order by b.value;
Unsurprisingly I need to pass the XML data to the XMLTABLE in order to process it further. The XML data is found in v$cell_state.statistics_value. I need to access the view and pass the relevant column to the XMLTABLE function’s “passing” clause. An XMLTYPE is expected here, which is why I have to convert the LOB.
The XMLTABLE function should only process data that belongs to the nodes below ‘<stats type=”columnarcache”>’. An XPATH expression helps me defining the scope. This is actually where I spent most of the time because writing correct XMLPATHs doesn’t come naturally to me. Have a look at Wikipedia if XPATH is completely alien to you.
Getting the XPATH right can be challenging if you don’t do it every day. I learned from Marco Gralike who did something very similar to my investigation that it is possible to return the pruned XML tree structure as an XMLTYPE. I wasn’t quite sure for example which part of the XML document I wanted to use for the XMLTABLE’s columns clause. I went through a couple of iterations to find “my” subset of data. Here is one way to do this according to Marco’s blog post:
SQL> select b.cc from v$cell_state a, xmltable ( '/cell_stats/stats/stats[@type="columnarcache"]' passing xmltype(a.statistics_value) columns cc XMLTYPE PATH '*' ) b where a.cell_name = '192.168.10.9';
You will get XML back (the column returned by XMLTABLE is cast to XMLTYPE in line 6), and then use that as the basis for further analysis.
Once the XML returned contains all the data you need, you can continue with writing the XMLTABLE’s columns clause to extract information from the XML document. That is where I ran into the next problem: I had syntax issues trying to reference the “self” node (which is a dot as you see in the full example in line 8) which is why I experimented a bit. It turned out I had to specify the column name, data type, and path before referencing the element.
The tricky bit working with the XML data is finding the correct XPATH, at least for me, because I am not an XML developer. I found it useful to start in the row containing the information I want and then work my way up the tree until I reach the root element. Playing around a bit more I managed to shorten my XPATH to just this one:
I still prefer the “complete” path expression because it gives me a clue where in the DOM model that information is stored.
If you want the complete picture for your entire Exadata cluster, simply add the cell name to the query:
SQL> select a.cell_name,b.name, b.value from v$cell_state a, xmltable ( '/cell_stats/stats/stats[@type="columnarcache"]/stat' passing xmltype(a.statistics_value) columns name varchar2(50) path '@name', value number path '.' ) b where b.value <> 0 order by a.cell_name,b.value;
Please have a look at Marco’s post where he explains a more complex scenario of nesting XMLTABLE functions, depending on what information you are after.
Hope this helps!