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

2 commenti:

Kaley Crum ha detto...

Hi Andrea! Nice post! I'd actually encountered a similar issue--an aggregation query against DBA_FREE_SPACE was running very slowly for me. The issue was the X$KTFBUE table... Any time the table was full-scanned, the query ran slowly, and when the index was used, it ran sub-second. (Looks like you might have the same thing going on here) Apparently this is a pretty common issue--this table doesn't have true statistics on it and Oracle defaults the estimated number of rows to 10000. I ended up solving my issue using the scale_rows part of the OPT_ESTIMATE hint to make Oracle think there were about 700x the number of rows that it thought there were... That was enough to get Oracle to start using the index again.

Awesome blog!

Andrea Salzano ha detto...

Hello Kaley.

Thanks for reading my blog.

I’m sure you already know, but just for info, you could gather statistics also on data dictionary and x$.

Following link could give you some extra info. It also have reference to the MOS DocID

https://juliandontcheff.wordpress.com/2011/06/04/on-gathering-dictionary-statistics-do-i-analyze-the-sys-schema/