Re: performance question

Terry Rose <terryr_at_inetnebr.com> wrote:

>My company is currently evaluating Oracle for OLTP. On the server side, we have a
>Tricord with 4 Pentium CPU's, 512MB RAM, running Solaris 2.5 and Oracle 7.3. On
>the client side, we're currently using Windows NT 3.51.
 

>I have an OCI application being used to benchmark Oracle performance. A table is
>created with two columns: key CHAR(5), data CHAR(75). An index is created on the
>key.
 

>In the first phase, 20000 rows with unique key values 0-19999 are inserted in
>random order. The data column is initially blank.

*

>The second phase involves selecting 10000 rows by key, in random order.

*

>My question is about the third phase which updates the data column of 10000 random
>rows. This phase progresses in "bursts".

*

>Using a displayed counter, I can see
>several hundred updates...then a pause...several hundred more...a pause...etc.
 

>What is causing the pauses?
 

>The final phase updates the key column of 10000 random row to 'ZZZZZ'. This phase
>also exhibits the burst behavior.

*

>Performance in all phases has been disappointing. We have tried adjusting the
>server configuration from SMALL to LARGE. This resulted in better performance in
>phase 2, worse performance in phases 3 and 4. Best times were recorded with
>MEDIUM settings. Any other suggestions or tuning tips would be appreciated!

• Is your SQL statement using the index or not? Are you using cost based or rules based optimization? What percentage of data was found in the db_block buffer? Could the SQL statement be causing table scans?

[warning: self promotion on]

You can find queries to measure db_block buffer performance and a white paper on tuning Oracle at:

        www.menlosoftware.com

[self promotion off]

Regards,
Paul

--
posborn_at_menlosoftware.com      Menlo Software
http://www.menlosoftware.com   744 College Ave.
V:415.324.1286                      Menlo Park, CA 94025-5204 USA