Автоматизация настройки запросов в версии Oracle 10g: некоторые дополнительные возможности

Владимир Пржиялковский

"Уж мы его - и этак и раз-этак, -
Буржуя энтого... которого... в Крыму..."
И клены морщатся ушами длинных веток,
И бабы охают в немую полутьму.
С. Есенин. Русь советская.

Уж я к ней и так, и этак,
Со словами и без слов!
Обломал немало веток,
Наломал немало дров!
М. Танич. Страдание.

Содержание

Аннотация

Статья является продолжением статьи "Версия Oracle 10g: специалисты по настройке запросов больше не нужны" и рассматривает некоторые дополнительные возможности пакета DBMS_SQLTUNE по выполнению углубленного анализа отдельных запросов и групп запросов.

Получение рекомендаций в виде готового сценария

Вместо представления в повествовательной форме (с помощью функции REPORT_TUNUNG_TASK) рекомендации можно получить в виде готового сценария для SQL*Plus:

SELECT
 DBMS_SQLTUNE.SCRIPT_TUNING_TASK ( 'my_sql_tuning_task' ) 
FROM dual;

Получим примерно такой результат:

DBMS_SQLTUNE.SCRIPT_TUNING_TASK('MY_SQL_TUNING_TASK')
-------------------------------------------------------------------------------------
-----------------------------------------------------------------
-- Script generated by DBMS_SQLTUNE package, advisor framework --
-- Use this script to implement some of the recommendations --
-- made by the SQL tuning advisor. --
-- --
-- NOTE: this script may need to be edited for your system --
-- (index names, privileges, etc) before it is executed. --
-----------------------------------------------------------------
execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname =>
'DEPT', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
execute dbms_sqltune.accept_sql_profile(task_name => 'my_sql_tuning_task', replace => TRUE);

(Здесь приведены две команды EXECUTE, слишком длинные, чтобы каждой поместиться в одной строке экрана).

Возможна и более ограниченная выдача, например:

SELECT 
 DBMS_SQLTUNE.SCRIPT_TUNING_TASK
 ( 'my_sql_tuning_task', 'STATISTICS, INDEXES' ) 
FROM dual
;

Настройка запроса по ссылке в рабочей области SQL в SGA

Углубленный анализ запроса можно выполнить, сославшись на его идентификатор в рабочей области SQL в SGA, на SQL_ID (V$SQLAREA). Например, в нашем случае можно было бы создать задание так:

DECLARE
my_task_name VARCHAR2 ( 30 );

BEGIN
my_task_name := 
DBMS_SQLTUNE.CREATE_TUNING_TASK (
 sql_id => '3dcfttkf1kwmn'
, task_name => 'a_very_hard_sql_task'
);
END;
/

Групповая настройка запросов

Средствами DBMS_SQLTUNE можно провести углубленный анализ (с построением, если возможно, профиля) сразу для групп запросов - например, поступающих из заданного приложения, или выбранных из рабочей области SQL в SGA СУБД. Ниже приводится пример второго.

Построим набор запросов, поступавших от пользователя SCOTT:

EXECUTE DBMS_SQLTUNE.CREATE_SQLSET ( 'my_workload' )

DECLARE
cur DBMS_SQLTUNE.SQLSET_CURSOR;

BEGIN
OPEN cur FOR
 SELECT VALUE ( P )
 FROM TABLE (
 DBMS_SQLTUNE.SELECT_CURSOR_CACHE (
 basic_filter => 'parsing_schema_name = ''SCOTT'''
 , attribute_list => 'ALL'
 )
 ) P
;
 
DBMS_SQLTUNE.LOAD_SQLSET (
 sqlset_name => 'my_workload'
, populate_cursor => cur
);
END;
/

Табличная функция SELECT_CURSOR_CACHE возвращает вложенную таблицу объектов типа DBMS_SQLTUNE.SQLSET_ROW, каждый из которых содержит сведения о запросах, отобранных из рабочей области SQL в SGA. Загрузка "набора запросов" выполняется процедурой LOAD_SQLSET через ссылку на курсор, сформированный после преобразования вложенной таблицы в список объектов функцией TABLE. Фильтр для отбора строк в набор запросов из области SQL строится как условное выражение по полям таблицы V$SQLAREA и может быть гораздо более сложным. Тип SQLSET_CURSOR есть просто тип нестрогой ссылки на курсор, то же, что SYS_REFCURSOR, однако зачем-то определен самостоятельно в пакете DBMS_SQLTUNE и здесь употреблен по инерции.

Построим задание для углубленного анализа созданного набора запросов:

VARIABLE ttask VARCHAR2 ( 100 )

BEGIN
:ttask := DBMS_SQLTUNE.CREATE_TUNING_TASK (
 sqlset_name => 'my_workload'
, task_name => 'my_sqlset_task'
);
END;
/

Выполним анализ:

EXECUTE DBMS_SQLTUNE.EXECUTE_TUNING_TASK ( :ttask )

Средствами пакета можно формировать наборы запросов для анализа и иначе, например, по данным репозитария рабочей нагрузки (Automatic Workload Repository).

Заключение

Вроде - гляну - все в порядке,
А выходит ерунда!
М. Танич. Страдание.

В светлом прошлом отцы-основатели реляционного подхода к моделированию баз данных ("моделирование с помощью отношений") полагали, что в реляционной системе для разработчика приложения нет понятия "настройка запроса". Считалось, что разработчик будет формулировать запросы так, как ему удобнее их читать, а оптимизацией выполнения запроса займется СУБД.

Равно как и все прочие разработчики СУБД, использовавшие прилагательное "реляционная" для своих систем, фирма Oracle назвалась груздем, а в кузов полезать не торопилась. Настройкой запросов в Oracle заниматься приходилось с самого начала этой СУБД. Первый оптимизатор запросов (часть СУБД, отвечающая за выработку плана обработки запроса), rule-based, был прост, скор и... неадекватен. Частые плохие планы требовали ручной работы по анализу переформулировке. Пока нагрузки на БД были невелики, это можно было терпеть, но со временем потребовалось разработать новый вариант оптимизатора - cost-based. Он решил многие проблемы оптимизатора доступа, но ручной работы вряд ли убавил, породив целый класс специалистов по "подсказкам" оптимизатору. Тем не менее, появившись в последних выпусках версии 7, он все-таки совершенствовался от версии к версии.

Наконец, третий существенный шаг по отработке долгосрочного кредита, получаемого в течение многих лет от покупателей своей системы, фирма Oracle сделала как раз в версии 10. Углубленный анализ действительно позволяет делать много нового, например обнаруживать декартовы произведения, поступающие от приложений (проверьте !), за что ему уже можно ставить памятник. Однако не надо забывать, что он, подобно сбору статистики для объектов запроса (таблиц, ...) осуществляется вручную и требует своевременного (когда?...) повторения.

Надо надеяться, что в следующих версиях фирма еще больше приблизится к тому, что замышлялось создателями реляционного подхода 30 лет назад. Для этого потребуется самая малость: сделать ручное выполнение углубленного анализа запроса автоматическим и основным!