Queries against dba_free_space are slow - is there any way to speed them up?

The Oracle (tm) Users' Co-Operative FAQ

Queries against dba_free_space are slow - is there any way to speed them up ?

Queries against DBA_FREE_SPACE can take 20secs to few mins. And running them to monitor free space should not add performance over-head. The SQL provided below is a solution I used (instead of having to create a new view as suggested in “Queries against dba_free_space with locally managed tablespaces are slow - is there any way to speed them up?”

Original Code :

SELECT

TABLESPACE_NAME, SUM(BYTES) SUMB,

MAX(BYTES) LARGEST,COUNT(*) CHUNKS

FROM DBA_FREE_SPACE A

GROUP BY TABLESPACE_NAME

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.01       0.00          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        2     23.34      24.58          0    6342177         25           7

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total        4     23.35      24.58          0    6342177         25           7

Modification:

SELECT /*+ use_hash (A.ts A.fi) */

TABLESPACE_NAME, SUM(BYTES) SUMB,

MAX(BYTES) LARGEST,COUNT(*) CHUNKS

FROM DBA_FREE_SPACE A

GROUP BY TABLESPACE_NAME

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.00       0.02          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        2      0.46       0.47          1       1114         40           7

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total        4      0.46       0.49          1       1114         40           7

Further reading: