рассматривает некоторые дополнительные возможности пакета DBMS_SQLTUNE по выполнению углубленного анализа отдельных запросов и групп запросов.

Наткнулся на статью 2006 года
может будет интересно

Версия Oracle 10g: специалисты по настройке запросов больше не нужны

Владимир Пржиялковский,
Преподаватель технологий Oracle,
www.ccas.ru/prz/

	Моя поэзия здесь больше не нужна, Да и, пожалуй, сам я тоже здесь не нужен. С. Есенин. Русь советская.
	Печь затопит, все заготовит, закупит, Яичко испечет да сам и облупит. Попадья Балдой не нахвалится ... А. С. Пушкин. Сказка о попе и о работнике его Балде.

Аннотация

В версии 10 Oracle появился специальный режим работы оптимизатора затрат (cost optimizer), позволяющий провести углубленный анализ запроса и предложить для последующего выполнения более качественный, чем обычно, план. В статье рассматривается пример программного выполнения углубленного анализа запросов с помощью нового в версии 10 пакета DBMS_SQLTUNE.

Введение

В версии 10 Oracle появился специальный режим работы оптимизатора затрат (cost optimizer), позволяющий провести углубленный анализ запроса и предложить для последующего выполнения более качественный, чем обычно, план. Применить такой анализ можно с помощью пакета DBMS_SQLTUNE, который позволяет получить от СУБД рекомендации по настройке конкретных запросов и даже получить сценарий внесения изменений. Рекомендации строятся на основе дополнительного, сверх обычного, изучения данных, проверочных прогонов частей плана и накопленных специалистами по настройке запросов знаний.Пакетом можно пользоваться как вручную (программно), так и через графические средства OEM. Далее рассматривается пример программного выполнения углубленного анализа запросов.

Углубленный анализ запросов и его возможности

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

• Анализ статистики запроса. Оптимизатор выяснит, имеется ли у объектов запроса статистика и насколько она устарела, и выдаст конкретные рекомендации по сбору статистики. На случай, если рекомендации будут проигнорированы, оптимизатор выработает корректировки к обычному плану.
• Анализ способа доступа к данным. Оптимизатор выясняет, насколько целесообразно для выполнения запроса создать дополнительный индекс и предложит вариант создания индекса.
• Анализ структуры. Оптимизатор выяснит, возможно ли для ускорения ответа переформулировать запрос и предупредит о возможном при этом искажении семантики (в общем случае).
• Анализ адекватности существующего плана. Оптимизатор сверится со статистикой предшествующих исполнений запроса и соберет дополнительную информацию, способную устранить собственные ошибки при обычной подготовке плана. Такая информация называетсяпрофилем запроса, она сохраняется в БД и может применяться в дальнейших обработках, корректируя автоматически основной план в лучшую сторону.

Углубленный анализ запроса требует времени, в том числе за счет проверочных прогонов частей плана для получения более точных оценок затрат. Анализ без построения профиля требует меньше времени и называется ограниченным (limited). Анализ с построением профиля требует больше времени и называетсяполным (comprehensive).
В отличие от статистик объектов запроса, профиль является свойством конкретного запроса и способен учитывать соотношения данных, проявляющие себя именно в конкретном запросе. Применение профиля способно дать более качественныый план, не требуя переформулировки запроса, что позволяет повышать эффективность обработки запросов в готовых приложениях.

Настройка отдельных запросов

Рассмотрим пример настройки с помощью пакета DBMS_SQLTUNE конкретного запроса.Откроем в SQL*Plus сеанс связи с СУБД от имени SCOTT и сделаем необходимые приготовления:

CONNECT scott/tiger

SET AUTOTRACE TRACEONLY EXPLAIN

SELECT ename, loc, sal, hiredate 
FROM emp, dept 
WHERE emp.deptno = dept.deptno
;

Ответ на запрос может выглядеть примерно так:

Execution Plan
----------------------------------------------------------
Plan hash value: 615168685

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    14 |   532 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN         |      |    14 |   532 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |     4 |    84 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP  |    14 |   238 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

Note
-----
   - dynamic sampling used for this statement

Переключимся на другой сеанс от имени SYS, например так:

HOST sqlplus / AS SYSDBA

Создадим задание на автоматическую настройку запроса со стороны пользователя SCOTT (на углубленный анализ):

DECLARE
my_task_name VARCHAR2 ( 30 );
my_sqltext   CLOB;

BEGIN
my_sqltext :=
   'SELECT ename, loc, sal, hiredate FROM emp, dept '
|| 'WHERE emp.deptno = dept.deptno'
;

