| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Re: A transaction profile question - block growth always groups of 5?
>
> <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 :)
> With
> one exception: the rounding up to 5 will only NOT take place if there is
> insufficient space in which to create the 5-multiple extent.
>
> So no, I probably didn't just invent it. All apologies gratefully received,
Apologies. I was partially wrong. Oracle does try to round up. Does not happen for <5-block extents though, and you still can get up to 4 blocks more than rounded value.
Just in case: As mentioned many times before, no minimum extent size for tablespace is specified.
> but I doubt I'll be holding my breath.
Up to you.
Igor.
>
> HJR
>
>
> > Igor.
> >
> >
> > >
> > > HJR
> > >
> > >
> > > <61.12.128.6 [Igor Laletin]> wrote in message
> > > news:9cthq18pfc_at_news2.newsguy.com...
> > > > > From memory, the old 5 block trick was an Oracle 7 thing... but it
also
> > > > > takes place in Oracle 8 (and presumably 8i) UNLESS you specify a
MINIMUM
> > > > > EXTENT clause for the tablespace. If that's specified, then the
increment
> > > > > is in MINIMUM EXTENT-sized chunks. If it's not, then it's the
*table's*
> > > > > NEXT clause, rounding up to the nearest 5.
> > > >
> > > > Let me disagree. There is no compulsory rounding up to the nearest 5.
> > > > The extent could be 1 block in size (no min extent size of course).
> > > > 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.
> > > >
> > > > Igor.
> > > >
> > > >
> > > >
> > > > ==================================
> > > > Poster's IP address: 61.12.128.6
> > > > Posted via http://nodevice.com
> > > > Linux Programmer's Site
> > >
> > >
> >
> >
> > ==================================
> > Poster's IP address: 203.101.17.58
> > Posted via http://nodevice.com
> > Linux Programmer's Site
>
>
 |
 |