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:
- Run the BQ, so it takes just few seconds
- Run the aggregation on the BQ
Note:
I have modified the output of explain plan because the blog visualization.
Part 2
Nessun commento:
Posta un commento