Использование метода «почти бесконечного» запроса для сопоставления CPU интенсивных операций в Oracle

Не так давно я опубликовал заметку о некотором варианте иерархического запроса, который выполняется очень долго (почти неограниченно) и не потребляет иных ресурсов базы данных кроме CPU.

Возникла довольно естественная идея использовать этот метод для сопоставления CPU интенсивных задач в БД Oracle. Например, если мы встраиваем такую прожорливую CPU-функцию в наш пайплайн исполнения SQL-запроса так, чтобы она выполнилась более миллиарда раз, то получаем возможность сравнить затраты на CPU в разных случаях.

В данном случае мы будем сравнивать по производительности различные функции хеширования в Oracle: ORA_HASH и различные варианты STANDARD_HASH.

Мы выполняем запросы следующего вида:

Baseline не выполняет доп. функций на CPU:

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

Запрос с 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);

Запрос с вычислением хеша одним из стандартных методов:

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

Типичный SQL Monitor-отчёт для подобных запросов:

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 |     . |          |                 |
=======================================================================================================================================================

Выводы;
Вычисление хеша методом MD5 в 3.5 раза более затратно, чем ORA_HASH, а также даёт добавку порядка 87% к общему времени выполнения запроса (baseline), что совсем неплохо.
Остальные методы вычисления стандартного хеша гораздо затратнее.