Re: CPU speed comparison
- From: Tanel Põder <tanel.poder.003@xxxxxxx>
- To: "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 23 Dec 2005 13:36:18 -0600
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:
