Oakies Blog Aggregator

RMOUG Presentations

Like many other DBAs, I’ll be attending RMOUG training days conference on Feb 17-18 in Denver. I’ll give two presentations in the conference. On the same day, just thinking about it makes me exhausted.

The first presentation is “Everything DBAs need to know about TCP/IP Networks”. Here’s the paper and the slides. I’ll also present this at NoCOUG’s winter conference in Pleasanton, CA. Maybe you prefer to catch me there.

The second presentation is “Analyzing Database Performance using Time Series Techniques”. Here’s the paper and the slides.

I still have time to improve the presentations and papers – so comments are very welcome :)

The Core Performance Fundamentals Of Oracle Data Warehousing – Table Compression

[back to Introduction] Editor’s note: This blog post does not cover Exadata Hybrid Columnar Compression. The first thing that comes to most people’s mind when database table compression is mentioned is the savings it yields in terms of disk space. While reducing the footprint of data on disk is relevant, I would argue it is the lesser of the benefits for data warehouses. Disk capacity is very cheap and generally plentiful, however, disk bandwidth (scan speed) is proportional to the number of spindles, no mater what the disk capacity and thus is more expensive. Table compression reduces the footprint on the disk drives that a given data set occupies so the amount of physical data that must be read off the disk platters is reduced when compared to the uncompressed version. For example, if 4000 GB of raw data can compress to 1000 GB, it can be read off the same disk drives 4X as fast because it is reading and transferring 1/4 of the data off the spindles (relative to the uncompressed size). Likewise, table compression allows for the database buffer cache to contain more data without having to increase the memory allocation because more rows can be stored [...]

BAARF - Battle against any raid 5


BAARF - Battle Against any Raid Five
In the chart below, 1 represents good and 5 represents bad.
Raid 5 runs into read performance issues in a mixed workload because every write takes two reads plus a write. These two reads can conflict with other readers. Also because Raid 5 is typically configured with small stripe sizes, say 32K or 64K, to mitigate write penalties, a multiblock read can easily span several disks, increasing the chances of concurrency read conflicts.


IO operations per second


Vendors say that read cache will take care of IO Operations per sec, but if you have a sustained throughput it will only sustain for so long
Here is an example from James Morle http://scaleability.com :


Seagate Barracuda 4LP

Seagate Cheetah 73LP

Capacity

2.16GB

73.4GB

Rotation Speed

7200rpm

10000rpm

Rotational Delay(avg)

4.1ms

3ms

Time to read 32Kb

6ms

3ms

Seek Time (avg)

9.4ms

4.9

Total time for Single I/O

19.5ms

10.9ms

I/O per second (conservative)

51

92

IOPs/sec per 100GB

2550

126


The Oracle Wait Interface Is Useless (sometimes) – part 3a

OK, here it is, the ‘first part of the last part’, though the topics discussed in these articles will be discussed more over time in my blog and in Tanel’s. I’ve split it into two subparts, because it was just getting insanely long as single posting.

Before I get going on this one, let’s just clear up a misunderstanding from the first part of this series. The first part uses a specific example which, for clarity reasons, will continue to be used for the remainder of the series. The example shown is some kind of query-related issue in this case, but the approach shown here is a more general one that will locate the root cause in many, many other cases than just the case of a bad query. This SQL example is still a good one, though, because there are no waits, lots of CPU, and no system calls. But the underlying problem might not be caused by poor SQL, and diving straight into SQL tuning could waste you 2 days fixing SQL when that is not the root cause. Just because we happen to be running SQL, it does not mean that it is a SQL problem. What we are trying to achieve here is a more robust approach to root cause diagnosis of Oracle performance problems, one where we don’t guess, we don’t assume, we don’t stick with things we’ve seen before: We quantitatively find the real problem. That might be the smart way, it might be the lazy way – those might be the same thing! But most crucially, it’s the fast way to fixing the real problem.

So, back to the main topic. In the first part of this blog, I walked through the example problem and showed that we have a problem that has the following attributes:

  • The user is reporting a server response time problem
  • Zero Oracle waits
  • 100% CPU
  • Zero system calls
  • DB Time is showing all the CPU and elapsed time is spent executing SQL (in this example…)

Note to Kevin: Though I stated the query ‘never’ returns in part one, I was, of cause, using my usual exaggerative vernacular. I meant, of course, that it was taking a very long time to return, but still returning correct results. Sorry about that – it will ‘never’ happen again.

At this point, we might jump into a prolonged SQL tuning exercise. But let’s not for now, because that is still guesswork in my opinion. Let’s make the following reasonable assumption: The whole system is running slowly, using huge quantities of CPU, and this is a mission-critical system where you cannot simply start killing queries and re-executing them in your SQL*Plus session. That’s a reasonable assumption – I have worked on many, many, production systems where this is the case. So let’s carry on diagnosing without actually interfering with the production system in any way.

This is where we might deviate from time-based diagnostics. Not because we don’t think that time is the single most valuable metric on which to determine what ‘fast’ and ‘slow’ actually mean in quantitative terms, but because there is a bit of a shortcut available to us here that might just nail the cause of the problem. That shortcut is session-based statistics, and they have nothing to do with time, being simple counters.

This is where it makes no sense at all to re-invent the wheel, and was why Tanel is on the hook for this part: Tanel has already done some significant work on session-based statitics analysis and has written a great tool, snapper, for just this purpose. If you have not already read part two of this series, head over to Tanel’s blog now to catch up before proceeding!

OK, so we’ve abandoned the wait interface, and let’s assume that the sessions statistics did not yield anything particularly obvious. Or, maybe you just want to dig a little deeper, or possibly take yet another short cut to the truth? Now it’s time to look beyond Oracle and into the Operating System. The Operating System has a completely different viewpoint of the operation of Oracle, notably through the use of execution stack profiling. Don’t run away just yet, it’s not as difficult as you might imagine.

Various methods exist to probe the execution stack across the various Operating Systems. All of these should be used with caution, as they have varying degrees of intrusiveness on the operation of Oracle. The more intrusive methods, such as gdb, and the (possibly) less tested methods (oradebug short_stack) have very real potential to crash the process that you are attaching to, or worse. Don’t say I didn’t warn you! However, other methods for profiling the stack are less intrusive, and are already very robust: I’m talking here about Dtrace and variants.

I have held off from getting too excited by Dtrace in the past, even though the technology always looked amazing. The reason for holding back was that it was a single-OS tool, and without anything comparable on the Linux platform I didn’t see it being useful to >75% of my customers. That’s changing now, though, with the production release of Systemtap (partial, at least) in RHEL 5.4 and there is even a similar tool for AIX named Probevue. So now I’m excited about this technology!

Before digging into Dtrace and friends, I think it’s important that we take a little diversionary refresh into what a stack actually is, and why it is important. For those with a good coding background, look away now, and rejoin the group for part 3b. Just to keep your interest level, though, would it be interesting to get a second-by-second view of where the time goes for a process, down to the following levels of detail?

  • Which line of the plan did I spend most time in?
  • How much time did I spend waiting for page faults?
  • Which wait events did I spend most time in?

That’s going to be part 3b… so please check back periodically!

Back to the “Stack Primer for DBAs”: Consider the following elementary piece of C code:

#include 

int i;

void my_function();

void
main() {

	scanf("%c",&i);
	my_function();

}

Don’t worry about the details of the language if you are unfamiliar with C. The only things to observe here are that we have one “function”, named ‘main()’, which is making a call to another function ‘my_function()’. The main() function is a special one – all standalone C programs have a main() function, which is the initial point of execution. A C program is just a set of compiled machine code instructions at execute time, just like any other computer program. These instructions occupy a chunk of memory, where each memory location is either a machine code instruction or a piece of data. When the code gets down to the call to my_function() in main(), the execution must jump (or branch) to the address that my_function()’s machine code instructions reside at. Before this can happen, an amount of context must be stored, namely:

  • The ‘return’ address, so that execution can resume in the main() function after my_function() is complete
  • Any ‘data’ passed to my_function() (the function arguments)

In C, this information is pushed onto the stack and then the execution branches to the entry point of my_function(). As a sidenote, the sharing of data and execution context in this way is a common way to exploit security holes by overwriting execution context with oversized arguments from the data portion. We’re not interested in all that stuff for this – we are only interested in observing the stack contents. If we took a stack dump while the program were in the my_function() function, it would look like this:

my_function()
main()

Pretty simple, huh? If there were arguments for main() and my_function(), they would also be shown in some way. Most stack trace facilities can only show primitive datatypes (such as integers) and all other more complex arguments (such as structs) are shown as pointers (addresses) to the actual data without decoding the actual content, as follows:

my_function(42, 0x12345678)
main(0x45678900)

Some tools will print a stack trace (known as a backtrace, because it is unwound backwards from the current position in the stack) with some known pointers decoded into human readable form, such as simple struct datatypes. Anyway, let’s have a look at something a bit more complicated – an Oracle backtrace taken from an errorstack trace file:

----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
-------------------- -------- -------------------- ----------------------------
skdstdst()+36        call     kgdsdst()            000000000 ? 000000000 ?
                                                   7FFFB3456278 ? 000000001 ?
                                                   7FFFB345A778 ? 000000000 ?
ksedst1()+98         call     skdstdst()           000000000 ? 000000000 ?
                                                   7FFFB3456278 ? 000000001 ?
                                                   000000000 ? 000000000 ?
ksedst()+34          call     ksedst1()            000000001 ? 000000001 ?
                                                   7FFFB3456278 ? 000000001 ?
                                                   000000000 ? 000000000 ?
dbkedDefDump()+2736  call     ksedst()             000000001 ? 000000001 ?
                                                   7FFFB3456278 ? 000000001 ?
                                                   000000000 ? 000000000 ?
ksedmp()+36          call     dbkedDefDump()       000000001 ? 000000000 ?
                                                   7FFFB3456278 ? 000000001 ?
                                                   000000000 ? 000000000 ?
ksdxfdmp()+1837      call     ksedmp()             000000001 ? 000000000 ?
                                                   7FFFB3456278 ? 000000001 ?
                                                   000000000 ? 000000000 ?
ksdxcb()+1782        call     ksdxfdmp()           7FFFB345BAE0 ? 000000011 ?
                                                   000000003 ? 7FFFB345BA40 ?
                                                   7FFFB345B9A0 ? 000000000 ?
sspuser()+112        call     ksdxcb()             000000001 ? 000000011 ?
                                                   000000001 ? 000000001 ?
                                                   7FFFB345B9A0 ? 000000000 ?
__restore_rt()       call     sspuser()            000000001 ? 000000011 ?
                                                   000000001 ? 000000001 ?
                                                   7FFFB345B9A0 ? 000000000 ?
semtimedop()+10      signal   __restore_rt()       000018000 ? 7FFFB345C730 ?
                                                   000000001 ?
                                                   FFFFFFFFFFFFFFFF ?
                                                   FFFFFFFFFFD23940 ?
                                                   000000000 ?
sskgpwwait()+259     call     semtimedop()         000018000 ? 7FFFB345C730 ?
                                                   000000001 ? 7FFFB345C6D8 ?
                                                   FFFFFFFFFFD23940 ?
                                                   000000000 ?
skgpwwait()+151      call     sskgpwwait()         7FFFB345CB94 ? 00A9A15C0 ?
                                                   07848C4D8 ? 0002DC6C0 ?
                                                   7FFF00000000 ? 000000000 ?
ksliwat()+1816       call     skgpwwait()          7FFFB345CB94 ? 00A9A15C0 ?
                                                   000000000 ? 0002DC6C0 ?
                                                   000000000 ? 000000000 ?
kslwaitctx()+157     call     ksliwat()            078666348 ? 078666348 ?
                                                   005F5DFA7 ? 000000000 ?
                                                   100000000 ? 000000000 ?
kslwait()+136        call     kslwaitctx()         7FFFB345CE30 ? 000000000 ?
                                                   005F5DFA7 ? 000000000 ?
                                                   100000000 ? 000000000 ?
psdwat()+107         call     kslwait()            005F5DFA7 ? 000000167 ?
                                                   000000000 ? 005F5DFA7 ?
                                                   000000000 ? 000000000 ?
pevm_icd_call_commo  call     psdwat()             005F5DFA7 ? 000000167 ?
n()+421                                            000000000 ? 005F5DFA7 ?
                                                   000000000 ? 000000000 ?
pfrinstr_ICAL()+164  call     pevm_icd_call_commo  7FFFB345E0F0 ? 000000000 ?
                              n()                  000000001 ? 000000004 ?
                                                   7FAF00000001 ? 000000000 ?
pfrrun_no_tool()+63  call     pfrinstr_ICAL()      7FAF7E8A3500 ? 06A03B9AA ?
                                                   7FAF7E8A3570 ? 000000004 ?
                                                   7FAF00000001 ? 000000000 ?
pfrrun()+1025        call     pfrrun_no_tool()     7FAF7E8A3500 ? 06A03B9AA ?
                                                   7FAF7E8A3570 ? 000000004 ?
                                                   7FAF00000001 ? 000000000 ?
plsql_run()+769      call     pfrrun()             7FAF7E8A3500 ? 000000000 ?
                                                   7FAF7E8A3570 ? 7FFFB345E0F0 ?
                                                   7FAF00000001 ? 070C18646 ?
peicnt()+296         call     plsql_run()          7FAF7E8A3500 ? 000000001 ?
                                                   000000000 ? 7FFFB345E0F0 ?
                                                   7FAF00000001 ? 000000000 ?
kkxexe()+520         call     peicnt()             7FFFB345E0F0 ? 7FAF7E8A3500 ?
                                                   7FAF7E8C4028 ? 7FFFB345E0F0 ?
                                                   7FAF7E8C1FD0 ? 000000000 ?
opiexe()+14796       call     kkxexe()             7FAF7E8A5128 ? 7FAF7E8A3500 ?
                                                   000000000 ? 7FFFB345E0F0 ?
                                                   7FAF7E8C1FD0 ? 000000000 ?
kpoal8()+2283        call     opiexe()             000000049 ? 000000003 ?
                                                   7FFFB345F678 ? 7FFFB345E0F0 ?
                                                   7FAF7E8C1FD0 ? 000000000 ?
opiodr()+1149        call     kpoal8()             00000005E ? 00000001C ?
                                                   7FFFB3462750 ? 7FFFB345E0F0 ?
                                                   7FAF7E8C1FD0 ? 5E00000001 ?
ttcpip()+1251        call     opiodr()             00000005E ? 00000001C ?
                                                   7FFFB3462750 ? 000000000 ?
                                                   008C5E7F0 ? 5E00000001 ?
opitsk()+1628        call     ttcpip()             00A9B0890 ? 0086BA768 ?
                                                   7FFFB3462750 ? 000000000 ?
                                                   7FFFB34621B0 ? 7FFFB3462954 ?
opiino()+953         call     opitsk()             00A9B0890 ? 000000000 ?
                                                   7FFFB3462750 ? 000000000 ?
                                                   7FFFB34621B0 ? 7FFFB3462954 ?
opiodr()+1149        call     opiino()             00000003C ? 000000004 ?
                                                   7FFFB3463E48 ? 000000000 ?
                                                   7FFFB34621B0 ? 7FFFB3462954 ?
