NO_MERGE hint, example (2/2)

Part 1

Now, what I run is (I call this CQ - CA Query )

SELECT x.tablespace_name, sum(x.bytes) bytes_expired
FROM ( SELECT a.tablespace_name, bytes FROM dba_undo_extents a WHERE status = 'EXPIRED') x
GROUP BY x.tablespace_name;

SQL> set timing on 
SQL> set autot on exp stat
SQL> set lines 160


SELECT x.tablespace_name, sum(x.bytes) bytes_expired 
FROM ( SELECT a.tablespace_name, bytes FROM dba_undo_extents a WHERE status = 'EXPIRED') x 
GROUP BY x.tablespace_name;

TABLESPACE_NAME                BYTES_EXPIRED
------------------------------ -------------
UNDOTBS1                       1844445184 
UNDOTBS2                       4526112768 

 Elapsed: 00:03:52.20 

Execution Plan 
---------------------------------------------------------- 

Plan hash value: 2680378823 
-------------------------------------------------------------------------------- 
| Id   | Operation              | Name     | Rows | Bytes | Cost (%CPU)| Time  | 
-------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT        |          |  109 |  5886 | 77  (62)| 00:00:01 | 
|   1 |  HASH GROUP BY          |          |  109 |  5886 | 77  (62)| 00:00:01 | 
|   2 |   NESTED LOOPS          |          |  109 |  5886 | 77  (62)| 00:00:01 | 
|   3 |    VIEW                 | VW_GBC_5 |  109 |  5014 | 77  (62)| 00:00:01 | 
|   4 |     HASH GROUP BY       |          |  109 | 13625 | 77  (62)| 00:00:01 | 
|*  5 |      HASH JOIN          |          |  182 | 22750 | 76  (61)| 00:00:01 | 
|*  6 |       HASH JOIN         |          |  182 | 17108 | 48  (96)| 00:00:01 | 
|*  7 |        TABLE ACCESS FULL| UNDO$    |  141 |  2256 |  2   (0)| 00:00:01 | 
|*  8 |        FIXED TABLE FULL | X$KTFBUE | 1000 | 78000 | 45 (100)| 00:00:01 | 
|*  9 |       TABLE ACCESS FULL | TS$      |  154 |  4774 | 28   (0)| 00:00:01 | 
|* 10 |    INDEX UNIQUE SCAN    | I_FILE2  |    1 |     8 |  0   (0)| 00:00:01 | 
-------------------------------------------------------------------------------- 

 Predicate Information (identified by operation id): 
--------------------------------------------------- 
  5 - access("T"."TS#"="U"."TS#") 
  6 - access("E"."KTFBUESEGTSN"="U"."TS#" AND "E"."KTFBUESEGBNO"="U"."BLOCK#" 
             AND "E"."KTFBUESEGFNO"="U"."FILE#") 
  7 - filter(("U"."SPARE1"=1 OR "U"."SPARE1"=2) AND "U"."STATUS$"<>1) 
  8 - filter(DECODE("E"."KTFBUESTA",1,'ACTIVE',2,'EXPIRED',3,'UNEXPIRED','U NDEFINED')='EXPIRED') 
  9 - filter(BITAND("T"."FLAGS",16777216)=0) 
 10 - access("ITEM_1"="F"."TS#" AND "ITEM_2"="F"."RELFILE#") 

 Statistics 
---------------------------------------------------------- 
  84496 recursive calls 
  61604 db block gets 
 447085 consistent gets 
 140720 physical reads 
      0 redo size 
    704 bytes sent via SQL*Net to client 
    552 bytes received via SQL*Net from client 
      2 SQL*Net roundtrips to/from client 
     16 sorts (memory) 
      0 sorts (disk) 
      2 rows processed

If you compare "AQ" execution plan and "CQ" execution plan, you can see that they are the same. This mean that CBO made some manipulations during the choice of the best plan.

Pay attention. The behavior of the CBO is normal. The problem is that sometime the choice of the execution plan is not the best.

