Slow inserts due to…the recycle bin??

We had an ODI job that was dropping and creating 296 tables (and indexes) every two minutes. The drops were not issued with a “purge” directive appended, so we created, dropped, and pushed to the recycle bin almost 8 million objects in the ODI_EXT7 schema since late April. When we traced, when the user was loading the background directive to relieve space pressure in the tablespace was undertaken. These purges took about three seconds for each table, so the entire job bogged down. A way to rewrite the job is being designed that will not require a drop/create every two minutes. Adding a datafile seems like a Band-Aid on a known design issue.

As a side point of interest In the trace, you can see the drop issued by SYS and not ODI_EXT7, as it is recursive…

PARSING IN CURSOR #140581294926432 len=60 dep=1 uid=0 oct=12 lid=0 tim=1526674010325422 hv=761558858 ad='7fdba21038b0' sqlid='49h6qw8qq8xua'
drop table "ODI_EXT7"."BIN$a7WQzbRRTpngUxccG6xouQ==$0" purge

…with the associated recursive SQL below, which represents about 75% of total run time in the job…

select obj#, type#, flags, related, bo, purgeobj, con#
RecycleBin$    where ts#=:1 and to_number(bitand(flags, 16)) = 16    order
  by dropscn

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse      186      0.00       0.00          0          0          0           0
Execute    186      0.02       0.03          0          0          0           0
Fetch      380   1373.52    1413.54        234   30810175          0         380
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      752   1373.55    1413.59        234   30810175          0         380

As ODI_EXT7, we currently have a job running that is purging individual objects, sorted by space in descending order so we can get ahead of the issue and allow testing (with the new design) to continue. The objects that occupy space in the user recycle bin should be purged by Monday; however, as-is, a complete purge of all ODI_EXT7 dropped objects will take over two weeks using ten threads. While ten threads doesn’t result in linear improvement, it does result in better performance than a single threaded “purge user_recyclebin”.