Learned yesterday an important lesson on the Oracle OTN XMLDB forum. A question was asked why a count via a full table scan was quicker than the same example while using an XMLIndex. “XMLIndex performance regarding // (any descendant))”. The poster used the // xpath expression to do his search.
As in most cases nowadays it is important to keep your statistics in order. An XMLIndex is not an “index” as such, it is an logical index / domain index, specially designed for use with XMLDB / XML data.
Although statistics were present in this case, the count via the full table scan was quicker. Without a full explain plan of the presented example, it is a little bit difficult to really understand what went on, but Geoff (one of the members of the XMLDB Development team) solved the issue via (re)generation of stats on three XDB X$ Tables.
These “token” tables can be found via the following statement
SQL> SELECT TABLE_NAME 2 , to_char(last_analyzed,'YYYY/MM/DD') "ANALYZED" 3 FROM dba_tables 4 WHERE TABLE_NAME LIKE 'X$%' 5 AND owner = 'XDB'; TABLE_NAME ANALYZED ------------------------------ ---------- X$QN31S9LBVEWT9PUP3MTHPTT2K9VT 2007/07/09 X$PT31S9LBVEWT9PUP3MTHPTT2K9VT 2007/07/09 X$NM31S9LBVEWT9PUP3MTHPTT2K9VT 2007/04/03 3 ROWS selected. SQL> DESC XDB.X$QN31S9LBVEWT9PUP3MTHPTT2K9VT Name NULL? TYPE ----------------------------- -------- --------------- NMSPCID RAW(8) LOCALNAME VARCHAR2(2000) FLAGS RAW(4) ID RAW(8) SQL> DESC XDB.X$PT31S9LBVEWT9PUP3MTHPTT2K9VT Name NULL? TYPE ----------------------------- -------- --------------- PATH RAW(2000) ID RAW(8) SQL> DESC XDB.X$NM31S9LBVEWT9PUP3MTHPTT2K9VT Name NULL? TYPE ----------------------------- -------- --------------- NMSPCURI VARCHAR2(2000) ID RAW(8) SQL> |
As noted by Geoff Lee on the OTN XMLDB forum:
For a // query, xmlindex rewrite uses the token tables. Therefore, stats should be gathered on these tables as well. There are 3 token tables. They are owned by XDB, and their names begin with ‘X$’.
and, as an answer on the two very valid questions of the poster:
- Should I always gather table statistics on those token tables every time after I create new XMLIndexes?
- What can cause the content of these token tables to change?
One example that a token table content may change is when unique paths are changed during DML operations.
(XMLIndex) Path subsetting is critical for keeping in check the index size, which does affect index scan performance.
Using an XMLIndex with constraints (=path subsetting) can create an very huge XMLIndex, so in that sense (but I have to check), I can imagine that the index BLEVEL or LEAF_BLOCKS increase likewise.
M.
Update
In an almost vanilla database (version 11.1.0.4.0 beta for Windows):
SQL> SELECT COUNT(*) 2 FROM XDB.X$QN31S9LBVEWT9PUP3MTHPTT2K9VT; COUNT(*) --------- 452 1 ROW selected. SQL> SELECT INDEX_NAME, 2 INDEX_TYPE, 3 BLEVEL, 4 LEAF_BLOCKS, 5 DISTINCT_KEYS 6 FROM dba_indexes 7 WHERE TABLE_NAME='X$QN31S9LBVEWT9PUP3MTHPTT2K9VT'; INDEX_NAME INDEX_ BLEVEL LEAF_BLOCKS DISTINCT ------------------------------ ------ ------- ----------- --------- X$QS31S9LBVEWT9PUP3MTHPTT2K9VT NORMAL 0 1 11 X$QQ31S9LBVEWT9PUP3MTHPTT2K9VT NORMAL 1 3 452 X$QI31S9LBVEWT9PUP3MTHPTT2K9VT NORMAL 0 1 452 3 ROWS selected. SQL> SELECT * FROM XDB.X$QN31S9LBVEWT9PUP3MTHPTT2K9VT 2 WHERE rownum < = 5; NMSPCID ---------------- LOCALNAME ----------------------------------------------------- FLAGS ID -------- ---------------- 06FB CADANS-JWB 00 2DAA 07 xmlns 01 1696 06FB PERSOON 00 527F 06FB C100-SOC-FSC-NMR 00 5909 06FB R100-PRS 00 6808 5 ROWS selected. SQL> |
SQL> SELECT COUNT(*) 2 FROM XDB.X$PT31S9LBVEWT9PUP3MTHPTT2K9VT 3 ; COUNT(*) --------- 289 1 ROW selected. SQL> SELECT INDEX_NAME, 2 INDEX_TYPE, 3 BLEVEL, 4 LEAF_BLOCKS, 5 DISTINCT_KEYS 6 FROM dba_indexes 7 WHERE TABLE_NAME='X$PT31S9LBVEWT9PUP3MTHPTT2K9VT'; INDEX_NAME INDEX_ BLEVEL LEAF_BLOCKS DISTINCT ------------------------------ ------ ------- ----------- --------- X$PP31S9LBVEWT9PUP3MTHPTT2K9VT NORMAL 1 2 289 X$PI31S9LBVEWT9PUP3MTHPTT2K9VT NORMAL 0 1 289 X$PR31S9LBVEWT9PUP3MTHPTT2K9VT FUNCTI 1 2 289 ON-BAS ED NOR MAL 3 ROWS selected. SQL> SELECT * FROM XDB.X$PT31S9LBVEWT9PUP3MTHPTT2K9VT 2 WHERE rownum < = 5; PATH ----------------------------------------------------- ID ---------------- 022DAA 6CCE 022DAA021696 2F9E 022DAA02527F 5D99 022DAA02527F025909 6854 022DAA02527F026808 4DF9 5 ROWS selected. SQL> |
SQL> SELECT COUNT(*) 2 FROM XDB.X$NM31S9LBVEWT9PUP3MTHPTT2K9VT; COUNT(*) --------- 16 1 ROW selected. SQL> SELECT INDEX_NAME, 2 INDEX_TYPE, 3 BLEVEL, 4 LEAF_BLOCKS, 5 DISTINCT_KEYS 6 FROM dba_indexes 7 WHERE TABLE_NAME='X$NM31S9LBVEWT9PUP3MTHPTT2K9VT'; INDEX_NAME INDEX_ BLEVEL LEAF_BLOCKS DISTINCT ------------------------------ ------ ------- ----------- --------- X$NN31S9LBVEWT9PUP3MTHPTT2K9VT NORMAL 0 1 12 X$NI31S9LBVEWT9PUP3MTHPTT2K9VT NORMAL 0 1 12 2 ROWS selected. SQL> SELECT * FROM XDB.X$NM31S9LBVEWT9PUP3MTHPTT2K9VT 2 WHERE rownum < = 5; NMSPCURI --------------------------------------------------------- ID ---------------- http://www.uwv.nl/cadans/historie/JWB/CADANS-JWB-HISTORIE 06FB http://www.w3.org/XML/1998/namespace 01 http://www.w3.org/XML/2000/xmlns 02 http://www.w3.org/2001/XMLSchema-instance 03 http://www.w3.org/2001/XMLSchema 04 5 ROWS selected. SQL> |
See also $ORACLE_HOME/rdbms/admin/catxdbtm.sql
2 comments
Marco Gralike
3 July, 2009 at 15:39 (UTC 1) Link to this comment
Cool alternative use based on the X$ tables, have a look here:
http://forums.oracle.com/forums/thread.jspa?messageID=3597596
Marco Gralike
28 December, 2009 at 19:15 (UTC 1) Link to this comment
The token tables and indexes are created during $ORACLE_HOME/rdbms/admin/catxdbtm.sql