Re: locally managed autoallocate (was: Separate Indexes and

Hi!

I think 5 blocks was the minimum extent size despite any parameters in Oracle 7 (and all allocations were rounded to multiple of 5 blocks). The parameter _bump_highwarer_mark_count (which defaults to 5), specifies how many new blocks above HWM are put onto freelist when new free blocks are needed. And _walk_insert_treshold specified number of blocks in freelist to scan before a new block was allocated instead (causing HWM to extend).

Tanel.

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Wednesday, October 01, 2003 3:24 PM

> Actually, 5 blocks wasn't completely hardwired, there was an undocumented
> parameter ("_walk_insert_threshold" or something like that. My notes from
> Scott Gosset's course are largely unreadable. What has hapened to my
> handwriting? ) which was utilized to define the number of blocks that
will be
> added to the free list. If I remember corectly, there was a serious bug
with
> DMT, which was precisely about the FREELIST mechanism which would prevent
> freed blocks from being reused. That was mentioned by somebody else here.
I
> believe that the workaround was to set _walk_nsert_threshold to 7. I
> distinctly remember seeing K. Gopalakrishnan's name mentioned in
connection
> with that, and this is the same symptom that was described by somebody
else on
> this list. As I am utilizing LMT's and segment space auto management, I'm
> mostly oblivious to DMT woes. K. Gopalakrishnan (I hope my spelling is
> correct) my shed some more light onto this affair.
>
>
> On 2003.10.01 00:59, Wolfgang Breitling wrote:
> > I can't recall right now where I found out about the 3 blocks required
for
> > automatic space management. Could have been an error message when I
tried to
> > create a table with a 2 block extent in an ASSM tablespace, or a
> > presentation at IOUG, or perhaps even on this list.
> > The 5 block rule is the documented allocation rule for DMT where Oracle
> > rounds requests for segments greater than 5 blocks to the next multiple
of 5
> > blocks (unless it finds a free segment of exactly the right size or
..).
> > Which is why you couldn't implement a uniform extent size policy in DMT
with
> > extent sizes of exact powers of 2 (64, 128, 256, 1024, ...) to make use
of
> > the full IO bandwith of the OS (which is generally a power of 2) for
full
> > scans since they all were not multiples of 5 blocks. Not until the
minimum
> > extent size option came in Oracle 8 (not to be confused with
minextents).
> > But then LMTs came in Oracle 8i and retired the entire DMT allocation
> > scheme.
> >
> > At 07:49 PM 9/30/2003 -0800, you wrote:
> >
> >> I repeated your test, with the same result. You, of course, are right.
> >> Interesting, that means that oracle gave up on that "5 blocks rule".
> >> Where did you come accross the fact that automatic space management
> >> requires 3 blocks? That is, I suppose, for freeelists & freelist
groups?
> >> I must confess that I assumed that the old 5 blocks rule still holds
true,
> >> so I didn't test further. Also, I was testing the problem that I had
with
> >> autoallocate and automatic segment management, which turned out to be
> >> a SCSI controller problem. Basically, when I created the tablespace on
EIDE
> >> device, it worked as advertised, but when I attempted to do that on a
SCSI
> >> disk, it failed. To dispell all doubts, SCSI controller died in 2 days,
> >> causing, of course a system and the database crash. May it rest in
peace,
> >> in the place SCSI controllers go when they burn out.
> >>
> >> --
> >> Mladen Gogala
> >> Oracle DBA
> >> --
> >> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> >> --
> >> Author: Mladen Gogala
> >> INET: mgogala_at_adelphia.net
> >
> > Wolfgang Breitling
> > Oracle7, 8, 8i, 9i OCP DBA
> > Centrex Consulting Corporation
> > http://www.centrexcc.com
> >--
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> >--
> > Author: Wolfgang Breitling
> > INET: breitliw_at_centrexcc.com
> >
> > 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).
> --
> Mladen Gogala
> Oracle DBA
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Mladen Gogala
> INET: mgogala_at_adelphia.net
>
> 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).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tanel Poder
  INET: tanel.poder.003_at_mail.ee

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