Управление планами выполнения SQL запросов в базе Oracle

Стас Белков

Стас Белков

Автор статьи. Известный специалист в мире IT. Консультант по продуктам и решениям Oracle. Практикующий программист и администратор баз данных. Подробнее.

План выполения позволяет оптимизировать SQL запросы и повысить их производительность в OracleИзменения наподобие обновления баз данных до более новой версии или даже более мелкие изменения вроде добавления или удаления индекса могут влиять на планы выполнения SQL. Выше в этой главе было показано, как использовать предлагаемый в Oracle механизм хранимых шаблонов в качестве способа для сбережения планов выполнения SQL и тем самым предотвращать ухудшение производительности при подвергании базы данных серьезным изменениям, в частности, ее обновления до более новой версии. Однако для предотвращения воздействия серьезных изменений на производительность системы в Oracle рекомендуют применять новый механизм SPM (SQL Plan Management — Управление планами выполнения SQL). Этот механизм позволяет сберегать производительность базы данных при всех следующих видах системных изменений:

  • обновление базы данных Oracle до более новой версии;
  • использование новой версии оптимизатора;
  • внесение изменений в параметры оптимизатора;
  • внесение изменений в настройки системы;
  • внесение изменений в определения схем и метаданных;
  • развертывание новых модулей приложения.

Хотя SQL-операторы можно настраивать с помощью SQL Tuning Advisor и ADDM, такой подход в лучшем случае является реактивным и требует вмешательства администратора баз данных. Применение же механизма SPM является, скорее, профилактической (проактивной) мерой. В таком случае база данных производит расширение (evolution) планов выполнения SQL с помощью новых базовых линий планов выполнения SQL (SQL plan baselines), которые представляют собой наборы эффективных планов выполнения, захваченных базой данных за некоторый период времени. Она позволяет новому плану выполнения становиться частью базовых линий планов выполнения данного SQL-оператора только в том случае, если он не вызывает ухудшений производительности. Она всегда использует для выполнения SQL-операторов только те планы выполнения, которые входят в состав их базовых линий планов выполнения, и тем самым добивается сбережения производительности базы данных при внесении в систему серьезных изменений, наподобие обновления базы данных до более новой версии.

В частности, механизм SPM оказывается очень полезным при выполнении обновления до версии Oracle Database 11g. При обновлении до этой версии, скажем, от Oracle Database 10g, сначала можно оставить для параметра OPTIMIZER_FEATURES_ENABLE значение 10.2. После же выполнения механизмом SPM сбора планов выполнения и их сохранения в базовых линиях планов выполнения SQL, этот параметр можно установить в 11.1. Такой подход гарантирует получение всех новых возможностей выпуска 11g без подверганию риску производительности SQL: производительность остается на том же уровне благодаря использованию базовых линий планов выполнения SQL, которые в этом отношении похожи на поддерживаемые базой данных хранимые планы выполнения.

 

Базовые линии планов выполнения SQL

При использовании механизма SQL Plan Management база данных поддерживает хронологию планов выполнения (plan history), т.е. фиксирует все планы выполнения SQL, которые генерируются оптимизатором для SQL-оператора на протяжении определенного периода времени. Оптимизатор использует эту хронологию для вычисления оптимального плана выполнения для оператора. Однако не все планы в ней являются приемлемыми. База данных считает приемлемыми только те планы выполнения, которые не ведут к ухудшению производительности по сравнению с другими планами в рамках хронологии. Базовая линия планов выполнения SQL-оператора, по сути, представляет собой набор все принятых планов в хронологии планов данного оператора.

Самый первый план для оператора всегда считается принятым, поскольку его пока еще не с чем сравнивать. На этом этапе базовая линия планов выполнения SQL и хронология планов для нового SQL-оператора выглядят идентично. Следующие более новые планы выполнения оператора будут автоматически включаться в хронологию его планов, но добавляться в базовую линию планов выполнения только в случае верификации и получения подтверждения в том, что они не вызывают спада в производительности. Верификацией планов выполнения SQL занимается советник Automatic SQL Tuning Advisor (Советник по автоматической настройке SQL-операторов), и эта задача является одной из его автоматических задач по обслуживанию. В рамках этой задачи он отыскивает SQL-операторы с высокой степенью нагрузки и сохраняет принятые для них планы в соответствующей им базовой линии планов выполнения SQL.