opidrv()+565         call     opiodr()             00000003C ? 000000004 ?
                                                   7FFFB3463E48 ? 000000000 ?
                                                   008C5E2A0 ? 7FFFB3462954 ?
sou2o()+98           call     opidrv()             00000003C ? 000000004 ?
                                                   7FFFB3463E48 ? 000000000 ?
                                                   008C5E2A0 ? 7FFFB3462954 ?
opimai_real()+128    call     sou2o()              7FFFB3463E20 ? 00000003C ?
                                                   000000004 ? 7FFFB3463E48 ?
                                                   008C5E2A0 ? 7FFFB3462954 ?
ssthrdmain()+209     call     opimai_real()        000000002 ? 7FFFB3464010 ?
                                                   000000004 ? 7FFFB3463E48 ?
                                                   008C5E2A0 ? 7FFFB3462954 ?
main()+196           call     ssthrdmain()         000000002 ? 7FFFB3464010 ?
                                                   000000001 ? 000000000 ?
                                                   008C5E2A0 ? 7FFFB3462954 ?
__libc_start_main()  call     main()               000000002 ? 7FFFB34641B8 ?
+253                                               000000001 ? 000000000 ?
                                                   008C5E2A0 ? 7FFFB3462954 ?
_start()+36          call     __libc_start_main()  0009D3D74 ? 000000002 ?
                                                   7FFFB34641A8 ? 000000000 ?
                                                   008C5E2A0 ? 7FFFB3462954 ?

I know, it’s a bit much to deal with at first glance. But take it a piece at a time, reading in conjunction with Metalink note 175982.1, and it all begins to make sense. Actually, I’m guessing some of these, because the Metalink note doesn’t give all the answers, but it seems to make sense.

Let’s start at the bottom, which is the beginning of the execution of the C program named ‘oracle’. The first two lines are the GLIBC execution wrappers that eventually call Oracle’s main() statement which starts at line 110 of the listing. The next several lines above are the initialisation of Oracle and the (O)racle (P)rogramatic (I)nterface, which is the internal API used in Oracle. TTCPIP is the Two-Task-Communication interface with my SQL*Plus process, but the first interesting line is at line 74, kkxexe(), which is the (k)ernel (k)ompile e(x)ecute) (exe)cute statement call, which is the start of the actual SQL processing. In this case it is actually a simple PL/SQL call to DBMS_LOCK.SLEEP(100000), and the entry to the Oracle Wait interface can be seen at line 50 with the kslwait() call, eventually ending up with a semtimedop() call, which is the way the sleep call has been implemented. Incidentally, the preceding sskgpwwait() call at line 37 is Oracle’s platform-specific code which, in this case, is Red Hat Linux.  At line 11 we see the call to ksedst(), which probably stands for (k)ernel (s)ervices (e)rrorstack (d)ump (s)tack (t)race, which is the part of code actually producing the stack trace we are reading.

So what does all this actually mean in terms of seeing what our Oracle session is up to? Well, we can see what is called, by whom, and how long each call took. We don’t need to know all the function names and what they are responsible for, but we can come up with a selection of interesting ones that we would like to pay attention to. Thinking in terms of SQL execution we can see, for example, a nested loop rowsource function calling out to an index fetch rowsource function, which in turn calls out to a table fetch function.

Even just a few consecutive stack traces of a running query will make it very plain which rowsource Oracle is spending the most time within, simply because of the laws of probability. If you look once a second for a minute, and every stack you see has the ‘index fetch’ rowsource as the current function, then you probably spent most of the last minute doing that kind of processing. But we can do better than that… we can start to look at quantifying where the time is spent, and also to supplement that information with other interesting aspects of the operating system operation, aspects which are somewhat  concealed from Oracle and yet can detrimentally affect its performance.

Join me back for part 3b for the conclusion of this series of articles!

ORA-31098: Internal event to turn on XDB tracing

“Do not document”…

…if not only due to that comment, it is a very interesting event, if not only that it seems that it is used for multiple items and not only tracing. I tried to figure out what I could do with this event regarding the XDB Protocol Server trying to figure out how it works and to trace a partially documented/undocumented feature in the manuals that makes use of the XDB Protocol Server.

The XDB Procotol Server architecture is used for more than the (APEX) PL/SQL Gateway. It also supports HTTP, FTP and WebDAV (so called) “servlets” and a hook-in into C kernel library that enables the XMLDB Native Database Web Service (NDWS).


The following should only be done asked by Oracle Support and/or are at your own risk. Always test on a test environment (so if when the database is corrupt is not a big deal)

Anyway, “events” can be set on session and system level and/or via the oradebug facility. Julian Dyke has a good post on the basics. As Julian describes, there are four types of numeric events: Immediate dumps, Conditional dumps, Trace dumps, Events that change database behavior. The “fun” with the ORA-31098 seems that is used for multiple things. It at least creates DDL scripts in trace file during dbms_xmlschema registration and it also traces XDB Protocol Server issues. During X-Mas and new years eve I had some time, so I tried to figure out some of them…

Described in a small overview XDB event post, some of these settings can be found somewhere in the Oracle manuals…

Debugging XML Schema Registration

ORA-31098: Internal event to turn on XDB tracing

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

#993333; font-weight: bold;">ALTER session #993333; font-weight: bold;">SET events #66cc66;">= #ff0000;">'31098 trace name context forever'

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

Tracing The Protocol Server

This event is tracing the XDB protocol server. Trace files will be written to the udump directory. Look out for shared server trace files like the following: SID_s000_17016.trc. Depending on the amount of shared servers you have defined, you will encounter more then one trace file. Be ware that this is causing a lot of extra overhead, so switch it off (remove the event setting from the database parameter file / spfile) if you don’t use it any more…

For multiple system event 31098 level x settings, although tedious I checked level 1-16, while doing every time the following, after bouncing the database with the new event 31098 level setting:

SQL#66cc66;">> #993333; font-weight: bold;">ALTER system #993333; font-weight: bold;">SET event#66cc66;">= 
     #ff0000;">'31098 trace name context forever, level x' scope#66cc66;">=spfile;
SQL#66cc66;">> shutdown immediate
SQL#66cc66;">> startup

HTTP

  • Connected via HTTP on default port 8080 while using the SYSTEM database account
  • Clicked on the OLAP_XDB folder
  • Clicked on the dsclass.xml file and therefore opening it
  • Closed the Windows Internet Explorer

FTP

  • Opened a FTP session by connecting on default port 2100
  • Logging in via the SYSTEM database account
  • Did a “ls” statement
  • Did a “cd OLAP_XDB” statement
  • Did a “get dsclass.xml” statement
  • Exited the ftp session via “bye”

Now to the results of my attempt to document the undocumented…

31098 trace name context forever, level 1

HTTP – Level 1 showed simple tracing of HTTP 1.1 messages like

#66cc66;">*** #cc66cc;">2009#66cc66;">-06#66cc66;">-08 #cc66cc;">23:07:#cc66cc;">40.120
HTTP: GET : #66cc66;">/OLAP_XDS#66cc66;">/ : HTTP#66cc66;">/#cc66cc;">1.1 #cc66cc;">200 OK
HTTP: GET : #66cc66;">/favicon#66cc66;">.ico : HTTP#66cc66;">/#cc66cc;">1.1 #cc66cc;">404 #993333; font-weight: bold;">NOT found

FTP – Level 1 caused connection problems…

ftp#66cc66;">> ls
#cc66cc;">200 PORT Command successful
#cc66cc;">150 ASCII #993333; font-weight: bold;">DATA Connection
Aborting any active #993333; font-weight: bold;">DATA connections#66cc66;">...
#cc66cc;">550 NLST error #993333; font-weight: bold;">IN processing request

The NLST statement for ftp is the raw statement that returns a list of file names in the given directory.

31098 trace name context forever, level 2

HTTP – Level 2 showed HTTP 1.1 tracing:

  • HTTP 1.1 state messagecodes
  • DAV security calls
  • Client info like browser version etc
  • Directory info send
  • File get statement
  • Directory get statement
  • File content
  • Server info
  • ETag and GUID / mime info

FTP – No information about the ftp session was traced.

31098 trace name context forever, level 3

HTTP – Level 3 showed HTTP 1.1 tracing

  • HTTP 1.1 state messagecodes
  • DAV security calls
  • Client info like browser version etc
  • Directory info send
  • File get statement
  • Directory get statement
  • ETag and GUID / mime info

FTP – No information about the ftp session was traced.

31098 trace name context forever, level 4

No HTTP or FTP tracing but tracing of execution of XDB repository event triggers…

#808080; font-style: italic;">--------Dumping Sorted Master Trigger List --------
#993333; font-weight: bold;">TRIGGER Owner : XDB
#993333; font-weight: bold;">TRIGGER Name : XDBCONFIG_VALIDATE
#993333; font-weight: bold;">TRIGGER Owner : XDB
#993333; font-weight: bold;">TRIGGER Name : XDB$CONFIG$xd
#808080; font-style: italic;">--------Dumping Trigger Sublists --------
 #993333; font-weight: bold;">TRIGGER sublist #cc66cc;">0 :
 #993333; font-weight: bold;">TRIGGER sublist #cc66cc;">1 :
#993333; font-weight: bold;">TRIGGER Owner : XDB
#993333; font-weight: bold;">TRIGGER Name : XDBCONFIG_VALIDATE
 #993333; font-weight: bold;">TRIGGER sublist #cc66cc;">2 :
 #993333; font-weight: bold;">TRIGGER sublist #cc66cc;">3 :
#993333; font-weight: bold;">TRIGGER Owner : XDB
#993333; font-weight: bold;">TRIGGER Name : XDB$CONFIG$xd
 #993333; font-weight: bold;">TRIGGER sublist #cc66cc;">4 :

HTTP – No information about the http session was traced.
FTP – No information about the ftp session was traced.

31098 trace name context forever, level 5

HTTP – Level 5 showed HTTP 1.1 server tracing

qmhGetHTTPError: Got unknown oracle error#66cc66;">. Error stack #993333; font-weight: bold;">IS:
HTTP#66cc66;">/#cc66cc;">1.1 #cc66cc;">500 Internal Server Error
MS#66cc66;">-Author#66cc66;">-Via: DAV
DAV: #cc66cc;">1#66cc66;">,#cc66cc;">2#66cc66;">,<http :#66cc66;">//www#66cc66;">.oracle#66cc66;">.com#66cc66;">/xdb#66cc66;">/webdav#66cc66;">/props#66cc66;">>
Server: Oracle XML DB#66cc66;">/Oracle #993333; font-weight: bold;">DATABASE
Date: Mon#66cc66;">, 08 Jun #cc66cc;">2009 #cc66cc;">21:#cc66cc;">52:#cc66cc;">37 GMT
Content#66cc66;">-Type: text#66cc66;">/html; charset#66cc66;">=UTF#66cc66;">-#cc66cc;">8
Content#66cc66;">-Length: #cc66cc;">165
 
#66cc66;">< !DOCTYPE HTML PUBLIC #ff0000;">"-//IETF//DTD HTML 2.0//EN"#66cc66;">>
#66cc66;"><html#66cc66;">><head#66cc66;">>
#66cc66;"><title#66cc66;">>#cc66cc;">500 Internal Server Error#66cc66;">title#66cc66;">>
#66cc66;">head#66cc66;">><body#66cc66;">><h1#66cc66;">>Internal Server Error#66cc66;">h1#66cc66;">>
#66cc66;">body#66cc66;">>html#66cc66;">>
#66cc66;">http#66cc66;">>

FTP – No information about the ftp session was traced.

31098 trace name context forever, level 6

As far as I could detect, it looked the same as level 3…
To give you an example, trace output had the following output.

#66cc66;">*** #cc66cc;">2009#66cc66;">-06#66cc66;">-08 #cc66cc;">23:#cc66cc;">56:#cc66cc;">01.513
GET
#66cc66;">/OLAP_XDS#66cc66;">/
HTTP#66cc66;">/#cc66cc;">1.1
Accept: image#66cc66;">/gif#66cc66;">, image#66cc66;">/x#66cc66;">-xbitmap#66cc66;">, image#66cc66;">/jpeg#66cc66;">, image#66cc66;">/pjpeg#66cc66;">, application#66cc66;">/x#66cc66;">-shockwave#66cc66;">-flash#66cc66;">, application#66cc66;">/vnd#66cc66;">.ms#66cc66;">-excel#66cc66;">, application#66cc66;">/vnd#66cc66;">.ms#66cc66;">-powerpoint#66cc66;">, application#66cc66;">/msword#66cc66;">, #66cc66;">*#808080; font-style: italic;">/*
Referer: http://10.252.252.102:8080/
Accept-Language: nl
UA-CPU: x86
Accept-Encoding: gzip, deflate
If-None-Match: "6850905AC52423DBE040E40AD6DE7A51786D04180B2E2F68C47904"
User-Agent: Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; .NET CLR 1.1.4322; .NET CLR 2.0.50727; InfoPath.2)
Host: 10.252.252.102:8080
Connection: Keep-Alive
Authorization: ***

FTP – No information about the ftp session was traced.

31098 trace name context forever, level 7

HTTP tracing is the same as level 3 and 6

FTP tracing showed the following (based on the statements used showed below):

