Performance problem on inserting

From: Kamal <kamal80_at_virgilio.it>
Date: 29 Jan 2003 03:02:40 -0800
Message-ID: <4e766a02.0301290302.3d2591c2@posting.google.com>

Hi everybody.

I have big performance problem on an instance with 9i R2. This machine is a Compaq AlphaServer DS10, with a 600 Mhz processor, 1/2 Gb ram, two scsi (10000 RPM) disks of about 40 Gb each. We hoped this new machine would improve the performance with respect of the other machine we had before. It was a Pentium 3 800 Mhz machine, with the same ram and normal ATA disks, with a 8i R1 instance.

For example, this simple script on the first machine takes about 3 times the time of execution of the same script on the second:

begin
for i in 1..1000000 loop
insert into t values ('a string of about 80 characters...'); end loop;
end;

We see the performance problem in many other applications, it seems to me mostly when writing to tables, not much in selecting. The table t has no particular storage setting on both the machines. In the first machine I've put the oracle software and some datafiles on a disk, and other datafiles on another disk. No particular setting on the second machine.
I think it's a sga problem, but, I've done the most to set it finely. I'm desperate.
If somebody can see at a glance a big mistake on my init file, I would appreciate it very much.
I'll show you just the first machine init file, the second has nothing particular, just a smaller sga than the first... (Why? Why?!!!)

Here's the AlphaServer's init file:

##########################################################
db_block_size=32768

db_cache_size=201326592
db_file_multiblock_read_count=128

open_cursors=300

db_domain=""
db_name=test

background_dump_dest=/oracle/admin/test/bdump
core_dump_dest=/oracle/admin/test/cdump
user_dump_dest=/oracle/admin/test/udump

control_files=("/oradata/test/sys/control01.ctl",
"/oradata/test/sys/control02.ctl",
"/oradata/test/sys/control03.ctl")

instance_name=test

compatible=9.2.0.0.0

hash_join_enabled=TRUE
query_rewrite_enabled=FALSE

utl_file_dir=/usr/users/oracle/utl_file

java_pool_size=33554432
large_pool_size=8388608
shared_pool_size=67108864

processes=150

pga_aggregate_target=33554432
sort_area_size=4194304

##########################################################

Thank you very much.

Kamal Received on Wed Jan 29 2003 - 05:02:40 CST