CPU-intensive query to simulate CPU load in Oracle database

Introduction

Recently, a question was raised on the Oracle-L Mailing List about utilities to quickly load database CPU utilization to 100%. Tom Dale provided the blog post Maxing out CPUs – script. Although slightly dated, the script remains relevant and effective for its purpose. However, given that DBMS_JOB has been deprecated since Oracle 12cR2, I decided to modernize it by leveraging DBMS_SCHEDULER.

Additionally, I found Julian Dyke’s single-core testing script, which provides a targeted approach to stress-testing a CPU core. I have combined these resources and created a comprehensive script to maximize CPU utilization of an Oracle database.

Database performance testing often involves creating controlled workloads to evaluate system limits or optimize configurations. One such scenario is generating a CPU-intensive workload to assess database server CPU performance mostly speed. In this blog post, I will provide a PL/SQL script that utilizes Oracle’s DBMS_SCHEDULER to create and execute CPU-bound jobs.

The CPU is inherently designed to process tasks in a time-sliced, serve-and-go model. It prioritizes active workloads, serving them as computational resources become available, and queues tasks in a ready-to-run state when resources are occupied. Testing CPU performance often involves artificially generating workloads that mimic this queuing process. Such tests essentially measure the CPU’s ability to handle demand under simulated contention, where processes either execute immediately or wait their turn in the scheduler’s queue.

Database servers are often benchmarked for Transactions Per Second (TPS) and Input/Output Operations Per Second (IOPS), leveraging tools like SLOB (Simple Database I/O Testing Toolkit for Oracle). These benchmarks mostly focus on storage and transaction performance. Database performance expert Kevin Closson has detailed various approaches to effectively use SLOB for I/O-centric testing in his blog. Also, the CALIBRATE_IO procedure within Oracle’s DBMS_RESOURCE_MANAGER package can be used to test the I/O capabilities of storage.

However, in scenarios such as hardware procurement, capacity planning or bottleneck identification, measuring CPU performance directly can also be a target.

No table is required, as no data will be retrieved. Instead of focusing on I/O operations, this test is designed to emphasize CPU usage. The core idea involves executing simple calculations to maximize CPU utilization.

Short version:

SET SERVEROUTPUT ON;

DECLARE
  job_name         VARCHAR2(30);      
  cpu_count        NUMBER := 4;          
  loop_iterations  NUMBER := 500000000; 

BEGIN
  FOR i IN 1..cpu_count LOOP
    job_name := 'CPU_LOAD_JOB_' || i;

    DBMS_SCHEDULER.create_job(
      job_name        => job_name,
      job_type        => 'PLSQL_BLOCK',
      job_action      => 'DECLARE num NUMBER := 1; BEGIN FOR idx IN 1..' || loop_iterations || 
                         ' LOOP num := MOD(num + SQRT(idx), 13); END LOOP; END;',
      enabled         => TRUE
    );

    DBMS_OUTPUT.put_line('Created job: ' || job_name);
  END LOOP;
END;
/

I have also provided an enhanced version of the script that captures AWR snapshots both before and after the CPU stress test. This version dynamically adjusts the workload to align with the number of available CPUs, ensuring maximum utilization and also a little bit logging. If the workload exceeds the CPU count, some tasks will naturally wait for CPU resources to become available.

Enhanced Version:

Here’s a quick demonstration using Oracle Database Version 23.5 running on Docker. You may refer to my blog post for “Install Oracle Database 23ai and Sample Schemas on Macbook M Series (Apple Silicon)” for creating a test environment.

osmandinc@192 ~ % sql  system/InsaneDBAiscool@localhost:1426/FREEPDB1

SQLcl: Release 24.3 Production on Tue Dec 10 00:06:41 2024

Copyright (c) 1982, 2024, Oracle.  All rights reserved.

Last Successful login time: Tue Dec 10 2024 00:06:42 +03:00

Connected to:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.5.0.24.07

SQL> SET SERVEROUTPUT ON;

DECLARE
  job_name         VARCHAR2(30);       -- Variable for job names
  cpu_count        NUMBER;             -- Number of CPUs from the database
  loop_iterations  NUMBER := 500000000; -- Number of iterations for the workload
  start_snap_id    NUMBER;             -- Snapshot ID at the start
  end_snap_id      NUMBER;             -- Snapshot ID at the end
  job_running      NUMBER;             -- Jobs still running

