Maxing out CPUs – script
Posted by John Hallas on November 19, 2009
I have long subscribed to the ORACLE-L mailing list and I find it a great source of ideas and views on the management of Oracle databases. As a sidenote for anybody who used it in the past it seems to be much stronger now as a community than previously where there was too much RTFM and other flaming type responses.
In the last couple of days there has been a thread running entitled Stress my CPU’s started by Lee Robertson where he was asking for a way to ‘hammer the CPU’s on the box as we want to test dynamically allocating CPU’s from another partition to handle the increased workload’.
The strength of the list is that there were a number of quality responses but my hat goes off to Tom Dale for producing this gem
set serveroutput on
l_cpus_to_hog CONSTANT integer :=4;
l_loop_count varchar2(10) := '500000000'; begin
** Create some jobs to load the CPU
for l_job in 1..l_cpus_to_hog loop
job => l_job_out
, what => 'declare a number := 1; begin for i in 1..'||l_loop_count||' loop a := ( a + i )/11; end loop; end;'
dbms_output.put_line( 'job - '|| l_job_out );
select what into l_what from dba_jobs where job = l_job_out;
dbms_output.put_line( 'what - '|| l_what );
Short, sweet and very effective. I will certainly be using it when I want to look at using resource management.
PS, if you want to stop the jobs running, although they do finish in a few minutes using the default value of 500 million iterations, then use the following dynamic sql.
select 'execute dbms_job.remove('||job||');' from user_jobs where what like 'declare a number := 1%';