ftp#66cc66;">> open localhost
ftp: connect: Connection refused
ftp#66cc66;">> open localhost #cc66cc;">2100
Connected #993333; font-weight: bold;">TO localhost#66cc66;">.localdomain#66cc66;">.
#cc66cc;">220#66cc66;">- srv01#66cc66;">-#cc66cc;">18#66cc66;">-102#66cc66;">.amis#66cc66;">.#993333; font-weight: bold;">LOCAL
Unauthorised #993333; font-weight: bold;">USE of this FTP server #993333; font-weight: bold;">IS prohibited #993333; font-weight: bold;">AND may be subject #993333; font-weight: bold;">TO civil #993333; font-weight: bold;">AND criminal prosecution#66cc66;">.
#cc66cc;">220 srv01#66cc66;">-#cc66cc;">18#66cc66;">-102#66cc66;">.amis#66cc66;">.#993333; font-weight: bold;">LOCAL FTP Server #66cc66;">(Oracle XML DB#66cc66;">/Oracle #993333; font-weight: bold;">DATABASE#66cc66;">) ready#66cc66;">.
#cc66cc;">530  Please login #993333; font-weight: bold;">WITH USER #993333; font-weight: bold;">AND PASS#66cc66;">.
#cc66cc;">530  Please login #993333; font-weight: bold;">WITH USER #993333; font-weight: bold;">AND PASS#66cc66;">.
KERBEROS_V4 rejected #993333; font-weight: bold;">AS an authentication type
Name #66cc66;">(localhost:oracle#66cc66;">): system
#cc66cc;">331 pass required #993333; font-weight: bold;">FOR SYSTEM
Password:
#cc66cc;">230 SYSTEM logged #993333; font-weight: bold;">IN
Remote system type #993333; font-weight: bold;">IS Unix#66cc66;">.
ftp#66cc66;">> ls
#cc66cc;">227 Entering Passive Mode #66cc66;">(#cc66cc;">10#66cc66;">,#cc66cc;">252#66cc66;">,#cc66cc;">252#66cc66;">,#cc66cc;">102#66cc66;">,#cc66cc;">62#66cc66;">,#cc66cc;">156#66cc66;">)
#cc66cc;">150 ASCII #993333; font-weight: bold;">DATA Connection
drw#66cc66;">-r#808080; font-style: italic;">--r--   2 SYS      oracle         0 APR 24 10:45 OLAP_XDS
drw#66cc66;">-r#808080; font-style: italic;">--r--   2 SYS      oracle         0 MAY 25 07:49 home
drw#66cc66;">-r#808080; font-style: italic;">--r--   2 SYS      oracle         0 APR 24 11:01 images
drw#66cc66;">-r#808080; font-style: italic;">--r--   2 SYS      oracle         0 APR 24 10:45 olap_data_security
drw#66cc66;">-r#808080; font-style: italic;">--r--   2 SYS      oracle         0 MAY 27 17:29 public
drw#66cc66;">-r#808080; font-style: italic;">--r--   2 SYS      oracle         0 MAY 11 18:34 sys
#66cc66;">-rw#66cc66;">-r#808080; font-style: italic;">--r--   1 SYS      oracle         0 MAY 15 14:35 xdbconfig.xml
drw#66cc66;">-r#808080; font-style: italic;">--r--   2 SYS      oracle         0 APR 24 10:45 xds
#cc66cc;">226 ASCII Transfer Complete
ftp#66cc66;">> cd OLAP_XDS
#cc66cc;">250 CWD Command successful
ftp#66cc66;">> ls
#cc66cc;">227 Entering Passive Mode #66cc66;">(#cc66cc;">10#66cc66;">,#cc66cc;">252#66cc66;">,#cc66cc;">252#66cc66;">,#cc66cc;">102#66cc66;">,#cc66cc;">39#66cc66;">,#cc66cc;">101#66cc66;">)
#cc66cc;">150 ASCII #993333; font-weight: bold;">DATA Connection
#66cc66;">-rw#66cc66;">-r#808080; font-style: italic;">--r--   1 SYS      oracle         0 APR 24 10:45 dsclass.xml
#cc66cc;">226 ASCII Transfer Complete
ftp#66cc66;">> get dsclass#66cc66;">.xml
#993333; font-weight: bold;">LOCAL: dsclass#66cc66;">.xml remote: dsclass#66cc66;">.xml
#cc66cc;">227 Entering Passive Mode #66cc66;">(#cc66cc;">10#66cc66;">,#cc66cc;">252#66cc66;">,#cc66cc;">252#66cc66;">,#cc66cc;">102#66cc66;">,#cc66cc;">221#66cc66;">,#cc66cc;">72#66cc66;">)
#cc66cc;">150 ASCII #993333; font-weight: bold;">DATA Connection
#cc66cc;">226 ASCII Transfer Complete
#cc66cc;">1078 bytes received #993333; font-weight: bold;">IN #cc66cc;">0.004 seconds #66cc66;">(2#66cc66;">.6e#66cc66;">+02 Kbytes#66cc66;">/s#66cc66;">)
ftp#66cc66;">> bye
#cc66cc;">221 QUIT Goodbye#66cc66;">.

The trace output for these statements were traced as follows:

#66cc66;">*** #cc66cc;">2009#66cc66;">-06#66cc66;">-09 00:08:#cc66cc;">45.916
#cc66cc;">220#66cc66;">- srv01#66cc66;">-#cc66cc;">18#66cc66;">-102#66cc66;">.amis#66cc66;">.#993333; font-weight: bold;">LOCAL 
Unauthorised #993333; font-weight: bold;">USE of this FTP server #993333; font-weight: bold;">IS prohibited #993333; font-weight: bold;">AND may be subject #993333; font-weight: bold;">TO civil #993333; font-weight: bold;">AND criminal prosecution#66cc66;">.
#cc66cc;">220 srv01#66cc66;">-#cc66cc;">18#66cc66;">-102#66cc66;">.amis#66cc66;">.#993333; font-weight: bold;">LOCAL FTP Server #66cc66;">(Oracle XML DB#66cc66;">/Oracle #993333; font-weight: bold;">DATABASE#66cc66;">) ready#66cc66;">.
AUTH GSSAPI
#cc66cc;">530  Please login #993333; font-weight: bold;">WITH USER #993333; font-weight: bold;">AND PASS#66cc66;">.
AUTH KERBEROS_V4
#cc66cc;">530  Please login #993333; font-weight: bold;">WITH USER #993333; font-weight: bold;">AND PASS#66cc66;">.
 
#66cc66;">*** #cc66cc;">2009#66cc66;">-06#66cc66;">-09 00:08:#cc66cc;">48.034
USER system
#cc66cc;">331 pass required #993333; font-weight: bold;">FOR SYSTEM
 
#66cc66;">*** #cc66cc;">2009#66cc66;">-06#66cc66;">-09 00:08:#cc66cc;">49.176
PASS XXXXXXX
#cc66cc;">230 SYSTEM logged #993333; font-weight: bold;">IN
SYST
#cc66cc;">215 Unix Type:A Version:Oracle XML DB 
 
#66cc66;">*** #cc66cc;">2009#66cc66;">-06#66cc66;">-09 00:08:#cc66cc;">50.449
PASV
#cc66cc;">227 Entering Passive Mode #66cc66;">(#cc66cc;">10#66cc66;">,#cc66cc;">252#66cc66;">,#cc66cc;">252#66cc66;">,#cc66cc;">102#66cc66;">,#cc66cc;">62#66cc66;">,#cc66cc;">156#66cc66;">)
LIST
#cc66cc;">150 ASCII #993333; font-weight: bold;">DATA Connection
#cc66cc;">226 ASCII Transfer Complete
 
#66cc66;">*** #cc66cc;">2009#66cc66;">-06#66cc66;">-09 00:08:#cc66cc;">52.244
CWD OLAP_XDS
#cc66cc;">250 CWD Command successful
 
#66cc66;">*** #cc66cc;">2009#66cc66;">-06#66cc66;">-09 00:08:#cc66cc;">53.159
PASV
#cc66cc;">227 Entering Passive Mode #66cc66;">(#cc66cc;">10#66cc66;">,#cc66cc;">252#66cc66;">,#cc66cc;">252#66cc66;">,#cc66cc;">102#66cc66;">,#cc66cc;">39#66cc66;">,#cc66cc;">101#66cc66;">)
LIST
#cc66cc;">150 ASCII #993333; font-weight: bold;">DATA Connection
#cc66cc;">226 ASCII Transfer Complete
 
#66cc66;">*** #cc66cc;">2009#66cc66;">-06#66cc66;">-09 00:08:#cc66cc;">55.688
PASV
#cc66cc;">227 Entering Passive Mode #66cc66;">(#cc66cc;">10#66cc66;">,#cc66cc;">252#66cc66;">,#cc66cc;">252#66cc66;">,#cc66cc;">102#66cc66;">,#cc66cc;">221#66cc66;">,#cc66cc;">72#66cc66;">)
RETR dsclass#66cc66;">.xml
#cc66cc;">150 ASCII #993333; font-weight: bold;">DATA Connection
#cc66cc;">226 ASCII Transfer Complete
 
#66cc66;">*** #cc66cc;">2009#66cc66;">-06#66cc66;">-09 00:08:#cc66cc;">56.907
QUIT
#cc66cc;">221 QUIT Goodbye#66cc66;">.

31098 trace name context forever, level 8

Level 8 traced the same kind of error as in level 5 (server errors)

31098 trace name context forever, level 9

Level 9 traced the same kind of error as in level 7 (FTP & HTTP output)

31098 trace name context forever, level 10

Level 10 traced the same kind of error as in level 7 (FTP & HTTP output)

31098 trace name context forever, level 11

Level 11 traced the same kind of error as in level 7 (FTP & HTTP output)

31098 trace name context forever, level 12

Level 12 traced the same kind of error as in level 5 (server errors)

31098 trace name context forever, level 13 – 16

Level 13 until level 16 didn’t produce any trace files regarding my FTP and HTTP statements.

Summery

Although this tracing is far from complete regarding actions I could have done like WebDAV access or to trigger automatic shredding of objects or other XDB event (repository?) relevant actions, at least some events could be useful for further investigation if needed regarding HTTP tracing (level 2), FTP tracing (eg. level 7) and repository event XML Schema triggers (level 4) and automatic XDB shredding mechanisms.

Setting event level 2 could be useful regarding APEX PL/SQL Gateway tracing and/or for APEX PL/SQL Gateway “hacking”, although I think your asking for it, if the PL/SQL Gateway via the XDB Proctocol Server has been put on the internet unprotected, and does not at least a proxy server in front of it to protect for some unauthorized access…

HTH

:-)

Structured XMLIndex (Part 3) – Building Multiple XMLIndex Structures

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

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

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

Structured and Unstructured XMLIndex structures combined

SQL#66cc66;">> #993333; font-weight: bold;">CREATE user otn #993333; font-weight: bold;">IDENTIFIED #993333; font-weight: bold;">BY otn account #993333; font-weight: bold;">UNLOCK;
 
User created#66cc66;">.
 
SQL#66cc66;">> #993333; font-weight: bold;">GRANT dba#66cc66;">, xdbadmin #993333; font-weight: bold;">TO otn;
 
#993333; font-weight: bold;">GRANT succeeded#66cc66;">.
 
SQL#66cc66;">> #993333; font-weight: bold;">SELECT #66cc66;">* #993333; font-weight: bold;">FROM v$version;
 
BANNER
#808080; font-style: italic;">--------------------------------------------------------------------------------
Oracle #993333; font-weight: bold;">DATABASE 11g Enterprise Edition Release 11#66cc66;">.2#66cc66;">.0#66cc66;">.1#66cc66;">.0 #66cc66;">- Production
PL#66cc66;">/SQL Release 11#66cc66;">.2#66cc66;">.0#66cc66;">.1#66cc66;">.0 #66cc66;">- Production
CORE    11#66cc66;">.2#66cc66;">.0#66cc66;">.1#66cc66;">.0      Production
TNS #993333; font-weight: bold;">FOR Linux: Version 11#66cc66;">.2#66cc66;">.0#66cc66;">.1#66cc66;">.0 #66cc66;">- Production
NLSRTL Version 11#66cc66;">.2#66cc66;">.0#66cc66;">.1#66cc66;">.0 #66cc66;">- Production
 
SQL#66cc66;">> #993333; font-weight: bold;">CREATE #993333; font-weight: bold;">TABLE FACE_MASKS
  #cc66cc;">2  #66cc66;">(CSXML_DOC #ff0000;">"SYS"#66cc66;">.#ff0000;">"XMLTYPE" #993333; font-weight: bold;">NOT #993333; font-weight: bold;">NULL ENABLE#66cc66;">)
  #cc66cc;">3  TABLESPACE #ff0000;">"USERS"
  #cc66cc;">4  XMLTYPE #993333; font-weight: bold;">COLUMN #ff0000;">"CSXML_DOC" STORE #993333; font-weight: bold;">AS SECUREFILE #993333; font-weight: bold;">BINARY XML;
 
#993333; font-weight: bold;">TABLE created#66cc66;">.
 
SQL#66cc66;">> #993333; font-weight: bold;">SET long #cc66cc;">1000000
SQL#66cc66;">> #993333; font-weight: bold;">SET pages #cc66cc;">5000
SQL#66cc66;">> #993333; font-weight: bold;">INSERT #993333; font-weight: bold;">INTO FACE_MASKS#66cc66;">(CSXML_DOC#66cc66;">) #993333; font-weight: bold;">VALUES#66cc66;">(XMLTYPE#66cc66;">(#ff0000;">'
  2   
  3   223
  4   001
  5   002
  6   003
  7   224
  8   005
  9   006
 10   002
 11   
 12   '#66cc66;">)#66cc66;">);
 
#cc66cc;">1 row created#66cc66;">.
 
SQL#66cc66;">> commit;
 
Commit complete#66cc66;">.
 
SQL#66cc66;">> #993333; font-weight: bold;">DROP #993333; font-weight: bold;">INDEX MY_UXI_FACEMASK_INDEX;
#993333; font-weight: bold;">DROP #993333; font-weight: bold;">INDEX MY_UXI_FACEMASK_INDEX
           #66cc66;">*
ERROR at line #cc66cc;">1:
ORA#66cc66;">-01418: specified #993333; font-weight: bold;">INDEX does #993333; font-weight: bold;">NOT exist
 
 
SQL#66cc66;">> #993333; font-weight: bold;">CREATE #993333; font-weight: bold;">INDEX MY_UXI_FACEMASK_INDEX
  #cc66cc;">2    #993333; font-weight: bold;">ON FACE_MASKS#66cc66;">(CSXML_DOC#66cc66;">)
  #cc66cc;">3    INDEXTYPE #993333; font-weight: bold;">IS XDB#66cc66;">.XMLIndex
  #cc66cc;">4  PARAMETERS#66cc66;">(#ff0000;">'PATH TABLE          FACE_PATHTABLE           (TABLESPACE SYSAUX NOLOGGING)
  5              PIKEY INDEX         FACE_PATHTABLE_PIKEY_IX  (TABLESPACE USERS PARALLEL 2)
  6              PATH ID INDEX       FACE_PATHTABLE_ID_IX     (TABLESPACE USERS)
  7              VALUE INDEX         FACE_PATHTABLE_VALUE_IX  (TABLESPACE USERS)
  8              ORDER KEY INDEX     FACE_PATHTABLE_KEY_IX    (TABLESPACE USERS)
  9              ASYNC (SYNC ALWAYS) STALE (FALSE)
 10             '#66cc66;">)
 #cc66cc;">11  ;
 
#993333; font-weight: bold;">INDEX created#66cc66;">.
 
SQL#66cc66;">> #993333; font-weight: bold;">SELECT object_name#66cc66;">, object_type #993333; font-weight: bold;">FROM user_objects
  #cc66cc;">2  #993333; font-weight: bold;">ORDER #993333; font-weight: bold;">BY created#66cc66;">, object_name;
 
OBJECT_NAME                                        OBJECT_TYPE
#808080; font-style: italic;">-------------------------------------------------- -------------------
FACE_MASKS                                         #993333; font-weight: bold;">TABLE
SYS_LOB0000074634C00002$$                          LOB
FACE_PATHTABLE                                     #993333; font-weight: bold;">TABLE
FACE_PATHTABLE_ID_IX                               #993333; font-weight: bold;">INDEX
FACE_PATHTABLE_KEY_IX                              #993333; font-weight: bold;">INDEX
FACE_PATHTABLE_PIKEY_IX                            #993333; font-weight: bold;">INDEX
MY_UXI_FACEMASK_INDEX                              #993333; font-weight: bold;">INDEX
FACE_PATHTABLE_VALUE_IX                            #993333; font-weight: bold;">INDEX
 
#cc66cc;">8 rows selected#66cc66;">.
 
