From charliem@mwh.com Wed, 03 Oct 2001 07:55:48 -0700 From: "Charlie Mengler" Date: Wed, 03 Oct 2001 07:55:48 -0700 Subject: Re: Extents size. In-Reply-To: Message-ID: MIME-Version: 1.0 Content-Type: text/plain Does the 505 extent "limit" apply to the whole of a partitioned table or to the number of extents per partition? For example if I had a table wth 371 partitions (53 weeks per year * 7 years) to hold invoice data for tax purposes, do the number of extents per partition need to be kept at 1 to avoid exceeding the 505 extent value on the table as a whole? Christopher Spence wrote: > > LMT's have their own problems as well though. > > Using LMT's, the bitmap on the tablespace is only used to manage free space, > used space is in the segment header of the segment which represents the > extents. Therefore, to do a query of DBA_EXTENTS you can hit all segment > headers (of all tables and indexes in the database) as well as all the > additional extent maps. This can cause a very large amount of physical > reads in comparison to the dictionary-managed extents. > > Although LMT's are great, they are not perfect and do have their share of > concerns. And like Connor pointed out, for a 8K block size, it is highly > suggested to keep the extents to 505 which would only create a single block > for the segment header, anything further than that will create additional > blocks for the segment. Remember, DBA_EXTENTS has to hit all the segment > headers (be it one or two) as well as the extent maps. > > The formula he used is (blocksize / 16)-7, which determine how many extents, > can fit in the segment header of a LMT tablespace. > > "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: Tuesday, October 02, 2001 5:55 PM > To: Multiple recipients of list ORACLE-L > > True - but we're all using LMT's now anyway aren't we > ? > > :-) > > --- Rachel Carmichael wrote: > > well, only when you are deleting massive amounts of > > data or truncating > > with drop storage.. then there is an impact because > > of the hits on the > > dictionary tables. > > > > but basically yes... I've been told by various > > Oracle employees that up > > to 4096 extents cause no problem whatsoever. > > > > > > --- Christopher Spence wrote: > > > 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: truc@nguyen.as > > > > > > 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: ListGuru@fatcity.com (note EXACT spelling of > > 'ListGuru') and in > > > the message BODY, include a line containing: UNSUB > > ORACLE-L > > > (or the name of mailing list you want to be > > removed from). You may > > > also send the HELP command for other information > > (like subscribing). > > > -- > > > Please see the official ORACLE-L FAQ: > > http://www.orafaq.com > > > -- > > > Author: Wong, Bing > > > INET: bing.wong@IngramMicro.com > > > > > > 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: ListGuru@fatcity.com (note EXACT spelling of > > 'ListGuru') and in > > > the message BODY, include a line containing: UNSUB > > ORACLE-L > > > (or the name of mailing list you want to be > > removed from). You may > > > also send the HELP command for other information > > (like subscribing). > > > -- > > > Please see the official ORACLE-L FAQ: > > http://www.orafaq.com > > > -- > > > Author: Christopher Spence > > > INET: cspence@FuelSpot.com > > > > > > 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: ListGuru@fatcity.com (note EXACT spelling of > > 'ListGuru') and in > > > the message BODY, include a line containing: UNSUB > > ORACLE-L > > > (or the name of mailing list you want to be > > removed from). You may > > > also send the HELP command for other information > > (like subscribing). > > > > > > __________________________________________________ > > Do You Yahoo!? > > Listen to your Yahoo! Mail messages from any phone. > > http://phone.yahoo.com > > -- > > Please see the official ORACLE-L FAQ: > > http://www.orafaq.com > > -- > > Author: Rachel Carmichael > > INET: wisernet100@yahoo.com > > > > 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: ListGuru@fatcity.com (note EXACT spelling of > > 'ListGuru') and in > > the message BODY, include a line containing: UNSUB > > ORACLE-L > > (or the name of mailing list you want to be removed > > from). You may > > also send the HELP command for other information > > (like subscribing). > > ===== > Connor McDonald > http://www.oracledba.co.uk (mirrored at > http://www.oradba.freeserve.co.uk) > > "Some days you're the pigeon, some days you're the statue" > > ____________________________________________________________ > Do You Yahoo!? > Get your free @yahoo.co.uk address at http://mail.yahoo.co.uk > or your free @yahoo.ie address at http://mail.yahoo.ie > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: =?iso-8859-1?q?Connor=20McDonald?= > INET: hamcdc@yahoo.co.uk > > 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: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Christopher Spence > INET: cspence@FuelSpot.com > > 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: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). -- Charlie Mengler Maintenance Warehouse charliem@mwh.com 10641 Scripps Summit Ct. 858-831-2229 San Diego, CA 92131 United WE Stand! Justice WILL Be Served! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Charlie Mengler INET: charliem@mwh.com 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: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).