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

[JoeyBuch]

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.

[denevge]

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

[tycho]

Hi JoeyBuch,

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

Hope this helps.

tycho

[tamilselvan]

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.

[JoeyBuch]

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.