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

Introduction

Starting from the second part of Loading Oracle post, our main scope will be the bulletins listed in the Simulation section of Part 1. In the next parts of the post those bulletins will be discussed in detail. To begin with we explain how to imitate hundreds of users in Oracle without using any external thread/process creating software.

Job concept in Oracle

In http://tahiti.oracle.com
Oracle defines the job as follows:

“A job is a user-defined task that is scheduled to run one or more times. It is a combination of what needs to be executed (the action) and when (the schedule)”

Prior to 10g, Oracle has an infamous PL/SQL package DBMS_JOBS having 5-6 sub-routines in. However by the arrival of 10g, Oracle replace his old package with a more talented one namely DBMS_SCHEDULER. Although detailed discussion of DBMS_SCHEDULER package is not the scope of this post, we will briefly explain how Oracle handles its scheduled jobs and why this is important for our purpose. So let us explain the Oracle job architecture on a single instance (it is much more an extension in RAC):

(DBA|ALL|USER)_SCHEDULER_JOBS views

Oracle internally keeps the metadata of all scheduler jobs in its internal tables. Database users may access this information from catalog views named as *_SCHEDULER_JOBS (just like almost any catalog view).

Oracle creates those views by joining its internal tables obj$, user$, obj$, and scheduler$_job. Since the details of these views are out of our scope, for further detail on them you may refer to http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_2049.htm#i1587306

JOB ORCHASTIRATION & EXECUTION

So if Oracle stores his scheduled jobs in those structures, the next question may be “How he manages and executes them?”(By the way Oracle is a male).Each Oracle instance has a job coordinator background process namely cjqNNN. This process orchestrate all other slave job processes for execution, do caching of jobs for faster execution, perform disaster recovery of jobs for crashed instances, etc.

First of the two most important topics for Loading Oracle post purpose is the execution of those jobs. As a slave process got the metadata of the job to be executed it starts a new session, performs its task and closes the connection by committing his transaction. This means that each job by itself acts like a session opening a database connection. So this means that it will be like loading database with number of sessions at a time if Oracle can start multiples of scheduler jobs at a time.

So the next question will be how to create those sessions at a time and what determines the number of Oracle jobs that can run simultaneously. The answer is JOB_QUEUE_PROCESSES parameter. Oracle can handle as many slave processes as the value of JOB_QUEUE_PROCESSES parameter. This parameter is a system and instance modifiable parameter and can take a maximum value of 1000 which is a pretty large value.

A CHASM CAN BE FELT IN…

Remember that Oracle is software and is not designed to run in real-time. So scheduling of n simultaneous jobs to start at the same point in time may not be as easy as the value of n grows up. So you are the one as developer that has to synchronize your jobs to start almost at the same time. As you will see in below we use another good old Oracle package namely DBMS_ALERT for this purpose. To understand how the things work see Figure 1.

Figure 1

1. At time t = 0, we schedule 10 jobs to be executed (DBMS_SCHEDULER.create_job). Theoretically they have to start execution slightly after t = 0. But we know that it may not be possible in practice. So we order each slave process to wait for GO signal till t = 60. (DBMS_ALERT.waitone(‘GO’,l_msg,l_status,60)).
2. Although scheduled processes may not start execution at exactly the same time, almost all of them will reach to so-called synchronization barrier by time t = 30 – ∆. (a small time just before t = 30 sec.)
3. At time t = 30 master process signal a GO for registered slaves (DBMS_ALERT.signal(‘GO’,’Go’)). All waiting processes hanged at synchronization barrier will continue running having l_status set to value 0 meaning that “I’ve received the signal“.
4. After this point two things will occur but the exact ordering of those events cannot be marked in precision. So they will be discussed as sub-events of the same event:
   1. Since a few jobs may not reach synchronization barrier before the signaling of GO, they will timeout at time t = 60. But this is not a big problem since they have l_status to be set 1 meaning that timeout has occurred for them and they don’t contribute to timing metrics.
   2. Finally at time t = 84 no more running processes left.

Let’s load it…

We see that it is possible to load Oracle with hundreds of processes at a time. Let’s as the first example of Loading Oracle post, try a mini example by testing the average insertion rate (IPS) for heap and indexed organized tables. By this example we fundamentally show that

Time metrics you measure by “Idiot Looping” doesn’t represent the real world.

Pre-loading tasks

Before running the scripts loading the Oracle, test schema needs some grants and objects. Moreover JOB_QUEUE_PROCESSES parameter needs to be set to an appropriate value. So run the preloading.sql to complete following tasks:

• Set JOB_QUEUE_PROCESSES = 50(A larger value than 20, because there are some Oracle functionalities (like AQ propagation processes) having a number of scheduled jobs).
• Grant DBMS_LOCK and DBMS_ALERT to test schema.
• Create a timing table called AUX_RUN_STAT for measuring the time metrics.
• Create a sequence called DEMO_SEQ for later use.

IPS over IOT in an isolated environment

First we measure the IOT insertion rate (IPS) in an isolated environment. For doing this test, we will use SIOT.sql (Sequential IOT) script. This script inserts 5000 rows into an IOT table 20 times. Then we will take the average of this insertion times and talk over this in “Moral of the story” section.

IPS over heap table in an isolated environment

This time we perform the same test we have just done on a heap table using same storage parameters we have used for IOT table. For doing this we’ve used SHEAP.sql (Sequential HEAP).

Insertion rate of IOT in a concurrent environment

After completing “Idiot Looping” tests now we can start up with real world tests. First we will try loading Oracle with 20 concurrent sessions each will try inserting 5000 rows into an IOT. For this we’ve used CIOT.sql (Concurrent IOT).

Insertion rate of heap table in an isolated environment

Finally we do the concurrency test for HEAP using CHEAP.sql (Concurrent HEAP).

Moral of the story

After all those executions, we may extract the following summary table using AUX_RUN_STAT table, a piece of statistics and SQL.

	Scenario	IPS	Average (msec.)	Std (msec.)	Service Time for 95% of sessions or iterations
1	SHEAP	52687.04	94.90	1.59	94.03 – 95.77
2	SIOT	45433.89	110.05	11.08	104.02 – 116.08
3	CHEAP	68181.82	1100.00	108.09	1029.96 – 1170.04
4	CIOT	66473.65	1128.27	12.20	1120.36 – 1136.17

Figure 2

So let’s underline some results:

1. Notice that IPS rate increases (by an average rate of 23% – 32%) as the level of concurrency increases from 1 to 20.
2. Another point is one can conclude that heap table yields a 13% better IPS rate than indexed organized table by just looking sequential test results. Indeed, this is the pitfall of “Idiot Looping” method. Because concurrent test results indicate that this difference is only 2.5% at 20 level of concurrency.
3. As a final remark sequential results shows a statistically significant difference in service times between insertion timings over heap table and IOT, but this result is falsified by the results of concurrent test because the service time intervals of IOT and heap table are overlapping.

To sum up, this simple example illustrates the problem with testing systems using “Idiot Looping” technique. Remember that it may give a rough idea about the performance of the system but concurrent environment tests may totally irrelevant to those results. So in order to reach correct benchmarks about your system, you need to benchmark your system at the correct level of concurrency.

Loading Oracle Part 3: Exponential inter-arrivals & a bit of queuing theory