Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Re: A transaction profile question - block growth always groups of 5?

Re: Re: A transaction profile question - block growth always groups of 5?

From: Howard J. Rogers <howardjr_at_www.com>
Date: Wed, 9 May 2001 07:04:37 +1000
Message-ID: <3af85f93@news.iprimus.com.au>

<61.12.128.6 [Igor Laletin]> wrote in message news:9d8hf262597_at_news1.newsguy.com...
> >
> > <203.101.17.58 [Igor Laletin]> wrote in message
> > news:9d5jrp62upr_at_news2.newsguy.com...
> > > > Igor: read your books.
> > >
> > > Where is it written that next extent always gets rounded up to the
 nearest
> > 5 blocks? Reference, please!
> >
> > Well, for starters: http://www.ixora.com.au/tips/creation/extents.htm
> >
> > ...in which it becomes clear that I made one (albeit significant)
 omission:
> > if you ask for LESS than 5 blocks, you get what you ask for. But if you
 ask
> > for anything else, it gets rounded up. Ask for 7, you'll get 10. Ask
 for
> > 44, you'll get 45. Ask for 3, you'll get 3.
> >
> > >
> > > > I didn't just invent this stuff.
> > >
> > > You probably did :) Let's check it out in 8.1.7 but I'm pretty sure
 it's
> > the same in other versions.
> >
> > "pretty sure"? Nice to be so authoratitive.
>
> OK, you are right here. There is no need to be "so authoratitive" so I  take it back.

> And, yes, it is nice :)
>
>

> > >
> > > SQL> create table x (x number) tablespace data storage (initial 8k
 next 8k
 pctincrease 0);
> > >
> > > Table created.
> > >
> > > SQL> insert into x (select 1000 from dba_objects);
> > >
> > > 3053 rows created.
> > >
> > > SQL> select EXTENT_ID,FILE_ID,BLOCK_ID,BLOCKS
> > > 2 from dba_extents where owner='SYSTEM' and segment_name='X' order
 by
 EXTENT_ID;
> > >
> > > EXTENT_ID FILE_ID BLOCK_ID BLOCKS
> > > --------------- --------------- --------------- ---------------
> > > 0 5 1007 2
> > > 1 5 1009 1
> > > 2 5 262 1
> > > 3 5 1010 1
> > > 4 5 1011 1
> > >
> > > 5 rows selected.
> > >
> > > So... Why I can not see extents "rounding up to the nearest 5" ?
> > >
> >
> > Because you asked for LESS than 5 block extents (which is pretty
 unlikely in
> > most production environements in my experience). Try it again with
 settings
> > for initial and next of, say 56K -8 blocks, will round up to 80K. In
 fact,
> > why don't I just show you on 8.1.6:
> >
> > SVRMGR> connect / as sysdba
> > Connected.
> > SVRMGR> create tablespace blah datafile 'c:\blah.dbf' size 1m;
> > Statement processed.
> > SVRMGR> create table blahtest(
> > 2> col1 char(5))
> > 3> tablespace blah
> > 4> storage (initial 56K next 56K);
> > Statement processed.
> > SVRMGR> alter table blahtest allocate extent;
> > Statement processed.
> > SVRMGR> select EXTENT_ID,FILE_ID,BLOCK_ID,BLOCKS
> > 2> from dba_extents where segment_name = 'BLAHTEST';
> > EXTENT_ID FILE_ID BLOCK_ID BLOCKS
> > ---------- ---------- ---------- ----------
> > 0 9 2 10
> > 1 9 12 10
> > 2 rows selected.
> >
> > ... And 10 blocks, unless I'm very much mistaken, sounds suspiciously
 like a
> > rounding up to 5 blocks or multiples thereof to me.
> >
> > You'll also note from Steve's website that fragmentation doesn't affect
 this
> > issue, or cause it, in the slightest, despite what you posted earlier.
>
> Wrong. Howard: read your books.
> http://www.ixora.com.au/tips/creation/extents.htm
> "Extents may be enlarged further if their allocation would otherwise leave
 a free space fragment of less than 5 blocks." >
> Say you have 12-blocks free extent and you ask for 7-blocks extent. Oracle  rounds up to 10 but remaining 2 blocks are a too small chunk.
> So you get all 12.
>
> ... And 12 blocks, unless I'm very much mistaken, sounds suspiciously like
 not a rounding up to 5 blocks or multiples thereof to me :) >

In other words, to avoid fragmentation in the first place, Oracle may decide NOT to round to multiples of 5. Which is entirely the opposite of what you stated originally ("Yes, it can get up to 4 blocks more than you requested. _If_ the free space is fragmented in such a way Oracle couldn't find anything better."), and agrees with my statement that 'fragmentation does not cause [rounding]' -because what you have here is potential fragmentation *preventing* the rounding. I agree that in this quantum age, concepts of causality can be confusing, but not that much in this case, surely.

[Snip]

HJR Received on Tue May 08 2001 - 16:04:37 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US