Skip to content

oracle 10g recycle bin can impact tablespace size select queries





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.
VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

Post a Comment

You must be logged in to post a comment.