SQL#66cc66;">> BEGIN
  #cc66cc;">2    DBMS_XMLINDEX#66cc66;">.registerParameter#66cc66;">(#ff0000;">'MY_XSI_GROUP_PARAMETER'
  #cc66cc;">3                                     #66cc66;">, #ff0000;">'ADD_GROUP GROUP MY_XSI_GROUP
  4                                       XMLTABLE fm_content_table_01
  5                                         '#ff0000;">'*'#ff0000;">'
  6                                         COLUMNS
  7                                           xmlresult      XMLTYPE PATH '#ff0000;">'/Face/FaceType'#ff0000;">' VIRTUAL
  8                                       XMLTABLE fm_content_table_02
  9                                         '#ff0000;">'*'#ff0000;">'
 10                                         PASSING xmlresult
 11                                         COLUMNS
 12                                           FaceTypeId_col number  PATH '#ff0000;">'FaceTypeId/text()'#ff0000;">'
 13                                   '#66cc66;">);
 #cc66cc;">14  END;
 #cc66cc;">15  #66cc66;">/ 
 
PL#66cc66;">/SQL procedure successfully completed#66cc66;">.
 
SQL#66cc66;">> #993333; font-weight: bold;">SELECT object_name#66cc66;">, object_type #993333; font-weight: bold;">FROM user_objects
  #cc66cc;">2  #993333; font-weight: bold;">ORDER #993333; font-weight: bold;">BY created#66cc66;">, object_name;
 
OBJECT_NAME                                        OBJECT_TYPE
#808080; font-style: italic;">-------------------------------------------------- -------------------
FACE_MASKS                                         #993333; font-weight: bold;">TABLE
SYS_LOB0000074634C00002$$                          LOB
FACE_PATHTABLE                                     #993333; font-weight: bold;">TABLE
FACE_PATHTABLE_ID_IX                               #993333; font-weight: bold;">INDEX
FACE_PATHTABLE_KEY_IX                              #993333; font-weight: bold;">INDEX
FACE_PATHTABLE_PIKEY_IX                            #993333; font-weight: bold;">INDEX
MY_UXI_FACEMASK_INDEX                              #993333; font-weight: bold;">INDEX
FACE_PATHTABLE_VALUE_IX                            #993333; font-weight: bold;">INDEX
 
#cc66cc;">8 rows selected#66cc66;">.
 
SQL#66cc66;">> #993333; font-weight: bold;">ALTER #993333; font-weight: bold;">INDEX MY_UXI_FACEMASK_INDEX PARAMETERS#66cc66;">(#ff0000;">'PARAM MY_XSI_GROUP_PARAMETER'#66cc66;">);
 
#993333; font-weight: bold;">INDEX altered#66cc66;">.
 
SQL#66cc66;">> #993333; font-weight: bold;">SELECT object_name#66cc66;">, object_type #993333; font-weight: bold;">FROM user_objects
  #cc66cc;">2  #993333; font-weight: bold;">ORDER #993333; font-weight: bold;">BY created#66cc66;">, object_name;
 
OBJECT_NAME                                        OBJECT_TYPE
#808080; font-style: italic;">-------------------------------------------------- -------------------
FACE_MASKS                                         #993333; font-weight: bold;">TABLE
SYS_LOB0000074634C00002$$                          LOB
FACE_PATHTABLE                                     #993333; font-weight: bold;">TABLE
FACE_PATHTABLE_ID_IX                               #993333; font-weight: bold;">INDEX
FACE_PATHTABLE_KEY_IX                              #993333; font-weight: bold;">INDEX
FACE_PATHTABLE_PIKEY_IX                            #993333; font-weight: bold;">INDEX
MY_UXI_FACEMASK_INDEX                              #993333; font-weight: bold;">INDEX
FACE_PATHTABLE_VALUE_IX                            #993333; font-weight: bold;">INDEX
FM_CONTENT_TABLE_01                                #993333; font-weight: bold;">TABLE
FM_CONTENT_TABLE_02                                #993333; font-weight: bold;">TABLE
SYS74637_74643_RID_IDX                             #993333; font-weight: bold;">INDEX
SYS74637_74646_PKY_IDX                             #993333; font-weight: bold;">INDEX
SYS74637_74646_RID_IDX                             #993333; font-weight: bold;">INDEX
SYS_C0011060                                       #993333; font-weight: bold;">INDEX
SYS_C0011062                                       #993333; font-weight: bold;">INDEX
 
#cc66cc;">15 rows selected#66cc66;">.
 
SQL#66cc66;">> BEGIN
  #cc66cc;">2    DBMS_XMLINDEX#66cc66;">.registerParameter#66cc66;">(#ff0000;">'MY_SECOND_XSI_GROUP_PARAMETER'
  #cc66cc;">3                                     #66cc66;">, #ff0000;">'ADD_GROUP GROUP MY_SECOND_XSI_GROUP
  4                                       XMLTABLE fm_content_01
  5                                         '#ff0000;">'*'#ff0000;">'
  6                                         COLUMNS
  7                                           xmlresult      XMLTYPE PATH '#ff0000;">'/Face/FaceType'#ff0000;">' VIRTUAL
  8                                       XMLTABLE fm_content_02
  9                                         '#ff0000;">'*'#ff0000;">'
 10                                         PASSING xmlresult
 11                                         COLUMNS
 12                                           FaceTypeId_col number  PATH '#ff0000;">'FaceTypeId/text()'#ff0000;">'
 13                                   '#66cc66;">);
 #cc66cc;">14  END;
 #cc66cc;">15  #66cc66;">/ 
 
 
PL#66cc66;">/SQL procedure successfully completed#66cc66;">.
 
SQL#66cc66;">>  #993333; font-weight: bold;">SELECT object_name#66cc66;">, object_type #993333; font-weight: bold;">FROM user_objects
  #cc66cc;">2  #993333; font-weight: bold;">ORDER #993333; font-weight: bold;">BY created#66cc66;">, object_name;
 
OBJECT_NAME                                        OBJECT_TYPE
#808080; font-style: italic;">-------------------------------------------------- -------------------
FACE_MASKS                                         #993333; font-weight: bold;">TABLE
SYS_LOB0000074634C00002$$                          LOB
FACE_PATHTABLE                                     #993333; font-weight: bold;">TABLE
FACE_PATHTABLE_ID_IX                               #993333; font-weight: bold;">INDEX
FACE_PATHTABLE_KEY_IX                              #993333; font-weight: bold;">INDEX
FACE_PATHTABLE_PIKEY_IX                            #993333; font-weight: bold;">INDEX
MY_UXI_FACEMASK_INDEX                              #993333; font-weight: bold;">INDEX
FACE_PATHTABLE_VALUE_IX                            #993333; font-weight: bold;">INDEX
FM_CONTENT_TABLE_01                                #993333; font-weight: bold;">TABLE
FM_CONTENT_TABLE_02                                #993333; font-weight: bold;">TABLE
SYS74637_74643_RID_IDX                             #993333; font-weight: bold;">INDEX
SYS74637_74646_PKY_IDX                             #993333; font-weight: bold;">INDEX
SYS74637_74646_RID_IDX                             #993333; font-weight: bold;">INDEX
SYS_C0011060                                       #993333; font-weight: bold;">INDEX
SYS_C0011062                                       #993333; font-weight: bold;">INDEX
 
#cc66cc;">15 rows selected#66cc66;">.
 
SQL#66cc66;">> #993333; font-weight: bold;">ALTER #993333; font-weight: bold;">INDEX MY_UXI_FACEMASK_INDEX PARAMETERS#66cc66;">(#ff0000;">'PARAM MY_SECOND_XSI_GROUP_PARAMETER'#66cc66;">);
 
#993333; font-weight: bold;">INDEX altered#66cc66;">.
 
SQL#66cc66;">> #993333; font-weight: bold;">SELECT object_name#66cc66;">, object_type #993333; font-weight: bold;">FROM user_objects
  #cc66cc;">2  #993333; font-weight: bold;">ORDER #993333; font-weight: bold;">BY created#66cc66;">, object_name;
 
OBJECT_NAME                                        OBJECT_TYPE
#808080; font-style: italic;">-------------------------------------------------- -------------------
FACE_MASKS                                         #993333; font-weight: bold;">TABLE
SYS_LOB0000074634C00002$$                          LOB
FACE_PATHTABLE                                     #993333; font-weight: bold;">TABLE
FACE_PATHTABLE_ID_IX                               #993333; font-weight: bold;">INDEX
FACE_PATHTABLE_KEY_IX                              #993333; font-weight: bold;">INDEX
FACE_PATHTABLE_PIKEY_IX                            #993333; font-weight: bold;">INDEX
MY_UXI_FACEMASK_INDEX                              #993333; font-weight: bold;">INDEX
FACE_PATHTABLE_VALUE_IX                            #993333; font-weight: bold;">INDEX
FM_CONTENT_TABLE_01                                #993333; font-weight: bold;">TABLE
FM_CONTENT_TABLE_02                                #993333; font-weight: bold;">TABLE
SYS74637_74643_RID_IDX                             #993333; font-weight: bold;">INDEX
SYS74637_74646_PKY_IDX                             #993333; font-weight: bold;">INDEX
SYS74637_74646_RID_IDX                             #993333; font-weight: bold;">INDEX
SYS_C0011060                                       #993333; font-weight: bold;">INDEX
SYS_C0011062                                       #993333; font-weight: bold;">INDEX
FM_CONTENT_01                                      #993333; font-weight: bold;">TABLE
FM_CONTENT_02                                      #993333; font-weight: bold;">TABLE
SYS74637_74650_RID_IDX                             #993333; font-weight: bold;">INDEX
SYS74637_74653_PKY_IDX                             #993333; font-weight: bold;">INDEX
SYS74637_74653_RID_IDX                             #993333; font-weight: bold;">INDEX
SYS_C0011064                                       #993333; font-weight: bold;">INDEX
SYS_C0011066                                       #993333; font-weight: bold;">INDEX
 
#cc66cc;">22 rows selected#66cc66;">.
 
SQL#66cc66;">> #993333; font-weight: bold;">DROP #993333; font-weight: bold;">INDEX MY_UXI_FACEMASK_INDEX;
 
#993333; font-weight: bold;">INDEX dropped#66cc66;">.
 
SQL#66cc66;">>  #993333; font-weight: bold;">SELECT object_name#66cc66;">, object_type #993333; font-weight: bold;">FROM user_objects
  #cc66cc;">2  #993333; font-weight: bold;">ORDER #993333; font-weight: bold;">BY created#66cc66;">, object_name;
 
OBJECT_NAME                                        OBJECT_TYPE
#808080; font-style: italic;">-------------------------------------------------- -------------------
FACE_MASKS                                         #993333; font-weight: bold;">TABLE
SYS_LOB0000074634C00002$$                          LOB

XMLIndex Maintenance

Say you made a mistake after the creation of the first structured XMLIndex then you could rectify this via:

SQL#66cc66;">> BEGIN
   #cc66cc;">2   DBMS_XMLINDEX#66cc66;">.registerParameter#66cc66;">(#ff0000;">'MY_XSI_GROUP_PARAMETER'#66cc66;">,#ff0000;">'DROP_GROUP GROUP MY_XSI_GROUP'#66cc66;">);
   #cc66cc;">3 END;
   #cc66cc;">4 #66cc66;">/ 
 
SQL#66cc66;">> #993333; font-weight: bold;">SELECT object_type#66cc66;">, object_name #993333; font-weight: bold;">FROM user_objects
  #cc66cc;">2  #993333; font-weight: bold;">ORDER #993333; font-weight: bold;">BY created#66cc66;">, object_type;
 
OBJECT_TYPE         OBJECT_NAME
#808080; font-style: italic;">------------------- --------------------------------------------------
LOB                 SYS_LOB0000074706C00002$$
#993333; font-weight: bold;">TABLE               FACE_MASKS
#993333; font-weight: bold;">INDEX               MY_UXI_FACEMASK_INDEX
#993333; font-weight: bold;">INDEX               FACE_PATHTABLE_ID_IX
#993333; font-weight: bold;">INDEX               FACE_PATHTABLE_KEY_IX
#993333; font-weight: bold;">INDEX               FACE_PATHTABLE_PIKEY_IX
#993333; font-weight: bold;">TABLE               FACE_PATHTABLE
#993333; font-weight: bold;">INDEX               FACE_PATHTABLE_VALUE_IX
#993333; font-weight: bold;">INDEX               SYS_C0011086
#993333; font-weight: bold;">INDEX               SYS74709_74715_RID_IDX
#993333; font-weight: bold;">INDEX               SYS_C0011088
#993333; font-weight: bold;">INDEX               SYS74709_74718_RID_IDX
#993333; font-weight: bold;">INDEX               SYS74709_74718_PKY_IDX
#993333; font-weight: bold;">TABLE               FM_CONTENT_TABLE_02
#993333; font-weight: bold;">TABLE               FM_CONTENT_TABLE_01
 
#cc66cc;">15 rows selected#66cc66;">.
 
SQL#66cc66;">> #993333; font-weight: bold;">ALTER #993333; font-weight: bold;">INDEX MY_UXI_FACEMASK_INDEX PARAMETERS#66cc66;">(#ff0000;">'PARAM MY_XSI_GROUP_PARAMETER'#66cc66;">);
 
#993333; font-weight: bold;">INDEX altered#66cc66;">.
 
SQL#66cc66;">> #993333; font-weight: bold;">SELECT object_type#66cc66;">, object_name #993333; font-weight: bold;">FROM user_objects
  #cc66cc;">2  #993333; font-weight: bold;">ORDER #993333; font-weight: bold;">BY created#66cc66;">, object_type;
 
OBJECT_TYPE         OBJECT_NAME
#808080; font-style: italic;">------------------- --------------------------------------------------
LOB                 SYS_LOB0000074706C00002$$
#993333; font-weight: bold;">TABLE               FACE_MASKS
#993333; font-weight: bold;">INDEX               FACE_PATHTABLE_KEY_IX
#993333; font-weight: bold;">INDEX               FACE_PATHTABLE_PIKEY_IX
#993333; font-weight: bold;">INDEX               FACE_PATHTABLE_ID_IX
#993333; font-weight: bold;">INDEX               MY_UXI_FACEMASK_INDEX
#993333; font-weight: bold;">TABLE               FACE_PATHTABLE
#993333; font-weight: bold;">INDEX               FACE_PATHTABLE_VALUE_IX
 
#cc66cc;">8 rows selected#66cc66;">.

The optimizer (CBO) is picky regarding what you select… In standard “SQL” the CBO would rewrite the varchar to number to allow the statement but now it follows the correct rules (varchar is not equal to a number)… The following show you the effect of some choices you could make choosing the datatype for “COLUMNS” in you XMLTABLE statement, using it to create a structured XMLIndex. Also it demonstrates how to add an XMLIndex in a existing structure by adding to the groups using the ADD_GROUP syntax.

 
SQL#66cc66;">> BEGIN
  #cc66cc;">2    DBMS_XMLINDEX#66cc66;">.registerParameter#66cc66;">(#ff0000;">'MY_XSI_NEW_PARAMETER'
  #cc66cc;">3                                     #66cc66;">, #ff0000;">'ADD_GROUP GROUP MY_XSI_GROUP_NEW
  4                                       XMLTABLE fm_content_table_01
  5                                         '#ff0000;">'*'#ff0000;">'
  6                                         COLUMNS
  7                                           xmlresult      XMLTYPE PATH '#ff0000;">'/Face/FaceType'#ff0000;">' VIRTUAL
  8                                       XMLTABLE fm_content_table_02
  9                                         '#ff0000;">'*'#ff0000;">'
 10                                         PASSING xmlresult
 11                                         COLUMNS
 12                                           FaceTypeId_COLUMN varchar2(3)  PATH '#ff0000;">'FaceTypeId/text()'#ff0000;">'
 13                                   '#66cc66;">);
 #cc66cc;">14  END;
 #cc66cc;">15  #66cc66;">/ 
 
