Nigel Noble's Oracle Blog

23/07/2010

10gR2 – 11gR2, ASSM Space Management “Bug”

Filed under: 10gR2, 11gR1, 11gR2, assm — Nigel Noble @ 10:59 am

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).

The problem

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

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)

Solution:

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”)

About these ads

5 Comments »

  1. [...] growth in size and not using of free space with ASSM Nigel Noble-10gR2 – 11gR2, ASSM Space Management “Bug” Leave a [...]

    Pingback by Blogroll Report 16/07/2010 – 23/07/2010 « Coskan’s Approach to Oracle — 23/07/2010 @ 4:18 pm

  2. [...] I see that Nigel Noble has written an article about a bug in the way that ASSM checks free space maps that can result in significant space wastage in particular special cases. Comments [...]

    Pingback by Fragmentation 3 « Oracle Scratchpad — 24/07/2010 @ 10:23 am

  3. Hi Nigel,

    That is a nice issue with ASSM. Well, I obviously don’t mean “nice, likeable”, I mean a nice description of it.

    Obviously, swapping to using a Manually Managed tablespace is a good solution for you. Another would be to possibly have the daemon commit suicide after x hours. I had a similar issue with daemons on a 10.1 database where we had memory leak issues associated with all of the ten daemons running. Our solution was to make the daemons terminate themselves after an hour or so and another “master daemon” starting up new processing daemons if they dropped below 10. We got rid of {most of} the memory leak issue and also gained a side benefit, a way to control the number of processing daemons on the live system (we just altered the number of active sessions the master daemon checked for and initiated.

    >this “bug” (Still not sure if I should call it a “bug” or “feature”).
    I’d call it a feature; but then I still think the issue you highlighted in your previous post on the KEEP pool is more an unintentional side-effect and not a bug:-)

    Comment by mwidlake — 26/07/2010 @ 11:19 pm

  4. I think you should open enhancement request on support.oracle.com for a new parameter – something like _assm_bitmap_session_cache = false or true

    Comment by Robert — 15/09/2010 @ 10:18 am

    • Yes, I should try and chase Oracle for a fix. It was one of those cases where once we had a work around we just used it and did not follow up with support.

      Comment by Nigel Noble — 15/09/2010 @ 1:38 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Silver is the New Black Theme Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 27 other followers

%d bloggers like this: