Same SELECT used in an INSERT has different execution plan

Posted by amacias on Oracle Blogs See other posts from Oracle Blogs or by amacias
Published on Wed, 3 Jul 2013 00:40:40 +0000 Indexed on 2013/07/03 5:12 UTC
Read the original article Hit count: 280

Filed under:

/Oracle Optimizer

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_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;

----------------------------------------------------------
| 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$2

Predicate 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 / DDL
And 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

Developer IT

Same SELECT used in an INSERT has different execution plan - Developer IT

Same SELECT used in an INSERT has different execution plan

/Oracle Optimizer

Related posts about /Oracle Optimizer

E-Business Suite Technology Sessions at OpenWorld 2012

26 Days: Countdown to Oracle OpenWorld 2012

OTN ???? ?????? ???????

SOA PARTNER COMMUNITY NEWSLETTER JULY 2012

Oracle ADF Coverage at OOW

Categories cloud