PL#66cc66;">/SQL procedure successfully completed#66cc66;">.
 
SQL#66cc66;">> #993333; font-weight: bold;">ALTER #993333; font-weight: bold;">INDEX MY_UXI_FACEMASK_INDEX PARAMETERS#66cc66;">(#ff0000;">'PARAM MY_XSI_NEW_PARAMETER'#66cc66;">);
 
#993333; font-weight: bold;">INDEX altered#66cc66;">.
 
SQL#66cc66;">> #993333; font-weight: bold;">SET autotrace #993333; font-weight: bold;">ON pages #cc66cc;">5000 #993333; font-weight: bold;">LINES #cc66cc;">150
 
SQL#66cc66;">> #993333; font-weight: bold;">SELECT vt#66cc66;">.FaceTypeId_col
  #cc66cc;">2  #993333; font-weight: bold;">FROM   FACE_MASKS fm
  #cc66cc;">3  #66cc66;">,      XMLTABLE#66cc66;">(#ff0000;">'*'
  #cc66cc;">4                  PASSING fm#66cc66;">.csxml_doc
  #cc66cc;">5                   #993333; font-weight: bold;">COLUMNS
  #cc66cc;">6                     xmlresult      XMLTYPE PATH #ff0000;">'/Face/FaceType'
  #cc66cc;">7                 #66cc66;">) xt
  #cc66cc;">8  #66cc66;">,      XMLTABLE#66cc66;">(#ff0000;">'*'
  #cc66cc;">9                   PASSING xt#66cc66;">.xmlresult
 #cc66cc;">10                   #993333; font-weight: bold;">COLUMNS
 #cc66cc;">11                     FaceTypeId_col number PATH #ff0000;">'FaceTypeId/text()'
 #cc66cc;">12                 #66cc66;">) vt
 #cc66cc;">13  ;
 
no rows selected
 
 
Execution Plan
#808080; font-style: italic;">----------------------------------------------------------
Plan hash value: #cc66cc;">1036555850
 
#808080; font-style: italic;">-----------------------------------------------------------------------------------------------
#66cc66;">| Id  #66cc66;">| Operation                    #66cc66;">| Name           #66cc66;">| Rows  #66cc66;">| Bytes #66cc66;">| Cost #66cc66;">(%CPU#66cc66;">)#66cc66;">| Time     #66cc66;">|
#808080; font-style: italic;">-----------------------------------------------------------------------------------------------
#66cc66;">|   #cc66cc;">0 #66cc66;">| #993333; font-weight: bold;">SELECT STATEMENT             #66cc66;">|                #66cc66;">|     #cc66cc;">1 #66cc66;">|  #cc66cc;">1534 #66cc66;">|     #cc66cc;">0   #66cc66;">(#cc66cc;">0#66cc66;">)#66cc66;">|          #66cc66;">|
#66cc66;">|*  #cc66cc;">1 #66cc66;">|  FILTER                      #66cc66;">|                #66cc66;">|       #66cc66;">|       #66cc66;">|            #66cc66;">|          #66cc66;">|
#66cc66;">|   #cc66cc;">2 #66cc66;">|   NESTED LOOPS               #66cc66;">|                #66cc66;">|     #cc66cc;">1 #66cc66;">|  #cc66cc;">1534 #66cc66;">|     #cc66cc;">4   #66cc66;">(#cc66cc;">0#66cc66;">)#66cc66;">| 00:00:01 #66cc66;">|
#66cc66;">|*  #cc66cc;">3 #66cc66;">|    #993333; font-weight: bold;">TABLE ACCESS #993333; font-weight: bold;">FULL         #66cc66;">| FACE_PATHTABLE #66cc66;">|     #cc66cc;">1 #66cc66;">|  #cc66cc;">1522 #66cc66;">|     #cc66cc;">3   #66cc66;">(#cc66cc;">0#66cc66;">)#66cc66;">| 00:00:01 #66cc66;">|
#66cc66;">|   #cc66cc;">4 #66cc66;">|    #993333; font-weight: bold;">TABLE ACCESS #993333; font-weight: bold;">BY USER ROWID#66cc66;">| FACE_MASKS     #66cc66;">|     #cc66cc;">1 #66cc66;">|    #cc66cc;">12 #66cc66;">|     #cc66cc;">1   #66cc66;">(#cc66cc;">0#66cc66;">)#66cc66;">| 00:00:01 #66cc66;">|
#808080; font-style: italic;">-----------------------------------------------------------------------------------------------
 
Predicate Information #66cc66;">(#993333; font-weight: bold;">IDENTIFIED #993333; font-weight: bold;">BY operation id#66cc66;">):
#808080; font-style: italic;">---------------------------------------------------
 
   #cc66cc;">1 #66cc66;">- filter#66cc66;">(#993333; font-weight: bold;">NULL #993333; font-weight: bold;">IS #993333; font-weight: bold;">NOT #993333; font-weight: bold;">NULL#66cc66;">)
   #cc66cc;">3 #66cc66;">- filter#66cc66;">(SYS_ORDERKEY_DEPTH#66cc66;">(#ff0000;">"SYS_P0"#66cc66;">.#ff0000;">"ORDER_KEY"#66cc66;">)#66cc66;">=#cc66cc;">1 #993333; font-weight: bold;">AND
              SYS_PATHID_IS_ATTR#66cc66;">(#ff0000;">"SYS_P0"#66cc66;">.#ff0000;">"PATHID"#66cc66;">)#66cc66;">=#cc66cc;">0 #993333; font-weight: bold;">AND SYS_XMLI_LOC_ISNODE#66cc66;">(#ff0000;">"SYS_P0"#66cc66;">.#ff0000;">"LOCATOR"#66cc66;">)#66cc66;">=#cc66cc;">1#66cc66;">)
 
Note
#808080; font-style: italic;">-----
   #66cc66;">- dynamic sampling used #993333; font-weight: bold;">FOR this statement #66cc66;">(level#66cc66;">=#cc66cc;">2#66cc66;">)
 
 
Statistics
#808080; font-style: italic;">----------------------------------------------------------
        #cc66cc;">760  recursive calls
          #cc66cc;">0  db block gets
        #cc66cc;">569  consistent gets
          #cc66cc;">0  physical reads
          #cc66cc;">0  redo size
        #cc66cc;">294  bytes sent via SQL#66cc66;">*Net #993333; font-weight: bold;">TO client
        #cc66cc;">405  bytes received via SQL#66cc66;">*Net #993333; font-weight: bold;">FROM client
          #cc66cc;">1  SQL#66cc66;">*Net roundtrips #993333; font-weight: bold;">TO#66cc66;">/#993333; font-weight: bold;">FROM client
         #cc66cc;">61  sorts #66cc66;">(memory#66cc66;">)
          #cc66cc;">0  sorts #66cc66;">(disk#66cc66;">)
          #cc66cc;">0  rows processed
 
SQL#66cc66;">> #993333; font-weight: bold;">SELECT vt#66cc66;">.FaceTypeId_col
  #cc66cc;">2  #993333; font-weight: bold;">FROM   FACE_MASKS fm
  #cc66cc;">3  #66cc66;">,      XMLTABLE#66cc66;">(#ff0000;">'*'
  #cc66cc;">4                  PASSING fm#66cc66;">.csxml_doc
  #cc66cc;">5                   #993333; font-weight: bold;">COLUMNS
  #cc66cc;">6                     xmlresult      XMLTYPE PATH #ff0000;">'/Face/FaceType'
  #cc66cc;">7                 #66cc66;">) xt
  #cc66cc;">8  #66cc66;">,      XMLTABLE#66cc66;">(#ff0000;">'*'
  #cc66cc;">9                   PASSING xt#66cc66;">.xmlresult
 #cc66cc;">10                   #993333; font-weight: bold;">COLUMNS
 #cc66cc;">11                     FaceTypeId_col varchar2#66cc66;">(#cc66cc;">3#66cc66;">) PATH #ff0000;">'FaceTypeId/text()'
 #cc66cc;">12                 #66cc66;">) vt
 #cc66cc;">13  ;
 
 
FAC
#808080; font-style: italic;">---
001
002
003
005
006
002
 
#cc66cc;">6 rows selected#66cc66;">.
 
 
Execution Plan
#808080; font-style: italic;">----------------------------------------------------------
Plan hash value: #cc66cc;">521366288
 
#808080; font-style: italic;">---------------------------------------------------------------------------------------------------------
#66cc66;">| Id  #66cc66;">| Operation                      #66cc66;">| Name                   #66cc66;">| Rows  #66cc66;">| Bytes #66cc66;">| Cost #66cc66;">(%CPU#66cc66;">)#66cc66;">| Time     #66cc66;">|
#808080; font-style: italic;">---------------------------------------------------------------------------------------------------------
#66cc66;">|   #cc66cc;">0 #66cc66;">| #993333; font-weight: bold;">SELECT STATEMENT               #66cc66;">|                        #66cc66;">|     #cc66cc;">2 #66cc66;">|  #cc66cc;">2062 #66cc66;">|     #cc66cc;">3   #66cc66;">(#cc66cc;">0#66cc66;">)#66cc66;">| 00:00:01 #66cc66;">|
#66cc66;">|   #cc66cc;">1 #66cc66;">|  NESTED LOOPS                  #66cc66;">|                        #66cc66;">|       #66cc66;">|       #66cc66;">|            #66cc66;">|          #66cc66;">|
#66cc66;">|   #cc66cc;">2 #66cc66;">|   NESTED LOOPS                 #66cc66;">|                        #66cc66;">|     #cc66cc;">2 #66cc66;">|  #cc66cc;">2062 #66cc66;">|     #cc66cc;">3   #66cc66;">(#cc66cc;">0#66cc66;">)#66cc66;">| 00:00:01 #66cc66;">|
#66cc66;">|   #cc66cc;">3 #66cc66;">|    NESTED LOOPS                #66cc66;">|                        #66cc66;">|     #cc66cc;">1 #66cc66;">|   #cc66cc;">526 #66cc66;">|     #cc66cc;">3   #66cc66;">(#cc66cc;">0#66cc66;">)#66cc66;">| 00:00:01 #66cc66;">|
#66cc66;">|   #cc66cc;">4 #66cc66;">|     #993333; font-weight: bold;">TABLE ACCESS #993333; font-weight: bold;">FULL          #66cc66;">| FACE_MASKS             #66cc66;">|     #cc66cc;">1 #66cc66;">|    #cc66cc;">12 #66cc66;">|     #cc66cc;">3   #66cc66;">(#cc66cc;">0#66cc66;">)#66cc66;">| 00:00:01 #66cc66;">|
#66cc66;">|   #cc66cc;">5 #66cc66;">|     #993333; font-weight: bold;">TABLE ACCESS #993333; font-weight: bold;">BY #993333; font-weight: bold;">INDEX ROWID#66cc66;">| FM_CONTENT_TABLE_01    #66cc66;">|     #cc66cc;">1 #66cc66;">|   #cc66cc;">514 #66cc66;">|     #cc66cc;">0   #66cc66;">(#cc66cc;">0#66cc66;">)#66cc66;">| 00:00:01 #66cc66;">|
#66cc66;">|*  #cc66cc;">6 #66cc66;">|      #993333; font-weight: bold;">INDEX RANGE SCAN          #66cc66;">| SYS74709_74722_RID_IDX #66cc66;">|     #cc66cc;">1 #66cc66;">|       #66cc66;">|     #cc66cc;">0   #66cc66;">(#cc66cc;">0#66cc66;">)#66cc66;">| 00:00:01 #66cc66;">|
#66cc66;">|*  #cc66cc;">7 #66cc66;">|    #993333; font-weight: bold;">INDEX RANGE SCAN            #66cc66;">| SYS74709_74725_PKY_IDX #66cc66;">|     #cc66cc;">6 #66cc66;">|       #66cc66;">|     #cc66cc;">0   #66cc66;">(#cc66cc;">0#66cc66;">)#66cc66;">| 00:00:01 #66cc66;">|
#66cc66;">|   #cc66cc;">8 #66cc66;">|   #993333; font-weight: bold;">TABLE ACCESS #993333; font-weight: bold;">BY #993333; font-weight: bold;">INDEX ROWID  #66cc66;">| FM_CONTENT_TABLE_02    #66cc66;">|     #cc66cc;">6 #66cc66;">|  #cc66cc;">3030 #66cc66;">|     #cc66cc;">0   #66cc66;">(#cc66cc;">0#66cc66;">)#66cc66;">| 00:00:01 #66cc66;">|
#808080; font-style: italic;">---------------------------------------------------------------------------------------------------------
 
Predicate Information #66cc66;">(#993333; font-weight: bold;">IDENTIFIED #993333; font-weight: bold;">BY operation id#66cc66;">):
#808080; font-style: italic;">---------------------------------------------------
 
   #cc66cc;">6 #66cc66;">- access#66cc66;">(#ff0000;">"FM"#66cc66;">.ROWID#66cc66;">=#ff0000;">"SYS_ALIAS_2"#66cc66;">.#ff0000;">"RID"#66cc66;">)
   #cc66cc;">7 #66cc66;">- access#66cc66;">(#ff0000;">"SYS_ALIAS_2"#66cc66;">.#ff0000;">"KEY"#66cc66;">=#ff0000;">"SYS_ALIAS_3"#66cc66;">.#ff0000;">"PKEY"#66cc66;">)
 
Note
#808080; font-style: italic;">-----
   #66cc66;">- dynamic sampling used #993333; font-weight: bold;">FOR this statement #66cc66;">(level#66cc66;">=#cc66cc;">2#66cc66;">)
 
 
Statistics
#808080; font-style: italic;">----------------------------------------------------------
         #cc66cc;">72  recursive calls
          #cc66cc;">0  db block gets
        #cc66cc;">410  consistent gets
          #cc66cc;">0  physical reads
          #cc66cc;">0  redo size
        #cc66cc;">504  bytes sent via SQL#66cc66;">*Net #993333; font-weight: bold;">TO client
        #cc66cc;">416  bytes received via SQL#66cc66;">*Net #993333; font-weight: bold;">FROM client
          #cc66cc;">2  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;">6  rows processed

Structured XMLIndex structures ONLY

One of the things that took me initially some time to figure out, and wasn’t properly described in the (beta) manuals, howto add structured xmlindex structures. So hereby an example with only structured XMLIndexes…

SQL#66cc66;">> #993333; font-weight: bold;">CREATE user otn #993333; font-weight: bold;">IDENTIFIED #993333; font-weight: bold;">BY otn account #993333; font-weight: bold;">UNLOCK;
 
User created#66cc66;">.
 
SQL#66cc66;">> #993333; font-weight: bold;">GRANT dba#66cc66;">, xdbadmin #993333; font-weight: bold;">TO otn;
 
#993333; font-weight: bold;">GRANT succeeded#66cc66;">.
 
