insert very slow

[ronnie]

Hi,
I have a statement like this

SQL> insert into manda_report
2 (select * from
3 manda.manda_report@sid6.world
4 where dc_type <> 'DOM');

and it is very slow.

It is trying to insert just 12000 rows and is taking more than 30 mins.

It used to work fine on a machine with 1 gigs of RAM but now when i have the same database (recreated with cold backup) on a different machine with 256 MB RAM its very slow.

Any ideas why and is there a better way of doing this.

I am using Oracle 8.1.7 on Win 2000

Thanks
Ronnie

[irehman]

Well your RAM is decreased by 75%, that is clearly one reason.

[pando]

how about your network since you are selecting from a remote database?

[mooks]

Hi,

Since you are inserting the records from a remote database, it might be a difference in the speed as well as being mentioned is your decrease in your RAM. Maybe you can try to export the data and copy the exported file into the machine to do your insert. But I cannot guarantee that it will speed up a lot! Good Luck!

[ronnie]

I also have noticed that the database server pops up a message that "server out of virtual memory " after 15-20 mins .

I would like to mention that this 8.1.7 database has been migrated from an 8.0.5 database . i dont know how that will have any impact on it.

Is 12000 records too much for an 256 MB RAM machine. It should do it even if it takes a few hours and not give an out of virtual memory error and freeze the machine

Any Suggestions.

Ronnie

[pando]

what is ur SGA?

[oddster]

8.1.7 uses more memory than 8.0.

You should also make sure you have downsized your SGA for the smaller box. It sounds as though you are using all of your swap space.

Rgds,

[Shestakov]

Are you create second DB (with 256MB) with some SGA parameters or not. If with some then your DB works in swap (not in memory).
First step - check SGA.

[ronnie]

This time I cloned the database on a 512 MB RAM windows 2000 machine and ran the same insert statement and got the following error

SQL> insert into manda_report
2 (select * from
3 manda.manda_report@sid6.world
4 where dc_type <> 'DOM');
where dc_type <> 'DOM')
*
ERROR at line 4:
ORA-04030: out of process memory when trying to allocate 8192 bytes (cursor
work he,get new buffer)


Here are my init.ora parameters
--------------------------------------

db_name = sid9
db_files = 1020
db_file_multiblock_read_count = 16
db_block_buffers = 30000
shared_pool_size = 30000000
log_checkpoint_interval = 8000
processes = 250
open_cursors = 250
open_links = 8
dml_locks = 200
log_buffer = 153600
background_dump_dest = C:\oradata\Sid9Backup\trace
user_dump_dest =C:\oradata\Sid9Backup\trace
db_block_size =8192
compatible = 8.0.5.0.0
sort_area_size = 524288
log_checkpoint_timeout = 0
remote_login_passwordfile = shared
max_dump_file_size = 10240
utl_file_dir=*
session_cached_cursors = 50
db_domain = world
control_files = (C:\oracle\oradata\sid9\CTL1SID9.ORA,C:\oracle\oradata\sid9\CTL2SID9.ORA)


Please Help

Ronnie

[ronnie]

I was able to solve the problem. It was Bug Number 1294730 in Oracle 8.1.7.

Thanks
Anurag