Category: Performance

So you’re on 11.2.0.2.0 and you encountered in SQL*Plus this new feature “Unoptimized XML construct detected (enable XMLOptimizationCheck for more information)“. What can you do and how to get more info…?

I encountered this new feature in SQL*Plus a month or so ago via the executing the following:

SQL> set autotrace on

SQL> SELECT xt.nam, xt2.color
  2  FROM XMLTable(XMLNamespaces(DEFAULT 'abc.com/123',
  3                              'xyz.net/456' AS "b"),
  4                'employees/emp'
  5                PASSING XMLTYPE('
  6   
  7    Scott
  8    
  9     red
 10      orange
 11     
 12    
 13    
 14     John
 15     
 16      blue
 17      green
 18     
 19    
 20   ')
 21                 COLUMNS
 22                 nam      VARCHAR2(20) PATH 'name',
 23                 color_t  XMLTYPE      PATH 'b:favorites') xt,  
 24        XMLTable(XMLNamespaces('xyz.net/456' AS "b"),
 25                 'b:favorites/b:color'
 26                 PASSING xt.color_t  
 27                 COLUMNS
 28                 color  VARCHAR2(10) PATH '.') xt2;

NAM                  COLOR
-------------------- ----------
Scott                red
Scott                orange
John                 blue
John                 green


Execution Plan
----------------------------------------------------------
Plan hash value: 1368717035

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                        |    66M|   254M|   221K  (1)| 00:44:21 |
|   1 |  NESTED LOOPS                      |                        |    66M|   254M|   221K  (1)| 00:44:21 |
|   2 |   COLLECTION ITERATOR PICKLER FETCH| XMLSEQUENCEFROMXMLTYPE |  8168 | 16336 |    29   (0)| 00:00:01 |
|   3 |   COLLECTION ITERATOR PICKLER FETCH| XMLSEQUENCEFROMXMLTYPE |  8168 | 16336 |    27   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------

Note
-----
   - Unoptimized XML construct detected (enable XMLOptimizationCheck for more information)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        557  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          4  rows processed

By noticing the “COLLECTION ITERATOR PICKLER FETCH” this already dawned with me. In principle a “COLLECTION ITERATOR PICKLER FETCH” means that the XML document or (intermediate) fragment is handled in memory and should be avoided because it is “serialized” and dealt via a Pickler Fetch routine, which in most cases is done via a standard XML parser, which can not be optimized by Oracle, for example, because Oracle doesn’t have enough information (provided maybe via an XML Schema) to re-write this query in a more optimal form. See this website for more information on collection iterator pickler fetches.

XMLOptimizationCheck

The “Unoptimized XML construct detected (enable XMLOptimizationCheck for more information)” is new in SQL*Plus / database version 11.2.0.2.0 and is the equivalent of setting a 19021 level 0x1 via for example: ALTER session SET events =’19021 trace name context forever, level 0x1′.

By setting the “XMLOptimizationCheck” setting in SQL*Plus, Oracle/the database will refuse to execute this unoptimized code.

BE AWARE:
Playing with internal Oracle support database events should only be done when advised by Oracle support, or on a test system were it is not a big deal when this gets corrupted! My advice from me to you, but don’t start whining if it break your environment…You can’t say I didn’t warn you.

According to an entry in the XMLDB Developers manual this is only used in a “test” or “debug” situation.

When this mode is on, the plan of execution is automatically checked for XQuery optimization, and if the plan is suboptimal then an error is raised and diagnostic information is written to the trace file indicating which operators are not rewritten.

And in the SQL*Plus manual for the latest release, under new features, 11.2.0.2, it states:

SET XMLOPTIMIZATIONCHECK
SET XMLOPTIMIZATIONCHECK specifies that only fully optimized XML queries and DML operations are executed. It is only to assist during code development and debugging.