Oracle DBA – A lifelong learning experience

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

declare

l_job_out integer;

l_what dba_jobs.what%type;

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

dbms_job.submit(

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;'

);

commit;

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 );

end loop;

end;

/

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%';

 

About these ads

3 Responses to “Maxing out CPUs – script”

  1. […] 7-How to hammer your CPU ? John Hallas-Maxing out CPUs – script […]

  2. […] To generate load on the database I’ve got a script, hit_cpu.sql, that is based on one provided by one of our DBAs: […]

  3. Mohammed said

    Excellent!!

    Thanks for sharing useful details.. Appreciate your help.

    Mohammed

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 217 other followers

%d bloggers like this: