Recycling or Wasting?

Pre-Ramble

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.

Recent Case

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.

This is the logical block accesses they’ve done so far:

SID BLOCK_GETS CONSISTENT_GETS PHYSICAL_READS BLOCK_CHANGES CONSISTENT_CHANGES

———- ———- ————— ————– ————- ——————

43        142        73903895 14224             0                  2

68        142        75150676 14573             0                  2

189        143        23221325 9066             1                  2

267        142        22997804 9059             0                  2

This is the SQL statement they are all running:

PARSING PARSING

SQL_TEXT

—————————————————————-

select  total_mb as met212_1, total_mb-free_mb as met212_2

from(select a.m + b.m + decode(c.m,null,0,c.m) total_mb, d.m fr

ee_mb    from(select sum(bytes)/1048576 m from v$datafile) a,

(select sum(bytes)/1048576 m from v$log) b,     (select sum(by

tes)/1048576 m from v$tempfile) c,     (select sum(bytes)/104867

6 m from dba_free_space) d)

CHLD      SHARE      PERST        RUN LOADED KEPT OPEN            PARSE                                            USER  SCHEMA

NUM        MEM        MEM        MEM   VERS VERS VERS LOADS INVL CALLS FIRST_LOAD_TIME     LAST_LOAD_TIME           ID      ID

—- ———- ———- ———- —— —- —- —– —- —– ——————- ——————- ——- ——-

0     153076      68608      67336      1    0    1     1    0     8 2009-07-30/11:51:10 2009-07-30/11:51:10      66      66

CHLD                 DISK     BUFFER                             ROWS

NUM     EXECS      READS       GETS      SORTS    FETCHES  PROCESSED      COST

—- ——— ———- ———- ———- ———- ———- ———

0         8 120258  549877971 64          4          4         1

CHLD    CPU_TIME     ELAPSED

NUM         SEC         SEC MODULE                                             ACTION

—- ———– ———– ————————————————– ————————————————–

0    18916.85 20740.22 SQL*Plus