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   

  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   

  Thanks and Regards

  Sriram Kumar Received on Fri Dec 23 2005 - 20:36:18 CST