xmldb

Undocumented stuff is always very interesting, although there is of course a reason why it is undocumented… Event settings are more or less in this category. They are often used by Oracle Support in their quest to do a good job solving our problems.

Sometimes events disable or enable features. Sometimes they trace internal processes. The later can be very helpful like setting the infamous “10046” event. I noticed some event settings that can be helpful while using XMLDB or the Protocol Server.

Here a small overview of some events that are in the manuals or mentioned during discussions on the OTN XMLDB Forum… Be careful while “playing” with these events. Some are mentioned in the XMLDB Developers Guide and not an big issue, others can cause very strange results or abnormal behavior.

So be advised…

Debugging XML Schema Registration

ORA-31098: Internal event to turn on XDB tracing

When you store your data via the object relational method, using DBMS_XMLSCHEMA.RegisterSchema, you can use the following event to debug or see which statements are used to create the tables and object types. You should call this event before you execute DBMS_XMLSCHEMA.

alter session set events = '31098 trace name context forever'

The trace file with the statements is created in the udump directory. The udump directory is set via the parameter user_dump_dest

Tracing The Protocol Server

One of the more useful ones is tracing the protocol server.

alter system set event = '31098 trace name context forever, level 2' scope=spfile

Trace files will be written to the udump directory. Look out for shared server trace files like the following: TEST_s000_17016.trc. Depending on the amount of shared servers you have defined, you will encounter more then one trace file.

Be ware that this is causing a lot of extra overhead, so switch it off (remove the event setting from the database parameter file / spfile) if you don’t use it any more…

Query Rewrite with XML operators

ORA-19027: Event for suppressing query rewrite with XML operators

I guess I don’t have to say that you should use this only during test cases or if Oracle support asks you to do this (for instance, avoiding problems at your site). 

The following will trace the reasons why an XML function is not re-written. 

Event 19021 with level 4096 (0x1000) to get the optimized rewritten query in the trace file to see what underlying queries are executed on the underlying internal tables created for XML storage and index models.

alter session set events = '19027 trace name context forever, level 0x1000'

Query Rewrite with XML operators in Views

You can disable XPath rewrite for views constructed using a SQL/XML function by using the following event flag:

alter session set events = '19027 trace name context forever, level 64';

You can disable XPath rewrite for view constructed using object types, object views, and SQL function sys_XMLGen by using the following event flag:

alter session set events = '19027 trace name context forever, level 1';

Dumping trace data regarding Query Rewrite issues

Event 19027 with level 8192 (0x2000) to get a dump in the trace file indicating why a particular expression is not rewritten.

alter session set events = '19027 trace name context forever, level 0x2000'

You can stop re-write operations while using XML operators while setting the following event. Also read up on it here: OTN XMLDB Forum .

alter session set events ='19021 trace name context forever, level 0x800'

You may set event 19021 with level 1 for a given database session using SQL statement ALTER SESSION to determine if your XML operation was rewritten. Turn on event 19021 with level 1 if you want to raise an error whenever any of the XML functions is not rewritten and is instead evaluated functionally. The error “ORA-19022 – XML XPath functions are disabled” is raised when such functions execute.

XMLQuery and XMLTable function

ORA-19119: Event for turning on XMLQuery and XMLTable function

Fixing encoding issues by altering behavior of XMLTABLE and XMLQuery (event 19119). Also see the following OTN Thread regarding encoding issues. In this case, avoiding bug/workaround for XMLAGG (bug 8246403).

— — Replace with ?

alter session set events = '19119 trace name context forever, level 0x100000'

— Replace with Character Reference

alter session set events = '19119 trace name context forever, level 0x200000'

— Remove Bad Characters

alter session set events = '19119 trace name context forever, level 0x400000'

Disable DTD or XML Schema Validation

To prevent DTD validation set the following event.

alter session set events ='31156 trace name context forever, level 2'

Also see OTN XMLDB Forum post “Converting to xmltype, error ORA-31011” for more information regarding this alter session set event type.

Disabling checks on XML well-formedness or XML Schema validation can be disabled via the XMLTYPE data type itself. See the manual link here.

Disabling well-formedness and XML Schema validation is not supported by the XMLDB team, it is at your own risk. Although it can be very useful avoiding the “ORA-31167: 64k size limit for XML node” error, as is discussed here on the XMLDB OTN Forum.

Disabling validation for XML Schema based Binary XML

Disabling validation for XML Schema based Binary XML storage can be done via

SQL> alter session 2 set events ='31150 trace name context forever, level 0x4000' 3 /

Also see the OTN Forum for more information here: OTN link on the how and when to do this.

Enabling VARRAYs columns to be created as OCTs

