Search

Top 60 Oracle Blogs

Recent comments

11gR1, 11gR2

Oracle XMLDB XQuery Update in Database Release 11.2.0.3.0

I just made use of the very cool OTN Virtual Developer Day Database site. In this environment you can follow OTN Developer Day sessions, for example, at home, while making use of all the material available on that site plus the downloadable Virtualbox OTN Developer Day Appliance. Despite you can choose for tracks like Java, .Net, APEX, there is also a database section which handles (as you might expect it from me regarding interest) Oracle XMLDB functionality.

Updated XML Content & Paper section

Once per year I try to update the “XML Content” page that, in principle, should contain all my XML relevant thoughts and “how to” posts, so I can find my gained “wisdom” a bit more quickly (getting old and stuff). This year I had only 14 and a bit of such XML related posts, which was afterwards easily explained when I updated my “Paper” page where among others I have a list on “presentations” done… Oops… I have been busy…

Anyway. Enjoy the updated XML content overview reference page, it contains now 100+ posts regarding specific XML(DB) related howto’s, solutions, approaches, ideas, etc.

 

Oracle XBRL Extension Ready for Download

The new ready to download Oracle XBRL Extension is a “no cost option” on top of the latest Oracle Database 11.2.0.2.0 release. With this added functionality you will get a database environment that contains

  • One or more back-end XBRL repositories based on Oracle Database, which provide XBRL storage and query-ability with a set of XBRL-specific services
  • An external XBRL processing engine (XPE)

The XBRL Extension to Oracle XML DB integrates easily with Oracle Business Intelligence Suite Enterprise Edition (OBIEE) for analytics and with interactive development environments (IDEs) and design tools for creating and editing XBRL taxonomies.

Architecture of XBRL Extension to Oracle XML DB

Oracle XBRL Extension

There is a generic “patch” (patch 10411201), p10411201_112020_generic.zip, which can be downloaded from http://support.oracle.com and installed to be used on top of the Oracle 11.2.0.2.0 XML DB. In addition, there are also two platform-specific patches (currently only available for Linux-x86-64 and Solaris-64 platforms):

For the Linux-x86 64 bit platform:

  • p10074437_112020_Linux-x86-64.zip
  • p10232225_112020_Linux-x86-64.zip

For the Solaris 64 bit platform:

  • p10074437_112020_SOLARIS64.zip
  • p10232225_112020_SOLARIS64.zip

Besides a installation readme, also a HTML/PDF document is available in these patches that have extensive info on how to use this XML DB XBRL extension and a demo based on the US GAAP XBRL taxonomy. On the Oracle XBRL Extension main page, you also can see the power of this architecture combined with web enabled application, the standard tools like Microsoft Excel, Word and analyses done on such an taxonomy database architecture via Oracle BI Server.

For more information see the Oracle XBRL Extension main page or the Oracle XML DB discussion forum.

M.

HOWTO: Trace “ORA-19022: Unoptimized XML construct”

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#66cc66;">> #993333; font-weight: bold;">SET autotrace #993333; font-weight: bold;">ON
 
SQL#66cc66;">> #993333; font-weight: bold;">SELECT xt#66cc66;">.nam#66cc66;">, xt2#66cc66;">.color
  #cc66cc;">2  #993333; font-weight: bold;">FROM XMLTable#66cc66;">(XMLNamespaces#66cc66;">(#993333; font-weight: bold;">DEFAULT #ff0000;">'abc.com/123'#66cc66;">,
  #cc66cc;">3                              #ff0000;">'xyz.net/456' #993333; font-weight: bold;">AS #ff0000;">"b"#66cc66;">)#66cc66;">,
  #cc66cc;">4                #ff0000;">'employees/emp'
  #cc66cc;">5                PASSING XMLTYPE#66cc66;">(#ff0000;">'
  6   
  7    Scott
  8    
  9     red
 10      orange
 11     
 12    
 13    
 14     John
 15     
 16      blue
 17      green
 18     
 19    
 20   '#66cc66;">)
 #cc66cc;">21                 #993333; font-weight: bold;">COLUMNS
 #cc66cc;">22                 nam      VARCHAR2#66cc66;">(#cc66cc;">20#66cc66;">) PATH #ff0000;">'name'#66cc66;">,
 #cc66cc;">23                 color_t  XMLTYPE      PATH #ff0000;">'b:favorites'#66cc66;">) xt#66cc66;">,  
 #cc66cc;">24        XMLTable#66cc66;">(XMLNamespaces#66cc66;">(#ff0000;">'xyz.net/456' #993333; font-weight: bold;">AS #ff0000;">"b"#66cc66;">)#66cc66;">,
 #cc66cc;">25                 #ff0000;">'b:favorites/b:color'
 #cc66cc;">26                 PASSING xt#66cc66;">.color_t  
 #cc66cc;">27                 #993333; font-weight: bold;">COLUMNS
 #cc66cc;">28                 color  VARCHAR2#66cc66;">(#cc66cc;">10#66cc66;">) PATH #ff0000;">'.'#66cc66;">) xt2;
 
NAM                  COLOR
#808080; font-style: italic;">-------------------- ----------
Scott                red
Scott                orange
John                 blue
John                 green
 
 
Execution Plan
#808080; font-style: italic;">----------------------------------------------------------
Plan hash #993333; font-weight: bold;">VALUE: #cc66cc;">1368717035
 
