NO_QUERY_TRANSFORMATION Hint is Ignored… Well, Almost Ignored

3 01 2011

January 3, 2011

A couple of days ago I used a NO_QUERY_TRANSFORMATION hint to permit a query (provided by Martin Berger) to execute, which on the surface appeared to be quite sane, but without hints the query would usually fail to execute.  A test case version of the query worked on 11.2.0.1 running on 64 bit Windows on Dec 31, 2010, but suffered from the first known year 2011 bug ( ;-) ), and failed to execute on Jan 1, 2011 in the same database.  

A test case version of that sane looking query looks like this: 

select
  owner,
  view_name
from
  (select
    v.owner,
    v.view_name,
    o.object_type
  from
    dba_views v,
    dba_objects o
  where
    v.owner = o.owner
    AND v.view_name = o.object_name
    AND o.object_type='VIEW'
    AND o.status='VALID'
    AND v.owner ='SYS'  ) vv
where
  dbms_metadata.get_ddl(VV.OBJECT_TYPE, vv.view_name, vv.owner) like '%TEST%'; 

If you think that the DBMS_METADATA.GET_DDL call in the WHERE clause is at fault, you might try this query instead:

select
  owner,
  view_name
from
  (SELECT
    dbms_metadata.get_ddl('VIEW', vv.view_name, vv.owner) DDL,
    OWNER,
    VIEW_NAME
  FROM
    (select
      v.owner,
      v.view_name,
      o.object_type
    from
      dba_views v,
      dba_objects o
    where
      v.owner = o.owner
      AND v.view_name = o.object_name
      AND o.object_type='VIEW'
      AND o.status='VALID'
      AND v.owner ='SYS'  ) vv
  )
WHERE
  DDL like '%TEST%'; 

So, the NO_QUERY_TRANSFORMATION hint was not ignored when the above queries were hinted, and the hint allowed both of the above queries to execute when that hint was added immediately after the SELECT.  Just to make certain that there is no magic involved with this hint, let's take a look at the description from the Oracle Database 11.2 documentation:

"The NO_QUERY_TRANSFORMATION hint instructs the optimizer to skip all query transformations, including but not limited to OR-expansion, view merging, subquery unnesting, star transformation, and materialized view rewrite. For example:

SELECT /*+ NO_QUERY_TRANSFORMATION */ employee_id, last_name
  FROM (SELECT * FROM employees e) v
  WHERE v.last_name = 'Smith';         "

I think that the above explanation is easy to understand - essentially: leave my query alone!

So, what led to this blog article?  I saw a statement in the "Pro Oracle SQL" book on page 329 that I wanted to verify since I had never used the ANY keyword in a SQL statement:

 "There isn't much to say about the =ANY version [of the SQL statement] since it is merely an alternate way of writing IN."

I set up a simple test case script with two tables to see if I could demonstrate to myself that a query with the ANY keyword is handled exactly the same as a query using the IN syntax:

CREATE TABLE T1 AS
SELECT
  ROWNUM C1,
  LPAD('A',100,'A') PADDING
FROM
  DUAL
CONNECT BY
  LEVEL<=100;

CREATE TABLE T2 AS
SELECT
  ROWNUM C0,
  TRUNC((MOD(ROWNUM-1,80)+1)*1.2) C1,
  LPAD('A',100,'A') PADDING
FROM
  DUAL
CONNECT BY
  LEVEL<=1000;

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1')
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T2') 

Let's assume that table T1 is a list of items in a hardware store, and table T2 is a list of items that were purchased in the last month.  We just need a quick way to determine the list of items that were purchased in the last month (we might also need to select another column, such as the product description from table T1, thus the need to access both tables T1 and T2).  So, we put together the following two SQL statements to see if the ANY syntax really is equivalent to the IN syntax:

SELECT
  T1.C1
FROM
  T1
WHERE
  T1.C1 IN (
    SELECT
      T2.C1
    FROM
      T2);

SELECT
  T1.C1
FROM
  T1
WHERE
  T1.C1 = ANY (
    SELECT
      T2.C1
    FROM
      T2); 

First, let's check the execution plans:

SET AUTOTRACE TRACEONLY EXPLAIN

SELECT
  T1.C1
FROM
  T1
WHERE
  T1.C1 IN (
    SELECT
      T2.C1
    FROM
      T2);

Execution Plan
----------------------------------------------------------
Plan hash value: 1713220790

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    80 |   480 |    11  (10)| 00:00:01 |
|*  1 |  HASH JOIN SEMI    |      |    80 |   480 |    11  (10)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1   |   100 |   300 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   |  1000 |  3000 |     7   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."C1"="T2"."C1")

-

SELECT
  T1.C1
FROM
  T1
WHERE
  T1.C1 = ANY (
    SELECT
      T2.C1
    FROM
      T2);

