Эта небольшая заметка вдохновлена реальной и гораздо более сложной проблемой, которую я наблюдал и исследовал в 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 | | | |
===============================================================================================================================================
Что мы можем заметить из отчёта выше:
Запрос выполняется почти 2 часа и, в основном, занимается тем, что пожирает CPU.
Мы запроцессили порядка 4G (4,000,000,000,000) строк (и не собираемся на этом останавливаться).
Мы использовали 0 TEMPа и какие-то совсем незначительные 760K PGA.
Некоторые выводы:
Внутренняя часть плана генерит бесконечный набор данных (из-за потенциальной ошибки кодирования).
Если бы не HASH UNIQUE шаг в execution plan, мы бы довольно быстро упали, отъев всю доступную для сеанса PGA. Однако наличие HASH UNIQUE приводит к тому, что внутренняя часть плана постоянно «кормит» вышестоящий шаг плана данными и откидывает большую часть этих данных на лету, все это и приводит к бесконечному выполнению запроса.
P.S. Комбинация из CONNECT BY и какого-либо варианта UNIQUE (HASH UNIQUE/SORT UNIQUE) следующим шагом — является потенциально опасной и может приводить к выполнению запросов, которые впустую пожирают системные ресурсы.
P.P.S. Возможно, вы найдёте варианты положительного использования данного «искусственного», но очень интересного по поведению запроса.
