First I've heard from Ferenc for awhile, even if it is 2 years old.
Using extreme cases like this really doesn't make for a good example. I know an instructor that dropped/recreated/imported and entire database because the 'drop table' ran for 2 days with no end in sight. Extents were 300,000+. I suspect that that the graph of time vs. number of extents in data dictionary operations has a rather steep curve on the right side. It would be interesting to see where the curve breaks, as it's not likely a linear rise. Of course, if you're using LMT's, this exercise is a waste of time. :) Jared On Tuesday 02 October 2001 15:16, Mohammad Rafiq wrote: > Further to my previous message, I am reproudcing an email of a list member > on this subject. It is really interesting to read it.. > > > Reply-To: Ferenc Mantfeld <[EMAIL PROTECTED]> > To: "oracle list" <[EMAIL PROTECTED]> > Date: Tue, 20 Jul 1999 16:25:15 -0500 > > Hi all > > We know that Oracle often gives us enough rope to hang > ourselves with. In > Kevin Loney's presentation at IOUG this year, he brought up > an interesting > point about unlimited extents being one of the more > boneheaded idea Oracle > came up with, so I decided to conduct my own experiment : > > Environment : > HP V2500, 16 CPU's running at 450 Mhz, 8 GB Ram, 2.5 GBPS > fiber channel port > connection, running Oracle 7.3.4.4 on HP-UX 11.0. > DB_BLOCK_SIZE=8192 > In other words, one real kick-ass machine > > One table : > > 1,2 million rows in a single extent of 120 MB. > > Export this table. > Set timing on > Drop it : 3.5 seconds. > > Recreate the table as follows : > Create table my_tab ( ......) storage (initial 16 k next 8 k > pctincrease 0 > minextetns 1 maxextents 30000 ) pctfree 5 > tablespace my_own_persoanl_ts ; > Make a note of the datafile numbers for your tablespace, you > will need this > to determine how far you are from being done : > > import your old table (ignore=y, indexes=no), check your > alert log , when > you see : > ORA-1631: max # extents 30000 reached in table/cluster > DBA_USER.S_ORG_PROD > then ps_ef | grep -i imp > and kill the process. > > Now you have a 30,000 extent table. > First turn on tracing for tkprof : > SQL > alter session set sql_trace true ; > session altered > SQL > > > Now drop the table : > SBPRD-DBA_USER> drop table s_org_prod; > > table dropped > Elapsed: 01:53:42.89 > > SQL > > > In the meantime, from another session, logged in as SYS > You know that this is the only table that lives in your > particular datafile > , so : > SQL > select count (*) from uet$ where file#=5 ; > > COUNT(*) > ---------- > 26638 > SBPRD-DBA_USER> / > > COUNT(*) > ---------- > 26254 > > (so you can see the allocated user extent table decreasing). > > Bottom line is that from the recursive DML being done on uet$ > and fet$, the > process has slowed down from 3.5 seconds to almost two hours, > and we are not > talking a Mickey Mouse machine here. > > Just another reminder that good database design ALWAYS pays > off, and we are > to look at new "benefits" that the rdbms offers us, with > optimistic > skepticism . > > Hope this benchmark is of use to someone out there. If you > like, I can mail > the output result of the tkprof (sys=yes) to you. > > Regards : > > Ferenc Mantfeld > Oracle DBA > > > > > MOHAMMAD RAFIQ > > > > Reply-To: [EMAIL PROTECTED] > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Date: Tue, 02 Oct 2001 13:15:30 -0800 > > Why is that? And would that only count for an object in a dictionary > managed tablespace? Would the time/speed it takes for drops and truncates > really matter as far as performance is concerned? What I mean is who would > set storage specs for objects with the speed it takes for truncates and > drops of that object in mind? It would seem to me that if an object is > getting dropped or truncated that often that speed should be an issue, > there are bigger problems at hand. Guru's correct me if I'm wrong please. > Later, Ivan > > -----Original Message----- > Sent: Tuesday, October 02, 2001 3:56 PM > To: Multiple recipients of list ORACLE-L > > > Any DDL like drop table and truncate table definately take longer with > 10,000 extents than 1 extent. Try it. There was a test result 1 year back > by > > a list member on that. > > Regards > > MOHAMMAD RAFIQ > > > > Reply-To: [EMAIL PROTECTED] > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Date: Tue, 02 Oct 2001 07:55:28 -0800 > > That is completely a myth. There is no notable performance different with > a table with 10,000 extents and one with 1. > > The only problem is when it comes to the bitmaps when dealing with LMT and > cluster when dealing with dictionary managed. When you query the extent > views, or do space management type processes. > > "Do not criticize someone until you walked a mile in their shoes, that way > when you criticize them, you are a mile a way and have their shoes." > > Christopher R. Spence > Oracle DBA > Phone: (978) 322-5744 > Fax: (707) 885-2275 > > Fuelspot > 73 Princeton Street > North, Chelmsford 01863 > > > > -----Original Message----- > Sent: Monday, October 01, 2001 7:15 PM > To: Multiple recipients of list ORACLE-L > > May be it is good practice to keep number of extents to be less than 50, no > matter what the size of extent. > > > > -----Original Message----- > Sent: Monday, October 01, 2001 3:35 PM > To: Multiple recipients of list ORACLE-L > > > Hello, > > I'll do an reorganization of a database (about 140 gigs). Some people say > that it'd be good to use 128K, 4M and 128M extents. I saw somewhere it'd be > 160K, 4M and 160M. Which size do you advice me ? I have also many small > indexes (less than 16K). > > Regards, > > Thanh-truc Nguyen > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Thanh-truc Nguyen > 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: Wong, Bing > 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). > > > _________________________________________________________________ > Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Mohammad Rafiq > 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). > > > _________________________________________________________________ > Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still 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).