SQL#66cc66;">> #993333; font-weight: bold;">SELECT #66cc66;">* #993333; font-weight: bold;">FROM v$version;
 
BANNER
#808080; font-style: italic;">--------------------------------------------------------------------------------
Oracle #993333; font-weight: bold;">DATABASE 11g Enterprise Edition Release 11#66cc66;">.2#66cc66;">.0#66cc66;">.1#66cc66;">.0 #66cc66;">- Production
PL#66cc66;">/SQL Release 11#66cc66;">.2#66cc66;">.0#66cc66;">.1#66cc66;">.0 #66cc66;">- Production
CORE    11#66cc66;">.2#66cc66;">.0#66cc66;">.1#66cc66;">.0      Production
TNS #993333; font-weight: bold;">FOR Linux: Version 11#66cc66;">.2#66cc66;">.0#66cc66;">.1#66cc66;">.0 #66cc66;">- Production
NLSRTL Version 11#66cc66;">.2#66cc66;">.0#66cc66;">.1#66cc66;">.0 #66cc66;">- Production
 
SQL#66cc66;">> #993333; font-weight: bold;">CREATE #993333; font-weight: bold;">TABLE FACE_MASKS
  #cc66cc;">2  #66cc66;">(CSXML_DOC #ff0000;">"SYS"#66cc66;">.#ff0000;">"XMLTYPE" #993333; font-weight: bold;">NOT #993333; font-weight: bold;">NULL ENABLE#66cc66;">)
  #cc66cc;">3  TABLESPACE #ff0000;">"USERS"
  #cc66cc;">4  XMLTYPE #993333; font-weight: bold;">COLUMN #ff0000;">"CSXML_DOC" STORE #993333; font-weight: bold;">AS SECUREFILE #993333; font-weight: bold;">BINARY XML;
 
#993333; font-weight: bold;">TABLE created#66cc66;">.
 
SQL#66cc66;">> #993333; font-weight: bold;">SET long #cc66cc;">1000000
SQL#66cc66;">> #993333; font-weight: bold;">SET pages #cc66cc;">5000
SQL#66cc66;">> #993333; font-weight: bold;">INSERT #993333; font-weight: bold;">INTO FACE_MASKS#66cc66;">(CSXML_DOC#66cc66;">) #993333; font-weight: bold;">VALUES#66cc66;">(XMLTYPE#66cc66;">(#ff0000;">'
  2   
  3   223
  4   001
  5   002
  6   003
  7   224
  8   005
  9   006
 10   002
 11   
 12   '#66cc66;">)#66cc66;">);
 
#cc66cc;">1 row created#66cc66;">.
 
SQL#66cc66;">> commit;
 
Commit complete#66cc66;">.
 
SQL#66cc66;">> #993333; font-weight: bold;">SELECT object_type#66cc66;">, object_name #993333; font-weight: bold;">FROM user_objects
  #cc66cc;">2  #993333; font-weight: bold;">ORDER #993333; font-weight: bold;">BY created#66cc66;">, object_type;
 
OBJECT_TYPE         OBJECT_NAME
#808080; font-style: italic;">------------------- --------------------------------------------------
LOB                 SYS_LOB0000074729C00002$$
#993333; font-weight: bold;">TABLE               FACE_MASKS
 
SQL#66cc66;">> #993333; font-weight: bold;">CREATE #993333; font-weight: bold;">INDEX structured_xmlindexes_only
  #cc66cc;">2    #993333; font-weight: bold;">ON FACE_MASKS#66cc66;">(CSXML_DOC#66cc66;">)
  #cc66cc;">3    INDEXTYPE #993333; font-weight: bold;">IS XDB#66cc66;">.XMLIndex
  #cc66cc;">4  PARAMETERS #66cc66;">(#ff0000;">'GROUP MY_XSI_FIRST_GROUP
  5               XMLTABLE fm_content_table_01
  6                 '#ff0000;">'*'#ff0000;">'
  7                 COLUMNS
  8                   xmlresult      XMLTYPE PATH '#ff0000;">'/Face/FaceType'#ff0000;">' VIRTUAL
  9               XMLTABLE fm_content_table_02
 10                 '#ff0000;">'*'#ff0000;">'
 11                 PASSING xmlresult
 12                 COLUMNS
 13                   FaceTypeId_col number  PATH '#ff0000;">'FaceTypeId/text()'#ff0000;">'
 14              '#66cc66;">);
 
#993333; font-weight: bold;">INDEX created#66cc66;">.
 
SQL#66cc66;">> #993333; font-weight: bold;">SELECT object_type#66cc66;">, object_name #993333; font-weight: bold;">FROM user_objects
  #cc66cc;">2   #993333; font-weight: bold;">ORDER #993333; font-weight: bold;">BY created#66cc66;">, object_type;
 
OBJECT_TYPE         OBJECT_NAME
#808080; font-style: italic;">------------------- --------------------------------------------------
LOB                 SYS_LOB0000074729C00002$$
#993333; font-weight: bold;">TABLE               FACE_MASKS
#993333; font-weight: bold;">INDEX               STRUCTURED_XMLINDEXES_ONLY
#993333; font-weight: bold;">INDEX               SYS_C0011099
#993333; font-weight: bold;">INDEX               SYS74741_74745_RID_IDX
#993333; font-weight: bold;">INDEX               SYS74741_74745_PKY_IDX
#993333; font-weight: bold;">INDEX               SYS74741_74742_RID_IDX
#993333; font-weight: bold;">INDEX               SYS_C0011101
#993333; font-weight: bold;">TABLE               FM_CONTENT_TABLE_02
#993333; font-weight: bold;">TABLE               FM_CONTENT_TABLE_01
 
#cc66cc;">10 rows selected#66cc66;">.
 
SQL#66cc66;">> #993333; font-weight: bold;">ALTER #993333; font-weight: bold;">INDEX structured_xmlindexes_only
  #cc66cc;">2  parameters #66cc66;">(#ff0000;">'ADD_GROUP GROUP MY_XSI_SECOND_GROUP
  3                         XMLTABLE fm_content_table_03
  4                         '#ff0000;">'*'#ff0000;">'
  5                         COLUMNS
  6                           xmlresult      XMLTYPE PATH '#ff0000;">'/Face/FaceType'#ff0000;">' VIRTUAL
  7                         XMLTABLE fm_content_table_04
  8                         '#ff0000;">'*'#ff0000;">'
  9                         PASSING xmlresult
 10                         COLUMNS
 11                           NEW_COLUMN_NAME_HERE number  PATH '#ff0000;">'FaceTypeId/text()'#ff0000;">'
 12                         '#66cc66;">);
 
#993333; font-weight: bold;">INDEX altered#66cc66;">.
 
SQL#66cc66;">> #993333; font-weight: bold;">SELECT object_type#66cc66;">, object_name #993333; font-weight: bold;">FROM user_objects
  #cc66cc;">2   #993333; font-weight: bold;">ORDER #993333; font-weight: bold;">BY created#66cc66;">, object_type;
 
OBJECT_TYPE         OBJECT_NAME
#808080; font-style: italic;">------------------- --------------------------------------------------
LOB                 SYS_LOB0000074729C00002$$
#993333; font-weight: bold;">TABLE               FACE_MASKS
#993333; font-weight: bold;">INDEX               SYS74741_74745_RID_IDX
#993333; font-weight: bold;">INDEX               STRUCTURED_XMLINDEXES_ONLY
#993333; font-weight: bold;">INDEX               SYS_C0011099
#993333; font-weight: bold;">INDEX               SYS74741_74742_RID_IDX
#993333; font-weight: bold;">INDEX               SYS_C0011101
#993333; font-weight: bold;">INDEX               SYS74741_74745_PKY_IDX
#993333; font-weight: bold;">TABLE               FM_CONTENT_TABLE_02
#993333; font-weight: bold;">TABLE               FM_CONTENT_TABLE_01
#993333; font-weight: bold;">INDEX               SYS74741_74752_RID_IDX
#993333; font-weight: bold;">INDEX               SYS74741_74752_PKY_IDX
#993333; font-weight: bold;">INDEX               SYS_C0011105
#993333; font-weight: bold;">INDEX               SYS_C0011103
#993333; font-weight: bold;">INDEX               SYS74741_74749_RID_IDX
#993333; font-weight: bold;">TABLE               FM_CONTENT_TABLE_04
#993333; font-weight: bold;">TABLE               FM_CONTENT_TABLE_03
 
#cc66cc;">17 rows selected#66cc66;">.
 
SQL#66cc66;">> #993333; font-weight: bold;">SELECT object_type#66cc66;">, object_name #993333; font-weight: bold;">FROM user_objects
  #cc66cc;">2  #993333; font-weight: bold;">ORDER #993333; font-weight: bold;">BY  object_type;
 
OBJECT_TYPE         OBJECT_NAME
#808080; font-style: italic;">------------------- --------------------------------------------------
#993333; font-weight: bold;">INDEX               STRUCTURED_XMLINDEXES_ONLY
#993333; font-weight: bold;">INDEX               SYS74741_74752_RID_IDX
#993333; font-weight: bold;">INDEX               SYS_C0011105
#993333; font-weight: bold;">INDEX               SYS74741_74749_RID_IDX
#993333; font-weight: bold;">INDEX               SYS_C0011103
#993333; font-weight: bold;">INDEX               SYS74741_74745_PKY_IDX
#993333; font-weight: bold;">INDEX               SYS74741_74752_PKY_IDX
#993333; font-weight: bold;">INDEX               SYS_C0011099
#993333; font-weight: bold;">INDEX               SYS74741_74742_RID_IDX
#993333; font-weight: bold;">INDEX               SYS_C0011101
#993333; font-weight: bold;">INDEX               SYS74741_74745_RID_IDX
LOB                 SYS_LOB0000074729C00002$$
#993333; font-weight: bold;">TABLE               FM_CONTENT_TABLE_03
#993333; font-weight: bold;">TABLE               FM_CONTENT_TABLE_04
#993333; font-weight: bold;">TABLE               FM_CONTENT_TABLE_01
#993333; font-weight: bold;">TABLE               FACE_MASKS
#993333; font-weight: bold;">TABLE               FM_CONTENT_TABLE_02
 
#cc66cc;">17 rows selected#66cc66;">.

The post is based on a OTN thread were I gave some examples on how to build a structured XMLIndex or multiple structured XMLIndex on a binary XML column. The examples are easily rewritten for an XMLType table storage solution using the virtual column OBJECT_VALUE instead of using the xmltype column name.

HTH

:-)

Related Posts

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

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

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

SQL#66cc66;">> #993333; font-weight: bold;">CREATE user otn #993333; font-weight: bold;">IDENTIFIED #993333; font-weight: bold;">BY otn account #993333; font-weight: bold;">UNLOCK;
 
User created#66cc66;">.
 
SQL#66cc66;">> #993333; font-weight: bold;">GRANT dba#66cc66;">, xdbadmin #993333; font-weight: bold;">TO otn;
 
#993333; font-weight: bold;">GRANT succeeded#66cc66;">.
 
SQL#66cc66;">> conn otn#66cc66;">/otn
Connected#66cc66;">.
 
SQL#66cc66;">> #993333; font-weight: bold;">SELECT #66cc66;">* #993333; font-weight: bold;">FROM v$version;
 
BANNER
#808080; font-style: italic;">--------------------------------------------------------------------------------
Oracle #993333; font-weight: bold;">DATABASE 11g Enterprise Edition Release 11#66cc66;">.2#66cc66;">.0#66cc66;">.1#66cc66;">.0 #66cc66;">- Production
PL#66cc66;">/SQL Release 11#66cc66;">.2#66cc66;">.0#66cc66;">.1#66cc66;">.0 #66cc66;">- Production
CORE    11#66cc66;">.2#66cc66;">.0#66cc66;">.1#66cc66;">.0      Production
TNS #993333; font-weight: bold;">FOR Linux: Version 11#66cc66;">.2#66cc66;">.0#66cc66;">.1#66cc66;">.0 #66cc66;">- Production
NLSRTL Version 11#66cc66;">.2#66cc66;">.0#66cc66;">.1#66cc66;">.0 #66cc66;">- Production
 
SQL#66cc66;">> #993333; font-weight: bold;">CREATE #993333; font-weight: bold;">TABLE FACE_MASKS
  #cc66cc;">2  #66cc66;">(CSXML_DOC #ff0000;">"SYS"#66cc66;">.#ff0000;">"XMLTYPE" #993333; font-weight: bold;">NOT #993333; font-weight: bold;">NULL ENABLE#66cc66;">)
  #cc66cc;">3  TABLESPACE #ff0000;">"USERS"
  #cc66cc;">4  XMLTYPE #993333; font-weight: bold;">COLUMN #ff0000;">"CSXML_DOC" STORE #993333; font-weight: bold;">AS SECUREFILE #993333; font-weight: bold;">BINARY XML;
 
#993333; font-weight: bold;">TABLE created#66cc66;">.
 
SQL#66cc66;">> #993333; font-weight: bold;">SET long #cc66cc;">1000000
SQL#66cc66;">> #993333; font-weight: bold;">SET pages #cc66cc;">5000
 
SQL#66cc66;">> #808080; font-style: italic;">--Populate data
SQL#66cc66;">>INSERT #993333; font-weight: bold;">INTO FACE_MASKS#66cc66;">(CSXML_DOC#66cc66;">) #993333; font-weight: bold;">VALUES#66cc66;">(XMLTYPE#66cc66;">(#ff0000;">'
  2  
  3  223
  4  001
  5  002
  6  003
  7  224
  8  005
  9  006
 10  002
 11  
 12  '#66cc66;">)#66cc66;">);
 
#cc66cc;">1 row created#66cc66;">.
 
SQL#66cc66;">> commit;
 
Commit complete#66cc66;">.
 
#808080; font-style: italic;">-- The following is causing the problem described by you...
 
SQL#66cc66;">> #993333; font-weight: bold;">SELECT xt#66cc66;">.FaceTypeId
  #cc66cc;">2  #993333; font-weight: bold;">FROM   FACE_MASKS fm
  #cc66cc;">3  #66cc66;">,      XMLTABLE#66cc66;">(#ff0000;">'*'
  #cc66cc;">4                  PASSING fm#66cc66;">.csxml_doc
  #cc66cc;">5                  #993333; font-weight: bold;">COLUMNS
  #cc66cc;">6                    FaceTypeId number PATH #ff0000;">'/Face/FaceType/FaceTypeId'
  #cc66cc;">7                 #66cc66;">) xt
  #cc66cc;">8  ;
 
#993333; font-weight: bold;">SELECT xt#66cc66;">.FaceTypeId
#66cc66;">*
ERROR at line #cc66cc;">1:
ORA#66cc66;">-#cc66cc;">19279: XPTY0004 #66cc66;">- XQuery dynamic type mismatch: expected singleton sequence
#66cc66;">- got multi#66cc66;">-item sequence
 
 
#808080; font-style: italic;">-- So one step "back" - the resultset in xml would be
 SQL#66cc66;">> #993333; font-weight: bold;">SELECT xt#66cc66;">.XMLRESULT
   #cc66cc;">2  #993333; font-weight: bold;">FROM   FACE_MASKS fm
   #cc66cc;">3  #66cc66;">,      XMLTABLE#66cc66;">(#ff0000;">'*'
   #cc66cc;">4                  PASSING fm#66cc66;">.csxml_doc
   #cc66cc;">5                  #993333; font-weight: bold;">COLUMNS
   #cc66cc;">6                    XMLRESULT XMLTYPE PATH #ff0000;">'/Face/FaceType'
   #cc66cc;">7                 #66cc66;">) xt
   #cc66cc;">8  ;
 
