Re: Oracle Myths

"Chris Fournier" <cfournier_at_nrtc.org> wrote in message news:ue5fnl8qiei092_at_corp.supernews.com...
> I have questions about a few of these which I though were correct:
>
> Store your segments in one extent for optimum performance.

This is guaranteed (in a dictionary managed tablespace) to cause fragmentation. Segments that are constantly grabbing a new extent simply have the wrong extent size but io against a (say) 2gb table will perform indistinguishably regardless of wether the segment has 1 or 100 extents. More to the point with the advent of 8i I consider (though I know others disagree) that all tablespaces should be locally managed (with the exception of SYSTEM which can't be) with uniform extent allocation.

>
> Seperate tables and indexes for performance reasons.

see the huge thread on this earlier at http://shrinkalink.com/201

I think my summary of this is that in general seperating data and indexes should be done for
1) management reasons only.

>
> Buffer cache hit ratio should be as high as possible preferably greater
than
> 90%.(from the java tool we use developed in house)

the goal should be to achieve you results in the minimum number of ios - a hit ratio hides the volume of work being done. mean time connor's piece of code referred to above will let you pick any hit ratio you may be required to meet, this in itself demonstrates the foolishness of relying upon a single, simple metric to diagnose good performance.

HTH

--
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

******************************************>


> Thanks!

>

>

> "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

> >

> > ******************************************

> >

> >

>

>