Using the “Nearly Infinite” Query Method to Map CPU Intensive Operations in Oracle

Some time ago I posted a note about a variant of a hierarchical query that takes a very long time to execute (almost indefinitely) and does not consume any database resources other than CPU.

A natural idea was to use this method to compare CPU intensive tasks in Oracle DB. For example, if we embed such a CPU-hungry function into our SQL query execution pipeline so that it is executed more than a billion times, we get the opportunity to compare CPU costs in different cases.

In this case, we will compare the performance of different hashing functions in Oracle: ORA_HASH and various options STANDARD_HASH.

We execute queries of the following type:

Baseline does not perform additional functions on the CPU:

select * from (select distinct id
               from (select 'abcdef' id from dual
                     union all
                     select 'fgrjk' from dual)
               connect by level <= 3e1);

Query with ORA_HASH:

select * from (select distinct ora_hash(id,1111113)
               from (select 'abcdef' id from dual
                    union all
                    select 'fgrjk' from dual)
              connect by level <= 3e1);

Query with hash calculation using one of the standard methods:

select * from (select distinct STANDARD_HASH(id,'SHA384') 
               from (select 'abcdef' id from dual 
                   union all 
                   select 'fgrjk' from dual) 
               connect by level <= 3e1);

A typical SQL Monitor report for such queries:

SQL Monitoring Report
SQL Text
select * from (select distinct STANDARD_HASH(id,'SHA256') from (select 'abcdef' id from dual union all select 'fgrjk' from dual) connect by level <= 3e1)
Global Information
Status              :  DONE (ALL ROWS)
Instance ID         :  2
Session             :  REMIZOV (509:24232)
SQL ID              :  fwhbynu8jtfr3
SQL Execution ID    :  33554432
Execution Started   :  07/16/2024 10:11:47
First Refresh Time  :  07/16/2024 10:11:53
Last Refresh Time   :  07/16/2024 11:35:26
Duration            :  5019s
Module/Action       :  PL/SQL Developer/SQL Window
Service             :  ZZZZZZ
Program             :  plsqldev.exe
Fetch Calls         :  1
Global Stats
| Elapsed |   Cpu   |  Other   | Fetch |
| Time(s) | Time(s) | Waits(s) | Calls |
|    5019 |    4964 |       55 |     1 |
SQL Plan Monitoring Details (Plan Hash Value=682020348)
| Id |                 Operation                 | Name |  Rows   | Cost |   Time    | Start  | Execs |   Rows   |  Mem  | Activity | Activity Detail |
|    |                                           |      | (Estim) |      | Active(s) | Active |       | (Actual) | (Max) |   (%)    |   (# samples)   |
|  0 | SELECT STATEMENT                          |      |         |      |         1 |  +5019 |     1 |        2 |     . |          |                 |
|  1 |   VIEW                                    |      |       2 |    5 |         1 |  +5019 |     1 |        2 |     . |          |                 |
|  2 |    HASH UNIQUE                            |      |       2 |    5 |      5020 |     +0 |     1 |        2 | 507KB |    86.30 | Cpu (4327)      |
|  3 |     CONNECT BY WITHOUT FILTERING (UNIQUE) |      |         |      |      5014 |     +6 |     1 |       2G |  2048 |    13.70 | Cpu (687)       |
|  4 |      VIEW                                 |      |       2 |    4 |         1 |     +6 |     1 |        2 |     . |          |                 |
|  5 |       UNION-ALL                           |      |         |      |         1 |     +6 |     1 |        2 |     . |          |                 |
|  6 |        FAST DUAL                          |      |       1 |    2 |         1 |     +6 |     1 |        1 |     . |          |                 |
|  7 |        FAST DUAL                          |      |       1 |    2 |         1 |     +6 |     1 |        1 |     . |          |                 |
=======================================================================================================================================================

conclusions;
Calculating a hash using the MD5 method is 3.5 times more expensive than ORA_HASH, and also provides an addition of about 87% to the total query execution time (baseline), which is not bad at all.
Other methods of calculating a standard hash are much more expensive.