Plan Regression with VW_DCL (Lateral View Decorrelation) in Explain Plan New Transformation in 12c Optimizer
(Doc ID 2361393.1)
Last updated on MARCH 15, 2022
Applies to:
Oracle Database - Enterprise Edition - Version 12.1.0.2 and laterOracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Information in this document applies to any platform.
Symptoms
NOTE: In the images and/or the document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.
- After upgrade to 12.1.0.2,plan regression occurs with new 12c feature called lateral view decorrelation
- In 12c, there is a new transformation which is called lateral view decorrelation. This Transformation basically will rewrite ANSI Syntax to Oracle syntax representation:
SQL Query Involved
SELECT ka_pravo_id AS pravo_vaz_id,
st.stranka_id AS id,
st.id_group AS pravo_id,
ag.right_level_code,
Decode(ag.right_level_code, :"SYS_B_0", ag.name,
NULL) AS spravo_jmeno,
apg.id_person AS per_id,
Decode(apg.id_person, NULL, :"SYS_B_1",
:"SYS_B_2") AS ma_pravo,
NULL AS prof_id
FROM Test1 st
inner join tab_yyy ag
ON ag.id_group = st.id_group
AND ag.disabled = :"SYS_B_3"
AND ( NULL IS NULL
OR ag.right_level_code = :"SYS_B_4" )
left outer join Test2 apg
ON apg.id_group = ag.id_group
AND ( :"SYS_B_5" IS NULL
OR apg.id_person = :"SYS_B_6" )
AND apg.xxxfrom IS NULL
AND apg.xxxto IS NULL
WHERE ( st.stranka_id = :"SYS_B_7" )
AND ( ag.right_level_code = :"SYS_B_8"
OR apg.id_person IS NOT NULL )
AND st.status = :"SYS_B_9"
Above query shows LEFT OUTER JOIN an ANSI syntax.
This Transformation is visible in execution plans as VW_DCL_xxx and is also visible in 10053 Trace:
Bad Plan
----------------------------------------------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 49 | |
| 1 | FILTER | | | | | |
| 2 | HASH JOIN OUTER | | 1 | 106 | 49 | 00:00:01 |
| 3 | HASH JOIN | | 1 | 80 | 2 | 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 80 | 2 | 00:00:01 |
| 5 | NESTED LOOPS | | 2 | 80 | 2 | 00:00:01 |
| 6 | STATISTICS COLLECTOR | | | | | |
| 7 | TABLE ACCESS BY INDEX ROWID BATCHED | TEST2 | 2 | 36 | 1 | 00:00:01 |
| 8 | INDEX RANGE SCAN | IXUF_STRANKA_PRAVO_STRI| 4 | | 1 | 00:00:01 |
| 9 | INDEX UNIQUE SCAN | PK_AP_GROUP | 1 | | 1 | 00:00:01 |
| 10 | TABLE ACCESS BY INDEX ROWID | TEST1 | 1 | 62 | 1 | 00:00:01 |
| 11 | TABLE ACCESS FULL | TEST2 | 1 | 62 | 1 | 00:00:01 |
| 12 | VIEW | VW_DCL_AD0F2F3F | 7041 | 179K | 47 | 00:00:01 | >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> See the VW_DCL here.
| 13 | INDEX FAST FULL SCAN | IX_AP_PERS_GROUP | 7041 | 117K | 47 | 00:00:01 |
Good Plan
SELECT STATEMENT
FILTER
. NESTED LOOPS OUTER
.. NESTED LOOPS
... TABLE ACCESS BY INDEX ROWID XXXXXXX
.... INDEX RANGE SCAN IXUF_STRANKA_PRAVO
... TABLE ACCESS BY INDEX ROWID XXXXX
.... INDEX UNIQUE SCAN PK_AP_GROUP
.. VIEW
... INDEX RANGE SCAN IX_AP_PERS_GROUP
10053 Excerpt
******* UNPARSED QUERY IS *******
SELECT "ST"."KA_PRAVO_ID" "PRAVO_VAZBA_ID","ST"."KA_ID" "ID","ST"."ID_GROUP" "PRAVO_ID","AG"."RIGHT_LEVEL_CODE" "RIGHT_LEVEL_CODE",DECODE("AG"."RIGHT_LEVEL_CODE",:B1,"AG"."NAME",NULL) "SPRAVO_JMENO","VW_LAT_705
6CA6C"."ITEM_1_0" "PER_ID",DECODE("VW_LAT_7056CA6C"."ITEM_1_0",NULL,:B2,:B3) "MA_PRAVO",NULL "PROF_ID" FROM "SX"."XXXXXXXX" "ST","XXXXXX"."XXXXX" "AG", LATERAL( (SELECT "XXX"."ID_PERSON" "ITEM_1_0" FROM "XXXXXX"."XXXX" "APG" WHERE "APG"."ID_GROUP"="AG"."ID_GROUP" AND (:B4 IS NULL OR "APG"."ID_PERSON"=:B5) AND "APG"."XXXFROM" IS NULL AND "APG"."XXXTO" IS NULL))(+) "VW_LAT_7056CA6C" WHERE "ST"."STRANKA_ID"=:B6 AND (
"AG"."RIGHT_LEVEL_CODE"=:B7 OR "VW_LAT_7056CA6C"."ITEM_1_0" IS NOT NULL) AND "ST"."STATUS"=:B8 AND "AG"."ID_GROUP"="ST"."ID_GROUP" AND "AG"."DISABLED"=:B9
DCL: Checking validity of lateral view decorrelation SEL$9B24B749 (#1)
DCL: Passed decorrelation validity for lateral view block SEL$9B24B749 (#1)
DCL: Decorrelation of lateral view query block SEL$9B24B749 (#1).
Registered qb: SEL$74777671 0x7aed1e90 (VIEW DECORRELATED SEL$9B24B749; SEL$9B24B749)
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
Symptoms |
Cause |
Solution |
References |