Execution Plan
----------------------------------------------------------
Plan hash value: 1713220790

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    80 |   480 |    11  (10)| 00:00:01 |
|*  1 |  HASH JOIN SEMI    |      |    80 |   480 |    11  (10)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1   |   100 |   300 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   |  1000 |  3000 |     7   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."C1"="T2"."C1") 

The execution plans appear to be the same.  But wait, through the magic of automatic query transformations, Oracle's optimizer might have actually rewritten both SQL statements so that the actual SQL executed for both queries is identical.  I recall experiencing performance problems in Oracle Database 8.1.7.x with certain IN type queries, while the equivalent EXISTS queries were fast.  Let's use the magic NO_QUERY_TRANSFORMATION hint that allowed the SQL statement at the start of this blog article to execute, so that we may prevent the optimizer from rewriting the SQL statement.  That hint should help indicate whether or not the SQL statement using the ANY syntax really is equivalent to the query using the IN syntax.  Note that this test is performed on Oracle Database 11.2.0.1 and you might receive different results on different Oracle release versions.  The hinted test queries and their execution plans:

SELECT /*+ NO_QUERY_TRANSFORMATION */
  T1.C1
FROM
  T1
WHERE
  T1.C1 IN (
    SELECT
      T2.C1
    FROM
      T2);

Execution Plan
----------------------------------------------------------
Plan hash value: 895956251

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     3 |   103   (0)| 00:00:02 |
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| T1   |   100 |   300 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T2   |     2 |     6 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter( EXISTS (SELECT 0 FROM "T2" "T2" WHERE "T2"."C1"=:B1))
   3 - filter("T2"."C1"=:B1)

-

SELECT /*+ NO_QUERY_TRANSFORMATION */
  T1.C1
FROM
  T1
WHERE
  T1.C1 = ANY (
    SELECT
      T2.C1
    FROM
      T2);

Execution Plan
----------------------------------------------------------
Plan hash value: 895956251

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     3 |   103   (0)| 00:00:02 |
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| T1   |   100 |   300 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T2   |     2 |     6 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter( EXISTS (SELECT 0 FROM "T2" "T2" WHERE "T2"."C1"=:B1))
   3 - filter("T2"."C1"=:B1) 

In you look closely, you will see that the execution plans changed from their unhinted versions, and both of the hinted execution plans are identical.  But wait!  Take a close look at the Predicate Information section of the execution plan, specifically the line for plan ID 1.  Where did that EXISTS entry come from - the hint should have told Oracle's optimizer to not transform the query?  It appears that both queries were transformed into EXISTS queries.  Let's take a look at the 10053 trace file for a slightly modified version of the queries (so that we will force a hard parse):

ALTER SESSION SET TRACEFILE_IDENTIFIER='NO_QUERY_TRANSFORM'; 
ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1';

SELECT /*+ NO_QUERY_TRANSFORMATION */
  T1.C1,
  1
FROM
  T1
WHERE
  T1.C1 IN (
    SELECT
      T2.C1
    FROM
      T2);

SELECT /*+ NO_QUERY_TRANSFORMATION */
  T1.C1,
  1
FROM
  T1
WHERE
  T1.C1 = ANY (
    SELECT
      T2.C1
    FROM
      T2);

ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF'; 

Taking a look in the trace file:

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "SYS_ALIAS_1"."C1" "C1",1 "1" FROM "TESTUSER"."T1" "SYS_ALIAS_1" WHERE  EXISTS (SELECT 0 FROM "TESTUSER"."T2" "T2" WHERE "T2"."C1"="SYS_ALIAS_1"."C1")
...
sql=SELECT /*+ NO_QUERY_TRANSFORMATION */
  T1.C1,
  1
FROM
  T1
WHERE
  T1.C1 IN (
    SELECT
      T2.C1
    FROM
      T2)
----- Explain Plan Dump -----
----- Plan Table -----

============
Plan Table
============
--------------------------------------+-----------------------------------+
| Id  | Operation           | Name    | Rows  | Bytes | Cost  | Time      |
--------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT    |         |       |       |   103 |           |
| 1   |  FILTER             |         |       |       |       |           |
| 2   |   TABLE ACCESS FULL | T1      |   100 |   300 |     3 |  00:00:01 |
| 3   |   TABLE ACCESS FULL | T2      |     2 |     6 |     2 |  00:00:01 |
--------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - filter( IS NOT NULL)
3 - filter("T2"."C1"=:B1)

----------------------------------------------------------------------------------

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "SYS_ALIAS_1"."C1" "C1",1 "1" FROM "TESTUSER"."T1" "SYS_ALIAS_1" WHERE  EXISTS (SELECT 0 FROM "TESTUSER"."T2" "T2" WHERE "T2"."C1"="SYS_ALIAS_1"."C1")
...
sql=SELECT /*+ NO_QUERY_TRANSFORMATION */
  T1.C1,
  1
FROM
  T1