my_task_name :=
DBMS_SQLTUNE.CREATE_TUNING_TASK (
  sql_text    => my_sqltext
, user_name   => 'SCOTT'
, task_name   => 'my_sql_tuning_task'
);
END;
/

Узнать состояние задания можно из словаря-справочника:

SELECT status, execution_start start_time, execution_end end_time 
FROM dba_advisor_log 
WHERE owner = 'SYS' AND task_name = 'my_sql_tuning_task'
;

Оно будет 'INITIAL'.
Запустим задание для настройки запроса:

EXECUTE -
 DBMS_SQLTUNE.EXECUTE_TUNING_TASK ( 'my_sql_tuning_task' );

Повторный запрос к DBA_ADVISOR_LOG даст STATUS = 'COMPLETED'. (Для серьезных запросов задание могло бы выполняться намного дольше).
Задание выполнило углубленный анализ с применением контрольных прогонов фрагментов плана и подготовило рекомендации. Запросим их:

SET LONG 10000
SET LONGCHUNKSIZE 1000
SET LINESIZE 200

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

Получим примерно такой развернутый результат.
Оптимизатор сделал два наблюдения: (а) отсутствует статистика по одной из таблиц (и предложил готовую команду сбора статистики) и (б) можно получить более выгодный план, применив профиль (и указал для сравнения старый план и план с применением профиля).
Ту же информацию можно извлечь из словаря-справочника, например:

SELECT type, message 
FROM dba_advisor_findings 
WHERE owner = 'SYS' AND task_name = 'my_sql_tuning_task'
;

Применим созданый профиль к запросу, правда чуть в иной форме, нежели чем рекомендует оптимизатор, но по сути так же:

BEGIN
DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
  task_name => 'my_sql_tuning_task'
, name      => 'my_sql_profile'
);
END;
/

Уточнить свойства (и наличие) созданного профиля запроса можно по словарю-справочнику так:

SELECT category, type, status 
FROM dba_sql_profiles 
WHERE name = 'my_sql_profile'
;

Вернемся в исходный сеанс, снова выдадим запрос и понаблюдаем план:

EXIT
/

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

Execution Plan
----------------------------------------------------------
Plan hash value: 351108634

--------------------------------------------------------------------------------------
|Id | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT             |         |    14 |   364 |     4   (0)| 00:00:01 |
| 1 |  NESTED LOOPS                |         |    14 |   364 |     4   (0)| 00:00:01 |
| 2 |   TABLE ACCESS FULL          | EMP     |    14 |   238 |     3   (0)| 00:00:01 |
| 3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |     9 |     1   (0)| 00:00:01 |
|*4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

Note
-----
   - SQL profile "my_sql_profile" used for this statement

Заметим, что от текста запроса не требуется полного посимвольного совпадения. Выдадим:

select ename, LOC,SAL, hiredate
from emp,   dept    WHERE emp.deptno = dept.deptno
;

Получим снова:

Execution Plan
----------------------------------------------------------
Plan hash value: 351108634

--------------------------------------------------------------------------------------
|Id | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT             |         |    14 |   364 |     4   (0)| 00:00:01 |
| 1 |  NESTED LOOPS                |         |    14 |   364 |     4   (0)| 00:00:01 |
| 2 |   TABLE ACCESS FULL          | EMP     |    14 |   238 |     3   (0)| 00:00:01 |
| 3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |     9 |     1   (0)| 00:00:01 |
|*4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

Note
-----
   - SQL profile "my_sql_profile" used for this statement

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

select ename, LOC,SAL, hiredate
from emp, scott.dept    WHERE emp.deptno = dept.deptno
;

Хотя профиль и приписан запросу, отдельный сеанс может отказаться от его использования. Профиль был создан для "категории" (поле CATEGORY.DBA_SQL_PROFILES) DEFAULT и будет применен только при условии, что запрос выдается с установленым в DEFAULT значением параметра СУБД SQLTUNE_CATEGORY. Это значение умолчательное. Заменим его на другое:

ALTER SESSION SET SQLTUNE_CATEGORY = test;

SELECT ename, loc, sal, hiredate 
FROM emp, dept 
WHERE emp.deptno = dept.deptno;

План для этого (и только !) сеанса снова станет прежним:

 
Execution Plan
----------------------------------------------------------
Plan hash value: 615168685

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    14 |   532 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN         |      |    14 |   532 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |     4 |    84 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP  |    14 |   238 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

Note
-----
   - dynamic sampling used for this statement

Хотя профиль и имеется, но в этом запросе не учитывается.

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

Вместо представления в повествовательной форме (с помощью функции 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 лет назад. Для этого потребуется самая малость: сделать ручное выполнение углубленного анализа запроса автоматическим и основным!