#808080; font-style: italic;">-------------------------------------------------------------------------------------------------------------
#66cc66;">| Id  #66cc66;">| Operation                          #66cc66;">| Name                   #66cc66;">| #993333; font-weight: bold;">ROWS  #66cc66;">| Bytes #66cc66;">| Cost #66cc66;">(%CPU#66cc66;">)#66cc66;">| #993333; font-weight: bold;">TIME     #66cc66;">|
#808080; font-style: italic;">-------------------------------------------------------------------------------------------------------------
#66cc66;">|   #cc66cc;">0 #66cc66;">| #993333; font-weight: bold;">SELECT STATEMENT                   #66cc66;">|                        #66cc66;">|    66M#66cc66;">|   254M#66cc66;">|   221K  #66cc66;">(#cc66cc;">1#66cc66;">)#66cc66;">| 00:#cc66cc;">44:#cc66cc;">21 #66cc66;">|
#66cc66;">|   #cc66cc;">1 #66cc66;">|  NESTED LOOPS                      #66cc66;">|                        #66cc66;">|    66M#66cc66;">|   254M#66cc66;">|   221K  #66cc66;">(#cc66cc;">1#66cc66;">)#66cc66;">| 00:#cc66cc;">44:#cc66cc;">21 #66cc66;">|
#66cc66;">|   #cc66cc;">2 #66cc66;">|   COLLECTION ITERATOR PICKLER FETCH#66cc66;">| XMLSEQUENCEFROMXMLTYPE #66cc66;">|  #cc66cc;">8168 #66cc66;">| #cc66cc;">16336 #66cc66;">|    #cc66cc;">29   #66cc66;">(#cc66cc;">0#66cc66;">)#66cc66;">| 00:00:01 #66cc66;">|
#66cc66;">|   #cc66cc;">3 #66cc66;">|   COLLECTION ITERATOR PICKLER FETCH#66cc66;">| XMLSEQUENCEFROMXMLTYPE #66cc66;">|  #cc66cc;">8168 #66cc66;">| #cc66cc;">16336 #66cc66;">|    #cc66cc;">27   #66cc66;">(#cc66cc;">0#66cc66;">)#66cc66;">| 00:00:01 #66cc66;">|
#808080; font-style: italic;">-------------------------------------------------------------------------------------------------------------
 
Note
#808080; font-style: italic;">-----
   #66cc66;">- Unoptimized XML construct detected #66cc66;">(enable XMLOptimizationCheck #993333; font-weight: bold;">FOR more information#66cc66;">)
 
 
Statistics
#808080; font-style: italic;">----------------------------------------------------------
          #cc66cc;">0  recursive calls
          #cc66cc;">0  db block gets
          #cc66cc;">0  consistent gets
          #cc66cc;">0  physical reads
          #cc66cc;">0  redo #993333; font-weight: bold;">SIZE
        #cc66cc;">557  bytes sent via #993333; font-weight: bold;">SQL#66cc66;">*Net #993333; font-weight: bold;">TO client
        #cc66cc;">419  bytes received via #993333; font-weight: bold;">SQL#66cc66;">*Net #993333; font-weight: bold;">FROM client
          #cc66cc;">2  #993333; font-weight: bold;">SQL#66cc66;">*Net roundtrips #993333; font-weight: bold;">TO#66cc66;">/#993333; font-weight: bold;">FROM client
          #cc66cc;">0  sorts #66cc66;">(memory#66cc66;">)
          #cc66cc;">0  sorts #66cc66;">(disk#66cc66;">)
          #cc66cc;">4  #993333; font-weight: bold;">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 0×1 via for example: ALTER session SET events =’19021 trace name context forever, level 0×1′.

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.

 
SQL#66cc66;">> #993333; font-weight: bold;">SET XMLOptimizationCheck #993333; font-weight: bold;">ON
 
SQL#66cc66;">> #993333; font-weight: bold;">SELECT xt#66cc66;">.nam#66cc66;">, xt2#66cc66;">.color
  #cc66cc;">2  #993333; font-weight: bold;">FROM XMLTable#66cc66;">(XMLNamespaces#66cc66;">(#993333; font-weight: bold;">DEFAULT #ff0000;">'abc.com/123'#66cc66;">,
  #cc66cc;">3                              #ff0000;">'xyz.net/456' #993333; font-weight: bold;">AS #ff0000;">"b"#66cc66;">)#66cc66;">,
  #cc66cc;">4                #ff0000;">'employees/emp'
  #cc66cc;">5                PASSING XMLTYPE#66cc66;">(#ff0000;">'
  6   
  7    Scott
  8    
  9     red
 10      orange
 11     
 12    
 13    
 14     John
 15     
 16      blue
 17      green
 18     
 19    
 20   '#66cc66;">)
 #cc66cc;">21                 #993333; font-weight: bold;">COLUMNS
 #cc66cc;">22                 nam      VARCHAR2#66cc66;">(#cc66cc;">20#66cc66;">) PATH #ff0000;">'name'#66cc66;">,
 #cc66cc;">23                 color_t  XMLTYPE      PATH #ff0000;">'b:favorites'#66cc66;">) xt#66cc66;">,  #808080; font-style: italic;">-- path to the node that repeats
 #cc66cc;">24        XMLTable#66cc66;">(XMLNamespaces#66cc66;">(#ff0000;">'xyz.net/456' #993333; font-weight: bold;">AS #ff0000;">"b"#66cc66;">)#66cc66;">,
 #cc66cc;">25                 #ff0000;">'b:favorites/b:color'
 #cc66cc;">26                 PASSING xt#66cc66;">.color_t  #808080; font-style: italic;">-- define input XMLType as output of above, aka a join
 #cc66cc;">27                 #993333; font-weight: bold;">COLUMNS
 #cc66cc;">28                 color  VARCHAR2#66cc66;">(#cc66cc;">10#66cc66;">) PATH #ff0000;">'.'#66cc66;">) xt2;
 
#993333; font-weight: bold;">SELECT xt#66cc66;">.nam#66cc66;">, xt2#66cc66;">.color
#66cc66;">*
ERROR at line #cc66cc;">1:
ORA#66cc66;">-#cc66cc;">19022: Unoptimized XML construct detected
#66cc66;">.

In the trace directory, a trace file will be created showing the following, or alike trace file content for your statement. In the example here a trace file was created with the following content

#66cc66;">[oracle@localhost trace#66cc66;">]$ cat orcl_ora_3092#66cc66;">.trc
 
