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.