| 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: 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.
>
> 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);
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. 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, but I doubt I'll be holding my breath.
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
Received on Mon May 07 2001 - 18:46:00 CDT
 |
 |