WHERE
  T1.C1 = ANY (
    SELECT
      T2.C1
    FROM
      T2)
----- Explain Plan Dump -----
----- Plan Table -----

============
Plan Table
============
--------------------------------------+-----------------------------------+
| Id  | Operation           | Name    | Rows  | Bytes | Cost  | Time      |
--------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT    |         |       |       |   103 |           |
| 1   |  FILTER             |         |       |       |       |           |
| 2   |   TABLE ACCESS FULL | T1      |   100 |   300 |     3 |  00:00:01 |
| 3   |   TABLE ACCESS FULL | T2      |     2 |     6 |     2 |  00:00:01 |
--------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - filter( IS NOT NULL)
3 - filter("T2"."C1"=:B1) 

From the above, we are able to see that despite the NO_QUERY_TRANSFORMATION hint, both queries were written identically to use an EXISTS clause.  Maybe the NO_QUERY_TRANSFORMATION hint was ignored?  From the 10053 trace file:

End of Optimizer State Dump
Dumping Hints
=============
  atom_hint=(@=000007FFA41C2E00 err=0 resol=0 used=1 token=986 org=1 lvl=1 txt=NO_QUERY_TRANSFORMATION ())
====================== END SQL Statement Dump ====================== 

The above shows that the hint did not result in an error, and was used.  Well, maybe we should just say that the NO_QUERY_TRANSFORMATION hint was almost ignored.


Actions

Information

9 responses

3 01 2011
Jonathan Lewis

Charles,

It could be quite entertaining to think about what Oracle would do with ANSI SQL if you include the /*+ no_query_transformation */ hint. If it meant what it appears to mean then Oracle wouldn’t be able to optimise it at all because ANSI has to be transformed BEFORE optimisation.

I’ve always assumed that the hint meant “no cost based query transformation” – which would allow some “heuristic” transformations and “canonical” transformations to take place. (Canonical probably isn’t the right word, but I was thinking of the IN / EXISTS rewrite that ought to have nothing to do with cost, and simply acted to standardise the text).

3 01 2011
Charles Hooper

Jonathan,

Thank you for the comments. I am probably remembering incorrectly, but I thought that the decision to convert an IN type query to an EXISTS type query was a cost-based decision starting with Oracle Database 10.1 – but maybe I am confusing that transformation decision with the transformation decision to convert an IN type query into an inline view.

I mentioned in the blog article that Oracle Database 8.1.7.x did not automatically transform IN type queries into EXISTS type queries, so if we were to set OPTIMIZER_FEATURES_ENABLE to 8.1.7, we should see that same behavior. Here is a test that was performed on Oracle Database 10.2.0.2:

ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE='8.1.7';
ALTER SESSION SET TRACEFILE_IDENTIFIER='NO_QUERY_TRANSFORM_817';
 
ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1';
 
SELECT
  T1.C1,
  2
FROM
  T1
WHERE
  T1.C1 IN (
    SELECT
      T2.C1
    FROM
      T2);
 
SELECT /*+ NO_QUERY_TRANSFORMATION */
  T1.C1,
  2
FROM
  T1
WHERE
  T1.C1 IN (
    SELECT
      T2.C1
    FROM
      T2);
 
ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF';
 
ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE='10.2.0.2';
ALTER SESSION SET TRACEFILE_IDENTIFIER='NO_QUERY_TRANSFORM_102';
 
ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1';
 
SELECT
  T1.C1,
  2
FROM
  T1
WHERE
  T1.C1 IN (
    SELECT
      T2.C1
    FROM
      T2);
 
SELECT /*+ NO_QUERY_TRANSFORMATION */
  T1.C1,
  2
FROM
  T1
WHERE
  T1.C1 IN (
    SELECT
      T2.C1
    FROM
      T2);

ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF';

The result? The transformation was *not* made from an IN type query to an EXISTS type query unless the NO_QUERY_TRANSFORMATION hint was provided. From looking at the 10053 trace file, it appears that the optimizer did not even consider transforming the query to an EXISTS type query unless the NO_QUERY_TRANSFORMATION hint was provided. Here is partial output from the 10053 trace file:
OPTIMIZER_FEATURES_ENABLE='8.1.7', unhinted:

***************
Subquery Unnest
***************
SU: Considering subquery unnesting in query block SEL$1 (#0)
SU:   Transform an ANY subquery to semi-join or distinct.
Registered qb: SEL$683B0107 0x9de4df8 (SUBQ INTO VIEW FOR COMPLEX UNNEST SEL$2)
  signature (): qb_name=SEL$683B0107 nbfros=1 flg=0
    fro(0): flg=0 objn=114487 hint_alias="T2"@"SEL$2"
Registered qb: SEL$5DA710D3 0x9de7690 (SUBQUERY UNNEST SEL$1; SEL$2)
  signature (): qb_name=SEL$5DA710D3 nbfros=2 flg=0
    fro(0): flg=0 objn=114486 hint_alias="T1"@"SEL$1"
    fro(1): flg=5 objn=0 hint_alias="VW_NSO_1"@"SEL$5DA710D3"
...
******* UNPARSED QUERY IS *******
SELECT "T1"."C1" "C1",2 "2" FROM  (SELECT DISTINCT "T2"."C1" "$nso_col_1" FROM 
"TESTUSER"."T2" "T2") "VW_NSO_1","TESTUSER"."T1" "T1" WHERE "T1"."C1"="VW_NSO_1"."$nso_col_1"
kkoqbc-end
          : call(in-use=50912, alloc=65448), compile(in-use=47944, alloc=50120)
apadrv-end: call(in-use=50912, alloc=65448), compile(in-use=49136, alloc=50120)
 
sql_id=5605v8hmasq62.
Current SQL statement for this session:
SELECT
  T1.C1,
  2
FROM
  T1
WHERE
  T1.C1 IN (
    SELECT
      T2.C1
    FROM
      T2)
 
============
Plan Table
============
----------------------------------------+-----------------------------------+
| Id  | Operation             | Name    | Rows  | Bytes | Cost  | Time      |
----------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT      |         |       |       |     6 |           |
| 1   |  HASH JOIN            |         |    80 |  1280 |     6 |           |
| 2   |   VIEW                | VW_NSO_1|    80 |  1040 |     4 |           |
| 3   |    SORT UNIQUE        |         |    80 |   240 |     4 |           |
| 4   |     TABLE ACCESS FULL | T2      |  1000 |  3000 |     1 |           |
| 5   |   TABLE ACCESS FULL   | T1      |   100 |   300 |     1 |           |
----------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - access("T1"."C1"="$nso_col_1")

OPTIMIZER_FEATURES_ENABLE='8.1.7', hinted with NO_QUERY_TRANSFORMATION:

Registered qb: SEL$1 0x9de7690 (PARSER)
  signature (): qb_name=SEL$1 nbfros=1 flg=0
    fro(0): flg=4 objn=114486 hint_alias="T1"@"SEL$1"
Registered qb: SEL$2 0x9de4df8 (PARSER)
  signature (): qb_name=SEL$2 nbfros=1 flg=0
    fro(0): flg=4 objn=114487 hint_alias="T2"@"SEL$2"
**************************
Predicate Move-Around (PM)
**************************
PM: Considering predicate move-around in SEL$1 (#0).
PM:   Checking validity of predicate move-around in SEL$1 (#0).
PM:     PM bypassed: Predicate move-around not enabled.
CBQT: Validity checks failed for 2dqpt9zsngrrm.
CVM: Considering view merge in query block SEL$1 (#0)
CVM: Considering view merge in query block SEL$2 (#0)
CBQT: Validity checks failed for 2dqpt9zsngrrm.
***************
Subquery Unnest
***************
SU: Considering subquery unnesting in query block SEL$1 (#0)
SU:   Unnesting subquery SEL$2 (#0)
SU:     SU bypassed: No unnest hint.
*************************
Set-Join Conversion (SJC)
*************************
SJC: Considering set-join conversion in SEL$1 (#0).
SJC: Considering set-join conversion in SEL$2 (#0).
**************************
Predicate Move-Around (PM)
**************************
PM: Considering predicate move-around in SEL$1 (#0).
PM:   Checking validity of predicate move-around in SEL$1 (#0).
PM:     PM bypassed: Predicate move-around not enabled.
FPD: Considering simple filter push in SEL$1 (#0)
FPD:   Current where clause predicates in SEL$1 (#0) :
          EXISTS (SELECT 0 FROM "T2" "T2")
FPD: Considering simple filter push in SEL$2 (#0)
FPD:   Current where clause predicates in SEL$2 (#0) :
         "T2"."C1"=:B1
kkogcp: try to generate transitive predicate from check constraints for SEL$2 (#0)
predicates with check contraints: "T2"."C1"=:B1
after transitive predicate generation: "T2"."C1"=:B1
finally: "T2"."C1"=:B1
kkogcp: try to generate transitive predicate from check constraints for SEL$1 (#0)
predicates with check contraints:  EXISTS (SELECT 0 FROM "T2" "T2")
after transitive predicate generation:  EXISTS (SELECT 0 FROM "T2" "T2")
finally:  EXISTS (SELECT 0 FROM "T2" "T2")
...
******* UNPARSED QUERY IS *******
SELECT "SYS_ALIAS_1"."C1" "C1",2 "2" FROM "TESTUSER"."T1" "SYS_ALIAS_1" 
WHERE  EXISTS (SELECT 0 FROM "TESTUSER"."T2" "T2" WHERE "T2"."C1"="SYS_ALIAS_1"."C1")
kkoqbc-end
          : call(in-use=45136, alloc=65448), compile(in-use=45576, alloc=46360)
apadrv-end: call(in-use=45136, alloc=65448), compile(in-use=46744, alloc=50120)
 
sql_id=0xskx5manu92p.
Current SQL statement for this session:
SELECT /*+ NO_QUERY_TRANSFORMATION */
  T1.C1,
  2
FROM
  T1
WHERE
  T1.C1 IN (
    SELECT
      T2.C1
    FROM
      T2)
 
============
Plan Table
============
--------------------------------------+-----------------------------------+
| Id  | Operation           | Name    | Rows  | Bytes | Cost  | Time      |
--------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT    |         |       |       |     1 |           |
| 1   |  FILTER             |         |       |       |       |           |
| 2   |   TABLE ACCESS FULL | T1      |     5 |    15 |     1 |           |
| 3   |   TABLE ACCESS FULL | T2      |    13 |    39 |     1 |           |
--------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - filter( IS NOT NULL)
3 - filter("T2"."C1"=:B1)

--------------------------------

OPTIMIZER_FEATURES_ENABLE='10.2.0.2', unhinted:

*******************************
Cost-Based Complex View Merging
*******************************
CVM: Finding query blocks in SEL$5DA710D3 (#1) that are valid to merge.
Query block (0000000009DE0F58) before join elimination:
SQL:
Query block (0000000009DE0F58) unchanged
SU:   Transforming ANY subquery to a join.
...
******* UNPARSED QUERY IS *******
SELECT "T1"."C1" "C1",2 "2" FROM "TESTUSER"."T2" "T2","TESTUSER"."T1" "T1" 
WHERE "T1"."C1"="T2"."C1"
kkoqbc-end
          : call(in-use=34992, alloc=49080), compile(in-use=66128, alloc=103680)
apadrv-end: call(in-use=34992, alloc=49080), compile(in-use=66984, alloc=103680)
 
sql_id=5605v8hmasq62.
Current SQL statement for this session:
SELECT
  T1.C1,
  2
FROM
  T1
WHERE
  T1.C1 IN (
    SELECT
      T2.C1
    FROM
      T2)
 
============
Plan Table
============
--------------------------------------+-----------------------------------+
| Id  | Operation           | Name    | Rows  | Bytes | Cost  | Time      |
--------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT    |         |       |       |     6 |           |
| 1   |  HASH JOIN SEMI     |         |    80 |   480 |     6 |  00:00:01 |
| 2   |   TABLE ACCESS FULL | T1      |   100 |   300 |     2 |  00:00:01 |
| 3   |   TABLE ACCESS FULL | T2      |  1000 |  3000 |     3 |  00:00:01 |
--------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - access("T1"."C1"="T2"."C1")

OPTIMIZER_FEATURES_ENABLE='10.2.0.2', hinted with NO_QUERY_TRANSFORMATION:

COST-BASED QUERY TRANSFORMATIONS
********************************
FPD: Considering simple filter push (pre rewrite) in SEL$1 (#0)
FPD:   Current where clause predicates in SEL$1 (#0) :
         "T1"."C1"=ANY (SELECT "T2"."C1" FROM "T2" "T2")
Registered qb: SEL$1 0x9de0f58 (COPY SEL$1)
  signature(): NULL
Registered qb: SEL$2 0x119ffd20 (COPY SEL$2)
  signature(): NULL
*****************************
Cost-Based Subquery Unnesting
*****************************
SU: No subqueries to consider in query block SEL$2 (#2).
SU: Considering subquery unnesting in query block SEL$1 (#1)
SU: Performing unnesting that does not require costing.
SU: Considering subquery unnest on SEL$1 (#1).
SU:   Checking validity of unnesting subquery SEL$2 (#2)
SU:     SU bypassed: Negative hint found.
*******************************
Cost-Based Complex View Merging
*******************************
CVM: Finding query blocks in SEL$1 (#1) that are valid to merge.
*************************
Set-Join Conversion (SJC)
*************************
SJC: Considering set-join conversion in SEL$1 (#1).
SJC: Considering set-join conversion in SEL$2 (#2).
**************************
Predicate Move-Around (PM)
**************************
PM: Considering predicate move-around in SEL$1 (#1).
PM:   Checking validity of predicate move-around in SEL$1 (#1).
PM:     PM bypassed: Outer query contains no views.
***********************************
Cost-Based Filter Predicate Pull-Up
***********************************
***********************************
Cost-Based Join Predicate Push-down
***********************************
JPPD: Checking validity of push-down in query block SEL$2 (#2)
JPPD:   No view found to push predicate into.
JPPD: Checking validity of push-down in query block SEL$1 (#1)
JPPD:   No view found to push predicate into.
JPPD: Applying transformation directives
JPPD: Checking validity of push-down in query block SEL$2 (#2)
JPPD:   No view found to push predicate into.
JPPD: Checking validity of push-down in query block SEL$1 (#1)
JPPD:   No view found to push predicate into.
FPD: Considering simple filter push in SEL$1 (#1)
FPD:   Current where clause predicates in SEL$1 (#1) :
          EXISTS (SELECT 0 FROM "T2" "T2")
FPD: Considering simple filter push in SEL$2 (#2)
FPD:   Current where clause predicates in SEL$2 (#2) :
         "T2"."C1"=:B1
kkogcp: try to generate transitive predicate from check constraints for SEL$2 (#2)
predicates with check contraints: "T2"."C1"=:B1
after transitive predicate generation: "T2"."C1"=:B1
finally: "T2"."C1"=:B1
kkogcp: try to generate transitive predicate from check constraints for SEL$1 (#1)
predicates with check contraints:  EXISTS (SELECT 0 FROM "T2" "T2")
after transitive predicate generation:  EXISTS (SELECT 0 FROM "T2" "T2")
finally:  EXISTS (SELECT 0 FROM "T2" "T2")
...
******* UNPARSED QUERY IS *******
SELECT /*+ */ "SYS_ALIAS_1"."C1" "C1",2 "2" FROM "TESTUSER"."T1" "SYS_ALIAS_1" 
WHERE  EXISTS (SELECT /*+ */ 0 FROM "TESTUSER"."T2" "T2" WHERE "T2"."C1"="SYS_ALIAS_1"."C1")
kkoqbc-end
          : call(in-use=93048, alloc=98184), compile(in-use=78880, alloc=103680)
apadrv-end: call(in-use=93048, alloc=98184), compile(in-use=80000, alloc=103680)
 
sql_id=0xskx5manu92p.
Current SQL statement for this session:
SELECT /*+ NO_QUERY_TRANSFORMATION */
  T1.C1,
  2
FROM
  T1
WHERE
  T1.C1 IN (
    SELECT
      T2.C1
    FROM
      T2)
 
============
Plan Table
============
--------------------------------------+-----------------------------------+
| Id  | Operation           | Name    | Rows  | Bytes | Cost  | Time      |
--------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT    |         |       |       |   102 |           |
| 1   |  FILTER             |         |       |       |       |           |
| 2   |   TABLE ACCESS FULL | T1      |   100 |   300 |     2 |  00:00:01 |
| 3   |   TABLE ACCESS FULL | T2      |     2 |     6 |     2 |  00:00:01 |
--------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - filter( IS NOT NULL)
3 - filter("T2"."C1"=:B1)

Other than the differences in cardinality estimates and cost estimates, the execution plans with the NO_QUERY_TRANSFORMATION hint appear to be identical, regardless of the OPTIMIZER_FEATURES_ENABLE value. I am not expecting an explanation of why this happens, I just thought that it would be interesting to post these findings.

3 01 2011
Charles Hooper

I just noticed something as a result of what Kerry Osborne wrote into chapter 11 (page 342) of the SQL book. Autotrace will show in the Predicate Information section more detailed information, for example “1 – filter( EXISTS (SELECT 0 FROM “T2″ “T2″ WHERE “T2″.”C1″=:B1))“, while the 10053 trace file and DBMS_XPLAN.DISPLAY_CURSOR function display less detail, for example “1 – filter( IS NOT NULL)“.

For example, if we rerun the script from my previous comment with AUTOTRACE enabled:

SET AUTOTRACE TRACEONLY EXPLAIN

We will see the more extensive detail provided by AUTOTRACE:

OPTIMIZER_FEATURES_ENABLE='8.1.7', unhinted:

SQL> SELECT
  2    T1.C1,
  3    2
  4  FROM
  5    T1
  6  WHERE
  7    T1.C1 IN (
  8      SELECT
  9        T2.C1
 10      FROM
 11        T2);

Execution Plan
----------------------------------------------------------
Plan hash value: 1304214603

-----------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost  |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |    80 |  1280 |     6 |
|*  1 |  HASH JOIN           |          |    80 |  1280 |     6 |
|   2 |   VIEW               | VW_NSO_1 |    80 |  1040 |     4 |
|   3 |    SORT UNIQUE       |          |    80 |   240 |     4 |
|   4 |     TABLE ACCESS FULL| T2       |  1000 |  3000 |     1 |
|   5 |   TABLE ACCESS FULL  | T1       |   100 |   300 |     1 |
-----------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."C1"="$nso_col_1")

Note
-----
   - cpu costing is off (consider enabling it)

OPTIMIZER_FEATURES_ENABLE='8.1.7', hinted with NO_QUERY_TRANSFORMATION:

SQL> SELECT /*+ NO_QUERY_TRANSFORMATION */
  2    T1.C1,
  3    2
  4  FROM
  5    T1
  6  WHERE
  7    T1.C1 IN (
  8      SELECT
  9        T2.C1
 10      FROM
 11        T2);

Execution Plan
----------------------------------------------------------
Plan hash value: 895956251

-----------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost  |
-----------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     5 |    15 |     1 |
|*  1 |  FILTER            |      |       |       |       |
|   2 |   TABLE ACCESS FULL| T1   |     5 |    15 |     1 |
|*  3 |   TABLE ACCESS FULL| T2   |    13 |    39 |     1 |
-----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter( EXISTS (SELECT 0 FROM "T2" "T2" WHERE "T2"."C1"=:B1))
   3 - filter("T2"."C1"=:B1)

Note
-----
   - cpu costing is off (consider enabling it)

OPTIMIZER_FEATURES_ENABLE='10.2.0.2', unhinted:

SQL> SELECT
  2    T1.C1,
  3    2
  4  FROM
  5    T1
  6  WHERE
  7    T1.C1 IN (
  8      SELECT
  9        T2.C1
 10      FROM
 11        T2);

Execution Plan
----------------------------------------------------------
Plan hash value: 1713220790

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    80 |   480 |     6  (17)| 00:00:01 |
|*  1 |  HASH JOIN SEMI    |      |    80 |   480 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1   |   100 |   300 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   |  1000 |  3000 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."C1"="T2"."C1")

OPTIMIZER_FEATURES_ENABLE='10.2.0.2', hinted with NO_QUERY_TRANSFORMATION:

SQL> SELECT /*+ NO_QUERY_TRANSFORMATION */
  2    T1.C1,
  3    2
  4  FROM
  5    T1
  6  WHERE
  7    T1.C1 IN (
  8      SELECT
  9        T2.C1
 10      FROM
 11        T2);

Execution Plan
----------------------------------------------------------
Plan hash value: 895956251

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     3 |   102   (0)| 00:00:01 |
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| T1   |   100 |   300 |     2   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T2   |     2 |     6 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter( EXISTS (SELECT /*+ */ 0 FROM "T2" "T2" WHERE
              "T2"."C1"=:B1))
   3 - filter("T2"."C1"=:B1)

Of course it is possible to pull the execution plans from the server's memory to compare with something like this:

SET LINESIZE 140
SET TRIMSPOOL ON
SET PAGESIZE 1000

SELECT /*+ LEADING(S) */
  T.PLAN_TABLE_OUTPUT
FROM
  (SELECT
    SQL_ID,
    CHILD_NUMBER
  FROM
    V$SQL
  WHERE
    SQL_TEXT LIKE 'SELECT %T1.C1 IN (%') S,
  TABLE(DBMS_XPLAN.DISPLAY_CURSOR(S.SQL_ID,S.CHILD_NUMBER,'TYPICAL')) T;

I guess that it is good to try to learn something new every day.

5 01 2011
Kerry Osborne

Interesting idea Jonathan. I’m surprised you haven’t tried that yet. ;) I gave a quick try and managed to basically hang my session on the first shot.

Here’s a little output from 11.2.0.2 (Exadata)

SYS@SANDBOX1> select count(*) from kso.little_skew a left outer join kso.little_skew b on a.pk_col = b.pk_col;

  COUNT(*)
----------
   1300496

Elapsed: 00:00:00.88
SYS@SANDBOX1> select /*+ NO_QUERY_TRANSFORMATION */ count(*) from kso.little_skew a left outer join kso.little_skew b on a.pk_col = b.pk_col;
select /*+ NO_QUERY_TRANSFORMATION */ count(*) from kso.little_skew a left outer join kso.little_skew b on a.pk_col = b.pk_col
                                                                                                                      *
ERROR at line 1:
ORA-01013: user requested cancel of current operation


Elapsed: 00:03:30.21
SYS@SANDBOX1> @fss
Enter value for sql_text: %count(*) from kso.little_skew a%
Enter value for sql_id: 

SQL_ID         CHILD      EXECS   AVG_ROWS     AVG_ETIME       AVG_CPU       AVG_PIO      AVG_LIO SQL_TEXT
------------- ------ ---------- ---------- ------------- ------------- ------------- ------------ ----------------------------------------
3u689tu6mfkdw      0          1          1           .87           .87           .00       11,336 select count(*) from kso.little_skew a l
6x3g2dtmwgvyu      0          1          0        203.64        203.59           .00   25,436,471 select /*+ NO_QUERY_TRANSFORMATION */ co


2 rows selected.
SYS@SANDBOX1> @dplan
Enter value for sql_id: 3u689tu6mfkdw
Enter value for child_no: 

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  3u689tu6mfkdw, child number 0
-------------------------------------
select count(*) from kso.little_skew a left outer join kso.little_skew
b on a.pk_col = b.pk_col

Plan hash value: 1386718486

---------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |       |       |       |  4984 (100)|          |
|   1 |  SORT AGGREGATE             |             |     1 |    12 |       |            |          |
|*  2 |   HASH JOIN OUTER           |             |  1210K|    13M|    18M|  4984   (1)| 00:01:00 |
|   3 |    TABLE ACCESS STORAGE FULL| LITTLE_SKEW |  1100K|  6445K|       |  1546   (1)| 00:00:19 |
|   4 |    TABLE ACCESS STORAGE FULL| LITTLE_SKEW |  1100K|  6445K|       |  1546   (1)| 00:00:19 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A"."PK_COL"="B"."PK_COL")


22 rows selected.
SYS@SANDBOX1> @dplan
Enter value for sql_id: 6x3g2dtmwgvyu
Enter value for child_no: 

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  6x3g2dtmwgvyu, child number 0
-------------------------------------
select /*+ NO_QUERY_TRANSFORMATION */ count(*) from kso.little_skew a
left outer join kso.little_skew b on a.pk_col = b.pk_col

Plan hash value: 2156590542

---------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |       |       |  1703M(100)|          |
|   1 |  SORT AGGREGATE               |             |     1 |       |            |          |
|   2 |   VIEW                        |             |    12G|       |  1703M  (1)|999:59:59 |
|   3 |    NESTED LOOPS OUTER         |             |    12G|    67G|  1703M  (1)|999:59:59 |
|   4 |     TABLE ACCESS STORAGE FULL | LITTLE_SKEW |  1100K|  6445K|  1546   (1)| 00:00:19 |
|   5 |     VIEW                      |             | 11001 |       |  1548   (1)| 00:00:19 |
|*  6 |      TABLE ACCESS STORAGE FULL| LITTLE_SKEW | 11001 | 66006 |  1548   (1)| 00:00:19 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - filter("A"."PK_COL"="B"."PK_COL")


24 rows selected.

I Wolfgang'ed the statement and it was indeed transformed, to this:

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT COUNT(*) "COUNT(*)" FROM  (SELECT "from$_subquery$_004"."PK_COL" "PK_COL"
FROM "KSO"."LITTLE_SKEW" "A", 
LATERAL( (SELECT "B"."PK_COL" "PK_COL" FROM "KSO"."LITTLE_SKEW" "B"
WHERE "A"."PK_COL"="B"."PK_COL"))(+) "from$_subquery$_004") "from$_subquery$_003"

So it doesn't look like NO_QUERY_TRANSFORMATION really means "NO" transformations.

6 01 2011
Charles Hooper

Kerry,

Thank you for stopping by, and for performing your test on Exadata. I am interested in seeing the “Final query after transformation” section of a 10053 trace for the unhinted version of the query. I am curious to know if the final query after transformation is written any differently than it would be on a non-Exadata implementation.

By the way, I am impressed with chapter 11 of the “Pro Oracle SQL” book – very well written.

6 01 2011
Kerry Osborne

The non-hinted version got transformed to standard Oracle join syntax (as expected):

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT COUNT(*) “COUNT(*)” FROM “KSO”.”LITTLE_SKEW” “A”,”KSO”.”LITTLE_SKEW” “B” WHERE “A”.”PK_COL”=”B”.”PK_COL”(+)

Basically the hinted version ran (although it never returned) but the trace file looked like the optimizer had pretty much lost it’s mind. There were many transformations that were apparently still evaluated even with the hint.

Thanks for the kind words on the book. I appreciate you taking the time to read it and comment. I haven’t actually had time to read it all the way through yet myself. ;) Hopefully you’ll like the last chapter as well (Plan Stability and Control) as it covers a topic that I am very interested in.

4 01 2011
Anil

Hi charles,
Your posts on Oracle are pretty good. But, my question is something else.

How are you able to embed your code into a block? I’d love to do the same. Could you please explain or give me the source?

Thanks,
Anil

4 01 2011
Charles Hooper

The specific WordPress theme that I selected “Freshy by Jude” includes in its cascading style sheet (CSS) the description for the code blocks. The Freshy theme was one of only a small number that supported horizontally scrolling code sections, which are definitely needed in a large number of my blog posts. I then paid WordPress for the option (roughly $50 USD per year) to modify the cascading style sheet so that I could fine tune the appearance of this blog (you might be able to do the same, and then borrow the code section formatting from the Freshy theme).

You can see why my blog was modified by looking at the comments in these blog articles:
http://hoopercharles.wordpress.com/2009/12/01/sql-plan_hash_value-changes-for-the-same-sql-statement/
http://hoopercharles.wordpress.com/2010/04/08/true-or-false-autotrace/

14 02 2013
Rajeev

Charles,

I tried to use NO_QUERY_TRANSFORMATION and Hint is ignored in CTAS statement. Standalone SELECT … works perfectly in 16 min’s however with CTAS the execution plan changes and takes 4hrs+ to finish. How can I keep the same execution plan with or without CTAS in 11.2.0.3 on RH Linux 5.5.

Thanks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Follow

Get every new post delivered to your Inbox.

Join 139 other followers

%d bloggers like this: