Btw, I did some testing on ASSM (9.2.0.4) a while ago and it seems there is
only 2 blocks required for ASSM when talking about small number of 5 block
extents. 2 for ASSM + one for header and rest two get formatted for data
when first row is inserted into table (using conventional mode, when doing
direct insert then only these blocks are formatted which get data written
into them).
I don't understand the reasons why there is a 5 block minimum limit on ASSM
tablespace extent size (with smaller size you get an error message when
creating tablespace as you probably did). I't might have something to do
with level-3 bitmap blocks, but I'm still working on it...
Tanel.
- Original Message -----
To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
Sent: Wednesday, October 01, 2003 5:29 AM
> However, I get a different result:
>
> Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
> PL/SQL Release 9.2.0.1.0 - Production
> CORE 9.2.0.1.0 Production
> TNS for Linux: Version 9.2.0.1.0 - Production
> NLSRTL Version 9.2.0.1.0 - Production
>
> 5 rows selected.
>
> SQL>
> SQL> CREATE TABLESPACE "AUTO16K" LOGGING
> 2 DATAFILE '/u01/ORACLE/ora92/auto16k01.dbf' SIZE 51264K REUSE
> 3 AUTOEXTEND ON NEXT 20480K MAXSIZE 200M BLOCKSIZE 16384
> 4 EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT MANUAL
> 5 /
>
> Tablespace created.
>
> SQL>
> SQL> create table a (a number) tablespace auto16k;
>
> Table created.
>
> SQL>
> SQL> select owner,segment_name,extent_id,blocks
> 2 from dba_extents where tablespace_name = 'AUTO16K';
>
> OWNER SEGMENT_NAME EXTENT_ID blks
> -------- ---------------- ---------- ----------
> SCOTT A 0 4
>
> 1 row selected.
>
> SQL>
>
> 4*16K = 64K initial extent.
>
> Only when i replicate your example exactly, i.e. with space management
> auto, do I get the same result:
>
> Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
> PL/SQL Release 9.2.0.1.0 - Production
> CORE 9.2.0.1.0 Production
> TNS for Linux: Version 9.2.0.1.0 - Production
> NLSRTL Version 9.2.0.1.0 - Production
>
> 5 rows selected.
>
> SQL>
> SQL> CREATE TABLESPACE "AUTO16K" LOGGING
> 2 DATAFILE '/u01/ORACLE/ora92/auto16K01.dbf' SIZE 51264K REUSE
> 3 AUTOEXTEND ON NEXT 20480K MAXSIZE 200M BLOCKSIZE 16384
> 4 EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
> 5 /
>
> Tablespace created.
>
> SQL>
> SQL> create table a (a number) tablespace auto16k;
>
> Table created.
>
> SQL>
> SQL> select owner,segment_name,extent_id,blocks
> 2 from dba_extents where tablespace_name = 'AUTO16K';
>
> OWNER SEGMENT_NAME
> EXTENT_ID blks
> --------
> --------------------------------------------------------------------------
> ---------- ----------
> SCOTT A
> 0 64
>
> 1 row selected.
>
> So the 1M initial extent allocation is not due to a "5 block minimum
> allocation rule" but due to the fact that automatic space management
> requires 3 blocks plus 1 block for the segment header plus 1 block for
> actual data = 5 blocks, which lifts the request above the 64K threshold
for
> a tablespace with 16K extents.
>
> At 05:54 PM 9/30/2003 -0800, you wrote:
> >Yes, and there is one thing to add:
> >If you do not specify INTIAL, the extent allocation starts with 5 blocks
for
> >the intial extent. For 8k, it's 40k, but in an autoallocating LMT
extent
> >cannot be smaller then 64k, so it is the amount of the space allocated.
The
> >interesting question is: what happens with blocksize-16k? Will there be
64k
> >or two extents of 64k, i.e. 128k?
> >Here is the answer:
> >
> >SQL> create tablespace test1
> > 2 datafile '/data/oradata/data/test101.dbf' size 64M reuse
> > 3 autoextend on next 64m maxsize 513M
> > 4 extent management local autoallocate
> > 5 segment space management auto
> > 6 blocksize 16k
> > 7 /
> >
> >Tablespace created.
> >
> >SQL> create table a (a number) tablespace test1;
> >
> >Table created.
> >
> >SQL> select owner,segment_name,extent_id,blocks
> > 2 from dba_extents
> > 3 where segment_name='A'and tablespace_name='TEST1'
> > 4 and owner=user
> > 5 /
> >
> >OWNER SEGMENT_NA EXTENT_ID BLOCKS
> >------------------------------ ---------- ---------- ----------
> >OPS$MGOGALA A 0 64
> >
> >16k*64=1M. That means that oracle will allocate a full megabyte for the
> >initial extent. It cannot take 64k, because it's smaller then 5*16k
> >(that number of 5 blocks is hardwired into the RDBMS since time
immemorial)
> >and it cannot take two extents because that would, in turn, mean that the
> >initial extent is smaller then 5 blocks. Therefore, it takes 1M. Jonathan
> >Lewis was right. Here is one tecnique for optimizing the disk consumption
in
> >such cases:
> >
> >SQL> drop tablespace test1 including contents and datafiles;
> >
> >Tablespace dropped.
> >
> >SQL>
> >
> >
> >
> >
> >On 2003.09.30 20:34, Jacques Kilchoer wrote:
> >> > Ive read the book. PCTINCREASE is basically set to 100% so
> >> > the extent sizes double. Thats 'basically' how it works. I
> >> > have seen some posts on dejanews saying it doesnt necessarily
> >> > work this way and some people are finding large extent sizes
> >> > with just a few extents and when tables are dropped this is
> >> > leading to fragmentation. It hasnt happened to me, but the
> >> > posts on dejanews were from some pretty good posters. So Im
> >> > playing conservative. We also had one of the contributors
> >> > here mention issues.
> >>
> >>I think Jonathan Lewis has explained the algorithm before, but it's also
> >>something that we have investigated here.
> >>The algorithm (ignoring some details) is:
> >>There will be 4 extent sizes used, 64K, 1M, 8M, 64M
> >>As long as object allocation is 1M or less, 64K extent sizes are used,
> >>When object allocation is between 1M and 64M, 1M extent sizes are used.
> >>When object allocation is between 64M and 1G, 8M extent sizes are used.
> >>When object allocation is more than 1G, 64M extent sizes are used.
> >>However, when you initially create the object, the extents are
determined by
> >>figuring out the space allocated to the newly created object taking into
> >>account the INITIAL, NEXT, PCTINCREASE, MINEXTENTS storage parameters.
So
> >>the
> >>object might start off with 1M extents instead of starting off with 64K
> >>extents. The algorithm is similar to the one outlined above but it is
more
> >>complicated. The NEXT and PCTINCREASE seem to be ignored after the
object is
> >>created.
> >>e.g.
> >>create table ... tablespace locally_managed_autoallocate
> >> storage (initial 1M next 512K minextents 15 pctincrease 0) ... ;
> >>Initial allocation will be 1M + (15 - 1) * 512K = 8M
> >>When you create the table, you will see eight extents, each of one
megabyte.
> >>There are additional wrinkles, but I don't think the algorithm has
"bugs".
> >>I don't think that there really is "fragmentation" in the sense that an
> >>unused extent will remain unused forever. All extents will be in one of
the
> >>4
> >>sizes mentioned above, and all are subject to reuse at some point.
> >>--
> >>Please see the official ORACLE-L FAQ: http://www.orafaq.net
> >>--
> >>Author: Jacques Kilchoer
> >> INET: Jacques.Kilchoer_at_quest.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).
>
> Wolfgang Breitling
> 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).
>
--
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).
Received on Wed Oct 01 2003 - 09:39:43 CDT
