High-Concurrency Database Load Testing in Oracle Using Native PL/SQL

High-Concurrency Database Load Testing in Oracle Using Native PL/SQL

Building a Lightweight, In-Database Benchmarking Framework with DBMS_SQL and DBMS_SCHEDULER

Simulating high-concurrency database workloads often relies on external testing tools such as JMeter, HammerDB, or custom application drivers. While effective, external tools introduce network latency variations, connection pool overhead, and complex infrastructure setup.

When your objective is to evaluate pure database execution performance—such as engine efficiency, latch contention, or indexing strategies under heavy stress—executing the load directly inside the database engine provides the cleanest, most repeatable results.

This post details a pure PL/SQL methodology for conducting high-concurrency database load testing in Oracle using built-in packages.

Architectural Overview of the Methodology

The native load-testing framework consists of three core components:

1. The Workload Engine (DBMS_SQL): Executes targeted SQL statements inside a high-throughput loop, leveraging dynamic cursor parsing, variable binding, and randomized test data to prevent soft/hard parsing bottlenecks.
2. The Concurrency Orchestrator (DBMS_SCHEDULER): Spawns and manages asynchronous background worker threads, allowing effortless scaling across multiple CPU cores.
3. The Observability Engine (job_logs): Captures real-time metrics including execution start times, completion times, and total iterations per job thread.

Step 1: High-Efficiency Query Execution with DBMS_SQL

To measure true execution efficiency without incurring the overhead of PL/SQL static SQL context switching or hard parsing, we utilize the DBMS_SQL package.

By opening a dynamic cursor once, parsing the statement skeleton, and binding randomized search criteria during loop iterations, we isolate execution performance while mimicking realistic application behavior.

create or replace procedure run_sql_proc(p_iteration number, p_desc varchar2 default '') 

is 

  curid number; 

  sqltext varchar2(4000); 

  ret number; 

  l_counter number := 0; 

  l_mtn test.leadlist.mtn%type; 

  l_jobid number; 

begin

   -- Log execution start and obtain tracking ID

   insert into job_logs(job_id, executions, stime) 

   values (job_logs_seq.nextval, p_iteration, sysdate) 

   returning job_id into l_jobid;

   

   -- Define parameterized SQL statement

   sqltext := 'select /*test1*/ * from test.leadlist where mtn = :mtn';

   curid   := DBMS_SQL.OPEN_CURSOR; 

   

   loop 

       if l_counter > p_iteration then 

          exit; 

       end if;

       

       -- Generate random bind values to simulate realistic access patterns

       l_mtn := 'mtn' || to_char(round(10000 * dbms_random.value())); 

|---|

       DBMS_SQL.PARSE(curid, sqltext, DBMS_SQL.NATIVE); 

       DBMS_SQL.BIND_VARIABLE(curid, 'mtn', l_mtn); 

       ret := DBMS_SQL.EXECUTE_and_fetch(curid); 

       

       l_counter := l_counter + 1; 

  end loop; 

 

  dbms_sql.close_cursor(curid); 

 

  -- Log execution completion

  update job_logs 

  set etime = sysdate, description = 'Done - ' || p_desc 

  where job_id = l_jobid; 

end; 

/

Key Technical Considerations in run_sql_proc:

• Bind Variable Injection (:mtn): Prevents library cache lock contention by avoiding unique unparameterized SQL strings.
• Random Data Generation (DBMS_RANDOM): Simulates dynamic multi-user key lookups across the target table dataset.
• Single-Fetch Optimization (EXECUTE_and_fetch): Executes and fetches index lookup results in a single call to minimize overhead.

Step 2: Programmatic Concurrency Control via DBMS_SCHEDULER

To simulate multi-user workload patterns without maintaining open client terminal sessions, we leverage DBMS_SCHEDULER. The following submission script accepts a substitution variable (&&p_thread) to instantiate an exact number of parallel background jobs.

declare 

  l_counter number := 0; 

  l_msg varchar2(200); 

  l_job_action varchar2(400); 

