General
cancel
Showing results for 
Search instead for 
Did you mean: 

question about stress test on oracle

SOLVED
Go to solution
faust2004
Regular Advisor

question about stress test on oracle

Hi , experts,

I have a c/s aplication system , the server is oracle database, I'm going to the
convert the database server ( alpha box) to new hp box. but don't know the performance
of the new hp server, so want to do some stress
test before conversion bu simulate the many user using the server at the same time.
are there some tools to do this? how to choose and plan the test program?

thanks a lot
sunny

9 REPLIES
Steven Sim Kok Leong
Honored Contributor
Solution

Re: question about stress test on oracle

Hi,

On the commercial side, the most prestigious of stress test tools is the loadrunner by Mercury Interactive:

http://www-heva.mercuryinteractive.com/products/loadrunner/

Here's a list of commercial tools for stress testing (includes database stress test tools):

http://www.testingfaqs.org/t-load.htm

On the F.O.C. side, you can write your own sqlplus scripts to generate the load.

One crude example:

#!/sbin/sh

num=1
while [ "$num" -le 2000 ]
do
./mysqlscript &
num=`expr $num + 1`
done

Hope this helps. Regards.

Steven Sim Kok Leong
Email: steven@beepz.com. Homepage: https://www.beepz.com
faust2004
Regular Advisor

Re: question about stress test on oracle

Hi , steven,

I used some simple sql script to test, such as select , insert. just as you say . but find the stress program make the database dead lock. I don't know how to plan the test to make my test is similar the real situation, if the test is very diferent from real situation,
the result may be meaningless.


Thanks
Sunny
Steven Sim Kok Leong
Honored Contributor

Re: question about stress test on oracle

Hi,

Commercial tools such as loadrunner, as far as I can recall, records live traffic to the production server and replays them onto the test server so as to simulate as closely as possible and average-case scenerio.

Some IDSes allow traffic to be replayed (Snort, RealSecure as far as I can recall). However, I have yet to seen an IDS being used to perform stress simulation and testing.

Replaying live traffic is the closest you can get to simulating it.

As a crude stress testing tool to simulate concurrent users, a script suffices in measuring the worst-case scenerio for load.

Hope this helps. Regards.

Steven Sim Kok Leong
Email: steven@beepz.com. Homepage: https://www.beepz.com
Steven Sim Kok Leong
Honored Contributor

Re: question about stress test on oracle

Hi,

Btw, you mentioned that the script causes your database to deadlock. If that is really the case, you will have to put resolving this issue as top priority.

Just imagine a malicious user running a similar script to generate multiple sqlnet connections from his remote linux system to your database server. This user would have effectively created a denial-of-service (DoS) on your database server.

Hope this helps. Regards.

Steven Sim Kok Leong
Email: steven@beepz.com. Homepage: https://www.beepz.com
faust2004
Regular Advisor

Re: question about stress test on oracle

steven,
You are right, Replaying live traffic is what I want. somebody suggeested me to use a tool called expect , do you know sth about it?
many process use the same sql script to access the server is just like DoS, is such test a guide for me to adjust the database parameter?

Thanks
Sunny


Steven Sim Kok Leong
Honored Contributor

Re: question about stress test on oracle

Hi,

Expect allows you to automate programs that require interactive inputs. Input conditions can be dependent on the output displayed as well as time delay etc.

You can download a copy of Expect. The pre-compiled binary depot is available for HP-UX 10.20 and HP-UX 11.00 as well as the source from

http://hpux.connect.org.uk

With an Expect script, your script can decide on the next sql command to perform based on the output perceived. Thus, it simulates user interaction with the database based on the feedback output.

You can attempt a replay using Expect by coding a script but it is going to be very tedious because you will need one script for each user since the commands executed by each user can be different in real-life. You will need to have on-hand a record of the frequent commands executed and the tables accessed (perhaps from the OEM Oracle Enterprise Manager) as well as the different rates at which they are being accessed at different times of the day (e.g. during peak hours and during off-peak hours).

To simulate all these in an Expect script is going to be very tedious. Expect scripting, alike shell-script+sql-script, provides limited and crude simulation of the load. The good thing about Expect is that you will be able to be able to simulate the interaction with the command output.

What does your database alert.log and your system log syslog.log tell you when the database hung?

Hope this helps. Regards.

Steven Sim Kok Leong
Email: steven@beepz.com. Homepage: https://www.beepz.com
Steven Sim Kok Leong
Honored Contributor

Re: question about stress test on oracle

Hi,

The Oracle Performance Manager may interest you. It supports record and replay functions:

"Oracle Performance Manager captures, computes, and presents performance data for your database and operating system, allowing you to monitor key metrics required to effectively use memory, minimize disk I/O, and to avoid resource contention. It provides a graphical, real-time view of the performance metrics and lets you drill down into a monitoring view for quick access to detailed data for performance problem solving. The performance data is captured and displayed in real-time mode. You can also record the data for replay."

For more details, look at this link:

http://www.cs.umbc.edu/help/oracle8/server.815/a67775/ch12_too.htm

Hope this helps. Regards.

Steven Sim Kok Leong
Email: steven@beepz.com. Homepage: https://www.beepz.com
Steven Sim Kok Leong
Honored Contributor

Re: question about stress test on oracle

Hi,

Even though both the Oracle Performance Manager and the Oracle Capacity Planner are not really load simulation tools, they aid the stress testing by monitoring, analyzing and recommending based on the performance impact brought about by the stress test.

There are many tools provided by Oracle for performance monitoring, analyzing, tuning and capacity planning.

Additional information (with screenshots) can be found here:

http://www.dbspecialists.com/presentations/bottleneck.html

Hope this helps. Regards.

Steven Sim Kok Leong
Email: steven@beepz.com. Homepage: https://www.beepz.com
faust2004
Regular Advisor

Re: question about stress test on oracle

Steven,

I tried the expect days before, as you say,
it's very tedious to fufill my job. I find
a free script called autoexpect which can record the user key typing, the replay it with expect. but i feel it 's dangerous to do so, i'am afriad the autoexpect may be slow down the productin server.
my test is just sql script incliuding some select and update, sometimes 10 such scrips made the server dead lock , although I make the sleep between each, may be something wrong with my script.
another script of my test is select count(*) from a large table and group by , hundreds of such scrips make database latch free high and almost stop.
I'm confused about how to choose the right script now

Thanks
Sunny