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 multi-
extent 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 <[EMAIL PROTECTED]>
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
