The problem: Oracle views that join database tables with data obtained from XML using Extract and ExtractValue use an invalid execution plan in Oracle 11.2g. Does anyone know if this is a known problem with a simple solution?
My workplace has recently upgraded from Oracle 10.1 to Oracle 11.2 database. Some of our important legacy code uses views to expose and extract XML data. Testing reveals that these views no longer work consistently in 11.2g. The execution plan seems inconsistent and wrong in that it tries to join extracted XML data to relational table data before extracting the XML data, hence returning nothing when there is clearly data. We realise that Extract and ExtractValue are deprecated in 11.2g, but execution plans seem to have the same problem when we use XMLTable.
Strangely, after running the following queries without hints, then with the hints and getting consistent results (no data without the hints, data with the hints), retrying a few days later gives different results and execution plans so the queries work even without the hints. This has occurred in two different Oracle instances. Does the optimiser “learn”? It still doesn’t give us faith that it will work consistently over time.
Examples: Without going into the meaning of the XML data or why the original views are defined as they are – the original developers are no longer here to explain – here are some examples of original view definition queries and their execution plans, followed by modified versions and their plans. Note that the originals sometimes work, sometimes don’t, so we’re not confident.
- Original view’s query: In 10.1g this returned row(s) when expected. In 11.2g, due to the evaluation order, no rows are ever returned when the execution plan is as shown. Note that sometimes the exec plan changes and it works.
{code}
SELECT
xml.history_id,
xml.game_name,
xml.game_version_number,
xml.gamble_type_xref,
gt.code_id gamble_type_id
FROM (SELECT history_id,
UPPER (EXTRACTVALUE (VALUE (software_game), '//Game_Name'))
game_name,
EXTRACTVALUE (VALUE (software_game),
'//Game_Version_Number')
game_version_number,
EXTRACTVALUE (VALUE (game_gamble), '//Game_Gamble')
gamble_type_xref
FROM ogr_test_xml,
TABLE (
XMLSEQUENCE (
EXTRACT (
xml_file,
'/submission/Recommended_Product/Software/Software_Game'))) software_game,
TABLE (
XMLSEQUENCE (
EXTRACT (VALUE (software_game),
'Software_Game/Game_Gamble'))) game_gamble)
xml,
adm_code_ms gt
WHERE gt.external_ref = xml.gamble_type_xref
AND gt.type_code = (SELECT code_id
FROM adm_code_ms
WHERE internal_ref = 'TSV_GAMBLE_METHOD')
AND xml.history_id = 110 -----added to get specific data
{code}
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2035 | 66 |
| 1 | NESTED LOOPS | | 1 | 2035 | 64 |
| 2 | NESTED LOOPS | | 1 | 2033 | 34 |
| 3 | MERGE JOIN CARTESIAN | | 1 | 2027 | 5 |
| 4 | TABLE ACCESS FULL | OGR_TEST_XML | 1 | 2015 | 2 |
| 5 | BUFFER SORT | | 8 | 96 | 3 |
| 6 | TABLE ACCESS BY INDEX ROWID| ADM_CODE_MS | 8 | 96 | 3 |
| 7 | INDEX RANGE SCAN | IDX_ADM_CODE_TYPE | 8 | | 1 |
| 8 | INDEX RANGE SCAN | IDX_ADM_CODE_INT_REF_CODE | 1 | 15 | 2 |
| 9 | XPATH EVALUATION | | | | |
| 10 | XPATH EVALUATION | | | | |
---------------------------------------------------------------------------------------------
2. Add a hint to force evaluation order. The execution plan changes and it now returns row(s) as expected.
SELECT /*+ordered*/ -------- This is only change from (1)
xml.history_id,
xml.game_name,
xml.game_version_number,
xml.gamble_type_xref,
gt.code_id gamble_type_id
FROM (SELECT history_id, ...
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2035 | 66 |
| 1 | NESTED LOOPS | | 1 | 2035 | 64 |
| 2 | NESTED LOOPS | | 1 | 2023 | 61 |
| 3 | NESTED LOOPS | | 1 | 2021 | 31 |
| 4 | TABLE ACCESS FULL | OGR_TEST_XML | 1 | 2015 | 2 |
| 5 | XPATH EVALUATION | | | | |
| 6 | XPATH EVALUATION | | | | |
| 7 | TABLE ACCESS BY INDEX ROWID| ADM_CODE_MS | 1 | 12 | 3 |
| 8 | INDEX RANGE SCAN | IDX_ADM_CODE_TYPE | 8 | | 1 |
| 9 | INDEX RANGE SCAN | IDX_ADM_CODE_INT_REF_CODE | 1 | 15 | 2 |
------------------------------------------------------------------------------------------
3. Rewrite the SQL using XMLTable. This failed to return row(s) initially, due to its execution plan.
SELECT xml.history_id
,xml.game_name
,xml.game_version_number
,xml.gamble_type_xref
,gt.code_id gamble_type_id
FROM (
SELECT history_id
, upper(software_game."game_name") game_name
, software_game."game_version_number" game_version_number
, software_game."gamble_type_xref" gamble_type_xref
FROM ogr_test_xml,
xmltable('/submission/Recommended_Product/Software/Software_Game' passing xml_file
columns
"game_name" varchar2(20) path '//Game_Name/text()',
"game_version_number" varchar2(20) path
'//Game_Version_Number/text()',
"gamble_type_xref" varchar2(20) path '//Game_Gamble/text()'
)software_game
) xml,
adm_code_ms gt
WHERE gt.external_ref = xml.gamble_type_xref
AND gt.type_code = (SELECT code_id
FROM adm_code_ms
WHERE internal_ref = 'TSV_GAMBLE_METHOD')
AND xml.history_id = 110 -----added to get specific data
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2033 | 37 |
| 1 | NESTED LOOPS | | 1 | 2033 | 35 |
| 2 | MERGE JOIN CARTESIAN | | 1 | 2027 | 5 |
| 3 | TABLE ACCESS FULL | OGR_TEST_XML | 1 | 2015 | 2 |
| 4 | BUFFER SORT | | 8 | 96 | 3 |
| 5 | TABLE ACCESS BY INDEX ROWID| ADM_CODE_MS | 8 | 96 | 3 |
| 6 | INDEX RANGE SCAN | IDX_ADM_CODE_TYPE | 8 | | 1 |
| 7 | INDEX RANGE SCAN | IDX_ADM_CODE_INT_REF_CODE | 1 | 15 | 2 |
| 8 | XPATH EVALUATION | | | | |
--------------------------------------------------------------------------------------------
4. Add hint to XMLTable version: This returned row(s) immediately.
SELECT /*+ordered*/ -----this is the only change from (3)
xml.history_id
,xml.game_name
,xml.game_version_number
,xml.gamble_type_xref
,gt.code_id gamble_type_id
FROM (...
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2033 | 37 |
| 1 | NESTED LOOPS | | 1 | 2033 | 35 |
| 2 | NESTED LOOPS | | 1 | 2021 | 32 |
| 3 | TABLE ACCESS FULL | OGR_TEST_XML | 1 | 2015 | 2 |
| 4 | XPATH EVALUATION | | | | |
| 5 | TABLE ACCESS BY INDEX ROWID| ADM_CODE_MS | 1 | 12 | 3 |
| 6 | INDEX RANGE SCAN | IDX_ADM_CODE_TYPE | 8 | | 1 |
| 7 | INDEX RANGE SCAN | IDX_ADM_CODE_INT_REF_CODE | 1 | 15 | 2 |
------------------------------------------------------------------------------------------
Any suggestions?