Loading Oracle Part 1: A brief introduction

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.

Simulations

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…