Re: DB Buffer Cache Size

"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:<4128f1c0$0$3928$afc38c87_at_news.optusnet.com.au>...
> Don Burleson wrote:
>
> >
> > Like everything else in Oracle, it depends!
> >
> > For reads, disk I/O is almost always shower then an LIO.
> >
> > For writes, you are correct. That's why many DBA's place high DML
> > objects in a separate tablespace (with a different blocksize), and map
> > it to a smaller buffer.
>
> I wish you'd start qualifying that nugget of advice, too. You *can't* (ie,
> shouldn't) utilise different blocksize tablespaces unless you've got
> directio. Otherwise you are constrained in the matter.

I think that I know what you mean - that irrespective of ypur block size you are dependent upon os read() calls which take place at OS block size - but worry a little bit about saying *can't* - I'd rather say that doing so gives no discernible benefit.

> > Interesting point. When I visit a client I usually find thousands of
> > sub-optimal SQL statements that would take months to manually tune.
> > To get a head-start, I tweak the instance parms to broad-brush tune as
> > much as possible to the lowest common denominator. Then I can sweep
> > v$sql_plan and tune the exceptions. Tuning the instance first saves
> > buckets of manual tuning effort and lowering optimizer_index_cost_adj
> > will sometimes remove sub-optimal full-tables scans for hundreds of
> > statement in just a few minutes. What's wrong with that?
>
> What's wrong with it? Er, if you tune the instance first, you will (perhaps)
> have configured lots of memory which wasn't ever actually needed if only
> you'd sort the code out. If that involves a purchase of $40,000, I would be
> a mite pissed off about that.
>
> It's funny: the Oracle Performance Tuning course has for years been drumming
> into people (correctly, IMO) the order of events: Design, Application,
> Memory, I/O, Contention, Operating System. Any other order of events is
> likely to result in 'loop tuning', where you fix problem A, move onto
> problem B, and find that fixing problem B has re-blown problem A.
>
> No doubt it's lucrative for the consultant. But it's not efficient.

I have major issues with this particular approach as a DBA. The problem with it is is not its correctness but the ability to deal with this when you cannot touch either the code or the design - i.e. when you bought the product. For a *lot* of products you can in fact touch the design or application, by working with the application, usually unfortunately you don't realize the significance of your application setup choices until you have made them.

Its like the use bind variables mantra - great if I'm writing the sql, a complete bugger if all I can do is establish that the app doesn't use them.

Niall Received on Mon Aug 23 2004 - 08:34:55 CDT