Oracle Scratchpad

July 30, 2013


Filed under: ASSM,Infrastructure,Oracle — Jonathan Lewis @ 6:53 am BST Jul 30,2013

A couple of thoughts.

The intent of ASSM is to minimise contention when multiple small transactions are busy inserting data concurrently into the same table. As a consequence, you may be able to find a number of odd behaviour patterns if you do experiments with a single session running one transaction at a time; or when executing a single large transaction, or when experimenting with small tables.

As far as I know, no-one has written up any detailed explanation of the internal strategies of ASSM – and there are a number of strange variations that you can uncover if you start running things that you might call “unexpected” tests; but many of the anomalies disappear (or become insignificant) when you’re operating at the correct scale.

A little pointer for the investigative: everyone who knows anything about ASSM seems to know that there’s a “Low high water mark” and a “High high water mark” and that tablescans somehow behave differently as you cross the LHWM into the area between the LHWM and HHWM – but the manuals only give you the basic concept, and don’t really describe how Oracle handles the many HWMs an ASSM segment holds. Another little pointer: create as select behaves very differently from create table, insert as select – especially for small tables.

This note was prompted by a question on OTN that sounded to me as if the original source had been prompted by a misunderstanding of how ASSM worked.

There are further details on how ASSM operates in this blog note that I wrote a few years ago.

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

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

You are commenting using your 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by

%d bloggers like this: