Re: Please help with performance problem

In addition to all the other fine comments check to make sure if you use a where clause
references all columns in a composite index (if you have them) if you do and your WHERE clause
only references on of the coloumns it will not use the index. Create a separate index for the column used in the where clause.

Also try setting the parallel query option on the tables in question to a degree of
4 or 5.
ALTER TABLE TABLE_NAME PARALLEL DEGREE 4 alos set PARALLEL_MIN_SERVERS = 4
and PARALLEL_MAX_SERVERS = 8 in the init.ora file.

These refer to the parallel query option that is native to Oracle Server 7.3.
For good info about PQO read Oracle Press Book Advanced Oracale tuning and Administration Chapter 12.
Setting these values has a dramatic effect on the speed of queries.

Also I heard of a bug in Oracle for NT where cursors were failing to be closed. When ever a SQL statement is parsed Oracle opens an implicit cursor for any insert update or select statements. That could be the reason for the degredation of query performence.

Have fun.....

Gena 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..........
Received on Wed Nov 26 1997 - 00:00:00 CST