Управлять базовыми линиями планов выполнения SQL можно либо с помощью пакета DBMS_SPM, либо посредством приложения Enterprise Manager. Необходимые для этого шаги описаны в следующих разделах.

 

Захват базовых линий планов выполнения SQL

Захватывать базовые линии планов выполнения SQL можно двумя способами: либо позволив базе данных захватывать планы автоматически, либо выполнив их загрузки в базу данных вручную. Оба этих приема детально рассматриваются далее.

 

Автоматический захват планов

По умолчанию база данных не поддерживает хронологию планов для выполняемых SQL-операторов. Для того чтобы она начала захватывать базовые линии планов выполнения SQL, потребуется установить для параметра инициализации OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES значение TRUE (по умолчанию его значение равно FALSE). После установки для этого параметра значения TRUE база данных будет автоматически создавать и поддерживать хронологию планов для всех воспроизводимых SQL-операторов, которые выполняются в базе данных и могут повториться снова.


Совет. За счет применения инструмента SQL Performance Analyzer (Анализатор производительности SQL-операторов), о котором более подробно будет рассказываться в главе 20, можно выявлять SQL-операторы, производительность которых вероятнее всего ухудшится в результате обновления базы данных, скажем, до Oracle Database 11g Release 1 с Oracle Database 10g Release 2, а потом захватывать планы выполнения этих операторов, загружать их в компонент SQL Management Base в уже обновленной базе данных и тем самым избегать спада производительности.


 

Выполнение загрузки планов вручную

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

Загружать планы можно либо из набора STS (SQL Tuning Set — набор настроек SQL), либо из кэша курсора базы данных. Далее демонстрируются оба этих приема.

Для загрузки планов выполнения SQL из STS нужно выполнить функцию LOAD_PLANS_FROM_SQLSET из пакета DBMS_SPM. Но перед этим сначала потребуется создать пустой STS, как показано ниже: 

begin
dbms_sqltune.create_sqlset(
sqlset_name => 'testset1',
description => 'Test STS to capture AWR Data');
end;
/

Затем следует загрузить этот новый набор STS операторами SQL из снимков, хранящихся в AWR (Automatic Workload Repository — Автоматический репозиторий рабочей нагрузки): 

declare
baseline_cur dbms_sqltune.sqlset_cursor;
begin
open baseline_cur for
select value(p) from table (dbms_sqltune.select_workload_repository(
'peak baseline',null,null,'elapsed_time',null,null,null,20)) p;
dbms_sqltune.load_sqlset (
sqlset_name => 'testset1',
populate_cursor => baseline_cur);
end;
/

В этом примере в STS из базовой линии пиковых операторов в AWR (AWR peak baseline) загружается 20 верхних операторов на основании критерия затраченного времени (elapsed_time). Выбирать эти 20 операторов из AWR помогает ссылочный курсор (REF CURSOR) и табличная функция.

Далее останется только загрузить планы из STS в базовую линию планов выполнения SQL, выполнив функцию LOAD_PLANS_FROM_SQLSET

declare
test_plans pls_integer;
begin
test_plans := dbms_spm.load_plans_from_sqlset(
sqlset_name => 'testset1');
end;
/

В качестве источника планов, подлежащих загрузке в базовую линию планов выполнения SQL, вместо STS можно также использовать и кэш курсора. Ниже приведен пример, демонстрирующий, как загружать планы выполнения SQL из кэша курсора с помощью функции LOAD_PLANS_FROM_CURSOR_CACHE:

declare
test_plans pls_integer;
begin
test_plans := dbms_spm.load_plans_from_cursor_cache (
sql_id => '123456789999')
return pls_integer;
end;
/

 

Выбор базовых линий планов выполнения SQL

Как бы ни собирались планы выполнения SQL — с использованием AWR в качестве источника или из кэша курсора базы данных — нужно обязательно активизировать
использование этих планов за счет установки параметра инициализации OPTIMIZER_USE_SQL_PLAN_BASELINES в TRUE. Поскольку значение TRUE устанавливается для этого параметра по умолчанию, это означает, что базовые линии планов выполнения активизируются по умолчанию.

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


Совет. Просматривать план выполнения для конкретного SQL_HANDLE в базовой линии планов выполнения можно с помощью функции DISPLAY_SQL_PLAN_BASELINE из пакета DBMS_XPLAN.