Trace file #66cc66;">/home#66cc66;">/oracle#66cc66;">/app#66cc66;">/oracle#66cc66;">/diag#66cc66;">/rdbms#66cc66;">/orcl#66cc66;">/orcl#66cc66;">/trace#66cc66;">/orcl_ora_3092#66cc66;">.trc
Oracle #993333; font-weight: bold;">DATABASE 11g Enterprise Edition Release 11#66cc66;">.2#66cc66;">.0#66cc66;">.2#66cc66;">.0 #66cc66;">- Production
#993333; font-weight: bold;">WITH the Partitioning#66cc66;">, OLAP#66cc66;">, #993333; font-weight: bold;">DATA Mining #993333; font-weight: bold;">AND #993333; font-weight: bold;">REAL Application Testing options
ORACLE_HOME #66cc66;">= #66cc66;">/home#66cc66;">/oracle#66cc66;">/app#66cc66;">/oracle#66cc66;">/product#66cc66;">/11#66cc66;">.2#66cc66;">.0#66cc66;">/dbhome_2
System name:    Linux
Node name:      localhost#66cc66;">.localdomain
Release:        2#66cc66;">.6#66cc66;">.18#66cc66;">-194#66cc66;">.0#66cc66;">.0#66cc66;">.0#66cc66;">.4#66cc66;">.el5
Version:        ##cc66cc;">1 SMP Thu Apr #cc66cc;">8 #cc66cc;">18:#cc66cc;">20:#cc66cc;">19 EDT #cc66cc;">2010
Machine:        i686
Instance name: orcl
Redo thread mounted #993333; font-weight: bold;">BY this instance: #cc66cc;">1
Oracle process #993333; font-weight: bold;">NUMBER: #cc66cc;">19
Unix process pid: #cc66cc;">3092#66cc66;">, image: oracle@localhost#66cc66;">.localdomain #66cc66;">(TNS V1#66cc66;">-V3#66cc66;">)
 
 
#66cc66;">*** #cc66cc;">2010#66cc66;">-#cc66cc;">10#66cc66;">-04 #cc66cc;">14:#cc66cc;">51:#cc66cc;">46.775
#66cc66;">*** #993333; font-weight: bold;">SESSION ID:#66cc66;">(#cc66cc;">1.7#66cc66;">) #cc66cc;">2010#66cc66;">-#cc66cc;">10#66cc66;">-04 #cc66cc;">14:#cc66cc;">51:#cc66cc;">46.775
#66cc66;">*** CLIENT ID:#66cc66;">(#66cc66;">) #cc66cc;">2010#66cc66;">-#cc66cc;">10#66cc66;">-04 #cc66cc;">14:#cc66cc;">51:#cc66cc;">46.775
#66cc66;">*** SERVICE NAME:#66cc66;">(SYS$USERS#66cc66;">) #cc66cc;">2010#66cc66;">-#cc66cc;">10#66cc66;">-04 #cc66cc;">14:#cc66cc;">51:#cc66cc;">46.775
#66cc66;">*** MODULE NAME:#66cc66;">(#993333; font-weight: bold;">SQL#66cc66;">*Plus#66cc66;">) #cc66cc;">2010#66cc66;">-#cc66cc;">10#66cc66;">-04 #cc66cc;">14:#cc66cc;">51:#cc66cc;">46.775
#66cc66;">*** ACTION NAME:#66cc66;">(#66cc66;">) #cc66cc;">2010#66cc66;">-#cc66cc;">10#66cc66;">-04 #cc66cc;">14:#cc66cc;">51:#cc66cc;">46.775
 
#66cc66;">===============================================================================
XML Performance Diagnosis:
Unparsed Query:
#66cc66;">******* UNPARSED QUERY #993333; font-weight: bold;">IS #66cc66;">*******
#993333; font-weight: bold;">SELECT #993333; font-weight: bold;">CAST#66cc66;">(SYS_XQ_UPKXML2SQL#66cc66;">(SYS_XQEXVAL#66cc66;">(SYS_XQEXTRACT#66cc66;">(#993333; font-weight: bold;">VALUE#66cc66;">(KOKBF$#66cc66;">)#66cc66;">,
#ff0000;">'/emp/name'#66cc66;">,#ff0000;">'xmlns="abc.com/123" xmlns:b="xyz.net/456" '#66cc66;">)#66cc66;">,#cc66cc;">0#66cc66;">,#cc66cc;">0#66cc66;">,#cc66cc;">20971520#66cc66;">,#cc66cc;">0#66cc66;">)#66cc66;">,#cc66cc;">50#66cc66;">,#cc66cc;">1#66cc66;">,#cc66cc;">2#66cc66;">) 
#993333; font-weight: bold;">AS VARCHAR2#66cc66;">(#cc66cc;">20#66cc66;">) #66cc66;">) #ff0000;">"NAM"#66cc66;">,#993333; font-weight: bold;">CAST#66cc66;">(SYS_XQ_UPKXML2SQL#66cc66;">(SYS_XQEXVAL#66cc66;">(#993333; font-weight: bold;">VALUE#66cc66;">(KOKBF$#66cc66;">)#66cc66;">,#cc66cc;">0#66cc66;">,#cc66cc;">0#66cc66;">,
#cc66cc;">20971520#66cc66;">,#cc66cc;">0#66cc66;">)#66cc66;">,#cc66cc;">50#66cc66;">,#cc66cc;">1#66cc66;">,#cc66cc;">2#66cc66;">) #993333; font-weight: bold;">AS VARCHAR2#66cc66;">(#cc66cc;">10#66cc66;">) #66cc66;">) #ff0000;">"COLOR" 
#993333; font-weight: bold;">FROM #993333; font-weight: bold;">TABLE#66cc66;">(#ff0000;">"SYS"#66cc66;">.#ff0000;">"XQSEQUENCE"#66cc66;">(#993333; font-weight: bold;">EXTRACT#66cc66;">(#ff0000;">"SYS"#66cc66;">.#ff0000;">"XMLTYPE"#66cc66;">(
#ff0000;">'
 
  Scott
  
   red
    orange
   
  
  
   John
   
    blue
    green
   
  
 '#66cc66;">)#66cc66;">,#ff0000;">'/oraxq_defpfx:employees/oraxq_defpfx:emp'#66cc66;">,
 #ff0000;">' xmlns:oraxq_defpfx="abc.com/123"'#66cc66;">)#66cc66;">)#66cc66;">) #ff0000;">"KOKBF$"#66cc66;">,#993333; font-weight: bold;">TABLE#66cc66;">(#ff0000;">"SYS"#66cc66;">.#ff0000;">"XQSEQUENCE"#66cc66;">(#993333; font-weight: bold;">EXTRACT
 #66cc66;">(#993333; font-weight: bold;">EXTRACT#66cc66;">(#993333; font-weight: bold;">VALUE#66cc66;">(KOKBF$#66cc66;">)#66cc66;">,#ff0000;">'/oraxq_defpfx:emp/b:favorites'#66cc66;">,#ff0000;">' xmlns:oraxq_defpfx=
 "abc.com/123" xmlns:b="xyz.net/456"'#66cc66;">)#66cc66;">,#ff0000;">'/b:favorites/b:color'
 #66cc66;">,#ff0000;">' xmlns:b="xyz.net/456"'#66cc66;">)#66cc66;">)#66cc66;">) #ff0000;">"KOKBF$" 
 