BEGIN
  -- Fetch the number of CPUs from the database parameter
  SELECT VALUE INTO cpu_count
  FROM V$PARAMETER
  WHERE NAME = 'cpu_count';

  DBMS_OUTPUT.put_line('Number of CPUs detected: ' || cpu_count);

  -- Take the initial AWR snapshot
  SELECT DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT INTO start_snap_id FROM DUAL;
  DBMS_OUTPUT.put_line('Start Snapshot ID: ' || start_snap_id);

  -- Create a CPU-intensive job for each CPU
  FOR i IN 1..cpu_count LOOP
    job_name := 'CPU_LOAD_JOB_' || i;

    -- Create a scheduler job
    DBMS_SCHEDULER.create_job(
      job_name        => job_name,
      job_type        => 'PLSQL_BLOCK',
      job_action      => 'DECLARE num NUMBER := 1; BEGIN FOR idx IN 1..' || loop_iterations || 
                         ' LOOP num := MOD(num + SQRT(idx), 13); END LOOP; END;',
      enabled         => TRUE
    );

    DBMS_OUTPUT.put_line('Created job: ' || job_name);
  END LOOP;
    DBMS_SESSION.SLEEP(2); -- Wait for 2 seconds before checking

  -- Wait for all jobs to complete
  LOOP
    SELECT COUNT(*)
    INTO job_running
    FROM DBA_SCHEDULER_RUNNING_JOBS
    WHERE JOB_NAME LIKE 'CPU_LOAD_JOB_%';

    EXIT WHEN job_running = 0;

    DBMS_OUTPUT.put_line('Jobs still running: ' || job_running || '. Waiting...');
    DBMS_SESSION.SLEEP(1); -- Wait for 1 seconds before checking again
  END LOOP;

  -- Take the final AWR snapshot
  SELECT DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT INTO end_snap_id FROM DUAL;
  DBMS_OUTPUT.put_line('End Snapshot ID: ' || end_snap_id);

END;
/

Number of CPUs detected: 2
Start Snapshot ID: 154
Created job: CPU_LOAD_JOB_1
Created job: CPU_LOAD_JOB_2
Jobs still running: 2. Waiting...
Jobs still running: 2. Waiting...
Jobs still running: 2. Waiting...
Jobs still running: 2. Waiting...
Jobs still running: 2. Waiting...
Jobs still running: 2. Waiting...
Jobs still running: 2. Waiting...
Jobs still running: 2. Waiting...
Jobs still running: 2. Waiting...
Jobs still running: 2. Waiting...
Jobs still running: 2. Waiting...
Jobs still running: 2. Waiting...
Jobs still running: 2. Waiting...
Jobs still running: 2. Waiting...
Jobs still running: 2. Waiting...
Jobs still running: 2. Waiting...
Jobs still running: 2. Waiting...
Jobs still running: 2. Waiting...
Jobs still running: 2. Waiting...
Jobs still running: 2. Waiting...
Jobs still running: 2. Waiting...
Jobs still running: 2. Waiting...
Jobs still running: 2. Waiting...
Jobs still running: 2. Waiting...
Jobs still running: 2. Waiting...
Jobs still running: 2. Waiting...
Jobs still running: 2. Waiting...
Jobs still running: 2. Waiting...
Jobs still running: 2. Waiting...
Jobs still running: 2. Waiting...
Jobs still running: 2. Waiting...
Jobs still running: 2. Waiting...
Jobs still running: 2. Waiting...
Jobs still running: 2. Waiting...
Jobs still running: 2. Waiting...
Jobs still running: 2. Waiting...
Jobs still running: 2. Waiting...
Jobs still running: 2. Waiting...
Jobs still running: 2. Waiting...
Jobs still running: 2. Waiting...
Jobs still running: 2. Waiting...
Jobs still running: 2. Waiting...
Jobs still running: 2. Waiting...
Jobs still running: 2. Waiting...
Jobs still running: 2. Waiting...
Jobs still running: 2. Waiting...
Jobs still running: 2. Waiting...
Jobs still running: 2. Waiting...
Jobs still running: 2. Waiting...
End Snapshot ID: 155


PL/SQL procedure successfully completed.

Actually, The process completes in 4 – 5 minutes. If you need to cancel the jobs, you may use commands provided below.

DECLARE job_name VARCHAR2(30);
-- Variable for job names
cpu_count NUMBER;
-- Number of CPUs from the database
BEGIN -- Fetch the number of CPUs from the database parameter
SELECT 
  VALUE INTO cpu_count 
FROM 
  V$PARAMETER 
WHERE 
  NAME = 'cpu_count';
-- Stop the CPU-intensive jobs for each CPU
FOR i IN 1..cpu_count LOOP job_name := 'CPU_LOAD_JOB_' || i;
DBMS_SCHEDULER.STOP_JOB(
  job_name => job_name, 
  force => TRUE -- Optional, forces the job to stop even if it is running
  );
END LOOP;
END;
/

Docker stats while the test is going on :

osmandinc@192 ~ % docker stats oracle-free
CONTAINER ID   NAME          CPU %     MEM USAGE / LIMIT     MEM %     NET I/O          BLOCK I/O         PIDS
0bbd6422dac9   oracle-free   198.36%   2.963GiB / 7.655GiB   38.71%    903kB / 2.11MB   6.44GB / 9.29GB   82
0bbd6422dac9   oracle-free   200.69%   2.963GiB / 7.655GiB   38.71%    903kB / 2.11MB   6.44GB / 9.29GB   82
0bbd6422dac9   oracle-free   194.75%   2.963GiB / 7.655GiB   38.71%    903kB / 2.11MB   6.44GB / 9.29GB   82
...

During the test, CPU usage peaked at 200%, indicating that 2 CPUs were utilized heavily. Despite having 8 CPUs, the CPU usage did not reach 800%. This behavior is explained in the Side Note section.

Scheduler Running jobs output while the test is going on :

SQL> select owner,job_name,SESSION_ID, RUNNING_INSTANCE, ELAPSED_TIME, CPU_USED from dba_scheduler_running_jobs;

OWNER     JOB_NAME             SESSION_ID    RUNNING_INSTANCE ELAPSED_TIME           CPU_USED
_________ _________________ _____________ ___________________ ______________________ ______________________
SYSTEM    CPU_LOAD_JOB_1              205                   1 +00 00:04:06.222609    +00 00:00:00.000000
SYSTEM    CPU_LOAD_JOB_2              204                   1 +00 00:04:06.222604    +00 00:00:00.010000

Let’s investigate the AWR report of that period and validate the CPU usage.

SQL> select dbid from v$pdbs where name='FREEPDB1';

         DBID
_____________
   1858529453

SQL> SPOOL awr_report.html
SQL> SELECT output FROM table(dbms_workload_repository.AWR_REPORT_HTML(1858529453,'1',154,155));
SQL> SPOOL OFF

We can validate the CPU utilization during the workload execution by examining DB Time and Elapsed Time. Additionally, a small amount of resmgr:cpu quantum wait events were observed. These wait events indicate that sessions are waiting to be allocated CPU resources. This event occurs when the resource manager is enabled and is throttling CPU consumption.

Side Note:

By default, each container can access all the CPUs on the host machine. For example, on a MacBook Air M1 with 8 CPU cores, you would expect the database to detect and utilize all 8 CPUs. However, when I queried the CPU_COUNT parameter in the database (FREEPDB1), it returned a value of 2 instead of 8.

Upon further investigation, I discovered that this limitation is due to a restriction in Oracle Database 23ai Free – Developer Release. This edition intentionally caps the CPU_COUNT to 2, regardless of the host machine’s total CPU capacity. This constraint is part of the free version’s resource limitations designed for lightweight development and testing environments.

Oracle Database Free CPU Limitations

Oracle Database Free limits itself automatically to two cores for processing. For example, on a computer with 2 dual-core CPUs (four cores), if a large number of database clients try to simultaneously run CPU-intensive queries, then Oracle Database Free will process the queries at the rate of just two cores even if more CPU capacity is available.

Conclusion :

Results confirm that the database effectively utilizes its allocated CPU resources under the constraints imposed by Oracle Database 23ai Free – Developer Release.

Hope it helps.