Re: Performance problem on inserting

Do alter session set sql_trace=true for sql*plus sessions on both machines, and do a 10046 trace and rerun the scripts.

then run tkprof with the wait option and post the results. let's see what's being waited on.

the 8i tk can't do the 10046 wait analysis but it would be useful to have too.
"Kamal" <kamal80_at_virgilio.it> wrote in message news:4e766a02.0301290302.3d2591c2_at_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 - 06:25:21 CST