Search

Navigation

Affiliations

October 2010

Advert: PL/SQL Masterclass in Hong Kong is back…

Original article

Submitted by oraclebase on Tue, 2010-10-05 12:27

I managed to swap the Melbourne to Birmingham flights, so the Hong Kong date (October 25th-26th) is back again. If you are interested in coming, the contact details are listed here. The date hasn’t made it back onto the website yet, but it is definitely going to happen.

The final trip schedule is pretty scary.

Birmingham -> Dubai -> Singapore
Singapore PL/SQL Masterclass (14th – 15th)
Singapore -> Sydney
Sydney PL/SQL Masterclass (18th – 19th)
Speak at the Sydney Oracle Meetup in the evening (18th)
Sydney -> Melbourne
Melbourne PL/SQL Masterclass (21st – 22nd)
Melbourne -> Hong Kong
Hong Kong PL/SQL Masterclass (25th – 26th)
Hong Kong -> Bangkok -> Dubai Birmingham
Nervous Breakdown

I feel a bit sick at the thought of all that time on planes and all the timezone switches. The last time I did a trip this complicated I was away from home for 2 months. This time it’s compacted into 16 days, so if you see a fat zombie, point him to the projector and tell him to start speaking. Once the mouth starts, the rest will take care of itself.

Cheers

Tim…

Let’s play spot the Sve…

Original article

Submitted by oraclebase on Tue, 2010-10-05 11:57

I was just looking at a couple of photos from OpenWorld and I noticed a “not so little” Bulgarian hiding in the photo. Can you spot the Sve (Svetoslav Gyurov)?

Other things to notice about the photo are:

Richard Foote explaining exactly how much like David Bowie he really is. My original explanation of his action was a little more uncharitable.
Me being interested enough in Richard’s conversation to drag me away from my iPad for 2 seconds.
Chris Muir ignoring us both and trying to come to terms with the fact he hadn’t brought his iPad to the US and was forced to use one of those mini-iPads.

If you recognize yourself on this photo feel free to comment. I think the foot may belong to Jacco Landlust as I seem to remember him having one of those posh lunch boxes one of the days we were camped out in The Zone. Having said that, Jacco is usually making far more of a fashion statement than denim.

Cheers

Tim…

UKOUG Conference in Birmingham

Original article

Submitted by Chris Antognini on Tue, 2010-10-05 09:51

This is a short note to point out that I added to the Public Appearances page the next conference organized by the UKOUG in Birmingham. It will take place from the 29th of November to the 1st of December. My talk, entitled “Transaction Management Internals”, is scheduled on Wednesday 1st December.

Speaking

Deferred Segment Creation as of 11.2.0.2

Original article

Submitted by Chris Antognini on Tue, 2010-10-05 09:29

One year ago I wrote a post entitled Deferred Segment Creation. If you read the comments related to it you can see that several people were concerned by the fact that it was not possible to easily get rid of segments associated to empty tables. That was with version 11.2.0.1. Now we have version 11.2.0.2 [...]

_connect_by_use_union_all

Original article

Submitted by Timur Akhmadeev on Tue, 2010-10-05 03:01

This is just a short note on the parameter introduced in the 11gR2 called _connect_by_use_union_all. I’ve noticed it for the first time in Doc ID 7210630.8, which gives a brief overview of the changes made to the way CBO generates plans for hierarchical queries. As usually happens, the change helps to one problem, but produces [...]

Libel Reform

Original article

Submitted by Jonathan Lewis on Mon, 2010-10-04 21:19

This is an off-topic post largely for readers from the UK.

You may recall the unsuccessful libel cases brought against the writers Simon Singhand Ben Goldacre. The two writers are now something of a focal point for libel reform in the UK and since, in my own small way, I believe in pointing the finger at fakers I’m also happy to declare my support for their cause.

I’ve recently been sent the following email asking for a little financial aid for the libel reform group in their campaign, and it seems totally appropriate to broadcast it here.

Dear Friends

