Xref: alice comp.databases.oracle.server:83123
Path: alice!news-feed.fnsi.net!news-FFM2.ecrc.net!news-MUC.ecrc.net!news.hamburg.cityline.net!not-for-mail
From: "Roger" <NOrgSPAM@mcs-hh.de>
Newsgroups: comp.databases.oracle.server
Subject: Re: Checkpoint stalls database for seconds
Date: Wed, 2 Feb 2000 22:34:24 +0100
Organization: MCS - Moorbek Computer Systeme GmbH, Hamburg, Germany
Lines: 79
Message-ID: <87a7t0$n71$1@opal.hamburg.cityline.net>
References: <38988E5A.CD2D3483@bellesystems.com>
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2314.1300
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300

Hi,

one thing I would do is to generate some statistices using utlbstat and
utlestat.  Look there for the value of "DBWR checkpoint requests". If this
value is very high than to many checkpoints are occuring (if this is the
case try to increase the size of your redo logs). But judging from the
information you posted I would look for something like "Checkpoint not
complete; unable to allocate file" in the alert.log file. In this case I
would add some more Redo Log groups

Bye
  Roger

BTW:  Is there a reason that you set log_checkpoint_timeout=30, since the
result is that there is a  lock switch as soon as 30 operating system blocks
have been written.

Brian Rasmusson <brasmuss@bellesystems.com> schrieb in im Newsbeitrag:
38988E5A.CD2D3483@bellesystems.com...
> Hi,
>
> we're doing some initial benchmarking of an OLTP application performing
> about 100 updates and 2-300 insert a second. 1 session is estimated to
> be 1 update and 2-3 inserts. Initial results showed 200 sessions a
> second, but it has dropped to 100 because of problems with checkpoints.
>
> First let me point out that I'm not a DBA, but have consulted several to
> get to this configuration.
>
> What we see is that a checkpoint stalls the database for a couple of
> seconds, and since this is a real-time system that is not tolerated.
> Where would you suggest we start digging?
>
> Platform is Oracle 8.0.5, Sun Solaris on a Sun 450, 4 CPUs and 2GB ram
> and around 10 disks. No RAID, just plain 10.000 RPM disks. Redo logs are
> stored on a separate disk with direct-io enabled.
>
> Configuration clips:
>
> rollback_segments=(r1,r2,r3,r4)
> db_files        = 100
> open_cursors    = 256
> db_file_multiblock_read_count   = 32
> db_block_buffers                = 12800 ## 100M - memory size dependent
> db_block_lru_latches            = 4 ## set = number of cpu's
> buffer_pool_keep                =  8192
> shared_pool_size                = 120M
> optimizer_mode  = FIRST_ROWS
> dml_locks = 500
> log_buffer = 262144                     ## 256K
> log_simultaneous_copies = 8             ## 2 * cpu_count
> log_small_entry_max_size = 32           ## no copy_latch for_log_upd.
> below bytes
> log_checkpoint_interval = 30
> log_checkpoint_timeout  = 0
> lgwr_io_slaves = 4                      ## min. one for each disk/raid
> channel
> log_checkpoints_to_alert = false        ## to save time??
> sequence_cache_entries = 100
> sequence_cache_hash_buckets = 89
> max_dump_file_size = 20480              ## 10M  # max trace file
> large_pool_min_alloc                    = 128K
> object_cache_optimal_size               = 65536
> object_cache_max_size_percent           = 100
> row_cache_cursors                       = 60
> session_cached_cursors                  = 20
> sessions                                = 100
> processes                               = 250
> db_writer_processes = 1 ## usely adequate on async i/o
>
> Tables are partitioned, so that the table we update frequently is spread
> on 3 disks, and the index on 3 other disks. I'm sure our own DBA will be
> able to tweak it further, but I would like to hear other opinions as
> well as he has left the office for today :-)
>
> Thanks for any suggestions,
> Brian