Reason: upkxml2sql
#66cc66;">===============================================================================
#66cc66;">.

As shown here its depicts the “upkxml2sql” method as the guilty one. You can now try to avoid this issue by using alternative means and/or provide the database with more information, for example, while using XMLType Binary storage, XML Schema based storage (Object Relational / Binary XML) or register an XML Schema in the XDB Repository. Other alternatives could be avoiding the use of the “//” XPath operator.

The moment the query is parsed and executed / shared in cache via the cursor sharing mechanism, then there is a chance you won’t see a trace file. To avoid cursor sharing you could set the cursor sharing to “exact” and/or add white space to your query or alternate upper/lower case and/or other small tricks to force re-parsing.

Before Oracle 11.2.0.2.0 there is an alternative since, AFAIK at least, Oracle version 11.x. by setting the 19027 event, level 0×2000 to get a bit more insight in what happens under the hood, if Oracle can optimize (or not), the request via query re-write.

19027 trace name context forever, level 0×2000

If you would set the 19027 event, level 0×2000, then a trace file is produced in the trace directory of the base DIAG directory structure.

 
SQL#66cc66;">> #993333; font-weight: bold;">ALTER #993333; font-weight: bold;">SESSION #993333; font-weight: bold;">SET events #66cc66;">= #ff0000;">'19027 trace name context forever, level 0x2000';
 
#993333; font-weight: bold;">SESSION altered#66cc66;">.
 
SQL#66cc66;">> #993333; font-weight: bold;">SELECT xt#66cc66;">.nam#66cc66;">, xt2#66cc66;">.color
  #cc66cc;">2  #993333; font-weight: bold;">FROM XMLTable#66cc66;">(XMLNamespaces#66cc66;">(#993333; font-weight: bold;">DEFAULT #ff0000;">'abc.com/123'#66cc66;">,
  #cc66cc;">3                              #ff0000;">'xyz.net/456' #993333; font-weight: bold;">AS #ff0000;">"b"#66cc66;">)#66cc66;">,
  #cc66cc;">4                #ff0000;">'employees/emp'
  #cc66cc;">5                PASSING XMLTYPE#66cc66;">(#ff0000;">'
  6   
  7    Scott
  8    
  9     red
 10      orange
 11     
 12    
 13    
 14     John
 15     
 16      blue
 17      green
 18     
 19    
 20   '#66cc66;">)
 #cc66cc;">21                 #993333; font-weight: bold;">COLUMNS
 #cc66cc;">22                 nam      VARCHAR2#66cc66;">(#cc66cc;">20#66cc66;">) PATH #ff0000;">'name'#66cc66;">,
 #cc66cc;">23                 color_t  XMLTYPE      PATH #ff0000;">'b:favorites'#66cc66;">) xt#66cc66;">,  #808080; font-style: italic;">-- path to the node that repeats
 #cc66cc;">24        XMLTable#66cc66;">(XMLNamespaces#66cc66;">(#ff0000;">'xyz.net/456' #993333; font-weight: bold;">AS #ff0000;">"b"#66cc66;">)#66cc66;">,
 #cc66cc;">25                 #ff0000;">'b:favorites/b:color'
 #cc66cc;">26                 PASSING xt#66cc66;">.color_t  #808080; font-style: italic;">-- define input XMLType as output of above, aka a join
 #cc66cc;">27                 #993333; font-weight: bold;">COLUMNS
 #cc66cc;">28                 color  VARCHAR2#66cc66;">(#cc66cc;">10#66cc66;">) PATH #ff0000;">'.'#66cc66;">) xt2;
 
NAM                  COLOR
#808080; font-style: italic;">-------------------- ----------
Scott                red
Scott                orange
John                 blue
John                 green
 
SQL#66cc66;">> #993333; font-weight: bold;">ALTER #993333; font-weight: bold;">SESSION #993333; font-weight: bold;">SET events #66cc66;">=#ff0000;">'19027 trace name errorstack off';
 
#993333; font-weight: bold;">SESSION altered#66cc66;">.

The trace file can be found via following the DIAG directory structure, which can be found, for example, via entering “show parameter diag” in SQL*Plus (as user SYS or alternative highly privileged database account).

One of the latest trace files in this directory shows now similar content in that trace file as shown below.

#66cc66;">[oracle@localhost trace#66cc66;">]$ pwd
 
#66cc66;">/home#66cc66;">/oracle#66cc66;">/app#66cc66;">/oracle#66cc66;">/diag#66cc66;">/rdbms#66cc66;">/orcl#66cc66;">/orcl#66cc66;">/trace
 
