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:
- Run the BQ, so it takes just few seconds => rows 3-9 into DQ execution plan
- 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
