Re: Extents size.

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 <wisernet100_at_yahoo.com> 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 <cspence_at_FuelSpot.com> 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_at_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_at_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_at_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_at_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_at_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_at_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_at_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_at_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_at_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_at_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_at_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_at_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_at_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_at_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_at_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).