Your help with the libel reform campaign over the last two weeks is amazing. A huge thank you to all of you who have responded to the call for funds from Simon Singh, Ben Goldacre and Peter Wilmshurst (read their appeal again here http://www.libelreform.org/news/470-a-letter-from-simon-singh-ben-goldacre-and-peter-wilmshurst-calling-for-support-for-libel-reform). It has so far added £23,474 to our campaign funds. Some wonderful messages of support have been left at www.JustGiving.com/libelreform.

The funds enabled us to press the case for reform to MPs with events at all three party conferences, including a well-attended panel with Justice Minister Lord McNally, who will be responsible for bringing forward the promised Government bill early in the new year, and made possible the distribution of a much-needed leaflet Reforming Libel: What must a Defamation Bill achieve? This explains that our libel laws afford minimal protection for reputation and it sets out what is needed to protect public debate from chilling threats: http://www.libelreform.org/news/471-reforming-libel-what-must-a-defamation-bill-achieve (PDF).

With support continuing to grow as many of you forward the fundraising call to friends and colleagues, we can start on the substantial task of ensuring that in the next few months the Government, MPs, commentators and officials are fully aware of the impact of the libel laws on many different areas of discussion and that these are not ignored by people who oppose reform.

We also need to keep up the pressure to act from other quarters. Do you know any organisations that should be signed up to the campaign but aren’t yet? If your organisation isn’t on this list http://www.libelreform.org/who-supports-us can you ask them to sign up? And let us know who they are and how you get on.

Uncategorized

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

Original article

Submitted by marco on Mon, 2010-10-04 20:40

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…

PL/SQL Masterclass in Hong Kong?

Original article

Submitted by oraclebase on Mon, 2010-10-04 14:59

Two days after getting the final confirmation for the Singapore, Sydney and Melbourne dates and the cancellation of the Hong Kong date things have changed a little… Maybe…

I’ve been asked to try and reschedule the Hong Kong date (October 25th-26th). I’m in the process of trying to check the implications of the flight changes now. If it were a Eurpoean date it would be a no-brainer, but since it’s the other side of the world it has to be combined with the existing trip or there is simply not enough time to get home and fly back out again.

I’m hoping to know by tomorrow if the date can be rescheduled. Watch this space.

Cheers

Tim…

New online seminars – Advanced Oracle Troubleshooting v2.0 Deep Dives

Original article

Submitted by tanelpoder on Mon, 2010-10-04 14:21

As I mentioned in a previous post, I won’t be doing much flying anymore and so am changing all my seminar offering to online seminars.

So, I’ve changed and re-arranged my seminar content into self-contained 4-hour deep dives and thanks to the online nature (no travel needed), people can choose which days they want to attend. If you’re interested in latch contention only, you can attend the Latch Contention deep dive for example etc. Or you can still attend all the deep dives. The cool thing is that these deep dive sessions take only half a day, too (and are priced accordingly). That way you don’t have to skip work for the whole day (or week) and still can get some of your daily work done too. Hopefully it makes your life a bit easier when getting approval to attend the sessions.

As the main feedback from my seminars has been that “there’s too much to learn” within the short 2-3 days I used to do my seminars in, I have arranged the material so that there will be more time to go deep into the subject area. Also, I have planned plenty of time for questions & answers (1 hour out of the 4 hours is planned Q&A sessions and attendees can also ask questions any time during the lecture & demos).

It looks like I will only offer my Advanced Oracle Troubleshooting v2.0 class online this year. I will probably schedule my Advanced SQL Tuning deep dives in January/February 2011 and the Advanced Troubleshooting class again in March/April and so on (until I go public with my other business, when I won’t have time for full length training anymore).

You can check the current schedule and pricing out here:

http://tech.e2sn.com/oracle-training-seminars

Here’s a brief outline of individual half-day Deep Dives I offer:

AOT deep dive 1: Systematic approach for Advanced Oracle Troubleshooting
AOT deep dive 2: Troubleshooting physical IO and buffer cache issues
AOT deep dive 3: Troubleshooting commit, redo, undo and transaction issues
AOT deep dive 4: Troubleshooting Oracle SGA/PGA/UGA and OS memory issues
AOT deep dive 5: Troubleshooting shared pool and library cache issues
AOT deep dive 6: Troubleshooting enqueue lock waits and deadlocks
AOT deep dive 7: Troubleshooting latch contention
AOT deep dive 8: Troubleshooting Mutex and “cursor: pin” contention
AOT deep dive 9: Troubleshooting complex hangs and spins
AOT deep dive 10: Troubleshooting crashes, bugs and ORA-600/ORA-7445 errors

So, sign up now, seats are limited ;-)

Build your own 11.2.0.2 stretched RAC part III

Original article

Submitted by martin.bach on Mon, 2010-10-04 13:57

On to the next part in the series. This time I am showing how I prepared the iSCSI openFiler “appliances” on my host. This is quite straight forward, if one knows how it works :)

Setting up the openFiler appliance on the dom0

OpenFiler 2.3 has a special download option suitable for paravirtualised Xen hosts. Proceed by downloading the file from your favourite mirror, the file name I am using is “openfiler-2.3-x86_64.tar.gz”, you might have to pick another one if you don’t want a 64bit system.

All my domU go to /var/lib/xen/images/vm-name, and so do the openFiler ones. I am not using LVM to present storage to the domUs, my system came without free space I could have turned into a physical volume. Here are the steps to create the openFiler, remember to repeat this 3 times, one for each storage provider.

Begin with the first openFiler appliance. Whenever you see numbers in {} then that implies that the operation has to be repeated for each of the numbers in the curly braces.

# cd /var/lib/xen/images/
# mkdir filer0{1,2,3}
# cd filer0{1,2}

Next create the virtual disks for the appliance. I use 4G for the root file system and one 5G + 2 10G disks. The 5G disk will later on be part of the OCR and voting files disk group, whereas the other two are going to be the local ASM disks. These steps are for filer01 and filer02, the iSCSI target providers.

# dd if=/dev/zero of=disk01 bs=1 count=0 seek=4G
0+0 records in
0+0 records out
0 bytes (0 B) copied, 1.3296e-05 s, 0.0 kB/s  

# dd if=/dev/zero of=disk02 bs=1 count=0 seek=5G
# dd if=/dev/zero of=disk03 bs=1 count=0 seek=10G
# dd if=/dev/zero of=disk04 bs=1 count=0 seek=10G

For the NFS filer03, you only need two 4G disks, disk1 and disk2. For all filers, a root partition has to be created. You also have to create a file system on the “root” volume:

# mkfs.ext3 disk01
mke2fs 1.41.9 (22-Aug-2009)
disk01 is not a block special device.
Proceed anyway? (y,n) y
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
262144 inodes, 1048576 blocks
52428 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=1073741824
32 block groups
32768 blocks per group, 32768 fragments per group
8192 inodes per group
Superblock backups stored on blocks:
32768, 98304, 163840, 229376, 294912, 819200, 884736
Writing inode tables: done
Creating journal (32768 blocks): done
Writing superblocks and filesystem accounting information: done
This filesystem will be automatically checked every 21 mounts or
180 days, whichever comes first.  Use tune2fs -c or -i to override.
openSUSE-112-64-minimal:/var/lib/xen/images/filer01 #

Prepare to mount the root volume as a loop device, and also label the disk. Once mounted, copy the contents of the downloaded openfiler tarball into it as shown in this example:

# e2label disk01 root
# mkdir tmpmnt/
# mount -o loop disk01 tmpmnt/
# cd tmpmnt
# tar --gzip -xvf /m/downloads/openfiler-2.3-x86_64.tar.gz

With this done, we need to extract the kernel and the initial RAMdisk for later use in the xen config file. I have not experimented with pygrub for the openfiler appliances, someone with more knowledge may correct me here. This in any case works for this demonstration:

# mkdir  /m/xenkernels/openfiler
# cp -a /var/lib/xen/images/filer01/tmpmnt/boot /m/xenkernels/openfiler

Here are the files now stored inside the kernel directory on the dom0:

# ls -l /m/xenkernels/openfiler/
total 9276
-rw-r--r-- 1 root root  770924 May 30  2008 System.map-2.6.21.7-3.20.smp.gcc3.4.x86_64.xen.domU
-rw-r--r-- 1 root root   32220 Jun 28  2008 config-2.6.21.7-3.20.smp.gcc3.4.x86_64.xen.domU
drwxr-xr-x 2 root root    4096 Jul  1  2008 grub
-rw-r--r-- 1 root root 1112062 Jul  1  2008 initrd-2.6.21.7-3.20.smp.gcc3.4.x86_64.xen.domU.img
-rw-r--r-- 1 root root 5986208 May 14 18:01 vmlinux
-rw-r--r-- 1 root root 1558259 Jun 28  2008 vmlinuz-2.6.21.7-3.20.smp.gcc3.4.x86_64.xen.domU