The 22830 event enables all VARRAY columns to be created as Ordered Collection in Tables (ie with table storage) and is probably used as the mechanism behind the xdb:annotation “xdb:storeVarrayAsTable= TRUE | FALSE” mechanism.

In the xmldb install ($ORACLE_HOME/rdbms/admin/catqm.sql) script, it is used for turning off the REF cascade semantics for resource$

alter session set events '22830 trace name context forever, level 4'; alter session set events '22830 trace name context off';

Creating histogram statistics on a PATH TABLE

As described in the Oracle whitepaper “Oracle XML DB: Best Practices to get optimal performance out of XML Queries”, its sometimes useful to create histogram statistics on a Unstructured XMLIndex PATH TABLE. 

This can be achieved via setting the following event:

-- Base table SQL> call dbms_stats.gather_table_stats ('XMARK', 'SITE_TAB', null, null); -- Path table SQL> alter session set events '31150 trace name context forever, level 0x8'; SQL> call dbms_stats.gather_table_stats('XMARK', 'AUCTION_PATH_TABLE', estimate_percent=>100, method_opt=>'for all columns size 254') /

Force XQuery Parser behavior

Via the following event you can force the “old” XQuery 11.2.0.1.0 XML Parser behavior in newer Oracle 11.2.0.x database versions. Also see the OTN XMLDB Forum post here: “Receiving OCI/ORA-27163 when querying XML data in 11g.” regarding this event.

This can be achieved via setting the following event:

SQL> alter session set events= '31156 trace name context forever, level 0x400';

If you don’t use it; Switch it off…

If you don’t use it, switch if off; If not for security reasons.

One off those parameters I want to point out here is the recyclebin feature. If you don’t use flashback or anything that really is needed or depending on the recyclebin, then disable it.

Switching off the recyclebin can help you with increasing performance while working with copy evolve using DBMS_XMLSCHEMA.copyEvolve. 

ALTER SESSION SET RECYCLEBIN = OFF;

There is another one on my list I am thinking of to put it in the category “best practice”: the parameter XML_DB_EVENTS… I am a little bit careful with that one, but I guess that with the current version (11.1.0.6.0) this is an option do disable, if you don’t use it.

ALTER SYSTEM SET XML_DB_EVENTS = DISABLE;

Another one I encountered very often during checks on customer databases, is parameter DISPATCHERS. This functionality is very often enabled without any need for the XMLDB Protocol Server functionality or Shared Server functionality. This parameter will cause a lot off CPU time consumption. Wasted CPU time.

In Oracle 10gR1 Protocol Server functionality was enabled by default and a possible security issue. In Oracle 10gR2 is was disabled by default. Oracle 10g up to 11gR1, a default DISPATCHER is defined in standard default Oracle environments. If you don’t use it; Switch it off…

XML SQL Hints

In that context also using hints can make sense. 

Hint used in a statement: 

/*+ NO_XML_QUERY_REWRITE */

can be used, especially in PL/SQL to avoid a Query re-write when it doesn’t make sense, that is, if there is no XMLType storage in place, and as such can’t be optimized anyway. That is, the query will be resolved by the XML Virtual Machine (XVM).

Some internal hints, I found, used by the CBO…

NO_COST_XML_QUERY_REWRITE XMLINDEX_REWRITE_IN_SELECT XMLINDEX_REWRITE XML_DML_RWT_STMT FORCE_XML_QUERY_REWRITE

Regarding 

/*+ NO_COST_XML_QUERY_REWRITE */

The documentation states regarding “no cost” / “cost” and “rule” based optimization:

Rule-Based and Cost-Based XQuery Optimization

Several competing optimization possibilities can exist for queries with XQuery expressions, depending on various factors such as the XMLType storage model and indexing that are used.

By default, Oracle XML DB follows a prioritized set of rules to determine which of the possible optimizations should be used for any given query and context. This behavior is referred to as rule-based XML query rewrite.

Alternatively, Oracle XML DB can use cost-based XML query rewrite. In this mode, Oracle XML DB estimates the performance of the various XML optimization possibilities for a given query and chooses the combination that is expected to be most performant.

You can impose cost-based optimization for a given SQL statement by using the optimizer hint /*+ COST_XML_QUERY_REWRITE */.

Instead of using SQL hints, you can also use XQuery #prama hints to be a bit more selective and/or influence behavior:

Oracle XMLDB Pragma – Cheat sheet

Only Test…

Don’t use it in production. Don’t use it in production. Don’t use it in production (if not really needed).

Do I have to repeat it?

If you have too, than these event settings should have been advised by Oracle Support. If you break something don’t start calling me. These events are great for getting a better understanding of the software you are working with, so use them on a test environment. Over the years I have corrupted some test databases (my own), which were truly unrecoverable (ever tried to update sys.seq$ or sys.props$?).