Xref: alice comp.databases.oracle.server:83127
Path: alice!news-feed.fnsi.net!news-FFM2.ecrc.net!newsfeeds.belnet.be!news.belnet.be!skynet.be!newsfeed2.news.nl.uu.net!sun4nl!bullseye.news.demon.net!demon!news.demon.nl!demon!sybrandb.demon.nl!not-for-mail
From: "Sybrand Bakker" <postmaster@sybrandb.demon.nl>
Newsgroups: comp.databases.oracle.server
Subject: Re: Checkpoint stalls database for seconds
Date: Wed, 2 Feb 2000 22:44:47 +0100
Message-ID: <949528016.8614.0.pluto.d4ee154e@news.demon.nl>
References: <38988E5A.CD2D3483@bellesystems.com>
Reply-To: "Sybrand Bakker" <postmaster@sybrandb.nospam.demon.nl>
X-Trace: news.demon.nl 949528016 pluto:8614 NO-IDENT sybrandb.demon.nl:212.238.21.78
X-Complaints-To: abuse@nl.demon.net
X-Newsreader: Microsoft Outlook Express 5.00.2014.211
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2014.211
Lines: 73

The issue here is log_checkpoint_interval. That value is in units of
512-bytes.
You are performing a checkpoint after every 15k written!
This value needs to be greater than or equal to the size of the redolog
file.

Hth,


--
Sybrand Bakker, Oracle DBA
Brian Rasmusson <brasmuss@bellesystems.com> wrote in message
news: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