Эта небольшая заметка вдохновлена реальной и гораздо более сложной проблемой, которую я наблюдал и исследовал в PL/SQL коде, написанном довольно опытным разработчиком в Лондоне на моём предыдущем месте работы.

Начиналось это как довольно банальное упражнение в performance tuning для SQL-кода, использующего иерархические запросы.

Исследование затруднялось тем, что изначально использовалось PARALLEL QUERY и PARALLEL DML, и вначале параллельность казалась главным подозреваемым :)
Однако после нескольких часов терзаний и подходов к снаряду я осознал, что это довольно общая проблема, которая может проявляться в разнообразных execution plans с использованием «connect by» — проприетарного синтаксиса Oracle для иерархических запросов.

Кстати, решение было скопипасчено из интернета для подобного класса задач и содержит эту «бомбу», привожу ссылки на статьи: решение с distinct или solution 2.
После осознания проблемы довольно нетрудно найти подходящее решение и избежать такого зловредного поведения. Кстати, сразу подскажу, что некоторые правильные подходы содержатся в комментариях к статьям, ссылки на которые приведены выше.

В своей статье я намеренно не буду рассказывать деталей реальной задачи, а также путей решения, вместо этого хочу сосредоточиться на сути проблемы и на том аспекте, который считаю весьма общим и, в каком-то смысле, показательным, раскрывающим некоторые внутренние детали исполнения таких SQL.

Здесь будет представлен простейший синтетический пример, демонстрирующий проблему на простейшем запросе с использованием dual.

Дисклеймер: не запускайте низлежащий запрос на высоконагруженных базах Oracle.

select distinct id
  from (select id
    from (select 1 id
      from dual
        union all
        select 2
      from dual
        union all
        select 3
      from dual)
connect by level < 1e6)

И давайте сразу посмотрим на план исполнения запроса:

SQL_ID  dzqk3jmt0xtj6, child number 0
-------------------------------------
select distinct id from ( select id  from ( select 1 id from dual union 
all select 2 from dual union all select 3 from dual ) connect by level 
<1e6)
 
Plan hash value: 318940406
 
---------------------------------------------------------------------------------------
| Id  | Operation                      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |      |       |       |     7 (100)|          |
|   1 |  HASH UNIQUE                   |      |     3 |     9 |     7  (15)| 00:00:01 |
|   2 |   VIEW                         |      |     3 |     9 |     6   (0)| 00:00:01 |
|   3 |    CONNECT BY WITHOUT FILTERING|      |       |       |            |          |
|   4 |     VIEW                       |      |     3 |     9 |     6   (0)| 00:00:01 |
|   5 |      UNION-ALL                 |      |       |       |            |          |
|   6 |       FAST DUAL                |      |     1 |       |     2   (0)| 00:00:01 |
|   7 |       FAST DUAL                |      |     1 |       |     2   (0)| 00:00:01 |
|   8 |       FAST DUAL                |      |     1 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Теперь попробуем получить SQL-Monitor отчёт после некоторого времени ожидания.

select dbms_sqltune.report_sql_monitor(sql_id =>'dzqk3jmt0xtj6',type => 'TEXT') from dual;

Результат будет похож на:

 SQL Monitoring Report 

SQL Text
------------------------------
select distinct id from ( select id from ( select 1 id from dual union all select 2 from dual union all select 3 from dual ) connect by level <1e6)
Global Information
------------------------------

 Status              :  EXECUTING                          
 Instance ID         :  1                                  
 Session             :  RLS_USER (355:26535)               
 SQL ID              :  dzqk3jmt0xtj6                      
 SQL Execution ID    :  16777216                           
 Execution Started   :  03/05/2023 16:55:23                
 First Refresh Time  :  03/05/2023 16:55:29                
 Last Refresh Time   :  03/05/2023 18:38:19                
 Duration            :  6178s                              
 Module/Action       :  PL/SQL Developer/Secondary Session 
 Service             :  XXXXXZZZZ                       
 Program             :  plsqldev.exe                       
Global Stats                

================================
| Elapsed |   Cpu   |  Other   |
| Time(s) | Time(s) | Waits(s) |
================================
|    6176 |    6167 |     8.61 |
================================


SQL Plan Monitoring Details (Plan Hash Value=318940406)
===============================================================================================================================================
| Id   |            Operation             | Name |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Mem  | Activity | Activity Detail |
|      |                                  |      | (Estim) |      | Active(s) | Active |       | (Actual) |      |   (%)    |   (# samples)   |
===============================================================================================================================================
|    0 | SELECT STATEMENT                 |      |         |      |           |        |     1 |          |      |          |                 |
|    1 |   HASH UNIQUE                    |      |       3 |    7 |      6135 |     +2 |     1 |        0 | 760K |    10.48 | Cpu (645)       |
|    2 |    VIEW                          |      |       3 |    6 |      5941 |     +6 |     1 |       4G |      |     1.33 | Cpu (82)        |
| -> 3 |     CONNECT BY WITHOUT FILTERING |      |         |      |      6177 |     +1 |     1 |       4G |      |    88.18 | Cpu (5426)      |
|    4 |      VIEW                        |      |       3 |    6 |         1 |     +6 |     1 |        3 |      |          |                 |
|    5 |       UNION-ALL                  |      |         |      |         1 |     +6 |     1 |        3 |      |          |                 |
|    6 |        FAST DUAL                 |      |       1 |    2 |         1 |     +6 |     1 |        1 |      |          |                 |
|    7 |        FAST DUAL                 |      |       1 |    2 |         1 |     +6 |     1 |        1 |      |          |                 |
|    8 |        FAST DUAL                 |      |       1 |    2 |         1 |     +6 |     1 |        1 |      |          |                 |
===============================================================================================================================================

Что мы можем заметить из отчёта выше:

1. Запрос выполняется почти 2 часа и, в основном, занимается тем, что пожирает CPU.

2. Мы запроцессили порядка 4G (4,000,000,000,000) строк (и не собираемся на этом останавливаться).

3. Мы использовали 0 TEMPа и какие-то совсем незначительные 760K PGA.

Некоторые выводы:

• Внутренняя часть плана генерит бесконечный набор данных (из-за потенциальной ошибки кодирования).

• Если бы не HASH UNIQUE шаг в execution plan, мы бы довольно быстро упали, отъев всю доступную для сеанса PGA. Однако наличие HASH UNIQUE приводит к тому, что внутренняя часть плана постоянно «кормит» вышестоящий шаг плана данными и откидывает большую часть этих данных на лету, все это и приводит к бесконечному выполнению запроса.

P.S. Комбинация из CONNECT BY и какого-либо варианта UNIQUE (HASH UNIQUE/SORT UNIQUE) следующим шагом — является потенциально опасной и может приводить к выполнению запросов, которые впустую пожирают системные ресурсы.

P.P.S. Возможно, вы найдёте варианты положительного использования данного «искусственного», но очень интересного по поведению запроса.