Хотя переменные связывания и улучшают производительность и масштабирование за счет сокращения времени, затрачиваемого на выполнение синтаксического анализа, и объема используемой памяти, применение литеральных значений, а не значений связывания для переменных в действительности приводит к получению более качественных планов выполнения. При принудительном разделении курсора в базе данных в результате установки для параметра CURSOR_SHARING значения EXACT или SIMILAR, некоторые SQL-операторы могут получать квазиоптимальные планы выполнения по каким-то из значений переменных связывания. CBO может создать квазиоптимальный план при считывании значений переменных связывания и принятии решения о том, что значения переменных связывания, используемые в первых подлежащих помещению в разделяемый пул SQL-операторах, не отражают истинных значений переменной. Разработчики и администраторы базы данных иногда прибегают к установке неофициального параметра инициализации _OPTIM_PEEK_USER_BINDS (ALTER SESSION SET "_optim_peek_user_binds"=FALSE;) для лишения базы данных возможности считывать значения переменных связывания. Адаптивное разделение курсора (adaptive cursor sharing) представляет собой куда более элегантный способ для предотвращения создания оптимизатором квазиоптимальных планов выполнения из-за считывания значений переменных связывания.
Oracle полагается на свою технологию “считывания значений переменных связывания” (bind peeking) при первом синтаксическом анализе SQL-оператора. Оптимизатор всегда подвергает новый оператор полному синтаксическому анализу и считывает значения переменных связывания для получения представления о том, как они выглядят.
Первоначальные значения переменных связывания, которые он видит на этапе считывания, оказывают серьезное влияние на выбираемый для оператора план выполнения. Если, например, на этапе считывания эти первоначальные значения диктуют использовать индекс, Oracle будет продолжать использовать индекс, даже если последующие значения будут в действительности диктовать использовать вместо этого операцию полного сканирования таблицы. Из-за того, что считывание значений переменных связывания фактически приводит к созданию квазиоптимальных планов выполнения в подобных случаях, жестко закодированные значения переменных оказываются предпочтительнее значений переменных связывания.
Как показывает вышесказанное, разделение курсора за счет использования переменных связывания может не всегда приводить к получению наилучших (оптимальных) планов выполнения. Использование жестко закодированных значений для переменных на самом деле может давать более оптимальные планы выполнения, чем применение переменных связывания, особенно при работе с очень несимметричными данными. В качестве попытки устранить конфликт между разделением курсора с помощью переменных связывания и оптимизацией запросов Oracle и предлагает механизм адаптивного разделения курсора. В случае применения этого механизма, всякий раз, когда база данных будет вычислять, что дешевле создать новый план выполнения для оператора, чем повторно использовать те же самые курсоры, для оператора будут генерироваться новые дочерние курсоры. Oracle будет все равно стараться сводить количество дочерних курсоров к минимуму и пользоваться преимуществами механизма разделения курсоров, однако пытаться использовать курсоры повторно больше не будет.
Совет. Механизм адаптивного разделения курсора работает автоматически и всегда включен. Отключить его никак нельзя.
Как работает механизм адаптивного разделения курсора
В работе механизма адаптивного разделения курсора важную роль играют две концепции: чувствительность курсора к связыванию и осведомленность курсора о связывании. Если изменение значений переменной связывания ведет к созданию других планов выполнения, курсор называют чувствительным к связыванию (bind-sensitive). Всякий раз, когда база данных выясняет, что требуется создавать новые планы выполнения из-за того, что значения переменной связывания сильно варьируются, переменная признается чувствительной к связыванию. После того, как база данных помечает курсор как являющийся чувствительным к связыванию, он начинает называться осведомленным о связывании (bind-aware).
Важно! Механизм адаптивного разделения курсора работает независимо от обычного механизма разделения курсора.
Ниже приведен пример, демонстрирующий работу механизма адаптивного разделения курсора. Предположим, что в базе данных несколько раз выполняется следующий запрос:
SQL> select * from hr.employees where salary = :1 and department_id = :2;
Как видно, в этом SQL-операторе присутствуют две переменных связывания: SALARY и DEPARTMENT_ID.
При самом первом выполнении нового SQL-оператора база данных считывает значения переменных связывания, вычисляет степень избирательности предиката и делает курсор чувствительным к связыванию. Она создает каждый план выполнения с набором значений избирательности наподобие (0.25, 0.0050), которые свидетельствуют о диапазоне избирательности плана выполнения. Если новые значения переменных связывания подпадают под этот диапазон, оптимизатор использует данный план выполнения повторно, а если нет, то создает новый план выполнения.
На следующем этапе выясняется, является ли курсор осведомленным о связывании. После выполнения первого полного синтаксического анализа база данных далее выполняет только частичный синтаксический анализ выполняемого повторно оператора и сравнивает его статистику со статистикой выполнения полного синтаксического анализа. Если она решает, что курсор осведомлен о связывании, она применяет технологию сопоставления с осведомленным о связывании курсором, когда выполняет запрос снова. Если новая пара значений переменных связывания подпадает под диапазон избирательности какого-то плана выполнения, база данных просто использует повторно этот план; в противном случае она снова проводит полный синтаксический анализ и, соответственно, генерирует новый дочерний курсор с другим планом. Если новое выполнение приводит к получению похожего плана, база данных производит слияние дочерних курсоров, а это значит, что если значения переменных связывания являются приблизительно одинаковыми, операторы будут разделять один и тот же план выполнения.
Осуществление мониторинга за механизмом адаптивного разделения курсора
В представлении V$SQL содержится два столбца с именами IS_BIND_SENSITIVE и IS_BIND_AWARE, которые помогают осуществлять мониторинг за работой механизма
адаптивного разделения курсора в базе данных.
В частности, столбец IS_BIND_SENSITIVE позволяет узнать, является ли курсор чувствительным к связыванию, а столбец IS_BIND_AWARE — пометила ли база данных курсор как осведомленный о связывании. Например, ниже приведен запрос, сообщающий, какие SQL-операторы чувствительны к связыванию, а какие — осведомленными о нем:
SQL> SELECT sql_id, executions, is_bind_sensitive, is_bind_aware FROM v$sql; SQL_ID EXECUTIONS I I -------------- ----------- --- --- 57pfs5p8xc07w 21 Y N 1gfaj4z5hn1kf 4 Y N 1gfaj4z5hn1kf 4 N N ... 294 rows selected. SQL>
В выводе этого запроса столбец IS_BIND_SENSITIVE показывает, будет ли база данных генерировать разные планы выполнения на основе значений переменных связывания. Любой курсор, для которого в столбце IS_BIND_SENSITIVE отображается значения Y, является кандидатом на изменение плана выполнения. Когда база данных планирует использовать множество планов выполнения для оператора на основе наблюдаемых значений в переменных связывания, она отображает для этого оператора значение Y в столбце IS_BIND_AWARE. Это свидетельствует об осознании оптимизатором того, что разные значения переменных связывания будут вести к разными схемам данных, что требует подвергания оператора полному синтаксическому анализу при следующем выполнении. Для принятия решения о том, следует ли менять план выполнения, база данных просчитывает несколько следующих выполнений SQL-оператора. Если она после этого приходит к выводу о необходимости изменения плана выполнения оператора, курсор помечается как осведомленный о связывании, а в столбец IS_BIND_AWARE для этого оператора помещается значение Y. Осведомленный о связывании курсор представляет собой такой курсор, для которого база данных на самом деле изменила план выполнения на основании обнаруженных значений в переменных связывания.
Ниже перечислены представления, которые можно использовать для управления механизмом адаптивного разделения курсора.
- V$SQL_CS_HISTOGRAM. Это представление показывает, как распределяется количество выполнений в виде гистограммы хронологии выполнений.
- V$SQL_CS_SELECTIVITY. Это представление показывает, как выглядят диапазоны избирательности, хранящиеся в курсорах для предикатов с переменными связывания.
- V$SQL_CS_STATISTICS. В этом представлении содержатся касающиеся выполнений статистические данные курсора с различными собранными базой данных наборами значений переменных связывания.
Регулярное повторное создание таблиц и индексов
В базе данных с большим количеством DML-операций индексы со временем могут становиться несбалансированными. Поэтому очень важно регулярно создавать такие индексы повторно, чтобы запросы могли выполняться быстрее. Повторно создавать индексы может быть необходимо, если нужно изменить их характеристики хранения или консолидировать их и тем самым сократить фрагментацию. Применяйте для повторного создания индекса оператор ALTER INDEX...REBUILD, если старый индекс должен оставаться доступным во время создания нового. (В качестве альтернативного варианта можно сначала удалить индекс, а потом воссоздавать его заново.)
При повторном создании индексов следует включить оператор COMPUTE STATISTICS, чтобы после воссоздания не приходилось отдельно собирать статистические данные. Разумеется, в среде, работающей 24 часа в сутки 7 дней в неделю, необходимо использовать оператор ALTER INDEX...REBUILD ONLINE, чтобы не оказывать влияния на доступ пользователей к базе данных. Очень важно, чтобы при воссоздании индекса в оперативном режиме таблицы не подвергались массе DML-операций, поскольку механизм выполнения этой операции в оперативном режиме при таких обстоятельствах может не работать так, как ожидается. На самом деле он может даже внезапно начать мешать выполнению одновременных обновлений пользователями.
Освобождение неиспользуемого пространства
Утилита Segment Advisor запускается автоматически во время запланированного ночного процесса обслуживания и выдает рекомендации касательно тех объектов, которые можно сжать, чтобы освободить неэффективно используемое пространство. Главное запомнить, что для применения этой утилиты необходимо использовать локально управляемые табличные пространства с опцией Automatic Segment Space Management (Автоматическое управление пространством в сегментах). Сжатие сегментов позволяет экономить пространство, и, что еще более важно, улучшать производительности за счет понижения маркера максимального уровня заполнения сегментов и устранения неизбежной фрагментации, которая происходит со временем в объектах, подвергающихся большому количеству операций обновления и удаления.
Кэширование небольших таблиц в памяти
Если приложение не использует данные таблицы повторно на протяжении длительного времени, эти данные могут удаляться из SGA и снова нуждаться в считывании с диска. Небольшие таблицы можно безопасно закреплять в кэше буферов следующим образом:
SQL> ALTER TABLE hr.employees CACHE; Table altered. SQL>
