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.gralike.com/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
Cool alternative use based on the X$ tables, have a look here:
http://forums.oracle.com/forums/thread.jspa?messageID=3597596
The token tables and indexes are created during $ORACLE_HOME/rdbms/admin/catxdbtm.sql