Recycle Bin and Performance

10G introduced a new feature FLASHBACK DROP. FLASHBACK DROP enables a user to quickly undo the previously disastrous mistake of dropping a table. Well, disastrous is a strong word but depending on your environment it most likely isn't a fun experience.

As a side and somewhat unrelated note, many years ago at a previous company it was noticed that data was missing from one table. I believe at the time we were either on 8i or 7.3.4. They weren't sure exactly when the problem started so we had to perform many point in time recoveries. By the end of the exercise I believe I had completed over 30 recoveries!

I won't go into detail on how to use flashback drop. Between Oracle's documentation and numerous blog posts this topic has been covered quite a bit. In a nutshell, when you drop an object (tables, indexes, constraints, etc) the space occupied by the object isn't released immediately. This is the behaviour in 8i/9i as well. However, in 10g, the object is renamed and placed in a recycle bin. The recycle bin is actually a data dictionary table which contains information on dropped objects and is required for FLASHBACK DROP to work. This feature is controlled by an initialization parameter and it is not included in the list of mandatory 11i initialization parameters (Note:216205.1) but by default it is enabled.

A quick example:

SQL> show parameter recyclebin

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
recyclebin                           string      on

SQL> create table t1 (name varchar2(2));

Table created.

SQL> insert into t1 values ('hi');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t1;

NA
--
hi

SQL> drop table t1;

Table dropped.

SQL> show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
T1               BIN$RxJptgohQevgRAADugOWhQ==$0 TABLE        2008-02-26:10:35:00

SQL> select * from t1;
select * from t1
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> flashback table t1 to before drop;

Flashback complete.

SQL> select * from t1;

NA
--
hi

Since we upgraded to 10g I've noticed that the tablespace page in Database Control, which shows you the amount free/used space, has been taking longer and longer to load. As well, the following query has been flagged as consuming more resources than normal:

insert into mgmt_db_size_gtt select tablespace_name,NVL(sum(bytes)/1048576, 0) sz from sys.dba_free_space group by tablespace_name 

The reason is because the dba_free_space view now references the sys.recyclebin$ as well. So as the recyclebin table grows, some queries such as the one above and the underlying query(s) of the tablespace page in DB Control get slower over time. (Anything that touches the dba_free_space view.) The solution is to purge the recycle bin. You can purge individual objects, objects owned by a particular user or system wide. Example:

SQL> purge recyclebin;
 
Recyclebin purged.

Before I purged the recycle bin system wide I thought it would be interesting to see who the culprit was.

 1   select owner, count(*)
  2   from dba_recyclebin
  3   group by owner
  4* order by 2 desc
SQL> /

OWNER                            COUNT(*)
------------------------------ ----------
GL                                  86089
APPS                                  698
BNE                                    50
[snip]

GL by far has the most object consisting of tables and indexs:

  1  select type, count(*)
2  from dba_recyclebin
3  where owner = 'GL'
4  group by type
5* order by 2 desc
SQL> /

TYPE                        COUNT(*)
------------------------- ----------
INDEX                          54966
TABLE                          31123

Diving deeper I found that this is caused by Mass Allocation. I'm debating whether or not to disable the recycle bin feature or at the very least schedule a script to purge objects older than X days. In our test environment, a system wide purge of 89k objects took 4hrs. Anyways, if someone accidentally drops a table, then we are going to know about it right away! The only apps related issue I have found is a conflict with a patch where the AD_DEFERRED_JOBS table couldn't be created even tho it didn't currently exist.

Relevent Metalink notes:

Note:390413.1 - ORA-38301 During Installation Of Patch 4709948
Note:271169.1 - Queries on DBA_FREE_SPACE are Slow
Note:414256.1 - Poor Performance For Tablespace Page Display In Grid Control Console
Note:265253.1 - 10g Recyclebin Features And How To Disable it( _recyclebin )