| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: locally managed autoallocate (was: Separate Indexes and
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 MANUAL5 /
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 AUTO5 /
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
--------
---------------------------------------------------------------------------------
---------- ----------
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).Received on Tue Sep 30 2003 - 21:29:33 CDT
 |
 |