Re: Automatic Segment Space Management

In an attempt to get a handle on the overhead of the LHWM / HHWM thing, it's worth noting that you seem to get bitmap blocks per extent, and that the bitmap block caters for formatting 16 blocks at a time within that extent.

The Low HWM is the point up to which all the blocks are formatted - and above which there may be unformatted blocks; the High HWM is the highest point in the last extent below which blocks are formatted.

This means that once a 'normal' tablescan reaches the Low HWM, Oracle has to start checking the (level 1) bitmap blocks to guide it to the 16-block chunks (which can be read with multiblock reads) which are formatted above the LHWM.

In theory, there won't be very many such chunks, so the overhead caused by the non-contiguity shouldn't be terribly significant.

Of course, you are scanning more blocks than you need, and this looks particularly bad if you compare it to the vanilla scenario where you have a maximum of about 5 blocks formatted but unused on the master free list. But if you compare ASSM with the effects of having multiple freelists or freelist groups (which is the main problem that ASSM seeks to address), where each free list has had N blocks allocated, the difference is less dramatic - especially if you consider the case of old OPS - where you could allocate an extent to an instance - and find that an entire extent had to be formatted because there was only one high-water mark.

A couple of open questions I have still:

    What's the largest number of extents with     unformatted blocks in the middle that I can     generate without cheating ? (My bust_bits     script is a sort of cheat).

    When does the low high water mark get     adjusted - I've got some results in multiextent      segments where the 'correct' place
    for the LHWM is right up at the HHWM,     but the actual stored LHWM is still right     at the start of the segment.

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Coming soon one-day tutorials:
Cost Based Optimisation
Trouble-shooting and Tuning
Indexing Strategies
(see http://www.jlcomp.demon.co.uk/tutorial.html )

____UK_______March 19th
____USA_(FL)_May 2nd

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____USA_(CA, TX)_August

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

-----Original Message-----
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: 20 February 2003 12:16

>Kirti:
>
>There are some (or more) errors in my previous post. The process can
>not find the partly filled/free blocks until it scans the L1 bitmaps
>(I said L2 bitmap in the previous post as I was in half-sleep while
>composting the message) and the L2 bitmaps just point the scanning
>process to the respective L1 bitmaps.
>
>And the other overhead in ASSM is, there are chances you will waste
>around 1-5% (depending on the block size,etc) blocks for just keeping
>the meta data and this could be a overhead along with the space
waster
>in between the Low HWN and high HWM.
>
>Best Regards
>K Gopalakrishnan

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).