Internals

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

tanelpoder's picture

Oracle Closed World presentation links

Thanks to everybody who attended my OCW hacking session!

Sorry to guys who attended via webinar – I’ll do the session again in a few weeks, with audio from end to end hopefully! And I will get someone to assist me with monitoring the transmission quality and attendee questions etc.

Note that this stuff is mostly for hacking and fun – don’t use the undocumented stuff in production!

The links are below:

Download scripts & Tools:

Rlwrap links:

Diagnostic events:

X$TRACE
I haven’t written any articles on X$TRACE yet, but you can find some stuff from one of my very old presentations:

Or just type:

ALTER TRACING ENABLE “10704:4:ALL”

Where 10704 is the KST event number, 4 is the level and ALL means all Oracle PIDs (Oracle PIDs, not OSPID’s).


Share/Bookmark

tanelpoder's picture

Hot stuff! Oracle Closed World Secret presentation webcast!

After hours of careful planning (as you’ll see from the images) I decided to webcast my tomorrow’s Oracle Closed World hacking session…

This session isn’t necessarily going to be useful, but it definitely should be fun !!!

You can register here:

http://tech.e2sn.com/secret

Share/Bookmark

tanelpoder's picture

Which number takes more space in an Oracle row?

So, which number takes more bytes inside an Oracle row?

A: 123

B:  1000000000000000000000000000000000000

And the correct answer is … (drumroll) … A! The “big” number 1000000000000000000000000000000000000 actually takes less space than the “small” 123!

Let’s verify this:

SQL> select vsize(123) A, vsize(1000000000000000000000000000000000000) B from dual;

         A          B
---------- ----------
         3          2

WTF? Why does such a small number 123 take more space than  1000000000000000000000000000000000000 ?

Well, the answer lies in how Oracle stores numbers. Oracle NUMBER datatype doesn’t store numbers in their platform-native integer format. Oracle uses it’s own format which stores numbers in scientific notation, in exponent-mantissa form. More details about this here.

You can use the DUMP sql function to see the actual binary value of the number data stored:

select dump(123) from dual;

DUMP(123)
---------------------
Typ=2 Len=3: 194,2,24

SQL> select dump(1000000000000000000000000000000000000) from dual;

DUMP(10000000000000
-------------------
Typ=2 Len=2: Typ=2 Len=2: 211,2

So, although the number 1000000000000000000000000000000000000 is bigger than 123, when stored in base-10 exponent form, it really carries much less information in it than 123 (1 x 10^36 vs 123 x 10^0). Oracle doesn’t need many bits for keeping the precision of this large value as it happens to be a power of 10.

See what happens when I store a number only slightly bigger or smaller than the original large number, now the stored number requires much more storage for keeping the required precision:

SQL> select dump(1000000000000000000000000000000000000+1) from dual;

DUMP(1000000000000000000000000000000000000+1)
-------------------------------------------------------
Typ=2 Len=20: 211,2,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,2

SQL> select dump(1000000000000000000000000000000000000-1) from dual;

DUMP(1000000000000000000000000000000000000-1)
-----------------------------------------------------------------------------------------
Typ=2 Len=19: 210,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100

Share/Bookmark

marco's picture

XMLDB Whitepapers and Tooling about Design, Performance and Selectivity

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

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

tanelpoder's picture

Dropping and creating tables in read only tablespaces?!

You probably already know that it’s possible to drop tables in Oracle read only tablespaces… (You did know that already, right? ;-) Here’s a little example:

SQL> create tablespace ronly datafile '/u03/oradata/LIN112/ronly.01.dbf' size 10m;

Tablespace created.

SQL> create table test tablespace ronly as select * from all_users;

Table created.

SQL> alter tablespace ronly READ ONLY;

Tablespace altered.

SQL> drop table test;

Table dropped.

I just dropped a table from a read only tablespace! Well, perhaps it’s because that instead of dropping the table was put into recyclebin instead (which is a data dictionary update)? Let’s check which segments remain in the RONLY tablespace:

SQL> select owner,segment_name,segment_type from dba_segments where tablespace_name = 'RONLY';

OWNER   SEGMENT_NAME                    SEGMENT_TYPE
------- ------------------------------- ------------------
TANEL   BIN$ix7rAUXZfB3gQKjAgS4LXg==$0  TABLE

Indeed, it seems that the table segment wasn’t actually dropped. Well, let’s purge the recycle bin to try to actually drop the table segment:

tanelpoder's picture

The full power of Oracle’s diagnostic events, part 2: ORADEBUG DOC and 11g improvements

I haven’t written any blog entries for a while, so here’s a very sweet treat for low-level Oracle troubleshooters and internals geeks out there :)

Over a year ago I wrote that Oracle 11g has a completely new low-level kernel diagnostics & tracing infrastructure built in to it. I wanted to write a longer article about it with comprehensive examples and use cases, but by now I realize I won’t ever have time for this, so I’ll just point you to the right direction :)

Basically, since 11g, you can use SQL_Trace, kernel undocumented traces, various dumps and other actions at much better granularity than before.

For example, you can enable SQL_Trace for a specific SQL_ID only:

SQL> alter session set events 'sql_trace[SQL: 32cqz71gd8wy3{pgadep: exactdepth 0} {callstack: fname opiexe}
plan_stat=all_executions,wait=true,bind=true';


Session altered.

Actually I have done more in above example, I have also said that trace only when the PGA depth (the dep= in tracefile) is zero. This means that trace only top-level calls, issued directly by the client application and not recursively by some PL/SQL or by dictionary cache layer. Additionally I have added a check whether we are currently servicing opiexe function (whether the current call stack contains opiexe as a (grand)parent function) – this allows to trace & dump only in specific cases of interest!

tanelpoder's picture

Oracle memory troubleshooting article

Randolf Geist has written a good article about systematic troubleshooting of a PL/SQL memory allocation & CPU utilization problem – and he has used some of my tools too!

http://oracle-randolf.blogspot.com/2010/05/advanced-oracle-troubleshooting-session.html

Share/Bookmark

tanelpoder's picture

Execution plan Quiz: Shouldn’t these row sources be the other way around ;-)

Here’s a little trick question. Check out the execution plan below.

What the hell, shouldn’t the INDEX/TABLE access be the other way around?!

Also, how come it’s TABLE ACCESS FULL (and not by INDEX ROWID) in there?

This question is with a little gotcha, but can you come up with a query which produced such plan? ;-)

----------------------------------------------
| Id  | Operation          | Name   | E-Rows |
----------------------------------------------
|   0 | SELECT STATEMENT   |        |        |
|*  1 |  INDEX RANGE SCAN  | PK_EMP |      1 |
|*  2 |   TABLE ACCESS FULL| EMP    |      1 |
----------------------------------------------

Share/Bookmark

To prevent automated spam submissions leave this field empty.
Syndicate content