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 END OF STMT
…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# from 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”.