Как встроить переменную в PL/SQL?

Ситуация

У меня возникли проблемы с планом выполнения запроса для запроса среднего размера над большим объемом данных в Oracle 11.2.0.2.0. Чтобы ускорить процесс, я ввел фильтр диапазона, который примерно так:

PROCEDURE DO_STUFF(
    org_from VARCHAR2 := NULL,
    org_to   VARCHAR2 := NULL)

  -- [...]
  JOIN organisations org
    ON (cust.org_id = org.id
   AND ((org_from IS NULL) OR (org_from <= org.no))
   AND ((org_to   IS NULL) OR (org_to   >= org.no)))
  -- [...]

Как видите, я хочу ограничить JOIN из organisations используя дополнительный диапазон номеров организации. Код клиента может позвонить DO_STUFF с (должен быть быстрым) или без (очень медленным) ограничением.

Проблема

Проблема в том, что PL/SQL создаст переменные связывания для вышеупомянутых org_from а также org_to параметры, чего я и ожидал в большинстве случаев:

  -- [...]
  JOIN organisations org
    ON (cust.org_id = org.id
   AND ((:B1 IS NULL) OR (:B1 <= org.no))
   AND ((:B2 IS NULL) OR (:B2 >= org.no)))
  -- [...]

Обходной путь

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

  -- [...]
  JOIN organisations org
    ON (cust.org_id = org.id
   AND ((10 IS NULL) OR (10 <= org.no))
   AND ((20 IS NULL) OR (20 >= org.no)))
  -- [...]

Под "много" я ​​подразумеваю в 5-10 раз быстрее. Обратите внимание, что запрос выполняется очень редко, то есть раз в месяц. Поэтому мне не нужно кэшировать план выполнения.

Мои вопросы

  • Как я могу встроить значения в PL/SQL? Я знаю о EXECUTE IMMEDIATE, но я бы предпочел, чтобы PL/SQL компилировал мой запрос, а не делал конкатенацию строк.

  • Я только что измерил что-то, что произошло по совпадению, или я могу предположить, что встроенные переменные действительно лучше (в этом случае)? Причина, по которой я спрашиваю, заключается в том, что я думаю, что переменные связывания заставляют Oracle разрабатывать общий план выполнения, в то время как встроенные значения позволяют анализировать очень специфическую статистику по столбцам и индексам. Поэтому я могу представить, что это не просто совпадение.

  • Я что-то пропустил? Может быть, существует совершенно другой способ добиться улучшения плана выполнения запроса, кроме встраивания переменных (заметьте, я тоже пробовал довольно много подсказок, но я не эксперт в этой области)?

Источник

5 ответов

Решение

В одном из ваших комментариев вы сказали:

"Также я проверил различные значения связывания. С переменными связывания я получаю ПОЛНОЕ СКАНИРОВАНИЕ, тогда как с жестко закодированными значениями план выглядит намного лучше".

Есть два пути. Если вы передаете NULL для параметров, тогда вы выбираете все записи. В этих условиях полное сканирование таблицы является наиболее эффективным способом получения данных. Если вы передаете значения, то индексированное чтение может быть более эффективным, потому что вы выбираете только небольшое подмножество информации.

Когда вы формулируете запрос, используя переменные связывания, оптимизатор должен принять решение: должен ли он предполагать, что большую часть времени вы будете передавать значения или что вы будете передавать нули? Сложно. Посмотрите на это по-другому: более неэффективно выполнять полное сканирование таблицы, когда вам нужно только выбрать подмножество записей, или выполнять индексированное чтение, когда вам нужно выбрать все записи?

Похоже, что оптимизатор полностью развернул таблицу как наименее неэффективную операцию для покрытия всех возможных ситуаций.

В то время как вы жестко кодируете значения, Оптимизатор сразу же 10 IS NULL оценивается как ЛОЖЬ, и поэтому он может взвесить преимущества использования индексированных операций чтения для поиска нужных записей подмножества.

Так что делать? Как вы говорите, этот запрос выполняется только один раз в месяц, и я думаю, что потребуется лишь небольшое изменение бизнес-процессов, чтобы иметь отдельные запросы: один для всех организаций и один для подмножества организаций.