#66cc66;">[oracle@localhost trace#66cc66;">]$ cat orcl_ora_6167#66cc66;">.trc
Trace file #66cc66;">/home#66cc66;">/oracle#66cc66;">/app#66cc66;">/oracle#66cc66;">/diag#66cc66;">/rdbms#66cc66;">/orcl#66cc66;">/orcl#66cc66;">/trace#66cc66;">/orcl_ora_6167#66cc66;">.trc
Oracle #993333; font-weight: bold;">DATABASE 11g Enterprise Edition Release 11#66cc66;">.2#66cc66;">.0#66cc66;">.2#66cc66;">.0 #66cc66;">- Production
#993333; font-weight: bold;">WITH the Partitioning#66cc66;">, OLAP#66cc66;">, #993333; font-weight: bold;">DATA Mining #993333; font-weight: bold;">AND #993333; font-weight: bold;">REAL Application Testing options
ORACLE_HOME #66cc66;">= #66cc66;">/home#66cc66;">/oracle#66cc66;">/app#66cc66;">/oracle#66cc66;">/product#66cc66;">/11#66cc66;">.2#66cc66;">.0#66cc66;">/dbhome_2
System name:    Linux
Node name:      localhost#66cc66;">.localdomain
Release:        2#66cc66;">.6#66cc66;">.18#66cc66;">-194#66cc66;">.0#66cc66;">.0#66cc66;">.0#66cc66;">.4#66cc66;">.el5
Version:        ##cc66cc;">1 SMP Thu Apr #cc66cc;">8 #cc66cc;">18:#cc66cc;">20:#cc66cc;">19 EDT #cc66cc;">2010
Machine:        i686
Instance name: orcl
Redo thread mounted #993333; font-weight: bold;">BY this instance: #cc66cc;">1
Oracle process #993333; font-weight: bold;">NUMBER: #cc66cc;">24
Unix process pid: #cc66cc;">6167#66cc66;">, image: oracle@localhost#66cc66;">.localdomain #66cc66;">(TNS V1#66cc66;">-V3#66cc66;">)
 
 
#66cc66;">*** #cc66cc;">2010#66cc66;">-#cc66cc;">10#66cc66;">-04 #cc66cc;">11:06:#cc66cc;">22.030
#66cc66;">*** #993333; font-weight: bold;">SESSION ID:#66cc66;">(#cc66cc;">1.47#66cc66;">) #cc66cc;">2010#66cc66;">-#cc66cc;">10#66cc66;">-04 #cc66cc;">11:06:#cc66cc;">22.030
#66cc66;">*** CLIENT ID:#66cc66;">(#66cc66;">) #cc66cc;">2010#66cc66;">-#cc66cc;">10#66cc66;">-04 #cc66cc;">11:06:#cc66cc;">22.030
#66cc66;">*** SERVICE NAME:#66cc66;">(SYS$USERS#66cc66;">) #cc66cc;">2010#66cc66;">-#cc66cc;">10#66cc66;">-04 #cc66cc;">11:06:#cc66cc;">22.030
#66cc66;">*** MODULE NAME:#66cc66;">(#993333; font-weight: bold;">SQL#66cc66;">*Plus#66cc66;">) #cc66cc;">2010#66cc66;">-#cc66cc;">10#66cc66;">-04 #cc66cc;">11:06:#cc66cc;">22.030
#66cc66;">*** ACTION NAME:#66cc66;">(#66cc66;">) #cc66cc;">2010#66cc66;">-#cc66cc;">10#66cc66;">-04 #cc66cc;">11:06:#cc66cc;">22.030
 
        XMLTABLE RWT QUERY
#993333; font-weight: bold;">SELECT #993333; font-weight: bold;">CAST#66cc66;">( sys_xq_upkxml2sql#66cc66;">(sys_xqexval#66cc66;">(sys_xqextract#66cc66;">(#993333; font-weight: bold;">VALUE#66cc66;">(QMXTABF$#66cc66;">)#66cc66;">, #ff0000;">'/emp/name'#66cc66;">,#ff0000;">'xmlns="abc.com/123" xmlns:b="xyz.net/456" '#66cc66;">)#66cc66;">)#66cc66;">, #cc66cc;">50#66cc66;">,#cc66cc;">1#66cc66;">, #cc66cc;">2#66cc66;">) #993333; font-weight: bold;">AS VARCHAR2#66cc66;">(#cc66cc;">20#66cc66;">) #66cc66;">)  #993333; font-weight: bold;">AS #ff0000;">"NAM"#66cc66;">,xmlquery#66cc66;">( #ff0000;">'declare default element namespace "abc.com/123";declare namespace b="xyz.net/456";b:favorites' PASSING #993333; font-weight: bold;">BY XMLTABLE #993333; font-weight: bold;">VALUE#66cc66;">(QMXTABF$#66cc66;">) returning content #66cc66;">) #993333; font-weight: bold;">AS #ff0000;">"COLOR_T" #993333; font-weight: bold;">FROM #993333; font-weight: bold;">TABLE#66cc66;">(xqsequence#66cc66;">( xmlquery#66cc66;">(#ff0000;">'declare default element namespace "abc.com/123";declare namespace b="xyz.net/456";employees/emp' passing #993333; font-weight: bold;">BY #993333; font-weight: bold;">VALUE QMXTABCOL$ returning #993333; font-weight: bold;">SEQUENCE#66cc66;">)  #66cc66;">)#66cc66;">) QMXTABF$
        XMLTABLE RWT QUERY #993333; font-weight: bold;">END
        XMLTABLE RWT QUERY
#993333; font-weight: bold;">SELECT #993333; font-weight: bold;">CAST#66cc66;">( sys_xq_upkxml2sql#66cc66;">(sys_xqexval#66cc66;">(xmlquery#66cc66;">( #ff0000;">'declare namespace b="xyz.net/456";.' PASSING #993333; font-weight: bold;">BY XMLTABLE #993333; font-weight: bold;">VALUE#66cc66;">(QMXTABF$#66cc66;">) returning #993333; font-weight: bold;">SEQUENCE #66cc66;">)#66cc66;">)#66cc66;">, #cc66cc;">50#66cc66;">,#cc66cc;">1#66cc66;">, #cc66cc;">2#66cc66;">) #993333; font-weight: bold;">AS VARCHAR2#66cc66;">(#cc66cc;">10#66cc66;">) #66cc66;">)  #993333; font-weight: bold;">AS #ff0000;">"COLOR" #993333; font-weight: bold;">FROM #993333; font-weight: bold;">TABLE#66cc66;">(xqsequence#66cc66;">( xmlquery#66cc66;">(#ff0000;">'declare namespace b="xyz.net/456";b:favorites/b:color' passing #993333; font-weight: bold;">BY #993333; font-weight: bold;">VALUE QMXTABCOL$ returning #993333; font-weight: bold;">SEQUENCE#66cc66;">)  #66cc66;">)#66cc66;">) QMXTABF$
        XMLTABLE RWT QUERY #993333; font-weight: bold;">END
