Re: Please help with performance problem

On Thu, 20 Nov 1997 20:42:22 -0200, Gena <gshepin_at_webspan.net> wrote:

>Hi.
>
>I am trying to tune an Oracle 7.3.3 running on an NT 4 server.
>This machine has 256Megs of Memory and dual 200MHZ processors.
>This machine is completely dedicated to Oracle and never has anything
>else running.
>
>I have been trying to tune it few a few days now.
>The selects seem to work fine, but updates, deletes and especially
>inserts seem to take much longer than I would think they should on such
>a machine.
>
>To insert 2500 records on a 4 field table (which has only about 2000
>records) takes 45 seconds to a minute.
>To update or delete the same # of records (the delete criteria is on an
>indexed field) takes 30-40 seconds.
>Basically, every operation that does any database changes seems to kill
>performance.
>
>
>I am trying anything I can think of to improve this performance but
>nothing seems to be working.
>
>The Data, indexes, and rollback tablespaces are all on separate drives.
>
>I have rebuilt the database with the following initorcl parameters:
>
>I changes all initorcl parameters to those of a Large Database, I also
>set
>cpu_count=2
>db_block_size=8192 (the maximum allowed on NT)
>shared_pool_size=12000000
>pre_page_sga = True (to ensure the entire SGA will be in memory)
>
>Then I rebuilt the database.
>
>I saw little if any improvement in performance (from the default install
>
>configuration) after the rebuild.
>
>Can somebody please tell me if I'm missing any important parameters to
>improve
>performance, or has any other suggestions, or best of all if someone has
>a similar
>hardware setup, can I please see you initorcl file.
>
>any help would be GREATLY appreciated..........
>

Are you running in archivelog mode? How big and how many redo logs do you have. It may be that the transactions are waiting for redo logs to be archived. I use redo logs 1M in size. By what mechanism (=tool) are the rows being inserted?

My own machine is a 200MHz Pentium Pro with 64M RAM and a single hard disk. On a two column table with a primary key index on one column and a non-unique index on the other, I can issue 3000 identical insert statements in sqlplus ( insert into clr values ( s.nextval, to_char(s.nextval )) in 30 seconds. During this process, the limiting factor is CPU which makes me think the SQLPlus interpreter is slowing things down. Deletion by key value ( delete from clr where clridx > 12000) takes 6-9 seconds, depending on db_block_buffers.

Strange problem: each subsequent execution of the sqlplus command file containing the insert statements takes thirty seconds longer i.e. first run takes 30 seconds, second run takes 60 seconds, third run takes 90 seconds. I stopped when it was taking five minutes. I commit between runs. This is not affected by exiting and re-entering sqlplus, nor does it seem to be affected by db_block_buffers. The time resets to 30 seconds after the database is shutdown and restarted. Does anyone have an explanation of this?

Is it possible under NT4 to limit the amount of memory used for file caching? I'm having a problem that if I write a large export file for instance, the file cache grows to the point where the SGA is paged out, causing performance problems?

sqlldr will insert 22000 records in 45 seconds with rows=10, or 22000 rows in 30 seconds with rows=1000. The moral of this tale being that the choice of tool to carry out the inserts can have an enormous influence on performance. Received on Fri Nov 21 1997 - 00:00:00 CST