A customer complained that a query and its INSERT counterpart had different execution plans, and of course, the INSERT was slower.
First lets look at the SELECT :
SELECT ua_tr_rundatetime, ua_ch_treatmentcode, ua_tr_treatmentcode, ua_ch_cellid, ua_tr_cellid FROM (SELECT DISTINCT CH.treatmentcode AS UA_CH_TREATMENTCODE, CH.cellid AS UA_CH_CELLID FROM CH, DL WHERE CH.contactdatetime > SYSDATE - 5 AND CH.treatmentcode = DL.treatmentcode) CH_CELLS, (SELECT DISTINCT T.treatmentcode AS UA_TR_TREATMENTCODE, T.cellid AS UA_TR_CELLID, T.rundatetime AS UA_TR_RUNDATETIME FROM T, DL WHERE T.treatmentcode = DL.treatmentcode) TRT_CELLS WHERE CH_CELLS.ua_ch_treatmentcode(+) = TRT_CELLS.ua_tr_treatmentcode;
The query has 2 DISTINCT subqueries.
The execution plan shows one with DISTICT Placement transformation applied and not the other.
The view in Step 5 has the prefix VW_DTP which means DISTINCT Placement.
--------------------------------------------------------------------
| Id | Operation | Name | Cost (%CPU)
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 272K(100)
|* 1 | HASH JOIN OUTER | | 272K (1)
| 2 | VIEW | | 4408 (1)
| 3 | HASH UNIQUE | | 4408 (1)
|* 4 | HASH JOIN | | 4407 (1)
| 5 | VIEW | VW_DTP_48BAF62C | 1660 (2)
| 6 | HASH UNIQUE | | 1660 (2)
| 7 | TABLE ACCESS FULL | DL | 1644 (1)
| 8 | TABLE ACCESS FULL | T | 2744 (1)
| 9 | VIEW | | 267K (1)
| 10 | HASH UNIQUE | | 267K (1)
|* 11 | HASH JOIN | | 267K (1)
| 12 | PARTITION RANGE ITERATOR| | 266K (1)
|* 13 | TABLE ACCESS FULL | CH | 266K (1)
| 14 | TABLE ACCESS FULL | DL | 1644 (1)
--------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$AF418D5F / TRT_CELLS@SEL$1
3 - SEL$AF418D5F
5 - SEL$F6AECEDE / VW_DTP_48BAF62C@SEL$48BAF62C
6 - SEL$F6AECEDE
7 - SEL$F6AECEDE / DL@SEL$3
8 - SEL$AF418D5F / T@SEL$3
9 - SEL$2 / CH_CELLS@SEL$1
10 - SEL$2
13 - SEL$2 / CH@SEL$2
14 - SEL$2 / DL@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("CH_CELLS"."UA_CH_TREATMENTCODE"="TRT_CELLS"."UA_TR_TREATMENTCODE")
4 - access("T"."TREATMENTCODE"="ITEM_1")
11 - access("CH"."TREATMENTCODE"="DL"."TREATMENTCODE")
13 - filter("CH"."CONTACTDATETIME">SYSDATE@!-5)
The outline shows PLACE_DISTINCT(@"SEL$3" "DL"@"SEL$3")
indicating that the QB3 is the one that got the transformation.
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SEL$F6AECEDE")
OUTLINE_LEAF(@"SEL$AF418D5F")
PLACE_DISTINCT(@"SEL$3" "DL"@"SEL$3")
OUTLINE_LEAF(@"SEL$1")
OUTLINE(@"SEL$48BAF62C")
OUTLINE(@"SEL$3")
NO_ACCESS(@"SEL$1" "TRT_CELLS"@"SEL$1")
NO_ACCESS(@"SEL$1" "CH_CELLS"@"SEL$1")
LEADING(@"SEL$1" "TRT_CELLS"@"SEL$1" "CH_CELLS"@"SEL$1")
USE_HASH(@"SEL$1" "CH_CELLS"@"SEL$1")
FULL(@"SEL$2" "CH"@"SEL$2")
FULL(@"SEL$2" "DL"@"SEL$2")
LEADING(@"SEL$2" "CH"@"SEL$2" "DL"@"SEL$2")
USE_HASH(@"SEL$2" "DL"@"SEL$2")
USE_HASH_AGGREGATION(@"SEL$2")
NO_ACCESS(@"SEL$AF418D5F" "VW_DTP_48BAF62C"@"SEL$48BAF62C")
FULL(@"SEL$AF418D5F" "T"@"SEL$3")
LEADING(@"SEL$AF418D5F" "VW_DTP_48BAF62C"@"SEL$48BAF62C" "T"@"SEL$3")
USE_HASH(@"SEL$AF418D5F" "T"@"SEL$3")
USE_HASH_AGGREGATION(@"SEL$AF418D5F")
FULL(@"SEL$F6AECEDE" "DL"@"SEL$3")
USE_HASH_AGGREGATION(@"SEL$F6AECEDE")
END_OUTLINE_DATA
*/
The 10053 shows there is a comparative of cost with and without the transformation.
This means the transformation belongs to Cost-Based Query Transformations (CBQT).
In SEL$3 the optimization of the query block without the transformation is 6659.73
and with the transformation is 4408.41 so the transformation is kept.
GBP/DP: Checking validity of GBP/DP for query block SEL$3 (#3)
DP: Checking validity of distinct placement for query block SEL$3 (#3)
DP: Using search type: linear
DP: Considering distinct placement on query block SEL$3 (#3)
DP: Starting iteration 1, state space = (5) : (0)
DP: Original query
DP: Costing query block.
DP: Updated best state, Cost = 6659.73
DP: Starting iteration 2, state space = (5) : (1)
DP: Using DP transformation in this iteration.
DP: Transformed query
DP: Costing query block.
DP: Updated best state, Cost = 4408.41
DP: Doing DP on the original QB.
DP: Doing DP on the preserved QB.
In SEL$2 the cost without the transformation is less than with it so it is not kept.
GBP/DP: Checking validity of GBP/DP for query block SEL$2 (#2)
DP: Checking validity of distinct placement for query block SEL$2 (#2)
DP: Using search type: linear
DP: Considering distinct placement on query block SEL$2 (#2)
DP: Starting iteration 1, state space = (3) : (0)
DP: Original query
DP: Costing query block.
DP: Updated best state, Cost = 267936.93
DP: Starting iteration 2, state space = (3) : (1)
DP: Using DP transformation in this iteration.
DP: Transformed query
DP: Costing query block.
DP: Not update best state, Cost = 267951.66
To the same query an INSERT INTO is added and the result is a very different execution plan.
INSERT INTO cc (ua_tr_rundatetime, ua_ch_treatmentcode, ua_tr_treatmentcode, ua_ch_cellid, ua_tr_cellid)SELECT ua_tr_rundatetime, ua_ch_treatmentcode, ua_tr_treatmentcode, ua_ch_cellid, ua_tr_cellidFROM (SELECT DISTINCT CH.treatmentcode AS UA_CH_TREATMENTCODE, CH.cellid AS UA_CH_CELLID FROM CH, DL WHERE CH.contactdatetime > SYSDATE - 5 AND CH.treatmentcode = DL.treatmentcode) CH_CELLS, (SELECT DISTINCT T.treatmentcode AS UA_TR_TREATMENTCODE, T.cellid AS UA_TR_CELLID, T.rundatetime AS UA_TR_RUNDATETIME FROM T, DL WHERE T.treatmentcode = DL.treatmentcode) TRT_CELLSWHERE CH_CELLS.ua_ch_treatmentcode(+) = TRT_CELLS.ua_tr_treatmentcode;----------------------------------------------------------| Id | Operation | Name | Cost (%CPU)----------------------------------------------------------| 0 | INSERT STATEMENT | | 274K(100)| 1 | LOAD TABLE CONVENTIONAL | | |* 2 | HASH JOIN OUTER | | 274K (1)| 3 | VIEW | | 6660 (1)| 4 | SORT UNIQUE | | 6660 (1)|* 5 | HASH JOIN | | 6659 (1)| 6 | TABLE ACCESS FULL | DL | 1644 (1)| 7 | TABLE ACCESS FULL | T | 2744 (1)| 8 | VIEW | | 267K (1)| 9 | SORT UNIQUE | | 267K (1)|* 10 | HASH JOIN | | 267K (1)| 11 | PARTITION RANGE ITERATOR| | 266K (1)|* 12 | TABLE ACCESS FULL | CH | 266K (1)| 13 | TABLE ACCESS FULL | DL | 1644 (1)----------------------------------------------------------Query Block Name / Object Alias (identified by operation id):------------------------------------------------------------- 1 - SEL$1 3 - SEL$3 / TRT_CELLS@SEL$1 4 - SEL$3 6 - SEL$3 / DL@SEL$3 7 - SEL$3 / T@SEL$3 8 - SEL$2 / CH_CELLS@SEL$1 9 - SEL$2 12 - SEL$2 / CH@SEL$2 13 - SEL$2 / DL@SEL$2Predicate Information (identified by operation id):--------------------------------------------------- 2 - access("CH_CELLS"."UA_CH_TREATMENTCODE"="TRT_CELLS"."UA_TR_TREATMENTCODE") 5 - access("T"."TREATMENTCODE"="DL"."TREATMENTCODE") 10 - access("CH"."TREATMENTCODE"="DL"."TREATMENTCODE") 12 - filter("CH"."CONTACTDATETIME">SYSDATE@!-5)Outline Data------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('11.2.0.3') DB_VERSION('11.2.0.3') ALL_ROWS OUTLINE_LEAF(@"SEL$2") OUTLINE_LEAF(@"SEL$3") OUTLINE_LEAF(@"SEL$1") OUTLINE_LEAF(@"INS$1") FULL(@"INS$1" "CC"@"INS$1") NO_ACCESS(@"SEL$1" "TRT_CELLS"@"SEL$1") NO_ACCESS(@"SEL$1" "CH_CELLS"@"SEL$1") LEADING(@"SEL$1" "TRT_CELLS"@"SEL$1" "CH_CELLS"@"SEL$1") USE_HASH(@"SEL$1" "CH_CELLS"@"SEL$1") FULL(@"SEL$2" "CH"@"SEL$2") FULL(@"SEL$2" "DL"@"SEL$2") LEADING(@"SEL$2" "CH"@"SEL$2" "DL"@"SEL$2") USE_HASH(@"SEL$2" "DL"@"SEL$2") USE_HASH_AGGREGATION(@"SEL$2") FULL(@"SEL$3" "DL"@"SEL$3") FULL(@"SEL$3" "T"@"SEL$3") LEADING(@"SEL$3" "DL"@"SEL$3" "T"@"SEL$3") USE_HASH(@"SEL$3" "T"@"SEL$3") USE_HASH_AGGREGATION(@"SEL$3") END_OUTLINE_DATA */
There is no DISTINCT Placement view and no hint.The 10053 trace shows a new legend "DP: Bypassed: Not SELECT"implying that this is a transformation that it is possible only for SELECTs.
GBP/DP: Checking validity of GBP/DP for query block SEL$3 (#4)
DP: Checking validity of distinct placement for query block SEL$3 (#4)
DP: Bypassed: Not SELECT.
GBP/DP: Checking validity of GBP/DP for query block SEL$2 (#3)
DP: Checking validity of distinct placement for query block SEL$2 (#3)
DP: Bypassed: Not SELECT.
In 12.1 (and hopefully in 11.2.0.4 when released) the restriction on applying CBQT to some DMLs and DDLs (like CTAS) is lifted.This is documented in BugTag Note:10013899.8 Allow CBQT for some DML / DDLAnd interestingly enough, it is possible to have a one-off patch in 11.2.0.3.
SQL> select DESCRIPTION,OPTIMIZER_FEATURE_ENABLE,IS_DEFAULT
2 from v$system_fix_control where BUGNO='10013899';
DESCRIPTION
----------------------------------------------------------------
OPTIMIZER_FEATURE_ENABLE IS_DEFAULT
------------------------- ----------
enable some transformations for DDL and DML statements
11.2.0.4 1