XMLRESULT
#808080; font-style: italic;">---------------------------------
#66cc66;"><facetype#66cc66;">>
  #66cc66;"><facetypeid#66cc66;">>001#66cc66;">facetypeid#66cc66;">>
#66cc66;">facetype#66cc66;">>
#66cc66;"><facetype#66cc66;">>
  #66cc66;"><facetypeid#66cc66;">>002#66cc66;">facetypeid#66cc66;">>
#66cc66;">facetype#66cc66;">>
#66cc66;"><facetype#66cc66;">>
  #66cc66;"><facetypeid#66cc66;">>003#66cc66;">facetypeid#66cc66;">>
#66cc66;">facetype#66cc66;">>
#66cc66;"><facetype#66cc66;">>
  #66cc66;"><facetypeid#66cc66;">>005#66cc66;">facetypeid#66cc66;">>
#66cc66;">facetype#66cc66;">>
#66cc66;"><facetype#66cc66;">>
  #66cc66;"><facetypeid#66cc66;">>006#66cc66;">facetypeid#66cc66;">>
#66cc66;">facetype#66cc66;">>
#66cc66;"><facetype#66cc66;">>
  #66cc66;"><facetypeid#66cc66;">>002#66cc66;">facetypeid#66cc66;">>
#66cc66;">facetype#66cc66;">>
 
 
#808080; font-style: italic;">-- This resultset I now can pass (later while using the SXI) on via...
 
#808080; font-style: italic;">-- Test XMLTABLE structure to nest XMLTABLE result data set
 
SQL#66cc66;">> #993333; font-weight: bold;">SELECT vt#66cc66;">.FaceTypeId_col
  #cc66cc;">2  #993333; font-weight: bold;">FROM   FACE_MASKS fm
  #cc66cc;">3  #66cc66;">,      XMLTABLE#66cc66;">(#ff0000;">'*'
  #cc66cc;">4                  PASSING fm#66cc66;">.csxml_doc
  #cc66cc;">5                   #993333; font-weight: bold;">COLUMNS
  #cc66cc;">6                     xmlresult      XMLTYPE PATH #ff0000;">'/Face/FaceType'
  #cc66cc;">7                 #66cc66;">) xt
  #cc66cc;">8  #66cc66;">,      XMLTABLE#66cc66;">(#ff0000;">'*'
  #cc66cc;">9                   PASSING xt#66cc66;">.xmlresult
 #cc66cc;">10                   #993333; font-weight: bold;">COLUMNS
 #cc66cc;">11                     FaceTypeId_col number PATH #ff0000;">'FaceTypeId/text()'
 #cc66cc;">12                 #66cc66;">) vt
 #cc66cc;">13  ;
 
FACETYPEID_COL
#808080; font-style: italic;">--------------
             #cc66cc;">1
             #cc66cc;">2
             #cc66cc;">3
             #cc66cc;">5
             #cc66cc;">6
             #cc66cc;">2
 
#cc66cc;">6 rows selected#66cc66;">.
 
#808080; font-style: italic;">-- Create Structured XMLIndex syntax see Example 6-26: 
#808080; font-style: italic;">-- http://download.oracle.com/docs/cd/E11882_01/appdev.112/e10492/xdb_indexing.htm#BCGJCEAF
 
#808080; font-style: italic;">/* Example 6-26 from the Oracle 11gR2 XMLDB Developers Guide
 
