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 (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) taks 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

Nessun commento: