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

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

«Также я проверил различные значения привязки. С переменными привязки я получаю несколько ПОЛНЫХ СКАНИРОВАНИЙ ТАБЛИЦЫ, тогда как с жестко закодированными значениями план выглядит намного лучше».

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

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

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

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

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

«Кстати, удаление предложения :R1 IS NULL не сильно меняет план выполнения, что оставляет меня с другой стороной условия ИЛИ, :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.

Это очень похоже на потребность в Adaptive Cursor Sharing в сочетании со стабильностью 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 должен будет выполнить преобразования, так что это может привести к полному сканированию.

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