Re: Tuning oracle database

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

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

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

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.

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

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

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.

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