Loading Oracle Part 1: A brief introduction


Besides various tests like unit testing, integration testing or regression testing performed in routine development processes, development in Oracle requires another very important testing namely load/stress testing. The behavior of the systems in terms of integrity, performance and availability may dramatically change under load and concurrency.

Possible Risks under Concurrency

Loss of Integrity under Load

In a very well-known scenario by Thomas Kyte in Expert One-on-one, it is illustrated that in case that you don’t use FOR UPDATE statement before specific update operations, it’s pretty much guaranteed that your application will fail in a concurrent environment. For doing this Mr. Kyte, open two simultaneous sessions from SQL*PLUS and performs his test.

This example and many others are good for demonstrative purposes. But it is also true that real life is much more complicated then the laboratory conditions. So there is a common need for testing a program unit under concurrent executions in PL/SQL development environments.

Performance Degeneration under Load

If you ever read Oracle concept guide most probably you are familiar with parameters like INITTRANS, FREELISTS for tables. Or you must hear that heavy indexing over OLTP systems will degenerate the performance of DML operations. But I am pretty sure that you have never seen the effect of those by eyes unless you suffer from them in a real production environment.

So another requirement for concurrent programming is to test the performance of the configuration or performance of modules under load before a swarm of users test them.

Non-scalability under Load

You may know that in case that heavy locking/blocking will increase the average response time of system users in any concurrent application. This is also true for Oracle. In case that you obtain unnecessary TM or TX locks, it is true that sooner or later you will complete your task but system will be unavailable for other users requiring the same locks. This will obviously decrease the scalability of a concurrent environment.

So we also need to measure the level of scalability before deploying a non-scalable application.


Solutions to Problem

After stating the possible risks may be faced in a concurrent environment, another good thing will be to propose some solution methods. Here are most popular of them:

Large Loops

Idiot Loopers

Most people use this method. They believe that looping from a single session means testing system under load. Yes it may be true that Oracle performs many I/Os and uses lots of CPU cycles, but unfortunately this has nothing to do with concurrency. You are still working over a single session and there is only you acting on your test application.

Advanced Loopers

More expert loopers try this method. They execute their loops from 5-10 parallel sessions. By doing this they create a parallel system use. But this approach has problems also. We may list them as follows:

  • 5-10 level of concurrency is usually not a sensible level for many real-world applications.
  • Pure looping by itself is problematic in the sense that it doesn’t represent the real life. In real life users don’t open the application at the same time. You need to model the so-called ramping in your testing environment.
  • Creating, managing, closing parallel sessions over SQL*PLUS may be difficult. You need to sit in front of your desk to monitor whether all go successfully.

Real World Tests

This is maybe the best solution in terms of results among all others. You may find 100s of testers before production and let them to test your system over their own SQL*PLUS sessions. But most of the time this is also the most expensive method. Also you can never be sure that all testers perform their test cases on time or coordination of those testers may be a real problem for a concurrent test.


The final method is the topic of rest of the blog. In the broadest sense, simulation may be defined as the imitation of the real-life. This is done by the use of probability theory and statistics. Using simulation model will allow us to do the followings:

  • Testing our PL/SQL modules and Oracle for 0-1000 users.
  • Loading and de-loading system using ramping.
  • Modeling interarrival times for user requests.
  • Mix testing.
  • Logging concurrent environment errors.
  • Measuring concurrent environment response times.
  • Deciding whether one PL/SQL module or Oracle configuration is really better than the other under load.

Loading Oracle 2: Hundreds of Virtual Users in Oracle 10g…

About kocakahin

Just a computer engineer

Posted on March 2, 2007, in Oracle. Bookmark the permalink. 6 Comments.

  1. Great starting sir, waiting comings 🙂

  2. Ertürk Diriksoy

    Good article, but I do not believe
    “Creating, managing, closing parallel sessions over SQL*PLUS may be difficult.”
    Use bash under linux, a great tool for creating and managing sessions.

  3. Yes it is true that in Linux starting parallel sessions is not hardç But read the second part of the post. You will see the reason. You need a master driving session which may be the though part.

  4. A small example for Unix background processing;

    while [ $LOOP -lt $2 ]
    LOOP=`expr $LOOP + 1`

    #Run child session.sh script in background

    ./session.sh $1 $LOOP $3 &
    echo $LOOP
    # Wait for all child sessions to complete

    With this solution still the same start time problem exists, we need an extra control logic like dbms_alert as kocakahin mentioned in part 2.

  1. Pingback: Log Buffer #56: a Carnival of the Vanities for DBAs « H.Tonguç YILMAZ Oracle Blog

  2. Pingback: PL/SQL based Oracle Database Loader(PSODL) Release 2.0 on sourceforge.net « H.Tonguç YILMAZ Oracle Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: