So you’ve just completed a database cleanup of old long-lived process instances using the process purge utility and you expect that you’re going to get an immediate performance increase. Well, why not… you just removed 90% of the rows in some frequently accessed tables. Something to be aware of is that in most databases, when you remove a row of data via a sql delete command, the space used by that row is not returned to some magical disk pool, it instead remains a part of that table in something called its freelist space. The idea is allocation for future inserts is made more efficient since the diskspace is readily available. This is useful in ebb and flow data stores, however when you have disparity between the min and max sizes, having all that extra unused “freelist” space allocated to a table might not be such a great idea.
The operations for this reclaim are very DBA oriented, each database has a number of ways that you can reclaim disk space.
So, other than the obvious reason of getting loads of disk space back on the database server, why else would you bother doing this? One word, fragmentation… Whenever I hear that word I automatically associate it to the word “slow”. Generally slow performance on the database side (even a 10% decrease in CRUD operations) has a dramatic performance impact elsewhere. Since ADEP Document Services is very dependent on database content, ensuring your database is in tip-top shape is a necessity and highly recommended.
Even if you haven’t done a massive purge, it’s always a good idea to take a look at how much fragmentation or unused disk space is allocated to a tablespace.
Some useful links:
Oracle Reclaim: http://eoracle.blogspot.com/2010/09/how-to-reclaim-disk-space-from-database.html