Моя поэзия здесь больше не нужна,
Да и, пожалуй, сам я тоже здесь не нужен.
С. Есенин. Русь советская.
Печь затопит, все заготовит, закупит,
Яичко испечет да сам и облупит.
Попадья Балдой не нахвалится ...
А. С. Пушкин. Сказка о попе и о работнике его Балде.
Аннотация
Введение
В версии 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
Хотя профиль и имеется, но в этом запросе не учитывается.
