Internals

tanelpoder's picture

Expert Oracle Exadata book – Alpha chapters available for purchase!

Hi,

Apress has made the draft versions of our Expert Oracle Exadata book available for purchase.

How this works is:

  1. You purchase the “alpha” version of the Expert Oracle Exadata book
  2. You get the access to draft/alpha PDF versions of some chapters now!
  3. As more chapters will be added and existing ones updated, you’ll receive an email and you can download these too
  4. You will get a PDF copy of the final book once it’s out!

This is an awesome deal if you can’t wait until the final launch and want to get ahead of the curve with your Exadata skills ;-)

Buy the alpha version of our Expert Oracle Exadata book from Apress here!

If you haven’t heard about this book earlier – I’m one of the 3 authors, writing it together with Kerry Osborne and Randy Johnson from Enkitec and our official tech reviewer is no other than THE Kevin Closson and we are also getting some (unofficial) feedback from Oracle database junkie Arup Nanda.

So this book will absolutely rock and if you want a piece of it now, order the alpha book above!

P.S. This hopefully also explains why I’ve been so quiet with my blogging lately – can’t write a book and do many other things at the same time… (at least if you want to do it well…)

Share

tanelpoder's picture

Advanced Oracle Troubleshooting v2.0 Online Deep Dives in April and May 2011

Due to a lot interest I’m going to do another run of my Advanced Oracle Troubleshooting v2.0 Online Deep Dive seminars in April and May (initially I had planned to do it no earlier than Sep/Oct…)

Check the dates & additional info out here:

P.S. People who already attended the AOT2 seminars last year – I will schedule the follow-up Q&A sessions in mid-March!

Share

tanelpoder's picture

Is this valid SQL syntax? :-)

I’m talking about this:

select-1from from dual;

Looks like invalid, right? Well, let’s run it:

SQL> select-1from from dual;

       ROM
----------
 -1.0E+000

This is because:

  1. Oracle doesn’t need whitespace for tokenizing the SQL statement (differences in character classes will do – as I’ve explained here)
  2. The first from “keyword” in the above statement is broken down to two tokens as an “F” right after a digit means that the preceding number is a FLOAT (and “D” means DOUBLE) and the tokenizer stops right there, knowing that whatever comes after this character (“ROM”) is a next token, which according to the Oracle SQL syntax rules will be assigned as the output column alias

The following funky-looking SQL statements are also valid:

SQL> select.1e2ffrom dual;

     .1E2F
----------
  1.0E+001

SQL> select.1e2fas"."from dual;

         .
----------
  1.0E+001

In the upper example, the “.1e2f” means number .1 * 10^2 (scientific notation) represented as a FLOAT internally and in the lower one I’ve just added a column alias with “AS” keyword just to make the SQL look a bit crazier.

:-)

Share

mwidlake's picture

You can explain an invalid SQL statement

I’m in “nightmare weekend before presenting” mode. I’m up to my eyes at work (and have been for ages, thus the quiet blog) and my recent weekends have been full of normal {and abnormal} life.

As is the way, when up against it and putting together my proofs for wild claims, everything breaks subtly and makes my wild claims look a little, well, wild – even though they are real issues I’ve seen, worked through and fixed in the day job. *sigh*. It does not help when you come across little oddities you have never seen before and end up spending valuable time looking into them.

So here is one. I’m just putting together a very, very simple demo of how the number of rows the CBO expects to see drops off as you move outside the known range. In the below you can see the statement I am using (I keep passing in different days of the month and watching the expected number of rows drop until I hit 1 expected row), but look at how it progress to the last entry…

mdw11> select count(*) from date_test_flat where date_1=to_date('&day-02-2011','DD-MM-YYYY')
  2  /
Enter value for day: 01

Execution Plan
----------------------------------------------------------
Plan hash value: 247163334

-------------------------------------------------------------------------------------
| Id  | Operation          | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                |     1 |     8 |   215   (0)| 00:00:04 |
|   1 |  SORT AGGREGATE    |                |     1 |     8 |            |          |
|*  2 |   TABLE ACCESS FULL| DATE_TEST_FLAT |    16 |   128 |   215   (0)| 00:00:04 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("DATE_1"=TO_DATE(' 2011-02-01 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))

mdw11> /
Enter value for day: 15

Execution Plan
----------------------------------------------------------
Plan hash value: 247163334

-------------------------------------------------------------------------------------
| Id  | Operation          | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                |     1 |     8 |   215   (0)| 00:00:04 |
|   1 |  SORT AGGREGATE    |                |     1 |     8 |            |          |
|*  2 |   TABLE ACCESS FULL| DATE_TEST_FLAT |     2 |    16 |   215   (0)| 00:00:04 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("DATE_1"=TO_DATE(' 2011-02-15 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))

mdw11> /
Enter value for day: 21

Execution Plan
----------------------------------------------------------
Plan hash value: 247163334

-------------------------------------------------------------------------------------
| Id  | Operation          | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                |     1 |     8 |   215   (0)| 00:00:04 |
|   1 |  SORT AGGREGATE    |                |     1 |     8 |            |          |
|*  2 |   TABLE ACCESS FULL| DATE_TEST_FLAT |     1 |     8 |   215   (0)| 00:00:04 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("DATE_1"=TO_DATE(' 2011-02-21 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))

mdw11> /
Enter value for day: 30

Execution Plan
----------------------------------------------------------
Plan hash value: 247163334

-------------------------------------------------------------------------------------
| Id  | Operation          | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                |     1 |     8 |   215   (0)| 00:00:04 |
|   1 |  SORT AGGREGATE    |                |     1 |     8 |            |          |
|*  2 |   TABLE ACCESS FULL| DATE_TEST_FLAT |    99 |   792 |   215   (0)| 00:00:04 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("DATE_1"=TO_DATE('30-02-2011','DD-MM-YYYY'))

mdw11>

The expected number of rows drops, becomes and – and has shot up to 99 again (which is the expected number in the known range, as I have 10,000 rows spread over 100 days). My immediate thought is “Wow! Maybe Oracle have put some odd fix in where when you go well out of range it reverts to expecting an average number of rows”. Nope. It is because I asked for the data for 30th February. And I did not get an error.

I think it is because I have set autotrace traceonly explain. This causes the SQL statement not to be executed {if it is just a select, not an insert, update or delete}. It seems the costing section of the CBO is not so good at spotting duff dates, but it then gets the costing wrong.

I’ve spotted that the format of the filter also changes when the date is invalid, I really want to check that out – but I better continue failing to write the presentation!

I know, pretty pointless knowing this but it just amused me. Below is just a quick continuation to show that if the statment is to be executed you get an error and no plan and that utterly duff dates can be passed in.

mdw11> /
Enter value for day: 28

Execution Plan
----------------------------------------------------------
Plan hash value: 247163334

-------------------------------------------------------------------------------------
| Id  | Operation          | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                |     1 |     8 |   215   (0)| 00:00:04 |
|   1 |  SORT AGGREGATE    |                |     1 |     8 |            |          |
|*  2 |   TABLE ACCESS FULL| DATE_TEST_FLAT |     1 |     8 |   215   (0)| 00:00:04 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("DATE_1"=TO_DATE(' 2011-02-28 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))

mdw11> SET AUTOTRACE ON
mdw11> /
Enter value for day: 20
any key>

  COUNT(*)
----------
         0

1 row selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 247163334

-------------------------------------------------------------------------------------
| Id  | Operation          | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                |     1 |     8 |   215   (0)| 00:00:04 |
|   1 |  SORT AGGREGATE    |                |     1 |     8 |            |          |
|*  2 |   TABLE ACCESS FULL| DATE_TEST_FLAT |     1 |     8 |   215   (0)| 00:00:04 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("DATE_1"=TO_DATE(' 2011-02-20 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))

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

mdw11> /
Enter value for day: 30
select count(*) from date_test_flat where date_1=to_date('30-02-2011','DD-MM-YYYY')
                                                         *
ERROR at line 1:
ORA-01839: date not valid for month specified

mdw11> set autotrace traceonly explain
mdw11> /
Enter value for day: 30

Execution Plan
----------------------------------------------------------
Plan hash value: 247163334

-------------------------------------------------------------------------------------
| Id  | Operation          | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                |     1 |     8 |   215   (0)| 00:00:04 |
|   1 |  SORT AGGREGATE    |                |     1 |     8 |            |          |
|*  2 |   TABLE ACCESS FULL| DATE_TEST_FLAT |    99 |   792 |   215   (0)| 00:00:04 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("DATE_1"=TO_DATE('30-02-2011','DD-MM-YYYY'))

mdw11> /
Enter value for day: 45

Execution Plan
----------------------------------------------------------
Plan hash value: 247163334

-------------------------------------------------------------------------------------
| Id  | Operation          | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                |     1 |     8 |   215   (0)| 00:00:04 |
|   1 |  SORT AGGREGATE    |                |     1 |     8 |            |          |
|*  2 |   TABLE ACCESS FULL| DATE_TEST_FLAT |    99 |   792 |   215   (0)| 00:00:04 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("DATE_1"=TO_DATE('45-02-2011','DD-MM-YYYY'))

tanelpoder's picture

Asynch descriptor resize wait event in Oracle

A lot of people have started seeing “asynch descriptor resize” wait event in Oracle 11gR2. Here’s my understanding of what it is. Note that I didn’t spend too much time researching it, so some details may be not completely accurate, but my explanation will at least give you an idea of why the heck you suddenly see this event in your database.

FYI, there’s a short, but incomplete explanation of this wait event also documented in MOS Note 1081977.1



The “direct path loader” (KCBL) module is used for performing direct path IO in Oracle, such as direct path segment scans and reading/writing spilled over workareas in temporary tablespace. Direct path IO is used whenever you see “direct path read/write*” wait events reported in your session. This means that IOs aren’t done from/to buffer cache, but from/to PGA directly, bypassing the buffer cache.



This KCBL module tries to dynamically scale up the number of asynch IO descriptors (AIO descriptors are the OS kernel structures, which keep track of asynch IO requests) to match the number of direct path IO slots a process uses. In other words, if the PGA workarea and/or spilled-over hash area in temp tablespace gets larger, Oracle also scales up the number of direct IO slots. Direct IO slots are PGA memory structures helping to do direct IO between files and PGA.



In order to be able to perform this direct IO asynchronously, Oracle also dynamically scales up the number of OS asynch IO descriptors, one for each slot (up to 4096 descriptors per process). When Oracle doesn’t need the direct IO slots anymore (when the direct path table scan has ended or a workarea/tempseg gets cancelled) then it scales down the number of direct IO slots and asynch IO descriptors. Scaling asynch IO descriptors up/down requires issuing syscalls to OS (as the AIO descriptors are OS kernel structures).



I guess this is supposed to be an optimization, to avoid running out of OS AIO descriptors, by releasing them when not they’re not needed, but as that Metalink note mentioned, the resize apparently sucks on Linux. Perhaps that’s why other ports also suffer and have seen the same wait event.



The “asynch descriptor resize” event itself is really an IO wait event (recorded in the wait class Other though), waiting for reaping outstanding IOs. Once this wait is over, then the OS call to change the amount of asynch IO descriptors (allocated to that process) is made. There’s no wait event recorded for the actual “resize” OS call as it shouldn’t block.



So, the more direct IO you do, especially when sorting/hashing to temp with frequent workarea closing/opening, the more of this event you’ll see (and it’s probably the same for regular tablespace direct path IO too).



This problem wouldn’t be noticeable if Oracle kept async io descriptors cached and wouldn’t constantly allocated/free them. Of course then you may end up running out of aio descriptors in the whole server easier. Also I don’t know whether there would be some OS issues with reusing cached aio descriptors, perhaps there is a good reason why such caching isn’t done.



Nevertheless, what’s causing this wait event is too frequent aio descriptor resize due to changes in direct IO slot count (due to changes in PGA workarea/temp segment and perhaps when doing frequent direct path scans through lots of tables/partitions too).



So, the obvious question here is what to do about this wait event? Well, first you should check how big part of your total response time this event takes at all?



  1. If it’s someting like 1% of your response time, then this is not your problem anyway and troubleshooting this further would be not practical – it’s just how Oracle works :)
  2. If it’s something like 20% or more of your response time, then it’s clearly a problem and you’d need to talk to Oracle Support to sort out the bug
  3. If it’s anything in between, make sure you don’t have an IO problem first, before telling that this is a bug. In one recent example I saw direct path reads take over a second on average when this problem popped up. The asynch descriptor resize wait event may well disappear from the radar once you fix the root cause – slow IO (or SQL doing too much IO). Remember, the asynch descriptor resize wait event, at least on Linux, is actually an IO wait event, the process is waiting for outstanding IO completion before the descriptor count increase/decrease can take place.

Share

tanelpoder's picture

A little new feature for shared pool geeks :-)

If you’ve taken any shared pool dumps from 11g+ databases lately, you might have wondered about this:

http://tech.e2sn.com/oracle/troubleshooting/shared-pool

Share

tanelpoder's picture

Another (secret) hacking session with me – using Oracle Session Snapper for flexible troubleshooting (and fun)

And this time we have audio !!! (Wow!)

Following the huge success of my last hacking session, planned while drinking beer at Graham Woods OOW pre-party and delivered from Miracle’s massive Oracle Closed World event in Thirsty Bear (between drinking beers), I’m announcing another hacking session:

What: Using Session Snapper for flexible Oracle Performance Troubleshooting

When: Wednesday 27th Oct 9:00-10:00 AM PDT (US West coast / California time). Check what’s this in your time zone here

Where: Internet! -> Sign up here: http://tech.e2sn.com/secret

You’ll need to register fast and be “there” on time as my current GotoWebinar account only allow 100 attendees to log on… last time over 100 people signed up, but “luckily” less actually showed up, so nobody got left outside!

BTW, I have figured out what went wrong with audio last time and caused my voice in the end of presentation disappear). A program, which I accidentally launched via a keyboard shortcut, grabbed my Mic input to itself, so gotowebinar’s app couldn’t access it anymore.

See you soon!

Share

tanelpoder's picture

A: The most fundamental difference between hash and nested loop joins

Ok guys, thanks for waiting!

I ended up expanding the article quite a lot compared to what I had originally planned. In fact I only wrote 50% of what I plan to write, I’ll update the rest… um… later… Instead of just stating the difference between the joins I took a step back and elaborated something what I often see people doing (and talking about in newsgroups and lists too).

Basically the most fundamental (or biggest or most important) difference between nested loop and hash joins is that:

  • Hash joins can not look up rows from the inner (probed) row source based on values retrieved from the outer (driving) row source, nested loops can.

In other words, when joining table A and B (A is driving table, B is the probed table), then a nested loop join can take 1st row from A and perform a lookup to B using that value (of the column(s) you join by). Then nested loop takes the next row from A and performs another lookup to table B using the new value. And so on and so on and so on.

This opens up additional access paths to the table B, for example when joining ORDERS and ORDER_ITEMS by ORDER_ID (and ORDER_ID is leading column of PK in ORDER_ITEMS table), then for whatever orders are taken from ORDERS table, we can perform a focused, narrow index range scan on ORDER_ITEMS for every ORDER_ID retrieved from the driving ORDERS table. A hash join can’t do that.

Of course this doesn’t mean that hash joins can’t use any indexes for tables they read – index range scans and unique lookups can still be used under a hash join, but only if there are constant values in the query text (in form of literal or bind variables). If there are no such constant (filter) conditions under a hash join, then the other options to use that index would be to do an INDEX FULL SCAN (which is a range scan from end to end of the index) or INDEX FAST FULL SCAN (which is like a full table scan through the entire index segment). However none of these opportunities give the same benefits as nested loops looking up rows from row source B dynamically based on what was retrieved from A during runtime.

Note that this nested loops benefit isn’t limited to indexes only on table B, the difference is more fundamental than just a specific access path. For example, if table B happens to be a single table hash cluster or indexed X$ table, then the nested loop is also able to do “optimized” lookups from these row-sources, based on the values retrieved from table A.

So, my article with a lot of (loosely) related details is here:

In the comments section of my question, Tom, Bernard Polarski, Christian Antognini and Marc Musette got the closest to what I had in my mind when I asked the question. However, of course your mileage may vary somewhat depending on what kind of problems you have experienced the most over all the years. Also, Jonathan Lewis had a valid comment regarding that the answer depends on what exactly does one mean by “fundamental” and yeah this was open to interpretation.

Nevertheless, I wanted to emphasize that there’s a more important difference between NL and hash joins, than the usual stuff you see in training material which talk about implementation details like hash tables and memory allocation…

Some day I will complete that article, I plan to add some design advice in there, like denormalization opportunities for getting the best of the both worlds etc. But now I’m gonna get a beer instead.

Thanks for reading and answering my blog, I was quite impressed by the volume of comments & answers to my question. I must do this more often!

Share

marco's picture

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…

tanelpoder's picture

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

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:

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

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

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

Share

Syndicate content