Performance problems with Oracle EE 8iR2 on 64 Bit Solaris

From: Alexander Penev <peneva_at_wien.spardat.at>
Date: Mon, 18 Dec 2000 10:25:35 +0100
Message-ID: <91kl70$gu2$1@leech.it-austria.net>

Hi,
We use a Sun E10k domain with 2 CPU Sparc 450, 4 GB RAM and a T3 Disk Storage array (9x18G) for our DB Instance: 8.1.6.0.0 without a patch. We have a very write intensive app (loading and procesing data of some gigs per day with about a total of 50-60 GB data tablespaces and about 100 mio recs) We migrated to the sun plattform mainly for performance reasons (from NT/Compaq) Now we have a performance increase of about 100% (the old mashine is a PP200/256MB/8 disks with 5400 rps and >12ms access time) For the investment that we made that's absolutely unacceptable. We have done some performance test and made the conclusion that the mashine is almost idle all the time: cpus 50%, disk array writes with 2.5 MB/s with 60% load. All the files are on the T3 which is configured as a huge raid 5. I know the common problems with io distribution but the sun specialist means that such kinds of problems were solved with their intelligent caching system. Hier are the init.ora parameter:
open_cursors = 100
max_enabled_roles = 30

db_block_size = 16384
db_block_buffers = 10240
db_file_multiblock_read_count=32
db_writer_processes = 3

shared_pool_size = 160000000
large_pool_size = 614400
java_pool_size = 0
log_checkpoint_interval = 10000
log_checkpoint_timeout = 1800
processes = 50
sort_area_size = 10485760
sort_area_retained_size = 1048576
log_buffer = 491520
max_dump_file_size = 10000 # limit trace file size to 5M each timed_statistics = true # if you want timed statistics compatible = "8.0.5"

Hier are some test data:
import of a 1GB table/13 Mio recs, no index, import with constraints=N and indexes=N, extends are pre-created with the safe concept (20x5120k extents), rows=y
execution time 18-19 min, avg cpu's load 45%, disk (from the whole array): read 1.3MB/s, write 2.5 MB/s, load 60%. If we start other loads (sqlldr , insert into select with 2 mio recs and so on) the load goes to a high of 12MB /S write and 80% disk array load and 80% avg cpu load without a significat performance penalty for the import job. We have also copied files while the import was running and had up to 75 MB/s read+write for the whole disk array without a performance lost for the oracle jobs. The same test with the sqlloader, direct = true, parallel=true has brought a very poor performance=30 min. with disc io of 1.2 MB/s write and <1.0 MB read, cpu < 30%. The sun specialist have told us that the only reason is the oracle instance because it doesn't succeed to bring enough load to the machine. We have also invited someone from oracle but he couldn't come. We tried with increasing the count of db writers, io_slaves but all of this test results were poor, even very poor.
Inserts into slect from queries were a little bit better : about 6MB/s Write+2MB/s read but still unacceptable. Next weak we have a new meeting also with a support specialist from oracle but I would be very glad to become some advise from you in order to check some more performance options before we make the new tests. This could be as well advices in connection with the operating system and the T3 storage array "behavior" or other parameters which are not commonly known. Thank you in advance
Alex Received on Mon Dec 18 2000 - 03:25:35 CST