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

© Владимир Пржиялковский,
координатор Евро-Азиатской Группы Пользователей Oracle,
преподаватель УКЦ Interface Ltd.

Моя поэзия здесь больше не нужна,
Да и, пожалуй, сам я тоже здесь не нужен.
С. Есенин. Русь советская.

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

Содержание

• Аннотация
• Введение
• Углубленный анализ запросов и его возможности
• Настройка отдельных запросов

Аннотация

В версии 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;

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

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK')
------------------------------------------------------------------------
GENERAL INFORMATION SECTION
------------------------------------------------------------------------
Tuning Task Name                  : my_sql_tuning_task
Tuning Task Owner                 : SYS
Scope                             : COMPREHENSIVE
Time Limit(seconds)               : 60
Completion Status                 : COMPLETED
Started at                        : 03/14/2006 20:57:05
Completed at                      : 03/14/2006 20:57:05
Number of Statistic Findings      : 1
Number of SQL Profile Findings    : 1

-------------------------------------------------------------------------
Schema Name: SCOTT
SQL ID     : 3dcfttkf1kwmn
SQL Text   : SELECT ename, loc, sal, hiredate FROM emp, dept
             WHERE emp.deptno = dept.deptno

-------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------

1- Statistics Finding
---------------------
  Table "SCOTT"."DEPT" and its indices were not analyzed.

  Recommendation
  --------------
  - Consider collecting optimizer statistics for this table and its indices.
    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);

  Rationale
  ---------
    The optimizer requires up-to-date statistics for the table and its indices
    in order to select a good execution plan.

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK')
-------------------------------------------------------------------------

2- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
  A potentially better execution plan was found for this statement.

  Recommendation (estimated benefit: 38.11%)
  ------------------------------------------
  - Consider accepting the recommended SQL profile.
    execute dbms_sqltune.accept_sql_profile(task_name =>
            'my_sql_tuning_task', replace => TRUE);

------------------------------------------------------------------------
EXPLAIN PLANS SECTION
------------------------------------------------------------------------

1- Original With Adjusted Cost
------------------------------
Plan hash value: 615168685

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    14 |   364 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN         |      |    14 |   364 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |     4 |    36 |     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")

2- Using SQL Profile
--------------------
Plan hash value: 351108634


DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK')
------------------------------------------------------------------------
--------------------------------------------------------------------------------------
|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")

------------------------------------------------------------------------

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

Ту же информацию можно извлечь из словаря-справочника, например:

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

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

• Другие статьи по продуктам Oracle из рубрики "Мастерская Oracle"
• Вы можете отправить свое мнение о материале его автору
• Oracle Database 10g: интеграция и способность информации к соединению
• Линейка продуктов Oracle 10g
• Обратиться в Interface Ltd. за дополнительной информацией/по вопросу приобретения продуктов
• Приобретение продуктов Oracle в электронном магазине
• Курсы по продуктам фирмы Oracle

За дополнительной информацией обращайтесь в компанию Interface Ltd.

Обсудить на форуме Oracle

INTERFACE Ltd. Телефон/Факс: +7 (495) 925-0049 Отправить E-Mail http://www.interface.ru

Ваши замечания и предложения отправляйте редактору По техническим вопросам обращайтесь к вебмастеру Дата публикации: 14.05.06