ASSM (Automatic Segment Space Management) has an issue when trying to re-use “deleted” space created by another session. There is a very specific set of circumstances which must occur for this issue to show itself, but will result in tables (and I suspect indexes) growing significantly larger than they need to be. I am aware that the problem exists in versions 10.2.0.3 through to the current 11gR2 inclusive although I don’t know which Oracle release first introduced the problem.
The conditions required to cause the issue
My site has a number of daemon style jobs running permanently on the database loading data into a message table. The daemon is managed by dbms_job and is re-started when the database is first started and can run for many days, weeks (or in some cases months) before the job is ever stopped. We only need to keep the messages for a short time, so we have another daemon job whose role is to delete the messages from the table as soon as the expiry time is reached. In one example we only need to retain the data for a few minutes (after which time we no longer need it) and we also wanted to keep the table as small as possible so it remained cached in the buffer cache (helped by a KEEP pool). When we developed the code, we expected the message table to remain at a fairly constant size of 50 – 100MB in size. What we found was the table continued to grow at a consistent rate to many gigabytes in size until we stopped the test. If we copied the table (eg using “create table <new table> as select * from <current table>”) the new table would again be 50MB so most of the space in the table was empty “deleted” space. The INSERT statements were never re-using the space made free by the delete statement (run in another session).
What we found was the INSERT statement could not reuse the space which was freed by the other session (which was deleting space). We played around with a number of different configurations and what we found was when we created our table in a Manually Managed Tablespace, the application worked perfectly and the table remained at a constant <100MB size for many hours. We spoke to a contact within Oracle and it seems that there was some optimization added to INSERT statements to reduce the time lost in re-evaluating the ASSM free space map. If the session remains connected and does not reparse the INSERT statement, Oracle will continue to use a static (stale) space map.
Not a problem in most applications
In most cases, this “bug” (Still not sure if I should call it a “bug” or “feature”) does not cause any problem because when lots of sessions are running, connecting, disconnecting and re-parsing over the life of your application they would be able to pick up a fresh “space map” and be able to re-use any “deleted” available blocks. It may account for a small overhead in the size of the tables and indexes but suspect you would never really notice it. Worth remembering that this is caused by optimization to make INSERT statements faster.
My site has seen a couple of variations of the problem. Firstly the table we expected to be very small (example above), but the other was a table we expected to be large because the table held 7 days data and we had a nightly job which deleted data older than 7 days but, it seems we were having the same issue. We expected the table to be large so never noticed it was twice the size it should have been. As with the other example, the larger table was loaded using a single dedicated job which stayed running for weeks at a time. Only when the application was restarted, would the spaced freed from previous delete operations now be available for reuse. The issue causes the High Water Mark of the table (suspect indexes too) to grow to the largest amount of time which the INSERT daemon runs without being restarted. Once the daemon is restart, it would could continue to re-use freed blocks for many weeks without the table/indexes growing at all until such a time that all the remaining blocks are filled and then the object would start to grow again (ignoring newly created free blocks)
The work-around my site uses is when we know we are loading tables by deamons which will constantly run and that same table will be deleted at a fixed rate (by other session(s)), we just place the table and related indexes in MANUALLY MANAGED tablespaces (“SEGMENT SPACE MANAGEMENT MANUAL”)