Re: Performance problem on inserting

kamal80_at_virgilio.it (Kamal) 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

How many disk drives did the old system have? A two drive disk system is not conductive to high performance unless the database concurrent user load is low.

What changes were made in the init.ora parameters between the two instances?

Is Oracle running under TRUE64 or OpenVMS?

You choose a 32K block size, is this a warehouse system? I doubt that your system can perform 128 X 32K worth if IO in one request so your true multiblock figure is probably much less.

Just asking a few questions to help find the differences.

• Mark D Powell --