We have a query in 11.2.0.2 that is selecting from a table with a column of XMLTYPE. When we run an explain plan we get the following result:
SELECT
idi."co_cd",
idi."rate_src_cd",
idi."pymt_mthd_cd",
idi."line_of_bus_cd",
idi."media_type_cd",
idi."curr_cd",
idi."legal_entity",
idi."type_cd",
idi."inv_type_cd",
idi."rev_stream_cd",
idi."item_net_usd",
idi."pre_ern_crdt",
idi."post_ern_crdt"
FROM
eci_schema.rev_item_earn_dtl ried,
XMLTABLE('/revenueInvoice'
PASSING ried.inv_dtl_img
COLUMNS
"co_cd" VARCHAR2(3) PATH 'trackingNbr/@coCd',
"rate_src_cd" VARCHAR2(3) PATH 'trackingNbr/@rateSrcCd',
"pymt_mthd_cd" VARCHAR2(3) PATH 'trackingNbr/@pymtMthdCd',
"line_of_bus_cd" VARCHAR2(5) PATH 'trackingNbr/@lineOfBusCd',
"media_type_cd" VARCHAR2(2) PATH 'billToInfo/@mediaTypeCd',
"curr_cd" VARCHAR2(3) PATH 'billToInfo/@currCd',
"legal_entity" VARCHAR2(3) PATH 'billToInfo/@legalEntity',
"type_cd" VARCHAR2(3) PATH 'creditCardID/@typeCd',
"inv_type_cd" VARCHAR2(1) PATH 'trackingNbr/@invTypeCd',
"rev_stream_cd" VARCHAR2(1) PATH 'trackingNbr/@revStreamCd',
"item_net_usd" NUMBER(16,6) PATH 'amount[@type="itemNetUsd"]/val6P',
"pre_ern_crdt" NUMBER(16,6) PATH 'amount[@type="preErnCrdt"]/val2P',
"post_ern_crdt" NUMBER(16,6) PATH 'amount[@type="postErnCrdt"]/val2P')
idi
WHERE
ried.rev_item_earn_dtl_id_nbr = 1230010101863700
/
co_ rat pym line_ me cur leg typ i r item_net_usd pre_ern_crdt post_ern_crdt
--- --- --- ----- -- --- --- --- - - ------------ ------------ -------------
002 CHR 01 00001 T USD USA 3 D 5.88
Execution Plan
----------------------------------------------------------
Plan hash value: 9903639
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 61 | 3 (0)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 30 | | | | |
|* 2 | TABLE ACCESS BY GLOBAL INDEX ROWID| RIED_AIRBILL_AMOUNT_NT | 1 | 30 | 4 (0)| 00:00:01 | ROWID | ROWID |
|* 3 | INDEX RANGE SCAN | RIED_AIRBILL_AMOUNT_NT_PK | 5 | | 3 (0)| 00:00:01 | | |
| 4 | SORT AGGREGATE | | 1 | 31 | | | | |
|* 5 | TABLE ACCESS BY GLOBAL INDEX ROWID| RIED_AIRBILL_AMOUNT_NT | 1 | 31 | 4 (0)| 00:00:01 | ROWID | ROWID |
|* 6 | INDEX RANGE SCAN | RIED_AIRBILL_AMOUNT_NT_PK | 5 | | 3 (0)| 00:00:01 | | |
| 7 | SORT AGGREGATE | | 1 | 31 | | | | |
|* 8 | TABLE ACCESS BY GLOBAL INDEX ROWID| RIED_AIRBILL_AMOUNT_NT | 1 | 31 | 4 (0)| 00:00:01 | ROWID | ROWID |
|* 9 | INDEX RANGE SCAN | RIED_AIRBILL_AMOUNT_NT_PK | 5 | | 3 (0)| 00:00:01 | | |
| 10 | PARTITION HASH SINGLE | | 1 | 61 | 3 (0)| 00:00:01 | 1 | 1 |
| 11 | TABLE ACCESS BY GLOBAL INDEX ROWID| REV_ITEM_EARN_DTL | 1 | 61 | 3 (0)| 00:00:01 | ROWID | ROWID |
|* 12 | INDEX UNIQUE SCAN | REV_ITEM_EARN_DTL_PK | 1 | | 2 (0)| 00:00:01 | 1 | 1 |
---------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("SYS_ALIAS_2"."AIRBILL_AMOUNT_TYPE"='itemNetUsd')
3 - access("NESTED_TABLE_ID"=:B1)
5 - filter("SYS_ALIAS_1"."AIRBILL_AMOUNT_TYPE"='preErnCrdt')
6 - access("NESTED_TABLE_ID"=:B1)
8 - filter("SYS_ALIAS_0"."AIRBILL_AMOUNT_TYPE"='postErnCrdt')
9 - access("NESTED_TABLE_ID"=:B1)
12 - access("RIED"."REV_ITEM_EARN_DTL_ID_NBR"=1230010101863700)
Note
-----
- Unoptimized XML construct detected (enable XMLOptimizationCheck for more information)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
28 consistent gets
0 physical reads
0 redo size
1439 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
When running with xmloptimizationcheck on, we get the following error:
SQL> set xmloptimizationcheck on
SQL> @xpathProc-mod.sql
ried.rev_item_earn_dtl_id_nbr = 1230010101863700
*
ERROR at line 35:
ORA-19022: Unoptimized XML construct detected.
We also get the following error inside the trace file:
cat CADD411_ora_25112.trc
Trace file /opt/oracle/diag/rdbms/cadd41/CADD411/trace/CADD411_ora_25112.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ORACLE_HOME = /opt/oracle/product/11.2.0.2
System name: Linux
Node name: tcd00041.prod.fedex.com
Release: 2.6.18-194.11.4.el5
Version: #1 SMP Fri Sep 17 04:57:05 EDT 2010
Machine: x86_64
Instance name: CADD411
Redo thread mounted by this instance: 1
Oracle process number: 47
Unix process pid: 25112, image: oracle@tcd00041.prod.fedex.com (TNS V1-V3)
*** 2011-03-23 19:39:50.183
*** SESSION ID:(4253.563) 2011-03-23 19:39:50.183
*** CLIENT ID:() 2011-03-23 19:39:50.183
*** SERVICE NAME:(SYS$USERS) 2011-03-23 19:39:50.183
*** MODULE NAME:(sqlplus@tcd00041.prod.fedex.com (TNS V1-V3)) 2011-03-23 19:39:50.183
*** ACTION NAME:() 2011-03-23 19:39:50.183
===============================================================================
XML Performance Diagnosis:
Unparsed Query:
******* UNPARSED QUERY IS *******
SELECT CAST("SYS_ALIAS_3"."SYS_NC00013$" AS VARCHAR2(3) ) "co_cd",CAST("SYS_ALIAS_3"."SYS_NC00015$" AS VARCHAR2(3) ) "rate_src_cd",CAST("SYS_ALIAS_3"."SYS_NC00019$" AS VARCHAR2(3) ) "pymt_mthd_cd",CAST("SYS_ALIAS_3"."SYS_NC00014$" AS VARCHAR2(5) ) "line_of_bus_cd",CAST("SYS_ALIAS_3"."SYS_NC00030$" AS VARCHAR2(2) ) "media_type_cd",CAST("SYS_ALIAS_3"."SYS_NC00031$" AS VARCHAR2(3) ) "curr_cd",CAST("SYS_ALIAS_3"."SYS_NC00036$" AS VARCHAR2(3) ) "legal_entity",CAST("SYS_ALIAS_3"."SYS_NC00065$" AS VARCHAR2(3) ) "type_cd",CAST("SYS_ALIAS_3"."SYS_NC00021$" AS VARCHAR2(1) ) "inv_type_cd",CAST("SYS_ALIAS_3"."SYS_NC00022$" AS VARCHAR2(1) ) "rev_stream_cd",CAST(TO_NUMBER(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL( (SELECT "SYS"."SYS_IXQAGG"(SYS_XQCON2SEQ(SYS_XMLGEN(TO_CHAR("SYS_ALIAS_2"."AIRBILL_AMOUNT_VAL6P"),131,'val6P','','9BCCC5870DB1BBA1E04052C729110D92',12864))) "VALUE(P)" FROM "ECI_SCHEMA"."RIED_AIRBILL_AMOUNT_NT" "SYS_ALIAS_2" WHERE "SYS_ALIAS_2"."NESTED_TABLE_ID"="SYS_ALIAS_3"."SYS_NC0004800049$" AND "SYS_ALIAS_2"."AIRBILL_AMOUNT_TYPE"='itemNetUsd'),0,0,54525952,0),50,1,2)) AS NUMBER(16,6) ) "item_net_usd",CAST(TO_NUMBER(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL( (SELECT "SYS"."SYS_IXQAGG"(SYS_XQCON2SEQ(SYS_XMLGEN(TO_CHAR("SYS_ALIAS_1"."AIRBILL_AMOUNT_VAL2P"),131,'val2P','','9BCCC5870DB1BBA1E04052C729110D92',12863))) "VALUE(P)" FROM "ECI_SCHEMA"."RIED_AIRBILL_AMOUNT_NT" "SYS_ALIAS_1" WHERE "SYS_ALIAS_1"."NESTED_TABLE_ID"="SYS_ALIAS_3"."SYS_NC0004800049$" AND "SYS_ALIAS_1"."AIRBILL_AMOUNT_TYPE"='preErnCrdt'),0,0,54525952,0),50,1,2)) AS NUMBER(16,6) ) "pre_ern_crdt",CAST(TO_NUMBER(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL( (SELECT "SYS"."SYS_IXQAGG"(SYS_XQCON2SEQ(SYS_XMLGEN(TO_CHAR("SYS_ALIAS_0"."AIRBILL_AMOUNT_VAL2P"),131,'val2P','','9BCCC5870DB1BBA1E04052C729110D92',12863))) "VALUE(P)" FROM "ECI_SCHEMA"."RIED_AIRBILL_AMOUNT_NT" "SYS_ALIAS_0" WHERE "SYS_ALIAS_0"."NESTED_TABLE_ID"="SYS_ALIAS_3"."SYS_NC0004800049$" AND "SYS_ALIAS_0"."AIRBILL_AMOUNT_TYPE"='postErnCrdt'),0,0,54525952,0),50,1,2)) AS NUMBER(16,6) ) "post_ern_crdt" FROM "ECI_SCHEMA"."REV_ITEM_EARN_DTL" "SYS_ALIAS_3" WHERE "SYS_ALIAS_3"."REV_ITEM_EARN_DTL_ID_NBR"=1230010101863700
Reason: XQEXVAL
===============================================================================
We are wondering what this 'Reason: XQEXVAL' means and where an explanation of all the 'REASONS' are located. It also appears as if our XMLQuery is well formed but it is choking on the portion where we place a filter on a regular column of the table.
Thoughts?