Execution Plans, XML Documents, and Exadata

Exadata is 100% compatible with Oracle Database, period. But how much benefit do Exadata features lend to particular data types?

The Original Request

We needed to know how much dynamic sampling was being used in recent queries, if at all. I knew that previous execution plans were saved in DBA_HIST_SQL_PLAN, and I also knew that the level of dynamic sampling chosen for a query was saved in the “Notes” section of the saved execution plan — in the column OTHER_XML.

Name              Null?    Type           
----------------- -------- -------------- 
DBID              NOT NULL NUMBER         
SQL_ID            NOT NULL VARCHAR2(13)   
PLAN_HASH_VALUE   NOT NULL NUMBER         
ID                NOT NULL NUMBER         
OPERATION                  VARCHAR2(30)   
. . .
TIMESTAMP                  DATE           
OTHER_XML                  CLOB           
CON_DBID                   NUMBER         
CON_ID                     NUMBER

But Shouldn’t an XML Document Be An XMLTYPE Column?

The column OTHER_XML is a CLOB, but its contents look like a well-formed XML document, so I should be able to use XMLTABLE to query it.

<other_xml>
   <info type="derived_cpu_dop" id="1">3</info>
   <info type="derived_io_dop" id="1">3</info>
   <info type="dop_op_reason" note="y">scan of object CLARITY.CLM_VALUES</info>
. . .
   <info type="dynamic_sampling" note="y">5</info>
   < / OTHER_XML >

The query was straightforward — I’ll use XMLTABLE to embed an XML query function and pass the CLOB column OTHER_XML converted to XMLTYPE:

select /*+ monitor */ to_number(samp_lvl) samp_level, 
         count(to_number(samp_lvl))
from
(select /*+ parallel(8) */ p.sql_id, t.attr, t.samp_lvl
 from   dba_hist_sql_plan p,
        xmltable('for $i in /other_xml/info
                     where $i/@type eq "dynamic_sampling"
                     return $i'
                 passing xmltype(p.other_xml)
                 columns attr      varchar2(50) path '@type',
                         samp_lvl  varchar2(50) path '/') t
where  p.other_xml is not null)
group by to_number(samp_lvl)
order by to_number(samp_lvl);

The results looked like what I was expecting: the queries that needed dynamic sampling used level 4 most of the time:

But What About Exadata?

The query results were the answer to the original question, but I wanted to see what happens with Exadata — this was for a relatively small number of rows — what if I had millions or billions of XML documents that needed to be searched on Exadata? Would I get any offloading features to make my query any faster?

The query plan was the same whether I ran it on Exadata or on a generic Oracle Database platform. In this case, the plan was complicated with an extended data link since it was in a PDB:

From this point on, I created a local copy of DBA_HIST_SQL_PLAN to avoid the complications of internal database links and the data dictionary. The first thing I tried was to create the copy of DBA_HIST_SQL_PLAN with an XMLTYPE data type instead of CLOB:

create table dba_hist_sql_plan_xmltype
(sql_id     varchar2(13),
 other_xml  xmltype);

This did not change the query plan much; some kind of filtering was happening at the storage layer, but the “XPATH EVALUATION” was still happening at the compute layer:

Don’t Forget V$SQLFN_METADATA

I almost forgot to check V$SQLFN_METADATA, and it gave me some of the answers I was looking for:

As expected, none of the XML functions I’ve used so far (and the ones that I was thinking of using!) were offloadable. But I was reminded that the REGEXP_* functions were offloadable — what if I kept the OTHER_XML column as a CLOB and did my own regular expression parsing with REGEXP_INSTR? The query looks like this:

select /*+ monitor parallel(4) */
   to_char(regexp_substr(other_xml,
           'info type="dynamic_sampling" note="y">\d{1,2}</info>',1)),count(*)
from dba_hist_sql_plan_copy
where other_xml is not null
  and length(regexp_substr(other_xml,'info type="dynamic_sampling" note="y">\d{1,2}</info>',1)) > 0
group by to_char(regexp_substr(other_xml,'info type="dynamic_sampling" note="y">\d{1,2}</info>',1))
order by 1;

The result gives me close to the output format I want:

The details of the additional parsing needed to extract the numeric portion of the XML fragment are left to the reader, but the end result is that the execution plan and offloading are about the same! And I’m going to hit the VARCHAR2 limit at some point with REGEXP_SUBSTR, so using the XML functionality will give me a lot more flexibility when the XML document is stored as an XMLTYPE, and queries will still skip some of the I/O at the Exadata storage layer because of the WHERE clause. Just because an Oracle built-in function like REGEXP_INSTR is offloadable doesn’t mean you always will get a significant benefit by using that function on Exadata. The moral of the story is the same as always: test, test, and test again!