В конечном итоге получается, что оптимизатор будет всегда выбирать либо тот план выполнения, который является наилучшим по стоимости, либо тот, который является принятым и хранится в базовой линии планов выполнения SQL. Узнать, как точно будет выглядеть конечная стратегия оптимизатора в этом отношении, можно из столбца OTHER_XML в выводе EXPLAIN PLAN таблицы PLAN_TABLE.

 

Расширение базовых линий планов выполнения

База данных постоянно проверяет новые планы, чтобы расширять (evolve) базовые линии планов выполнения SQL. Под расширением подразумевается преобразование не принятого плана в принятый и превращение его в часть базовой линий планов выполнения SQL. Как уже упоминалось ранее, для преобразования не принятого плана в принятый и включения его в базовую линию планов выполнения он должен обладать более высокими показателями по производительности по сравнению с уже принятым планом. В случае выполнения загрузки SQL-планов вручную, формально расширять планы нет никакой необходимости, поскольку каждый загружаемый подобным образом план автоматически считается принятым. Те планы, однако, которые база данных захватывает автоматически, необходимо формально расширять до базовых линий планов выполнения SQL.

Расширять базовые линии планов выполнения SQL можно либо с применением функции EVOLVE_SQL_PLAN_BASELINE, либо с помощью инструмента SQL Tuning Advisor. Ниже приведен пример, демонстрирующий использование функции EVOLVE_SQL_PLAN_BASELINE для добавления новых принятых планов в базовую линию планов выполнения SQL: 

SQL> exec dbms_spm.evolve_sql_plan_baseline (sql_handle => '123456789111');

В этом примере используется атрибут SQL_HANDLE для указания на план конкретного SQL-оператора, но опустив этот атрибут, можно заставлять Oracle расширять все не принятые планы в базе данных. При желании можно также предоставлять список подлежащих расширению планов SQL, задавая атрибута PLAN_LIST.


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


 

Фиксированные базовые линии планов выполнения SQL

Допустимое количество принимаемых планов для SQL-операторов можно ограничить, устанавливая для плана атрибут FIXED в значение YES. При фиксации плана в базовой линии планов выполнения подобным образом, оптимизатор отдает предпочтение ему, а не тем, которые не были зафиксированы, даже если какие-то из них являются менее дорогостоящими в плане выполнения. База данных прекращает расширять фиксированную линию планов SQL, но всегда остается возможность выполнения ее расширения вручную просто добавлением в нее нового плана.

Следующий запрос к представлению DBA_SQL_PLAN_BASELINES позволяет увидеть все важные атрибуты планов в базовой линии планов выполнения SQL: 

SQL> SELECT sql_handle, sql_text, plan_name, origin, enabled, accepted,
fixed, autopurge
FROM dba_sql_plan_baselines;
SQL_HANDLE  SQL_TEXT       PLAN_NAME        ORIGIN         ENA    ACC   FIX   AUT
----------  -----------    -------------    ------------   ----   ---   ---   ---
SYS_SQL_02a delete from... SYS_SQL_PLAN_930 AUTO-CAPTURE   YES    YES   NO    YES
SYS_SQL_a6f SELECT...      SYS_SQL_PLAN_ael AUTO-CAPTURE   YES    YES   NO    YES
SQL>

Оптимизатор будет использовать только те планы, которые являются активными и принятыми (т.е. имеют в столбцах ENA и ACC значение YES).

 

Управление базовыми линиями планов выполнения SQL

Все планы выполнения SQL, которые хранятся в базовой линии планов выполнения SQL-оператора, можно просматривать с помощью функции DISPLAY_SQL_PLAN_BASELINE, содержащейся в пакете DBMS_XPLAN. Например:

SQL> set serveroutput on
SQL> set long 100000
SQL> SELECT * FROM table(
2 dbms_xplan.display_sql_plan_baseline(
3 sql_handle => 'SYS_SQL_ba5e12ccae97040f',
4* format => 'basic'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
SQL handle: SYS_SQL_ba5e12ccae97040f
SQL text: select t.week_ending_day, p.prod_subcategory, sum(s.amount_sold) as
dollars, s.channel_id,s.promo_id from sales s,times t, products p where
s.time_id = t.time_id and s.prod_id = p.prod_id and s.prod_id>10 and
s.prod_id  

В этом примере вывод показывает, что план выполнения SQL был захвачен автоматически и в текущий момент является активным и принятым. Вдобавок он показывает, что план не является фиксированным.


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


 

Компонент SQL Management Base

База данных сохраняет информацию о базовых линиях планов выполнения SQL в компоненте SMB (SQL Management Base — база управления выполнением SQL), который находится в табличном пространстве Sysaux. Размером этого компонента и периодом сохранности в нем данных можно управлять путем установки (с помощью пакета DBMS_SPM) соответствующих значений для параметров SPACE_BUDGET_PERCENT и PLAN_RETENTION_WEEKS. Узнать значения этих параметров в текущий момент можно следующим запросом: 

SQL> SELECT parameter_name, parameter_value
FROM dba_sql_management_config;
PARAMETER_NAME            PARAMETER_VALUE
-----------------------  ------------------
SPACE_BUDGET_PERCENT            30
PLAN_RETENTION_WEEKS            53
SQL> 

Параметр SPACE_BUDGET_PERCENT отвечает за то, сколько места (в процентах) SBM может занимать в табличном пространстве Sysaux. По умолчанию его значение составляет 10%, но вообще для него можно устанавливать любое значение от 1% до 50%. Можно удалять устаревшие базовые линии планов выполнения SQL и профили SQL из SMB для высвобождения места, а можно и увеличивать размер табличного пространства Sysaux. Для изменения значения параметра SPACE_BUDGET_PERCENT служит процедура CONFIGURE:

SQL> EXEC dbms_spm.configure ('space_budget_percent', 40);

В данном случае процедура CONFIGURE указывает, что SPM может занимать до 40% места в табличном пространстве Sysaux.


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

SQL> exec dbms_spm.configure ('plan_retention_weeks', 105);

Кроме того, можно удалять из SMB только конкретные базовые линии планов выполнения, как показано в следующем примере:

SQL> exec dbms_spm.purge_sql_plan_baseline(''SYS_SQL_PLAN_b5429511dd6ab0f'); 

Узнать текущие параметры пространства и хранения у SMB можно, выполнив запрос к представлению DBA_SQL_MANAGEMENT_CONFIG.

 

Применение опции параллельного выполнения

Опция параллельного выполнения может заставить SQL выполняться быстрее и особенно подходит для баз данных типа хранилищ данных. Устанавливается она как на уровне всей базы данных, так и на уровне отдельных таблиц. В случае увеличения степени параллелизма для таблицы Oracle может принимать решение выполнять больше операций полного сканирования таблицы вместо использования индекса, потому что стоимость полного сканирования таблицы после этого может оказываться ниже стоимости сканирования индекса. При желании применять параллельные операции в среде OLTP, необходимо удостовериться в наличии достаточного количества процессоров на машине, во избежание превращения ЦП в узкое место.

 

Другие задачи администратора баз данных

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

 

Сбор статистических данных по системе

Даже в случае использования механизма Automatic Optimizer Statistics Collection (Автоматический сбор статистических данных для оптимизатора), Oracle не будет собирать статистические данные по системе. Как уже объяснялось ранее в этой главе, такие статистические данные должны собираться самостоятельно, чтобы оптимизатор Oracle мог точно вычислять альтернативные планы выполнения.

 

Частое обновление статистических данных

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

То, насколько часто следует запускать пакет DBMS_STATS для сбора статистических данных, зависит от природы самих данных. В приложениях с умеренным количеством DML-транзакций будет вполне хватать и сбора статистических данных раз в неделю. При наличии причин полагать, что данные подвергаются серьезным изменениям ежедневно, нужно планировать выполнение сбора статистических данных на каждый день.

 

Использование гистограмм

Обычно CBO предполагает, что данные распределяются по таблице равномерно. Бывают, однако, случаи, когда данные в таблице не распределяются равномерным образом. При чрезвычайно ассиметричном распределении данных в таблице, лучше использовать для хранения статистических данных по столбцам гистограммы (histograms). Если данные в таблице распределяются очень несимметрично по отношению к некоторым значениям, присутствие гистограмм обеспечивает более эффективными методами доступа к ним. Для изображения распределения данных в столбце в гистограммах применяются столбцы (buckets), и Oracle может использовать эти столбцы для выяснения того, насколько несимметричным является распределение данных.

В частности, в базе данных Oracle поддерживаются следующие гистограммы.

  • Гистограммы, сбалансированные по высоте (height-based histograms), в которых значения столбцов делятся на отдельные полосы, в каждой из которых содержится приблизительно равное число строк. То есть для таблицы со 100 строками такая гистограмма могла бы состоять из 10 столбцов с 10 строками в каждом.
  • Частотные гистограммы (frequency-base histograms), в которых количество столбцов определятся на основании числа различающихся значений в столбце. В каждом из столбцов содержатся только данные с одинаковыми значениями.

 

Создание гистограмм

Гистограммы создаются за счет применения в процедуре из пакета DBMS_STATS (например, GATHER_TABLE_STATS, GATHER_DATABASE_STATS и т.д.) атрибута METHOD_OPT. Для этого атрибута допускается как указывать свои собственные требования по созданию гистограмм с помощью конструкции FOR COLUMNS, так и использовать значение AUTO или SKEWONLY. В случае установки для него значения AUTO, Oracle будет принимать решение о том, для каких столбцов следует собирать гистограммы, на основании показателя распределения данных и рабочей нагрузки, а в случае установки значения SKEWONLY — на основании только показателя распределения данных в столбцах. Ниже идут два примера, демонстрирующие применение конструкции FOR COLUMNS для задания специфических показателей, на основании которых должны создаваться гистограммы.

Первый пример показывает, как при сборе статистических данных для оптимизатора создать сбалансированную по высоте гистограмму: 

SQL> BEGIN
DBMS_STATS.GATHER_table_STATS (OWNNAME => 'HR', TABNAME => 'BENEFITS',
METHOD_OPT => 'FOR COLUMNS SIZE 10 Number_of_visits');
END;

Второй пример показывает, как создать частотную гистограмму:

SQL> BEGIN
DBMS_STATS.GATHER_table_STATS(OWNNAME => 'HR', TABNAME => 'PERSONS',
METHOD_OPT => 'FOR COLUMNS SIZE 20 department_id');
END; 

 

Просмотр данных гистограмм

Для просмотра данных гистограмм можно использовать представление DBA_TAB_COL_STATISTICS. Например, ниже приведены два запроса, с помощью которых можно просмотреть информацию о количестве столбцов (num_buckets) и количестве различающихся значений (num_distinct) в сбалансированной по высоте и частотной гистограммах, которые были созданы в предыдущем разделе:

SQL> SELECT column_name, num_distinct, num_buckets, histogram
FROM USER_TAB_COL_STATISTICS
WHERE table_name = 'BENEFITS' AND column_name = 'NUMBER_OF_VISITS';
COLUMN_NAME         NUM_DISTINCT   NUM_BUCKETS   HISTOGRAM
-----------------   ------------   -----------   ---------------
NUMBER_OF_VISITS        320            10        HEIGHT BALANCED
SQL> SELECT column_name, num_distinct, num_buckets, histogram
FROM USER_TAB_COL_STATISTICS
WHERE table_name = 'PERSONS' AND column_name = 'DEPARTMENT_ID';
COLUMN_NAME         NUM_DISTINCT   NUM_BUCKETS    HISTOGRAM
-----------------   ------------   ------------   ----------
DEPARTMENT_ID            8              8         FREQUENCY 

 

Вас заинтересует / Intresting for you:

Создание БД Oracle 12c с макси...
Создание БД Oracle 12c с макси... 4272 просмотров Андрей Васенин Mon, 20 Aug 2018, 13:43:20
Настройка памяти базы данных O...
Настройка памяти базы данных O... 19212 просмотров Stas Belkov Sat, 07 Jul 2018, 15:44:14
Мониторинг Oracle через метрик...
Мониторинг Oracle через метрик... 5060 просмотров sepia Tue, 21 Nov 2017, 13:18:05
Oracle ADDM - автоматическая д...
Oracle ADDM - автоматическая д... 7672 просмотров Алексей Вятский Tue, 21 Nov 2017, 13:18:05
Войдите чтобы комментировать

apv аватар
apv ответил в теме #8870 6 года 2 мес. назад
SQL Plan Management - данным инструментом должен уметь каждый программист и стоящий админ при работе с СУБД Oracle