With this information, at hand we can construct ourselves a xen configuration file, such as the following:

# cat filer01.xml

  filer01
  f5419d70-c124-19a9-6d64-935165c2d7d8
  524288
  524288
  1
  
  
    linux
    /m/xenkernels/openfiler/vmlinuz-2.6.21.7-3.20.smp.gcc3.4.x86_64.xen.domU
    /m/xenkernels/openfiler/initrd-2.6.21.7-3.20.smp.gcc3.4.x86_64.xen.domU.img
    root=/dev/xvda1 ro 
  
  
  destroy
  restart
  restart

In plain English, this verbose XML file describes the VM as a paravirtualised linux system with 4 hard disks and 2 network interfaces. The MAC must be static, otherwise you’ll end up with network problems each time you boot. For all currently started domUs the MAC also has to be unique! Change the UUID, name, paths to the disks (“source file”) and MAC addresses for filer02. The same applies for filer03, but this one only uses 2 disks-xvda and xvdb so please remove the disk-tags for disk03 and disk04.

Define the VM in xenstore and start it, while staying attached to the console:

# virsh define filer0{1,2,3}.xml
# xm start filer01 -c

Repeat this for filer02.xml and filer03.xml in separate terminal sessions.

Eventually, you are going to be presented with the welcome screen:

 Welcome to Openfiler NAS/SAN Appliance, version 2.3

You do not appear to have networking. Please login to start networking.

Configuring the OpenFiler domU

Log in as root (which doesn’t have a password, you should change this now!) and correct the missing network information. We have 2 virtual NICs, eth0 for the public network, and eth1 for the storage network. As root, navigate to /etc/sysconfig/network-scripts/ and edit ifcfg-eth{0,1}. In our example, we need 2 static interfaces. For eth0 for example, the existing file has the following contents:

[root@localhost network-scripts]# vi ifcfg-eth0
# Device file installed by rBuilder
DEVICE=eth0
BOOTPROTO=dhcp
ONBOOT=yes
TYPE=Ethernet

Change this to:

[root@localhost network-scripts]# cat ifcfg-eth0
# Device file installed by rBuilder
DEVICE=eth0
BOOTPROTO=none
ONBOOT=yes
NETWORK=192.168.99.0
NETMASK=255.255.255.0
IPADDR=192.168.99.50

Similarly, change ifcfg-eth1 for address 192.168.101.50 and restart the network:

[root@localhost network-scripts]# service network restart

After this, ifconfig should report the correct interfaces and you are ready to access the web console.

The network for filer02 uses 192.168.99.51 for eth0 and 192.168.101.51 for eth1. Similarly, filer03 uses 192.168.99.52 for eth0 and 192.168.101.52 for eth1.

All domUs are in the internal network, you have to set up some port forwarding rules. The easiest way to do this is in your $HOME/.ssh/config file. For my server, I set up the following options:

martin@linux-itgi:~> cat .ssh/config
Host *eq8
HostName eq8
User martin
Compression yes
# note the white space
LocalForward 4460 192.168.99.50:446
LocalForward 4470 192.168.99.51:446
LocalForward 4480 192.168.99.52:446
LocalForward 5902 192.168.99.56:5902

# other hosts
Host *
PasswordAuthentication yes
 FallBackToRsh no
martin@linux-itgi:~>

I am forwarding the local ports 4460, 4470, 4480 on my PC to the openfiler appliances. This way, I can enter https://localhost:44{6,7,8}0 to access the web frontend for the openFiler appliance. This is needed, as you can’t really administer them otherwise. When using Firefox, you’ll get a warning about certificates-I have added security exceptions because I know the web server is not conducting a man in the middle attack on me. You should always be careful adding unknown certificates to your browser in other cases.

Administering OpenFiler

NOTE: The following steps are for filer01 and filer02 only!

