Mittwoch, 7. Januar 2009

Instance parameters derived from cpu_count

Today I have a lot of pictures!
We got a brand new SUN T5140, and I managed to get my hands on it to run some tests.

Glenn Fawcett wrote some interresting entries in his blog about these so called chip multithreading CPUs. 2 should be mentioned here, as they had influence to this test-case:
Oracle's vision of multi-core processors on Solaris and Virtual CPUs effect on Oracle SGA allocations.
I wanted to know which parameters are derived from cpu_count.
A fresh installed and patched RDBMS was my victim.
I used this script to get my data:
#!/usr/bin/ksh  -x

integer i=1
while ((i <= 128));
  print " $i ";

  echo "*.compatible=''
*.cpu_count=$i"> initBERX2.ora

  echo "startup pfile='/appl/oracle/product/' ;

insert into init_parameters
select $i         ,
       a.ksppinm  ,
       b.ksppstvl ,
from x\$ksppi a, x\$ksppcv b, x\$ksppsv c
where a.indx = b.indx and a.indx = c.indx;


select distinct(\"CPUs\") from init_parameters;


exit;"> cpus/doit$i.sql

sqlplus "/ as sysdba" @cpus/doit$i.sql >cpus/log$i.log

sleep 5

  (( i = i + 1));

Afterwards I just had to get the interresting data out:
select /*+ PARALLEL */ i."CPUs" || ',' || i."Parameter" || ',' || i."InstanceValue"  
from init_parameters i  
where "Parameter" in (select distinct(i1."Parameter")
    from init_parameters i1,
       init_parameters i2
    where i1."Parameter" = i2."Parameter"
       and i1."CPUs" <> i2."CPUs"
      and i1."InstanceValue" <> i2."InstanceValue" )  
order by i."CPUs", i."Parameter";

and waste my time in excel creating the graphs:
(the whole spool output can be found here.)
I hope everyone enjoys these graphs. I'm sure there can be done much more tests, so if you have an interresting testcase which can be automated such as mine, feel free to contact me.
Kommentar veröffentlichen