Re: Tuning oracle database

spencer <spencerp_at_swbell.net> wrote in message news:1ycc5.202$Fb3.110058_at_nnrp1.sbc.net...
> "Howard J. Rogers" <howardjr_at_www.com> wrote in message
 news:39707ec5_at_news.iprimus.com.au...
> >
> > "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message
> > news:963507966.1643.1.pluto.d4ee154e_at_news.demon.nl...
> > > 1 db_block_size : at least 4k
> > > 2 db_block_buffers: at least 2000 (based on 4k), but 5000 is probably
 more
> > > like it.
> > > 3 shared_pool_size: at least 10 to 20 M
> > > 4 sort_area_size at least 262144 (256k) but better still 1048576 (1M)
> > > 5 online redo log files: at least 4 of 5M each, sw mirrored.
> > >
> >
> > Where do you get these numbers??! Until you know how many concurrent
 users
> > he has, I can't see how you can reasonably propose a sort_area_size.
> > Suppose he has a thousand users. You really want him to have a Gigabyte
 of
> > sort_area_size??
> >
>
> in the unlikely event that 1000 queries are running concurrently, setting
> sort_area_size to a large value would increase the total memory required.
> with a large number of concurrent connections, where a number of them
> are idle at any given time, i'd be much more concerned with the value of
> the sort_area_retained_size than sort_area_size.
>

Er, I rather think that's my point.... it all depends on whether he has 1000 concurrent sorts, or some lying idle, doesn't it? And you know the answer to this one because....? Ah, I see. You don't know the answer (just as none of us do), because the original poster didn't tell us. Yet it's perfectly OK to propose a sort-area_size that may be completely unworkable once we *do* find out those answers, huh?

Of course you would be concerned with all these numbers *having thought about it*, and having come to considered conclusions about your database and the way it is being and is intended to be used.

> > DB_BLOCK_SIZE?? Depends on his operating system, and on whether
 performance
> > is king -but 16K blocks these days is a bit of a no brainer.
>
> avoiding the 2K blocksize is a no brainer. jumping from 4K to 16K is not
> always prudent. of course, the selection of the database blocksize is
 really
> going to depend on the type of database access... for a DSS, yes, larger
> blocks can reduce overhead and improve performance. for an OLTP database,
> a smaller block size, like 4K, may be a better starting point.
>

Not these days and with the operating systems there are around today it's not.

> > How do you propose a number of online redo logs, until you've seen his
 alert
> > log and the rate at which he is proposing to switch logs? The number of
> > logs is governed almost enitrely by the rate at which log switches
 occur.
> > The size of each log is governed almost enitrely by the rate at which he
> > wants to checkpoint and the speed with which he wishes to recover the
> > database in the event of failure.
> >
>
> the number of redo logs should _NOT_ be "governed almost entirely by the
> rate at which log switches occur". that's nonsense, plain and simple.
>

No it's not. If he's switching frequently, he is at risk of switching back on himself before checkpointing of the log is completed. Solution: add more log groups. When the number of groups is becoming unwieldy, start thinking of intra-log checkpointing, with log_checkpoint_interval et al. Unless you *like* checkpointing left right and centre. When performance is not critical, or when mean time to recovery is, then fair enough. But the fact remains -if you switch very frequently, you will almost invariably have more log groups than if you switch very rarely.

> for a database running in archive log mode, the number of redo logs needs
> to be sufficient such that the database doesn't end up waiting for the
 ARCH
> process to complete. yes, a checkpoint is taken at each log switch, but
> it is possible to checkpoint more frequently than at each log switch. the
> frequency of log switches really needs to be governed by the how
 frequently
> you need archive log files written (so that the files can be sent offsite,
 or
> written to tape, or applied to a standby database), and how many archive
> log files you want to manage.
>

The issue of taking archives, and of switching back before an archive is completed, is a genuine one. They complicate the checkpointing situation, undoubtedly. But they don't negate the points I raised, and since the original poster is talking about setting up a database to begin with, I doubt that considerations of future standby databases should or would weigh very heavily on his decision-making processes.

> > In short: Your numbers are gibberish.
>
> twas brillig, and the slithy toves did gyre and gimble in the wabe;
> all mimsy were the borogoves, and the mome raths outgrabe.
>
> Your responses are nonsense. Given what was asked for, Sybrand's
> recommendations are right on. The original poster asked for what
> default values should be changed to what size.
>

And my point is that you cannot possibly recommend absolute sizes for anything (bar, perhaps, block size) on the basis of the information supplied.

> > The polite answer to his original question is, 'no, there are no
 shortcuts
> > to working out how your database is behaving, how you want it to behave,
 and
> > learning how to turn the one state of affairs into the other.'
>
> Yes, your proposed answer would have been polite, but it also would have
> been wrong. Clearly, there ARE shortcuts. One obvious one is to avoid
> creating a default database with a 2K blocksize, and to create more than
> 2 log groups with 2 members each.

Doesn't compute, captain. I'll happily agree with the block size issue. But 2 log groups may be just fine and dandy. And 2 members per group could perfectly reasonably be seen as too little protection. "May" and "Could" being the operative words, I again come back to the point that you can't simply trot out a bunch of numbers and expect them to make sense in any or all situations.

>Starting with a configuration that is
> more reasonable than the default can be a big time saver. Or do you build
> your databases with a 2K blocksize, and then rebuild them with the 16K
> blocksize at a later point in time ?
>

Fabulous argument: you pick the one point on which I originally proposed an absolute number: '16K block size is a bit of a no-brainer'. For the rest of your argument, I merely refer to your own words "a configuration that is more reasonable". Reasonableness is a subjective quantity, and (for the record just one more time) my point simply was that the original poster needs to determine what is reasonable for himself, and not take a whole bunch of absolute numbers as some kind of gospel.

> Yes, of course, an analysis of the current performance characteristics is
> an essential component in tuning an Oracle database... but that doesn't
> require that one start with a default configuration.
>

Who said anything about accepting a default configuration? Certainly not me... 16K blocks aren't even the default in 8i. I just don't like to see a whole bunch of numbers proposed on the basis of, er... nothing very much.

Regards
HJR
> >
> > HJR
> >
> >
> > > Hth,
> > >
> > > Sybrand Bakker, Oracle DBA
> > >
> > > "Axel Pötzinger" <a.poet_at_freenet.de> wrote in message
> > > news:8kkqdp$r9h$11$1_at_news.t-online.com...
> > > > Hi,
> > > >
> > > > could anybody please give us some hints what are the most common
 means to
> > > > tune an oracle database. what default values should be increase to
 what
 size
> > > > .......
> > > >
> > > > Axel
> > > >
>
>
>
Received on Sun Jul 16 2000 - 00:00:00 CDT