The Oracle (tm) Users' Co-Operative FAQ

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


Author's name: Nitin Pai

Author's Email: nitinkpai@yahoo.com

Date written: 16th June 2005

Oracle version(s): 8i and 9i

Response to the FAQ: Queries against dba_free_space with locally managed tablespaces are slow - is there any way to speed them up?

 

I noticed that we can simply use a HINT instead of having to create a separate view to improve the performance of the SQL’s against DBA_FREE_SPACE.

Back to index of questions


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:


Back to top

Back to index of questions