My old website (www.jlcomp.demon.co.uk) will be disappearing in a couple of weeks – but there are a couple of timeless articles on it that are worth saving and a method for soaking up all the CPU on your system with a simple SQL statement against a small data set is, surely, one of them. Here, then is a little script that I wrote (or, at least, formalised) 15 years ago to stress out a CPU:
rem rem Script: kill_cpu.sql rem Author: Jonathan Lewis rem Dated: March 2001 rem Purpose: Exercise CPU and latches rem rem Last tested rem 18.104.22.168 rem 22.214.171.124 rem 126.96.36.199 rem 10.2.0.5 rem 10.1.0.4 rem 188.8.131.52 rem 184.108.40.206 rem rem Notes: rem The count(*) will return power((2,n-1)) rem To run from Oracle 9 and later we have to set parameter rem _old_connect_by_enabled = true; rem rem Base calculation (historical): rem ============================== rem Rule of thumb - ca. 10,000 logical I/Os per sec per 100 MHz of CPU. rem (Modern versions of Oracle on modern CPUs - about twice that, maybe rem due to the introduction of the "fastpath" consistent gets with the rem elimination of some logging activity that used to exist.) rem rem With the value of 23 shown we do 6M buffer visits of which 4M rem are "conistent read gets", and 2M are "buffer is pinned count". rem (That's power(2,23-1) and power(2,23-2) respectively). For each rem row you add to the kill_cpu table you double the run-time. rem rem This is an example of SQL that can take MUCH longer when run rem with rowsource_execution_statistics enabled. Mostly spent on rem CPU calling the O/S timer. (On my last test, using a 12c VM rem the time jumped from 6 seconds - 23 rows - to 75 seconds when rem I set statistics_level to all; but half would be the effect of rem running through the VM.) rem drop table kill_cpu; begin begin execute immediate 'purge recyclebin'; exception when others then null; end; begin execute immediate 'alter session set "_old_connect_by_enabled"=true'; exception when others then null; end; end; / create table kill_cpu(n, primary key(n)) organization index as select rownum n from all_objects where rownum <= 23 ;
execute snap_my_stats.start_snap set timing on set serveroutput off -- alter session set statistics_level = all; spool kill_cpu select count(*) X from kill_cpu connect by n > prior n start with n = 1 ; select * from table(dbms_xplan.display_cursor(null,null,'allstats last cost')); set serveroutput on execute snap_my_stats.end_snap spool off set timing off alter session set statistics_level = typical;
The calls to snap_my_stats use a package (owned by sys) that I wrote a long time ago for taking a snapshot of v$mystats; many people use Tanel Poder’s “Snapper” script or Tom Kyte’s script instead.