Re: RE: Autoallocate vs Uniform extent performance
Hi Jonathan, OK let's give it a go (note that I'm now doing this at home on my not quite so powerful PC with Best of Bowie playing on the CD so it's just me having a play) SQL create tablespace biggish 2 datafile 'c:\bowie\biggish01.dbf' size 3000m 3 uniform size 24k; Tablespace created. SQL set timing on SQL create table lots_of_extents (x number) 2 storage (initial 1450m) 3 tablespace biggish; Table created. Elapsed: 00:01:48.02 SQL select count(*) from user_extents where segment_name = 'LOTS_OF_EXTENTS'; COUNT(*) -- 61887 Elapsed: 00:00:08.01 SQL drop table lots_of_extents; Table dropped. Elapsed: 00:00:16.09 SQL create table lots_of_extents1 (x number) 2 tablespace biggish; Table created. Elapsed: 00:00:00.01 SQL create table lots_of_extents2 (x number) 2 tablespace biggish; Table created. Elapsed: 00:00:00.00 SQL begin 2for i in 1..61886 loop 3 execute immediate 'alter table lots_of_extents1 allocate extent'; 4 execute immediate 'alter table lots_of_extents2 allocate extent'; 5end loop; 6 end; 7 / PL/SQL procedure successfully completed. Elapsed: 00:19:29.07 SQL select count(*) from user_extents where segment_name='LOTS_OF_EXTENTS1'; COUNT(*) -- 61887 Elapsed: 00:00:04.09 SQL select count(*) from user_extents where segment_name='LOTS_OF_EXTENTS2'; COUNT(*) -- 61887 Elapsed: 00:00:04.08 SQL drop table lots_of_extents1; Table dropped. Elapsed: 00:00:06.05 SQL drop table lots_of_extents2; Table dropped. Elapsed: 00:00:05.07 It's actually somewhat faster but in case caching and the such has had an effect ... SQL create table lots_of_extents (x number) 2 storage (initial 1450m) 3 tablespace biggish; Table created. Elapsed: 00:01:13.00 SQL drop table lots_of_extents; Table dropped. Elapsed: 00:00:05.06 So it's all about the same I have a little experiment in mind that could cause me to reconsider heaps of extents but it might have to wait a day or two. Cheers Richard - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, April 05, 2003 5:04 AM I think you ought to refine your test: Create two tables at one extent each, then alternately allocate one extent to each table until you get to a very large number of extents. THEN try dropping one of them. Remember to set tablespace quotas for the user creating the table. It still won't scare you off, by the way, so you don't have to do it. Check with Connor which version of Oracle introduced the modification that updates tsq$ just once one the drop, rather than once per extent as this does make a difference. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html UK___April 8th UK___April 22nd Denmark__May 21-23rd USA_(FL)_May 2nd Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK_(Manchester)_May Estonia___June (provisional) USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: 04 April 2003 09:23 Hi Pete, Stop using my favourite answer :) I'm not suggesting this is particularly scientific but here's a quick test on dropping a moderate number of extents (9.2 on XP): SQL create tablespace biggish 2 datafile 'c:\bowie\biggish01.dbf' size 2000M 3 uniform size 16K; Tablespace created. SQL set timing on SQL create table lots_of_extents (x number) 2 storage (initial 1990M) 3 tablespace biggish; Table created. Elapsed: 00:00:49.06 SQL select count(*) from user_extents where segment_name = 'LOTS_OF_EXTENTS'; COUNT(*) -- 127423 Elapsed: 00:00:04.01 SQL drop table lots_of_extents; Table dropped. Elapsed: 00:00:06.08 Based on the above, I could drop a table with 1,000,000 extents in under 1 minute. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote
Re: RE: Autoallocate vs Uniform extent performance
Hi Pete, Stop using my favourite answer :) I'm not suggesting this is particularly scientific but here's a quick test on dropping a moderate number of extents (9.2 on XP): SQL create tablespace biggish 2 datafile 'c:\bowie\biggish01.dbf' size 2000M 3 uniform size 16K; Tablespace created. SQL set timing on SQL create table lots_of_extents (x number) 2 storage (initial 1990M) 3 tablespace biggish; Table created. Elapsed: 00:00:49.06 SQL select count(*) from user_extents where segment_name = 'LOTS_OF_EXTENTS'; COUNT(*) -- 127423 Elapsed: 00:00:04.01 SQL drop table lots_of_extents; Table dropped. Elapsed: 00:00:06.08 Based on the above, I could drop a table with 1,000,000 extents in under 1 minute. I need a better reason for keeping this table to the 100 extent mark ... Cheers Richard - Original Message - Date: Friday, April 4, 2003 1:53 pm BD Did you expect any other answer than it depends? :) Seriously, we've seen a few thousand extents without any problems in later releases. We've also heard of customers not noticing things had gone horribly adrift from what they thought and end up with hundreds of thousandsof extents. No empirical evidence of performance problems with said hundreds of thousands of extents, but boy that drop command can be an absolute mother if you try to fix it! :) Pete Controlling developers is like herding cats. Kevin Loney, Oracle DBA Handbook Oh no, it's not. It's much harder than that! Bruce Pihlamae, long-term Oracle DBA -Original Message- Sent: Thursday, April 03, 2003 5:28 PM To: Multiple recipients of list ORACLE-L Just a general question to everyone (and one I've asked a few times before in different forums). If we're talking LMT, how many extents are too many ? Assuming no quotas (which does introduce some known issues) at what point do you say that your standard uniform size of 64K has generated too many extents and that performance is noticeably suffering to the level where the inconvenience of a table reorg is warranted ? When has anyone reached the point with an object in a LMT whereby performance has been an issue and by *only* reducing the number of extents, you've said phew, that's better ? If seen many suggestions on standard uniform sizes that are somewhat similar to those used by autoallocate, most of which have a scale of magnitude around the 100 mark. These always made sense with DMT so are we trying to implement outdated recommendations to LMTs ? Does hitting the 100 extent mark warrant such concern and need to change our extent size ? My little brain usually works best with smaller numbers and I can gauge the level of growth somewhat easier with smaller number of extents but is that a justification for being so picky with what extent size an object should have ? Some dba_ views will take longer to get me details I'm after but is that sufficient justification for being so picky with extent sizes ? Curious in anyone's thoughts as I would hate to think we have a myth a happening ... Richard - Original Message - Date: Friday, April 4, 2003 9:18 am I totally agree Gaja. I support a SAP BW system and they create tables with a 100 of partitionsand only load 24 of them. With autoallocate, most of them are small (64k) and space is not wasted. If they do decide to load them up, I'm still safe because the extent size increase as the object grows. I'm don't advocate of autoallocate for everything because I can't determinethe next extent, but this is one place where it's better than uniform. I also have some uniform LMTs for larger tables that I migrate to when tables get too big. Steve - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, April 03, 2003 11:33 AM Totally agree with Connor. Just to add a comment to his note. A usage model recommended for UNIFORM vs. AUTOALLOCATE follows: If you know the data volume and growth of your segments and they are predictable, then use UNIFORM. If you are completely in the dark with: 1) How much data is going to be persisted in the segments? 2) What growth patterns the segments are going to exhibit? Then use AUTOALLOCATE. Of course, if you do change your mind, after the fact, you can use the MOVE command to the tablespace of choice with the extent allocation of your choice. Cheers, Gaja --- Connor McDonald [EMAIL PROTECTED] wrote: I don't believe that was the case. auto and uniform in all of the (admittedly rudimentary and subjective) tests I've done appear the same in terms of performance. I prefer uniform purely for the reasons of: - more thorough elimination of fragmentation - predictability of next
Re: RE: Autoallocate vs Uniform extent performance
I count that as 127 thousand extents dropped in 6seconds. The CREATE TABLE + EXTENTS took 49seconds. No, I am not trying to draw any conclusions, just making an observation. I am comfortable with thousands of extents but wouldn't look at a million extents. Hemant At 12:23 AM 04-04-03 -0800, you wrote: Hi Pete, Stop using my favourite answer :) I'm not suggesting this is particularly scientific but here's a quick test on dropping a moderate number of extents (9.2 on XP): SQL create tablespace biggish 2 datafile 'c:\bowie\biggish01.dbf' size 2000M 3 uniform size 16K; Tablespace created. SQL set timing on SQL create table lots_of_extents (x number) 2 storage (initial 1990M) 3 tablespace biggish; Table created. Elapsed: 00:00:49.06 SQL select count(*) from user_extents where segment_name = 'LOTS_OF_EXTENTS'; COUNT(*) -- 127423 Elapsed: 00:00:04.01 SQL drop table lots_of_extents; Table dropped. Elapsed: 00:00:06.08 Based on the above, I could drop a table with 1,000,000 extents in under 1 minute. I need a better reason for keeping this table to the 100 extent mark ... Cheers Richard - Original Message - Date: Friday, April 4, 2003 1:53 pm BD Did you expect any other answer than it depends? :) Seriously, we've seen a few thousand extents without any problems in later releases. We've also heard of customers not noticing things had gone horribly adrift from what they thought and end up with hundreds of thousandsof extents. No empirical evidence of performance problems with said hundreds of thousands of extents, but boy that drop command can be an absolute mother if you try to fix it! :) Pete Controlling developers is like herding cats. Kevin Loney, Oracle DBA Handbook Oh no, it's not. It's much harder than that! Bruce Pihlamae, long-term Oracle DBA -Original Message- Sent: Thursday, April 03, 2003 5:28 PM To: Multiple recipients of list ORACLE-L Just a general question to everyone (and one I've asked a few times before in different forums). If we're talking LMT, how many extents are too many ? Assuming no quotas (which does introduce some known issues) at what point do you say that your standard uniform size of 64K has generated too many extents and that performance is noticeably suffering to the level where the inconvenience of a table reorg is warranted ? When has anyone reached the point with an object in a LMT whereby performance has been an issue and by *only* reducing the number of extents, you've said phew, that's better ? If seen many suggestions on standard uniform sizes that are somewhat similar to those used by autoallocate, most of which have a scale of magnitude around the 100 mark. These always made sense with DMT so are we trying to implement outdated recommendations to LMTs ? Does hitting the 100 extent mark warrant such concern and need to change our extent size ? My little brain usually works best with smaller numbers and I can gauge the level of growth somewhat easier with smaller number of extents but is that a justification for being so picky with what extent size an object should have ? Some dba_ views will take longer to get me details I'm after but is that sufficient justification for being so picky with extent sizes ? Curious in anyone's thoughts as I would hate to think we have a myth a happening ... Richard - Original Message - Date: Friday, April 4, 2003 9:18 am I totally agree Gaja. I support a SAP BW system and they create tables with a 100 of partitionsand only load 24 of them. With autoallocate, most of them are small (64k) and space is not wasted. If they do decide to load them up, I'm still safe because the extent size increase as the object grows. I'm don't advocate of autoallocate for everything because I can't determinethe next extent, but this is one place where it's better than uniform. I also have some uniform LMTs for larger tables that I migrate to when tables get too big. Steve - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, April 03, 2003 11:33 AM Totally agree with Connor. Just to add a comment to his note. A usage model recommended for UNIFORM vs. AUTOALLOCATE follows: If you know the data volume and growth of your segments and they are predictable, then use UNIFORM. If you are completely in the dark with: 1) How much data is going to be persisted in the segments? 2) What growth patterns the segments are going to exhibit? Then use AUTOALLOCATE. Of course, if you do change your mind, after the fact, you can use the MOVE command to the tablespace of choice with the extent allocation of your choice. Cheers, Gaja --- Connor McDonald [EMAIL PROTECTED] wrote: I don't believe that was the case. auto and uniform in all of the (admittedly rudimentary
Re: Autoallocate vs Uniform extent performance
rumor hath it (as I've never actually had an object hit that high a number) that when you exceed 4K extents it's time to resize. This came from one of the instructors in Oracle University, one who is well-known to actually have more than a clue. He said this at the Data Internals class, before 9i was released. I have not seen his test results but I do know that tests done with DMTs have shown that large numbers of extents (I believe Kevin Loney tested with 60K extents, and I vaguely remember a conversation with Cary where he said he had also tested large numbers) are a problem during operations that empty a lot of extents (think large deletes) because of thrashing on FET$ and UET$. Since an LMT doesn't access those tables by design, I would think that that problem goes away. --- Richard Foote [EMAIL PROTECTED] wrote: Just a general question to everyone (and one I've asked a few times before in different forums). If we're talking LMT, how many extents are too many ? Assuming no quotas (which does introduce some known issues) at what point do you say that your standard uniform size of 64K has generated too many extents and that performance is noticeably suffering to the level where the inconvenience of a table reorg is warranted ? When has anyone reached the point with an object in a LMT whereby performance has been an issue and by *only* reducing the number of extents, you've said phew, that's better ? If seen many suggestions on standard uniform sizes that are somewhat similar to those used by autoallocate, most of which have a scale of magnitude around the 100 mark. These always made sense with DMT so are we trying to implement outdated recommendations to LMTs ? Does hitting the 100 extent mark warrant such concern and need to change our extent size ? My little brain usually works best with smaller numbers and I can gauge the level of growth somewhat easier with smaller number of extents but is that a justification for being so picky with what extent size an object should have ? Some dba_ views will take longer to get me details I'm after but is that sufficient justification for being so picky with extent sizes ? Curious in anyone's thoughts as I would hate to think we have a myth a happening ... Richard - Original Message - Date: Friday, April 4, 2003 9:18 am I totally agree Gaja. I support a SAP BW system and they create tables with a 100 of partitionsand only load 24 of them. With autoallocate, most of them are small (64k) and space is not wasted. If they do decide to load them up, I'm still safe because the extent size increase as the object grows. I'm don't advocate of autoallocate for everything because I can't determinethe next extent, but this is one place where it's better than uniform. I also have some uniform LMTs for larger tables that I migrate to when tables get too big. Steve - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, April 03, 2003 11:33 AM Totally agree with Connor. Just to add a comment to his note. A usage model recommended for UNIFORM vs. AUTOALLOCATE follows: If you know the data volume and growth of your segments and they are predictable, then use UNIFORM. If you are completely in the dark with: 1) How much data is going to be persisted in the segments? 2) What growth patterns the segments are going to exhibit? Then use AUTOALLOCATE. Of course, if you do change your mind, after the fact, you can use the MOVE command to the tablespace of choice with the extent allocation of your choice. Cheers, Gaja --- Connor McDonald [EMAIL PROTECTED] wrote: I don't believe that was the case. auto and uniform in all of the (admittedly rudimentary and subjective) tests I've done appear the same in terms of performance. I prefer uniform purely for the reasons of: - more thorough elimination of fragmentation - predictability of next extent sizes hth connor --- [EMAIL PROTECTED] wrote: Hi all Some time ago there was a discussion about the use of the different extent management types and that if my memory serves me that there was a perception that Auto allocate extents had some performance issues against Uniform extents. Was this correct and can it be backed up with some definitive testing, has someone done a whitepaper??? Cheers -- = Peter McLarty E-mail: [EMAIL PROTECTED] Technical ConsultantWWW: http://www.mincom.com APAC Technical Services Phone: +61 (0)7 3303 3461 Brisbane, AustraliaMobile: +61 (0)402 094 238
Re: Autoallocate vs Uniform extent performance
as has been pointed out to me privately (and it's really okay to correct me publicly!), it is not delete that would release blocks but truncate or drop. Resolution: do not post before at least two cups of coffee. My apologies to anyone I might have confused. --- Rachel Carmichael [EMAIL PROTECTED] wrote: rumor hath it (as I've never actually had an object hit that high a number) that when you exceed 4K extents it's time to resize. This came from one of the instructors in Oracle University, one who is well-known to actually have more than a clue. He said this at the Data Internals class, before 9i was released. I have not seen his test results but I do know that tests done with DMTs have shown that large numbers of extents (I believe Kevin Loney tested with 60K extents, and I vaguely remember a conversation with Cary where he said he had also tested large numbers) are a problem during operations that empty a lot of extents (think large deletes) because of thrashing on FET$ and UET$. Since an LMT doesn't access those tables by design, I would think that that problem goes away. --- Richard Foote [EMAIL PROTECTED] wrote: Just a general question to everyone (and one I've asked a few times before in different forums). If we're talking LMT, how many extents are too many ? Assuming no quotas (which does introduce some known issues) at what point do you say that your standard uniform size of 64K has generated too many extents and that performance is noticeably suffering to the level where the inconvenience of a table reorg is warranted ? When has anyone reached the point with an object in a LMT whereby performance has been an issue and by *only* reducing the number of extents, you've said phew, that's better ? If seen many suggestions on standard uniform sizes that are somewhat similar to those used by autoallocate, most of which have a scale of magnitude around the 100 mark. These always made sense with DMT so are we trying to implement outdated recommendations to LMTs ? Does hitting the 100 extent mark warrant such concern and need to change our extent size ? My little brain usually works best with smaller numbers and I can gauge the level of growth somewhat easier with smaller number of extents but is that a justification for being so picky with what extent size an object should have ? Some dba_ views will take longer to get me details I'm after but is that sufficient justification for being so picky with extent sizes ? Curious in anyone's thoughts as I would hate to think we have a myth a happening ... Richard - Original Message - Date: Friday, April 4, 2003 9:18 am I totally agree Gaja. I support a SAP BW system and they create tables with a 100 of partitionsand only load 24 of them. With autoallocate, most of them are small (64k) and space is not wasted. If they do decide to load them up, I'm still safe because the extent size increase as the object grows. I'm don't advocate of autoallocate for everything because I can't determinethe next extent, but this is one place where it's better than uniform. I also have some uniform LMTs for larger tables that I migrate to when tables get too big. Steve - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, April 03, 2003 11:33 AM Totally agree with Connor. Just to add a comment to his note. A usage model recommended for UNIFORM vs. AUTOALLOCATE follows: If you know the data volume and growth of your segments and they are predictable, then use UNIFORM. If you are completely in the dark with: 1) How much data is going to be persisted in the segments? 2) What growth patterns the segments are going to exhibit? Then use AUTOALLOCATE. Of course, if you do change your mind, after the fact, you can use the MOVE command to the tablespace of choice with the extent allocation of your choice. Cheers, Gaja --- Connor McDonald [EMAIL PROTECTED] wrote: I don't believe that was the case. auto and uniform in all of the (admittedly rudimentary and subjective) tests I've done appear the same in terms of performance. I prefer uniform purely for the reasons of: - more thorough elimination of fragmentation - predictability of next extent sizes hth connor --- [EMAIL PROTECTED] wrote: Hi all Some time ago there was a discussion about the use of the different extent management types and that if my memory serves me that there was a perception that Auto allocate extents had some performance issues against Uniform extents. Was this correct and can it be
RE: Autoallocate vs Uniform extent performance
You've also been to Scott Gosset's classes in NYC in the year 2001? He had a whole series of those classes. It was a very rare event indeed, given that he lives in Peoria, Il. I was very, very impressed with him and his knowledge. He warmly recommended Steve Adams book, which has really surprised me because I know from the private communication that Steve has never worked for oracle and has never been granted access to the oracle source code. The two classes that I missed were OPS classes given by Scott Heisey (a friend of mine was able to go and he claims that Scott Heisey is a genuine cornucopia of the OPS information) and the class given by one and only Cary Millsap. -Original Message- Sent: Friday, April 04, 2003 8:01 AM To: Multiple recipients of list ORACLE-L rumor hath it (as I've never actually had an object hit that high a number) that when you exceed 4K extents it's time to resize. This came from one of the instructors in Oracle University, one who is well-known to actually have more than a clue. He said this at the Data Internals class, before 9i was released. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gogala, Mladen 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: RE: Autoallocate vs Uniform extent performance
I think you ought to refine your test: Create two tables at one extent each, then alternately allocate one extent to each table until you get to a very large number of extents. THEN try dropping one of them. Remember to set tablespace quotas for the user creating the table. It still won't scare you off, by the way, so you don't have to do it. Check with Connor which version of Oracle introduced the modification that updates tsq$ just once one the drop, rather than once per extent as this does make a difference. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html UK___April 8th UK___April 22nd Denmark__May 21-23rd USA_(FL)_May 2nd Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK_(Manchester)_May Estonia___June (provisional) USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: 04 April 2003 09:23 Hi Pete, Stop using my favourite answer :) I'm not suggesting this is particularly scientific but here's a quick test on dropping a moderate number of extents (9.2 on XP): SQL create tablespace biggish 2 datafile 'c:\bowie\biggish01.dbf' size 2000M 3 uniform size 16K; Tablespace created. SQL set timing on SQL create table lots_of_extents (x number) 2 storage (initial 1990M) 3 tablespace biggish; Table created. Elapsed: 00:00:49.06 SQL select count(*) from user_extents where segment_name = 'LOTS_OF_EXTENTS'; COUNT(*) -- 127423 Elapsed: 00:00:04.01 SQL drop table lots_of_extents; Table dropped. Elapsed: 00:00:06.08 Based on the above, I could drop a table with 1,000,000 extents in under 1 minute. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: Autoallocate vs Uniform extent performance
Actually it was Bethesda a few years earlier but yes, Scott Gossett's classes. He is indeed impressive. I took Scott Heisey's class on 8i new features in Chicago in '96 or '97. Also a great instructor. I've never taken a class with Cary (I'm hoping to kinda change that at the Hotsos Symposium next year) but I've seen him present and spent time talking with him These guys are why I think I know nothing! Rachel --- Gogala, Mladen [EMAIL PROTECTED] wrote: You've also been to Scott Gosset's classes in NYC in the year 2001? He had a whole series of those classes. It was a very rare event indeed, given that he lives in Peoria, Il. I was very, very impressed with him and his knowledge. He warmly recommended Steve Adams book, which has really surprised me because I know from the private communication that Steve has never worked for oracle and has never been granted access to the oracle source code. The two classes that I missed were OPS classes given by Scott Heisey (a friend of mine was able to go and he claims that Scott Heisey is a genuine cornucopia of the OPS information) and the class given by one and only Cary Millsap. -Original Message- Sent: Friday, April 04, 2003 8:01 AM To: Multiple recipients of list ORACLE-L rumor hath it (as I've never actually had an object hit that high a number) that when you exceed 4K extents it's time to resize. This came from one of the instructors in Oracle University, one who is well-known to actually have more than a clue. He said this at the Data Internals class, before 9i was released. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gogala, Mladen 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). __ Do you Yahoo!? Yahoo! Tax Center - File online, calculators, forms, and more http://tax.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael 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: Autoallocate vs Uniform extent performance
Rachel, At a recent past job, under 8.1.6 on Win2k we had tables with out-of-line CLOB segments of 30,000 extents (1MB each). Every month we dropped one to make room for another (6 months of CLOB documents online). It always just took a few seconds for the drop. These were in DMTs. Later we switched servers and I changed to LMTs of 100MB Uniform Extents for the CLOB segments. Going from 30,000 to 300 extents for those hulks made no noticeable difference in query or interMedia indexing performance, nor did it noticeably change the time it took to drop the tables. Here at AISD, our student information database (SASI, for those in Education who know this 3rd party app) has over 47,000 tables and 70,000 indexes (typical abysmal design for a 3rd party app, eh?), many of them empty or with very few rows. A few months ago I rebuilt it under 8.1.7.4.6 (Win2k - it was previously at 8.1.7.0.0) with LMTs of 8KB Uniform Extents to save space. Surprisingly, only 40 or so segments have over 1000 extents. One, a consolidated Student table, has a little over 10,000 extents. We've noticed no problem at all with performance, etc. I've not been concerned about extent counts for several years now, and I've seen nothing convincing that I should be. Maybe I've just not hit the situation where it matters. That is not to say that extents don't matter, but it's only if they obey the stupid directives of uninformed duhvelopers, such as those of our 3rd party Financials system, where they used PctIncrease of 50. Like children and dogs, there are no bad extents, just bad designers.;-) Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] Rachel Carmichael [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L o.com[EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: Re: Autoallocate vs Uniform extent performance 04/04/2003 07:01 AM Please respond to ORACLE-L rumor hath it (as I've never actually had an object hit that high a number) that when you exceed 4K extents it's time to resize. This came from one of the instructors in Oracle University, one who is well-known to actually have more than a clue. He said this at the Data Internals class, before 9i was released. I have not seen his test results but I do know that tests done with DMTs have shown that large numbers of extents (I believe Kevin Loney tested with 60K extents, and I vaguely remember a conversation with Cary where he said he had also tested large numbers) are a problem during operations that empty a lot of extents (think large deletes) because of thrashing on FET$ and UET$. Since an LMT doesn't access those tables by design, I would think that that problem goes away. -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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: Autoallocate vs Uniform extent performance
[EMAIL PROTECTED] wrote: These guys are why I think I know nothing! yes my goddess, what ever you say my goddess.;-) you are the reason i think i don't know anything. so guess how far behind those guys i am.;-) -- -- Bill Shrek Thater ORACLE DBA [EMAIL PROTECTED] Om mani padme hum. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: bill thater 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: Autoallocate vs Uniform extent performance
Jack, I've got no experience with out-of-line CLOB segments, so I don't know if that changes how the drop would work. As for the maximum number of extents, that's what I was told. Not that it was necessarily don't ever do that, just something to think about. Rachel --- [EMAIL PROTECTED] wrote: Rachel, At a recent past job, under 8.1.6 on Win2k we had tables with out-of-line CLOB segments of 30,000 extents (1MB each). Every month we dropped one to make room for another (6 months of CLOB documents online). It always just took a few seconds for the drop. These were in DMTs. Later we switched servers and I changed to LMTs of 100MB Uniform Extents for the CLOB segments. Going from 30,000 to 300 extents for those hulks made no noticeable difference in query or interMedia indexing performance, nor did it noticeably change the time it took to drop the tables. Here at AISD, our student information database (SASI, for those in Education who know this 3rd party app) has over 47,000 tables and 70,000 indexes (typical abysmal design for a 3rd party app, eh?), many of them empty or with very few rows. A few months ago I rebuilt it under 8.1.7.4.6 (Win2k - it was previously at 8.1.7.0.0) with LMTs of 8KB Uniform Extents to save space. Surprisingly, only 40 or so segments have over 1000 extents. One, a consolidated Student table, has a little over 10,000 extents. We've noticed no problem at all with performance, etc. I've not been concerned about extent counts for several years now, and I've seen nothing convincing that I should be. Maybe I've just not hit the situation where it matters. That is not to say that extents don't matter, but it's only if they obey the stupid directives of uninformed duhvelopers, such as those of our 3rd party Financials system, where they used PctIncrease of 50. Like children and dogs, there are no bad extents, just bad designers.;-) Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] Rachel Carmichael [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L o.com [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: Re: Autoallocate vs Uniform extent performance 04/04/2003 07:01 AM Please respond to ORACLE-L rumor hath it (as I've never actually had an object hit that high a number) that when you exceed 4K extents it's time to resize. This came from one of the instructors in Oracle University, one who is well-known to actually have more than a clue. He said this at the Data Internals class, before 9i was released. I have not seen his test results but I do know that tests done with DMTs have shown that large numbers of extents (I believe Kevin Loney tested with 60K extents, and I vaguely remember a conversation with Cary where he said he had also tested large numbers) are a problem during operations that empty a lot of extents (think large deletes) because of thrashing on FET$ and UET$. Since an LMT doesn't access those tables by design, I would think that that problem goes away. -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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
Re: Autoallocate vs Uniform extent performance
I don't believe that was the case. auto and uniform in all of the (admittedly rudimentary and subjective) tests I've done appear the same in terms of performance. I prefer uniform purely for the reasons of: - more thorough elimination of fragmentation - predictability of next extent sizes hth connor --- [EMAIL PROTECTED] wrote: Hi all Some time ago there was a discussion about the use of the different extent management types and that if my memory serves me that there was a perception that Auto allocate extents had some performance issues against Uniform extents. Was this correct and can it be backed up with some definitive testing, has someone done a whitepaper??? Cheers -- = Peter McLarty E-mail: [EMAIL PROTECTED] Technical ConsultantWWW: http://www.mincom.com APAC Technical Services Phone: +61 (0)7 3303 3461 Brisbane, AustraliaMobile: +61 (0)402 094 238 Facsimile: +61 (0)7 3303 3048 = A great pleasure in life is doing what people say you cannot do. - Walter Bagehot (1826-1877 British Economist) = Mincom The People, The Experience, The Vision = This transmission is for the intended addressee only and is confidential information. If you have received this transmission in error, please delete it and notify the sender. The contents of this e-mail are the opinion of the writer only and are not endorsed by the Mincom Group of companies unless expressly stated otherwise. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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). = Connor McDonald web: http://www.oracledba.co.uk web: http://www.oaktable.net email: [EMAIL PROTECTED] GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day __ Yahoo! Plus For a better Internet experience http://www.yahoo.co.uk/btoffer -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Connor=20McDonald?= 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: Autoallocate vs Uniform extent performance
Totally agree with Connor. Just to add a comment to his note. A usage model recommended for UNIFORM vs. AUTOALLOCATE follows: If you know the data volume and growth of your segments and they are predictable, then use UNIFORM. If you are completely in the dark with: 1) How much data is going to be persisted in the segments? 2) What growth patterns the segments are going to exhibit? Then use AUTOALLOCATE. Of course, if you do change your mind, after the fact, you can use the MOVE command to the tablespace of choice with the extent allocation of your choice. Cheers, Gaja --- Connor McDonald [EMAIL PROTECTED] wrote: I don't believe that was the case. auto and uniform in all of the (admittedly rudimentary and subjective) tests I've done appear the same in terms of performance. I prefer uniform purely for the reasons of: - more thorough elimination of fragmentation - predictability of next extent sizes hth connor --- [EMAIL PROTECTED] wrote: Hi all Some time ago there was a discussion about the use of the different extent management types and that if my memory serves me that there was a perception that Auto allocate extents had some performance issues against Uniform extents. Was this correct and can it be backed up with some definitive testing, has someone done a whitepaper??? Cheers -- = Peter McLarty E-mail: [EMAIL PROTECTED] Technical ConsultantWWW: http://www.mincom.com APAC Technical Services Phone: +61 (0)7 3303 3461 Brisbane, AustraliaMobile: +61 (0)402 094 238 Facsimile: +61 (0)7 3303 3048 = A great pleasure in life is doing what people say you cannot do. - Walter Bagehot (1826-1877 British Economist) = Mincom The People, The Experience, The Vision = This transmission is for the intended addressee only and is confidential information. If you have received this transmission in error, please delete it and notify the sender. The contents of this e-mail are the opinion of the writer only and are not endorsed by the Mincom Group of companies unless expressly stated otherwise. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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). = Connor McDonald web: http://www.oracledba.co.uk web: http://www.oaktable.net email: [EMAIL PROTECTED] GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day __ Yahoo! Plus For a better Internet experience http://www.yahoo.co.uk/btoffer -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Connor=20McDonald?= 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). = __ Do you Yahoo!? Yahoo! Tax Center - File online, calculators, forms, and more http://tax.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gaja Krishna Vaidyanatha 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
Re: Autoallocate vs Uniform extent performance
I totally agree Gaja. I support a SAP BW system and they create tables with a 100 of partitions and only load 24 of them. With autoallocate, most of them are small (64k) and space is not wasted. If they do decide to load them up, I'm still safe because the extent size increase as the object grows. I'm don't advocate of autoallocate for everything because I can't determine the next extent, but this is one place where it's better than uniform. I also have some uniform LMTs for larger tables that I migrate to when tables get too big. Steve - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, April 03, 2003 11:33 AM Totally agree with Connor. Just to add a comment to his note. A usage model recommended for UNIFORM vs. AUTOALLOCATE follows: If you know the data volume and growth of your segments and they are predictable, then use UNIFORM. If you are completely in the dark with: 1) How much data is going to be persisted in the segments? 2) What growth patterns the segments are going to exhibit? Then use AUTOALLOCATE. Of course, if you do change your mind, after the fact, you can use the MOVE command to the tablespace of choice with the extent allocation of your choice. Cheers, Gaja --- Connor McDonald [EMAIL PROTECTED] wrote: I don't believe that was the case. auto and uniform in all of the (admittedly rudimentary and subjective) tests I've done appear the same in terms of performance. I prefer uniform purely for the reasons of: - more thorough elimination of fragmentation - predictability of next extent sizes hth connor --- [EMAIL PROTECTED] wrote: Hi all Some time ago there was a discussion about the use of the different extent management types and that if my memory serves me that there was a perception that Auto allocate extents had some performance issues against Uniform extents. Was this correct and can it be backed up with some definitive testing, has someone done a whitepaper??? Cheers -- = Peter McLarty E-mail: [EMAIL PROTECTED] Technical ConsultantWWW: http://www.mincom.com APAC Technical Services Phone: +61 (0)7 3303 3461 Brisbane, AustraliaMobile: +61 (0)402 094 238 Facsimile: +61 (0)7 3303 3048 = A great pleasure in life is doing what people say you cannot do. - Walter Bagehot (1826-1877 British Economist) = Mincom The People, The Experience, The Vision = This transmission is for the intended addressee only and is confidential information. If you have received this transmission in error, please delete it and notify the sender. The contents of this e-mail are the opinion of the writer only and are not endorsed by the Mincom Group of companies unless expressly stated otherwise. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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). = Connor McDonald web: http://www.oracledba.co.uk web: http://www.oaktable.net email: [EMAIL PROTECTED] GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day __ Yahoo! Plus For a better Internet experience http://www.yahoo.co.uk/btoffer -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Connor=20McDonald?= 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: Autoallocate vs Uniform extent performance
Just a general question to everyone (and one I've asked a few times before in different forums). If we're talking LMT, how many extents are too many ? Assuming no quotas (which does introduce some known issues) at what point do you say that your standard uniform size of 64K has generated too many extents and that performance is noticeably suffering to the level where the inconvenience of a table reorg is warranted ? When has anyone reached the point with an object in a LMT whereby performance has been an issue and by *only* reducing the number of extents, you've said phew, that's better ? If seen many suggestions on standard uniform sizes that are somewhat similar to those used by autoallocate, most of which have a scale of magnitude around the 100 mark. These always made sense with DMT so are we trying to implement outdated recommendations to LMTs ? Does hitting the 100 extent mark warrant such concern and need to change our extent size ? My little brain usually works best with smaller numbers and I can gauge the level of growth somewhat easier with smaller number of extents but is that a justification for being so picky with what extent size an object should have ? Some dba_ views will take longer to get me details I'm after but is that sufficient justification for being so picky with extent sizes ? Curious in anyone's thoughts as I would hate to think we have a myth a happening ... Richard - Original Message - Date: Friday, April 4, 2003 9:18 am I totally agree Gaja. I support a SAP BW system and they create tables with a 100 of partitionsand only load 24 of them. With autoallocate, most of them are small (64k) and space is not wasted. If they do decide to load them up, I'm still safe because the extent size increase as the object grows. I'm don't advocate of autoallocate for everything because I can't determinethe next extent, but this is one place where it's better than uniform. I also have some uniform LMTs for larger tables that I migrate to when tables get too big. Steve - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, April 03, 2003 11:33 AM Totally agree with Connor. Just to add a comment to his note. A usage model recommended for UNIFORM vs. AUTOALLOCATE follows: If you know the data volume and growth of your segments and they are predictable, then use UNIFORM. If you are completely in the dark with: 1) How much data is going to be persisted in the segments? 2) What growth patterns the segments are going to exhibit? Then use AUTOALLOCATE. Of course, if you do change your mind, after the fact, you can use the MOVE command to the tablespace of choice with the extent allocation of your choice. Cheers, Gaja --- Connor McDonald [EMAIL PROTECTED] wrote: I don't believe that was the case. auto and uniform in all of the (admittedly rudimentary and subjective) tests I've done appear the same in terms of performance. I prefer uniform purely for the reasons of: - more thorough elimination of fragmentation - predictability of next extent sizes hth connor --- [EMAIL PROTECTED] wrote: Hi all Some time ago there was a discussion about the use of the different extent management types and that if my memory serves me that there was a perception that Auto allocate extents had some performance issues against Uniform extents. Was this correct and can it be backed up with some definitive testing, has someone done a whitepaper??? Cheers -- = Peter McLarty E-mail: [EMAIL PROTECTED] Technical ConsultantWWW: http://www.mincom.com APAC Technical Services Phone: +61 (0)7 3303 3461 Brisbane, AustraliaMobile: +61 (0)402 094 238 Facsimile: +61 (0)7 3303 3048 = A great pleasure in life is doing what people say you cannot do. - Walter Bagehot (1826-1877 British Economist) = Mincom The People, The Experience, The Vision = This transmission is for the intended addressee only and is confidential information. If you have received this transmission in error, please delete it and notify the sender. The contents of this e-mail are the opinion of the writer only and are not endorsed by the Mincom Group of companies unless expressly stated otherwise. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com
RE: Autoallocate vs Uniform extent performance
Title: RE: Autoallocate vs Uniform extent performance I get the impression from what I've read on this list and elsewhere that in general one shouldn't worry about the number of extents in a table, at least for full table scans. (It can become a problem when dropping a table - at least in a dictionary managed tablespace - though as I know from experience.) For locally managed tablespaces, the only word of caution I've read is from the Ixora website Planning extents http://www.ixora.com.au/tips/creation/extents.htm ... we recommend that the number of extents per segment in locally managed tablespaces be limited to the number of rows that can be accommodated in the extent map within the segment header block - that is, approximately (db_block_size / 16) - 7. -Original Message- From: Richard Foote [mailto:[EMAIL PROTECTED]] Just a general question to everyone (and one I've asked a few times before in different forums). If we're talking LMT, how many extents are too many ? ...
RE: Autoallocate vs Uniform extent performance
BD Did you expect any other answer than it depends? :) Seriously, we've seen a few thousand extents without any problems in later releases. We've also heard of customers not noticing things had gone horribly adrift from what they thought and end up with hundreds of thousands of extents. No empirical evidence of performance problems with said hundreds of thousands of extents, but boy that drop command can be an absolute mother if you try to fix it! :) Pete Controlling developers is like herding cats. Kevin Loney, Oracle DBA Handbook Oh no, it's not. It's much harder than that! Bruce Pihlamae, long-term Oracle DBA -Original Message- Sent: Thursday, April 03, 2003 5:28 PM To: Multiple recipients of list ORACLE-L Just a general question to everyone (and one I've asked a few times before in different forums). If we're talking LMT, how many extents are too many ? Assuming no quotas (which does introduce some known issues) at what point do you say that your standard uniform size of 64K has generated too many extents and that performance is noticeably suffering to the level where the inconvenience of a table reorg is warranted ? When has anyone reached the point with an object in a LMT whereby performance has been an issue and by *only* reducing the number of extents, you've said phew, that's better ? If seen many suggestions on standard uniform sizes that are somewhat similar to those used by autoallocate, most of which have a scale of magnitude around the 100 mark. These always made sense with DMT so are we trying to implement outdated recommendations to LMTs ? Does hitting the 100 extent mark warrant such concern and need to change our extent size ? My little brain usually works best with smaller numbers and I can gauge the level of growth somewhat easier with smaller number of extents but is that a justification for being so picky with what extent size an object should have ? Some dba_ views will take longer to get me details I'm after but is that sufficient justification for being so picky with extent sizes ? Curious in anyone's thoughts as I would hate to think we have a myth a happening ... Richard - Original Message - Date: Friday, April 4, 2003 9:18 am I totally agree Gaja. I support a SAP BW system and they create tables with a 100 of partitionsand only load 24 of them. With autoallocate, most of them are small (64k) and space is not wasted. If they do decide to load them up, I'm still safe because the extent size increase as the object grows. I'm don't advocate of autoallocate for everything because I can't determinethe next extent, but this is one place where it's better than uniform. I also have some uniform LMTs for larger tables that I migrate to when tables get too big. Steve - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, April 03, 2003 11:33 AM Totally agree with Connor. Just to add a comment to his note. A usage model recommended for UNIFORM vs. AUTOALLOCATE follows: If you know the data volume and growth of your segments and they are predictable, then use UNIFORM. If you are completely in the dark with: 1) How much data is going to be persisted in the segments? 2) What growth patterns the segments are going to exhibit? Then use AUTOALLOCATE. Of course, if you do change your mind, after the fact, you can use the MOVE command to the tablespace of choice with the extent allocation of your choice. Cheers, Gaja --- Connor McDonald [EMAIL PROTECTED] wrote: I don't believe that was the case. auto and uniform in all of the (admittedly rudimentary and subjective) tests I've done appear the same in terms of performance. I prefer uniform purely for the reasons of: - more thorough elimination of fragmentation - predictability of next extent sizes hth connor --- [EMAIL PROTECTED] wrote: Hi all Some time ago there was a discussion about the use of the different extent management types and that if my memory serves me that there was a perception that Auto allocate extents had some performance issues against Uniform extents. Was this correct and can it be backed up with some definitive testing, has someone done a whitepaper??? Cheers -- = Peter McLarty E-mail: [EMAIL PROTECTED] Technical ConsultantWWW: http://www.mincom.com APAC Technical Services Phone: +61 (0)7 3303 3461 Brisbane, AustraliaMobile: +61 (0)402 094 238 Facsimile: +61 (0)7 3303 3048 = A great pleasure in life is doing what people say you cannot do. - Walter Bagehot (1826-1877 British Economist)
