Привет! Я работаю в компании Bercut, мы более 20 лет занимается разработкой и поддержкой ПО для операторов сотовой и фиксированной связи. Прошел путь от инженера в отделе сопровождения до менеджера продукта. В последние годы работаю ведущим специалистом в отделе администрирования (Senior DBA) и решаю проблемы производительности высоконагруженных биллинговых баз данных, обслуживающих от сотен тысяч до десятков миллионов абонентов. Сегодня я хочу рассказать про проблему построения оптимального плана выполнения запроса при использовании коллекций в качестве входных переменных запроса и вариантах ее решения.

Проблема

Последнее время часто стали применяться запросы, где в качестве входных переменных запроса указываются PL/SQL коллекции. В качестве примера взята реальная история — долгая работа запроса отображения деталей клиентов по списку идентификаторов (коллекция)

В плане запроса — полный просмотр таблицы CLIENT:

Оптимизатор, вместо индексного доступа по первичному ключу выбрал полный просмотр таблицы. В списке предикатов подставляемая переменная типа коллекции выглядит так:

Результат — запрос работал почти 3 часа, пользователи не дожидались завершения:

Причина

Причина подобного поведения — оптимизатор не имеет информации о том, сколько записей содержит передаваемая в запрос коллекция и использует при расчете стоимости запроса фиксированное значение 8168.

Вот например сильно упрощенный запрос, на котором разберем ситуацию детально:

В запрос подставляется коллекция, содержащая 3 значения.

CREATE OR REPLACE TYPE T_REALS_TAB AS TABLE OF NUMBER
/

SELECT *
FROM client clv
WHERE clv.clnt_id IN (
  SELECT COLUMN_VALUE 
  FROM TABLE (t_reals_tab (43039612, 46790131, 47745803)));

Посмотрим детальный план выполнения запроса:

alter session set statistics_level='ALL'
 
set pages 1000 lines 200 timing on
 
SELECT *
  FROM client clv
 WHERE clv.clnt_id IN (SELECT COLUMN_VALUE FROM TABLE (t_reals_tab (43039612, 46790131, 47745803)));
 
                                      
SELECT *
FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST ALL +OUTLINE'));

Здесь колонка E‑Rows (estimated) означает, сколько, по мнению оптимизатора, ожидается строк. A‑Rows (actual) — сколько строк оказалось по факту при выполнении.

8168 — это то число строк, которое принимается как количество строк в коллекции по умолчанию. Вот кусок трейса 10 053. Тут видно, что при построении плана запроса оптимизатор берет количество строк из статистики на таблице CLIENT — 63млн. А по табличной функции KOKBG$0 — константа 8168, которая постоянна во всех БД с размером блока в БД по умолчанию 8192.

Из‑за неправильной оценки размера коллекции план запроса может стать фатально не оптимальным.

Для сравнения, если мы выполним тот же запрос с явной подстановкой значений, без использования коллекции, результат будет другим:

SELECT * 

FROM client clv 

WHERE clv.clnt_id IN (43039612, 46790131, 47745803 );

Из плана этого запроса видно, что стоимость драматически меньше, всего 7 против 540, а оценка E‑rows не отличается от фактического числа записей A‑Rows.

В обоих упрощенных случаях план получился очень простым и оптимальным, с индексным доступом, и выполняется быстро — менее 1 секунды. Однако, как в примере из заявки выше, ситуации бывают разные.

Пути решения

Метод 1: Подсказка оптимизатору CARDINALITY

Данный метод официально не документирован и не поддерживается. Использование на свой страх и риск

Суть метода — в запрос добавляется подсказка оптимизатору с указанием мощности (cardinality) для источника строк в запросе. В приведенном ниже примере разработчик оценивает размер коллекции в 10 строк

SELECT /*+ CARDINALITY(t, 10)*/ *

FROM client c,

TABLE (t_reals_tab (43039612, 46790131, 47745803)) t

WHERE c.clnt_id=t.column_value

/

Недостатки метода очевидны. Во‑первых, он официально не поддерживается и его работа не гарантирована. Во‑вторых, точность построения запроса зависит от значения в подсказке оптимизатору (hint), который укажет разработчик. И в случае если объем данных источника будет значительно отличаться, план запроса может быть построен не оптимальный.

Кстати, есть есть необходимость сохранить оригинальный вид запроса, то его настройка потребует дополнительных условий. Сначала нужно будет определить имя Query Block, которое присваивается подзапросу, а затем его указать в подсказке:

alter session set statistics_level='ALL';
  
set pages 1000 lines 200 timing on
 
 SELECT /*+ cardinality(KOKBF$0@SEL$3 , 10)*/ *
  FROM client c
  WHERE c.clnt_id in (select column_value from   TABLE (t_reals_tab (43039612, 46790131, 47745803)))
/
 
SELECT *
FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST ALL +OUTLINE'));

Метод 2: Подсказка оптимизатору OPT_ESTIMATE

Данный метод официально не документирован и не поддерживается. Использование на свой страх и риск.

По данному методу есть неофициальные комментарии, которыми лично я сам довольно часто пользуюсь при настройке запросов средствами БД: https://blog.pythian.com/oracles‑opt_estimate‑hint‑usage‑guide/

Суть метода — в запрос добавляется подсказка оптимизатору с указанием количества строк в таблице. В приведенном ниже примере разработчик оценивает размер коллекции в 20 строк.

Хинт OPT_ESTIMATE может влиять на мощность различных источников строк — таблицы, индекса или соединения. Нужны подробности? — см. ссылку выше.

SELECT /*+ OPT_ESTIMATE(TABLE t ROWS=20)*/ * 

FROM client c, 

TABLE (t_reals_tab (43039612, 46790131, 47745803)) t

WHERE c.clnt_id=t.column_value

/

Общая итоговая стоимость запроса — 69, с учетом не вполне точной оценки размера коллекции.

Метод 3. Extensible optimiser

В состав БД входит функциональность Oracle Data Cartridges, которые позволяют реализовать пользовательские расширения для БД. Типы объектов и другие функции, такие как большие объекты (LOBs), внешние процедуры, расширяемое индексирование и оптимизация запросов, могут использоваться для создания мощных, повторно используемых серверных компонентов, называемых Oracle Data Cartridges (ODC).

Extensible optimizer является частью Oracle Data Cartridge и позволяет реализовать пользовательские функции и индексы для сбора статистической информации, расчета селективности и стоимости. Эта информация используется оптимизатором при выборе плана запроса, тем самым расширяя возможности оптимизатора по использованию предоставленной пользователем информации.

Данный метод документирован и полностью поддерживается начиная с версии Oracle 10g.

Решение для коллекций выглядит следующим образом:

1) Для оптимизируемого типа коллекции делается функция‑обертка, которая принимает в качестве аргумента коллекцию заданного типа и её же возвращает, в примере — collection_wrapper_number.

2) Для функции‑обёртки в БД создается ассоциация, указывающая оптимизатору Oracle как собирать статистику по этой коллекции и сообщающая оптимизатору точные сведения о размере коллекции.

1. Создается тип для элементов коллекции. Возможно, такой уже есть и данный шаг — опционален.

CREATE OR REPLACE TYPE t_numbers_tab AS TABLE OF NUMBER;
/

2. Создается функция‑обертка для коллекции типа созданного ранее или существующего типа. Функция ничего не делает, только возвращает поданную на вход коллекцию:

CREATE OR REPLACE FUNCTION collection_wrapper_number(p_collection IN t_numbers_tab) RETURN t_numbers_tab IS
BEGIN
RETURN p_collection;
END collection_wrapper_number;

3. Создается спецификация интерфейсного типа, который будет связан с созданной ранее функцией‑оберткой. В объектный тип включена функция, которая будет собирать статистику по переданной коллекции