NO REWRITE
        Reason #66cc66;">==> #993333; font-weight: bold;">NOT SQLX operand
NO REWRITE
        Reason #66cc66;">==> non sqlx expression #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> #993333; font-weight: bold;">NOT SQLX operand
NO REWRITE
        Reason #66cc66;">==> non sqlx expression #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> no #993333; font-weight: bold;">TYPE info
NO REWRITE
        Reason #66cc66;">==> xseq:#993333; font-weight: bold;">NOT lazydef
NO REWRITE
        Reason #66cc66;">==> #993333; font-weight: bold;">NOT SQLX operand
NO REWRITE
        Reason #66cc66;">==> non sqlx expression #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> non rewritable sqlx #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> non rewritable sqlx #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> #993333; font-weight: bold;">NOT SQLX operand
NO REWRITE
        Reason #66cc66;">==> non sqlx expression #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> non rewritable sqlx #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> non rewritable sqlx #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> #993333; font-weight: bold;">NOT SQLX operand
NO REWRITE
        Reason #66cc66;">==> non sqlx expression #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> non rewritable sqlx #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> non rewritable sqlx #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> #993333; font-weight: bold;">NOT SQLX operand
NO REWRITE
        Reason #66cc66;">==> non sqlx expression #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> #993333; font-weight: bold;">NOT SQLX operand
NO REWRITE
        Reason #66cc66;">==> non sqlx expression #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> non rewritable sqlx #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> xseq:#993333; font-weight: bold;">NOT lazydef
NO REWRITE
        Reason #66cc66;">==> #993333; font-weight: bold;">NOT SQLX operand
NO REWRITE
        Reason #66cc66;">==> non sqlx expression #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> non rewritable sqlx #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> non rewritable sqlx #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> #993333; font-weight: bold;">NOT SQLX operand
NO REWRITE
        Reason #66cc66;">==> non sqlx expression #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> non rewritable sqlx #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> #993333; font-weight: bold;">NOT SQLX operand
NO REWRITE
        Reason #66cc66;">==> non sqlx expression #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> non rewritable sqlx #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> xseq:#993333; font-weight: bold;">NOT lazydef
NO REWRITE
        Reason #66cc66;">==> #993333; font-weight: bold;">NOT SQLX operand
NO REWRITE
        Reason #66cc66;">==> non sqlx expression #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> no #993333; font-weight: bold;">TYPE info
NO REWRITE
        Reason #66cc66;">==> xseq:#993333; font-weight: bold;">NOT lazydef
NO REWRITE
        Reason #66cc66;">==> #993333; font-weight: bold;">NOT SQLX operand
NO REWRITE
        Reason #66cc66;">==> non sqlx expression #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> no #993333; font-weight: bold;">TYPE info
NO REWRITE
        Reason #66cc66;">==> no #993333; font-weight: bold;">TYPE info
NO REWRITE
        Reason #66cc66;">==> #993333; font-weight: bold;">NOT SQLX operand
NO REWRITE
        Reason #66cc66;">==> non sqlx expression #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> no #993333; font-weight: bold;">TYPE info
NO REWRITE
        Reason #66cc66;">==> non rewritable sqlx #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> #993333; font-weight: bold;">NOT SQLX operand
NO REWRITE
        Reason #66cc66;">==> non sqlx expression #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> no #993333; font-weight: bold;">TYPE info
NO REWRITE
        Reason #66cc66;">==> non rewritable sqlx #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> #993333; font-weight: bold;">NOT SQLX operand
NO REWRITE
        Reason #66cc66;">==> non sqlx expression #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> no #993333; font-weight: bold;">TYPE info
NO REWRITE
        Reason #66cc66;">==> non rewritable sqlx #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> xseq:#993333; font-weight: bold;">NOT lazydef
NO REWRITE
        Reason #66cc66;">==> xseq:#993333; font-weight: bold;">NOT lazydef
NO REWRITE
        Reason #66cc66;">==> #993333; font-weight: bold;">NOT SQLX operand
NO REWRITE
        Reason #66cc66;">==> non sqlx expression #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> non rewritable sqlx #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> #993333; font-weight: bold;">NOT SQLX operand
NO REWRITE
        Reason #66cc66;">==> non sqlx expression #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> non rewritable sqlx #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> xseq:#993333; font-weight: bold;">NOT lazydef
NO REWRITE
        Reason #66cc66;">==> #993333; font-weight: bold;">NOT SQLX operand
NO REWRITE
        Reason #66cc66;">==> non sqlx expression #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> no #993333; font-weight: bold;">TYPE info
NO REWRITE
        Reason #66cc66;">==> xseq:#993333; font-weight: bold;">NOT lazydef
NO REWRITE
        Reason #66cc66;">==> #993333; font-weight: bold;">NOT SQLX operand
NO REWRITE
        Reason #66cc66;">==> non sqlx expression #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> no #993333; font-weight: bold;">TYPE info
NO REWRITE
        Reason #66cc66;">==> no #993333; font-weight: bold;">TYPE info
NO REWRITE
        Reason #66cc66;">==> #993333; font-weight: bold;">NOT SQLX operand
NO REWRITE
        Reason #66cc66;">==> non sqlx expression #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> no #993333; font-weight: bold;">TYPE info
NO REWRITE
        Reason #66cc66;">==> non rewritable sqlx #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> #993333; font-weight: bold;">NOT SQLX operand
NO REWRITE
        Reason #66cc66;">==> non sqlx expression #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> no #993333; font-weight: bold;">TYPE info
