Top 60 Oracle Blogs

Recent comments


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