(495) 925-0049, ITShop интернет-магазин 229-0436, Учебный Центр 925-0049
  Главная страница Карта сайта Контакты
Поиск
Вход
Регистрация
Рассылки сайта
 
 
 
 
 

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

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

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

Содержание

Аннотация

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

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

Ссылки по теме


 Распечатать »
 Правила публикации »
  Написать редактору 
 Рекомендовать » Дата публикации: 14.05.2006 
 

Магазин программного обеспечения   WWW.ITSHOP.RU
Oracle Database Standard Edition 2 Named User Plus License
Oracle Database Standard Edition 2 Processor License
Oracle Database Personal Edition Named User Plus Software Update License & Support
Oracle Database Personal Edition Named User Plus License
ARCHICAD 21, локальная лицензия на 12 месяцев
 
Другие предложения...
 
Курсы обучения   WWW.ITSHOP.RU
 
Другие предложения...
 
Магазин сертификационных экзаменов   WWW.ITSHOP.RU
 
Другие предложения...
 
3D Принтеры | 3D Печать   WWW.ITSHOP.RU
 
Другие предложения...
 
Новости по теме
 
Рассылки Subscribe.ru
Информационные технологии: CASE, RAD, ERP, OLAP
Новости ITShop.ru - ПО, книги, документация, курсы обучения
Программирование на Microsoft Access
CASE-технологии
Компьютерный дизайн - Все графические редакторы
СУБД Oracle "с нуля"
Мир OLAP и Business Intelligence: новости, статьи, обзоры
 
Статьи по теме
 
Новинки каталога Download
 
Исходники
 
Документация
 
 



    
rambler's top100 Rambler's Top100