begin 

  loop 

     l_counter := l_counter + 1; 

     if l_counter > &&p_thread then 

        exit; 

     end if;

     l_msg := 'local2-thread-' || &&p_thread || '_' || to_char(l_counter); 

     l_job_action := 'begin run_sql_proc(1000000,''' || l_msg || ''' ); commit; end;';

     dbms_scheduler.create_job(

         job_name   => 'RUN_SQL_JOB_' || to_char(l_counter), 

         job_type   => 'PLSQL_BLOCK', 

         job_action => l_job_action, 

         enabled    => true, 

         auto_drop  => true, 

         comments   => 'run_sql_job'

     );

     

     -- Optionally pin job threads to specific RAC instances

     dbms_scheduler.set_attribute('RUN_SQL_JOB_' || to_char(l_counter), 'INSTANCE_ID', '1');

  end loop; 

end; 

/

undefine p_thread

Step 3: Parametric Scalability Testing Execution Model

To evaluate database scalability under stress, tests should be executed in geometric thread increments: 1, 2, 4, 8, 16, 32, 64, and 128 threads.

Each thread runs a designated workload loop (e.g., 1,000,000 iterations per job). By adjusting &&p_thread, performance engineers can pinpoint exact saturation thresholds, latch bottlenecks, and throughput limits.

Test Environment Workflow:

1. Truncate or initialize the telemetry logging table (job_logs).
2. Execute the scheduler submission script with p_thread = 1.
3. Wait for job completion and collect timing metrics.
4. Repeat for higher thread counts (2, 4, 8, 16, 32, 64, 128).

Step 4: Tracking Metrics with Custom Instrumentation

A key requirement for database benchmarking is accurate timing. By capturing execution timestamps directly inside the procedure surrounding the workload loop, network latency and client-side logging delays are completely eliminated.

Performance Metrics Captured

• Average Job Completion Time (Minutes): Indicates how scaling concurrency impacts single-thread latency.
• Queries Per Second (QPS): Total aggregate database throughput across all active concurrent worker threads.

$$QPS = \frac{\text{Total Iterations Across All Threads}}{\text{Max Elapsed Duration (Seconds)}}$$

---

Secondary Case Study: Evaluating Partitioned Index Performance

To demonstrate this methodology in practice, the framework was deployed on an Oracle test database   to benchmark local versus global partitioned index access paths.

Test Dataset Setup

• Table: TEST.LEADLIST (Daily interval-partitioned table)
• Volume: 140,000 rows across 1,682 partitions
• Access Pattern: Index lookup by MTN key (select * from test.leadlist where mtn = :mtn)

 CREATE TABLE "TEST"."LEADLIST"

   (    "LEADLISTID" VARCHAR2(20),

        "MTN" VARCHAR2(10),

        "CREATEDATE" DATE DEFAULT sysdate,

        "LASTMODFIEDUSER" VARCHAR2(20)

   ) PCTFREE 20 PCTUSED 40 INITRANS 1 MAXTRANS 255

  STORAGE(

  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "USERS"

  PARTITION BY RANGE ("CREATEDATE") INTERVAL (NUMTODSINTERVAL(1,'DAY'))

 (PARTITION "PART_MIN"  VALUES LESS THAN (TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED

  PCTFREE 1 PCTUSED 40 INITRANS 1 MAXTRANS 255

 NOCOMPRESS LOGGING

  STORAGE(

  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "USERS" ) ;

insert into test.leadlist

(leadlistid, mtn, createdate,lastmodfieduser)

select

'aa' || to_char(level) ,

'mtn' ||  to_char(round(10000 * dbms_random.value())),

sysdate - 1682 * dbms_random.value(),

'user123'

from dual

connect by level <=140000

;

create index test.leadlist_ix1 on test.leadlist(mtn) local parallel 8;

alter index test.leadlist_ix1 noparallel;

create index test.leadlist_ix1 on test.leadlist(mtn) parallel 4;

alter index test.leadlist_ix1 noparallel;

Test Results Data

 Fig. 1 Average Job completion time vs Number  of Threads

Fig. 2 QPS vs Number  of Threads

Concurrent Threads	Global Index Avg Time (min)	Local Index Avg Time (min)	Global Index Total QPS	Local Index Total QPS
1 Thread	1.3	6.9	~15,000	~3,000
8 Threads	1.3	6.7	~100,000	~20,000
32 Threads	1.8	7.2	~290,000	~75,000
64 Threads	2.7	7.7	~402,000 (Peak)	~138,000
128 Threads	5.4	10.0	~396,000	~212,000

Benchmark Results Analysis & Observations

1. Completion Time Scaling: At 64 concurrent threads, jobs running against the Global Index completed in an average of 2.7 minutes, compared to 7.7 minutes for jobs using the Local Index.
2. Throughput Saturation: The Global Index configuration reached peak performance at 64 concurrent threads (~402,000 QPS). Beyond 64 threads, throughput leveled off due to CPU resource saturation.
3. Index Access Efficiency: Because the query predicate did not include the partition key (CREATEDATE), the Local Index required checking 1,682 individual index partitions per lookup, causing higher logical read overhead compared to a single B-tree lookup in the Global Index.

Summary

By combining DBMS_SQL for efficient dynamic SQL execution and DBMS_SCHEDULER for concurrent background process orchestration, database engineers can build a powerful, zero-dependency load testing framework directly inside Oracle. This native methodology provides precise, isolated performance insights free from network noise or third-party tool overhead.