How Fast Are My Database Server CPUs?

Posted on 25-Aug-2016 by Craig Shallahamer, craig@orapub.com

Ever wonder how your CPU speeds compare from one Oracle Database system to another? And since we are interested in performance on an Oracle system we need to factor in all the various components that impact performance, such as the hardware, operating system, Oracle release and the various versions and patches.

Are you completely and totally overwhelmed yet?

How to honestly and quickly do this is what this post is all about.

It's Not Easy. But It's Very Useful.

Insanity comes to mind, because I am not interested in an industry published benchmark. I need a way to compare CPUs speeds in any Oracle environment. What I would like is to create a single Oracle-focused "speed" number enabling an "apples to apples" comparison of CPU speed from one Oracle system to another.

So, how do I honestly compare CPU speeds?

In a nutshell, the way I do this is by determining and then comparing how long it takes Oracle to serially process a single "buffer get" in each environment I want to compare. The more buffers Oracle can touch in a millisecond, the "faster" the CPU. Period. It's not a comprehensive test, but it's not supposed to be.

This Is Not About Scalability Or Parallelism

Keep in mind this type of CPU speed test is not an indicator of scalability (i.e., benefit of adding additional CPUs) in any way shape or form. It is simply a measure of brut force Oracle buffer cache logical IO processing capability based on a number of factors. If you are architecting a system, other tests will be required.

However, a relative CPU speed value is incredibly useful when doing:

It's All About The Numbers

It's actually very simple. On "Server A" in SQL*Plus you run the speed test with a result of 15.933. I call the result the "speed factor." Now run the speed test on "Server B" to get its speed factor, for example 8.432.

Without a doubt, when it comes to processing Oracle buffers, Server B's CPUs are about twice as fast as the Server A CPUs. So, if your "all about CPU" SQL runs in 10 seconds on Server A, it will run in around 5 seconds on Server B... even though they may have very different patch levels, Oracle versions or even different operating systems. Powerful stuff!

Will the SQL on Server B always run around 5 seconds? No. At times, the other system could have a CPU bottleneck or Oracle serialization issues. But unconstrained, the SQL on Server B should run in around 5 seconds. I'll write more about this in another post.

What Is The "Speed Factor"

This "speed factor" is simply a measurement about how fast Oracle can process buffers in memory. There are many advantages: a real load is not required or even desired, real Oracle code is being run at a particular version, real operating systems are being run and the processing of an Oracle buffer highly correlates with CPU consumption. Very cool.

As you might expect, I have a free tool you can download to determine the speed factor. I recently updated it to be more accurate, requires no DML or DDL, require less Oracle privileges, and also shows the execution plan of the tool's SQL. If the execution plan used in the speed tool is different on the various systems, then obviously we can't expect the "true speeds" to be comparable.

You can download my free tool HERE.

Running The Tool

Download the software HERE, decide how many samples you want to collect (start with perhaps 5) and do it!

Now I know some of you reading this would rather not read the text below. So, I created a short video for you about how to download, install and use the CPU Speed Test tool. Enjoy!

For those of you who would like to study the text, here is an example I ran today.

SQL> @op-cpu-speed-test-3a.sql 5
.
OraPub Speed Test 3. Enjoy but use at your own risk.
.
..........................................................................
To reduce the impact of bring blocks into the buffer cache, the first two
samples are not included in the final calculation. Also, if there is any
physical IO activity, the sample is not included in the final calculation.

If the average CPU utilization during the testing period is > 70%
the results would be considered invalid because the results may include OS
CPU queuing.

There will be 5 samples collected during the test.
..........................................................................
.
.
Caching table and then will begin test...
.
..........................................................................
... RAW OUTPUT (keep the output for your records)
..........................................................................
.
sample#,  delta_lio_v ,  delta_pio_v,  delta_time_s_v, ms_lio_v, delta_dbcpu_ms_v, dbcpu_ms_lio_v
.
1, 17660, 0, .378239, , 0,
...sample 1 will be NOT included in the final calculation.
2, 17644, 0, .356577, , 0,
...sample 2 will be NOT included in the final calculation.
3, 17644, 0, .356632, .020213, 1487.072, 84.282022
...sample 3 will be included in the final calculation.
4, 17644, 0, .356371, .020198, 0, 0
...sample 4 will be included in the final calculation.
5, 17644, 0, .329438, .018671, 0, 0
...sample 5 will be included in the final calculation.
.
The test is complete.
.
..........................................................................
... Results
..........................................................................
.
Speed Factor (higher is faster) : 50.777  <----
.
Supporting Details:
samples_v = 3 sum_walltime_ms_per_lio_v = .059082
avg_walltime_ms_per_lio_v = .019694
The speed factor = 1 / avg_walltime_ms_per_lio_v
.
The test SQL_ID is 8mphjcd631nd9
..........................................................................

PL/SQL procedure successfully completed.


NAME            DBID INSTANCE_NUMBER INSTANCE_NAME    PLATFORM_NAME        IPADDR               HOST_NAME
--------- ---------- --------------- ---------------- -------------------- -------------------- --------------------
PROD50      83498542               1 prod50           Linux x86 64-bit                          sixcore

.
To see the plan for the test SQL, enter the test SQL_ID shown above, when prompted below.
When comparing multiple systems, usually you want to ensure the plans are the same.
.
Enter value for test_sql_id_v: 8mphjcd631nd9

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  8mphjcd631nd9, child number 0
-------------------------------------
SELECT * FROM ALL_OBJECTS WHERE ROWNUM < 20000

Plan hash value: 2433918929

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                    |       |       | 81555 (100)|          |
|*  1 |  TABLE ACCESS BY INDEX ROWID         | SUM$               |     1 |     8 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN                  | I_SUM$_1           |     1 |       |     0   (0)|          |
|*  3 |  TABLE ACCESS FULL                   | USER_EDITIONING$   |     1 |     6 |     2   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID BATCHED| OBJ$               |     1 |    30 |     3   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN                  | I_OBJ1             |     1 |       |     2   (0)| 00:00:01 |
...

In the actual run above, the "speed factor" is 50.777. Today, a colleague ran the speed test on one of his systems and the speed factor was 9.763033. Then again on another box with a result of 10.632243. You can see there is quite a difference... which is why I did not publicly post the hardware and software details.

Give the CPU Speed Test tool a try on a couple of your Oracle systems. Thinking about the results usually brings some deep insights into your Oracle systems. You may even get an "Ah Ha" moment like, "Oh... that's why this SQL runs longer on that system!"

Thanks for reading and all the best in your Oracle performance work!

Craig.

If you have any questions or comments, feel free to email me directly at craig at orapub.com.

11 Ways To Get Your Oracle Database Conference Abstract Accepted Oracle Database SQL Statement Elapsed Times How To Approach Different Oracle Database Performance Problems