Настройка производительности базы данных Oracle подразумевает оптимизацию SQL-кода и калибровку используемых базой ресурсов. Настройку SQL-кода обычно выполняют разработчики, а администратор баз данных просто облегчает их усилия в этой области за счет настройки соответствующих параметров инициализации, включения трассировки и т.д. Тем не менее, существует еще несколько стратегий, которые администратор баз данных может реализовать для улучшения обработки SQL-кода в своей базе данных.
В некоторых случаях администратор баз данных (DBA) и разработчики могут работать над оптимизацией приложения вместе. Однако что, если у администратора базы данных нет возможности вносить изменения в код, как бывает в случае работы с “коробочными” (packaged) приложениями. Или что если даже разработчики знают, о необходимости внесения в код серьезных изменений для улучшения его производительности, но ограничения времени и бюджета делают выполнение этой процедуры невозможным? Существует несколько способов, которые могут помочь в подобных ситуациях и без изменения самого кода SQL.
Довольно часто администраторы баз данных жалуются на медленное время отклика из-за плохо написанного SQL-кода. Это можно вполне считать универсальной жалобой администраторов баз данных, которым приходится иметь дело с последствиями неэффективного кода. Хорошо спроектированное и закодированное приложение со всеми правильными соединениями и продуманными стратегиями индексирования является замечательным случаем, но гораздо чаще подобного идеального стечения обстоятельств не происходит. Согласно теории следующим наилучшим вариантом должно быть принятие всех возможных мер для оптимизации процесса обработки в приложении в пределах ограничений, накладываемых его дизайном.
С учетом вышесказанного, давайте рассмотрим некоторые из важным способов, которыми можно помогать улучшать производительность запросов в приложении даже тогда, когда немедленно изменить соответствующим образом сам код не представляется возможным.
Использование секционированных таблиц
Секционированные таблицы обычно дают потрясающие улучшения в производительности и легко поддаются администрированию. Разбиение таблицы на несколько секций, по сути, приводит к ограничению того объема данных, который требуется изучать для удовлетворения запросов. Поэтому при наличии больших таблиц, достигающих десятков миллионов строк, следует обязательно рассматривать вариант их секционирования.
В Oracle 11g поддерживается пять схем секционирования таблиц, о которых более подробно речь шла в этом блоге. Индексировать секционированные таблицы можно несколькими способами, в зависимости от потребностей приложения. В обслуживании секций тоже нет ничего сложного, и потому все эти замечательные преимущества, которые предоставляют секционированные таблицы, действительно стоят тех дополнительных усилий, которые для них требуется прикладывать.
Применение технологий сжатия
База данных Oracle позволяет применять технологию сжатия таблиц (table compression) для сжатия таблиц, секций таблиц и материализованных представлений. Сжатие таблиц помогает сокращать требования по объему необходимого таблицам пространства и улучшать производительность запросов. Oracle сжимает таблицы за счет устранения дублированных значений в блоках данных и их замены алгоритмами для воссоздания данных при возникновении такой необходимости. Технология сжатия таблиц особенно подходит для хранилищ данных и баз данных OLAP, но в базах данных OLTP ее тоже можно применять довольно продуктивно. Чем больше сжимаемая таблица, тем больше преимуществ принесет ее сжатие. Ниже приведен пример простого оператора сжатия таблицы:
SQL> CREATE table sales_compress 2 COMPRESS 3 AS SELECT * FROM sh.sales; Table created. SQL>
Технология сжатия индексного ключа (index key compression) служит для сжатия столбцов первичного ключа в индекс-таблицах.(Index-Organized Tables — IOT). Эта технология позволяет не только экономить используемое для хранения пространство, но и улучшать производительность запросов. Она работает за счет удаления дублированных значений столбцов из индекса.
Для сжатия индекса достаточно просто добавить после оператора создания индекса ключевое слово COMPRESS, как показано ниже:
SQL> CREATE INDEX item_product_x 2 ON order_items(product_id) 3 TABLESPACE order_items_indx_01 4 COMPRESS; Index created. SQL>
Для получения подтверждения в экономии пространства и времени можно провести некоторые тесты прямо во время выполнения операторов создания. Тестировать производительность запросов и замерять улучшения в ней можно позже.
Использование материализованных представлений
При необходимости иметь дело с большими объемами данных, следует всерьез рассмотреть вариант использования материализованных представлений для улучшения времени отклика. Материализованные представления (materialized views) — это объекты с данными внутри (каковыми обычно являются суммарные данные из базовых таблиц). Например, в них могут сохраняться результаты выполняемых заранее дорогостоящих операций соединения. При запросе пользователями базовой таблицы Oracle автоматически переписывает запрос так, чтобы он получал доступ к материализованному представлению, а не самим таблицам.
Материализованные представления сокращают потребность в использовании нескольких сложных запросов, поскольку позволяют заранее вычислять агрегаты. Операции соединения больших таблиц и агрегирование данных являются дорогостоящими в плане потребления ресурсов, а материализованные представления значительно сокращают время отклика при выполнении сложных запросов к таким большим таблицам. При отсутствии уверенности в том, какие материализованные представления необходимо создать, не стоит волноваться, потому что с помощью поставляемого Oracle пакета DBMS_OLAP можно получить рекомендации относительно идеальных материализованных представлений.
Ранее в блогах о материализованных представлениях рассказывалось более подробно и показывалось, как можно использовать инструмент SQL Access Advisor (Советник по оптимизации путей доступа для SQL-кода) для получения рекомендаций касательно создания материализованных представлений и журналов материализованных представлений.
Использование хранимых планов выполнения для стабилизации CBO
Как уже упоминалось ранее в этой статье, CBO не всегда использует одни и те же стратегии выполнения. Изменения в версиях Oracle или параметрах инициализации, касающихся выделения памяти, могут вынуждать CBO изменять свои планы. Для обеспечения гарантии того, что план выполнения будет оставаться стабильными несмотря ни на какие изменения в среде базы данных, можно применить предлагаемое Oracle средство стабилизации плана (plan stability feature).
Это средство предусматривает использование хранимых планов выполнения (stored outlines) для сбережения текущих планов выполнения даже в случае изменения статистических данных и режима оптимизатора. В случае его применения CBO будет использовать один и тот же план выполнения с идентичными путями доступа при каждом выполнении того же самого запроса. Уловка состоит в том, что запрос должен быть совершенно идентичным, когда нужно, чтобы применялся хранимый план.
Внимание! Использование хранимых планов выполнения для сбережения текущего эффективного плана выполнения приводит к ограничению способности Oracle изменять свои планы выполнения динамическим образом на основе изменений в среде базы данных или в статистических данных. Поэтому они должны применяться только по веским причинам, например, для поддержания похожих планов в распределенных приложениях.
На первый взгляд в средстве хранимых планов выполнения нет ничего впечатляющего. Однако давайте рассмотрим простой пример, чтобы увидеть, как хранимый план может быть полезен в реальной производственной среде.
Предположим, что есть система, которая работает вполне удовлетворительно, но возникает необходимость добавить к одной из ее таблиц индекс. Добавление нового индекса может невольно привести к изменению используемых CBO планов выполнения и, как следствие, замедлению обработки SQL-запросов, которые ранее обрабатывались быстро. Для переделки исходных запросов в целях исправления ситуации может потребоваться масса усилий, времени и тестов. Однако если были созданы хранимые планы, никаких проблем подобного рода не возникнет. После создания хранимого плана выполнения Oracle хранит его до тех пор, пока он не будет удален. В следующем разделе блога рассказывается о том, как реализовать преднамеренную стабилизацию планов выполнения в базе данных.
В каких случаях нужно использовать хранимые планы выполнения
Хранимые планы выполнения удобно использовать при планировании миграций с одной версии Oracle на другую. CBO может вести себя по-разному в разных версиях,
и, следовательно, применение хранимых планов для сбережения текущей производительности приложения может помочь сократить риск. Хранимые планы также полезно использовать при модернизации приложений. Они служат гарантией того, что пути выполнения, которые применялись запросами в тестовом экземпляре, будут успешно перенесены в производственный экземпляр.
Особенно полезными хранимые планы выполнения являются тогда, когда у пользователей приложений имеется такая информация о среде, которой CBO не владеет. Разрешая осуществлять непосредственное редактирование хранимых планов выполнения, Oracle тем самым позволяет настраивать SQL-запросы без внесения изменений в базовое приложение. Это оказывается особенно полезным в случае “коробочных” приложений, получать доступ к исходному коду которых не возможно.
Реализация стабилизации планов выполнения
В реализации стабилизации планов выполнения нет ничего сложного. Это подразумевает под собой проверку того, что перечисленные ниже параметры инициализации находятся в согласованном состоянии во всех средах. Для первых двух параметров нужно устанавливать значение TRUE. Что же касается последнего параметра, OPTIMIZER_FEATURES_ENABLE, то для него по умолчанию устанавливается значение 11.1.0.6, но в случае изменения этого значения в какой-нибудь одной среде, необходимо также обязательно изменять его соответствующим образом и во всех остальных средах. Итак, вот эти необходимые параметры инициализации:
- QUERY_REWRITE_ENABLED
- STAR_TRANSFORMATION_ENABLED
- OPTIMIZER_FEATURES_ENABLE
Создание хранимых планов выполнения
Управление самими хранимыми планами выполнения осуществляется с помощью поставляемых Oracle пакетов DBMS_OUTLN и DBMS_OUTLN_EDIT. Создавать хранимые планы для всех текущих SQL-запросов можно установкой для параметра инициализации CREATE_STORED_OUTLINES значения TRUE.
Пользователь OUTLN включается в состав базы данных при ее создании и назначается в ней владельцем всех хранимых планов. Сохраняются такие планы в таблице OL$. В листинге 1 показано, как выглядит структура этой таблицы.
SQL> DESC OL$ Name Null? Type ------------ ----- ------------ OL_NAME VARCHAR2(30) SQL_TEXT LONG TEXTLEN NUMBER SIGNATURE RAW(16) HASH_VALUE NUMBER HASH_VALUE2 NUMBER CATEGORY VARCHAR2(30) VERSION VARCHAR2(64) CREATOR VARCHAR2(30) TIMESTAMP DATE FLAGS NUMBER HINTCOUNT NUMBER SPARE1 NUMBER SPARE2 VARCHAR2(1000) SQL>
В столбце SQL_TEXT сохраняется код SQL-оператора, для которого создается хранимый план выполнения. Помимо таблицы OL$ пользователь OUTLN еще также может пользоваться и таблицами OL$HINTS и OL$NODES для управления хранимыми планами.
Для пользователя OUTLN и таблиц OL$, OL$HINTS и OL$NODES можно создавать специальное табличное пространство. По умолчанию они создаются в табличном пространстве System. После создания для пользователя OUTLN нового табличного пространства в него можно переместить и сами таблицы с помощью утилит для экспорта и импорта.
Создание хранимых планов выполнения на уровне базы данных
Чтобы позволить Oracle автоматически создавать хранимые планы выполнения для всех SQL-операторов, нужно применить параметр CREATE_STORED_OUTLINES, как показано ниже:
CREATE_STORED_OUTLINES = TRUE
С помощью оператора ALTER SYSTEM можно динамически включить функцию создания хранимых планов выполнения для всей базы данных:
SQL> ALTER SYSTEM SET CREATE_STORED_OUTLINES=TRUE; System altered. SQL>
В обоих случаях всем создаваемым Oracle шаблонам будет присваиваться категория DEFAULT. При желании можно указывать для хранимых планов выполнения категорию с каким-то другим именем. В случае установки параметра CREATE_STORED_OUTLINES база данных будет создавать хранимый план для каждого отличающегося SQL-оператора. Это означает, что при наличии слишком большого количества обрабатываемых SQL- операторов в табличном пространстве System может закончиться место. Из-за этого пользоваться параметром CREATE_STORED_OUTLINES следует осторожно. Чтобы сохранить накладные расходы на низком уровне, вместо этого, возможно, лучше создавать хранимые планы на уровне сеанса или вообще на уровне только одного SQL-оператора; этому посвящен следующий раздел блога.
Создание хранимых планов выполнения для конкретных операторов
Создавать хранимые планы выполнения для конкретного оператора или ряда операторов можно с помощью оператора ALTER SESSION, как показано ниже:
SQL> ALTER SESSION SET create_stored_outlines = true; Session altered. SQL>
Для всех операторов, которые будут выполняться после обработки данного оператора ALTER SESSION, соответствующие хранимые планы будут сохраняться автоматически.
Если необходимо создать хранимый план выполнения только для конкретного SQL- оператора, можно воспользоваться оператором CREATE OUTLINE. Для его выполнения понадобится привилегия CREATE OUTLINE. Ниже приведен пример создания простого хранимого плана выполнения для одного оператора SELECT, выполняемого в отношении таблицы employees:
SQL> CREATE OUTLINE test_outline 2 ON SELECT employee_id, last_name 3 FROM hr.employees; Outline created. SQL>
Для удаления хранимого плана выполнения можно использовать оператор DROP OUTLINE, как показано ниже:
SQL> DROP OUTLINE test_outline; Outline dropped. SQL>
Использование хранимых планов выполнения
После создания хранимых планов выполнения Oracle не будет их применять автоматически. Включить использование хранимых планов нужно вручную, установив для USE_STORED_OUTLINES значение TRUE с помощью либо оператора ALTER SESSION, либо оператора ALTER SYSTEM. Ниже приведен пример включения применения хранимых планов выполнения на уровне базы данных с помощью ALTER SYSTEM:
SQL> ALTER SYSTEM SET use_stored_outlines=true; System altered. SQL>
Включать использование хранимых планов можно также и установкой значения TRUE для параметра инициализации USE_STORED_OUTLINES. В противном случае база данных не будет пользоваться созданными хранимыми планами.
Редактирование хранимых планов выполнения
При использовании механизма стабилизации планов можно легко изменять пути доступа к хранимым планам выполнения. Для внесения изменений служит как пакет DBMS_OUTLN_EDIT, так и интерфейс OEM.
