How to Determine your Oracle DB's I/O benchmark

[Legacy User]

If you look in your CP or WP logs, you will see something like this:

U0003533 UCUDB: Check of data source finished: No errors. Performance CPU/DB: '19652524'/'53'

U0003544 UCUDB: Reference values tested with Windows 2003 on XEON 1500 MHz: CPU 813865, DB 470

This tells you the CPU and database performance.

More information can be found in this technical article:  Performance problems and an explanation of U0003533

Question:
What is the exact operation statement being done that determines the Oracle DB performance numbers so that a DBA can run it against their database to do a performance analysis?

Answer:

The AE does an

INSERT INTO DIVDB and SELECT from DIVDB.

After 1 second, we check how often this was done.

With v12 sp1 this is changed and we do exactly 1000 INSERT and SELECT and provide the information how long this took.

DIVDB is a dummy table and documentation on the DB schema can be found here.

[Legacy User]

Hi Kim,

Sorry to be picky but this has nothing to do with the I/O´s of the database storage. It´s an overview of the transaction time within the startup of the AE. There is no commit at the end > no IO;

[Legacy User]

its an insert, select, rollback, so i can confirm no I/O, also its just as Harriet already said an dummy table so its only a general ballpark figure on the performance of the DB Driver and DB.

[Keld Mollnitz]

The information says "Reference values tested with Windows 2003". Is Windows 2003 still supported ??

[Legacy User]

Obviously not, but its not really important for these operations.
The first value is just how often the System could increment 1 by 1 in one second.
Both values are not too dependent on the OS.

But you are right it looks a bit strange/funny in the log.

[Eric Felker]

Thanks for posting this here Harriet_Kim_1764!

I'm curious what other customers see in their logs for the DB threshold. We find that our numbers are consistently much lower than the threshold of "470." Generally we hover in the 200-250 range, but have seen it drop to less than 100 during periods of high use/instability. We are currently on Oracle 11.2.0.3 with upgrade to 12c coming shortly.

[Legacy User]

From a support perspective, I'd love to see the full bell curve of benchmarks. I've seen over 1500, and I've also seen 7 (I'll let you guess which system was actually having issues :) ). 

From a very general perspective, it's a good way to see if the DB performance tweaking that DBA's perform has an impact. It, of course, isn't 100% correlation, but it is high enough that when I see that number jump up consistently I usually see less performance impact. 

[Michael_Rothrauff_7681]

In our V11 environment we pretty consistently see: U00003533 UCUDB: Check of data source finished: No errors. Performance CPU/DB: '24194934'/'75'.  What have been the biggest gains in the DB performance numbers?  Take in mind this has been the case since the environment has been created.

[Michael A. Lowry]

The correct link for the KB article is:

Performance problems and an explanation of U00003533

The link for the v21 DIVDB table schema description is:

DIVDB

[Keld Mollnitz]

From my understanding these numbers are not the whole truth, but are still a good indicator of the latency between the AE and the DB, e.g. it could be that your AE server is super fast and your DB is also super fast, but the network may still slow you down...

I would love if everyone would share their performance numbers here for comparison.

Here are my numbers:

20230621/180913.588 - U00003533 UCUDB: Check of data source finished: No errors. Performance CPU/DB: '30541055'/'131 (1000/7.589336 s)'
20230621/180913.588 - U00003544 UCUDB: Reference values tested with Linux x64 on XEON 3600 MHz: CPU 525716336, DB 3505

[Dominic Irudayasamy]

20230622/020052.130 - 20230320/094129.343 - U00003533 UCUDB: Check of data source finished: No errors. Performance CPU/DB: '405230187'/'744 (1000/1.342554 s)'

20230622/020052.130 - 20230320/094129.343 - U00003544 UCUDB: Reference values tested with Linux x64 on XEON 3600 MHz: CPU 525716336, DB 3505

------------------------------
Dominic I
------------------------------

[Darren Sniezak]

The test I'd always love to do (but can't here) would be the difference in performance between a process running directly on the DB server really minimizing the network part of the tests. I also have more than a little concern that the benchmark leapt from 470 to 3505; I'm still not sure what changes would have had that value spike that much within Broadcom or what impact that might have on general design in the future. 

20230622/170029.040 - U00003533 UCUDB: Check of data source finished: No errors. Performance CPU/DB: '217731329'/'340 (1000/2.934452 s)'
20230622/170029.040 - U00003544 UCUDB: Reference values tested with Linux x64 on XEON 3600 MHz: CPU 525716336, DB 3505

[Michael A. Lowry]

Be sure to check out this discussion too:

Description of DB performance check behind message U0003533