"Кстати, удаление предложения:R1 IS NULL не сильно меняет план выполнения, что оставляет меня с другой стороны условия OR:R1 <= org.no, где NULL в любом случае не имеет смысла, как org. нет не пусто

Итак, дело в том, что у вас есть пара переменных связывания, которые определяют диапазон. В зависимости от распределения значений разные диапазоны могут соответствовать разным планам выполнения. То есть этот диапазон (вероятно) подойдет для сканирования индексированного диапазона...

WHERE org.id BETWEEN 10 AND 11

... в то время как это, вероятно, будет более приспособлено к полному сканированию таблицы...

WHERE org.id BETWEEN 10 AND 1199999

Вот где Bind Variable Peeking вступает в игру.

(в зависимости от распределения ценностей, конечно).

Источник

Так как планы запросов на самом деле постоянно отличаются, это означает, что оценки количества элементов оптимизатора по какой-то причине отключены. Можете ли вы подтвердить из планов запросов, что оптимизатор ожидает, что условия будут недостаточно избирательными при использовании переменных связывания? Так как вы используете 11.2, Oracle должен использовать адаптивное совместное использование курсоров, поэтому это не должно быть проблемой просмотра переменных связывания (при условии, что вы вызываете версию с переменными связывания много раз с разными NO значения в вашем тестировании.

Насколько верны оценки количества элементов в хорошем плане? Я знаю, что вы сказали, что статистика на NO столбцы являются точными, но я бы с подозрением отнесся к случайной гистограмме, которая, например, может не обновляться при обычном процессе сбора статистики.

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

Тем не менее, одним из дополнительных тестов будет замена синтаксиса соединения SQL 99 старым синтаксисом Oracle, т.е.

SELECT <<something>>
  FROM <<some other table>> cust,
       organization org
 WHERE cust.org_id = org.id
   AND (    ((org_from IS NULL) OR (org_from <= org.no)) 
        AND ((org_to   IS NULL) OR (org_to   >= org.no)))

Это, очевидно, не должно ничего менять, но были проблемы с синтаксическим анализатором с синтаксисом SQL 99, так что это кое-что нужно проверить.

Источник

Это пахнет как Bind Peeking, но я только на Oracle 10, поэтому я не могу утверждать, что такая же проблема существует в 11.

Источник

Это очень похоже на необходимость адаптивного совместного использования курсора в сочетании со стабильностью SQLPlan. Я думаю, что происходит то, что capture_sql_plan_baselines parameter is true, И то же самое для use_sql_plan_baselines, Если это правда, происходит следующее:

  1. При первом запуске запроса он анализируется, и он получает новый план.
  2. Во второй раз этот план сохраняется в sql_plan_baselines как принятый план.
  3. Все последующие запуски этого запроса используют этот план, независимо от того, каковы переменные связывания.

Если Adaptive Cursor Sharing уже активен, оптимизатор сгенерирует новый / лучший план, сохранит его в sql_plan_baselines, но не сможет его использовать, пока кто-то не примет этот более новый план в качестве приемлемого альтернативного плана. Проверьте dba_sql_plan_baselines и посмотреть, есть ли в вашем запросе записи с accepted = 'NO' and verified = nullТы можешь использовать dbms_spm.evolve разработать новый план и автоматически принять его, если выполнение плана будет как минимум в 1,5 раза лучше, чем без нового плана.

Надеюсь, это поможет.

Источник

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

Похоже, что в вашей организации есть столбец № (org.no), который определяется как число. В вашем жестко закодированном примере вы используете числа для сравнения.

JOIN organisations org
    ON (cust.org_id = org.id
   AND ((10 IS NULL) OR (10 <= org.no))
   AND ((20 IS NULL) OR (20 >= org.no)))

В вашей процедуре вы переходите в varchar2:

PROCEDURE DO_STUFF(
    org_from VARCHAR2 := NULL,
    org_to   VARCHAR2 := NULL)

Поэтому для сравнения varchar2 с числом Oracle придется выполнить преобразования, так что это может привести к полному сканированию.

Решение: измените proc, чтобы передать числа

Источник
Другие вопросы по тегам