CPU-intensive script to test CPU performance in Oracle database

stress-cpu-Oracle.sql

SET SERVEROUTPUT ON;

DECLARE
  job_name         VARCHAR2(30);       
  cpu_count        NUMBER;            
  loop_iterations  NUMBER := 500000000;
  start_snap_id    NUMBER;        
  end_snap_id      NUMBER;             
  job_running      NUMBER;            

BEGIN
  SELECT VALUE INTO cpu_count
  FROM V$PARAMETER
  WHERE NAME = 'cpu_count';

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

  SELECT DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT INTO start_snap_id FROM DUAL;
  DBMS_OUTPUT.put_line('Start Snapshot ID: ' || start_snap_id);

  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;
    DBMS_SESSION.SLEEP(2); 

  LOOP
    SELECT COUNT(*)
    INTO job_running
    FROM DBA_SCHEDULER_RUNNING_JOBS
    WHERE JOB_NAME LIKE 'CPU_LOAD_JOB_%';

    EXIT WHEN job_running = 0;

    DBMS_SESSION.SLEEP(5); 
  END LOOP;

  SELECT DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT INTO end_snap_id FROM DUAL;
  DBMS_OUTPUT.put_line('End Snapshot ID: ' || end_snap_id);

END;
/