Эта небольшая заметка вдохновлена реальной и гораздо более сложной проблемой, которую я наблюдал и исследовал в 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. Возможно, вы найдёте варианты положительного использования данного «искусственного», но очень интересного по поведению запроса.
ссылка на оригинал статьи https://habr.com/ru/articles/760892/
Добавить комментарий