My local council collects plastic, glass, paper, etc separately from the rubbish so that it can be put to good use for another purpose rather than these resources being wasted in a landfill somewhere.
Oracle databases (from 10g) collect dropped segments in a recycle bin, until the resource (tablespace capacity) needs to be used for something else.
My local council is having trouble finding a use (or buyer) for the collected materials, and so those materials are stockpiled and over time take up more and more space. Some databases are in a similar situation.
A monitoring tool had been activated for an Oracle 10g database for the first time. The monitoring tool’s database session was running for a long time and using a lot of CPU. In fact, the next check was starting before the previous one had finished.
I could see that these sessions were doing a lot of consistent reads from memory (not much physical I/O) so high CPU usage was understandable because a lot of processing was occurring.
The SQL was referencing v$datafile, v$log, v$tempfile and dba_free_space. It was a common enough piece of code that returned immediately on most 10g databases. What was different about this database? The first thing I thought to check was the number and size of the free and used chunks of space in the database. When ordering the segments by total size or number of extents, I noticed a lot of segments beginning with “BIN$%”. This made me think that the recycle bin had something to do with this issue.
SQL> select sum(bytes)/1048576 from dba_segments where segment_name like 'BIN$%'; SUM(BYTES)/1048576 ------------------ 147092.125 1 row selected. SQL> select min(droptime) from dba_recyclebin; MIN(DROPTIME) ------------------- 2008-11-13:08:24:45 SQL> select count(1) from dba_recyclebin; COUNT(1) ---------- 9525
There are 9,525 items in the recycle bin that take up 150GB out of a total database size of 250GB! Just think of the wasted space on disk, tape, etc.
Further reports on DBA_RECYCLEBIN showed that the application regularly drops some segments (partitions mostly). I surmised at this point that the dropped objects are never purged, and that the datafiles are manually extended whenever the recycle bin contents fill up the tablespace enough to make the used capacity reach a certain threshold. Note that if auto-extension had been left to handle growth, then the dropped objects would be purged as needed before the datafile was extended. It was human intervention that was circumventing Oracle’s recycling process.
I suggested that this database should have the recycle bin purged and disabled so that the DBAs could continue to manage the capacity with their standard procedures.
A Little More for Anyone Still Interested
A “My Oracle Support” search showed that there were some bugs and problems in previous versions /patch sets regarding performance of DBA_FREE_SPACE when objects had been dropped. Although there were no exact bug/note matches for this database’s version, I suspect that the underlying SQL was still not being executed efficiently. Gathering statistics on the X$ views (via DBMS_STATS.GATHER_FIXED_OBJECTS_STATS) didn’t help, because one of the referenced v$ views (V$DATAFILES I think it was) had a RULE hint in it. However, if the same SQL was forced to use CBO then it ran much quicker.