CREATE OR REPLACE TYPE collection_wrapper_number_ot AUTHID DEFINER AS OBJECT (
  dummy_attribute NUMBER,
  STATIC FUNCTION ODCIGetInterfaces (p_interfaces OUT SYS.ODCIObjectList)
    RETURN NUMBER,
  STATIC FUNCTION ODCIStatsTableFunction (
    p_function IN SYS.ODCIFuncInfo,
    p_stats OUT SYS.ODCITabFuncStats,
    p_args IN SYS.ODCIArgDescList,
    p_collection IN t_numbers_tab) RETURN NUMBER);
/

4. Создается тело типа. В фукнции, которая будет использоваться для сбора статистике по коллекции, делается вызов ODC API для выполнения простейшей операции — подсчета числа элементов коллекции.

CREATE OR REPLACE TYPE BODY collection_wrapper_number_ot AS
  STATIC FUNCTION ODCIGetInterfaces(p_interfaces OUT SYS.ODCIObjectList) RETURN NUMBER IS
    BEGIN
      p_interfaces := SYS.ODCIObjectList(SYS.ODCIObject ('SYS', 'ODCISTATS2'));
      RETURN ODCIConst.success;
    END ODCIGetInterfaces;
  STATIC FUNCTION ODCIStatsTableFunction (
    p_function IN SYS.ODCIFuncInfo,
    p_stats OUT SYS.ODCITabFuncStats,
    p_args IN SYS.ODCIArgDescList,
    p_collection IN t_numbers_tab) RETURN NUMBER IS
  BEGIN
    p_stats := SYS.ODCITabFuncStats(p_collection.COUNT);
    RETURN ODCIConst.success;
  END ODCIStatsTableFunction;
END;
/

5. Создается ассоциация созданной функции с созданным объектным типом

ASSOCIATE STATISTICS WITH FUNCTIONS collection_wrapper_number USING collection_wrapper_number_ot;

 Наличие нашей ассоциации можно проверить запросом:

select * from user_associations where object_name = 'COLLECTION_WRAPPER_NUMBER'

Далее можно приступать к тестированию.

Сначала посмотрим статистику выполнения простейшего запроса из коллекции без обертки:

alter session set statistics_level='ALL';
 
set pages 1000 lines 200 timing on
 
SELECT * FROM TABLE(t_numbers_tab(1,5,200,400,10500));
 
SELECT *
FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST ALL'));

А теперь с  оберткой:

alter session set statistics_level='ALL';
 
set pages 1000 lines 200 timing on
 
SELECT * FROM TABLE(collection_wrapper_number(t_numbers_tab(1,5,200,400,10500)));
 
SELECT *
FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST ALL'));

Как видно из второго теста, оценка количества строк при использовании обертки с точностью 100%.

Теперь сделаем тест с оберткой для нашего первоначального примера:

alter session set statistics_level='ALL';
 
set pages 1000 lines 200 timing on
 
SELECT *
  FROM client c
 WHERE c.clnt_id IN (SELECT COLUMN_VALUE FROM TABLE(collection_wrapper_number(t_numbers_tab (43039612, 46790131, 47745803))))
/
 
SELECT *
FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST ALL'));

Таким образом, использование специальной обертки для коллекции, в которой собирается статистика о размере коллекции, позволяет сообщить оптимизатору точное число элементов коллекции и вероятность получения оптимального плана выше.

Выводы

Мы рассмотрели несколько путей решения проблемы подстановки входных значений в SQL запрос из коллекций. Первые два официально не поддерживаются производителем СУБД и требуют от разработчика указать оценку числа записей в коллекции. Третий способ на основе ODC Extensible Optimizer официально поддерживается и самостоятельно рассчитывает статистику по коллекции. Изучайте, пробуйте и проверяйте, но не забывайте делать нагрузочные тестирования любого кода перед установкой на прод

Выражаю благодарность Алексею Онину за рецензирование и помощь в подготовке материала.