Re: CPU speed comparison

Hi,

Depending on how big your system's will grow, you might not want to worry about 
CPU speeds at all, but scalability instead (e.g. what's the memory bus speed 
and bandwidth, how many concurrent snoop (for detecting potential memory lines 
in other CPU's caches) or memory operations can be done on bus etc.

Btw, this script is not testing just LIOs, it's testing single row calls from 
PL/SQL loop (which might be heavily optimized in 10g), thus it's not good for 
benchmarking plain LIOs (as most of the time could be spent in PL/SQL engine 
instead..)

I would build a sample table with representative data set, with representative 
indexes, perform queries which would be ran in production and made some 
conclusions from there, e.g.

Indexed unique single-row access path -> X LIOs per query, query completed in X 
sec
Indexed range scan (returning constant number of rows), X LIOs per query -> 
query completed in X sec
Full table scan (returning all or no rows) -> X LIOs per query -> query 
completed in X sec
Nested loop to child table using indexed access path (as this can have few 
optimizations which affect LIO resource usage, like keeping buffers pinned if a 
next visit is believed to come immediately)
and so on.

Also, you should keep your reference data and queries constant, even the column 
ordering or predicate ordering in SQL query might affect CPU usage for LIOs.

And you got to do all of this on multiple CPUs in parallel -> there might be 
several scalability differences across platforms/versions, such shared read 
only cache buffer chain latch gets etc.

You could go from 1 serial job to CPU_COUNT * 3 parallel ones increasing number 
of jobs by one and measuring the curve how performance degrades. That could 
give you a rough idea 

Tanel.

  ----- Original Message ----- 
  From: Sriram Kumar 
  To: ORACLE-L 
  Sent: Friday, December 23, 2005 12:07 PM
  Subject: CPU speed comparison


  Hi Gurus,


  Firstly Wish you all Merry Christmas.

     We have a banking application and I am in the process of  doing a CPU 
Capacity prediction of the DB server based on the Ratio Modelling Technique. I 
have arrived at the CPU requirements for particular platform(Say Itanium2) and 
now I want to arrive at the CPU numbers for the other Server platforms(PA-RISC, 
IBM PowerV/VI, Intel Xeon, Ultrasparc IV/III). 

  Say for a given volume set I arrive at a 2 CPU Itanium2, Now how do I arrive 
at CPU numbers for the other platforms. Any inputs/pointers would very much be 
appriciated.

  I looked at http://home.comcast.net/~arivenes/sql/cpu_test.sql and feel that 
we can use this script for the said comparison provided the underying 
assumption is correct. The scripts basic assumption is using LIO's to determine 
the CPU usage . Run this script in various platforms and  use the CPU usage 
figure across platforms to arrive at the comparitive numbers. Does this 
approach sound logical? 

  In this effect, I remembered rading Cary's papaer "Why You Should Focus on 
LIOs Instead of PIOs" and I found the following comment from the author

  <Quote>
  The actual use of Oracle block content is the number one consumer of CPU 
capacity on any reasonably well optimized Oracle system. Your average LIO 
latency will vary, depending primarily upon your CPU speed and the total number 
of machine instructions required to parse the content of an Oracle block 
  <Unquote>

  Thanks and Regards

  Sriram Kumar

Other related posts: