Скахин Алексей / pihel
Личный блог. Заметки о программировании и не только
Страницы
воскресенье, 14 февраля 2016 г.
Oracle: адаптивные технологии оптимизации запросов
Оптимизация запросов со связанными bind переменными
1. При первом разборе происходит полный разбор запроса (hard parse)
План запроса помещается в глобальный кэш БД с определенным sql_id
2. При повторном выполнении происходит частичный разбор (soft parse)
Происходит только синтаксический разбор, проверки прав доступа и проверки bind переменных. Что для разных вариаций sql_id создает дочерние child_sql_id
Из-за такого механизма работы Oracle вытекает частая проблема oltp систем, где существует огромное число маленьких запросов, отличающихся друг от друга только фильтрами или параметрами. Это приводит к быстрому вытеснению планов из кэша и их последующему повторному hard parse.
В итоге может оказаться, что большую часть времени БД занимается разбором запросов, а не собственно их выполнением.
Отсюда вывод: по возможности используйте bind переменные в вариациях одного запроса, замен константных фильтров, т.к. это даст нам только один план запроса (child_sql_id) при разных значениях переменных на равномерно распределенном столбце.
Я не зря сказал ранее «на равномерно распределенном столбце», т.к. с bind переменными есть проблема: по умолчанию Oracle не знает какие данные будут переданы в запрос и из-за этого может сгенерить неверный план запроса.
Посмотрим на примере по умолчанию. Создадим таблицу с неравномерно распределенным столбцом «n» (9 строк со значением = 1, и 1млн-9 строк со значением 2):
Столбец не имеет гистограмм, но есть статистика по уникальным значениям. Построим план запроса с bind переменной = 1:
Oracle закономерно ожидает в результате половину таблицу и выбирает full scan, хотя мы то знаем, что тут был бы лучше Index scan.
К счастью с 11 версии Oracle может заглядывать в значения bind переменных и подбирать под них нужные планы.
Для этого соберем гистограмму с 2 вершинами и повторим эксперимент:
Oracle сгенерировал новый child_sql_id под новое значение bind переменной и выбрал правильный доступ по индексу.
Данный план был закеширован в глобальную память и если прямо сейчас выполнить заново с параметром 2, то мы получим тотже план (child number 2).
Замечу что на этом этапе уже надо смотреть план уже выполненного запроса, т.к. oracle не умеет показывать план и заглядывать в bind переменные, но при реальном выполнении запроса значения bind переменных смотрятся.
но oracle пометит этот запрос на пересмотр, т.к. план совсем не сошелся с реальными данными и при последующем применении сгенерирует новый child_sql_id (child number 3) под нашу bind переменную:
Из всего этого можно сделать вывод, что вопреки частому заблуждению, Oracle умеет генерировать правильные планы по bind переменным, но делает это не сразу, а при повторном вызове и при наличии гистограммы.
Второе: реальный план запроса с bind переменными можно узнать только во время или после выполнения запроса, т.к. «explain plan» не подсматривает в bind переменные.
Cardinality feedback
Ora Blog
Oracle мониторит и исправляет следующии «estimated rows» оценки на основе реальных «actual rows»
* Single table cardinality (after filter predicates are applied)
* Index cardinality (after index filters are applied)
* Cardinality produced by a group by or distinct operator
Dynamic Sampling
Ora Blog
Применимо для запросов со сложными предикатами фильтрации, которые дают существенную ошибку оптимизатора.
Включение dynamic sampling в зависимости от параметра пробирует от 32 блоков таблицы на предикаты фильтрации и определяем реальный лучший план.
Проблема со связанными переменными: как превратить оптимизатор из врага в друга
Автор статьи – Виктор Варламов(varlamovVp18), OCP.
Оригинал статьи опубликован 07.07.2017.
Отдельное спасибо автору перевода — brutaltag.
В нашей системе подготовки отчетности обычно выполняются сотни длительных запросов, которые вызываются различными событиями. Параметрами запросов служат список клиентов и временной интервал (дневной, недельный, месячный). Из-за неравномерных данных в таблицах один запрос может выдать как одну строку, так и миллион строк, в зависимости от параметров отчета (у разных клиентов — различное количество строк в таблицах фактов). Каждый отчет выполнен в виде пакета с основной функцией, которая принимает входные параметры, проводит дополнительные преобразования, затем открывает статический курсор со связанными переменными и в конце возвращает этот открытый курсор. Параметр БД CURSOR_SHARING выставлен в FORCE.
В такой ситуации приходится сталкиваться с плохой производительностью, как в случае повторного использования плана запроса оптимизатором, так и при полном разборе запроса с параметрами в виде литералов. Связанные переменные могут вызвать неоптимальный план запроса.
В своей книге “Oracle Expert Practices” Алекс Горбачев приводит интересную историю, рассказанную ему Томом Кайтом. Каждый дождливый понедельник пользователям приходилось сталкиваться с измененным планом запроса. В это трудно поверить, но так и было:
«Согласно наблюдениям конечных пользователей, в случаях, когда в понедельник шел сильный дождь, производительность базы данных была ужасной. В любой другой день недели или же в понедельник без дождя проблем не было. Из разговора с администратором БД Том Кайт узнал, что трудности продолжались до принудительного рестарта базы данных, после чего производительность становилась нормальной. Вот такой был обходной маневр: дождливый понедельник – рестарт».
Это реальный случай, и проблема была решена совершенно без всякой магии, только благодаря отличным знаниям того, как работает Oracle. Я покажу решение в конце статьи.
Вот небольшой пример, как работают связанные переменные.
Создадим таблицу с неравномерными данными.
Другими словами, у нас есть таблица VVP_HARD_PARSE_TEST с миллионом строк, где в 10.000 случаев поле C2 = 99, 8 записей с C2 = 1, а остальные с C2 = 1000000. Гистограмма по полю С2 указывает оптимизатору Oracle об этом распределении данных. Такая ситуация известна как неравномерное распределение данных, и гистограмма может помочь выбрать правильный план запроса в зависимости от запрашиваемых данных.
Понаблюдаем за простыми запросами к этой таблице. Очевидно, что для запроса
SELECT * FROM VVP_HARD_PARSE_TEST WHERE c2 = :p
если p = 1, то наилучшим выбором будет INDEX RANGE SCAN, для случая p = 1000000 лучше использовать FULL TABLE SCAN. Запросы Query1 и Query1000000 идентичны, за исключением текста в комментариях, это сделано чтобы получить различные идентификаторы планов запроса.
Теперь посмотрим на планы запросов:
Как можно видеть, план для разных запросов создается только один раз, в момент первого выполнения (только один дочерний курсор с CHILD_NUMBER = 0 существует для каждого запроса). Каждый запрос выполняется дважды (EXECUTION = 2). Во время жесткого разбора Oracle получает значения связанных переменных и выбирает план соответственно этим значениям. Но он использует тот же самый план и для следующего запуска, несмотря на то что связанные переменные изменились во втором запуске. Используются неоптимальные планы – Query1000000 с переменной C2 = 1 использует FULL TABLE SCAN вместо INDEX RANGE SCAN, и наоборот.
Понятно, что исправление приложения и использование параметров как литералов в запросе – это самый подходящий способ решения проблемы, но он ведет к динамическому SQL с его известными недостатками. Другой путь – отключение запроса связанных переменных ( ALTER SESSION SET «_OPTIM_PEEK_USER_BINDS» = FALSE ) или удаление гистограмм (ссылка).
Одно из возможных решений — это альтернативное использование политик на доступ к данным, также известных как Virtual Private Database (детальный контроль доступа, Fine Grained Access Control, контроль на уровне строк). Это позволяет менять запросы на лету и поэтому может вызвать полный разбор плана запроса каждый раз, когда запрос использует детальный контроль доступа. Эта техника подробно описана в статье Рэндальфа Гейста. Недостатком этого метода является возрастающее число полных разборов и невозможность манипулировать планами запросов.
Посмотрите, что мы сейчас сделаем. После анализа наших данных мы решаем разбить клиентов на три категории – Большие, Средние и Маленькие (L-M-S или 9-5-1) – согласно количествам сделок или транзакций в течение года. Также количество строк в отчете строго зависит от периода: Месячный – Large, Недельный – Middle, Дневной – Small или 9-5-1. Далее решение простое – сделаем предикат политики безопасности зависящим от каждой категории и от каждого периода. Так, для каждого запроса мы получим 9 возможных дочерних курсоров. Более того, запросы с разными политиками приведут нас к одним и тем же идентификаторам запросов, это дает возможность реализовать SQL PLAN MANAGEMENT (sql plan baseline).
Теперь, если мы хотим встроить такую технологию в отчет, нам надо добавить HARD_PARSE_TABLE в запрос (это ни капельки его не испортит) и вызывать CALC_PREDICATES перед тем, как выполняется основной запрос.
Посмотрим, как эта техника может преобразить предыдущий пример:
Посмотрим на планы выполнения:
Выглядит здорово! Каждый запрос выполняется дважды, с различными дочерними курсорами и разными планами. Для параметра C2 = 1000000 мы видим FULL TABLE SCAN в обоих запросах, а для параметра C1 = 1 мы видим всегда INDEX RANGE SCAN.
В конце привожу разгадку случая с дождливыми понедельниками:
«Оказывается, каждые выходные в воскресенье происходил холодный бэкап, так что все планы запросов перегенерировались по первому выполнению утром в понедельник. Один из сотрудников обычно начинал свою работу раньше остальных, и его план запроса выполнялся хорошо и для остальных пользователей в течение недели. Однако если шел дождь, этот пользователь опаздывал к началу рабочего дня из-за проблем с его утренним маршрутом. Тогда первым запускался пакетный расчет отчетов, но план запроса из-за неподходящих значений связанных переменных был совершенно плох для остальных случаев».
Статистика parse count (total) отражает число вызовов парсера, выполненных ядром Oracle. Результаты выполнения второго теста показывают, что ваш код не всегда приводит к вызову парсера – исходный код второго примера лишь чуть-чуть отличается от исходного кода первого примера, но обрабатываются они совершенно по-разному. Это результат ввода в действие кэша курсора PL/SQL – интерпретатор PL/SQL замечает, что внутри цикла выполняется одна и та же инструкция, задействует внутренний механизм для создания локальной курсорной переменной и удерживает этот курсор открытым. Это означает, что не требуется вызывать парсер в каждой итерации цикла, а достаточно вызвать процедуру выполнения после самой первой итерации. Эта оптимизация не применялась к механизму execute immediate до версии 10g – если провести тестирование в Oracle 9i, можно увидеть, что в версии с переменной связывания парсер вызывается в каждой итерации цикла:
Но в третьем примере Oracle фактически не производит оптимизацию инструкции при каждом ее выполнении – напомню, что не каждый вызов парсера ( parse count(total) ) сопровождается оптимизацией ( parse count(hard) ). Это объясняется вступлением в игру библиотечного кэша. Когда цикл выполняется в первый раз, Oracle анализирует и оптимизирует инструкцию, и загружает ее в библиотечный кэш. Когда цикл выполняется во второй раз, Oracle просматривает библиотечный кэш, прежде чем выполнить полный парсинг и оптимизацию инструкции. Если в кэше обнаруживается готовая инструкция, она используется повторно и сеансу не приходится снова платить за оптимизацию – это объясняет меньшее значение статистики parse count(hard ).
Примечание. Последовательность действий при парсинге немного сложнее, чем мои первоначальные предположения. Когда приложение передает в Oracle фрагмент текста, сначала выполняется проверка синтаксиса, чтобы определить допустимость инструкции, затем производится поиск совпадений в библиотечном кэше (для этого используется хэш-значение, вычисленное из текста инструкции). Если обнаруживается текстовое совпадение, Oracle выполняет семантическую проверку – означает ли новый текст то же самое, что имеющийся в библиотечном кэше (те же объекты, те же привилегии и т. д.), Эта проверка известна как проверка курсора (cursor authentication). Если совпадение подтверждается, отпадает необходимость повторной оптимизации инструкции.
Кэш курсора в базе данных Oracle
Примечание. Эти значения получены в 10.2. Результаты в 11g значительно отличаются, потому что в этой версии Oracle перешел на использование мьютексов в некоторых операциях.
| Статистика | session_cached_cursors со значением по умолчанию (50) | session_cached_cursors = 0 |
| parse count (total) | 1064 | 1077 |
| parse count (hard) | 6 | 6 |
| session cursor cache hits | 1050 | 0 |
| Защелка | session_cached_cursors со значением по умолчанию (50) | session_cached_cursors = 0 |
| library cache | 6393 | 10549 |
| library cache lock | 6174 | 10392 |
На первый взгляд статистики сеанса говорят, что число вызовов парсера одинаково в обоих случаях. И даже при том, что в код включена 1000 явных вызовов parse ( dbms_sql.parse ), полный парсинг выполнялся считанное число раз. Это означает, что подобное решение обеспечивает лишь незначительную оптимизацию и приходится повторно использовать инструкцию в цикле. Однако, значительно изменился способ доступа к курсору с этой инструкцией, а также изменился объем работы и степень конкуренции.
По умолчанию сеанс обладает кэшем курсора определенного размера. Это означает, что если вызывать инструкцию достаточно часто, Oracle (10g) присоединит блокировку KGL к курсору этой инструкции, чтобы удержать его открытым, и создаст в памяти сеанса объект состояния (state object), связывающий курсор так, что образуется короткий путь в курсор, позволяющий исключить операцию поиска в библиотечном кэше. Вам часто придется видеть утверждения, что курсор кэшируется на третьем вызове инструкции – это не совсем верно. Технически кэширование происходит в вызове, следующем за вызовом после проверки курсора (cursor authentication).
Если выполнить инструкцию, которая прежде не выполнялась, а затем выполнить ее еще несколько раз, можно заметить, что статистика cursor authentications увеличилась на втором вызове, а статистики session cursor cache hits и session cursor cache count увеличились на четвертом вызове, при этом имеет место следующая последовательность событий:
Данная последовательность событий соответствует распространенному мнению, что достаточно выполнить инструкцию трижды, чтобы она оказалась в кэше. Однако, если кто-то другой уже выполнял данную инструкцию, тогда у вас на первом вызове будет выполнена проверка курсора и инструкция попадет в кэш курсора сеанса после второго вызова. То есть, наличие кэша курсора сеанса означает, что вызов парсера не будет приводить к выполнению парсинга.
Примечание. Кэш курсора PL/SQL имеет важное отличие от кэша курсора сеанса, которое проявляется только в версии Oracle 11g, где появился механизм динамически разделяемых курсоров (adaptive cursor sharing). Если выполнить запрос, уже хранящийся в кэше курсора сеанса, он попадет во власть механизма динамически разделяемых курсоров и может оптимизироваться повторно. Если запрос хранится в кэше курсора PL/SQL, механизм динамически разделяемых курсоров вообще не будет задействован.
Удержание курсоров
В процессе выполнения этого кода, число приобретений различных защелок библиотечного кэша (в 10g) измеряется уже сотнями, а не тысячами, как в примере, где курсор явно открывается и закрывается в цикле. Для случаев частого использования легковесных инструкций SQL эта стратегия уменьшает риск конкуренции и оказывается наиболее эффективной.
Однако эту стратегию необходимо применять с осторожностью. Мне приходилось сталкиваться с двумя ключевыми ошибками, когда люди делали это неправильно, и обе, так уж случилось, были допущены в Java-окружении. Первая ошибка – использование функций открытия и закрытия курсора в программном коде на Java, подобно тому, как я это сделал в примере кода PL/SQL. Проблема в том, что используя описанную стратегию, класс открывает и закрывает курсор, но выполняет инструкцию только один раз – вы не получаете никаких преимуществ, если инструкция не выполняется достаточно часто перед закрытием курсора. (Я полагаю, что в последних версиях драйверов JDBC эта проблема может быть решена удержанием курсора открытым на уровне библиотечного кода, на котором основывается прикладной код программ, потому что примерно так используется кэш курсора сеанса в Oracle.)
Вторая ошибка состоит в том, что многие часто забывают закрыть курсор – часто в обработчиках исключений – и уничтожают класс. Программы (и программисты) на Java должны закрывать курсор; если курсор останется открытым в сеансе, курсор в библиотечном кэше не будет закрыт до конца сеанса. Когда такое происходит, программы обычно завершаются аварийно с ошибкой ORA-01000 : maximum open cursors exceeded (превышено максимальное число курсоров).
Чтобы лучше понять, почему и как содержимое курсора может быть вытолкнуто из библиотечного кэша (даже когда курсор удерживается открытым), обратим наше внимание на библиотечный кэш и разделяемый пул.
Обработка запросов в Oracle и PostgreSQL: следствия одного решения
Обработка запросов SQL и в Оракле, и в Постгресе имеет много общего. Так или иначе, надо выполнить синтаксический разбор, проверить семантику (для чего потребуется метаинформация, и не важно, называется ли это «словарь данных» или «системный каталог»), выполнить какие-то преобразования, построить оптимальный план выполнения (в обеих системах основанный на стоимости, а следовательно требующий заранее собранной статистики).
Но есть одно-единственное существенное различие, которое коренным образом меняет весь подход к обработке. Речь, конечно, о том, что Оракл использует глобальный кэш разобранных запросов, а Постгрес сохраняет запросы локально.
В статье мы попытаемся проследить, как из-за разницы в одном архитектурном решении логически следует совершенно разная идеология работы в запросами в двух СУБД.
Приведенные примеры (которые выполнялись на версиях Oracle 11.2 XE и PostgreSQL 9.4) содержат время выполнения запросов. Нас интересуют только относительные величины: во сколько раз изменилось время выполнения после внесения в запрос тех или иных изменений. При этом абсолютные цифры могут отличаться на порядки в зависимости от аппаратуры, нагрузки и настроек. Чтобы не давать повод для бессмысленных выводов на их основании, все абсолютные значения в статье отмасштабированы так, чтобы один из запросов составлял в обеих системах 10 секунд.
Оракл
Оракл использует глобальный для всего экземпляра кэш разобранных запросов (library cache, библиотечный кэш). План любого выполняемого запроса гарантированно находится в кэше: либо запрос выполняется с уже готовым планом из кэша, либо строится и сохраняется в кэше новый план — и это происходит автоматически.
Упрощенно общая схема выполнения запроса может быть представлена так:
Один и тот же запрос, повторенный два раза подряд, будет обработан по-разному. Первый раз произойдет так называемый полный разбор (hard parse) — от первого до последнего пункта. Второй раз будет выполнен только частичный разбор (soft parse) — синтаксический и семантический анализ — после чего в кэше будет найден и использован уже готовый план, что существенно эффективнее.
Наличие глобального кэша подталкивает к тому, чтобы минимизировать число записей в него. Одна причина состоит в том, что большой поток «одноразовых» запросов может вытеснить из кэша полезные планы, в то время как сами эти запросы никогда больше не повторятся. Но самое главное, к общему кэшу обращаются параллельно работающие процессы, следовательно, он должен быть защищен блокировками и запись в него может стать узким местом.
Действительно, процесс, выполняющий много полных разборов, становится проблемой всего экземпляра. Рассмотрим эту ситуацию на следующем примере:
create table t(
id number primary key,
n number not null
);
insert into t(id, n)
select level, 1
from dual
connect by rownum
exec dbms_stats.gather_table_stats(user,’T’);
alter session set statistics_level=all;
Здесь мы создаем таблицу, вставляем в нее сотню тысяч строк (конструкция «from dual connect by rowid begin
for i in (select id from t) loop
execute immediate ‘update t set n = n + 1 where >
end loop;
commit;
end;
/
Если выполнить трассировку, то вот что можно обнаружить:
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
Misses in library cache during parse: 100001
Здесь показана информация по всем SQL-запросам, инициированным из блока кода. Столбец elapsed показывает общее затраченное время (которое складывается из cpu и различных ожиданий), а строки parse, execute, fetch соответствуют этапам разбора, выполнения и получения результатов запроса. Как видно, основное время (95 секунд из 110, столбец elapsed) ушло на разбор ста тысяч (столбец count) однотипных запросов и помещение их одноразовых планов в кэш. Если запустить несколько аналогичных процессов одновременно, начнут появляться ожидания вроде «latch: shared pool» и «latch: row cache objects» (названия меняются от версии к версии), говорящие о конкуренции за доступ к библиотечному кэшу.
Чтобы такого не происходило, в Оракле принято использовать переменные связывания (bind variables). Например, так:
begin
for i in (select id from t) loop
execute immediate ‘update t set n = n + 1 where using i.id;
end loop;
commit;
end;
/
Или проще, без динамического SQL, поскольку PL/SQL автоматически преобразует свои переменные в переменные связывания БД:
begin
for i in (select id from t) loop
update t set n = n + 1 where >
end loop;
commit;
end;
/
Вот что покажет трассировка в этом случае:
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
Время разбора сократилось до минимального — все запросы update теперь выглядят для СУБД одинаковыми. «Одинаковость», то есть фактически ключ для кэша, определяется двумя значениями:
Таким образом, запрос update разбирается только один раз (число 3 в столбце count соответствует разборам PL/SQL-блока, запроса select в предложении for и запроса update в теле цикла). Его план помещается в кэш и дальше все работает относительно быстро.
(Почему «относительно»? Потому что правильный способ — выполнить обновление одной командой «update t set n = n + 1», которая выполняется еще на порядок быстрее.)
Однако «общий» план запроса, построенный без учета значений переменных, будет адекватен лишь для равномерно распределенных данных.
Изменим таблицу: добавим и проиндексируем поле flag, равное «Y» для 0,1% строк и «N» для остальных 99,9%.
alter table t add (
flag char(1) check (flag in (‘Y’,’N’))
);
update t
set flag = case when mod(id,1000)=0 then ‘Y’ else ‘N’ end;
create index t_flag on t(flag);
Чтобы оптимизатор принял к сведению неравномерность данных в поле flag, требуется собрать гистограмму по этому полю. Например, так:
exec dbms_stats.gather_table_stats(user,’T’,method_opt=>’for columns flag size 2′);
Интересно, что команда explain plan (результат которой доступен с помощью функции dbms_xplan.display) все равно покажет план, построенный из предположения равномерности, как будто оптимизатор ожидает получения половины таблицы:
explain plan for select * from t where flag = :f;
select * from table(dbms_xplan.display);
Это означает лишь то, что по большому счету командой explain plan в Оракле пользоваться нельзя. Она не учитывает ни значений переменных, ни их типы, а сгенерированный ею план не попадает в кэш и никак не используется.
На самом же деле при выполнении запроса Оракл «подглядывает» значения переменных связывания (это называется «bind peeking») и строит план исходя из этих значений. Реальный план нужно смотреть непосредственно в кэше, когда запрос уже отправлен на выполнение и разобран. Для этого используется функция dbms_xplan.display_cursor; с указанными в примере параметрами она выводит план последнего выполненного запроса и информацию о переменных связывания:
var f char(1)
exec :f := ‘Y’
select * from t where flag = :f;
.
100 rows selected.
select * from table(dbms_xplan.display_cursor(format=>’typical +peeked_binds’));
SQL_ID 6pncxxhknwgqc, child number 0
Теперь видно, что оптимизатор учел значение переменной (секция peeked binds), адекватно оценил число строк (135; погрешность не влияет на результат) и выбрал доступ по индексу.
Проблема в том, что построенный «частный» план попадает в кэш и будет повторно использоваться для таких же запросов — уже без учета значений переменных. Это не всегда хорошо: в нашем примере доступ по индексу будет крайне неэффективен для значения ‘N’. Традиционно решение состояло в использовании динамического SQL с вклеенными в текст запроса литералами — но решение это неудачное: в дополнение к обсужденным выше минусам такой подход опасен также возможностью SQL-инъекций. Поэтому (начиная с версии 11g) Оракл умеет находить и специально обрабатывать запросы, чувствительные к значениям переменных связывания (это называется «adaptive cursor sharing»). При выполнении запроса используется уже имеющийся в кэше план, но отслеживаются реально затраченные ресурсы и сравниваются со статистикой предыдущих выполнений.
Посмотрим на часть информации из библиотечного кэша по нашему запросу:
select child_number, is_bind_sensitive, is_bind_aware, executions, buffer_gets from v$sql where sql_id=’6pncxxhknwgqc’;
Запрос помечен как чувствительный к значениям переменных (bind sensitive). Buffer_gets — число прочитанных блоков данных.
Если обнаруживается, что с другими значениями запрос выполнялся хуже, то при следующем выполнении он будет помечен как нуждающийся в разных планах (bind aware).
Выполним тот же запрос с другим значением поля flag:
exec :f := ‘N’
select * from t where flag = :f;
.
99900 rows selected.
Убедимся, что запрос был выполнен с планом из кэша, а заодно продемонстрируем возможность вывода в плане не только ожидаемых, но и актуальных значений (именно для этого вначале был установлен параметр statistics_level):
select * from table(dbms_xplan.display_cursor(format=>’allstats last’));
SQL_ID 6pncxxhknwgqc, child number 0
Налицо несовпадение ожидаемого количества строк (135) и реального (99900). Кроме того, видно, что для выполнения пришлось прочитать существенно больше данных, чем в первый раз (столбец buffer_gets):
select child_number, is_bind_sensitive, is_bind_aware, executions, buffer_gets from v$sql where sql_id=’6pncxxhknwgqc’;
Выполним запрос еще раз:
select * from t where flag = :f;
.
99900 rows selected.
Теперь используется уже новый план, построенный для нового значения переменной связывания (обратите внимание на изменившийся child number и секцию peeked binds):
select * from table(dbms_xplan.display_cursor(format=>’typical +peeked_binds’));
SQL_ID 6pncxxhknwgqc, child number 1
На этот раз оптимизатор правильно оценил число строк (99856, с небольшой погрешностью) и выбрал полное сканирование таблицы. А в библиотечном кэше теперь две версии плана для одного и того же запроса:
select child_number, is_bind_sensitive, is_bind_aware, executions, buffer_gets from v$sql where sql_id=’6pncxxhknwgqc’;
Стремление к минимизации числа планов в кэше вынуждает оптимизатор «споткнуться», прежде чем принять решение о необходимости иметь разные планы для одного запроса. Заметим, что этого можно избежать, заранее вручную сделав подсказку оптимизатору.
Постгрес
В Постгресе нет глобального кэша разобранных запросов. Более того, если не предпринять специальных усилий, то запрос не будет сохраняться и локально в памяти процесса.
В частности, при повторении одного и того же запроса он будет полностью разбираться каждый раз заново. Конечно, написанный таким образом процесс будет работать не оптимально, но это, по крайней мере, не влияет напрямую на остальные процессы.
create table t(
id serial primary key,
n numeric not null
);
insert into t(n)
select 1 from generate_series(1,100000);
analyze t;
Выполним следующий код на PL/pgSQL:
Для того, чтобы сохранить результаты разбора, запрос надо подготовить, а уже затем сохраненный запрос можно многократно использовать:
prepare u(integer) as update t set n = n + 1 where >
execute u(1);
execute u(2);
.
execute u(100000);
Именно это происходит, если в блоке PL/pgSQL вызвать команду SQL без использования execute, как в первом примере. В нашем случае это дает выигрыш по скорости в 3,5 раза:
(А правильный вариант — одна команда SQL — выполняется быстрее еще в три раза.)
Общая схема разбора запроса состоит из следующих этапов:
При подготовке запроса он анализируется и переписывается. Оптимизация же выполняется каждый раз заново при выполнении — таким образом для каждого значения переменных связывания строится свой «частный» план.
Рассмотрим пример неравномерного распределения данных (вместо символьной переменной мы можем использовать логический тип):
alter table t add column
flag boolean;
update t
set flag = mod(id,1000)=0;
create index on t(flag);
Необходимая гистограмма будет автоматически построена при анализе таблицы:
analyze t;
Чтобы узнать, какой план выполнения будет выбран для истинного значения флага, надо воспользоваться командой explain. В Постгресе она осведомлена о значении и типе переменных связывания и показывает именно тот план, с которым команда будет выполнена:
explain execute s1(true);
QUERY PLAN
————————————————————————
Index Scan using t_flag_idx on t (cost=0.29..14.31 rows=110 width=10)
Index Cond: (flag = true)
Filter: flag
Оптимизатор предполагает выбрать 110 строк (также с небольшой погрешностью) и использует доступ по индексу.
Команда explain удобна также тем, что позволяет не только построить план, но и выполнить команду и сразу получить как ожидаемые, так и актуальные значения кардинальности. Продемонстрируем это для другого значения flag:
explain analyze execute s1(false);
QUERY PLAN
——————————————————————————————————
Seq Scan on t (cost=0.00..2958.00 rows=99890 width=10) (actual time=0.043..265.272 rows=99900 loops=1)
Filter: (NOT flag)
Rows Removed by Filter: 100
Execution time: 385.455 ms
В данном случае оптимизатор ожидает получить 99890 строк (на самом деле 99900) и адекватно выбирает полное чтение таблицы.
Здесь возникает проблема, противоположная той, с которой сталкивается Оракл: что, если план не зависит от значений переменных связывания? В этом случае было бы выгодно не оптимизировать запрос каждый раз.
И действительно, Постгрес умеет переходить от «частных» планов к «общему» (generic plan), но делает это не сразу. Первые пять раз запрос оптимизируется в любом случае, а дальше предпочтение отдается общему плану, если его стоимость (по оценке оптимизатора) не превышает средней стоимости частных планов. Число пять здесь — некий компромисс: небольшое значение не дает достаточной статистики стоимости при разных значениях переменных связывания, а большое значение сводит на нет саму оптимизацию.
Рассмотрим этот механизм на примере с равномерным распределением данных:
prepare s2(integer) as select * from t where >
explain execute s2(1);
QUERY PLAN
——————————————————————
Index Scan using t_pkey on t (cost=0.42..8.44 rows=1 width=10)
Index Cond: (id = 1)
Это частный план, что видно по условию «Index Cond: (id = 1)» — здесь указано конкретное число.
Однако если вызвать explain или просто выполнить запрос еще четыре раза с любыми значениями переменных, то произойдет переключение на общий план:
Теперь для запроса будет использоваться уже готовый план, что повышает эффективность выполнения (хотя и может привести к проблеме в случае ошибки расчета стоимости или если первые пять раз окажутся «не показательными»).
Заключение
Решение использовать в Оракле глобальный кэш разобранных запросов приводит к стремлению не записывать в него больше, чем совершенно необходимо — как из-за ограниченного размера и опасности вытеснения полезных планов, так и в силу конкуренции параллельных процессов за доступ к кэшу. Поэтому Оракл начинает с одного общего плана для запроса и только при необходимости переходит к нескольким частным.
Наоборот, решение не использовать в Постгресе глобальный кэш позволяет относиться к лишнему разбору проще. Постгрес наоборот начинает с частных планов и затем при возможности переходит к общему.
Оракл автоматически кэширует планы запросов. От разработчика в этом плане требуется только не забывать использовать переменные связывания, что в первую очередь продиктовано ограничениями глобального кэша. Ввиду серьезности проблемы Оракл даже предусматривает параметр cursor_sharing, принудительно заменяющий все константы на переменные.
Постгрес полностью отдает решение о необходимости сохранения разобранного запроса в руки разработчика — или инструмента разработки. Использование переменных связывания не играет в Постгресе столь драматической роли в производительности (хотя вопросы безопасности от SQL-инъекций актуальны для обеих систем в равной степени).
Если несколько процессов используют одинаковый запрос, в Оракле он будет разобран только один — первый — раз. Остальные процессы воспользуются уже готовым планом в глобальном кэше.
В Постгресе каждый процесс должен будет сам разобрать запрос. Зато одноразовые запросы выполняются без накладных расходов по помещению плана в кэш.
У каждого из решений есть как свои плюсы, так и минусы; в любом случае эти особенности должны учитывать разработчики и администраторы, проектирующие, реализующие и сопровождающие прикладные системы.
