I found a very cool presentation. Yep it is about XML, The concepts behind XQuery…
Category: Performance
Nowadays, this “Collection Iterator Pickler Fetch” output in an explain plan is less and less seen as the XML database functionality becomes more and more stronger in re-writing statements. Alas, I got one, again.
SQL> xquery
2 let $auction := ora:view("XM_TAB") return
3 count(
4 for $i in $auction/site/closed_auctions/closed_auction
5 where $i/price/text() >= 40
6 return $i/price
7* )
Result Sequence
---------------
6539
Execution Plan
----------------------------------------------------------
Plan hash value: 4083502163
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8168 | 16336 | 29 (0)| 00:00:01 |
| 1 | COLLECTION ITERATOR PICKLER FETCH| XQSEQUENCEFROMXMLTYPE | | | | |
-----------------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
143 recursive calls
0 db block gets
385537 consistent gets
119 physical reads
0 redo size
1439 bytes sent via SQL*Net to client
942 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
19500 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
I couldn’t find much on the internet about the “collection iterator pickler fetch” (lets call it CIPF for now). These posts from Steve Adams and Tom Kyte shed some light on what is happening.
In the XMLDB realm, I now found two examples of CIPF.
This post will show you some of the first numbers I collected regarding “Loading XML data”, while making use of different XMLType “physical storage containers”.
I also have done some initial testing with Object Relational XMLType storage, but because this method of storage has many options and extra features, I won’t describe them yet here. This topic is interesting enough to earn its on post.
If you need some background on Oracle XMLType Storage option than have a read through the “Binary-, CLOB, Object Relational Storage” Category option in the menu, the Oracle XMLDB Developers Manual or a short intro via Oracle 11g – XMLType Storage Options.
After having created an environment as described in “XMLDB Performance: Environment, Set-up, Procedure“, the following results were gathered by me while keeping values constant, for example the values for “connection.xml“, as described in the “XMLDB Performance: Environment, Set-up, Procedure” . Only the WIKI_STAGE create statements are different.