Re: Extent allocation
On Jul 20, 2001 at 07:50:44AM, Adrian Roe wrote: > Hi All, > > Is there any way to get Oracle (816) to do round robin extent allocation eg. > if a tablespace has 4 data files and each file is on a different disk, can > extents be allocated from each file in sequence ? As I understand, Oracle > will fill one file and then go onto the next file. Try this http://vbegun.net/oramag/extents_allocation/round_robin.sql Inform me please if it's helpful. Thanks. Have a nice weekend. -- Vladimir Begun | I think $[ is more like a coelacanth than a http://vbegun.net/ | mastadon. http://vbegun.net/wap/ | -- Larry Wall in [EMAIL PROTECTED]| <[EMAIL PROTECTED]> -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vladimir Begun INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Extent allocation
Adrian, As Vladimir showed in the sql script using the ALTER TABLE ... ALLOCATE EXTENT... works great. I have tablespaces across multiple datafiles and of course they are not used up completely before a table's next will not fit in the free space of the datafile. You have to change the next extent size to fit the free space and then ALLOCATE the extend to the table before you do the dataload. You end up with datafiles used to the max and no wasted free space at the end of the datafile. The method described in the "round_robin.sql" is a method of loading different data loads into different datafiles. Works great. ROR mª¿ªm >>> [EMAIL PROTECTED] 07/20/01 03:06PM >>> On Jul 20, 2001 at 07:50:44AM, Adrian Roe wrote: > Hi All, > > Is there any way to get Oracle (816) to do round robin extent allocation eg. > if a tablespace has 4 data files and each file is on a different disk, can > extents be allocated from each file in sequence ? As I understand, Oracle > will fill one file and then go onto the next file. Try this http://vbegun.net/oramag/extents_allocation/round_robin.sql Inform me please if it's helpful. Thanks. Have a nice weekend. -- Vladimir Begun | I think $[ is more like a coelacanth than a http://vbegun.net/ | mastadon. http://vbegun.net/wap/ | -- Larry Wall in [EMAIL PROTECTED]| <[EMAIL PROTECTED]> -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vladimir Begun INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Extent allocation
Manual allocation of extents, it is called "Oracle Striping". It is generally rarely used and most people tend to use raid to handle it. But is a very plausable techinique, as long as you can keep up with the extent allocation. I don't know of any way that automates this process. "Walking on water and developing software from a specification are easy if both are frozen." Christopher R. Spence Oracle DBA Fuelspot -Original Message- Sent: Friday, July 20, 2001 11:51 AM To: Multiple recipients of list ORACLE-L Hi All, Is there any way to get Oracle (816) to do round robin extent allocation eg. if a tablespace has 4 data files and each file is on a different disk, can extents be allocated from each file in sequence ? As I understand, Oracle will fill one file and then go onto the next file. Cheers, Ade -- Live Life in Broadband www.telewest.co.uk The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Statements and opinions expressed in this e-mail may not represent those of the company. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender immediately and delete the material from any computer. == -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Adrian Roe INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Christopher Spence INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Extent allocation
Manual allocation of extents, it is called "Oracle Striping". It is generally rarely used and most people tend to use raid to handle it. But is a very plausable techinique, as long as you can keep up with the extent allocation. I don't know of any way that automates this process. "Walking on water and developing software from a specification are easy if both are frozen." Christopher R. Spence Oracle DBA Fuelspot -Original Message- Sent: Friday, July 20, 2001 11:51 AM To: Multiple recipients of list ORACLE-L Hi All, Is there any way to get Oracle (816) to do round robin extent allocation eg. if a tablespace has 4 data files and each file is on a different disk, can extents be allocated from each file in sequence ? As I understand, Oracle will fill one file and then go onto the next file. Cheers, Ade -- Live Life in Broadband www.telewest.co.uk The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Statements and opinions expressed in this e-mail may not represent those of the company. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender immediately and delete the material from any computer. == -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Adrian Roe INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Christopher Spence INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Extent allocation
On Fri, 20 Jul 2001, Adrian Roe wrote: > Is there any way to get Oracle (816) to do round robin extent > allocation eg. if a tablespace has 4 data files and each file is on > a different disk, can extents be allocated from each file in > sequence ? As I understand, Oracle will fill one file and then go > onto the next file. It appears we may be discussing an imaginary problem. At least on the version I am using (8.1.6.2 HP-UX 64 bit), Oracle round robins among files with available space automatically. It does not just fill up one datafile and move on to the next. I did a little experiment: SQL> create tablespace jeremiah_temp1 1 datafile '/tmp/jeremiah_temp-01.dbf' size 10m, 2 '/tmp/jeremiah_temp-02.dbf' size 10m, 3 '/tmp/jeremiah_temp-03.dbf' size 10m, 4 '/tmp/jeremiah_temp-04.dbf' size 10m; Tablespace created. SQL> create table foobar 1 (baz varchar2(10)) 2 storage (initial 32k next 32k pctincrease 0 maxextents 4) 3 tablespace jeremiah_temp1; Table created. SQL> insert into foobar (baz) 1 select substr(trash,1,10) from garbage where rownum <= 6000; 6000 rows created. SQL> commit; Commit complete. SQL> select segment_name, e.bytes/1024 kb, file_name 1 from dba_extents e, dba_data_files d 2 where e.file_id = d.file_id 3 and e.segment_name = 'FOOBAR'; SEGMENT_NAME KB FILE_NAME -- -- -- FOOBAR 32 /tmp/jeremiah_temp-01.dbf FOOBAR 32 /tmp/jeremiah_temp-02.dbf FOOBAR 32 /tmp/jeremiah_temp-03.dbf FOOBAR 32 /tmp/jeremiah_temp-04.dbf So, it looks like it "round robins" automatically, and there is no need to do so manually. I don't know which version of Oracle was the first to do this. -- Jeremiah Wilton http://www.speakeasy.net/~jwilton -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeremiah Wilton INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Extent allocation
Jeremiah is right. Depending on the version of the database this is an imaginary problem. I have been using this 'auto round robin' feature since 8.0.6 (HP-UX 32-bit). Just did quite a bit of re-organization involving partitioning a large table and used this auto round robin extent allocation feature to spread out I/O... Anyone seen this in any lower versions ?? Cheers! - Kirti Deshpande Verizon Information Services http://www.superpages.com > -Original Message- > From: Jeremiah Wilton [SMTP:[EMAIL PROTECTED]] > Sent: Friday, July 20, 2001 4:57 PM > To: Multiple recipients of list ORACLE-L > Subject: Re: Extent allocation > > On Fri, 20 Jul 2001, Adrian Roe wrote: > > > Is there any way to get Oracle (816) to do round robin extent > > allocation eg. if a tablespace has 4 data files and each file is on > > a different disk, can extents be allocated from each file in > > sequence ? As I understand, Oracle will fill one file and then go > > onto the next file. > > It appears we may be discussing an imaginary problem. At least on the > version I am using (8.1.6.2 HP-UX 64 bit), Oracle round robins among > files with available space automatically. It does not just fill up > one datafile and move on to the next. > > I did a little experiment: > > SQL> create tablespace jeremiah_temp1 > 1 datafile '/tmp/jeremiah_temp-01.dbf' size 10m, > 2 '/tmp/jeremiah_temp-02.dbf' size 10m, > 3 '/tmp/jeremiah_temp-03.dbf' size 10m, > 4 '/tmp/jeremiah_temp-04.dbf' size 10m; > > Tablespace created. > > SQL> create table foobar > 1 (baz varchar2(10)) > 2 storage (initial 32k next 32k pctincrease 0 maxextents 4) > 3 tablespace jeremiah_temp1; > > Table created. > > SQL> insert into foobar (baz) > 1 select substr(trash,1,10) from garbage where rownum <= 6000; > > 6000 rows created. > > SQL> commit; > > Commit complete. > > SQL> select segment_name, e.bytes/1024 kb, file_name > 1 from dba_extents e, dba_data_files d > 2 where e.file_id = d.file_id > 3 and e.segment_name = 'FOOBAR'; > > SEGMENT_NAME KB FILE_NAME > -- -- -- > FOOBAR 32 /tmp/jeremiah_temp-01.dbf > FOOBAR 32 /tmp/jeremiah_temp-02.dbf > FOOBAR 32 /tmp/jeremiah_temp-03.dbf > FOOBAR 32 /tmp/jeremiah_temp-04.dbf > > So, it looks like it "round robins" automatically, and there is no > need to do so manually. I don't know which version of Oracle was the > first to do this. > > -- > Jeremiah Wilton > http://www.speakeasy.net/~jwilton > > also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Extent allocation
Kirti and others: Actually, Oracle only uses a round-robin extent allocation method for direct loads and parallel CTAS operations. For dynamic extent allocation, Oracle doesn't fill up one file and then the next. Actually, Oracle doesn't distinguish between datafiles, but rather looks at the total free space extents for the tablespace as listed in SYS.FET$ (Oracle's free extent cache). Basically, Oracle will look for a free extent equal to the size of the one it needs to allocate(see Doc ID #69343.1 on MetaLink of details on Oracle's extent algorithm). The first one on the list, regardless of the datafile, gets picked first. If one of exact size doesn't exist, Oracle will then split an existing larger extent. This larger extent, again, will be the first one found in SYS.FET$ which can provide the necessary space. So, it can at times appear to be round-robin allocation or one-file-at-a-time when, in point of fact, it is much more complex. Jon Walthour - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Friday, July 20, 2001 6:30 PM > Jeremiah is right. Depending on the version of the database this is an > imaginary problem. > I have been using this 'auto round robin' feature since 8.0.6 (HP-UX > 32-bit). Just did quite a bit of re-organization involving partitioning a > large table and used this auto round robin extent allocation feature to > spread out I/O... > > Anyone seen this in any lower versions ?? > > Cheers! > > - Kirti Deshpande > Verizon Information Services >http://www.superpages.com > > > -Original Message- > > From: Jeremiah Wilton [SMTP:[EMAIL PROTECTED]] > > Sent: Friday, July 20, 2001 4:57 PM > > To: Multiple recipients of list ORACLE-L > > Subject: Re: Extent allocation > > > > On Fri, 20 Jul 2001, Adrian Roe wrote: > > > > > Is there any way to get Oracle (816) to do round robin extent > > > allocation eg. if a tablespace has 4 data files and each file is on > > > a different disk, can extents be allocated from each file in > > > sequence ? As I understand, Oracle will fill one file and then go > > > onto the next file. > > > > It appears we may be discussing an imaginary problem. At least on the > > version I am using (8.1.6.2 HP-UX 64 bit), Oracle round robins among > > files with available space automatically. It does not just fill up > > one datafile and move on to the next. > > > > I did a little experiment: > > > > SQL> create tablespace jeremiah_temp1 > > 1 datafile '/tmp/jeremiah_temp-01.dbf' size 10m, > > 2 '/tmp/jeremiah_temp-02.dbf' size 10m, > > 3 '/tmp/jeremiah_temp-03.dbf' size 10m, > > 4 '/tmp/jeremiah_temp-04.dbf' size 10m; > > > > Tablespace created. > > > > SQL> create table foobar > > 1 (baz varchar2(10)) > > 2 storage (initial 32k next 32k pctincrease 0 maxextents 4) > > 3 tablespace jeremiah_temp1; > > > > Table created. > > > > SQL> insert into foobar (baz) > > 1 select substr(trash,1,10) from garbage where rownum <= 6000; > > > > 6000 rows created. > > > > SQL> commit; > > > > Commit complete. > > > > SQL> select segment_name, e.bytes/1024 kb, file_name > > 1 from dba_extents e, dba_data_files d > > 2 where e.file_id = d.file_id > > 3 and e.segment_name = 'FOOBAR'; > > > > SEGMENT_NAME KB FILE_NAME > > -- -- -- > > FOOBAR 32 /tmp/jeremiah_temp-01.dbf > > FOOBAR 32 /tmp/jeremiah_temp-02.dbf > > FOOBAR 32 /tmp/jeremiah_temp-03.dbf > > FOOBAR 32 /tmp/jeremiah_temp-04.dbf > > > > So, it looks like it "round robins" automatically, and there is no > > need to do so manually. I don't know which version of Oracle was the > > first to do this. > > > > -- > > Jeremiah Wilton > > http://www.speakeasy.net/~jwilton > > > > also send the HELP command for other information (like subscribing). > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Deshpande, Kirti > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists >
Re: Extent allocation
What do you mean by the "first one on the list?" The list ordered how? The order in which dynamic extent allocation is reading fet$ appears to favor distributing extents equally among files. -- Jeremiah Wilton http://www.speakeasy.net/~jwilton On Sat, 21 Jul 2001, Jon Walthour wrote: > Kirti and others: > > Actually, Oracle only uses a round-robin extent allocation method for direct > loads and parallel CTAS operations. For dynamic extent allocation, Oracle > doesn't fill up one file and then the next. Actually, Oracle doesn't > distinguish between datafiles, but rather looks at the total free space > extents for the tablespace as listed in SYS.FET$ (Oracle's free extent > cache). Basically, Oracle will look for a free extent equal to the size of > the one it needs to allocate(see Doc ID #69343.1 on MetaLink of details on > Oracle's extent algorithm). The first one on the list, regardless of the > datafile, gets picked first. If one of exact size doesn't exist, Oracle will > then split an existing larger extent. This larger extent, again, will be the > first one found in SYS.FET$ which can provide the necessary space. So, it > can at times appear to be round-robin allocation or one-file-at-a-time when, > in point of fact, it is much more complex. > > Jon Walthour > > - Original Message - > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Friday, July 20, 2001 6:30 PM > > > > Jeremiah is right. Depending on the version of the database this is an > > imaginary problem. > > I have been using this 'auto round robin' feature since 8.0.6 (HP-UX > > 32-bit). Just did quite a bit of re-organization involving partitioning a > > large table and used this auto round robin extent allocation feature to > > spread out I/O... > > > > Anyone seen this in any lower versions ?? > > > > Cheers! > > > > - Kirti Deshpande > > Verizon Information Services > >http://www.superpages.com > > > > > -Original Message- > > > From: Jeremiah Wilton [SMTP:[EMAIL PROTECTED]] > > > Sent: Friday, July 20, 2001 4:57 PM > > > To: Multiple recipients of list ORACLE-L > > > Subject: Re: Extent allocation > > > > > > On Fri, 20 Jul 2001, Adrian Roe wrote: > > > > > > > Is there any way to get Oracle (816) to do round robin extent > > > > allocation eg. if a tablespace has 4 data files and each file is on > > > > a different disk, can extents be allocated from each file in > > > > sequence ? As I understand, Oracle will fill one file and then go > > > > onto the next file. > > > > > > It appears we may be discussing an imaginary problem. At least on the > > > version I am using (8.1.6.2 HP-UX 64 bit), Oracle round robins among > > > files with available space automatically. It does not just fill up > > > one datafile and move on to the next. > > > > > > I did a little experiment: > > > > > > SQL> create tablespace jeremiah_temp1 > > > 1 datafile '/tmp/jeremiah_temp-01.dbf' size 10m, > > > 2 '/tmp/jeremiah_temp-02.dbf' size 10m, > > > 3 '/tmp/jeremiah_temp-03.dbf' size 10m, > > > 4 '/tmp/jeremiah_temp-04.dbf' size 10m; > > > > > > Tablespace created. > > > > > > SQL> create table foobar > > > 1 (baz varchar2(10)) > > > 2 storage (initial 32k next 32k pctincrease 0 maxextents 4) > > > 3 tablespace jeremiah_temp1; > > > > > > Table created. > > > > > > SQL> insert into foobar (baz) > > > 1 select substr(trash,1,10) from garbage where rownum <= 6000; > > > > > > 6000 rows created. > > > > > > SQL> commit; > > > > > > Commit complete. > > > > > > SQL> select segment_name, e.bytes/1024 kb, file_name > > > 1 from dba_extents e, dba_data_files d > > > 2 where e.file_id = d.file_id > > > 3 and e.segment_name = 'FOOBAR'; > > > > > > SEGMENT_NAME KB FILE_NAME > > > -- -- -- > > > FOOBAR 32 /tmp/jeremiah_temp-01.dbf > > > FOOBAR 32 /tmp/jeremiah_temp-02.dbf > > > FOOBAR 32 /tmp/jeremiah_temp-03.dbf > > > FOOBAR 32 /tmp/jeremiah_temp-04.db
Re: Extent allocation
I mean just that--that Oracle will look in its free extent cache for the first free extent the tablespace that is the same size as that which it needs. There is no particular ordering first. And that would make it "appear" to be distributing extents equally among the files, but not always. Sometimes it will also look, as another lister has pointed out, as if Oracle is always going to the biggest datafile in the tablespace. Jon Walthour - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Saturday, July 21, 2001 11:45 AM > What do you mean by the "first one on the list?" The list ordered > how? The order in which dynamic extent allocation is reading fet$ > appears to favor distributing extents equally among files. > > -- > Jeremiah Wilton > http://www.speakeasy.net/~jwilton > > On Sat, 21 Jul 2001, Jon Walthour wrote: > > > Kirti and others: > > > > Actually, Oracle only uses a round-robin extent allocation method for direct > > loads and parallel CTAS operations. For dynamic extent allocation, Oracle > > doesn't fill up one file and then the next. Actually, Oracle doesn't > > distinguish between datafiles, but rather looks at the total free space > > extents for the tablespace as listed in SYS.FET$ (Oracle's free extent > > cache). Basically, Oracle will look for a free extent equal to the size of > > the one it needs to allocate(see Doc ID #69343.1 on MetaLink of details on > > Oracle's extent algorithm). The first one on the list, regardless of the > > datafile, gets picked first. If one of exact size doesn't exist, Oracle will > > then split an existing larger extent. This larger extent, again, will be the > > first one found in SYS.FET$ which can provide the necessary space. So, it > > can at times appear to be round-robin allocation or one-file-at-a-time when, > > in point of fact, it is much more complex. > > > > Jon Walthour > > > > - Original Message - > > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > > Sent: Friday, July 20, 2001 6:30 PM > > > > > > > Jeremiah is right. Depending on the version of the database this is an > > > imaginary problem. > > > I have been using this 'auto round robin' feature since 8.0.6 (HP-UX > > > 32-bit). Just did quite a bit of re-organization involving partitioning a > > > large table and used this auto round robin extent allocation feature to > > > spread out I/O... > > > > > > Anyone seen this in any lower versions ?? > > > > > > Cheers! > > > > > > - Kirti Deshpande > > > Verizon Information Services > > >http://www.superpages.com > > > > > > > -Original Message- > > > > From: Jeremiah Wilton [SMTP:[EMAIL PROTECTED]] > > > > Sent: Friday, July 20, 2001 4:57 PM > > > > To: Multiple recipients of list ORACLE-L > > > > Subject: Re: Extent allocation > > > > > > > > On Fri, 20 Jul 2001, Adrian Roe wrote: > > > > > > > > > Is there any way to get Oracle (816) to do round robin extent > > > > > allocation eg. if a tablespace has 4 data files and each file is on > > > > > a different disk, can extents be allocated from each file in > > > > > sequence ? As I understand, Oracle will fill one file and then go > > > > > onto the next file. > > > > > > > > It appears we may be discussing an imaginary problem. At least on the > > > > version I am using (8.1.6.2 HP-UX 64 bit), Oracle round robins among > > > > files with available space automatically. It does not just fill up > > > > one datafile and move on to the next. > > > > > > > > I did a little experiment: > > > > > > > > SQL> create tablespace jeremiah_temp1 > > > > 1 datafile '/tmp/jeremiah_temp-01.dbf' size 10m, > > > > 2 '/tmp/jeremiah_temp-02.dbf' size 10m, > > > > 3 '/tmp/jeremiah_temp-03.dbf' size 10m, > > > > 4 '/tmp/jeremiah_temp-04.dbf' size 10m; > > > > > > > > Tablespace created. > > > > > > > > SQL> create table foobar > > > > 1 (baz varchar2(10)) > > > > 2 storage (initial 32k next 32k pctincrease 0 maxextents 4) > > > > 3 tablespace jeremiah_temp1; > > > > > > > > Table created. > > > > >
RE: Extent allocation
Thanks to all for your help, appreciated. Ade -Original Message- Sent: 21 July 2001 18:01 To: [EMAIL PROTECTED] I mean just that--that Oracle will look in its free extent cache for the first free extent the tablespace that is the same size as that which it needs. There is no particular ordering first. And that would make it "appear" to be distributing extents equally among the files, but not always. Sometimes it will also look, as another lister has pointed out, as if Oracle is always going to the biggest datafile in the tablespace. Jon Walthour - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Saturday, July 21, 2001 11:45 AM > What do you mean by the "first one on the list?" The list ordered > how? The order in which dynamic extent allocation is reading fet$ > appears to favor distributing extents equally among files. > > -- > Jeremiah Wilton > http://www.speakeasy.net/~jwilton > > On Sat, 21 Jul 2001, Jon Walthour wrote: > > > Kirti and others: > > > > Actually, Oracle only uses a round-robin extent allocation method for direct > > loads and parallel CTAS operations. For dynamic extent allocation, Oracle > > doesn't fill up one file and then the next. Actually, Oracle doesn't > > distinguish between datafiles, but rather looks at the total free space > > extents for the tablespace as listed in SYS.FET$ (Oracle's free extent > > cache). Basically, Oracle will look for a free extent equal to the size of > > the one it needs to allocate(see Doc ID #69343.1 on MetaLink of details on > > Oracle's extent algorithm). The first one on the list, regardless of the > > datafile, gets picked first. If one of exact size doesn't exist, Oracle will > > then split an existing larger extent. This larger extent, again, will be the > > first one found in SYS.FET$ which can provide the necessary space. So, it > > can at times appear to be round-robin allocation or one-file-at-a-time when, > > in point of fact, it is much more complex. > > > > Jon Walthour > > > > - Original Message - > > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > > Sent: Friday, July 20, 2001 6:30 PM > > > > > > > Jeremiah is right. Depending on the version of the database this is an > > > imaginary problem. > > > I have been using this 'auto round robin' feature since 8.0.6 (HP-UX > > > 32-bit). Just did quite a bit of re-organization involving partitioning a > > > large table and used this auto round robin extent allocation feature to > > > spread out I/O... > > > > > > Anyone seen this in any lower versions ?? > > > > > > Cheers! > > > > > > - Kirti Deshpande > > > Verizon Information Services > > >http://www.superpages.com > > > > > > > -Original Message- > > > > From: Jeremiah Wilton [SMTP:[EMAIL PROTECTED]] > > > > Sent: Friday, July 20, 2001 4:57 PM > > > > To: Multiple recipients of list ORACLE-L > > > > Subject: Re: Extent allocation > > > > > > > > On Fri, 20 Jul 2001, Adrian Roe wrote: > > > > > > > > > Is there any way to get Oracle (816) to do round robin extent > > > > > allocation eg. if a tablespace has 4 data files and each file is on > > > > > a different disk, can extents be allocated from each file in > > > > > sequence ? As I understand, Oracle will fill one file and then go > > > > > onto the next file. > > > > > > > > It appears we may be discussing an imaginary problem. At least on the > > > > version I am using (8.1.6.2 HP-UX 64 bit), Oracle round robins among > > > > files with available space automatically. It does not just fill up > > > > one datafile and move on to the next. > > > > > > > > I did a little experiment: > > > > > > > > SQL> create tablespace jeremiah_temp1 > > > > 1 datafile '/tmp/jeremiah_temp-01.dbf' size 10m, > > > > 2 '/tmp/jeremiah_temp-02.dbf' size 10m, > > > > 3 '/tmp/jeremiah_temp-03.dbf' size 10m, > > > > 4 '/tmp/jeremiah_temp-04.dbf' size 10m; > > > > > > > > Tablespace created. > > > > > > > > SQL> create table foobar > > > > 1 (baz varchar2(10)) > > > > 2 storage (initial 32k next 32k pctincrease 0 maxextents 4) > > &g
RE: Extent allocation
Title: Extent allocation It is a big clue. Oracle rounds to multiples of 5 extents to prevent the existence of a free extent of fewer than 5 blocks, which is unlikely to be usable. Paul Baumgartel InstiPro, Inc. [EMAIL PROTECTED] 212 813-0829 x103 (office) 917 549-4717 (mobile) -Original Message-From: Yttri, Lisa [mailto:[EMAIL PROTECTED]]Sent: Wednesday, February 07, 2001 4:26 PMTo: Multiple recipients of list ORACLE-LSubject: Extent allocation Hi - I am confused now about how oracle actually determines the size an extent should be. I have db_block_size set at 8192 and have defined a tablespace (and tables) at initial and next extents of 128K. When I do a show parameter db_file_multiblock_read_count it says 8. Therefore I thought 128K would be a good number, since it is a multiple of these numbers. However, when I check my extent sizes, I see that each extent contains 10 blocks which is 160K. I remember reading an article some time ago about how oracle actually determines what it thinks the extent size should be. Does anyone know of an article like this, or can anyone explain to me why it chose a different extent size? By the way, I looked at several tables and the one common thing I noticed is that the number of blocks allocated seems to be a multiple of 5 - ie. some are 5 blocks, some 10, some 20, etc. I don't know if that's coincidence or a big clue to the whole thing Lisa
Re: Extent Allocation
Hi, Oracle allocates blocks to an extent in multiple of 5. -- Neena -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: TIL-TEC INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Extent allocation
Hi Paul, I created an object with initial_extent 128K and it took 13 blocks. I then created the same object with 208K and still it took 13 blocks. My db_block size is 16K. I tested this on 8.1.5 and 8.1.6 and they both behave the same. TIA, Shakeel Qureshi [EMAIL PROTECTED] --- Paul Baumgartel <[EMAIL PROTECTED]> wrote: > It is a big clue. Oracle rounds to multiples of 5 > extents to prevent the > existence of a free extent of fewer than 5 blocks, > which is unlikely to be > usable. > > > > Paul Baumgartel > InstiPro, Inc. > [EMAIL PROTECTED] > 212 813-0829 x103 (office) > 917 549-4717 (mobile) > > -Original Message- > Sent: Wednesday, February 07, 2001 4:26 PM > To: Multiple recipients of list ORACLE-L > > > > Hi - > > I am confused now about how oracle actually > determines the size an extent > should be. I have db_block_size set at 8192 and > have defined a tablespace > (and tables) at initial and next extents of 128K. > When I do a show > parameter db_file_multiblock_read_count it says 8. > Therefore I thought 128K > would be a good number, since it is a multiple of > these numbers. > > However, when I check my extent sizes, I see that > each extent contains 10 > blocks which is 160K. > > I remember reading an article some time ago about > how oracle actually > determines what it thinks the extent size should be. > Does anyone know of an > article like this, or can anyone explain to me why > it chose a different > extent size? > > By the way, I looked at several tables and the one > common thing I noticed is > that the number of blocks allocated seems to be a > multiple of 5 - ie. some > are 5 blocks, some 10, some 20, etc. I don't know > if that's coincidence or > a big clue to the whole thing > > Lisa > > __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Shakeel Qureshi INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Extent allocation
Is the tablespace you are creating this table in a Locally Managed Tablespace? If so, then initial and next extents will be ignored. > -Original Message- > From: Shakeel Qureshi [SMTP:[EMAIL PROTECTED]] > Sent: Thursday, February 08, 2001 1:35 PM > To: Multiple recipients of list ORACLE-L > Subject: RE: Extent allocation > > Hi Paul, > > I created an object with initial_extent 128K and it > took 13 blocks. I then created the same object with > 208K and still it took 13 blocks. My db_block size is > 16K. I tested this on 8.1.5 and 8.1.6 and they both > behave the same. > > > TIA, > > Shakeel Qureshi > [EMAIL PROTECTED] > --- Paul Baumgartel <[EMAIL PROTECTED]> wrote: > > It is a big clue. Oracle rounds to multiples of 5 > > extents to prevent the > > existence of a free extent of fewer than 5 blocks, > > which is unlikely to be > > usable. > > > > > > > > Paul Baumgartel > > InstiPro, Inc. > > [EMAIL PROTECTED] > > 212 813-0829 x103 (office) > > 917 549-4717 (mobile) > > > > -Original Message- > > Sent: Wednesday, February 07, 2001 4:26 PM > > To: Multiple recipients of list ORACLE-L > > > > > > > > Hi - > > > > I am confused now about how oracle actually > > determines the size an extent > > should be. I have db_block_size set at 8192 and > > have defined a tablespace > > (and tables) at initial and next extents of 128K. > > When I do a show > > parameter db_file_multiblock_read_count it says 8. > > Therefore I thought 128K > > would be a good number, since it is a multiple of > > these numbers. > > > > However, when I check my extent sizes, I see that > > each extent contains 10 > > blocks which is 160K. > > > > I remember reading an article some time ago about > > how oracle actually > > determines what it thinks the extent size should be. > > Does anyone know of an > > article like this, or can anyone explain to me why > > it chose a different > > extent size? > > > > By the way, I looked at several tables and the one > > common thing I noticed is > > that the number of blocks allocated seems to be a > > multiple of 5 - ie. some > > are 5 blocks, some 10, some 20, etc. I don't know > > if that's coincidence or > > a big clue to the whole thing > > > > Lisa > > > > > > > __ > Do You Yahoo!? > Get personalized email addresses from Yahoo! Mail - only $35 > a year! http://personal.mail.yahoo.com/ > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Shakeel Qureshi > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Elliott, Patrick INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Extent allocation
Somewhere in the depths of Oracle 8.1 documentation, you will find a description of the extent algorithm. It's supposed to be more "enlightened" in allocating blocks to extents, in that it will try to allocate just what you asked for, and also try to fill holes, so it no longer necessarily rounds up to the nearest 5 blocks anymore. >>> [EMAIL PROTECTED] 02/08/01 03:26PM >>> Is the tablespace you are creating this table in a Locally Managed Tablespace? If so, then initial and next extents will be ignored. > -Original Message- > From: Shakeel Qureshi [SMTP:[EMAIL PROTECTED]] > Sent: Thursday, February 08, 2001 1:35 PM > To: Multiple recipients of list ORACLE-L > Subject: RE: Extent allocation > > Hi Paul, > > I created an object with initial_extent 128K and it > took 13 blocks. I then created the same object with > 208K and still it took 13 blocks. My db_block size is > 16K. I tested this on 8.1.5 and 8.1.6 and they both > behave the same. > > > TIA, > > Shakeel Qureshi > [EMAIL PROTECTED] > --- Paul Baumgartel <[EMAIL PROTECTED]> wrote: > > It is a big clue. Oracle rounds to multiples of 5 > > extents to prevent the > > existence of a free extent of fewer than 5 blocks, > > which is unlikely to be > > usable. > > > > > > > > Paul Baumgartel > > InstiPro, Inc. > > [EMAIL PROTECTED] > > 212 813-0829 x103 (office) > > 917 549-4717 (mobile) > > > > -Original Message- > > Sent: Wednesday, February 07, 2001 4:26 PM > > To: Multiple recipients of list ORACLE-L > > > > > > > > Hi - > > > > I am confused now about how oracle actually > > determines the size an extent > > should be. I have db_block_size set at 8192 and > > have defined a tablespace > > (and tables) at initial and next extents of 128K. > > When I do a show > > parameter db_file_multiblock_read_count it says 8. > > Therefore I thought 128K > > would be a good number, since it is a multiple of > > these numbers. > > > > However, when I check my extent sizes, I see that > > each extent contains 10 > > blocks which is 160K. > > > > I remember reading an article some time ago about > > how oracle actually > > determines what it thinks the extent size should be. > > Does anyone know of an > > article like this, or can anyone explain to me why > > it chose a different > > extent size? > > > > By the way, I looked at several tables and the one > > common thing I noticed is > > that the number of blocks allocated seems to be a > > multiple of 5 - ie. some > > are 5 blocks, some 10, some 20, etc. I don't know > > if that's coincidence or > > a big clue to the whole thing > > > > Lisa > > > > > > > __ > Do You Yahoo!? > Get personalized email addresses from Yahoo! Mail - only $35 > a year! http://personal.mail.yahoo.com/ > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Shakeel Qureshi > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Elliott, Patrick INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tim Sawmiller INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Extent allocation
Indeed, the Concepts Manual affirms this in Chapter 4. So why can't they correct the information regarding TRUNCATE? Just after the section on extent allocation, the book states that the privilige needed to TRUNCATE a table is DELETE ANY TABLE, when in fact it should be DROP ANY TABLE. Jared On Thu, 8 Feb 2001, Tim Sawmiller wrote: > Somewhere in the depths of Oracle 8.1 documentation, you will find a description > of the extent algorithm. It's supposed to be more "enlightened" in allocating > blocks to extents, in that it will try to allocate just what you asked for, > and also try to fill holes, so it no longer necessarily rounds up to the nearest 5 >blocks anymore. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Extent allocation
Title: RE: Extent allocation I found a document on metalink #69343.1 that pretty much explains it. Unfortunately, it makes it sound like you can never be sure of the sizing you choose, because it looks at the size of the first two open blocks, rather than finding a block that fits what you want. Am I missing something? -- From: Shakeel Qureshi To: Multiple recipients of list ORACLE-L Subject: RE: Extent allocation Date: Thursday, February 08, 2001 1:34PM Hi Paul, I created an object with initial_extent 128K and it took 13 blocks. I then created the same object with 208K and still it took 13 blocks. My db_block size is 16K. I tested this on 8.1.5 and 8.1.6 and they both behave the same. TIA, Shakeel Qureshi [EMAIL PROTECTED] --- Paul Baumgartel <[EMAIL PROTECTED]> wrote: > It is a big clue. Oracle rounds to multiples of 5 > extents to prevent the > existence of a free extent of fewer than 5 blocks, > which is unlikely to be > usable. > > > > Paul Baumgartel > InstiPro, Inc. > [EMAIL PROTECTED] > 212 813-0829 x103 (office) > 917 549-4717 (mobile) > > -Original Message- > Sent: Wednesday, February 07, 2001 4:26 PM > To: Multiple recipients of list ORACLE-L > > > > Hi - > > I am confused now about how oracle actually > determines the size an extent > should be. I have db_block_size set at 8192 and > have defined a tablespace > (and tables) at initial and next extents of 128K. > When I do a show > parameter db_file_multiblock_read_count it says 8. > Therefore I thought 128K > would be a good number, since it is a multiple of > these numbers. > > However, when I check my extent sizes, I see that > each extent contains 10 > blocks which is 160K. > > I remember reading an article some time ago about > how oracle actually > determines what it thinks the extent size should be. > Does anyone know of an > article like this, or can anyone explain to me why > it chose a different > extent size? > > By the way, I looked at several tables and the one > common thing I noticed is > that the number of blocks allocated seems to be a > multiple of 5 - ie. some > are 5 blocks, some 10, some 20, etc. I don't know > if that's coincidence or > a big clue to the whole thing > > Lisa > > __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Shakeel Qureshi INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists 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: Extent allocation
What do you want, miracles? :-) As you probably remember, DELETE ANY TABLE was indeed the privilege required to issue a TRUNCATE command, when TRUNCATE first became available (release 7.0, I think). They changed it early on to DROP ANY TABLE (7.1?), but the documentation has apparently been caste in stone. Sigh. Such is life in the big city of IS. >>> [EMAIL PROTECTED] 02/09/01 11:55AM >>> Indeed, the Concepts Manual affirms this in Chapter 4. So why can't they correct the information regarding TRUNCATE? Just after the section on extent allocation, the book states that the privilige needed to TRUNCATE a table is DELETE ANY TABLE, when in fact it should be DROP ANY TABLE. Jared On Thu, 8 Feb 2001, Tim Sawmiller wrote: > Somewhere in the depths of Oracle 8.1 documentation, you will find a description > of the extent algorithm. It's supposed to be more "enlightened" in allocating > blocks to extents, in that it will try to allocate just what you asked for, > and also try to fill holes, so it no longer necessarily rounds up to the nearest 5 >blocks anymore. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tim Sawmiller INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Extent allocation
Hi Patrick, No. Its not locally managed tablespace. Shakeel Qureshi [EMAIL PROTECTED] --- Tim Sawmiller <[EMAIL PROTECTED]> wrote: > Somewhere in the depths of Oracle 8.1 documentation, > you will find a description of the extent algorithm. > It's supposed to be more "enlightened" in > allocating blocks to extents, in that it will try to > allocate just what you asked for, and also try to > fill holes, so it no longer necessarily rounds up to > the nearest 5 blocks anymore. > > >>> [EMAIL PROTECTED] 02/08/01 03:26PM >>> > Is the tablespace you are creating this table in a > Locally Managed > Tablespace? If so, then initial and next extents > will be ignored. > > > -Original Message- > > From: Shakeel Qureshi [SMTP:[EMAIL PROTECTED]] > > Sent: Thursday, February 08, 2001 1:35 PM > > To: Multiple recipients of list ORACLE-L > > Subject:RE: Extent allocation > > > > Hi Paul, > > > > I created an object with initial_extent 128K and > it > > took 13 blocks. I then created the same object > with > > 208K and still it took 13 blocks. My db_block > size is > > 16K. I tested this on 8.1.5 and 8.1.6 and they > both > > behave the same. > > > > > > TIA, > > > > Shakeel Qureshi > > [EMAIL PROTECTED] > > --- Paul Baumgartel <[EMAIL PROTECTED]> wrote: > > > It is a big clue. Oracle rounds to multiples of > 5 > > > extents to prevent the > > > existence of a free extent of fewer than 5 > blocks, > > > which is unlikely to be > > > usable. > > > > > > > > > > > > Paul Baumgartel > > > InstiPro, Inc. > > > [EMAIL PROTECTED] > > > 212 813-0829 x103 (office) > > > 917 549-4717 (mobile) > > > > > > -Original Message- > > > Sent: Wednesday, February 07, 2001 4:26 PM > > > To: Multiple recipients of list ORACLE-L > > > > > > > > > > > > Hi - > > > > > > I am confused now about how oracle actually > > > determines the size an extent > > > should be. I have db_block_size set at 8192 and > > > have defined a tablespace > > > (and tables) at initial and next extents of > 128K. > > > When I do a show > > > parameter db_file_multiblock_read_count it says > 8. > > > Therefore I thought 128K > > > would be a good number, since it is a multiple > of > > > these numbers. > > > > > > However, when I check my extent sizes, I see > that > > > each extent contains 10 > > > blocks which is 160K. > > > > > > I remember reading an article some time ago > about > > > how oracle actually > > > determines what it thinks the extent size should > be. > > > Does anyone know of an > > > article like this, or can anyone explain to me > why > > > it chose a different > > > extent size? > > > > > > By the way, I looked at several tables and the > one > > > common thing I noticed is > > > that the number of blocks allocated seems to be > a > > > multiple of 5 - ie. some > > > are 5 blocks, some 10, some 20, etc. I don't > know > > > if that's coincidence or > > > a big clue to the whole thing > > > > > > Lisa > > > > > > > > > > > > __ > > Do You Yahoo!? > > Get personalized email addresses from Yahoo! Mail > - only $35 > > a year! http://personal.mail.yahoo.com/ > > -- > > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > > -- > > Author: Shakeel Qureshi > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- (858) 538-5051 > FAX: (858) 538-5051 > > San Diego, California-- Public Internet > access / Mailing Lists > > > > > 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). > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Elliott, Patrick >
RE: Extent allocation for objects
Fyi,
Note I was referring to the other day.
04:11 PM PST
Please respond to [EMAIL PROTECTED]
Sent by:[EMAIL PROTECTED]
To:"Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
cc:
At 02:16 PM 5/19/2003 -0800, you wrote:
>Adding the initial clause appears to work fine. Thank you very much for
>your help.
>
>Thanks for the other responses. I learn new things every day.
If you specify an initial extent size that is at most as large as the
smallest uniform extent size of any of your LMTs then you'll not run into
this problem in the first placs. Consider:
SQL> select
TABLESPACE_NAME,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTLEN,CONTENTS,EXTENT_MANAGEMENT,ALLOCATION_TYPE
2 from dba_tablespaces where tablespace_name in ('USERS','TOOLS')
3 /
TABLESPA INITIAL_EXTENT NEXT_EXTENT MIN_EXTLEN CONTENTS EXTENT_MAN ALLOCATIO
-- --- -- - -- -
TOOLS262144 262144 262144 PERMANENT LOCAL UNIFORM
USERS 32768 32768 32768 PERMANENT LOCAL UNIFORM
i.e. 2 tablespaces with 256K and 32K uniform extents respectively.
SQL> create table a (n number) tablespace tools;
Table created.
SQL> create table b (n number) tablespace tools storage( initial 2K);
Table created.
pollux.stats.scott> select segment_name, segment_type, tablespace_name,
initial_extent, extents, bytes
2 from user_segments where segment_name in ('A','B');
SEGMENT_ SEGMENT_TYPE TABLESPA INITIAL_EXTENTEXTENTS BYTES
-- -- -- --
ATABLE TOOLS262144 1 262144
BTABLE TOOLS 8192 1 262144
The 2K initial extent request for table b got adjusted by Oracle to the
minimum possible in the database with a 4K db_block_size, but it got
recorded in tab$ even though a 256K extent got allocated in the TOOLS LMT
according to the uniform extent size.
SQL> alter table a move tablespace users;
Table altered.
SQL> alter table b move tablespace users;
Table altered.
SQL> select segment_name, segment_type, tablespace_name, initial_extent,
extents, bytes
2 from user_segments where segment_name in ('A','B');
SEGMENT_ SEGMENT_TYPE TABLESPA INITIAL_EXTENTEXTENTS BYTES
-- -- -- --
ATABLE USERS262144 8 262144
BTABLE USERS 8192 1 32768
Because the recorded initial extent size of table b is less than the
uniform extent size of the target tablespace, it gets sized down correctly
while table a maintains it allocated size.
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).
American Express made the following
annotations on 06/09/2003 02:26:35 PM
--
**
"This message and any attachments are solely for the intended recipient and may
contain confidential or privileged information. If you are not the intended recipient,
any disclosure, copying, use, or distribution of the information included in this
message and any attachments is prohibited. If you have received this communication in
error, please notify us by reply e-mail and immediately and permanently delete this
message and any attachments. Thank you."
**
==
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Tracy Rahmlow
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
