RE: locally managed autoallocate (was: Separate Indexes and Data)
Well, sort of. If I had a 9i DB to play with, I'd try this myself... 1) Create an SMT. 2) Create 50 tables in that TS. 3) Drop every even table just created. 4) Extend one of the remaining tables until you can't. Depending on the size of the TS created and the blocksize, you could have a situation where there's enough freespace in the TS, but not contiguous. Thus, fragmentation. Granted, this is isn't a typical case, but over a year or two, I could see it happening here. Maybe I'll install 9iR2 at home tonite to try this... Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA > -Original Message- > From: Niall Litchfield [mailto:[EMAIL PROTECTED] > Sent: Thursday, October 02, 2003 5:10 PM > To: Multiple recipients of list ORACLE-L > Subject: RE: locally managed autoallocate (was: Separate Indexes and > Data) > > > > > -Original Message- > > > From: Jesse, Rich > > > Sent: Wednesday, October 01, 2003 9:49 AM > > > To: Multiple recipients of list ORACLE-L > > > Subject: RE: locally managed autoallocate (was: Separate > Indexes and > > > Data) > > > > > > Theoritically, perhaps, but what if an existing table needs > > > to auto-extend > > > at 1M and all that's left in the table is 16 (or whatever) > > > > (blush) Obviously, that's supposed to say "left in the tableSPACE". > > 9.0.1 'unable to allocate extent' > > C:\Documents and Settings\Niall>sqlplus niall/niall > > SQL*Plus: Release 9.2.0.1.0 - Production on Thu Oct 2 21:59:45 2003 > > Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. > > > Connected to: > Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production > With the Partitioning, OLAP and Oracle Data Mining options > JServer Release 9.2.0.1.0 - Production > > SQL> create tablespace test_auto > 2 datafile 'c:\oracle\oradata\home9i\test_auto.dbf' size 1152k > 3 extent management local; > > Tablespace created. > > SQL> create table t1(n number,charcol char(200)) > 2 tablespace test_auto > 3 ; > > Table created. > > SQL> select count(*) from dba_extents where segment_name='T1'; > > COUNT(*) > -- > 1 > [snip] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: locally managed autoallocate (was: Separate Indexes and Data)
> > -Original Message- > > From: Jesse, Rich > > Sent: Wednesday, October 01, 2003 9:49 AM > > To: Multiple recipients of list ORACLE-L > > Subject: RE: locally managed autoallocate (was: Separate Indexes and > > Data) > > > > Theoritically, perhaps, but what if an existing table needs > > to auto-extend > > at 1M and all that's left in the table is 16 (or whatever) > > (blush) Obviously, that's supposed to say "left in the tableSPACE". 9.0.1 'unable to allocate extent' C:\Documents and Settings\Niall>sqlplus niall/niall SQL*Plus: Release 9.2.0.1.0 - Production on Thu Oct 2 21:59:45 2003 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production SQL> create tablespace test_auto 2 datafile 'c:\oracle\oradata\home9i\test_auto.dbf' size 1152k 3 extent management local; Tablespace created. SQL> create table t1(n number,charcol char(200)) 2 tablespace test_auto 3 ; Table created. SQL> select count(*) from dba_extents where segment_name='T1'; COUNT(*) -- 1 SQL> begin 2 for i in 1..14 loop 3 execute immediate 'alter table t1 allocate extent'; 4 end loop; 5 end; 6 / PL/SQL procedure successfully completed. SQL> analyze table t1 compute statistics; Table analyzed. SQL> select count(*) from dba_extents where segment_name='T1'; COUNT(*) -- 15 SQL> alter table t1 allocate extent; Table altered. SQL> select count(*) from dba_extents where segment_name='T1'; COUNT(*) -- 16 SQL> alter table t1 allocate extent; alter table t1 allocate extent * ERROR at line 1: ORA-01653: unable to extend table NIALL.T1 by 128 in tablespace TEST_AUTO SQL> select bytes from dba_free_space where tablespace_name='TEST_AUTO'; BYTES -- 65536 SQL> create table tX(n number,charcol char(200)) 2 tablespace test_auto; Table created. Niall -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Niall Litchfield INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: locally managed autoallocate (was: Separate Indexes and
Hi!
I think 5 blocks was the minimum extent size despite any parameters in
Oracle 7 (and all allocations were rounded to multiple of 5 blocks).
The parameter _bump_highwarer_mark_count (which defaults to 5), specifies
how many new blocks above HWM are put onto freelist when new free blocks are
needed. And _walk_insert_treshold specified number of blocks in freelist to
scan before a new block was allocated instead (causing HWM to extend).
Tanel.
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Wednesday, October 01, 2003 3:24 PM
> Actually, 5 blocks wasn't completely hardwired, there was an undocumented
> parameter ("_walk_insert_threshold" or something like that. My notes from
> Scott Gosset's course are largely unreadable. What has hapened to my
> handwriting? ) which was utilized to define the number of blocks that
will be
> added to the free list. If I remember corectly, there was a serious bug
with
> DMT, which was precisely about the FREELIST mechanism which would prevent
> freed blocks from being reused. That was mentioned by somebody else here.
I
> believe that the workaround was to set _walk_nsert_threshold to 7. I
> distinctly remember seeing K. Gopalakrishnan's name mentioned in
connection
> with that, and this is the same symptom that was described by somebody
else on
> this list. As I am utilizing LMT's and segment space auto management, I'm
> mostly oblivious to DMT woes. K. Gopalakrishnan (I hope my spelling is
> correct) my shed some more light onto this affair.
>
>
> On 2003.10.01 00:59, Wolfgang Breitling wrote:
> > I can't recall right now where I found out about the 3 blocks required
for
> > automatic space management. Could have been an error message when I
tried to
> > create a table with a 2 block extent in an ASSM tablespace, or a
> > presentation at IOUG, or perhaps even on this list.
> > The 5 block rule is the documented allocation rule for DMT where Oracle
> > rounds requests for segments greater than 5 blocks to the next multiple
of 5
> > blocks (unless it finds a free segment of exactly the right size or
..).
> > Which is why you couldn't implement a uniform extent size policy in DMT
with
> > extent sizes of exact powers of 2 (64, 128, 256, 1024, ...) to make use
of
> > the full IO bandwith of the OS (which is generally a power of 2) for
full
> > scans since they all were not multiples of 5 blocks. Not until the
minimum
> > extent size option came in Oracle 8 (not to be confused with
minextents).
> > But then LMTs came in Oracle 8i and retired the entire DMT allocation
> > scheme.
> >
> > At 07:49 PM 9/30/2003 -0800, you wrote:
> >
> >> I repeated your test, with the same result. You, of course, are right.
> >> Interesting, that means that oracle gave up on that "5 blocks rule".
> >> Where did you come accross the fact that automatic space management
> >> requires 3 blocks? That is, I suppose, for freeelists & freelist
groups?
> >> I must confess that I assumed that the old 5 blocks rule still holds
true,
> >> so I didn't test further. Also, I was testing the problem that I had
with
> >> autoallocate and automatic segment management, which turned out to be
> >> a SCSI controller problem. Basically, when I created the tablespace on
EIDE
> >> device, it worked as advertised, but when I attempted to do that on a
SCSI
> >> disk, it failed. To dispell all doubts, SCSI controller died in 2 days,
> >> causing, of course a system and the database crash. May it rest in
peace,
> >> in the place SCSI controllers go when they burn out.
> >>
> >> --
> >> Mladen Gogala
> >> Oracle DBA
> >> --
> >> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> >> --
> >> Author: Mladen Gogala
> >> INET: [EMAIL PROTECTED]
> >
> > Wolfgang Breitling
> > Oracle7, 8, 8i, 9i OCP DBA
> > Centrex Consulting Corporation
> > http://www.centrexcc.com
> >--
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> >--
> > Author: Wolfgang Breitling
> > INET: [EMAIL PROTECTED]
> >
> > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
>
> 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
RE: locally managed autoallocate (was: Separate Indexes and Data)
> -Original Message- > From: Jesse, Rich > Sent: Wednesday, October 01, 2003 9:49 AM > To: Multiple recipients of list ORACLE-L > Subject: RE: locally managed autoallocate (was: Separate Indexes and > Data) > > Theoritically, perhaps, but what if an existing table needs > to auto-extend > at 1M and all that's left in the table is 16 (or whatever) (blush) Obviously, that's supposed to say "left in the tableSPACE". > 64K chunks. I > still maintain that system-managed tablespaces are barely > better than DMTs > -- fragmentation is still potentially a problem and needs to > be monitored. > > On the flip-side, LMT segments need to be watched too in case they are > growing beyond the design of the TS (e.g. more than 1024 or > how ever many > extents). I'd much rather deal with the latter because it's much less > likely to happen unexpectedly in our environment. > > Rich Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: locally managed autoallocate (was: Separate Indexes and Data)
> -Original Message- > From: Jacques Kilchoer [mailto:[EMAIL PROTECTED] > Sent: Tuesday, September 30, 2003 7:34 PM > To: Multiple recipients of list ORACLE-L > Subject: locally managed autoallocate (was: Separate Indexes and Data) > > > > 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. Theoritically, perhaps, but what if an existing table needs to auto-extend at 1M and all that's left in the table is 16 (or whatever) 64K chunks. I still maintain that system-managed tablespaces are barely better than DMTs -- fragmentation is still potentially a problem and needs to be monitored. On the flip-side, LMT segments need to be watched too in case they are growing beyond the design of the TS (e.g. more than 1024 or how ever many extents). I'd much rather deal with the latter because it's much less likely to happen unexpectedly in our environment. Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: locally managed autoallocate (was: Separate Indexes and
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" <[EMAIL PROTECTED]> 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 > CORE9.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'; > > OWNERSEGMENT_NAME EXTENT_ID blks > -- -- > SCOTTA 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 > CORE9.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'; > > OWNERSEGMENT_NAME > EXTENT_ID blks > > -- --- > -- -- > SCOTTA > 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$MGOGALAA 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; >
RE: locally managed autoallocate (was: Separate Indexes and
5 is the number of blocks (probably defined in a header file) that is gotten
for creation.
it could be that the blocksize matters, but haven't seen it any other way
than 5.
after that, the HWM is "bumped" with 5 blocks too
(_bump_highwater_mark_count)
_walk_insert_threshold is the number of blocks (5 by default) that oracle
looks at, at the top of the freelist,
for insertion of data.
frits
-Original Message-
Sent: woensdag 1 oktober 2003 14:25
To: Multiple recipients of list ORACLE-L
Actually, 5 blocks wasn't completely hardwired, there was an undocumented
parameter ("_walk_insert_threshold" or something like that. My notes from
Scott Gosset's course are largely unreadable. What has hapened to my
handwriting? ) which was utilized to define the number of blocks that will
be
added to the free list. If I remember corectly, there was a serious bug with
DMT, which was precisely about the FREELIST mechanism which would prevent
freed blocks from being reused. That was mentioned by somebody else here. I
believe that the workaround was to set _walk_nsert_threshold to 7. I
distinctly remember seeing K. Gopalakrishnan's name mentioned in connection
with that, and this is the same symptom that was described by somebody else
on
this list. As I am utilizing LMT's and segment space auto management, I'm
mostly oblivious to DMT woes. K. Gopalakrishnan (I hope my spelling is
correct) my shed some more light onto this affair.
On 2003.10.01 00:59, Wolfgang Breitling wrote:
> I can't recall right now where I found out about the 3 blocks required for
> automatic space management. Could have been an error message when I tried
to
> create a table with a 2 block extent in an ASSM tablespace, or a
> presentation at IOUG, or perhaps even on this list.
> The 5 block rule is the documented allocation rule for DMT where Oracle
> rounds requests for segments greater than 5 blocks to the next multiple of
5
> blocks (unless it finds a free segment of exactly the right size or ...).
> Which is why you couldn't implement a uniform extent size policy in DMT
with
> extent sizes of exact powers of 2 (64, 128, 256, 1024, ...) to make use of
> the full IO bandwith of the OS (which is generally a power of 2) for full
> scans since they all were not multiples of 5 blocks. Not until the minimum
> extent size option came in Oracle 8 (not to be confused with minextents).
> But then LMTs came in Oracle 8i and retired the entire DMT allocation
> scheme.
>
> At 07:49 PM 9/30/2003 -0800, you wrote:
>
>> I repeated your test, with the same result. You, of course, are right.
>> Interesting, that means that oracle gave up on that "5 blocks rule".
>> Where did you come accross the fact that automatic space management
>> requires 3 blocks? That is, I suppose, for freeelists & freelist groups?
>> I must confess that I assumed that the old 5 blocks rule still holds
true,
>> so I didn't test further. Also, I was testing the problem that I had with
>> autoallocate and automatic segment management, which turned out to be
>> a SCSI controller problem. Basically, when I created the tablespace on
EIDE
>> device, it worked as advertised, but when I attempted to do that on a
SCSI
>> disk, it failed. To dispell all doubts, SCSI controller died in 2 days,
>> causing, of course a system and the database crash. May it rest in peace,
>> in the place SCSI controllers go when they burn out.
>>
>> --
>> Mladen Gogala
>> Oracle DBA
>> --
>> Please see the official ORACLE-L FAQ: http://www.orafaq.net
>> --
>> Author: Mladen Gogala
>> INET: [EMAIL PROTECTED]
>
> Wolfgang Breitling
> Oracle7, 8, 8i, 9i OCP DBA
> Centrex Consulting Corporation
> http://www.centrexcc.com
>--
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
> Author: Wolfgang Breitling
> INET: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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
Re: locally managed autoallocate (was: Separate Indexes and
Actually, 5 blocks wasn't completely hardwired, there was an undocumented
parameter ("_walk_insert_threshold" or something like that. My notes from
Scott Gosset's course are largely unreadable. What has hapened to my
handwriting? ) which was utilized to define the number of blocks that will be
added to the free list. If I remember corectly, there was a serious bug with
DMT, which was precisely about the FREELIST mechanism which would prevent
freed blocks from being reused. That was mentioned by somebody else here. I
believe that the workaround was to set _walk_nsert_threshold to 7. I
distinctly remember seeing K. Gopalakrishnan's name mentioned in connection
with that, and this is the same symptom that was described by somebody else on
this list. As I am utilizing LMT's and segment space auto management, I'm
mostly oblivious to DMT woes. K. Gopalakrishnan (I hope my spelling is
correct) my shed some more light onto this affair.
On 2003.10.01 00:59, Wolfgang Breitling wrote:
I can't recall right now where I found out about the 3 blocks required for
automatic space management. Could have been an error message when I tried to
create a table with a 2 block extent in an ASSM tablespace, or a
presentation at IOUG, or perhaps even on this list.
The 5 block rule is the documented allocation rule for DMT where Oracle
rounds requests for segments greater than 5 blocks to the next multiple of 5
blocks (unless it finds a free segment of exactly the right size or ...).
Which is why you couldn't implement a uniform extent size policy in DMT with
extent sizes of exact powers of 2 (64, 128, 256, 1024, ...) to make use of
the full IO bandwith of the OS (which is generally a power of 2) for full
scans since they all were not multiples of 5 blocks. Not until the minimum
extent size option came in Oracle 8 (not to be confused with minextents).
But then LMTs came in Oracle 8i and retired the entire DMT allocation
scheme.
At 07:49 PM 9/30/2003 -0800, you wrote:
I repeated your test, with the same result. You, of course, are right.
Interesting, that means that oracle gave up on that "5 blocks rule".
Where did you come accross the fact that automatic space management
requires 3 blocks? That is, I suppose, for freeelists & freelist groups?
I must confess that I assumed that the old 5 blocks rule still holds true,
so I didn't test further. Also, I was testing the problem that I had with
autoallocate and automatic segment management, which turned out to be
a SCSI controller problem. Basically, when I created the tablespace on EIDE
device, it worked as advertised, but when I attempted to do that on a SCSI
disk, it failed. To dispell all doubts, SCSI controller died in 2 days,
causing, of course a system and the database crash. May it rest in peace,
in the place SCSI controllers go when they burn out.
--
Mladen Gogala
Oracle DBA
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mladen Gogala
INET: [EMAIL PROTECTED]
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
Re: locally managed autoallocate (was: Separate Indexes and
I can't recall right now where I found out about the 3 blocks required for automatic space management. Could have been an error message when I tried to create a table with a 2 block extent in an ASSM tablespace, or a presentation at IOUG, or perhaps even on this list. The 5 block rule is the documented allocation rule for DMT where Oracle rounds requests for segments greater than 5 blocks to the next multiple of 5 blocks (unless it finds a free segment of exactly the right size or ...). Which is why you couldn't implement a uniform extent size policy in DMT with extent sizes of exact powers of 2 (64, 128, 256, 1024, ...) to make use of the full IO bandwith of the OS (which is generally a power of 2) for full scans since they all were not multiples of 5 blocks. Not until the minimum extent size option came in Oracle 8 (not to be confused with minextents). But then LMTs came in Oracle 8i and retired the entire DMT allocation scheme. At 07:49 PM 9/30/2003 -0800, you wrote: I repeated your test, with the same result. You, of course, are right. Interesting, that means that oracle gave up on that "5 blocks rule". Where did you come accross the fact that automatic space management requires 3 blocks? That is, I suppose, for freeelists & freelist groups? I must confess that I assumed that the old 5 blocks rule still holds true, so I didn't test further. Also, I was testing the problem that I had with autoallocate and automatic segment management, which turned out to be a SCSI controller problem. Basically, when I created the tablespace on EIDE device, it worked as advertised, but when I attempted to do that on a SCSI disk, it failed. To dispell all doubts, SCSI controller died in 2 days, causing, of course a system and the database crash. May it rest in peace, in the place SCSI controllers go when they burn out. -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: locally managed autoallocate (was: Separate Indexes and
On 2003.09.30 22:29, Wolfgang Breitling wrote: 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 CORE9.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'; OWNERSEGMENT_NAME EXTENT_ID blks -- -- SCOTTA 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 CORE9.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'; OWNERSEGMENT_NAME EXTENT_ID blks - -- -- SCOTTA 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. I repeated your test, with the same result. You, of course, are right. Interesting, that means that oracle gave up on that "5 blocks rule". Where did you come accross the fact that automatic space management requires 3 blocks? That is, I suppose, for freeelists & freelist groups? I must confess that I assumed that the old 5 blocks rule still holds true, so I didn't test further. Also, I was testing the problem that I had with autoallocate and automatic segment management, which turned out to be a SCSI controller problem. Basically, when I created the tablespace on EIDE device, it worked as advertised, but when I attempted to do that on a SCSI disk, it failed. To dispell all doubts, SCSI controller died in 2 days, causing, of course a system and the database crash. May it rest in peace, in the place SCSI controllers go when they burn out. -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: locally managed autoallocate (was: Separate Indexes and
At 06:29 PM 9/30/2003 -0800, I wrote: 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. The "which lifts the request above the 64K threshold for a tablespace with 16K extents" is poorly worded. What I meant to say is "For a tablespace with a 16K block size those 5 blocks come to 80K which is more than the 64K first tier for autoallocated extents and you thus get 1 extent of the next tier size: 1M" Wolfgang Breitling Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
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 CORE9.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'; OWNERSEGMENT_NAME EXTENT_ID blks -- -- SCOTTA 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 CORE9.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'; OWNERSEGMENT_NAME EXTENT_ID blks - -- -- SCOTTA 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$MGOGALAA 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 c
Re: locally managed autoallocate (was: Separate Indexes and Data)
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$MGOGALAA 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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 subs
