Re: Performance problems with Oracle EE 8iR2 on 64 Bit Solaris

Alexander Penev wrote:
>
> 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

Were you running veritas file system (vxfs). There is a known bug that gives (as mush as) a five fold performance degradation..

HTH

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk (mirrored at
http://www.oradba.freeserve.co.uk)

"Some days you're the pigeon, some days you're the statue"