Once logged in as user “openfiler” (the default password is “password”), you might want to secure that password. Click on Accounts -> Admin Password and make the changes you like.

Next I recommend you verify the system setup. Click on System and review the settings. You should see the network configured correctly, and can change the hostname to filer0{1,2}.localdomain. Save your changes. Networking settings should be correct, if not you can update them here.

Next we need to partition our block devices. Previously unknown to me, openFiler uses the “gpt” format to partition disks. Click on Volumes -> Block devices to see all the block devices. Since you are running a domU, you can’t see the root device /dev/xvda. For each device (xvd{b,c,d} create one partition spanning the whole of the “disk”. You can do so by clicking on the device name. Scroll down to the “Create partition in /dev/xvdx” section and fill the data. Click “create” to create the partition. Note that you can’t see the partitions in fdisk should you log in to the appliance as root.

Once the partitions are created, it’s time to create volumes to be exported as iSCSI targets. Still in “Volumes”, click on “Volume Groups”. I chose to create the following volume groups:

ASM_VG with member PVs xvdc1 and xvdd1
OCRVOTE_VG with member PV xvdb1

Once the volume groups are created, you should proceed by creating logical volumes within these. Click on “Add Volume” to access this screen. You have a drop-down menu to select your volume group. For OCRVOTE_VG I opted to create the following logical volumes (you have to set the type to iSCSI rather than XFS):

ocrvote01_lv, about 2.5G in size, type iSCSI
ocrvote02_lv, about 2.5G in size, type iSCSI

For volume group ASM_VG, I created these logical volumes:

asmdata01_lv, about 10G in size, type iSCSI
asmdata02_lv, about 10G in size, type iSCSI

We are almost there! The storage has been carved out of the pool of available storage, and what remains to be done is the definition of the iSCSI targets and ACLs. You can define very fine grained access to iSCSI targets, and even for iSCSI discovery! This example tries to keep it simple and doesn’t use any CHAP authentication for iSCSI targets and discovery-in the real world you’d very much want to implement these security features though.

Preparing the iSCSI part

We are done for now on the Volumes tab. First, we need to enable the iSCSI target server. In “Services”, ensure that the “iSCSI target server” is enabled. If not, click on the link next to it. Before we can export any LUNs, we need to define who is eligible to mount them. In openFiler, this is configured via ACLs. Go to the “System” tab and scroll down to the “Network access configuration” section. Fill in the details of our cluster nodes here as shown below. These are the settings for edcnode1:

Name: edcnode1
Network/Host: 192.168.101.56
Netmaksk: 255.255.255.255 (IMPORTANT: it has to be 255.255.255.255, NOT 255.255.255.0)
Type: share

The settings for edcnode2 are identical, except for the IP address which is 192.168.101.58-remember, we are configuring the “STORAGE” network here! Click on “Update” to make the changes permanent. You are now ready to create the iSCSI targets, of which there will be 2: one for the OCR/Voting Disk, and another one for the ASM LUNs.

Back to the Volume tab, click on “iSCSI targets”. You will be notified that no targets have been defined yet. You will have to defined the following targets for filer01:

iqn.2006-01.com.openfiler:ocrvoteFiler01
iqn.2006-01.com.openfiler:asm01Filer01
iqn.2006-01.com.openfiler:asm02Filer01

Leave the default settings, they will do for our example. You simply add the name to the “Target IQN” field and then click on “Add”. The targets currently don’t support any LUNs yet, something that needs addressing in this step.

Switch to target iqn.2006-01.com.openfiler:ocrvoteFiler01 and then use the tab “LUN mapping” to map a LUN. In the list of available LUNs add ocrvote01_lv and ocrvote02_lv to the target. Click on “network ACL” and allow access to the LUN from edcnode1 and edcnode2. For the first ASM target, map asmdata01_lv and set the permissions, then repeat for the last target with asmdata02_lv.

Create the following targets for filer02:

iqn.2006-01.com.openfiler:ocrvoteFiler02
iqn.2006-01.com.openfiler:asm01Filer02
iqn.2006-01.com.openfiler:asm02Filer02

The mappings and settings for the ASM targets are identical to filer01, but for the OCRVOTE target only export the first logical volume, i.e. ocrvote01_lv.

NFS export

The third filer, filer03 is a little bit different in way that it only exports a NFS share to the cluster. It only has one data disk, data02. In a nutshell, create the filer as described to the point where it’s accessible via its web interface. The high level steps for it are:

Partition /dev/xvdb into 1 partition spanning the whole disk
Create a volume group ocrvotenfs_vg from /dev/xvdb1
Create a logical volume nfsvol_lv, approx 1G in size with ext3 as its file system
Enable the NFS v3 server (Services tab)

From there on the procedure is slightly different. Click on “Shares” to access the network shares available from the filer. You should see your volume group with the logical volume nfsvol_lv. Click on the link “nfsvol_lv” and enter “ocrvote” as subfolder name. A new folder icon with the name ocrvote will appear. Click on this one, and in the pop-up dialog click on “Make share”. You should set the following on the now opening lengthy configuration dialog:

Public guest acces
Host access for edcnode1 and edcnode2 for NFS RW (select the radio button)
Click on edit to access special options for edcnode1 and edcnode2. Ensure that the anonymous UID and GID match the one for the grid software owner. The UID/GID mapping has to be “all_squash”, IO mode has to be “sync”. You can ignore the write delay and origin port for this example
Leave all other protocols deselected
Click update to make the changes permanent

That was it! The storage layer is now perfectly set up for the cluster nodes which I’ll discuss in a follow-on post.

openSUSE-112-64-minimal:/var/lib/xen/images/filer01 # mkfs.ext3 disk01
mke2fs 1.41.9 (22-Aug-2009)
disk01 is not a block special device.
Proceed anyway? (y,n) y
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
262144 inodes, 1048576 blocks
52428 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=1073741824
32 block groups
32768 blocks per group, 32768 fragments per group
8192 inodes per group
Superblock backups stored on blocks:
32768, 98304, 163840, 229376, 294912, 819200, 884736Writing inode tables: done
Creating journal (32768 blocks): done
Writing superblocks and filesystem accounting information: doneThis filesystem will be automatically checked every 21 mounts or
180 days, whichever comes first. Use tune2fs -c or -i to override.
openSUSE-112-64-minimal:/var/lib/xen/images/filer01 #mkdir tmpmnt/e2label disk01 root

mount -o loop disk01 tmpmnt/

cd tmpmnt

tar –gzip -xvf ../openfiler-2.3-x86_64.tar.gz

# only for the first time
mkdir /m/xenkernels/openfiler

cp -a /var/lib/xen/images/filer01/tmpmnt/boot /m/xenkernels/openfiler/

cd ..
umount tmpmnt

openSUSE-112-64-minimal:/var/lib/xen/images/filer01 # ls -l /m/xenkernels/openfiler/
total 9276
-rw-r–r– 1 root root 770924 May 30 2008 System.map-2.6.21.7-3.20.smp.gcc3.4.x86_64.xen.domU
-rw-r–r– 1 root root 32220 Jun 28 2008 config-2.6.21.7-3.20.smp.gcc3.4.x86_64.xen.domU
drwxr-xr-x 2 root root 4096 Jul 1 2008 grub
-rw-r–r– 1 root root 1112062 Jul 1 2008 initrd-2.6.21.7-3.20.smp.gcc3.4.x86_64.xen.domU.img
-rw-r–r– 1 root root 5986208 May 14 18:01 vmlinux
-rw-r–r– 1 root root 1558259 Jun 28 2008 vmlinuz-2.6.21.7-3.20.smp.gcc3.4.x86_64.xen.domU

Search

Navigation

OakieTags

Who's online

Recent comments

Affiliations

October 2010

Advert: PL/SQL Masterclass in Hong Kong is back…

Let’s play spot the Sve…

UKOUG Conference in Birmingham

Deferred Segment Creation as of 11.2.0.2

_connect_by_use_union_all

Libel Reform

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

XMLOptimizationCheck

19027 trace name context forever, level 0×2000

PL/SQL Masterclass in Hong Kong?

New online seminars – Advanced Oracle Troubleshooting v2.0 Deep Dives

Build your own 11.2.0.2 stretched RAC part III

RSS Feeds

Popular content

Today's:

All time:

Monthly archive