Find out the EXACT Transaction Size of an application using Oracle DB
617886Feb 3 2008 — edited Feb 4 2008In Oracle 10g, we calculated the average size of a transaction by doing 40 transactions in our application and it turned out our transaction size, say 3MB per txn. By calculating the average size, we did the following:
- enable row movement
- shrink the database
- measure by dbms_space.object_space_usage
- do transaction
- enable row movement
- shrink the database
- measure by dbms_space.object_space_usage
We thought this is too high and decided the tune the application. After turning CLOB into BLOB columns, drop a few indicies, rebuild indices and a few other tuning methods, we recalculated and come up with a result around 1MB per txn, using the shrink command. Without shrink, the average size of a transaction is still 3MB, the space taken up is due to unused datablocks. For example, maybe 10% or 30% are used in certain datablocks.
The problem is, we found a bug in the shrink command, thus cannot export the table successfully. So we cannot really promote or implement the shrink command in controlled environments. Is there any other ways to calculate the exact disk space used, or any other approach to verify that our tuning has saved up database space, other than using the shrink command provided by Oracle? Can we export and re-import the DB and measure again? Is there any other ways that how can we lower the variation?