Benchmarks results stored to the database

I’ve just added some functionality to Swingbench that I probably should have added a long time ago. Now, every time you run a benchmark using Charbench, Swingbench or Minibench, the results will we saved to the database. This provides an archive of all of your benchmarks and makes it easy to perform comparisons between runs. These results can be trivially queried using SQL via Oracle’s support for dot notation. You will need an Oracle Database 19c or higher version for this functionality to work.

For example after running the SOE benchmark you can query the BENCHMARK_RESULTS table with a piece of SQL similar to the following

select *
from BENCHMARK_RESULTS

You should see something similar to

   ID RECORDING_TIME                  RECORDING_NAME                          RESULTS_JSON
_____ _______________________________ _______________________________________ ______________________
   61 14-APR-25 17.05.59.237226000    "Order Entry (PLSQL) V2" - Charbench    {"Results":{"xmlns":"http://www.dominicgiles.co....
   62 14-APR-25 17.07.03.775729000    "Order Entry (PLSQL) V2" - Charbench    {"Results":{"xmlns":"http://www.dominicgiles.co....
   63 14-APR-25 17.16.22.020068000    "Order Entry (PLSQL) V2" - Charbench    {"Results":{"xmlns":"http://www.dominicgiles.co....

Whilst this is useful we can use SQL support for JSON dot notation to look at the results in a little more detail..

select id,
       RECORDING_TIME,
       br.RESULTS_JSON.Results.Overview.AverageTransactionsPerSecond
from BENCHMARK_RESULTS br;

The dot notation syntax enables us to navigate through the hierarchy of the JSON document. In this instance we are using to retrieve the average transactions per second as shown in the snippet of the json document.

{
  "Results": {
    "xmlns": "http://www.dominicgiles.com/swingbench/results",
    "Overview": {
      "Comment": "Simple Order Entry benchmark using client side jdbc calls",
      "TotalLogonTime": "0:00:00",
      "TotalRunTime": "1:00:00",
      "TotalCompletedTransactions": "145018",
      "TotalFailedTransactions": "0",
      "BenchmarkName": "Order Entry (jdbc)",
      "AverageTransactionsPerSecond": "40.28",
      "TimeOfRun": "8 Apr 2025, 12:21:19",
      "MaximumTransactionRate": "3674"
    },

Using a similar approach we can compare the difference in performance between each run.

select br.id,
       br.RECORDING_TIME,
    json_value(br.RESULTS_JSON, '$.Results.Overview.AverageTransactionsPerSecond' RETURNING NUMBER) AS AverageTPS,
       ROUND(
               (
                   json_value(br.RESULTS_JSON, '$.Results.Overview.AverageTransactionsPerSecond' RETURNING NUMBER) -
                   LAG(json_value(br.RESULTS_JSON, '$.Results.Overview.AverageTransactionsPerSecond' RETURNING NUMBER), 1)
                       OVER (ORDER BY RECORDING_TIME)
                   ) /
               NULLIF(
                       LAG(json_value(br.RESULTS_JSON, '$.Results.Overview.AverageTransactionsPerSecond' RETURNING NUMBER), 1)
                       OVER (ORDER BY RECORDING_TIME), 0
               ) *
               100, 2
       ) "Percentage Difference from Previous"
from BENCHMARK_RESULTS br

Which gives us the following.

   ID RECORDING_TIME                     AVERAGETPS    Percentage Difference from Previous 
_____ _______________________________ _____________ ______________________________________ 
   86 15-APR-25 13.36.03.438917000           183.27                                        
   87 15-APR-25 13.36.41.229173000           284.13                                  55.03 
   88 15-APR-25 13.37.37.215548000            656.6                                 131.09

It’s also possible to de-nest the results from the individual transactions using SQL syntax similar to the following.

SELECT jt.* FROM SOE.BENCHMARK_RESULTS b,
    JSON_TABLE (
            b.RESULTS_JSON FORMAT JSON,
            '$.Results.TransactionResults.Result[*]'
            COLUMNS (
                id VARCHAR2(255) PATH '$.id',
                AverageResponse NUMBER PATH '$.AverageResponse',
                FiftythPercentile NUMBER PATH '$.FiftythPercentile'
                )
    ) jt
where b.ID = 88;

Which produces the following output for the JSON Document with an id of 88

ID                               AVERAGERESPONSE    FIFTYTHPERCENTILE 
__________________________ _____________________ ____________________ 
Customer Registration          13.95774075579033                   10 
Update Customer Details        6.322704081632653                    3 
Browse Products                7.997188139059305                    5 
Order Products                 17.94081893793986                   13 
Process Orders                 12.31829268292683                    9 
Browse Orders                 10.467011642949547                    8 

The introduction of JSON adds a huge amount of flexibility to analyzing the results of benchmark runs without the need to parse XML or JSON files.

Other than this feature you can now also ask for the results file in json format with the new -oj command line parameter (defaults to xml). There’s also some fixes and the inclusion of the latest Oracle Database 23ai jdbc drivers.

You can download the latest version here or from github

• 2025 1
• 2024 1
• 2023 5
• 2022 8