Search
Navigation
OakieTags
Who's online
There are currently 0 users and 33 guests online.
Affiliations
Oakies Blog Aggregator
Scalability Conflict
Here’s an example of how you have to think about conflicts of interest when dealing with problems of scalability. It starts with a request (that I won’t give in detail, and was a little different from the shape I describe below) from a client for advice on how to make a query go faster. Basic [...]
書き込みキャッシュは禁じ手か？
前回のテストで書き込みキャッシュの有効性を確認できた。
しかし、Shared Diskで使う場合はこの設定をしてはいけない。
停電対応のためだけにDisableにしなさいというのであればUPS電源を確保した環境であれば補える。
RACクラスター構成も使わないのであれば、メモリ障害などの危険性もないわけではないが、
これほど有効な設定を使わない手はない。
前にやったベンチマーク結果をもう一度おさらいすると：
メモリのWriteアクセススピードは約7.6GB/s
ディスクはTPC-Cの場合はランダムWriteを注目すべきなので約2.6MB/s
おおよそ2900倍の差がある。
最後に、
書き込み負荷でtpmが回らなかったのだから、4本のHDDでストライピングし解決したとすると、
当然tpmは上がり、CPUもある程度Busyになってくる。
そこでRAC構成にしてスケーラビリティを上げようとすると、書き込みキャッシュがいきなり「禁じ手」になる。
結果、2台のRACで1台で出したtpmと「同じ程度」しか出せない。
RACでスケーラビリティを上げようと思ったら4本のストライピングじゃ全然足りない。なぜならばWrite Busyを少しでも発生させると2900倍の差が発生するからだ。
VM環境テストで性能が上がらないケースにも当てはまる。
C based XML tools in your $ORACLE_HOME
Being triggered by Laurent Schneider’s post “extract xml from the command line“; I completely forgot about the C-based XDK tooling you nowadays can find in your $ORACLE_HOME. You, probably just like me, weren’t even aware, there were some (C-based that is). Most of these are executable’s and not “just” Java tools, although xsql is a shell script that still starts Java. More information can be found here in the “Oracle® XML Developer’s Kit Programmer’s Guide 11.2”
I mean in principle they are not “new”, they were there since 8.1.x, but now they are compiled executables which you can use on the shell prompt and or in scripting and that is, at least for me, easier than doing the same via their $ORACLE_HOME/xdk Java counterparts.
A shortlist:
#66cc66;">[oracle@localhost bin#66cc66;">]$ pwd #66cc66;">/home#66cc66;">/oracle#66cc66;">/app#66cc66;">/oracle#66cc66;">/product#66cc66;">/11#66cc66;">.2#66cc66;">.0#66cc66;">/dbhome_2#66cc66;">/bin #66cc66;">[oracle@localhost bin#66cc66;">]$ ls #66cc66;">-l x#66cc66;">* schema #66cc66;">-rwxr#66cc66;">-xr#66cc66;">-x #cc66cc;">1 oracle oracle #cc66cc;">3433339 Jun #cc66cc;">23 #cc66cc;">19:#cc66cc;">56 schema #66cc66;">-rwxr#66cc66;">-xr#66cc66;">-x #cc66cc;">1 oracle oracle #cc66cc;">3582629 Jun #cc66cc;">23 #cc66cc;">19:#cc66cc;">55 xml #66cc66;">-rwxr#66cc66;">-xr#66cc66;">-x #cc66cc;">1 oracle oracle #cc66cc;">4006197 Jun #cc66cc;">23 #cc66cc;">19:#cc66cc;">56 xmlcg #66cc66;">-rwx#808080; font-style: italic;">------ 1 oracle oracle 49812 Mar 11 2009 xmlwf #66cc66;">-rwxr#66cc66;">-xr#66cc66;">-x #cc66cc;">1 oracle oracle #cc66cc;">3485095 Jun #cc66cc;">23 #cc66cc;">19:#cc66cc;">56 xsl #66cc66;">-rwxr#66cc66;">-xr#66cc66;">-x #cc66cc;">1 oracle oracle #cc66cc;">748 Nov #cc66cc;">12 #cc66cc;">2006 xsql #66cc66;">-rwxr#66cc66;">-xr#66cc66;">-x #cc66cc;">1 oracle oracle #cc66cc;">3496134 Jun #cc66cc;">23 #cc66cc;">19:#cc66cc;">56 xvm #66cc66;">[oracle@localhost bin#66cc66;">]$ file x#66cc66;">* schema xml: ELF #cc66cc;">32#66cc66;">-bit LSB executable#66cc66;">, Intel #cc66cc;">80386#66cc66;">, version #cc66cc;">1 #66cc66;">(SYSV#66cc66;">)#66cc66;">, #993333; font-weight: bold;">FOR GNU#66cc66;">/Linux 2#66cc66;">.2#66cc66;">.5#66cc66;">, dynamically linked #66cc66;">(uses shared libs#66cc66;">)#66cc66;">, #993333; font-weight: bold;">FOR GNU#66cc66;">/Linux 2#66cc66;">.2#66cc66;">.5#66cc66;">, #993333; font-weight: bold;">NOT stripped xmlcg: ELF #cc66cc;">32#66cc66;">-bit LSB executable#66cc66;">, Intel #cc66cc;">80386#66cc66;">, version #cc66cc;">1 #66cc66;">(SYSV#66cc66;">)#66cc66;">, #993333; font-weight: bold;">FOR GNU#66cc66;">/Linux 2#66cc66;">.2#66cc66;">.5#66cc66;">, dynamically linked #66cc66;">(uses shared libs#66cc66;">)#66cc66;">, #993333; font-weight: bold;">FOR GNU#66cc66;">/Linux 2#66cc66;">.2#66cc66;">.5#66cc66;">, #993333; font-weight: bold;">NOT stripped xmlwf: ELF #cc66cc;">32#66cc66;">-bit LSB executable#66cc66;">, Intel #cc66cc;">80386#66cc66;">, version #cc66cc;">1 #66cc66;">(SYSV#66cc66;">)#66cc66;">, #993333; font-weight: bold;">FOR GNU#66cc66;">/Linux 2#66cc66;">.2#66cc66;">.5#66cc66;">, dynamically linked #66cc66;">(uses shared libs#66cc66;">)#66cc66;">, #993333; font-weight: bold;">FOR GNU#66cc66;">/Linux 2#66cc66;">.2#66cc66;">.5#66cc66;">, #993333; font-weight: bold;">NOT stripped xsl: ELF #cc66cc;">32#66cc66;">-bit LSB executable#66cc66;">, Intel #cc66cc;">80386#66cc66;">, version #cc66cc;">1 #66cc66;">(SYSV#66cc66;">)#66cc66;">, #993333; font-weight: bold;">FOR GNU#66cc66;">/Linux 2#66cc66;">.2#66cc66;">.5#66cc66;">, dynamically linked #66cc66;">(uses shared libs#66cc66;">)#66cc66;">, #993333; font-weight: bold;">FOR GNU#66cc66;">/Linux 2#66cc66;">.2#66cc66;">.5#66cc66;">, #993333; font-weight: bold;">NOT stripped xsql: Bourne shell script text executable xvm: ELF #cc66cc;">32#66cc66;">-bit LSB executable#66cc66;">, Intel #cc66cc;">80386#66cc66;">, version #cc66cc;">1 #66cc66;">(SYSV#66cc66;">)#66cc66;">, #993333; font-weight: bold;">FOR GNU#66cc66;">/Linux 2#66cc66;">.2#66cc66;">.5#66cc66;">, dynamically linked #66cc66;">(uses shared libs#66cc66;">)#66cc66;">, #993333; font-weight: bold;">FOR GNU#66cc66;">/Linux 2#66cc66;">.2#66cc66;">.5#66cc66;">, #993333; font-weight: bold;">NOT stripped schema: ELF #cc66cc;">32#66cc66;">-bit LSB executable#66cc66;">, Intel #cc66cc;">80386#66cc66;">, version #cc66cc;">1 #66cc66;">(SYSV#66cc66;">)#66cc66;">, #993333; font-weight: bold;">FOR GNU#66cc66;">/Linux 2#66cc66;">.2#66cc66;">.5#66cc66;">, dynamically linked #66cc66;">(uses shared libs#66cc66;">)#66cc66;">, #993333; font-weight: bold;">FOR GNU#66cc66;">/Linux 2#66cc66;">.2#66cc66;">.5#66cc66;">, #993333; font-weight: bold;">NOT stripped
So here a small overview on what they can do…
schema
The “schema” binary is probably the XML Schema Processor and can validate schemas, allowing use of simple and complex XML datatypes.
#66cc66;">[oracle@localhost bin#66cc66;">]$ schema #66cc66;">-h Unknown switch #66cc66;">-h Usage: schema #66cc66;">[flags#66cc66;">] #66cc66;"><instance#66cc66;">> #66cc66;">[schema#66cc66;">] #66cc66;">[working dir#66cc66;">] #993333; font-weight: bold;">WHERE: #66cc66;">instance#66cc66;">><instance#66cc66;">> #993333; font-weight: bold;">IS the XML instance document #993333; font-weight: bold;">TO validate #66cc66;">(required#66cc66;">) #66cc66;">[schema#66cc66;">] #993333; font-weight: bold;">IS the #993333; font-weight: bold;">DEFAULT schema #66cc66;">(optional#66cc66;">) #66cc66;">[working dir#66cc66;">] #993333; font-weight: bold;">IS the working directory #993333; font-weight: bold;">FOR processing #66cc66;">(optional#66cc66;">) Flags: #66cc66;">-#cc66cc;">0 Always exit #993333; font-weight: bold;">WITH code #cc66cc;">0 #66cc66;">(success#66cc66;">) #66cc66;">-c Extra tests #993333; font-weight: bold;">TO improve code coverage #66cc66;">-e #66cc66;"><encoding#66cc66;">> Specify #993333; font-weight: bold;">DEFAULT input file encoding #66cc66;">-E #66cc66;">encoding#66cc66;">><encoding#66cc66;">> Specify output#66cc66;">/#993333; font-weight: bold;">DATA#66cc66;">/presentation encoding #66cc66;">-i #993333; font-weight: bold;">IGNORE provided schema file #66cc66;">-o #66cc66;"><num#66cc66;">> Validation options #66cc66;">-p Print instance document #993333; font-weight: bold;">TO stdout #993333; font-weight: bold;">ON success #66cc66;">-v #993333; font-weight: bold;">SHOW version number #66cc66;">-u forced #993333; font-weight: bold;">TO Unicode path #66cc66;">[oracle@localhost bin#66cc66;">]$ schema #66cc66;">-v Oracle XML Developers Kit 11#66cc66;">.2#66cc66;">.0#66cc66;">.1#66cc66;">.0 #66cc66;">num#66cc66;">>encoding#66cc66;">>instance#66cc66;">>
xml
Among others, “xml”, can be used for checks on XML well formedness and creates and parses XML with industry standard DOM and SAX interfaces.
#66cc66;">[oracle@localhost bin#66cc66;">]$ xml #66cc66;">-h Usage: xml #66cc66;">[switches#66cc66;">] #66cc66;">[document URI#66cc66;">] #993333; font-weight: bold;">OR xml #66cc66;">-f #66cc66;">[switches#66cc66;">] #66cc66;">[document filespec#66cc66;">] Switches: #66cc66;">-B #66cc66;"><baseuri#66cc66;">> #993333; font-weight: bold;">SET the Base uri #993333; font-weight: bold;">FOR XSLT processor#66cc66;">. BaseUri of http:#66cc66;">//pqr#66cc66;">/xsl#66cc66;">.txt resolves pqr#66cc66;">.txt #993333; font-weight: bold;">TO http:#66cc66;">//pqr#66cc66;">/pqr#66cc66;">.txt #66cc66;">-c Conformance #993333; font-weight: bold;">CHECK only#66cc66;">, no validation #66cc66;">-e #66cc66;"><encoding#66cc66;">> Specify #993333; font-weight: bold;">DEFAULT input file encoding #66cc66;">(#66cc66;">-ee #993333; font-weight: bold;">TO force#66cc66;">) #66cc66;">-E #66cc66;">encoding#66cc66;">><encoding#66cc66;">> Specify output#66cc66;">/#993333; font-weight: bold;">DATA#66cc66;">/presentation encoding #66cc66;">-f File #66cc66;">- Interpret #66cc66;"><document#66cc66;">> #993333; font-weight: bold;">AS filespec#66cc66;">, #993333; font-weight: bold;">NOT URI #66cc66;">-G #66cc66;"><xptr exprs#66cc66;">> evaluates XPointer scheme examples give #993333; font-weight: bold;">IN a file #66cc66;">-h Help #66cc66;">- #993333; font-weight: bold;">SHOW this usage help #66cc66;">(#66cc66;">-hh #993333; font-weight: bold;">FOR more options#66cc66;">) #66cc66;">-i #66cc66;"><n#66cc66;">> Number of times #993333; font-weight: bold;">TO iterate the XSLT processing #66cc66;">-l #66cc66;"><language#66cc66;">> #993333; font-weight: bold;">LANGUAGE #993333; font-weight: bold;">FOR error reporting #66cc66;">-o #66cc66;"><xsloutfile#66cc66;">> Specify output file of XSLT processor #66cc66;">-p Print document#66cc66;">/DTD structures after parse #66cc66;">-P Pretty print #993333; font-weight: bold;">FROM root element #66cc66;">-PP Pretty print #993333; font-weight: bold;">FROM root node #66cc66;">(DOC#66cc66;">); includes XMLDecl #66cc66;">-PE #66cc66;"><encoding#66cc66;">> Specify encoding #993333; font-weight: bold;">FOR #66cc66;">-P #993333; font-weight: bold;">OR #66cc66;">-PP output #66cc66;">-PX Include XMLDecl #993333; font-weight: bold;">IN output always #66cc66;">-s #66cc66;"><style sheet#66cc66;">> Style sheet #66cc66;">- specifies the XSL style sheet #66cc66;">-v Version #66cc66;">- #993333; font-weight: bold;">SHOW parser version #993333; font-weight: bold;">AND exit #66cc66;">-V #66cc66;"><var#66cc66;">> #66cc66;"><value#66cc66;">> #993333; font-weight: bold;">TO test top level #993333; font-weight: bold;">VARIABLES #993333; font-weight: bold;">IN CXSLT #66cc66;">-w Whitespace #66cc66;">- preserve #993333; font-weight: bold;">ALL whitespace #66cc66;">-W Warning #66cc66;">- stop parsing after a warning #66cc66;">-x Exercise SAX interface #993333; font-weight: bold;">FOR parser #66cc66;">(prints document#66cc66;">) #66cc66;">-Y control characters are valid #66cc66;">[oracle@localhost bin#66cc66;">]$ xml #66cc66;">-cf #66cc66;">/tmp#66cc66;">/otn_dev_xsd_schema#66cc66;">.xml #66cc66;">[oracle@localhost bin#66cc66;">]$ xml #66cc66;">-cf #66cc66;">/tmp#66cc66;">/otn_dev_xsd_schema#66cc66;">.invalid#66cc66;">.xml #993333; font-weight: bold;">IN line #cc66cc;">14 of #66cc66;">/tmp#66cc66;">/otn_dev_xsd_schema#66cc66;">.invalid#66cc66;">.xml: LPX#66cc66;">-00225: end#66cc66;">-element tag #ff0000;">"OWNER" does #993333; font-weight: bold;">NOT match start#66cc66;">-element tag #ff0000;">"ROW" #66cc66;">[oracle@localhost bin#66cc66;">]$ xml #66cc66;">-v Oracle XML Developers Kit 11#66cc66;">.2#66cc66;">.0#66cc66;">.1#66cc66;">.0 #66cc66;">value#66cc66;">>var#66cc66;">>style#66cc66;">>encoding#66cc66;">>xsloutfile#66cc66;">>language#66cc66;">>n#66cc66;">>xptr#66cc66;">>document#66cc66;">>encoding#66cc66;">>baseuri#66cc66;">>
xmlcf
“xmlcg” is a C++ tool to generate C++ classes based on XML input.
#66cc66;">[oracle@localhost bin#66cc66;">]$ xmlcg #66cc66;">-h Error: Unknown switch Usage: xmlcg #66cc66;">[switches#66cc66;">] #66cc66;"><document#66cc66;">> #66cc66;">-d #66cc66;"><name#66cc66;">> DTD #66cc66;">- input #993333; font-weight: bold;">IS external DTD #66cc66;">(specify output name#66cc66;">) #66cc66;">-o #66cc66;"><directory#66cc66;">> Output #66cc66;">- specify output directory #66cc66;">-e #66cc66;"><encoding#66cc66;">> Encoding #66cc66;">- specify input file encoding #66cc66;">-h Help #66cc66;">- #993333; font-weight: bold;">SHOW this usage help #66cc66;">-v Version #66cc66;">- #993333; font-weight: bold;">SHOW Class Generator version#808080; font-style: italic;"># #66cc66;">-s #66cc66;"><name#66cc66;">> Schema #66cc66;">- input #993333; font-weight: bold;">IS an XML Schema #66cc66;">(spec#66cc66;">. output name#66cc66;">) #66cc66;">[oracle@localhost bin#66cc66;">]$ xmlcg #66cc66;">/tmp#66cc66;">/otn_dev_xsd_schema#66cc66;">.xml Generating classes #993333; font-weight: bold;">FROM DTD#66cc66;">... #66cc66;">[oracle@localhost bin#66cc66;">]$ xmlcg #66cc66;">/tmp#66cc66;">/otn_dev_xsd_schema#66cc66;">.invalid#66cc66;">.xml #993333; font-weight: bold;">IN line #cc66cc;">14 of #66cc66;">/tmp#66cc66;">/otn_dev_xsd_schema#66cc66;">.invalid#66cc66;">.xml: LPX#66cc66;">-00225: end#66cc66;">-element tag #ff0000;">"OWNER" does #993333; font-weight: bold;">NOT match start#66cc66;">-element tag #ff0000;">"ROW" Parse failed#66cc66;">, code #cc66cc;">225 #66cc66;">[oracle@localhost bin#66cc66;">]$ xmlcg #66cc66;">-v Oracle XML Developers Kit 11#66cc66;">.2#66cc66;">.0#66cc66;">.1#66cc66;">.0 #66cc66;">name#66cc66;">>encoding#66cc66;">>directory#66cc66;">>name#66cc66;">>document#66cc66;">>
xmlwf
The “xmlwf” tool is not described in the XDK manual and just / “only” checks on XML well formedness which can be useful in itself.
#66cc66;">[oracle@localhost bin#66cc66;">]$ xmlwf #66cc66;">-h usage: xmlwf #66cc66;">[#66cc66;">-n#66cc66;">] #66cc66;">[#66cc66;">-p#66cc66;">] #66cc66;">[#66cc66;">-r#66cc66;">] #66cc66;">[#66cc66;">-s#66cc66;">] #66cc66;">[#66cc66;">-w#66cc66;">] #66cc66;">[#66cc66;">-x#66cc66;">] #66cc66;">[#66cc66;">-d output#66cc66;">-dir#66cc66;">] #66cc66;">[#66cc66;">-e encoding#66cc66;">] file #66cc66;">... #66cc66;">[oracle@localhost bin#66cc66;">]$ xmlwf #66cc66;">/tmp#66cc66;">/otn_dev_xsd_schema#66cc66;">.xml #66cc66;">[oracle@localhost bin#66cc66;">]$ xmlwf #66cc66;">/tmp#66cc66;">/otn_dev_xsd_schema#66cc66;">.invalid#66cc66;">.xml #66cc66;">/tmp#66cc66;">/otn_dev_xsd_schema#66cc66;">.invalid#66cc66;">.xml:#cc66cc;">14:#cc66cc;">17: mismatched tag #66cc66;">[oracle@localhost bin#66cc66;">]$ xmlwf #66cc66;">-v xmlwf #993333; font-weight: bold;">USING expat_2#66cc66;">.0#66cc66;">.1 sizeof#66cc66;">(XML_Char#66cc66;">)#66cc66;">=#cc66cc;">1#66cc66;">, sizeof#66cc66;">(XML_LChar#66cc66;">)#66cc66;">=#cc66cc;">1#66cc66;">, XML_DTD#66cc66;">, XML_CONTEXT_BYTES#66cc66;">=#cc66cc;">1024#66cc66;">, XML_NS
xsl
Tool “xsl” is a C XSLT generator and can transform XML into other text-based formats such as HTML.
#66cc66;">[oracle@localhost bin#66cc66;">]$ xsl #66cc66;">-h Usage: xsl #66cc66;">[switches#66cc66;">] #66cc66;"><stylesheet#66cc66;">> #66cc66;"><instance#66cc66;">> #993333; font-weight: bold;">OR xsl #66cc66;">-f #66cc66;">[switches#66cc66;">] #66cc66;">[document filespec#66cc66;">] Switches: #66cc66;">-B #66cc66;"><baseuri#66cc66;">> #993333; font-weight: bold;">SET the Base uri #993333; font-weight: bold;">FOR XSLT processor#66cc66;">. BaseUri of http:#66cc66;">//pqr#66cc66;">/xsl#66cc66;">.txt resolves pqr#66cc66;">.txt #993333; font-weight: bold;">TO http:#66cc66;">//pqr#66cc66;">/pqr#66cc66;">.txt #66cc66;">-e #66cc66;"><encoding#66cc66;">> Specify #993333; font-weight: bold;">DEFAULT input file encoding #66cc66;">(#66cc66;">-ee #993333; font-weight: bold;">TO force#66cc66;">) #66cc66;">-E #66cc66;">encoding#66cc66;">><encoding#66cc66;">> Specify output#66cc66;">/#993333; font-weight: bold;">DATA#66cc66;">/presentation encoding #66cc66;">-f File #66cc66;">- Interpret #66cc66;"><document#66cc66;">> #993333; font-weight: bold;">AS filespec#66cc66;">, #993333; font-weight: bold;">NOT URI #66cc66;">-G #66cc66;"><xptr exprs#66cc66;">> evaluates XPointer scheme examples give #993333; font-weight: bold;">IN a file #66cc66;">-h Help #66cc66;">- #993333; font-weight: bold;">SHOW this usage help #66cc66;">(#66cc66;">-hh #993333; font-weight: bold;">FOR more options#66cc66;">) #66cc66;">-i #66cc66;"><n#66cc66;">> Number of times #993333; font-weight: bold;">TO iterate the XSLT processing #66cc66;">-l #66cc66;"><language#66cc66;">> #993333; font-weight: bold;">LANGUAGE #993333; font-weight: bold;">FOR error reporting #66cc66;">-o #66cc66;"><xsloutfile#66cc66;">> Specify output file of XSLT processor #66cc66;">-v Version #66cc66;">- #993333; font-weight: bold;">SHOW parser version #993333; font-weight: bold;">AND exit #66cc66;">-V #66cc66;"><var#66cc66;">> #66cc66;"><value#66cc66;">> #993333; font-weight: bold;">TO test top level #993333; font-weight: bold;">VARIABLES #993333; font-weight: bold;">IN CXSLT #66cc66;">-w Whitespace #66cc66;">- preserve #993333; font-weight: bold;">ALL whitespace #66cc66;">-W Warning #66cc66;">- stop parsing after a warning #66cc66;">value#66cc66;">>var#66cc66;">>xsloutfile#66cc66;">>language#66cc66;">>n#66cc66;">>xptr#66cc66;">>document#66cc66;">>encoding#66cc66;">>baseuri#66cc66;">>instance#66cc66;">>stylesheet#66cc66;">>
xsql
Combines XML, SQL, and XSLT in the server to deliver dynamic Web content.
#66cc66;">[oracle@localhost bin#66cc66;">]$ xsql Oracle XML Developers Kit 11#66cc66;">.2#66cc66;">.0#66cc66;">.2#66cc66;">.0 #66cc66;">- Production XML#66cc66;">-#cc66cc;">25009: Missing arguments #993333; font-weight: bold;">ON command line Usage: xsql xsqlFileURI #66cc66;">[outFileName#66cc66;">] #66cc66;">[param1#66cc66;">=value1 #66cc66;">... paramN#66cc66;">=valueN#66cc66;">]
xvm
“xvm” is the C alternative of the XSLT Virtual Machine (XVM) which provides a high-performance XSLT transformation engine that supports compiled stylesheets.
#66cc66;">[oracle@localhost bin#66cc66;">]$ xvm #66cc66;">-h Usage: xvm switches #66cc66;"><xslfile#66cc66;">> #66cc66;"><xmlfile#66cc66;">> xvm switches #66cc66;"><xpath#66cc66;">> #66cc66;"><xmlfile#66cc66;">> Switches: #66cc66;">-c Compile #66cc66;"><xslfile#66cc66;">>. The bytecode #993333; font-weight: bold;">IS #993333; font-weight: bold;">IN #ff0000;">'.xvm' #66cc66;">. #66cc66;">-ct Compile #66cc66;"><xslfile#66cc66;">> #993333; font-weight: bold;">AND transform #66cc66;"><xmlfile#66cc66;">>. #66cc66;">-t Transform #66cc66;">xmlfile#66cc66;">><xmlfile#66cc66;">> #993333; font-weight: bold;">USING bytecode #993333; font-weight: bold;">FROM #66cc66;"><xslfile#66cc66;">>. #66cc66;">-xc Compile #66cc66;"><xpath#66cc66;">>. The bytecode #993333; font-weight: bold;">IS #993333; font-weight: bold;">IN #ff0000;">'code.xvm'#66cc66;">. #66cc66;">-xct Compile #993333; font-weight: bold;">AND evaluate #66cc66;">xpath#66cc66;">><xpath#66cc66;">> #993333; font-weight: bold;">WITH #66cc66;"><xmlfile#66cc66;">>. #66cc66;">-xt Evaluate XPath bytecode #993333; font-weight: bold;">FROM #66cc66;"><xpath#66cc66;">> #993333; font-weight: bold;">WITH #66cc66;"><xmlfile#66cc66;">>. Examples: xvm #66cc66;">-ct db#66cc66;">.xsl db#66cc66;">.xml xvm #66cc66;">-t db#66cc66;">.xvm db#66cc66;">.xml xvm #66cc66;">-xct #ff0000;">"doc/emloyee[15]/family" db#66cc66;">.xml #66cc66;">xmlfile#66cc66;">>xpath#66cc66;">>xmlfile#66cc66;">>xpath#66cc66;">>xslfile#66cc66;">>xmlfile#66cc66;">>xslfile#66cc66;">>xmlfile#66cc66;">>xslfile#66cc66;">>xmlfile#66cc66;">>xpath#66cc66;">>xmlfile#66cc66;">>xslfile#66cc66;">>
Working with long columns
Various techniques for querying and manipulating long columns. July 2010 (updated March 2012)
BAAG, Best Practices and Multiple Choice Exams
(This post originally appeared at the Pythian blog)
I’ve been following the discussion in various MySQL blogs regarding the sort_buffer_size parameters. As an Oracle DBA, I don’t have an opinion on the subject, but the discussion did remind me of many discussions I’ve been involved in. What’s the best size for SDU? What is the right value for OPEN_CURSORS? How big should the shared pool be?
All are good questions. Many DBAs ask them hoping for a clear cut answer – Do this, don’t do that! Some experts recognize the need for a clear cut answer, and if they are responsible experts, they will give the answer that does the least harm.
Often the harmless answer is “Don’t touch anything, because if you have to ask this question you don’t have the experience to make the correct decision”. As Sheeri noted, it is a rather patronizing answer and it is stands in the way of those who truly want to learn and become experts.
But I can appreciate that it comes from long and bitter experience. Many users read random bits of information off the web and then rush to modify the production database without fully digesting the details. They end up tuning their database in a way no database should ever be tuned. Not even MySQL.
I used to think that users search for those “best practices” out of laziness, or maybe a lack of time. I used to laugh at the belief that there are best practices and clear answers, because if there were – we wouldn’t have a parameter. But now I think the problem is in the way most institutions evaluate intelligence, which affects the way many people approach any problem.
Even though all of us DBAs come from a wide variety of cultures, I’m willing to bet that every one of us had to work his way through a multiple choice test. If you ever took an Oracle certification exam, you know what I mean:
How do you find the name of the database server?
A) ORACLE_SID
B) ORACLE_DBNAME
C) DB_ID
D) none of the above
You run into those in certification exams, job interviews and in slightly different variation when you try to get accepted to a university. You had to learn to succeed at those multiple choice tests at a very early age, or you would be labled “less intelligent”.
Yet those questions are absurd. In the question above, the answer could be A, but A would be wrong if my database is a RAC cluster. Besides a much better way would be to use /etc/oratab because there may be more than one DB on the machine.
But you can’t have a discussion with the exam author. You can’t ask for assumptions and clarifications and you can’t even explain your assumptions in the test. What’s more, these tests also check for speed, so you don’t have much time to contemplate the options.
What these exams teach you is that every question has a single solution and one that is so obvious that once you see it, you recognize its rightness in less than 30 seconds. They also teach you that someone else knows the right answer (the person grading the test). So finding the right answer can be a matter of getting the “expert” to give you the one and obvious correct answer.
If our society teaches this point of view from a very young age, why are we surprised that DBAs keep looking for the one obvious answer?
I fully support the BAAG cause, and I believe that DBAs should be given a full explanation of the problem involved, the different choices that exist, their meaning, the trade-offs involved and in general give them the tools to make the right decision themselves time after time. But we should realize that undoing years of faulty teaching can take a while.
There is an even worse side effect to those multiple-choice-should-be-obvious tests. You may learn to never ask for clarifications. That asking for clarifications is “bad” or “wrong” in some way. In any real problem, asking for clarifications such as “why are you asking this?”, “what is the real issue you are trying to solve?” and “How will you use this script?” is the most important part of finding a solution. It is not cheating – it is doing a professional job.
It is a scary thought that the very way we train and evaluate the future DBAs is something that will prevent them from doing a good job of being DBAs.
External Views (XML based)
Something new? Eh? Should you do this? Eh?
In all, probably not, but for me this was a good exercise towards some more updated demo scripting for my “Boost your environment with XMLDB” presentation or hopefully more clearer relabeled Oracle Open World name for the almost same presentation called “Interfacing with Your Database via Oracle XML DB” (S319105). Just up front, there are some issues with the following:
- Why should you do it at all. You should have a good reason doing so…
- It can cause a lot of Physical I/O, at least initially when not cached in the SGA
- Until current versions, AFAIK, it will do a lot of “Pickler Fetching”, serializing in memory, which is very resource intensive (CPU/PGA)
- …and its probably not supported…?
…but it is good fun for a small exercise based on the following OTN Thread: “Error with basic XMLTable“…
Let me show you what I mean.
Via “bfilename” you are able, since a long time, I guess Oracle 9.2 and onwards, to read a file as a BLOB and because an “XMLTYPE” can swallow almost any datatype, you could do the following…
#66cc66;">[oracle@localhost ~#66cc66;">]$ sqlplus #66cc66;">/ #993333; font-weight: bold;">AS sysdba SQL#66cc66;">*Plus: Release 11#66cc66;">.2#66cc66;">.0#66cc66;">.1#66cc66;">.0 Production #993333; font-weight: bold;">ON Thu Jul #cc66cc;">29 09:#cc66cc;">20:#cc66cc;">24 #cc66cc;">2010 Copyright #66cc66;">(c#66cc66;">) #cc66cc;">1982#66cc66;">, #cc66cc;">2009#66cc66;">, Oracle#66cc66;">. #993333; font-weight: bold;">ALL rights reserved#66cc66;">. Connected #993333; font-weight: bold;">TO: Oracle #993333; font-weight: bold;">DATABASE 11g Enterprise Edition Release 11#66cc66;">.2#66cc66;">.0#66cc66;">.1#66cc66;">.0 #66cc66;">- Production #993333; font-weight: bold;">WITH the Partitioning#66cc66;">, OLAP#66cc66;">, #993333; font-weight: bold;">DATA Mining #993333; font-weight: bold;">AND Real Application Testing options 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 #993333; font-weight: bold;">TO otn; #993333; font-weight: bold;">GRANT succeeded#66cc66;">. SQL#66cc66;">> conn otn#66cc66;">/otn Connected#66cc66;">. SQL#66cc66;">> sho user USER #993333; font-weight: bold;">IS #ff0000;">"OTN" SQL#66cc66;">> #993333; font-weight: bold;">SET pages #cc66cc;">5000 SQL#66cc66;">> #993333; font-weight: bold;">SET #993333; font-weight: bold;">LINES #cc66cc;">1000 SQL#66cc66;">> #993333; font-weight: bold;">SET long #cc66cc;">10000 SQL#66cc66;">> #993333; font-weight: bold;">SELECT xmltype#66cc66;">(cursor#66cc66;">(#993333; font-weight: bold;">SELECT owner#66cc66;">, schema_url#66cc66;">, #993333; font-weight: bold;">LOCAL #993333; font-weight: bold;">FROM all_xml_schemas #993333; font-weight: bold;">ORDER #993333; font-weight: bold;">BY owner#66cc66;">)#66cc66;">) #993333; font-weight: bold;">FROM dual; XMLTYPE#66cc66;">(CURSOR#66cc66;">(SELECTOWNER#66cc66;">,SCHEMA_URL#66cc66;">,LOCALFROMALL_XML_SCHEMASORDERBYOWNER#66cc66;">)#66cc66;">) #808080; font-style: italic;">----------------------------------------------------------------------------------------------------------- #66cc66;">< ?xml version#66cc66;">=#ff0000;">"1.0"?#66cc66;">> #66cc66;"><rowset#66cc66;">> #66cc66;"><row#66cc66;">> #66cc66;"><owner#66cc66;">>EXFSYS#66cc66;">owner#66cc66;">> #66cc66;"><schema_url#66cc66;">>http:#66cc66;">//xmlns#66cc66;">.oracle#66cc66;">.com#66cc66;">/rlmgr#66cc66;">/rclsprop#66cc66;">.xsd#66cc66;">schema_url#66cc66;">> #66cc66;"><local#66cc66;">>NO#66cc66;">local#66cc66;">> #66cc66;">row#66cc66;">> #66cc66;"><row#66cc66;">> #66cc66;"><owner#66cc66;">>EXFSYS#66cc66;">owner#66cc66;">> #66cc66;"><schema_url#66cc66;">>http:#66cc66;">//xmlns#66cc66;">.oracle#66cc66;">.com#66cc66;">/rlmgr#66cc66;">/rulecond#66cc66;">.xsd#66cc66;">schema_url#66cc66;">> #66cc66;"><local#66cc66;">>NO#66cc66;">local#66cc66;">> #66cc66;">row#66cc66;">> #66cc66;"><row#66cc66;">> #66cc66;"><owner#66cc66;">>MDSYS#66cc66;">owner#66cc66;">> #66cc66;"><schema_url#66cc66;">>http:#66cc66;">//www#66cc66;">.opengis#66cc66;">.net#66cc66;">/gml#66cc66;">/feature#66cc66;">.xsd#66cc66;">schema_url#66cc66;">> #66cc66;"><local#66cc66;">>NO#66cc66;">local#66cc66;">> #66cc66;">row#66cc66;">> #66cc66;"><row#66cc66;">> #66cc66;">... #66cc66;">row#66cc66;">><row#66cc66;">> #66cc66;"><owner#66cc66;">>XDB#66cc66;">owner#66cc66;">> #66cc66;"><schema_url#66cc66;">>http:#66cc66;">//xmlns#66cc66;">.oracle#66cc66;">.com#66cc66;">/xdb#66cc66;">/dav#66cc66;">.xsd#66cc66;">schema_url#66cc66;">> #66cc66;"><local#66cc66;">>NO#66cc66;">local#66cc66;">> #66cc66;">row#66cc66;">> #66cc66;">rowset#66cc66;">> #cc66cc;">51 rows selected#66cc66;">.
Lets write the output to disk in the /tmp directory or my Oracle Enterprise Linux environment…
SQL#66cc66;">> sho user USER #993333; font-weight: bold;">IS #ff0000;">"OTN" SQL#66cc66;">> #993333; font-weight: bold;">CREATE #993333; font-weight: bold;">OR #993333; font-weight: bold;">REPLACE directory XMLDIR #993333; font-weight: bold;">AS #ff0000;">'/tmp/'; Directory created#66cc66;">. SQL#66cc66;">> declare #cc66cc;">2 rc sys_refcursor; #cc66cc;">3 begin #cc66cc;">4 open rc #993333; font-weight: bold;">FOR #993333; font-weight: bold;">SELECT #66cc66;">* #993333; font-weight: bold;">FROM #66cc66;">(#993333; font-weight: bold;">SELECT owner#66cc66;">, schema_url#66cc66;">, #993333; font-weight: bold;">LOCAL #993333; font-weight: bold;">FROM all_xml_schemas #993333; font-weight: bold;">ORDER #993333; font-weight: bold;">BY owner#66cc66;">); #cc66cc;">5 dbms_xslprocessor#66cc66;">.clob2file#66cc66;">(xmltype#66cc66;">(rc#66cc66;">)#66cc66;">.getClobVal#66cc66;">(#66cc66;">)#66cc66;">,#ff0000;">'TMPDIR'#66cc66;">,#ff0000;">'otn_dev_xsd_schema.xml'#66cc66;">); #cc66cc;">6 end; #cc66cc;">7 ; PL#66cc66;">/SQL procedure successfully completed#66cc66;">. SQL#66cc66;">> commit; commit complete SQL#66cc66;">> ! ls #66cc66;">-ltra #66cc66;">/tmp#808080; font-style: italic;">/*.xml -rw-rw-r-- 1 oracle oracle 6563 Jul 29 09:36 /tmp/otn_dev_xsd_schema.xml
Due to the fact the content of this XML file (be aware: 6K) is wellformed XML, for example the alert log is not wellformed, and I know its format, I could read it directly from my database session via the following…
SQL#66cc66;">> #993333; font-weight: bold;">SELECT xmltype#66cc66;">(bfilename#66cc66;">(#ff0000;">'TMPDIR'#66cc66;">,#ff0000;">'otn_dev_xsd_schema.xml'#66cc66;">)#66cc66;">, nls_charset_id#66cc66;">(#ff0000;">'AL32UTF8'#66cc66;">)#66cc66;">) #993333; font-weight: bold;">FROM dual; XMLTYPE#66cc66;">(BFILENAME#66cc66;">(#ff0000;">'TMPDIR'#66cc66;">,#ff0000;">'OTN_DEV_XSD_SCHEMA.XML'#66cc66;">)#66cc66;">,NLS_CHARSET_ID#66cc66;">(#ff0000;">'AL32UTF8'#66cc66;">)#66cc66;">) #808080; font-style: italic;">--------------------------------------------------------------------------------------- #66cc66;">< ?xml version#66cc66;">=#ff0000;">"1.0"?#66cc66;">> #66cc66;"><rowset#66cc66;">> #66cc66;"><row#66cc66;">> #66cc66;"><owner#66cc66;">>EXFSYS#66cc66;">owner#66cc66;">> #66cc66;"><schema_url#66cc66;">>http:#66cc66;">//xmlns#66cc66;">.oracle#66cc66;">.com#66cc66;">/rlmgr#66cc66;">/rclsprop#66cc66;">.xsd#66cc66;">schema_url#66cc66;">> #66cc66;"><local#66cc66;">>NO#66cc66;">local#66cc66;">> #66cc66;">row#66cc66;">> #66cc66;"><row#66cc66;">> #66cc66;"><owner#66cc66;">>EXFSYS#66cc66;">owner#66cc66;">> #66cc66;"><schema_url#66cc66;">>http:#66cc66;">//xmlns#66cc66;">.oracle#66cc66;">.com#66cc66;">/rlmgr#66cc66;">/rulecond#66cc66;">.xsd#66cc66;">schema_url#66cc66;">> #66cc66;"><local#66cc66;">>NO#66cc66;">local#66cc66;">> #66cc66;">row#66cc66;">> #66cc66;"><row#66cc66;">> #66cc66;"><owner#66cc66;">>MDSYS#66cc66;">owner#66cc66;">> #66cc66;"><schema_url#66cc66;">>http:#66cc66;">//www#66cc66;">.opengis#66cc66;">.net#66cc66;">/gml#66cc66;">/feature#66cc66;">.xsd#66cc66;">schema_url#66cc66;">> #66cc66;"><local#66cc66;">>NO#66cc66;">local#66cc66;">> #66cc66;">row#66cc66;">> #66cc66;"><row#66cc66;">> #66cc66;"><owner#66cc66;">>MDSYS#66cc66;">owner#66cc66;">> #66cc66;"><schema_url#66cc66;">>http:#66cc66;">//xmlns#66cc66;">.oracle#66cc66;">.com#66cc66;">/spatial#66cc66;">/georaster#66cc66;">/georaster#66cc66;">.xsd#66cc66;">schema_url#66cc66;">> #66cc66;"><local#66cc66;">>NO#66cc66;">local#66cc66;">> #66cc66;">row#66cc66;">> #66cc66;"><row#66cc66;">> #66cc66;"><owner#66cc66;">>MDSYS#66cc66;">owner#66cc66;">> #66cc66;"><schema_url#66cc66;">>http:#66cc66;">//www#66cc66;">.w3#66cc66;">.org#66cc66;">/#cc66cc;">1999#66cc66;">/xlink#66cc66;">/xlinks#66cc66;">.xsd#66cc66;">schema_url#66cc66;">> #66cc66;"><local#66cc66;">>NO#66cc66;">local#66cc66;">> #66cc66;">row#66cc66;">> #66cc66;"><row#66cc66;">> #66cc66;"><owner#66cc66;">>MDSYS#66cc66;">owner#66cc66;">> #66cc66;"><schema_url#66cc66;">>http:#66cc66;">//www#66cc66;">.opengis#66cc66;">.net#66cc66;">/gml#66cc66;">/geometry#66cc66;">.xsd#66cc66;">schema_url#66cc66;">> #66cc66;"><local#66cc66;">>NO#66cc66;">local#66cc66;">> #66cc66;">row#66cc66;">> #66cc66;">... #66cc66;"><row#66cc66;">> #66cc66;"><owner#66cc66;">>XDB#66cc66;">owner#66cc66;">> #66cc66;"><schema_url#66cc66;">>http:#66cc66;">//xmlns#66cc66;">.oracle#66cc66;">.com#66cc66;">/xdb#66cc66;">/dav#66cc66;">.xsd#66cc66;">schema_url#66cc66;">> #66cc66;"><local#66cc66;">>NO#66cc66;">local#66cc66;">> #66cc66;">row#66cc66;">> #66cc66;">rowset#66cc66;">> #cc66cc;">51 rows selected#66cc66;">.
That easy, although as said it has some issues, performance wise. From Oracle database version 10.2.0.1 and onwards, although I would use as a minimum 10.2.0.3.0 due to that the XQuery engine is C, Oracle kernel build-in based, you could now use the XMLTABLE function, to handle it further. XMLTABLE is a great function to make convert XML in a more relational in look-and-feel and / or because it supports XQuery version 1.0 (and as you (should) know XQuery is a very powerful query language). Anyway, via XMLTABLE, we can now show data in a relational form is demonstrated via the following:
SQL#66cc66;">> #993333; font-weight: bold;">SELECT xtab#66cc66;">.owner #cc66cc;">2 #66cc66;">, xtab#66cc66;">.schema_url #cc66cc;">3 #66cc66;">, xtab#66cc66;">.#993333; font-weight: bold;">LOCAL #cc66cc;">4 #993333; font-weight: bold;">FROM #66cc66;">(#993333; font-weight: bold;">SELECT xmltype#66cc66;">(bfilename#66cc66;">(#ff0000;">'TMPDIR'#66cc66;">,#ff0000;">'otn_dev_xsd_schema.xml'#66cc66;">)#66cc66;">, nls_charset_id#66cc66;">(#ff0000;">'AL32UTF8'#66cc66;">)#66cc66;">) OBJECT_VALUE #993333; font-weight: bold;">FROM dual#66cc66;">) xfile #cc66cc;">5 #66cc66;">, XMLTABLE #66cc66;">(#ff0000;">'/ROWSET/ROW' #cc66cc;">6 PASSING xfile#66cc66;">.object_value #cc66cc;">7 #993333; font-weight: bold;">COLUMNS OWNER VARCHAR2#66cc66;">(#cc66cc;">30#66cc66;">) path #ff0000;">'OWNER' #cc66cc;">8 #66cc66;">, SCHEMA_URL VARCHAR2#66cc66;">(#cc66cc;">80#66cc66;">) path #ff0000;">'SCHEMA_URL' #cc66cc;">9 #66cc66;">, #993333; font-weight: bold;">LOCAL VARCHAR2#66cc66;">(#cc66cc;">3#66cc66;">) path #ff0000;">'LOCAL' #cc66cc;">10 #66cc66;">) xtab #cc66cc;">11 ; OWNER SCHEMA_URL LOC #808080; font-style: italic;">------------------------------ -------------------------------------------------------------------------------- --- EXFSYS http:#66cc66;">//xmlns#66cc66;">.oracle#66cc66;">.com#66cc66;">/rlmgr#66cc66;">/rclsprop#66cc66;">.xsd NO EXFSYS http:#66cc66;">//xmlns#66cc66;">.oracle#66cc66;">.com#66cc66;">/rlmgr#66cc66;">/rulecond#66cc66;">.xsd NO MDSYS http:#66cc66;">//www#66cc66;">.opengis#66cc66;">.net#66cc66;">/gml#66cc66;">/feature#66cc66;">.xsd NO MDSYS http:#66cc66;">//xmlns#66cc66;">.oracle#66cc66;">.com#66cc66;">/spatial#66cc66;">/georaster#66cc66;">/georaster#66cc66;">.xsd NO MDSYS http:#66cc66;">//www#66cc66;">.w3#66cc66;">.org#66cc66;">/#cc66cc;">1999#66cc66;">/xlink#66cc66;">/xlinks#66cc66;">.xsd NO MDSYS http:#66cc66;">//www#66cc66;">.opengis#66cc66;">.net#66cc66;">/gml#66cc66;">/geometry#66cc66;">.xsd NO ORDSYS http:#66cc66;">//xmlns#66cc66;">.oracle#66cc66;">.com#66cc66;">/ord#66cc66;">/dicom#66cc66;">/preference_1_0 NO ORDSYS http:#66cc66;">//xmlns#66cc66;">.oracle#66cc66;">.com#66cc66;">/ord#66cc66;">/dicom#66cc66;">/mapping_1_0 NO ORDSYS http:#66cc66;">//xmlns#66cc66;">.oracle#66cc66;">.com#66cc66;">/ord#66cc66;">/dicom#66cc66;">/metadata_1_0 NO ORDSYS http:#66cc66;">//xmlns#66cc66;">.oracle#66cc66;">.com#66cc66;">/ord#66cc66;">/dicom#66cc66;">/constraint_1_0 NO ORDSYS http:#66cc66;">//xmlns#66cc66;">.oracle#66cc66;">.com#66cc66;">/ord#66cc66;">/dicom#66cc66;">/anonymity_1_0 NO ORDSYS http:#66cc66;">//xmlns#66cc66;">.oracle#66cc66;">.com#66cc66;">/ord#66cc66;">/meta#66cc66;">/xmp NO ORDSYS http:#66cc66;">//xmlns#66cc66;">.oracle#66cc66;">.com#66cc66;">/ord#66cc66;">/dicom#66cc66;">/standardDictionary_1_0 NO ORDSYS http:#66cc66;">//xmlns#66cc66;">.oracle#66cc66;">.com#66cc66;">/ord#66cc66;">/meta#66cc66;">/iptc NO ORDSYS http:#66cc66;">//xmlns#66cc66;">.oracle#66cc66;">.com#66cc66;">/ord#66cc66;">/dicom#66cc66;">/mddatatype_1_0 NO ORDSYS http:#66cc66;">//xmlns#66cc66;">.oracle#66cc66;">.com#66cc66;">/ord#66cc66;">/dicom#66cc66;">/orddicom_1_0 NO ORDSYS http:#66cc66;">//xmlns#66cc66;">.oracle#66cc66;">.com#66cc66;">/ord#66cc66;">/dicom#66cc66;">/datatype_1_0 NO ORDSYS http:#66cc66;">//xmlns#66cc66;">.oracle#66cc66;">.com#66cc66;">/ord#66cc66;">/dicom#66cc66;">/privateDictionary_1_0 NO ORDSYS http:#66cc66;">//xmlns#66cc66;">.oracle#66cc66;">.com#66cc66;">/ord#66cc66;">/meta#66cc66;">/exif NO ORDSYS http:#66cc66;">//xmlns#66cc66;">.oracle#66cc66;">.com#66cc66;">/ord#66cc66;">/dicom#66cc66;">/rpdatatype_1_0 NO ORDSYS http:#66cc66;">//xmlns#66cc66;">.oracle#66cc66;">.com#66cc66;">/ord#66cc66;">/meta#66cc66;">/ordimage NO ORDSYS http:#66cc66;">//xmlns#66cc66;">.oracle#66cc66;">.com#66cc66;">/ord#66cc66;">/dicom#66cc66;">/UIDdefinition_1_0 NO ORDSYS http:#66cc66;">//xmlns#66cc66;">.oracle#66cc66;">.com#66cc66;">/ord#66cc66;">/dicom#66cc66;">/manifest_1_0 NO ORDSYS http:#66cc66;">//xmlns#66cc66;">.oracle#66cc66;">.com#66cc66;">/ord#66cc66;">/dicom#66cc66;">/attributeTag_1_0 NO #66cc66;">... XDB http:#66cc66;">//xmlns#66cc66;">.oracle#66cc66;">.com#66cc66;">/xdb#66cc66;">/XDBStandard#66cc66;">.xsd NO XDB http:#66cc66;">//xmlns#66cc66;">.oracle#66cc66;">.com#66cc66;">/xdb#66cc66;">/XDBResConfig#66cc66;">.xsd NO XDB http:#66cc66;">//xmlns#66cc66;">.oracle#66cc66;">.com#66cc66;">/xdb#66cc66;">/acl#66cc66;">.xsd NO XDB http:#66cc66;">//xmlns#66cc66;">.oracle#66cc66;">.com#66cc66;">/xdb#66cc66;">/dav#66cc66;">.xsd NO #cc66cc;">51 rows selected#66cc66;">.
…and due to the fact you can query it, you can create a view out of it…
SQL#66cc66;">> #993333; font-weight: bold;">CREATE #993333; font-weight: bold;">OR #993333; font-weight: bold;">REPLACE #993333; font-weight: bold;">VIEW MY_EXTERNAL_XFILE #cc66cc;">2 #66cc66;">( OWNER #cc66cc;">3 #66cc66;">, SCHEMA #cc66cc;">4 #66cc66;">, MINE #cc66cc;">5 #66cc66;">) #cc66cc;">6 #993333; font-weight: bold;">AS #cc66cc;">7 #993333; font-weight: bold;">SELECT xtab#66cc66;">.owner #cc66cc;">8 #66cc66;">, xtab#66cc66;">.schema_url #cc66cc;">9 #66cc66;">, xtab#66cc66;">.#993333; font-weight: bold;">LOCAL #cc66cc;">10 #993333; font-weight: bold;">FROM #66cc66;">(#993333; font-weight: bold;">SELECT xmltype#66cc66;">(bfilename#66cc66;">(#ff0000;">'TMPDIR'#66cc66;">,#ff0000;">'otn_dev_xsd_schema.xml'#66cc66;">)#66cc66;">, nls_charset_id#66cc66;">(#ff0000;">'AL32UTF8'#66cc66;">)#66cc66;">) OBJECT_VALUE #993333; font-weight: bold;">FROM dual#66cc66;">) xfile #cc66cc;">11 #66cc66;">, XMLTABLE #66cc66;">(#ff0000;">'/ROWSET/ROW' #cc66cc;">12 PASSING xfile#66cc66;">.object_value #cc66cc;">13 #993333; font-weight: bold;">COLUMNS OWNER VARCHAR2#66cc66;">(#cc66cc;">30#66cc66;">) path #ff0000;">'OWNER' #cc66cc;">14 #66cc66;">, SCHEMA_URL VARCHAR2#66cc66;">(#cc66cc;">80#66cc66;">) path #ff0000;">'SCHEMA_URL' #cc66cc;">15 #66cc66;">, #993333; font-weight: bold;">LOCAL VARCHAR2#66cc66;">(#cc66cc;">3#66cc66;">) path #ff0000;">'LOCAL' #cc66cc;">16 #66cc66;">) xtab #cc66cc;">17 ; #993333; font-weight: bold;">VIEW created#66cc66;">. SQL#66cc66;">> #993333; font-weight: bold;">DESC my_external_xfile Name #993333; font-weight: bold;">NULL? Type #808080; font-style: italic;">----------------------------------------- -------- ---------------------------- OWNER VARCHAR2#66cc66;">(#cc66cc;">30#66cc66;">) SCHEMA VARCHAR2#66cc66;">(#cc66cc;">80#66cc66;">) MINE VARCHAR2#66cc66;">(#cc66cc;">3#66cc66;">) SQL#66cc66;">> #993333; font-weight: bold;">SELECT #66cc66;">* #993333; font-weight: bold;">FROM MY_EXTERNAL_XFILE #cc66cc;">2 ; OWNER SCHEMA MIN #808080; font-style: italic;">------------------------------ -------------------------------------------------------------------------------- --- EXFSYS http:#66cc66;">//xmlns#66cc66;">.oracle#66cc66;">.com#66cc66;">/rlmgr#66cc66;">/rclsprop#66cc66;">.xsd NO EXFSYS http:#66cc66;">//xmlns#66cc66;">.oracle#66cc66;">.com#66cc66;">/rlmgr#66cc66;">/rulecond#66cc66;">.xsd NO MDSYS http:#66cc66;">//www#66cc66;">.opengis#66cc66;">.net#66cc66;">/gml#66cc66;">/feature#66cc66;">.xsd NO MDSYS http:#66cc66;">//xmlns#66cc66;">.oracle#66cc66;">.com#66cc66;">/spatial#66cc66;">/georaster#66cc66;">/georaster#66cc66;">.xsd NO MDSYS http:#66cc66;">//www#66cc66;">.w3#66cc66;">.org#66cc66;">/#cc66cc;">1999#66cc66;">/xlink#66cc66;">/xlinks#66cc66;">.xsd NO MDSYS http:#66cc66;">//www#66cc66;">.opengis#66cc66;">.net#66cc66;">/gml#66cc66;">/geometry#66cc66;">.xsd NO ORDSYS http:#66cc66;">//xmlns#66cc66;">.oracle#66cc66;">.com#66cc66;">/ord#66cc66;">/dicom#66cc66;">/preference_1_0 NO ORDSYS http:#66cc66;">//xmlns#66cc66;">.oracle#66cc66;">.com#66cc66;">/ord#66cc66;">/dicom#66cc66;">/mapping_1_0 NO ORDSYS http:#66cc66;">//xmlns#66cc66;">.oracle#66cc66;">.com#66cc66;">/ord#66cc66;">/dicom#66cc66;">/metadata_1_0 NO ORDSYS http:#66cc66;">//xmlns#66cc66;">.oracle#66cc66;">.com#66cc66;">/ord#66cc66;">/dicom#66cc66;">/constraint_1_0 NO ORDSYS http:#66cc66;">//xmlns#66cc66;">.oracle#66cc66;">.com#66cc66;">/ord#66cc66;">/dicom#66cc66;">/anonymity_1_0 NO #66cc66;">... XDB http:#66cc66;">//xmlns#66cc66;">.oracle#66cc66;">.com#66cc66;">/xdb#66cc66;">/XDBStandard#66cc66;">.xsd NO XDB http:#66cc66;">//xmlns#66cc66;">.oracle#66cc66;">.com#66cc66;">/xdb#66cc66;">/XDBResConfig#66cc66;">.xsd NO XDB http:#66cc66;">//xmlns#66cc66;">.oracle#66cc66;">.com#66cc66;">/xdb#66cc66;">/acl#66cc66;">.xsd NO XDB http:#66cc66;">//xmlns#66cc66;">.oracle#66cc66;">.com#66cc66;">/xdb#66cc66;">/dav#66cc66;">.xsd NO #cc66cc;">51 rows selected#66cc66;">.
So now we have a “standard” relational view that we can query, for example, show me all “w3″ based in the XDB Repository registered XML Schemas which are not mine, but accessible for all in the database, via…
SQL#66cc66;">> #993333; font-weight: bold;">SELECT #66cc66;">* #993333; font-weight: bold;">FROM MY_EXTERNAL_XFILE #993333; font-weight: bold;">WHERE owner#66cc66;">=#ff0000;">'XDB' #cc66cc;">2 #993333; font-weight: bold;">AND instr#66cc66;">(SCHEMA#66cc66;">,#ff0000;">'w3'#66cc66;">) #66cc66;">> #cc66cc;">0 #cc66cc;">3 ; OWNER SCHEMA MIN #808080; font-style: italic;">------------------------------ -------------------------------------------------------------------------------- --- XDB http:#66cc66;">//www#66cc66;">.w3#66cc66;">.org#66cc66;">/#cc66cc;">2001#66cc66;">/csx#66cc66;">.xml#66cc66;">.xsd NO XDB http:#66cc66;">//www#66cc66;">.w3#66cc66;">.org#66cc66;">/#cc66cc;">2001#66cc66;">/csx#66cc66;">.XInclude#66cc66;">.xsd NO XDB http:#66cc66;">//www#66cc66;">.w3#66cc66;">.org#66cc66;">/#cc66cc;">2001#66cc66;">/XInclude#66cc66;">.xsd NO XDB http:#66cc66;">//www#66cc66;">.w3#66cc66;">.org#66cc66;">/#cc66cc;">1999#66cc66;">/csx#66cc66;">.xlink#66cc66;">.xsd NO XDB http:#66cc66;">//www#66cc66;">.w3#66cc66;">.org#66cc66;">/#cc66cc;">1999#66cc66;">/xlink#66cc66;">.xsd NO XDB http:#66cc66;">//www#66cc66;">.w3#66cc66;">.org#66cc66;">/#cc66cc;">2001#66cc66;">/xml#66cc66;">.xsd NO #cc66cc;">6 rows selected#66cc66;">.
Price to pay…?
As said, it comes with a price to pay, regarding I/O and other performance issues. For example check out the following stats, initially via “explain plan”…
SQL#66cc66;">> conn #66cc66;">/ #993333; font-weight: bold;">AS sysdba Connected#66cc66;">. SQL#66cc66;">> startup ORACLE instance started#66cc66;">. Total System Global Area #cc66cc;">313860096 bytes Fixed Size #cc66cc;">1336232 bytes Variable Size #cc66cc;">251661400 bytes #993333; font-weight: bold;">DATABASE Buffers #cc66cc;">54525952 bytes Redo Buffers #cc66cc;">6336512 bytes #993333; font-weight: bold;">DATABASE mounted#66cc66;">. #993333; font-weight: bold;">DATABASE opened#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;">SET autotrace #993333; font-weight: bold;">ON SQL#66cc66;">> #993333; font-weight: bold;">SELECT #66cc66;">* #993333; font-weight: bold;">FROM MY_EXTERNAL_XFILE #993333; font-weight: bold;">WHERE owner#66cc66;">=#ff0000;">'MDSYS' #cc66cc;">2 #993333; font-weight: bold;">AND instr#66cc66;">(SCHEMA#66cc66;">,#ff0000;">'w3'#66cc66;">) #66cc66;">> #cc66cc;">0; OWNER SCHEMA MIN #808080; font-style: italic;">------------------------------ -------------------------------------------------------------------------------- --- MDSYS http:#66cc66;">//www#66cc66;">.w3#66cc66;">.org#66cc66;">/#cc66cc;">1999#66cc66;">/xlink#66cc66;">/xlinks#66cc66;">.xsd NO Execution Plan #808080; font-style: italic;">---------------------------------------------------------- Plan hash value: #cc66cc;">2888251253 #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;">4 #66cc66;">| #cc66cc;">8 #66cc66;">| #cc66cc;">44 #66cc66;">(#cc66cc;">30#66cc66;">)#66cc66;">| 00:00:01 #66cc66;">| #66cc66;">| #cc66cc;">1 #66cc66;">| NESTED LOOPS #66cc66;">| #66cc66;">| #cc66cc;">4 #66cc66;">| #cc66cc;">8 #66cc66;">| #cc66cc;">44 #66cc66;">(#cc66cc;">30#66cc66;">)#66cc66;">| 00:00:01 #66cc66;">| #66cc66;">| #cc66cc;">2 #66cc66;">| FAST DUAL #66cc66;">| #66cc66;">| #cc66cc;">1 #66cc66;">| #66cc66;">| #cc66cc;">2 #66cc66;">(#cc66cc;">0#66cc66;">)#66cc66;">| 00:00:01 #66cc66;">| #66cc66;">|* #cc66cc;">3 #66cc66;">| COLLECTION ITERATOR PICKLER FETCH#66cc66;">| XMLSEQUENCEFROMXMLTYPE #66cc66;">| #cc66cc;">4 #66cc66;">| #cc66cc;">8 #66cc66;">| #cc66cc;">42 #66cc66;">(#cc66cc;">31#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;">3 #66cc66;">- filter#66cc66;">(CAST#66cc66;">(SYS_XQ_UPKXML2SQL#66cc66;">(SYS_XQEXVAL#66cc66;">(SYS_XQEXTRACT#66cc66;">(VALUE#66cc66;">(KOKBF$#66cc66;">)#66cc66;">,#ff0000;">'/ROW/OWNER'#66cc66;">)#66cc66;">,#cc66cc;">0#66cc66;">,#cc66cc;">0#66cc66;">,#cc66cc;">209715 #cc66cc;">20#66cc66;">,#cc66cc;">0#66cc66;">)#66cc66;">,#cc66cc;">50#66cc66;">,#cc66cc;">1#66cc66;">,#cc66cc;">2#66cc66;">) #993333; font-weight: bold;">AS VARCHAR2#66cc66;">(#cc66cc;">30#66cc66;">) #66cc66;">)#66cc66;">=#ff0000;">'MDSYS' #993333; font-weight: bold;">AND INSTR#66cc66;">(CAST#66cc66;">(SYS_XQ_UPKXML2SQL#66cc66;">(SYS_XQEXVAL#66cc66;">(SYS_XQEXTRACT#66cc66;">(VA LUE#66cc66;">(KOKBF$#66cc66;">)#66cc66;">,#ff0000;">'/ROW/SCHEMA_URL'#66cc66;">)#66cc66;">,#cc66cc;">0#66cc66;">,#cc66cc;">0#66cc66;">,#cc66cc;">20971520#66cc66;">,#cc66cc;">0#66cc66;">)#66cc66;">,#cc66cc;">50#66cc66;">,#cc66cc;">1#66cc66;">,#cc66cc;">2#66cc66;">) #993333; font-weight: bold;">AS VARCHAR2#66cc66;">(#cc66cc;">80#66cc66;">) #66cc66;">)#66cc66;">,#ff0000;">'w3'#66cc66;">)#66cc66;">>#cc66cc;">0#66cc66;">) Statistics #808080; font-style: italic;">---------------------------------------------------------- #cc66cc;">3624 recursive calls #cc66cc;">0 db block gets #cc66cc;">2888 consistent gets #cc66cc;">105 physical reads #cc66cc;">0 redo size #cc66cc;">583 bytes sent via SQL#66cc66;">*Net #993333; font-weight: bold;">TO client #cc66cc;">419 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;">143 sorts #66cc66;">(memory#66cc66;">) #cc66cc;">0 sorts #66cc66;">(disk#66cc66;">) #cc66cc;">1 rows processed SQL#66cc66;">> #993333; font-weight: bold;">SELECT #66cc66;">* #993333; font-weight: bold;">FROM MY_EXTERNAL_XFILE #993333; font-weight: bold;">WHERE owner#66cc66;">=#ff0000;">'MDSYS' #cc66cc;">2 #993333; font-weight: bold;">AND instr#66cc66;">(SCHEMA#66cc66;">,#ff0000;">'w3'#66cc66;">) #66cc66;">> #cc66cc;">0; OWNER SCHEMA MIN #808080; font-style: italic;">------------------------------ -------------------------------------------------------------------------------- --- MDSYS http:#66cc66;">//www#66cc66;">.w3#66cc66;">.org#66cc66;">/#cc66cc;">1999#66cc66;">/xlink#66cc66;">/xlinks#66cc66;">.xsd NO Execution Plan #808080; font-style: italic;">---------------------------------------------------------- Plan hash value: #cc66cc;">2888251253 #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;">4 #66cc66;">| #cc66cc;">8 #66cc66;">| #cc66cc;">44 #66cc66;">(#cc66cc;">30#66cc66;">)#66cc66;">| 00:00:01 #66cc66;">| #66cc66;">| #cc66cc;">1 #66cc66;">| NESTED LOOPS #66cc66;">| #66cc66;">| #cc66cc;">4 #66cc66;">| #cc66cc;">8 #66cc66;">| #cc66cc;">44 #66cc66;">(#cc66cc;">30#66cc66;">)#66cc66;">| 00:00:01 #66cc66;">| #66cc66;">| #cc66cc;">2 #66cc66;">| FAST DUAL #66cc66;">| #66cc66;">| #cc66cc;">1 #66cc66;">| #66cc66;">| #cc66cc;">2 #66cc66;">(#cc66cc;">0#66cc66;">)#66cc66;">| 00:00:01 #66cc66;">| #66cc66;">|* #cc66cc;">3 #66cc66;">| COLLECTION ITERATOR PICKLER FETCH#66cc66;">| XMLSEQUENCEFROMXMLTYPE #66cc66;">| #cc66cc;">4 #66cc66;">| #cc66cc;">8 #66cc66;">| #cc66cc;">42 #66cc66;">(#cc66cc;">31#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;">3 #66cc66;">- filter#66cc66;">(CAST#66cc66;">(SYS_XQ_UPKXML2SQL#66cc66;">(SYS_XQEXVAL#66cc66;">(SYS_XQEXTRACT#66cc66;">(VALUE#66cc66;">(KOKBF$#66cc66;">)#66cc66;">,#ff0000;">'/ROW/OWNER'#66cc66;">)#66cc66;">,#cc66cc;">0#66cc66;">,#cc66cc;">0#66cc66;">,#cc66cc;">209715 #cc66cc;">20#66cc66;">,#cc66cc;">0#66cc66;">)#66cc66;">,#cc66cc;">50#66cc66;">,#cc66cc;">1#66cc66;">,#cc66cc;">2#66cc66;">) #993333; font-weight: bold;">AS VARCHAR2#66cc66;">(#cc66cc;">30#66cc66;">) #66cc66;">)#66cc66;">=#ff0000;">'MDSYS' #993333; font-weight: bold;">AND INSTR#66cc66;">(CAST#66cc66;">(SYS_XQ_UPKXML2SQL#66cc66;">(SYS_XQEXVAL#66cc66;">(SYS_XQEXTRACT#66cc66;">(VA LUE#66cc66;">(KOKBF$#66cc66;">)#66cc66;">,#ff0000;">'/ROW/SCHEMA_URL'#66cc66;">)#66cc66;">,#cc66cc;">0#66cc66;">,#cc66cc;">0#66cc66;">,#cc66cc;">20971520#66cc66;">,#cc66cc;">0#66cc66;">)#66cc66;">,#cc66cc;">50#66cc66;">,#cc66cc;">1#66cc66;">,#cc66cc;">2#66cc66;">) #993333; font-weight: bold;">AS VARCHAR2#66cc66;">(#cc66cc;">80#66cc66;">) #66cc66;">)#66cc66;">,#ff0000;">'w3'#66cc66;">)#66cc66;">>#cc66cc;">0#66cc66;">) Statistics #808080; font-style: italic;">---------------------------------------------------------- #cc66cc;">0 recursive calls #cc66cc;">0 db block gets #cc66cc;">3 consistent gets #cc66cc;">0 physical reads #cc66cc;">0 redo size #cc66cc;">583 bytes sent via SQL#66cc66;">*Net #993333; font-weight: bold;">TO client #cc66cc;">419 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;">1 rows processed
…and a second time via the same “procedure” checking via the stats via DBMS_XPLAN…
SQL#66cc66;">> startup force ORACLE instance started#66cc66;">. Total System Global Area #cc66cc;">313860096 bytes Fixed Size #cc66cc;">1336232 bytes Variable Size #cc66cc;">251661400 bytes #993333; font-weight: bold;">DATABASE Buffers #cc66cc;">54525952 bytes Redo Buffers #cc66cc;">6336512 bytes #993333; font-weight: bold;">DATABASE mounted#66cc66;">. #993333; font-weight: bold;">DATABASE opened#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;">> conn otn#66cc66;">/otn Connected#66cc66;">. SQL#66cc66;">> #993333; font-weight: bold;">ALTER session #993333; font-weight: bold;">SET statistics_level#66cc66;">=#993333; font-weight: bold;">ALL; Session altered#66cc66;">. SQL#66cc66;">> #993333; font-weight: bold;">CREATE #993333; font-weight: bold;">OR #993333; font-weight: bold;">REPLACE #993333; font-weight: bold;">VIEW xplan #993333; font-weight: bold;">AS #993333; font-weight: bold;">SELECT #66cc66;">* #993333; font-weight: bold;">FROM #993333; font-weight: bold;">TABLE#66cc66;">(dbms_xplan#66cc66;">.display_cursor#66cc66;">(#993333; font-weight: bold;">NULL#66cc66;">,#993333; font-weight: bold;">NULL#66cc66;">,#ff0000;">'ALLSTATS LAST'#66cc66;">)#66cc66;">); #993333; font-weight: bold;">VIEW created#66cc66;">. SQL#66cc66;">> #993333; font-weight: bold;">SELECT #66cc66;">* #993333; font-weight: bold;">FROM MY_EXTERNAL_XFILE #993333; font-weight: bold;">WHERE owner#66cc66;">=#ff0000;">'MDSYS' #cc66cc;">2 #993333; font-weight: bold;">AND instr#66cc66;">(SCHEMA#66cc66;">,#ff0000;">'w3'#66cc66;">) #66cc66;">> #cc66cc;">0; SQL#66cc66;">> #993333; font-weight: bold;">SELECT #66cc66;">* #993333; font-weight: bold;">FROM MY_EXTERNAL_XFILE #993333; font-weight: bold;">WHERE owner#66cc66;">=#ff0000;">'MDSYS' #cc66cc;">2 #993333; font-weight: bold;">AND instr#66cc66;">(SCHEMA#66cc66;">,#ff0000;">'w3'#66cc66;">) #66cc66;">> #cc66cc;">0; OWNER SCHEMA MIN #808080; font-style: italic;">------------------------------ -------------------------------------------------------------------------------- --- MDSYS http:#66cc66;">//www#66cc66;">.w3#66cc66;">.org#66cc66;">/#cc66cc;">1999#66cc66;">/xlink#66cc66;">/xlinks#66cc66;">.xsd NO SQL#66cc66;">> #993333; font-weight: bold;">SELECT #66cc66;">* #993333; font-weight: bold;">FROM xplan; PLAN_TABLE_OUTPUT #808080; font-style: italic;">-------------------------------------------------------------------------------------------------------------------------------- SQL_ID 9kc32mbtas765#66cc66;">, child number #cc66cc;">0 #808080; font-style: italic;">------------------------------------- #993333; font-weight: bold;">SELECT #66cc66;">* #993333; font-weight: bold;">FROM MY_EXTERNAL_XFILE #993333; font-weight: bold;">WHERE owner#66cc66;">=#ff0000;">'MDSYS' #993333; font-weight: bold;">AND instr#66cc66;">(SCHEMA#66cc66;">,#ff0000;">'w3'#66cc66;">) #66cc66;">> #cc66cc;">0 Plan hash value: #cc66cc;">2888251253 #808080; font-style: italic;">-------------------------------------------------------------------------------------------------------------------------------- #66cc66;">| Id #66cc66;">| Operation #66cc66;">| Name #66cc66;">| Starts #66cc66;">| E#66cc66;">-Rows #66cc66;">| A#66cc66;">-Rows #66cc66;">| A#66cc66;">-Time #66cc66;">| Buffers #66cc66;">| Reads #66cc66;">| #808080; font-style: italic;">-------------------------------------------------------------------------------------------------------------------------------- #66cc66;">| #cc66cc;">0 #66cc66;">| #993333; font-weight: bold;">SELECT STATEMENT #66cc66;">| #66cc66;">| #cc66cc;">1 #66cc66;">| #66cc66;">| #cc66cc;">1 #66cc66;">|00:00:#cc66cc;">00.09 #66cc66;">| #cc66cc;">959 #66cc66;">| #cc66cc;">52 #66cc66;">| #66cc66;">| #cc66cc;">1 #66cc66;">| NESTED LOOPS #66cc66;">| #66cc66;">| #cc66cc;">1 #66cc66;">| #cc66cc;">4 #66cc66;">| #cc66cc;">1 #66cc66;">|00:00:#cc66cc;">00.09 #66cc66;">| #cc66cc;">959 #66cc66;">| #cc66cc;">52 #66cc66;">| #66cc66;">| #cc66cc;">2 #66cc66;">| FAST DUAL #66cc66;">| #66cc66;">| #cc66cc;">1 #66cc66;">| #cc66cc;">1 #66cc66;">| #cc66cc;">1 #66cc66;">|00:00:#cc66cc;">00.01 #66cc66;">| #cc66cc;">0 #66cc66;">| #cc66cc;">0 #66cc66;">| #66cc66;">|* #cc66cc;">3 #66cc66;">| COLLECTION ITERATOR PICKLER FETCH#66cc66;">| XMLSEQUENCEFROMXMLTYPE #66cc66;">| #cc66cc;">1 #66cc66;">| #cc66cc;">4 #66cc66;">| #cc66cc;">1 #66cc66;">|00:00:#cc66cc;">00.08 #66cc66;">| #cc66cc;">959 #66cc66;">| #cc66cc;">52 #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;">3 #66cc66;">- filter#66cc66;">(#66cc66;">(CAST#66cc66;">(SYS_XQ_UPKXML2SQL#66cc66;">(SYS_XQEXVAL#66cc66;">(SYS_XQEXTRACT#66cc66;">(VALUE#66cc66;">(KOKBF$#66cc66;">)#66cc66;">,#ff0000;">'/ROW/OWNER'#66cc66;">)#66cc66;">,#cc66cc;">0#66cc66;">,#cc66cc;">0#66cc66;">,#cc66cc;">20971520#66cc66;">,#cc66cc;">0#66cc66;">)#66cc66;">,#cc66cc;">50#66cc66;">,#cc66cc;">1#66cc66;">,#cc66cc;">2#66cc66;">) #993333; font-weight: bold;">AS VARCHAR2#66cc66;">(#cc66cc;">30#66cc66;">) #66cc66;">)#66cc66;">=#ff0000;">'MDSYS' #993333; font-weight: bold;">AND INSTR#66cc66;">(CAST#66cc66;">(SYS_XQ_UPKXML2SQL#66cc66;">(SYS_XQEXVAL#66cc66;">(SYS_XQEXTRACT#66cc66;">(VALUE#66cc66;">(KOKBF$#66cc66;">)#66cc66;">,#ff0000;">'/ROW/SCHEMA_URL'#66cc66;">)#66cc66;">,#cc66cc;">0#66cc66;">,#cc66cc;">0#66cc66;">,#cc66cc;">2 0971520#66cc66;">,#cc66cc;">0#66cc66;">)#66cc66;">,#cc66cc;">50#66cc66;">,#cc66cc;">1#66cc66;">,#cc66cc;">2#66cc66;">) #993333; font-weight: bold;">AS VARCHAR2#66cc66;">(#cc66cc;">80#66cc66;">) #66cc66;">)#66cc66;">,#ff0000;">'w3'#66cc66;">)#66cc66;">>#cc66cc;">0#66cc66;">)#66cc66;">) #cc66cc;">23 rows selected#66cc66;">. SQL#66cc66;">> #993333; font-weight: bold;">SELECT #66cc66;">* #993333; font-weight: bold;">FROM MY_EXTERNAL_XFILE #993333; font-weight: bold;">WHERE owner#66cc66;">=#ff0000;">'MDSYS' #cc66cc;">2 #993333; font-weight: bold;">AND instr#66cc66;">(SCHEMA#66cc66;">,#ff0000;">'w3'#66cc66;">) #66cc66;">> #cc66cc;">0; OWNER SCHEMA MIN #808080; font-style: italic;">------------------------------ -------------------------------------------------------------------------------- --- MDSYS http:#66cc66;">//www#66cc66;">.w3#66cc66;">.org#66cc66;">/#cc66cc;">1999#66cc66;">/xlink#66cc66;">/xlinks#66cc66;">.xsd NO SQL#66cc66;">> #993333; font-weight: bold;">SELECT #66cc66;">* #993333; font-weight: bold;">FROM xplan; PLAN_TABLE_OUTPUT #808080; font-style: italic;">----------------------------------------------------------------------------------------------------------------------- SQL_ID 9kc32mbtas765#66cc66;">, child number #cc66cc;">0 #808080; font-style: italic;">------------------------------------- #993333; font-weight: bold;">SELECT #66cc66;">* #993333; font-weight: bold;">FROM MY_EXTERNAL_XFILE #993333; font-weight: bold;">WHERE owner#66cc66;">=#ff0000;">'MDSYS' #993333; font-weight: bold;">AND instr#66cc66;">(SCHEMA#66cc66;">,#ff0000;">'w3'#66cc66;">) #66cc66;">> #cc66cc;">0 Plan hash value: #cc66cc;">2888251253 #808080; font-style: italic;">----------------------------------------------------------------------------------------------------------------------- #66cc66;">| Id #66cc66;">| Operation #66cc66;">| Name #66cc66;">| Starts #66cc66;">| E#66cc66;">-Rows #66cc66;">| A#66cc66;">-Rows #66cc66;">| A#66cc66;">-Time #66cc66;">| Buffers #66cc66;">| #808080; font-style: italic;">----------------------------------------------------------------------------------------------------------------------- #66cc66;">| #cc66cc;">0 #66cc66;">| #993333; font-weight: bold;">SELECT STATEMENT #66cc66;">| #66cc66;">| #cc66cc;">1 #66cc66;">| #66cc66;">| #cc66cc;">1 #66cc66;">|00:00:#cc66cc;">00.01 #66cc66;">| #cc66cc;">3 #66cc66;">| #66cc66;">| #cc66cc;">1 #66cc66;">| NESTED LOOPS #66cc66;">| #66cc66;">| #cc66cc;">1 #66cc66;">| #cc66cc;">4 #66cc66;">| #cc66cc;">1 #66cc66;">|00:00:#cc66cc;">00.01 #66cc66;">| #cc66cc;">3 #66cc66;">| #66cc66;">| #cc66cc;">2 #66cc66;">| FAST DUAL #66cc66;">| #66cc66;">| #cc66cc;">1 #66cc66;">| #cc66cc;">1 #66cc66;">| #cc66cc;">1 #66cc66;">|00:00:#cc66cc;">00.01 #66cc66;">| #cc66cc;">0 #66cc66;">| #66cc66;">|* #cc66cc;">3 #66cc66;">| COLLECTION ITERATOR PICKLER FETCH#66cc66;">| XMLSEQUENCEFROMXMLTYPE #66cc66;">| #cc66cc;">1 #66cc66;">| #cc66cc;">4 #66cc66;">| #cc66cc;">1 #66cc66;">|00:00:#cc66cc;">00.01 #66cc66;">| #cc66cc;">3 #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;">3 #66cc66;">- filter#66cc66;">(#66cc66;">(CAST#66cc66;">(SYS_XQ_UPKXML2SQL#66cc66;">(SYS_XQEXVAL#66cc66;">(SYS_XQEXTRACT#66cc66;">(VALUE#66cc66;">(KOKBF$#66cc66;">)#66cc66;">,#ff0000;">'/ROW/OWNER'#66cc66;">)#66cc66;">,#cc66cc;">0#66cc66;">,#cc66cc;">0#66cc66;">,#cc66cc;">20971520#66cc66;">,#cc66cc;">0#66cc66;">)#66cc66;">,#cc66cc;">50#66cc66;">, #cc66cc;">1#66cc66;">,#cc66cc;">2#66cc66;">) #993333; font-weight: bold;">AS VARCHAR2#66cc66;">(#cc66cc;">30#66cc66;">) #66cc66;">)#66cc66;">=#ff0000;">'MDSYS' #993333; font-weight: bold;">AND INSTR#66cc66;">(CAST#66cc66;">(SYS_XQ_UPKXML2SQL#66cc66;">(SYS_XQEXVAL#66cc66;">(SYS_XQEXTRACT#66cc66;">(VALUE#66cc66;">(KOKBF$#66cc66;">)#66cc66;">,#ff0000;">'/ROW/S CHEMA_URL'#66cc66;">)#66cc66;">,#cc66cc;">0#66cc66;">,#cc66cc;">0#66cc66;">,#cc66cc;">20971520#66cc66;">,#cc66cc;">0#66cc66;">)#66cc66;">,#cc66cc;">50#66cc66;">,#cc66cc;">1#66cc66;">,#cc66cc;">2#66cc66;">) #993333; font-weight: bold;">AS VARCHAR2#66cc66;">(#cc66cc;">80#66cc66;">) #66cc66;">)#66cc66;">,#ff0000;">'w3'#66cc66;">)#66cc66;">>#cc66cc;">0#66cc66;">)#66cc66;">) #cc66cc;">23 rows selected#66cc66;">.
As said before, initially Oracle has to do a lot of disk reads and all has to be done via “COLLECTION ITERATOR PICKLER FETCH”, which means in memory, but although in memory, Oracle is able to rewrite it via the XQuery engine (mark the “XQ” in the filter statements) to their relational counter parts due to the fact that Oracle got this information via the XMLTABLE function definition.
The second time around everything has been cached. See the following output via all statistics / DBMS_XPLAN…
SQL#66cc66;">> #993333; font-weight: bold;">SELECT #66cc66;">* #993333; font-weight: bold;">FROM MY_EXTERNAL_XFILE #993333; font-weight: bold;">WHERE owner#66cc66;">=#ff0000;">'XDB' #cc66cc;">2 #993333; font-weight: bold;">AND instr#66cc66;">(SCHEMA#66cc66;">,#ff0000;">'w3'#66cc66;">) #66cc66;">= #cc66cc;">0 #cc66cc;">3 ; OWNER SCHEMA MIN #808080; font-style: italic;">------------------------------ -------------------------------------------------------------------------------- --- XDB http:#66cc66;">//xmlns#66cc66;">.oracle#66cc66;">.com#66cc66;">/xdb#66cc66;">/csx#66cc66;">.xmltr#66cc66;">.xsd NO XDB http:#66cc66;">//xmlns#66cc66;">.oracle#66cc66;">.com#66cc66;">/xdb#66cc66;">/XDBResource#66cc66;">.xsd NO XDB http:#66cc66;">//xmlns#66cc66;">.oracle#66cc66;">.com#66cc66;">/xdb#66cc66;">/XDBSchema#66cc66;">.xsd NO XDB http:#66cc66;">//xmlns#66cc66;">.oracle#66cc66;">.com#66cc66;">/xs#66cc66;">/principal#66cc66;">.xsd NO XDB http:#66cc66;">//xmlns#66cc66;">.oracle#66cc66;">.com#66cc66;">/xs#66cc66;">/aclids#66cc66;">.xsd NO XDB http:#66cc66;">//xmlns#66cc66;">.oracle#66cc66;">.com#66cc66;">/xs#66cc66;">/dataSecurity#66cc66;">.xsd NO XDB http:#66cc66;">//xmlns#66cc66;">.oracle#66cc66;">.com#66cc66;">/xdb#66cc66;">/xdbconfig#66cc66;">.xsd NO XDB http:#66cc66;">//xmlns#66cc66;">.oracle#66cc66;">.com#66cc66;">/xs#66cc66;">/securityclass#66cc66;">.xsd NO XDB http:#66cc66;">//xmlns#66cc66;">.oracle#66cc66;">.com#66cc66;">/xs#66cc66;">/roleset#66cc66;">.xsd NO XDB http:#66cc66;">//xmlns#66cc66;">.oracle#66cc66;">.com#66cc66;">/xdb#66cc66;">/stats#66cc66;">.xsd NO XDB http:#66cc66;">//xmlns#66cc66;">.oracle#66cc66;">.com#66cc66;">/xdb#66cc66;">/XDBFolderListing#66cc66;">.xsd NO XDB http:#66cc66;">//xmlns#66cc66;">.oracle#66cc66;">.com#66cc66;">/xdb#66cc66;">/xmltr#66cc66;">.xsd NO XDB http:#66cc66;">//xmlns#66cc66;">.oracle#66cc66;">.com#66cc66;">/xdb#66cc66;">/log#66cc66;">/httplog#66cc66;">.xsd NO XDB http:#66cc66;">//xmlns#66cc66;">.oracle#66cc66;">.com#66cc66;">/xdb#66cc66;">/log#66cc66;">/ftplog#66cc66;">.xsd NO XDB http:#66cc66;">//xmlns#66cc66;">.oracle#66cc66;">.com#66cc66;">/xdb#66cc66;">/log#66cc66;">/xdblog#66cc66;">.xsd NO XDB http:#66cc66;">//xmlns#66cc66;">.oracle#66cc66;">.com#66cc66;">/xdb#66cc66;">/XDBStandard#66cc66;">.xsd NO XDB http:#66cc66;">//xmlns#66cc66;">.oracle#66cc66;">.com#66cc66;">/xdb#66cc66;">/XDBResConfig#66cc66;">.xsd NO XDB http:#66cc66;">//xmlns#66cc66;">.oracle#66cc66;">.com#66cc66;">/xdb#66cc66;">/acl#66cc66;">.xsd NO XDB http:#66cc66;">//xmlns#66cc66;">.oracle#66cc66;">.com#66cc66;">/xdb#66cc66;">/dav#66cc66;">.xsd NO #cc66cc;">19 rows selected#66cc66;">. SQL#66cc66;">> #993333; font-weight: bold;">SELECT #66cc66;">* #993333; font-weight: bold;">FROM xplan; PLAN_TABLE_OUTPUT #808080; font-style: italic;">-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID bjx9wfv2hp6u7#66cc66;">, child number #cc66cc;">0 #808080; font-style: italic;">------------------------------------- #993333; font-weight: bold;">SELECT #66cc66;">* #993333; font-weight: bold;">FROM MY_EXTERNAL_XFILE #993333; font-weight: bold;">WHERE owner#66cc66;">=#ff0000;">'XDB' #993333; font-weight: bold;">AND instr#66cc66;">(SCHEMA#66cc66;">,#ff0000;">'w3'#66cc66;">) #66cc66;">= #cc66cc;">0 Plan hash value: #cc66cc;">2888251253 #808080; font-style: italic;">----------------------------------------------------------------------------------------------------------------------- #66cc66;">| Id #66cc66;">| Operation #66cc66;">| Name #66cc66;">| Starts #66cc66;">| E#66cc66;">-Rows #66cc66;">| A#66cc66;">-Rows #66cc66;">| A#66cc66;">-Time #66cc66;">| Buffers #66cc66;">| #808080; font-style: italic;">----------------------------------------------------------------------------------------------------------------------- #66cc66;">| #cc66cc;">0 #66cc66;">| #993333; font-weight: bold;">SELECT STATEMENT #66cc66;">| #66cc66;">| #cc66cc;">1 #66cc66;">| #66cc66;">| #cc66cc;">19 #66cc66;">|00:00:#cc66cc;">00.03 #66cc66;">| #cc66cc;">3 #66cc66;">| #66cc66;">| #cc66cc;">1 #66cc66;">| NESTED LOOPS #66cc66;">| #66cc66;">| #cc66cc;">1 #66cc66;">| #cc66cc;">1 #66cc66;">| #cc66cc;">19 #66cc66;">|00:00:#cc66cc;">00.03 #66cc66;">| #cc66cc;">3 #66cc66;">| #66cc66;">| #cc66cc;">2 #66cc66;">| FAST DUAL #66cc66;">| #66cc66;">| #cc66cc;">1 #66cc66;">| #cc66cc;">1 #66cc66;">| #cc66cc;">1 #66cc66;">|00:00:#cc66cc;">00.01 #66cc66;">| #cc66cc;">0 #66cc66;">| #66cc66;">|* #cc66cc;">3 #66cc66;">| COLLECTION ITERATOR PICKLER FETCH#66cc66;">| XMLSEQUENCEFROMXMLTYPE #66cc66;">| #cc66cc;">1 #66cc66;">| #cc66cc;">1 #66cc66;">| #cc66cc;">19 #66cc66;">|00:00:#cc66cc;">00.03 #66cc66;">| #cc66cc;">3 #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;">3 #66cc66;">- filter#66cc66;">(#66cc66;">(CAST#66cc66;">(SYS_XQ_UPKXML2SQL#66cc66;">(SYS_XQEXVAL#66cc66;">(SYS_XQEXTRACT#66cc66;">(VALUE#66cc66;">(KOKBF$#66cc66;">)#66cc66;">,#ff0000;">'/ROW/OWNER'#66cc66;">)#66cc66;">,#cc66cc;">0#66cc66;">,#cc66cc;">0#66cc66;">,#cc66cc;">20971520#66cc66;">,#cc66cc;">0#66cc66;">)#66cc66;">,#cc66cc;">50#66cc66;">, #cc66cc;">1#66cc66;">,#cc66cc;">2#66cc66;">) #993333; font-weight: bold;">AS VARCHAR2#66cc66;">(#cc66cc;">30#66cc66;">) #66cc66;">)#66cc66;">=#ff0000;">'XDB' #993333; font-weight: bold;">AND INSTR#66cc66;">(CAST#66cc66;">(SYS_XQ_UPKXML2SQL#66cc66;">(SYS_XQEXVAL#66cc66;">(SYS_XQEXTRACT#66cc66;">(VALUE#66cc66;">(KOKBF$#66cc66;">)#66cc66;">,#ff0000;">'/ROW/SCH EMA_URL'#66cc66;">)#66cc66;">,#cc66cc;">0#66cc66;">,#cc66cc;">0#66cc66;">,#cc66cc;">20971520#66cc66;">,#cc66cc;">0#66cc66;">)#66cc66;">,#cc66cc;">50#66cc66;">,#cc66cc;">1#66cc66;">,#cc66cc;">2#66cc66;">) #993333; font-weight: bold;">AS VARCHAR2#66cc66;">(#cc66cc;">80#66cc66;">) #66cc66;">)#66cc66;">,#ff0000;">'w3'#66cc66;">)#66cc66;">=#cc66cc;">0#66cc66;">)#66cc66;">) #cc66cc;">23 rows selected#66cc66;">. SQL#66cc66;">> #993333; font-weight: bold;">SELECT owner#66cc66;">, count#66cc66;">(#66cc66;">*#66cc66;">) #cc66cc;">2 #993333; font-weight: bold;">FROM MY_EXTERNAL_XFILE #cc66cc;">3 #993333; font-weight: bold;">WHERE instr#66cc66;">(SCHEMA#66cc66;">,#ff0000;">'xmlns.oracle.com'#66cc66;">) #66cc66;">> #cc66cc;">0 #cc66cc;">4 #993333; font-weight: bold;">GROUP #993333; font-weight: bold;">BY owner; OWNER COUNT#66cc66;">(#66cc66;">*#66cc66;">) #808080; font-style: italic;">------------------------------ ---------- MDSYS #cc66cc;">1 EXFSYS #cc66cc;">2 ORDSYS #cc66cc;">19 XDB #cc66cc;">19 SYS #cc66cc;">1 SQL#66cc66;">> #993333; font-weight: bold;">SELECT #66cc66;">* #993333; font-weight: bold;">FROM xplan; PLAN_TABLE_OUTPUT #808080; font-style: italic;">-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 5sqvj1k6pmmxj#66cc66;">, child number #cc66cc;">0 #808080; font-style: italic;">------------------------------------- #993333; font-weight: bold;">SELECT owner#66cc66;">, count#66cc66;">(#66cc66;">*#66cc66;">) #993333; font-weight: bold;">FROM MY_EXTERNAL_XFILE #993333; font-weight: bold;">WHERE instr#66cc66;">(SCHEMA#66cc66;">,#ff0000;">'xmlns.oracle.com'#66cc66;">) #66cc66;">> #cc66cc;">0 #993333; font-weight: bold;">GROUP #993333; font-weight: bold;">BY owner Plan hash value: #cc66cc;">2770558904 #808080; font-style: italic;">--------------------------------------------------------------------------------------------------------------------------------------------------- #66cc66;">| Id #66cc66;">| Operation #66cc66;">| Name #66cc66;">| Starts #66cc66;">| E#66cc66;">-Rows #66cc66;">| A#66cc66;">-Rows #66cc66;">| A#66cc66;">-Time #66cc66;">| Buffers #66cc66;">| OMem #66cc66;">| 1Mem #66cc66;">| Used#66cc66;">-Mem #66cc66;">| #808080; font-style: italic;">--------------------------------------------------------------------------------------------------------------------------------------------------- #66cc66;">| #cc66cc;">0 #66cc66;">| #993333; font-weight: bold;">SELECT STATEMENT #66cc66;">| #66cc66;">| #cc66cc;">1 #66cc66;">| #66cc66;">| #cc66cc;">5 #66cc66;">|00:00:#cc66cc;">00.03 #66cc66;">| #cc66cc;">3 #66cc66;">| #66cc66;">| #66cc66;">| #66cc66;">| #66cc66;">| #cc66cc;">1 #66cc66;">| HASH #993333; font-weight: bold;">GROUP #993333; font-weight: bold;">BY #66cc66;">| #66cc66;">| #cc66cc;">1 #66cc66;">| #cc66cc;">408 #66cc66;">| #cc66cc;">5 #66cc66;">|00:00:#cc66cc;">00.03 #66cc66;">| #cc66cc;">3 #66cc66;">| 855K#66cc66;">| 855K#66cc66;">| 917K #66cc66;">(#cc66cc;">0#66cc66;">)#66cc66;">| #66cc66;">| #cc66cc;">2 #66cc66;">| NESTED LOOPS #66cc66;">| #66cc66;">| #cc66cc;">1 #66cc66;">| #cc66cc;">408 #66cc66;">| #cc66cc;">42 #66cc66;">|00:00:#cc66cc;">00.02 #66cc66;">| #cc66cc;">3 #66cc66;">| #66cc66;">| #66cc66;">| #66cc66;">| #66cc66;">| #cc66cc;">3 #66cc66;">| FAST DUAL #66cc66;">| #66cc66;">| #cc66cc;">1 #66cc66;">| #cc66cc;">1 #66cc66;">| #cc66cc;">1 #66cc66;">|00:00:#cc66cc;">00.01 #66cc66;">| #cc66cc;">0 #66cc66;">| #66cc66;">| #66cc66;">| #66cc66;">| #66cc66;">|* #cc66cc;">4 #66cc66;">| COLLECTION ITERATOR PICKLER FETCH#66cc66;">| XMLSEQUENCEFROMXMLTYPE #66cc66;">| #cc66cc;">1 #66cc66;">| #cc66cc;">408 #66cc66;">| #cc66cc;">42 #66cc66;">|00:00:#cc66cc;">00.02 #66cc66;">| #cc66cc;">3 #66cc66;">| #66cc66;">| #66cc66;">| #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;">4 #66cc66;">- filter#66cc66;">(INSTR#66cc66;">(CAST#66cc66;">(SYS_XQ_UPKXML2SQL#66cc66;">(SYS_XQEXVAL#66cc66;">(SYS_XQEXTRACT#66cc66;">(VALUE#66cc66;">(KOKBF$#66cc66;">)#66cc66;">,#ff0000;">'/ROW/SCHEMA_URL'#66cc66;">)#66cc66;">,#cc66cc;">0#66cc66;">,#cc66cc;">0#66cc66;">,#cc66cc;">20971520#66cc66;">,#cc66cc;">0#66cc66;">)#66cc66;">,#cc66cc;">50#66cc66;">,#cc66cc;">1#66cc66;">,#cc66cc;">2#66cc66;">) #993333; font-weight: bold;">AS VARCHAR2#66cc66;">(#cc66cc;">80#66cc66;">) #66cc66;">)#66cc66;">,#ff0000;">'xmlns.oracle.com'#66cc66;">)#66cc66;">>#cc66cc;">0#66cc66;">) #cc66cc;">23 rows selected#66cc66;">.
Who knows with a little bit of fantasy, me or you, could find a case this could be applied and makes sense. This example was based on the following OTN Thread: “Error with basic XMLTable” and its probably easier to read up it over there. Hope you liked it.
On the difficulties of Migrations – Especially to new Blogs
I haven’t posted here in a log while. That’s because I’ve been posting all my stories and ideas over at the Pythian blog.
I knew that migrations are one of the most difficult tasks in IT operations, but I did not realize this also applies to blogs. Yesterday, Alex helped me look at the blog statistics over at the Pythian blog and it turns out that over there I have about 10% of the readers that I had over here. While I’m just as brilliant in the Pythian blog as I was here, I guess that with all the old links, google ranks and people not changing their RSS subscriptions – blog locations have a lot more momentum than I suspected.
Anyway, to the 90% of my readers who apparently only read me at this address, in the next few days I’ll copy over the blog posts that I neglected to post here. I’ll try to post new articles here in the future, but they will always appear in the Pythian blog first, so you really should add my new address to whatever it is you use to follow blogs.
書き込みキャッシュ ポリシーのOFF
TPC-Cベンチマークの番外編として、Windows7の書き込みキャッシュ機能の調査をしました。
今回のテストではデバイスマネージャのプロパティ設定にある
「書き込みキャッシュ ポリシー」→デバイスの書き込みキャッシュを有効にするだけで行いました。
デバイスでWindowsによる書き込みキャッシュバッファのフラッシュをオフにするもテストしてみました：
tpmが少し安定しているのがわかります。
そして、最後にすべてをオフにしました：
書き込みキャッシュがとても有効なことがわかります。
そうか、、、前回の安定しない「波形」は書き込みキャッシュの周期的なフラッシュだったんだな。
Advert: The Michigan OakTable Symposium
Lets say you can’t make it to those presentations of Oracle Open World this year and you also hadn’t the budget to come to Europe to see really (technically) in depth, practice driven, great (probably new) views on your performance and architecture work at work during Miracle Open World, then there is a great alternative: The Michigan OakTable Symposium.
Its “only” a 2 day symposium but you have a chance there to come up personal, discuss issues during presentations and on site, for instance in the lobby, with some of the top people in their field like, among others, Jonathan Lewis, Tanel Poder or Cary Millsap.
I really like those mini-conferences, because they always bring me new ideas how to solve problems at work or stuff that I am dealing with in my mind, seeing them from a new perspective or get new info and techniques, involving Oracle software, I didn’t know about yet…
During this years The Michigan OakTable Symposium you have to chance to enjoy Cary’s extended version (2 slots, one on Tuesday and one on Friday) of his “Thinking Clearly About Performance” presentation that won this year’s ODTUG Editor’s Choice Award. Anyway, almost all presenters during this 2 day event are Oracle ACE’s, ACE Directors or the top in their field and really have something to say…
|
|
Their biographies of, I guess probably almost combined 200+ years of practical IT experience, can be found here:
As Mogens Norgaard said it during his remarks (see the video on Miracle Channel) on the new Second OakTable book called “Expert Oracle Practices: Oracle Database Administration from the Oak Table” that those “new” people really made a better book than the first one, they know their stuff…
Anyway, in short, I suggest to have a look at the MOTS agenda yourself:
See you there? If your not convinced look them up on the internet or via the OakTable aggregated www.oaktable.net blog site.
XMLDB Oracle Open World Agenda…
Becoming a bit of a tradition actually…
Trying for others to avoid the same, at least it was in 2008/2009, ordeal going thru the O.O.W. Schedule / Content builder trying to find XMLDB topics, I listed those I could find on the XMLDB OTN Forum. As said, trying to follow up on a tradition and to get myself (and hopefully you) an overview on things to come during Oracle Open World 2010 (/Oracle Develop /JavaOne). This year I think its becoming BIG regarding amounts /attendance of people if not only due to the combination of O.O.W./Develop/JavaOne on the same spot in San Francisco…
So, for me and those who are interested, just like the year before, hereby an attempt to find all XMLDB related presentations, workshops and other events during Oracle Open World 2010. I will try to add info, time and days later on (and/or you might) if I find them and/or if they become known. See for XMLDB presentations and Hands-on Lab sessions here:
I hope you enjoy your presentations during O.O.W. and who knows we meet this year.
Not really into XMLDB presentations? Have a look at the 50+ Oracle ACE(D) presentations Oracle Open World and JavaOne, Oracle Develop listing here…
.png){kind=small}
TweetsPopular content
Today's:
Monthly archive
- June 2013 (45)
- May 2013 (65)
- April 2013 (79)
- March 2013 (71)
- February 2013 (73)
- January 2013 (57)
- December 2012 (52)
- November 2012 (58)
- October 2012 (71)
- September 2012 (89)
- August 2012 (63)
- July 2012 (74)
- June 2012 (70)
- May 2012 (87)
- April 2012 (86)
- March 2012 (86)
- February 2012 (93)
- January 2012 (88)
- December 2011 (92)
- November 2011 (120)
- October 2011 (108)
- September 2011 (110)
- August 2011 (98)
- July 2011 (114)
- June 2011 (101)
- May 2011 (86)
- April 2011 (81)
- March 2011 (131)
- February 2011 (106)
- January 2011 (126)
Recent comments
21 weeks 1 day ago
31 weeks 2 hours ago
32 weeks 5 days ago
35 weeks 6 days ago
38 weeks 1 day ago
47 weeks 5 days ago
49 weeks 1 day ago
50 weeks 1 day ago
50 weeks 3 days ago
1 year 1 week ago