Xref: alice comp.databases.oracle.server:83107 Path: alice!news-feed.fnsi.net!news-FFM2.ecrc.net!newsfeeds.belnet.be!news.belnet.be!newsfeed00.sul.t-online.de!t-online.de!bignews.mediaways.net!cleanfeed.inet.tele.dQ!netscum.int!not-for-mail From: Brian Rasmusson Newsgroups: comp.databases.oracle.server Subject: Checkpoint stalls database for seconds Date: Wed, 02 Feb 2000 21:06:50 +0100 Organization: Belle Systems Lines: 58 Message-ID: <38988E5A.CD2D3483@bellesystems.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Trace: news.inet.tele.dk 949522227 28534 195.249.38.199 (Wed 2346 September 1993 20:10:27 GMT) X-Complaints-To: Department of Written Abuse NNTP-Posting-Date: Wed 2346 September 1993 20:10:27 GMT X-Mailer: Mozilla 4.7 [en] (WinNT; I) X-Accept-Language: en 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