Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Locally Managed Tablespaces - Questions.

Re: Locally Managed Tablespaces - Questions.

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 16 Apr 2003 08:09:06 -0800
Message-ID: <F001.00582AAF.20030416080906@fatcity.com>

Richard,

In principle, the strategy used by Oracle to allocate L1 blocks to processes, and data blocks from L1 blocks, should ensure that new allocations of formatted blocks should be kept to a minimum, so there should never be many 16-block chunks above the LHWM, and only one or two L1 blocks that need to be read to identify the chunks.

It strikes me that only in the case of small (1M to 2M) tables will the overhead be a perceptible fraction of the total cost of scanning.

(In practice there may still be a few anomalies to be fixed - such as my bitmap scenario).

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 22nd
____USA_(FL)_May 2nd
____Denmark__May 21-23rd
____Sweden___June
____Finland__September
____Norway___September

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html

____UK_(Manchester)_May x 2
____Estonia___June (provisional)
____Australia_June (provisional)
____USA_(CA, TX)_August

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

> Hi Jonathan,
>
> I kinda concur that the overhead associated with multiple extent
maps is not
> generally going to have a noticeable effect.
>
> However one issue that I believe is much more likely to have an
effect is
> ASSM. Here the overhead can be substantial both terms of the
relative number
> of "segment bitmap" blocks and in terms of having to access them.
Oracle
> will map between 16 blocks (min) and 64 blocks(max) depending on the
size of
> the extents within one bitmap block so there are relatively more of
these
> buggers with smaller extents. This means more "overhead" blocks need
to be
> read when performing a FTS which could impact performance.
>
> But again, whether such effects will be noticeable needs to be
determined.
> The impact could be greater though for extent sizes under 64 blocks.
>
> Food for thought ...
>
> Cheers
>
> Richard
>
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Sent: Wednesday, April 16, 2003 4:34 PM
>
>
> >
> > Connor,
> >
> > I think there's a lot of information (and
> > mis-information) on the market about
> > the 'best' thing to do, but so many systems
> > are so far from optimal that often it doesn't
> > make any difference if you make some change
> > from 'vaguely adequate' to 'perfect'. I think the
> > 'number/size' for extents falls into this category
> > as far as performance is concerned.
> >
> > As far as the extent map block argument is
> > concerned - you only have to consider the
> > side-effect of ASSM, and the need to read
> > L1 blocks to complete an FTS, and you realise
> > that Oracle Corp. thinks a few spare single block
> > reads are pretty irrelevant.
> >
> >
> > The argument I have for having a few tablespaces
> > with the 'relevant' extent size is the baby-sitting
> > argument. If I'm going to have a report telling me
> > about growing objects, I'd like to get one or two
> > lines each week. Not a one-hundred line report
> > each week where I have to add some intelligence
> > at read-time to decide if any of the lines mean
> > anything.
> >
> >
> >
> >
> > 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 22nd
> > ____USA_(FL)_May 2nd
> > ____Denmark__May 21-23rd
> > ____Sweden___June
> > ____Finland__September
> > ____Norway___September
> >
> > Three-day seminar:
> > see http://www.jlcomp.demon.co.uk/seminar.html
> > ____UK_(Manchester)_May x 2
> > ____Estonia___June (provisional)
> > ____Australia_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" <ORACLE-L_at_fatcity.com>
> > Sent: 16 April 2003 06:43
> >
> >
> > > Whilst I agree with Steve's assessment as it applies
> > > to squeezing the n'th degree of efficiency from
> > > Oracle, I'd be keen on hearing from anyone that
> > > suffered any detectable / noticeable performance
> > > degradation from having an excess of extent map blocks
> > > in their system.
> > >
> > > Don't get me wrong, I've no doubt that such a
> > > degradation must exist, but I'd be very surprised if
> > > there are many databases out there that are that well
> > > optimized so that this becomes a relevant issue.
> > >
> > > If that's the case, then I'm back to my argument that
> > > for almost all databases, you can quite happily get
> > > away with a 1m extent size for *every* segment in the
> > > database (assuming a 4G ceiling on segment size)
> > >
> > > Cheers
> > > Connor
> > >
> >
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Jonathan Lewis
> > INET: jonathan_at_jlcomp.demon.co.uk
> >
> > 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: 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.net
> --
> Author: Richard Foote
> INET: richard.foote_at_bigpond.com
>
> 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: 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.net
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

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: 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).
Received on Wed Apr 16 2003 - 11:09:06 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US