My LIO silly little benchmark

I have been working on a benchmark for LIO.  I know there are TPC and TPH transactions numbers that are published, on CPU speeds, but how much does that directly releate to LIO's, the heart of an Oracle database ?

  To help benchmark, I wrote a little pl/sql package.  This packages takes the Zip Code database, and randomly picks  some rows with a cursor (about 1 % of the table).  This package is then called by swing bench, and I put a "think time" in it for each execution of the package.

Ideally, I try to execute it up to what the Server can handle.. This was especially usefull with the benchmarking I did in a previous post on hyperthreading.

I was interested in what anyone else does ?  I try to do a LIO lookup, and compare numbers between servers.  By doing this I have a pretty good idea how many LIO's an AMD server can handle per second, an Intel server can do, and different architectures (2 socket, 4 socket, and 8 socket).. I even benchmark virutalization to see how much of an overhead is caused from the Software.

This may not be the best way (it excludes what happens with updates (redo logs etc), and how much physical I/O's affect the workload.

Any ideas would be appreciated.  I would love to come up with a nice reproducable benchark, and then maybe create a dbcapute of it, and do a dbreplay on different architectures ? Would that be more accurate.

I know many of you will say the line "well it depends on the workload", maybe the benchmarking that comes with swingbench is good enough ??

I'm just tired of reading server bencharks, and finding that for an oracle database, those benchmarks aren't very meaningful.

I would also love to do some benchmarking with Solaris X86, and RHEL/OEL on an 8 socket box.

I would also love to learn what anyone else has learned ?  I am especially interested how 8 socket intel servers compare with 2 socket. I'm seeing some pretty increadable numbers from 2 socket servers (almost 2x the speed of 8 socket).  I'm wondering if anyone else is seeing some measureable differences.

I'm starting to move to "go wide"  camp rather than go high camp for increasing server power.  The blade servers are being more, and more powerful, and you can have more memory local to the CPU. Increasing CPU sockets just increases hops to get those LIO's done, costing time, waits, latches. etc. etc.

So here is a piece of my LIO benchmark...

CREATE TABLE "KILLER" ("CC_ID" NUMBER(20, 0) NOT NULL ENABLE)  ;

/*  import 55,000 rows of distinct data */
CREATE PROCEDURE          kill_lio IS
   my_count number := 1;
   my_executions number;
   my_buffer_gets number
   my_cpu_time number;
   my_elapsed_time number;

error_code number;

BEGIN
for i in 1..10000 LOOP


select count(distinct cc_id) into my_count from kill_lio.killer;

end loop;

select executions,buffer_gets,cpu_time,elapsed_time into my_executions,my_buffer_gets,my_cpu_time,my_elapsed_time 
from sys.v_$sqlstats where sql_id='2j5tvp5rdzmym';
 
 dbms_output.put_line('exectutions:                          ' || to_char(my_executions,'999,999,999'));"
dbms_output.put_line('buffer gets:                          ' || to_char(my_buffer_gets,'999,999,999'));"
dbms_output.put_line('cpu time:                             ' || to_char(my_cpu_time,'999,999,999'));"
dbms_output.put_line('elapsed time:                         ' || to_char(my_elapsed_time,'999,999,999'));"
dbms_output.put_line('elapsed time per execution(ms)   :      ' ||to_char( my_elapsed_time/my_executions/1000,'999,999.9'));"
dbms_output.put_line('buffer_gets/second:                   ' ||to_char( my_buffer_gets/(my_elapsed_time/1000000),'999,999,999'));"

END;  -- exception handlers

and here is the output I use to compare.  I look at the average elapsed time, and buffer_gets/second to benchark systems.

executions:                                 10,000
buffer gets:                             1,190,000
cpu time:                              190,983,974
elapsed time:                          191,374,061
elapsed time per execution(ms)    :           19.1
buffer_gets/second:                          6,218

Here is the AWR report from the execution