You can from the timings, that you had no dramas scanning the 70M rows - that was nice and snappy. It was the aggregation that took all the time.
Where you are getting slammed is the calculation of the median.
Typically, a group by can be very efficient via hashing, eg
SQL> create table t tablespace largets nologging
2 as select
3 OWNER
4 ,OBJECT_NAME
5 ,SUBOBJECT_NAME
6 ,OBJECT_ID
7 ,DATA_OBJECT_ID
8 ,OBJECT_TYPE
9 ,cast(CREATED as timestamp) created
10 ,cast(LAST_DDL_TIME as timestamp) LAST_DDL_TIME
11 ,TIMESTAMP
12 ,STATUS
13 ,TEMPORARY
14 ,GENERATED
15 ,SECONDARY
16 ,NAMESPACE
17 ,EDITION_NAME
18 ,SHARING
19 ,EDITIONABLE
20 ,ORACLE_MAINTAINED
21 from dba_objects d,
22 ( select 1 from dual connect by level <= 200 );
Table created.
SQL> select
2 owner,
3 max(created - last_ddl_time) as MIN_LAG,
4 min(created - last_ddl_time) as MAX_LAG,
5 avg(extract(second from created - last_ddl_time)+
6 extract(minute from created - last_ddl_time) * 60 +
7 extract(hour from created - last_ddl_time) * 60 * 60 +
8 extract(day from created - last_ddl_time) * 60 * 60 * 24) AVG_LAG
9 from t
10 group by owner;
37 rows selected.
Elapsed: 00:00:09.04
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 37 | 1036 | 81466 (3)| 00:00:04 |
| 1 | HASH GROUP BY | | 37 | 1036 | 81466 (3)| 00:00:04 |
| 2 | TABLE ACCESS FULL| T | 16M| 439M| 80134 (1)| 00:00:04 |
---------------------------------------------------------------------------
but the moment I add the MEDIAN into the mix, then we have to fall back to sorting the data
SQL> select
2 owner,
3 max(created - last_ddl_time) as MIN_LAG,
4 min(created - last_ddl_time) as MAX_LAG,
5 avg(extract(second from created - last_ddl_time)+
6 extract(minute from created - last_ddl_time) * 60 +
7 extract(hour from created - last_ddl_time) * 60 * 60 +
8 extract(day from created - last_ddl_time) * 60 * 60 * 24) AVG_LAG,
9 median(extract(second from created - last_ddl_time)+
10 extract(minute from created - last_ddl_time) * 60 +
11 extract(hour from created - last_ddl_time) * 60 * 60 +
12 extract(day from created - last_ddl_time) * 60 * 60 * 24) MEDIAN_LAG
13 from t
14 group by owner;
37 rows selected.
Elapsed: 00:00:56.81
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 37 | 1036 | 81466 (3)| 00:00:04 |
| 1 | SORT GROUP BY | | 37 | 1036 | 81466 (3)| 00:00:04 |
| 2 | TABLE ACCESS FULL| T | 16M| 439M| 80134 (1)| 00:00:04 |
---------------------------------------------------------------------------
Somewhat counter-intuitively I got better results by ensuring I had a nice large PGA allocation, and then doing as much work with group-by-hash, and then just doing the median with the sort
SQL> with t1 as
2 ( select /*+ materialize */
3 owner,
4 max(created - last_ddl_time) as MIN_LAG,
5 min(created - last_ddl_time) as MAX_LAG,
6 avg(extract(second from created - last_ddl_time)+
7 extract(minute from created - last_ddl_time) * 60 +
8 extract(hour from created - last_ddl_time) * 60 * 60 +
9 extract(day from created - last_ddl_time) * 60 * 60 * 24) AVG_LAG
10 from t
11 group by owner
12 ),
13 t2 as
14 ( select /*+ materialize */
15 owner,
16 median(created - last_ddl_time ) MEDIAN_LAG
17 from t
18 group by owner
19 )
20 select *
21 from t1, t2
22 where t1.owner = t2.owner;
37 rows selected.
Elapsed: 00:00:19.44
Statistics
----------------------------------------------------------
30 recursive calls
16 db block gets
586443 consistent gets
586402 physical reads
1248 redo size
2907 bytes sent via SQL*Net to client
969 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
37 rows processed
Your mileage may vary, but start with a good sized PGA to improve the sort, and see how you go