Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: In praise of auto space management

Re: In praise of auto space management

From: Andrew Mobbs <>
Date: 11 Jul 2002 14:00:04 +0100 (BST)
Message-ID: <0xs*>

Jonathan Lewis <> wrote:
>Have you really got 5,141 truly concurrent
>insert processes running at high speed ?
>Your figures seem a bit extreme - especially
>for an index cluster.
>Can you tell us a little more ?
>Is this OPS/RAC by the way - the comment
>about half-empty extents suggests it may be.

No, usually only 200 or so processes at most. I probably went overboard on the number of freelists. Yes, truely concurrent, independent processes inserting into the same single-table index cluster. (If I have my way, it will become an IOT before too long).

The number of freelist groups keeps going up as I benchmark on faster machines to compensate for master freelist contention. The problem is, this these batch processes insert into an index cluster, and on an initial load of a new dataset there may be 8,000 or more new blocks being created per second (since of course, each unique cluster index gets its own block). On subsequent runs with the same data, and when only a small portion of the data are new, there isn't as much of a problem.

That places a huge load on the space management system. I've generally been struggling with a balancing act between _bump_highwater_mark_count, freelists, and freelist groups to counter buffer busy waits and HW enqueues. Thus I was very happy when I first say ASSM remove all these problems with a single clause to "create tablespace".

The theory behind the huge number of freelists and groups was to try and even out the hashing between a process and a master freelist. While with ideal hashing I should have been able to get away with a largish number of groups and a relatively small number of freelists, in practice, it seemed that this still saw contention. In the heat of a benchmark the obvious solution was to turn all the dials up to 11 (or 97, being the largest prime less than 100). While this did solve the buffer busy wait contention I was seeing, in the cold light of explaining it to somebody else, I'm less convinced by my own theory.

The comment about half empty extents was related to the number of freelist groups, and disproportionate space being returned by various deletion jobs. It probably wasn't totally fair, since the space will be reused eventually.

This particular test wasn't RAC, but a subsequent one was. Just to clarify, my job is mostly benchmarking and performance related work for a company that produces one of those 3rd party Oracle applications that the DBAs posting here so enjoy not being able to modify.

Andrew Mobbs -
Received on Thu Jul 11 2002 - 08:00:04 CDT

Original text of this message