We had an ODI job that was dropping and creating 296 tables (and indexes) every two minutes. The drops were not issued with a \u201cpurge\u201d 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.<\/p>\n
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\u2026<\/p>\n
\r\n=====================\r\nPARSING IN CURSOR #140581294926432 len=60 dep=1 uid=0 oct=12 lid=0 tim=1526674010325422 hv=761558858 ad='7fdba21038b0' sqlid='49h6qw8qq8xua'\r\ndrop table \"ODI_EXT7\".\"BIN$a7WQzbRRTpngUxccG6xouQ==$0\" purge\r\nEND OF STMT\r\n<\/pre>\n\u2026with the associated recursive SQL below, which represents about 75% of total run time in the job\u2026<\/p>\n
\r\nselect obj#, type#, flags, related, bo, purgeobj, con#\r\nfrom\r\nRecycleBin$ where ts#=:1 and to_number(bitand(flags, 16)) = 16 order\r\n by dropscn\r\n\r\n\r\ncall count cpu elapsed disk query current rows\r\n------- ------ -------- ---------- ---------- ---------- ---------- ----------\r\nParse 186 0.00 0.00 0 0 0 0\r\nExecute 186 0.02 0.03 0 0 0 0\r\nFetch 380 1373.52 1413.54 234 30810175 0 380\r\n------- ------ -------- ---------- ---------- ---------- ---------- ----------\r\ntotal 752 1373.55 1413.59 234 30810175 0 380\r\n<\/pre>\nAs 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\u2019t result in linear improvement, it does result in better performance than a single threaded \u201cpurge user_recyclebin\u201d.<\/p>\n","protected":false},"excerpt":{"rendered":"
We had an ODI job that was dropping and creating 296 tables (and indexes) every two minutes. The drops were not issued with a \u201cpurge\u201d directive appended, so we created, dropped, and pushed to the recycle bin almost 8 million…<\/p>\n