Recycling saves the planet but maybe not your database

The recyclebin is a very cool feature in the Oracle Database, and a great “Get of our jail free” card to have in your DBA toolkit. I always recommend that customers should go with the defaults and always have it enabled to have that last resort option to bring a segment back to life with FLASHBACK in the event of an accidental drop.

As most of Oracle practitioners know, bombarding your database with DDL is a sure fire way of limiting scalability. This is one of the reasons that we invented global temporary tables and later private temporary tables – to lower the need and cost for performing create/drop DDL repeatedly on the database. However, some applications just cannot help themselves and just smash away at the database, creating and dropping tables until the cows come home.

The problem with that is when it comes to cleaning up your recyclebin, which now might be filled with potentially millions of entries. When recently helping a customer to sort out their recyclebin mess, I observed this when running the PURGE command:

SQL> purge dba_recyclebin;

[waiting]

And we waited…and waited. When I finally lost patience and checked V$SESSION_LONGOPS – the predicted completion time was around 300 hours! It turned out that they had nearly 10 million objects in their recyclebin! So I threw a trace on the purge process, and intermingled with the various DROP commands that get generated to clean up the dictionary, was this slow running SQL:

delete from RecycleBin$                     
where bo=:1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       51      0.00       0.00          0          0          0           0
Execute     51      0.00       0.00         11        714        377          53
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      102      0.00       0.00         11        714        377          53

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)
Number of plan statistics captured: 3

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  DELETE  RECYCLEBIN$ (cr=14 pr=4 pw=0 time=328 us starts=1)
         1          2          3   TABLE ACCESS FULL RECYCLEBIN$ (cr=14 pr=4 pw=0 time=272 us starts=1 cost=5 size=52 card=1)

Full disclosure: This is actually from my own laptop because of customer confidentiality, so the numbers are much smaller, but notice the execution plan. A full scan on the RECYCLEBIN$ table and this is run once for each object that get cleaned up from the recyclebin. For the purposes of illustration, I created and dropped 51 objects in my own database, and thus you can see the 51 executions.

By default, there is no index on the BO column on the RECYCLEBIN$ table. Not having an index is by design, because for the vast majority of customers, a recyclebin cleanup is expected to be in the 10s or 100s of rows. As I said on my podcast episode on indexes, we should always adopt the Goldilocks principle for indexing on our databases:

Not too many, not too few… use just the right amount

However, when your recyclebin has 10 million rows, then a little indexing assistance is probably required 😆.

Even though this appears to be messing around with the internal dictionary tables, this strategy is sanctioned by Support, as per MOS note note 2284986.1.

• create index RecycleBin$_bo on RecycleBin$(bo);
• exec dbms_stats.gather_table_stats(‘SYS’,’RecycleBin$’,cascade=>TRUE);
• re-run purge

In the case of this customer, the 300 hours estimate came down to 30mins. But don’t forget that this is a triage step to get the recyclebin back down a reasonable size. Once you’ve done this, now is the time to look at whether your database needs the recyclebin feature, or whether you might disable it for certain schemas/sessions, or just perhaps schedule a more regular purge to avoid things getting out of control.