Re: Oracle Myths

Niall Litchfield wrote:

> I *think* that what Michael was trying to say was the same as you. I.E. that
> the tablespace should have a non-zero % increase and segments should have a
> zero pctincrease. If one can manage to enforce this I guess this is OK,
> however I've never understood why people who advocate this always seem to
> advocate 1% as the non-zero figure. If one can enforce it it doesn't matter
> what the non-zero figure is so why not just take the defaults. If one can't
> enforce it (like in the real world) then it doesn't matter what the non-zero
> figure is you'll always get fragmentation.
>
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
> *****************************************
> Please include version and platform
> and SQL where applicable
> It makes life easier and increases the
> likelihood of a good answer
>
> ******************************************
> "Daniel Morgan" <dmorgan_at_exesolutions.com> wrote in message
> news:3CE58A31.1D869A97_at_exesolutions.com...
> > Michael Brown wrote:
> >
> > > On 17 May 2002 07:46:19 -0700, gmirsky_at_optonline.net (Gregory N.
> > > Mirsky) wrote:
> > >
> > > >Niall,
> > > >
> > > >I've been trying to follow this thread and quite a few topics have
> > > >been brought up. Can you post an updated list of the "Oracle Myth's"?
> > > >So far I've learned quite a bit of how some of our DBA's have been
> > > >mis-informed or just Bull-S#$@! their way through.
> > > >
> > > >Great thread topic!
> > > >
> > > >Greg
> > > >
> > > >
> > > >"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in
> message news:<3ce21b71$0$8510$ed9e5944_at_reading.news.pipex.net>...
> > > >> Suggested list to be added to, deleted from etc
> > > >>
> > > >> Store your segments in one extent for optimum performance.
> > > >> Space in an index is never reused.
> > > >> Full table scans are always bad: use the index.
> > > >> Seperate tables and indexes for performance reasons.
> > > >> You should always backup the online redo logs to avoid data loss.
> > > >> Buffer cache hit ratio should be as high as possible preferably
> greater than
> > > >> 90%.(from the java tool we use developed in house)
> > > >> Library Cache hit ratio should be greater than 99% if it isn't
> increase the
> > > >> size of the shared pool.(ditto!)
> > > >> Smallest table should be the driving table for hash joins.
> > > >> PCTIncrease should be as small as possible but non-zero to minimize
> > > >> tablespace fragmentation.1% is a good value (from my OCP course notes
> though
> > > >> not necessarily given by the tutor!)
> > > >>
> > > >> --
> > > >> Niall Litchfield
> > > >> Oracle DBA
> > > >> Audit Commission UK
> > > >> *****************************************
> > > >> Please include version and platform
> > > >> and SQL where applicable
> > > >> It makes life easier and increases the
> > > >> likelihood of a good answer
> > > >>
> > > >> ******************************************
> > > My 2 cents worth:
> > > Buffer cache hit ratio: go to www.hotsos.com and get the paper "Why
> > > 99%+ Database Buffer Cache Hit Ratio is NOT OK." This paper
> > > demonstrates that extremely high hit ratios are usually a result of
> > > poorly tuned code.
> > >
> > > PCTIncrease should be non-zero. If PCTIncrease is anything other than
> > > 0 for both the tablespace and the objects in the tablespace,
> > > fragmentation occurs. Since the number of extents has no bearing on
> > > performance (yes this is arguable if the number is in the tens of
> > > thousands), the best utilization of the space in a tablespace is if
> > > every extent is the same size. This allows any free extent in the
> > > tablespace to be used when a new extent is needed. There is no reason
> > > to coalesce since the only fragmentation is on datafile boundaries (an
> > > extent must be in a single datafile).
> >
> > I absolutely disagree. For the tablespace ... perhaps back with dictionary
> managed tablespaces and before 8i. For the objects in the
> > tablespace? What? (registering complete surprise) Where did this come
> from?
> >
> > Daniel Morgan
> >

Because mistakes do happen. And if it takes a few months to find a table that slipped by better 1% than 50%. At least that was the thinking.

Daniel Morgan Received on Mon May 20 2002 - 10:40:50 CDT