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

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

Сертификационные экзамены Microsoft (Глава I).

Компания Oracle запустила собственный "облачный" сервис.

Oracle начинает продажи Oracle Big Data Appliance.

Новый SQL Server 2012.

Использование хэш-ключей в базах данных.

Главная » Sql