CREATE INDEX po_struct ON po_binxml (OBJECT_VALUE) INDEXTYPE IS XDB.XMLIndex
  PARAMETERS ('XMLTable po_ptab 
                 XMLNAMESPACES(DEFAULT ''http://www.example.com/po''), 
                 ''/purchaseOrder'' 
                 COLUMNS orderdate   DATE          PATH ''@orderDate'', 
                         id          BINARY_DOUBLE PATH ''@id'', 
                         items       XMLType       PATH ''items/item'' VIRTUAL 
               XMLTable li_tab 
                 XMLNAMESPACES(DEFAULT ''http://www.example.com/po''), 
                 ''/item'' PASSING items 
                 COLUMNS partnum     VARCHAR2(15)  PATH ''@partNum'', 
                         description CLOB          PATH ''productName'', 
                         usprice     BINARY_DOUBLE PATH ''USPrice'', 
                         shipdat     DATE          PATH ''shipDate'''); 
 
*/
 
SQL#66cc66;">> #993333; font-weight: bold;">CREATE #993333; font-weight: bold;">INDEX FaceTypeId_SXI
  #cc66cc;">2    #993333; font-weight: bold;">ON FACE_MASKS#66cc66;">(CSXML_DOC#66cc66;">)
  #cc66cc;">3    INDEXTYPE #993333; font-weight: bold;">IS XDB#66cc66;">.XMLIndex
  #cc66cc;">4  PARAMETERS #66cc66;">(#ff0000;">'XMLTABLE fm_content_table_01
  5                 '#ff0000;">'*'#ff0000;">'
  6                 COLUMNS
  7                   xmlresult      XMLTYPE PATH '#ff0000;">'/Face/FaceType'#ff0000;">' VIRTUAL
  8               XMLTABLE fm_content_table_02
  9                 '#ff0000;">'*'#ff0000;">'
 10                 PASSING xmlresult
 11                 COLUMNS
 12                   FaceTypeId_col number  PATH '#ff0000;">'FaceTypeId/text()'#ff0000;">'
 13              '#66cc66;">);
 
#993333; font-weight: bold;">INDEX created#66cc66;">.
 
 
SQL#66cc66;">> col OBJECT_NAME #993333; font-weight: bold;">FOR a50
 
SQL#66cc66;">> #993333; font-weight: bold;">SELECT object_type#66cc66;">, object_name #993333; font-weight: bold;">FROM user_objects
  #cc66cc;">2  #993333; font-weight: bold;">ORDER #993333; font-weight: bold;">BY created#66cc66;">, object_type;
 
OBJECT_TYPE         OBJECT_NAME
#808080; font-style: italic;">------------------- --------------------------------------------------
 
#808080; font-style: italic;">-- Binary XMLType table
LOB                 SYS_LOB0000077441C00002$$
#993333; font-weight: bold;">TABLE               FACE_MASKS
 
#808080; font-style: italic;">-- Content tables
#993333; font-weight: bold;">INDEX               SYS_C0011985
#993333; font-weight: bold;">INDEX               SYS77447_77451_RID_IDX
#993333; font-weight: bold;">INDEX               SYS77447_77448_RID_IDX
#993333; font-weight: bold;">TABLE               FM_CONTENT_TABLE_02
#993333; font-weight: bold;">TABLE               FM_CONTENT_TABLE_01
#993333; font-weight: bold;">INDEX               SYS_C0011987
 
#808080; font-style: italic;">-- The "rest" of the Structured XML Index part
#993333; font-weight: bold;">INDEX               FACETYPEID_SXI
#993333; font-weight: bold;">INDEX               SYS77447_77451_PKY_IDX
 
#cc66cc;">10 rows selected#66cc66;">.

So for example, if I now use the XML statement that was the base for the structured XMLIndex, you will see that is used by getting its results from the content tables…

SQL#66cc66;">> #993333; font-weight: bold;">SET #993333; font-weight: bold;">LINES #cc66cc;">200
SQL#66cc66;">> #993333; font-weight: bold;">SET autotrace #993333; font-weight: bold;">ON
 
 
SQL#66cc66;">> #993333; font-weight: bold;">SELECT vt#66cc66;">.FaceTypeId_col
  #cc66cc;">2  #993333; font-weight: bold;">FROM   FACE_MASKS fm
  #cc66cc;">3  #66cc66;">,      XMLTABLE#66cc66;">(#ff0000;">'*'
  #cc66cc;">4                  PASSING fm#66cc66;">.csxml_doc
  #cc66cc;">5                   #993333; font-weight: bold;">COLUMNS
  #cc66cc;">6                     xmlresult      XMLTYPE PATH #ff0000;">'/Face/FaceType'
  #cc66cc;">7                 #66cc66;">) xt
  #cc66cc;">8  #66cc66;">,      XMLTABLE#66cc66;">(#ff0000;">'*'
  #cc66cc;">9                   PASSING xt#66cc66;">.xmlresult
 #cc66cc;">10                   #993333; font-weight: bold;">COLUMNS
 #cc66cc;">11                     FaceTypeId_col number PATH #ff0000;">'FaceTypeId/text()'
 #cc66cc;">12                 #66cc66;">) vt
 #cc66cc;">13  ;
 
FACETYPEID_COL
#808080; font-style: italic;">--------------
             #cc66cc;">1
             #cc66cc;">2
             #cc66cc;">3
             #cc66cc;">5
             #cc66cc;">6
             #cc66cc;">2
 
#cc66cc;">6 rows selected#66cc66;">.
 
 
Execution Plan
#808080; font-style: italic;">----------------------------------------------------------
Plan hash value: #cc66cc;">2332841822
 
#808080; font-style: italic;">---------------------------------------------------------------------------------------------------------
#66cc66;">| Id  #66cc66;">| Operation                      #66cc66;">| Name                   #66cc66;">| Rows  #66cc66;">| Bytes #66cc66;">| Cost #66cc66;">(%CPU#66cc66;">)#66cc66;">| Time     #66cc66;">|
#808080; font-style: italic;">---------------------------------------------------------------------------------------------------------
#66cc66;">|   #cc66cc;">0 #66cc66;">| #993333; font-weight: bold;">SELECT STATEMENT               #66cc66;">|                        #66cc66;">|     #cc66cc;">2 #66cc66;">|  #cc66cc;">2082 #66cc66;">|     #cc66cc;">3   #66cc66;">(#cc66cc;">0#66cc66;">)#66cc66;">| 00:00:01 #66cc66;">|
#66cc66;">|   #cc66cc;">1 #66cc66;">|  NESTED LOOPS                  #66cc66;">|                        #66cc66;">|       #66cc66;">|       #66cc66;">|            #66cc66;">|          #66cc66;">|
#66cc66;">|   #cc66cc;">2 #66cc66;">|   NESTED LOOPS                 #66cc66;">|                        #66cc66;">|     #cc66cc;">2 #66cc66;">|  #cc66cc;">2082 #66cc66;">|     #cc66cc;">3   #66cc66;">(#cc66cc;">0#66cc66;">)#66cc66;">| 00:00:01 #66cc66;">|
#66cc66;">|   #cc66cc;">3 #66cc66;">|    NESTED LOOPS                #66cc66;">|                        #66cc66;">|     #cc66cc;">1 #66cc66;">|   #cc66cc;">526 #66cc66;">|     #cc66cc;">3   #66cc66;">(#cc66cc;">0#66cc66;">)#66cc66;">| 00:00:01 #66cc66;">|
#66cc66;">|   #cc66cc;">4 #66cc66;">|     #993333; font-weight: bold;">TABLE ACCESS #993333; font-weight: bold;">FULL          #66cc66;">| FACE_MASKS             #66cc66;">|     #cc66cc;">1 #66cc66;">|    #cc66cc;">12 #66cc66;">|     #cc66cc;">3   #66cc66;">(#cc66cc;">0#66cc66;">)#66cc66;">| 00:00:01 #66cc66;">|
#66cc66;">|   #cc66cc;">5 #66cc66;">|     #993333; font-weight: bold;">TABLE ACCESS #993333; font-weight: bold;">BY #993333; font-weight: bold;">INDEX ROWID#66cc66;">| FM_CONTENT_TABLE_01    #66cc66;">|     #cc66cc;">1 #66cc66;">|   #cc66cc;">514 #66cc66;">|     #cc66cc;">0   #66cc66;">(#cc66cc;">0#66cc66;">)#66cc66;">| 00:00:01 #66cc66;">|
#66cc66;">|*  #cc66cc;">6 #66cc66;">|      #993333; font-weight: bold;">INDEX RANGE SCAN          #66cc66;">| SYS77447_77448_RID_IDX #66cc66;">|     #cc66cc;">1 #66cc66;">|       #66cc66;">|     #cc66cc;">0   #66cc66;">(#cc66cc;">0#66cc66;">)#66cc66;">| 00:00:01 #66cc66;">|
#66cc66;">|*  #cc66cc;">7 #66cc66;">|    #993333; font-weight: bold;">INDEX RANGE SCAN            #66cc66;">| SYS77447_77451_PKY_IDX #66cc66;">|     #cc66cc;">6 #66cc66;">|       #66cc66;">|     #cc66cc;">0   #66cc66;">(#cc66cc;">0#66cc66;">)#66cc66;">| 00:00:01 #66cc66;">|
#66cc66;">|   #cc66cc;">8 #66cc66;">|   #993333; font-weight: bold;">TABLE ACCESS #993333; font-weight: bold;">BY #993333; font-weight: bold;">INDEX ROWID  #66cc66;">| FM_CONTENT_TABLE_02    #66cc66;">|     #cc66cc;">6 #66cc66;">|  #cc66cc;">3090 #66cc66;">|     #cc66cc;">0   #66cc66;">(#cc66cc;">0#66cc66;">)#66cc66;">| 00:00:01 #66cc66;">|
#808080; font-style: italic;">---------------------------------------------------------------------------------------------------------
 
Predicate Information #66cc66;">(#993333; font-weight: bold;">IDENTIFIED #993333; font-weight: bold;">BY operation id#66cc66;">):
#808080; font-style: italic;">---------------------------------------------------
 
   #cc66cc;">6 #66cc66;">- access#66cc66;">(#ff0000;">"FM"#66cc66;">.ROWID#66cc66;">=#ff0000;">"SYS_ALIAS_0"#66cc66;">.#ff0000;">"RID"#66cc66;">)
   #cc66cc;">7 #66cc66;">- access#66cc66;">(#ff0000;">"SYS_ALIAS_0"#66cc66;">.#ff0000;">"KEY"#66cc66;">=#ff0000;">"SYS_ALIAS_1"#66cc66;">.#ff0000;">"PKEY"#66cc66;">)
 
Note
#808080; font-style: italic;">-----
   #66cc66;">- dynamic sampling used #993333; font-weight: bold;">FOR this statement #66cc66;">(level#66cc66;">=#cc66cc;">2#66cc66;">)
 
 
Statistics
#808080; font-style: italic;">----------------------------------------------------------
          #cc66cc;">0  recursive calls
          #cc66cc;">0  db block gets
         #cc66cc;">13  consistent gets
          #cc66cc;">0  physical reads
          #cc66cc;">0  redo size
        #cc66cc;">498  bytes sent via SQL#66cc66;">*Net #993333; font-weight: bold;">TO client
        #cc66cc;">416  bytes received via SQL#66cc66;">*Net #993333; font-weight: bold;">FROM client
          #cc66cc;">2  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;">6  rows processed

This post is based on a OTN thread were I gave some examples on how to build a structured XMLIndex or multiple structured XMLIndex on a binary XML column. The examples are easily rewritten for an XMLType table storage solution using the virtual column OBJECT_VALUE instead of using the xmltype column name.

HTH

:-)

Related Posts

CLUSTERING_FACTOR What-If Analysis

In Oracle the clustering factor of an index is a single number that is supposed to represent the correlation between the order of the index and the order of the corresponding table.

If an execution plan contains an index range scan including an access to a table by ROWID, the clustering factor tells the cost-based optimizer how "clustered" or "scattered" the data in the table is with respect to the index - that is on average how likely contiguous rows from an index range scan will point to the same table block. Widely scattered data will require to read a different block for every row returned from the index range scan, in contrast in the case of clustered data many of the contiguous rows from the index will point to the same block, making a significant difference to the cost calculated by the cost-based optimizer (and of course making also a significant difference at actual execution time, in particular when many table blocks have to be read from disk).

As described e.g. in detail by Jonathan Lewis in his Cost-Based Fundamentals book, Chapter 5 (Clustering Factor), there are various scenarios that have significant influence on the clustering factor, and there are scenarios where Oracle actually doesn't get the clustering factor right - in particular when dealing with data that is inserted simultaneously in conjunction with some segment space management method that attempts to spread the inserted data across different blocks to avoid contention. This can happen for instance when using multiple freelists/freelist groups with manual segment space management (MSSM) or automatic segment space management (ASSM) for that matter.

Usually the clustering factor in case of an index range scan with table access involved represents the largest fraction of the cost associated with the operation, therefore indexes with high clustering factors (meaning that the table data is scattered in relation to the index order) tend to be ignored by the cost-based optimizer and different access paths might be favored instead, like full table scans or the usage of different available indexes.

Since this clustering factor is therefore often such a crucial information I have derived a simple query from what DBMS_STATS.GATHER_INDEX_STATS uses (and Jonathan mentions and explains in his book) and that allows to perform a what-if analysis regarding the clustering factor.

It can be used to:

- Validate/correct the clustering factor of an existing index determined by DBMS_STATS
- In particular check if concurrent inserts together with some segment space management lead to an non-representative clustering factor (this is something DBMS_STATS does not support at present and can only be corrected manually using DBMS_STATS.SET_INDEX_STATS)
- Perform various what-if scenarios without the need to actually create/re-create the index, e.g.
- The clustering factor of a new index to be added
- The effect of adding a column to an existing index on the clustering factor
- The effect of changing the column order of an existing index
- The effect of changing an index to an reverse index

Similar information could also be obtained by simply creating the corresponding index, but there are some points to consider here:

Creating an index up to release 10g might lead to unwanted changes in the execution plans if performed on a live system. In 11g the option to create an index as invisible can be used to avoid this, however you still incur the overhead of writing the index structure and allocating physical space, which could be significant depending on the amount of data.

Furthermore the query allows for certain kinds of analysis that is simply not possible by creating an index - more on this later.

The query has this general form:

select
sys_op_countchg(substrb(row_id,1,15), )
[* (100 / %perc)] as clustering_factor
, count(*)
[* (100 / %perc)] as cnt
, count(distinct substrb(row_id,1,15))
[* (100 / %perc)] as blocks
from
(
select /*+ no_merge no_eliminate_oby */
/* optionally use parallelism e.g. parallel(t, 2) */
rowid as row_id
[, t.*]
[, dbms_rowid.rowid_relative_fno(rowid) as file_number]
[, dbms_rowid.rowid_block_number(rowid) as block_number]
[, dbms_rowid.rowid_row_number(rowid) as row_number]
from
[sample [block] (%perc)] t
where
is not null
[or is not null]
order by
[asc|desc]
[, [asc|desc]]
, rowid
);

and a first version of a ready-to-use script could look like this:

-- Simulate / calculate clustering factor
set verify off

set termout off

column ora10 new_value if_v10 noprint
column oralower10 new_value if_lower_v10 noprint

-- Determine version for regular expression support
select
decode(substr(banner, instr(banner, 'Release ') + 8, 1), '1', '', '--') as ora10
from
v$version
where
rownum = 1;

select
decode('&if_v10', '--', '', '--') as oralower10
from
dual;

set termout on

accept table_name prompt 'Enter table name: '

define sample_pct = 100

accept sample_pct number default &sample_pct prompt 'Enter sample percent (default &sample_pct): '

define p_degree = DEFAULT

accept p_degree default &p_degree prompt 'Enter parallel degree (default &p_degree): '

define history = 1

accept history number default &history prompt 'Enter number of blocks to remember (default &history): '

-- ideally comma separated without spaces surrounding the comma
accept col_list prompt 'Enter comma separated index column list: '

set termout off

column define_sample_block new_value sample_block noprint

select
case
when &sample_pct < 100
then 'sample block (&sample_pct)'
else ''
end as define_sample_block
from
dual;

column col_list_where_expr new_value col_list_where noprint

-- Try to get clever with the WHERE clause derived from the index expression
select
replace(
&if_v10 regexp_replace('&col_list', '( asc| desc)?( nulls (last|first))?', '')
&if_lower_v10 replace(replace(replace(replace(replace('&col_list', ' asc', ''), ' desc', ''), ' nulls', ''), ' last', ''), ' first', '')
, ','
, ' is not null or '
) || ' is not null' as col_list_where_expr
from
dual;

column p_degree_hint_expr new_value p_degree_hint noprint

select
case
when '&p_degree' != 'DEFAULT'
then 'parallel (t &p_degree)'
else ''
end as p_degree_hint_expr
from
dual;

-- set echo on verify on
set termout on

select
sys_op_countchg(substrb(row_id,1,15), &history)
* (100 / &sample_pct) as clustering_factor
, count(*)
* (100 / &sample_pct) as cnt
, count(distinct substrb(row_id,1,15))
* (100 / &sample_pct) as blocks
from
(
select /*+ no_merge no_eliminate_oby &p_degree_hint */
rowid as row_id
--, t.*
from
&table_name &sample_block t
where
&col_list_where
order by
&col_list
, rowid
);

It is based on the undocumented aggregate function SYS_OP_COUNTCHG that is apparently used by DBMS_STATS to calculate the clustering factor.

The second parameter to this function (I called it "history") is very interesting, since it represents the number of blocks the function "remembers" to determine if the block has "changed" or not. DBMS_STATS uses 1 as value and therefore if we have data that is still clustered but unluckily scattered across a few blocks it will lead to a likely non-representative clustering factor since walking the index may jump forth and back between these few blocks but the SYS_OP_COUNTCHG function will increase the clustering factor with each different block, although it stays within the same few blocks and therefore these blocks very likely will be held in the cache.

For example in case of concurrent inserts and ASSM or freelist / freelist groups with MSSM choosing an appropriate number of blocks to retain could be the number of concurrent processes that insert the data - more on this in the demonstration part later.

The remaining placeholders are straightforward - the table name obviously, and if it is a large table you can use the sample clause to avoid reading the whole table, but then the values returned need to be adjusted accordingly - you could also try to run a potentially required full table scan in parallel (something that the original DBMS_STATS query doesn't) - note however that the aggregate function will/needs to be performed by the query coordinator (due to the dependency of the clustering factor evaluation on the data order) which might represent the bottleneck in case of parallel execution.

If you want to get a feeling on how the data will be sorted according to the index definition you can use the [t.*] and [DBMS_ROWID...] clauses and execute only the inner query without the aggregate function - in this case a potentially required sort operation is going to be more costly due to the increased data volume to sort.

The expressions in the WHERE and ORDER BY clause are supposed to represent the columns and/or expressions (in case of function-based indexes) used in the index definition.

The WHERE clause will ensure that only data will be considered that leads to non-null expressions in the index (a b*tree index only covers non-null data), and the ORDER BY clause will order the data the way the index will be ordered.

Note that non-unique indexes will get the ROWID added to make the index expression unique - for unique indexes this is not required, but doesn't harm, since the expression by itself is already "unique". You can omit the ROWID in this case, but it won't change the outcome.

This query also allows some interesting considerations. For example if you know that you'll mostly access only a particular "hot" part of the table which is well clustered, but the remaining "cold(er)" part of the table is rather scattered (for example in case of batch inserts of newly arrived data into a partially deleted and shrunk table via the new SHRINK option introduced in 10g), the overall average clustering factor determined might be bad but probably not representative for a typical query accessing only the "hot"/"latest" data. You could then add the corresponding selection criteria to the query to restrict the data analysed accordingly and use the obtained clustering factor to correct the index statistics using DBMS_STATS.SET_INDEX_STATS.

Here is an demonstration of some of the common scenarios regarding the clustering factor. It allows to reproduce issues with concurrent inserts, extra columns and changed column order. It is a modification of some code I've recently used to reproduce similar issues that one of my clients had.

prompt Tablespace
accept tblspace
rem define tblspace = test_8k

drop table t1 purge;

drop sequence seq_t1_run_id;

drop sequence seq_t1_seq_id;

create table t1 (
run_id integer not null, /* identify the process inserting the data */
batch_id integer not null, /* represents clustered data, could also be a (arriving) date */
a_value number null, /* represents sequence based data */
a_random number null, /* represents randomly scattered data */
a_date timestamp default systimestamp not null, /* represents the insert timestamp */
filler char(1) default 'x' not null /* can be used to size the row as required */
)
tablespace &tblspace;

/* a sample index */
create index t1_idx1 on
t1 (
batch_id,
a_value
)
tablespace &tblspace;

create sequence seq_t1_run_id;

create sequence seq_t1_seq_id;

create or replace procedure populate_t1(i_run_id in integer, i_iter in integer) as
begin
dbms_output.put_line(
dbms_lock.request(
1
, dbms_lock.s_mode
, release_on_commit => true
)
);
commit;
for i in 1..i_iter loop
for j in 1..100 loop
insert into t1 (
run_id
, batch_id
, a_value
, a_random
)
values (
i_run_id
, i
, seq_t1_seq_id.nextval
, trunc(dbms_random.value(1, 1000))
);
commit;
dbms_lock.sleep(0.01);
end loop;
end loop;
end;
/

If you want to test the effect of concurrent inserts with ASSM for instance, choose an appropriate tablespace (or modify the script to use freelists / freelist groups with MSSM) and run the following code.

In a main session run this:

-- run this as main session
-- afterwards start as many of the below code snippets
-- and press return in the main session
-- to let them all start at the same time

truncate table t1;

begin
dbms_output.put_line(
dbms_lock.request(
1
, dbms_lock.x_mode
, release_on_commit=>true
)
);
end;
/

prompt Press return to continue when sessions have been started

accept x

commit;

prompt Press return to continue when sessions have completed

accept x

exec dbms_stats.gather_table_stats(null, 't1', estimate_percent=>null, cascade=>true)

select
index_name
, clustering_factor
from
user_indexes
where
table_name = 'T1';

Then start this in as many sessions as you want to run concurrently:

set timing on echo on

variable n_run_id number

exec select seq_t1_run_id.nextval into :n_run_id from dual;

exec populate_t1(:n_run_id, 100)

commit;

After the sessions have started they're going to wait on the lock of the main session. Press ENTER to get the sessions started and press ENTER again after the session have completed to gather statistics and get initial information about the CLUSTERING_FACTOR of the sample index determined by DBMS_STATS.

The number of iterations (set to 100 in the code snippet above) determines how long this code will run - every insert is delayed by 1/100th of a second - the minimum delay supported by DBMS_LOCK.SLEEP - so the block above will insert 10,000 rows lasting approx. 100 seconds.

The code uses the simple synchronisation method also used by Jonathan in his sample scripts (based on DBMS_LOCK.REQUEST) - the main session allocates a user lock in exclusive mode, all other sessions attempt to request this in shared mode. Therefore all sessions will wait until the main session commits to release the lock. Note that this uses a hard coded lock handle - in a non-test system it is advisable to use DBMS_LOCK.ALLOCATE_UNIQUE to generate a unique lock handle.

Depending on what you've chosen as concurrency and segment space managment, the clustering factor of the index on (batch_id, a_value) might be close to the number of blocks or rows in the table as determined by the final DBMS_STATS call.

You can use now the query to perform some analysis regarding the clustering factor. You could run e.g. the following query:

select
sys_op_countchg(substrb(row_id,1,15), 1) as clustering_factor
, count(*) as cnt
, count(distinct substrb(row_id,1,15)) as blocks
from
(
select /*+ no_merge no_eliminate_oby */
rowid as row_id
, t1.*
from
t1
where
batch_id is not null
or a_value is not null
order by
batch_id
, a_value
, rowid
);

This should give you exactly the clustering factor that has been determined by the DBMS_STATS call used above.

In my case when using MSSM I got a clustering factor of 190 with the table having 186 blocks for 40,000 rows (four concurrent processes each inserting 10,000 rows).

When using ASSM for the same setup (four processes) I got a clustering factor of 28,483 (!) for the same index. Note that the results might vary significantly, depending on how the processes were assigned to the different freelists (MSSM) or block groups (ASSM).

If you've used ASSM or freelist / freelist groups with MSSM then replace the "history" parameter with your number of concurrent processes (or number of freelists, if you had more processes than freelists), e.g. in case of four concurrent processes:

select
sys_op_countchg(substrb(row_id,1,15), 4) as clustering_factor
...

and you should notice a significant drop in the clustering factor, caused by the fact that multiple concurrent inserts used different blocks and therefore the data is not in a single block, but clustered in a few blocks and Oracle has in this constructed case to "jump" forth and back between these few blocks to obtain the data (actually caused by the A_VALUE column which is an increasing value, but written concurrently by the different processes, so with ASSM/freelist (groups) each increasing value is potentially stored in a different block).

In my particular case the simulated clustering factor for ASSM dropped from 28,483 to 188.

Some variations of the query allow to reproduce some other scenarios, e.g. use the following columns to see the impact of adding a badly scattered column to an index:

batch_id

(in my case clustering factor 188 for MSSM with default freelists)

vs.

batch_id
, a_random

(in my case clustering factor 22,191 for MSSM with default freelists, selecting a history size of 4 showed a clustering factor of 190)

or this one to see the impact of changing the column order:

batch_id
, a_value
, a_random
, rowid

(in my case clustering factor 190 for MSSM with default freelists)

vs.

a_random
, batch_id
, a_value
, rowid

(in my case clustering factor 35,904 for MSSM with default freelists, and here increasing the history size to 4 doesn't make a significant difference)

You could also use variations of the following query to get a feeling how the data arrived in the table:

select
rowid
, run_id
, batch_id
, a_value
, to_char(a_date, 'DD.MM.YYYY HH24:MI:SS.FF') as insert_timestamp
, dbms_rowid.rowid_relative_fno(rowid) as file_number
, dbms_rowid.rowid_block_number(rowid) as block_number
, dbms_rowid.rowid_row_number(rowid) as row_number
from
t1
order by
a_date;

By using different ORDER BYs (or no ORDER BY) for the above query you can get some other interesting insights how the data is stored in the table - in particular the difference when using multiple freelists or ASSM with concurrent inserts.

Happy simulating!

I’m now an Oracle ACE Director

Just a quick post in celebration of my recent appointment as an Oracle ACE Director! I’m very happy to be invited to this group, and look forward to getting involved in some of the discussions.