on oracle 10g if the recycle bin has lof of objects and you dont have good dictionary statistics then your select queries on dba_free_space can take lot of time. below article explains the reason and how to handle it.
Hello Thomas,
I found the problem of querying the tablespace info. The problem was in the recycle bin
size of my schema. I ran your script
-- free.sql
-- This SQL Plus script lists freespace by tablespace
in "select" pieces and found out that one part of one view sys.dba_free_space
select /*+ ordered use_nl(u) use_nl(fi) */
ts.name, fi.file#, u.ktfbuebno,
u.ktfbueblks * ts.blocksize, u.ktfbueblks, u.ktfbuefno
from sys.recyclebin$ rb, sys.ts$ ts, sys.x$ktfbue u, sys.file$ fi
where ts.ts# = u.ktfbuesegtsn
and u.ktfbuesegtsn = fi.ts#
and u.ktfbuesegfno = fi.relfile#
and u.ktfbuesegtsn = rb.ts#
and u.ktfbuesegfno = rb.file#
and u.ktfbuesegbno = rb.block#
and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0
which has the join query on the recycle bin consumes 99% of the time. I purged my recycle
bin and ran
dbms_stats form my schema
and
dbms_stats.gather_dictionary_stats
After this I was able to query on the tablespace size in no time. Just as it has to be.
This recycle bin issue on 10g is fairly new to me. However now I know what the problem
was.
You may want to publish this.