NO REWRITE
        Reason #66cc66;">==> non rewritable sqlx #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> #993333; font-weight: bold;">NOT SQLX operand
NO REWRITE
        Reason #66cc66;">==> non sqlx expression #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> no #993333; font-weight: bold;">TYPE info
NO REWRITE
        Reason #66cc66;">==> non rewritable sqlx #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> xseq:#993333; font-weight: bold;">NOT lazydef
NO REWRITE
        Reason #66cc66;">==> xseq:#993333; font-weight: bold;">NOT lazydef
NO REWRITE
        Reason #66cc66;">==> #993333; font-weight: bold;">NOT SQLX operand
NO REWRITE
        Reason #66cc66;">==> non sqlx expression #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> #993333; font-weight: bold;">NOT SQLX operand
NO REWRITE
        Reason #66cc66;">==> non sqlx expression #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> #993333; font-weight: bold;">NOT SQLX operand
NO REWRITE
        Reason #66cc66;">==> non sqlx expression #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> #993333; font-weight: bold;">NOT SQLX operand
NO REWRITE
        Reason #66cc66;">==> non sqlx expression #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> xseq:#993333; font-weight: bold;">NOT lazydef
NO REWRITE
        Reason #66cc66;">==> xseq:#993333; font-weight: bold;">NOT lazydef

So both XMLTABLE subsections of the used query are attempted by Oracle to rewritten into internal XQuery (“xq”, XQuery, extract, evaluate, xml2sql, xq sequence routines). Looking at the original query…

#993333; font-weight: bold;">SELECT xt#66cc66;">.nam
#66cc66;">,      xt2#66cc66;">.color
#993333; font-weight: bold;">FROM   XMLTable#66cc66;">(XMLNamespaces#66cc66;">(#993333; font-weight: bold;">DEFAULT #ff0000;">'abc.com/123'#66cc66;">,
                              #ff0000;">'xyz.net/456' #993333; font-weight: bold;">AS #ff0000;">"b"#66cc66;">)#66cc66;">,
                #ff0000;">'employees/emp'
                PASSING XMLTYPE#66cc66;">(#ff0000;">'
                                 
                                  Scott
                                  
                                   red
                                    orange
                                   
                                  
                                  
                                   John
                                   
                                    blue
                                    green
                                   
                                  
                                 '#66cc66;">)
                 #993333; font-weight: bold;">COLUMNS
                 nam      VARCHAR2#66cc66;">(#cc66cc;">20#66cc66;">) PATH #ff0000;">'name'#66cc66;">,
                 color_t  XMLTYPE      PATH #ff0000;">'b:favorites'#66cc66;">) xt   
  #66cc66;">,     XMLTable#66cc66;">(XMLNamespaces#66cc66;">(#ff0000;">'xyz.net/456' #993333; font-weight: bold;">AS #ff0000;">"b"#66cc66;">)#66cc66;">,
                 #ff0000;">'b:favorites/b:color'
                 PASSING xt#66cc66;">.color_t  
                 #993333; font-weight: bold;">COLUMNS
                 color  VARCHAR2#66cc66;">(#cc66cc;">10#66cc66;">) PATH #ff0000;">'.'#66cc66;">)             xt2
;

You can see the two XMLTABLE statements…

#993333; font-weight: bold;">SELECT #993333; font-weight: bold;">CAST#66cc66;">( sys_xq_upkxml2sql#66cc66;">(sys_xqexval#66cc66;">(sys_xqextract#66cc66;">(#993333; font-weight: bold;">VALUE#66cc66;">(QMXTABF$#66cc66;">)#66cc66;">, #ff0000;">'/emp/name'
                                                                        #66cc66;">, #ff0000;">'xmlns="abc.com/123" xmlns:b="xyz.net/456" '#66cc66;">)
                                           #66cc66;">)#66cc66;">, #cc66cc;">50#66cc66;">,#cc66cc;">1#66cc66;">, #cc66cc;">2
                               #66cc66;">) #993333; font-weight: bold;">AS VARCHAR2#66cc66;">(#cc66cc;">20#66cc66;">) #66cc66;">)  #993333; font-weight: bold;">AS #ff0000;">"NAM"
#66cc66;">,      xmlquery#66cc66;">( #ff0000;">'declare default element namespace "abc.com/123";
                  declare namespace b="xyz.net/456";
                  b:favorites' 
                  PASSING #993333; font-weight: bold;">BY XMLTABLE #993333; font-weight: bold;">VALUE#66cc66;">(QMXTABF$#66cc66;">) 
                  returning content #66cc66;">) #993333; font-weight: bold;">AS #ff0000;">"COLOR_T" 
                  #993333; font-weight: bold;">FROM #993333; font-weight: bold;">TABLE#66cc66;">(xqsequence#66cc66;">( xmlquery#66cc66;">(#ff0000;">'declare default element namespace "abc.com/123";
                                                   declare namespace b="xyz.net/456";employees/emp' 
                                                   passing #993333; font-weight: bold;">BY #993333; font-weight: bold;">VALUE QMXTABCOL$ 
                                                   returning #993333; font-weight: bold;">SEQUENCE#66cc66;">) 
                                        #66cc66;">)
                            #66cc66;">) QMXTABF$

and…
.

#993333; font-weight: bold;">SELECT #993333; font-weight: bold;">CAST#66cc66;">( sys_xq_upkxml2sql#66cc66;">(sys_xqexval#66cc66;">(xmlquery#66cc66;">( #ff0000;">'declare namespace b="xyz.net/456";.' 
                                                      PASSING #993333; font-weight: bold;">BY XMLTABLE #993333; font-weight: bold;">VALUE#66cc66;">(QMXTABF$#66cc66;">) 
                                                      returning #993333; font-weight: bold;">SEQUENCE #66cc66;">)
                                           #66cc66;">)#66cc66;">, #cc66cc;">50#66cc66;">,#cc66cc;">1#66cc66;">, #cc66cc;">2
                               #66cc66;">) #993333; font-weight: bold;">AS VARCHAR2#66cc66;">(#cc66cc;">10#66cc66;">) #66cc66;">)  #993333; font-weight: bold;">AS #ff0000;">"COLOR" 
#993333; font-weight: bold;">FROM   #993333; font-weight: bold;">TABLE#66cc66;">(xqsequence#66cc66;">( xmlquery#66cc66;">(#ff0000;">'declare namespace b="xyz.net/456";
                                   b:favorites/b:color' 
                                   passing #993333; font-weight: bold;">BY #993333; font-weight: bold;">VALUE QMXTABCOL$ 
                                   returning #993333; font-weight: bold;">SEQUENCE#66cc66;">)  
                        #66cc66;">)
            #66cc66;">) QMXTABF$

Most of this long list in the trace file can be deducted as in a more regular “10053″ trace file / event…

NO REWRITE
        Reason #66cc66;">==> #993333; font-weight: bold;">NOT SQLX operand
NO REWRITE
        Reason #66cc66;">==> non sqlx expression #993333; font-weight: bold;">INPUT
NO REWRITE
        Reason #66cc66;">==> no #993333; font-weight: bold;">TYPE info
NO REWRITE
        Reason #66cc66;">==> xseq:#993333; font-weight: bold;">NOT lazydef
NO REWRITE
        Reason #66cc66;">==> non rewritable sqlx #993333; font-weight: bold;">INPUT
#66cc66;">...
#66cc66;">...

…but I will have to dig a bit more to see how this all fits together…

For now / HTH / To be continued…

XMLDB Whitepapers and Tooling about Design, Performance and Selectivity

From time to time the main Oracle XML DB page gets updated with new whitepapers, tooling or Oracle By Example/ Hands-on Lab examples. “Lately” some cool and interesting new whitepapers and updated tooling content were created on this main Oracle XML DB page. The following items and content are really worth reading. Small issue, though, is that you need a bit more than basic understanding to put all this “lessons learned from the last one, two years” into context, but its worth it and otherwise a small reprise on the Oracle XML DB Developers Guide is always useful. A bit like re-reading the Oracle Concepts Manual.

The “Ease of Use Tools” (xdbutilities.zip tool set) for handling XMLType Object Relational storage has been updated and is now applicable on Oracle 10.x and 11.x. No specific to be installed versioned tool set needed anymore. This prepacked tool set on PL/SQL packages is installable on both versions. The zip file also contains a whitepaper that describes some of the (performance) lessons learned while using XMLType Object Relational storage.

Enabling and Disabling Database Options

One of those small items that is easy overlooked (at least I overlooked it...) and I think, arrived with Oracle database version 11.2, at least on Windows/Linux...

The Oracle 11gR2 Database Installation Guide for Windows described a new tool, at least for me, that enables or disables database features on Windows. In the manual it is described as follows...

When you install Oracle Database, certain options are enabled and others are disabled. If you must enable or disable a particular database feature for an Oracle home, then shut down the database and use the chopt tool. See Example 5-1.

The chopt tool is a command-line utility that is located in the ORACLE_HOME\bin directory. The syntax for chopt is as follows:

chopt [ enable | disable] db_option

The possible values for db_option described in the following table.

Part of the Puzzle: Oracle XMLDB NFS Functionality

This story is long overdue and no its NOT about the Oracle Database 11g Database File System (DBFS). Its about an “undocumented” NFS functionality that, maybe someday, will be serviced by the XMLDB XDB Protocol Adapter. This post is “long overdue” because the actual attempts to try to figure it out were done during the bank holidays between X-mas and new year 2009.

So what is it all about. I once discovered in the Oracle 11gR1 documentation a small entry in the xmlconfig.xsd XML Schema regarding NFS elements that look like that they are or will be used for enabling NFS functionality based on the Oracle XMLDB Protocol Server architecture. In those days, when Oracle 11gR1 was just of the shelve, I made a few attempts, based on the xdbconfig.xsd XML Schema to adjust the corresponding xdbconfig.xml file that controls the XDB Protocol Server functionality, to see what would happen. At that time I only was able to get this far (see the picture) and I promised myself that I should look deeper into it trying to figure out if I could get it working and/or what the concepts were that made it tick in the XMLDB architecture but somewhere down the line I just didn’t come to it and it got “forgotten” by me due to my daily DBA workload.

NFS Protocol Server functionality enabled manually

Click picture to enlarge

Structured XMLIndex (Part 3) – Building Multiple XMLIndex Structures

You will probably never build only one structured XMLIndex. A practical use case would be an unstructured XMLIndex, indexing the semi-structured parts of your XML, multiple structured XMLIndexes, indexing the highly structured XML islands of data and maybe even a Oracle Text Context index indexing unstructured XML data.

So the next example’s will show how to build an unstructured XMLIndex and build multiple structured XMLIndexes on top of the first one. Also it will give some examples on what to do if you have made mistakes and/or how to apply some maintenance on the XMLIndex structures. You start of by determining which sections should be addressed by the Unstructured XMLIndex and via path subsetting restrict the index to that part (also see “Oracle 11g – XMLIndex (Part 2) – XMLIndex Path Subsetting” for more info on path subsetting). There should be, I think, a good reason for indexing the same node path via multiple structured or unstructured XMLIndexes. One I can think of is to support different kind of XML Queries, but be aware that it, multiple XMLIndex structures on the same nodes, will come with an extra index maintenance overhead.

Anyway, lets say you want most part (haven’t used path subsetting here for the unstructured XMLIndex, but as said I should have done) of the XML document indexed via a unstructured XMLIndex and an extra of two structured XMLIndexes on top of the domain XMLIndex…

Structured XMLIndex (Part 2) – Howto build a structured XMLIndex

As said in the “rule of numb” post, test your statement before you build an XMLIndex (structured or unstructured) on you column or table XML store. The database will check on the syntax you will use but NOT on the outcome. So if you statement doesn’t have the proper result set or is even empty, than the content table(s) or path table will be indexing the wrong element values or even a null data set. Be aware that XML in Oracle is case-sensitive and critical on calling a namespace reference if one if demanded by the W3C rules.

The following example will build a single structured XMLIndex on a binary xml column.