The manipulation that CBO used on "CQ" is to merge the external and internal select together. The effect is that "CQ" becomes "AQ".

So, what I do now, is to use the NO_MERGE hint, in order to force CBO to not make manipulation (statement DQ).


SELECT /*+ NO_MERGE(x) */ tablespace_name, sum(bytes) bytes_expired from 
( select a.tablespace_name, bytes FROM dba_undo_extents a WHERE status = 'EXPIRED') x 
group by tablespace_name;

TABLESPACE_NAME                BYTES_EXPIRED 
------------------------------ ------------- 
UNDOTBS1                       1400766464 
UNDOTBS2                       4706729984 

Elapsed: 00:00:00.08 


Execution Plan 
---------------------------------------------------------- 

Plan hash value: 1487528911 
--------------------------------------------------------------------------------------------- 
| Id | Operation                   | Name             | Rows| Bytes | Cost (%CPU)| Time     | 
--------------------------------------------------------------------------------------------- 
|  0 | SELECT STATEMENT            |                  |   1 |    30 |  1985  (99)| 00:00:01 | 
|  1 |  HASH GROUP BY              |                  |   1 |    30 |  1985  (99)| 00:00:01 | 
|  2 |   VIEW                      |                  |   1 |    30 |  1984  (99)| 00:00:01 | 
|  3 |    NESTED LOOPS             |                  |   1 |   133 |  1984  (99)| 00:00:01 | 
|  4 |     NESTED LOOPS            |                  | 182 | 22750 |  1984  (99)| 00:00:01 | 
|* 5 |      HASH JOIN              |                  | 141 |  6627 |    30   (0)| 00:00:01 | 
|* 6 |       TABLE ACCESS FULL     | UNDO$            | 141 |  2256 |     2   (0)| 00:00:01 | 
|* 7 |       TABLE ACCESS FULL     | TS$              | 154 |  4774 |    28   (0)| 00:00:01 | 
|* 8 |      FIXED TABLE FIXED INDEX| X$KTFBUE (ind:1) |   1 |    78 |    14 (100)| 00:00:01 | 
|* 9 |     INDEX UNIQUE SCAN       | I_FILE2          |   1 |     8 |     0   (0)| 00:00:01 | 
--------------------------------------------------------------------------------------------- 

 Predicate Information (identified by operation id): 
--------------------------------------------------- 
 5 - access("T"."TS#"="U"."TS#") 
 6 - filter(("U"."SPARE1"=1 OR "U"."SPARE1"=2) AND "U"."STATUS$"<>1) 
 7 - filter(BITAND("T"."FLAGS",16777216)=0) 
 8 - filter("E"."KTFBUESEGBNO"="U"."BLOCK#" 
            AND "E"."KTFBUESEGFNO"="U"."FILE#" AND "E"."KTFBUESEGTSN"="U"."TS#" 
            AND DECODE("E"."KTFBUESTA",1,'ACTIVE',2,'EXPIRED', 3,'UNEXPIRED','UNDEFINED')='EXPIRED') 
 9 - access("U"."TS#"="F"."TS#" AND "E"."KTFBUEFNO"="F"."RELFILE#") 

 Note ----- 
 - this is an adaptive plan 

 Statistics 
---------------------------------------------------------- 
 141 recursive calls 
   0 db block gets 
 876 consistent gets 
  25 physical reads 
   0 redo size 
 704 bytes sent via SQL*Net to client 
 552 bytes received via SQL*Net from client 
   2 SQL*Net roundtrips to/from client 
   0 sorts (memory) 
   0 sorts (disk) 
   2 rows processed


Now, if you compare the BQ execution plan with DQ execution plan you can see that there are just 2 more rows:

DQ execution plan, has

|  1 |  HASH GROUP BY
|  2 |   VIEW 

and this is exactly what I want:

1. Run the BQ, so it takes just few seconds  => rows 3-9 into DQ execution plan
2. Run the aggregation ong the BQ                  => rows  1-2 into  DQ execution plan

Note:
I have modified the output of explain plan because the blog visualization.

Part 1