My instance is failing it's performance test! Help!

Hello all.

I have a reasonably small Oracle instance (~500Mg) on UNIX. Those darn testers have been load testing my instance (by having about 200 users log in an the exact same instant and doing 1 insert each into 4 different tables. It takes about 20 seconds for all of the users to log in and do those inserts. Could some of you gurus give me some suggestions about what I could look at to get a handle on what is going on on the instance during that 20 seconds?

Any other suggestions would be GREATLY appreciated.

Thanks in advance for your help!

Joe B.

You say they had 200 sessions logging in at the same time.

If so, you bottleneck will not be you db, but your system. Making a new session is CPU expensive.

If you want to test the oracle performance, its better to first make all the connections, and afterwards starting the different test-queries.

Hope this helps
Gert

Hi JoeyBuch,

Maybe you can use UTLBSTAT and UTLESTAT to get a performance overview in a report.txt.

Hope this helps.

tycho

Following suggestions may help in this situation:

1 Use MTS

2 Increase the FREELIST and INITTRAN parameters for each table. This will really reduce time to insert new rows.

3 Increase number of rollback segments.

4 Also query from V$WAITSTAT for any wait.

5 Do not place any data file where redo log files are setup.

Thanks to everyone (a little late) for your suggestions. It turned out that we jumped the gun a bit on blaming the database for our woes. I will use many of your suggestions to do some proactive database tuning, though.

Thanks again!

Joe B.