Translation

The oldest posts, are written in Italian. If you are interested and you want read the post in English, please use Google Translator. You can find it on the right side. If the translation is wrong, please email me: I'll try to translate for you.

giovedì, gennaio 05, 2017

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

NO_MERGE hint, example (1/2)

Part 2

This query (that I call AQ - A Query (A stay for "the first one") ) takes 3 minutes and 44 seconds.

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

Here is the explain plan and statistics

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

SQL> SELECT a.tablespace_name, SUM(a.bytes) bytes_expired 
   > FROM dba_undo_extents a WHERE status = 'EXPIRED' 
   > GROUP BY tablespace_name; 

TABLESPACE_NAME                BYTES_EXPIRED 
------------------------------ ------------- 
UNDOTBS1                       1677983744 
UNDOTBS2                       5107023872 

Elapsed: 00:03:44.07 

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', 'UNDEFINED')='EXPIRED') 
  9 - filter(BITAND("T"."FLAGS",16777216)=0)
 10 - access("ITEM_1"="F"."TS#" AND "ITEM_2"="F"."RELFILE#") 

Statistics 
---------------------------------------------------------- 
  84080 recursive calls 
  61670 db block gets 
 438303 consistent gets 
 143544 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 
      1 sorts (memory) 
      0 sorts (disk) 
      2 rows processed


But if I run the statement without a aggregation (that I call BQ - B Query (B stay for "the second one") ), it is immediate

SQL> select a.tablespace_name, bytes 
   > FROM dba_undo_extents a WHERE status = 'EXPIRED'; 

TABLESPACE_NAME                 BYTES
 ------------------------------ ---------- 
UNDOTBS1                        65536 
UNDOTBS1                        1048576 
UNDOTBS1                        8388608 
[...] 

TABLESPACE_NAME BYTES 
------------------------------ ---------- 
UNDOTBS2                       65536 
UNDOTBS2                       1048576 
UNDOTBS2                       8388608 
UNDOTBS2                       16777216 
UNDOTBS2                       25165824 
UNDOTBS2                       33554432 
UNDOTBS2                       67108864 
[...] 

 1448 rows selected. 

 Elapsed: 00:00:00.19 

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

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

 Predicate Information (identified by operation id): 
--------------------------------------------------- 
 3 - access("T"."TS#"="U"."TS#") 4 - filter(("U"."SPARE1"=1 OR "U"."SPARE1"=2) AND "U"."STATUS$"<>1) 
 5 - filter(BITAND("T"."FLAGS",16777216)=0) 
 6 - 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') 
 7 - access("U"."TS#"="F"."TS#" AND "E"."KTFBUEFNO"="F"."RELFILE#") 

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

 Statistics 
---------------------------------------------------------- 
   164 recursive calls 
     0 db block gets 
   995 consistent gets 
    71 physical reads 
     0 redo size 
 27169 bytes sent via SQL*Net to client 
  1608 bytes received via SQL*Net from client 
    98 SQL*Net roundtrips to/from client 
     1 sorts (memory) 
     0 sorts (disk) 
  1448 rows processed


So the simple statement (BQ) take 1 seconds, while the aggregation (AQ) taks more than 3 minutes.

My idea is:
  1. Run the BQ, so it takes just few seconds
  2. Run the aggregation on the BQ


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

Part 2