Четыре способа корректировки планов запросов по образцу без изменения кода.

Илья Деев,
"Innova Systems",
член RuOUG

 

 

Источник: Russian Oracle User Group with the library and forum options,
http://www.ruoug.org/events/20121206/index.html
[От редакции FORS Magazine: дополнением к данной статье является одноименная презентация И.Деева, представленная им на семинаре Российской группы пользователей Oracle 06.12.2012.]

[От редакции FORS Magazine: в процессе обсуждения этой статьи уже после её опубликования обнаружилась небольшая неточность: в скрипте 1.1 выдача привилегий была корректна только для Oracle Database 11g и не применима в версии 10g. В настоящем тексте эта неточность исправлена. К сожалению, исправить также оперативно эту неточность в pgf-форматах этого выпуска журнала не представляется возможным, поэтому в следующем 6-м номере журнала будет опубликовано соответствующее письмо в редакцию нашего автора Ильи Деева.]

Введение

В процессе эксплуатации баз данных иногда возникает необходимость скорректировать планы запросов, не прибегая к изменению исходного кода. Такие средства влияния на план запроса как корректировка статистики, изменение параметров сессии, построение SQL профиля не всегда доступны, не всегда гарантированно ведут к нужному результату, а также могут иметь нежелательные побочные эффекты. Корректировка статистики объектов базы данных и установка параметров в сессии могут повлиять не только на проблемные запросы, но и на другие. Построение SQL профилей возможно только в Oracle Enterprise Edition при использовании платных Diagnostic &Tuning Pack. Однако, даже обладая этими инструментами, к сожалению, не всегда удается получить удовлетворительный результат. В данной заметке описываются способы решения проблемы с помощью метода настройки плана запроса по образцу.

После анализа плана проблемного запроса его можно скорректировать с помощью хинтов. Для проверки результата изменений можно выполнить настроенный вариант запроса и по значениям статистики убедиться в том, что при изменении плана запроса проблема будет действительно решена. Особенно важным бывает применение правильного набора хинтов   в тех случаях, когда изменение плана критичного запроса нужно выполнить срочно, не дожидаясь исправления кода приложений. Также при этом важна возможность быстрой отмены действия примененных хинтов.

Небольшое отступление по поводу хинтов. С одной стороны, набор хинтов – это результат работы оптимизатора. Начиная с Oracle 10.2 список хинтов запроса доступен через вывод плана запроса с помощью пакета DBMS_XPLAN с заданием формата вывода ‘outline’:

select * from table(dbms_xplan.display_cursor(<sql_id>,<child_number>,'outline'));

С другой стороны, хинты могут использоваться как входная управляющая информация непосредственно в тексте запроса либо, например, в функционале Stored Outlines. Использование хинтов в пользовательском коде может быть обосновано в том случае, когда прежде всего важна стабильность планов выполнения. Возможные недостатки при таком подходе – риск применения неполного списка хинтов, что может привести к изменению планов в другой среде, а также искусственное ограничение свободы действий оптимизатора, потенциально ведущее к невозможности использования других, быть может, более оптимальных планов. В целом, хинты в тексте запроса – это своеобразная смирительная рубашка для оптимизатора, которая иногда бывает необходима. Иногда возникает обратная задача, когда требуется отменить действие ненужных хинтов в тексте некоторых запросов.

Получить полный набор хинтов, реализующий необходимый план, довольно просто – для этого достаточно иметь правильно работающую версию запроса либо правильную версию плана запроса в AWR (см. DBA_HIST_SQL_PLAN). Затем необходимо каким-то образом применить эти хинты к исходному проблемному запросу. Далее будет рассмотрено четыре способа, с помощью которых можно это осуществить.

Тестовые данные

Скрипт 0. Создание тестовых данных .

create user test identified by test;
grant connect, resource to test;

create table test.drop_tbl as select rownum n, 'txt'||rownum txt from dual connect by level <=10000;
create index test.i_drop_tbl_id on test.drop_tbl(n);
 
begin
  dbms_stats.gather_table_stats(ownname => 'test', tabname => 'drop_tbl');
end;

Отдельный пользователь, от имени которого будет происходить настройка, создается для того, чтобы показать, какие права нужны для каждого способа настройки.

В приведенных ниже скриптах индексный доступ будет меняться на полное сканирование таблицы. Этого искусственного примера будет вполне достаточно для демонстрации возможностей каждого метода. Приведенные скрипты выполнялись в среде PL/SQL Developer 9.0.6, где SQL_ID запроса отличается от SQL_ID того же запроса в SQL*Plus за счет автоматически добавляемого пробела в конце текста запроса. При выполнении приведенных ниже скриптов в среде SQL*Plus или Oracle SQL Developer нужно изменить значения SQL_ID тестовых запросов. В частности, значение SQL_ID '4aay3kxc7rddg' нужно поменять на '8acvts5s612z9', а значение 'g2u2fv1npc6q5' - на '4uxxdz9wrjhnx'.

Для каждого способа настройки приводится также скрипт отказа от настроенного плана. Описание принципов работы применяемых в каждом случае механизмов выходит за рамки этой заметки. И, разумеется, все эксперименты желательно проводить в тестовой среде.

Способ №1. Использование функционала SQL Plan Management. Создание SQL plan baseline.

Способ, который задействует функционал SQL Plan Management, может быть применен в Oracle 11 Enterprise Edition. План настроенного запроса-образца с помощью процедуры dbms_spm.load_plan_from_cursor_cache загружается в качестве плана для настраиваемого запроса, текст которого явно указывается в соответствующем параметре процедуры. При этом создается SQL plan baseline.

В примере, представленном ниже, подразумевается, что параметр optimizer_capture_sql_plan_baselines имеет значение false (в этом случае SQL baseline для каждого запроса автоматически не создается). Необходимы следующие привилегии:

Скрипт 1.1 Привилегии для выполнения скрипта с использованием SQL Baselines:

grant select on v_$sql to test;
grant select on v_$sqlarea to test;

grant administer sql management object to test; -- 11g

grant create any sql profile to test; -- 10g
grant drop any sql profile to test;
grant alter any sql profile to test;

grant select on dba_sql_plan_baselines to test;

Скрипт 1.2. Пример настройки с использованием SQL plan baseline:

-- conn test
-- запрос для настройки (с индексным доступом)
select * from drop_tbl where n = 1; -- sql_id = '4aay3kxc7rddg' ('8acvts5s612z9' для SQL Developer)
-- запрос-образец
select /*+ full(drop_tbl) */ * from drop_tbl where n = 1; -- sql_id = 'g2u2fv1npc6q5' ('4uxxdz9wrjhnx')

-- Определяем параметры настраиваемого запроса и запроса-образца
select sql_text, sql_id, plan_hash_value 
  from v$sql 
 where sql_text like 'select%drop_tbl%' 
 order by last_active_time;

-- загрузка плана настроенного запроса
declare
  l_sql_id_src varchar2(13)    :='g2u2fv1npc6q5';   -- sql_id образца
  l_plan_hash_value_src number := 1871027057;       -- plan_hash_value образца
  l_sql_id_trg  varchar2(13)   :='4aay3kxc7rddg';   -- sql_id настраиваемого запроса
  l_sql_text_trg clob;  
  l_res number;  
begin
  -- текст запроса для настройки
  select a.sql_fulltext into l_sql_text_trg
    from v$sqlarea a 
   where a.sql_id = l_sql_id_trg;
  -- загрузка плана и создание SQL plan baseline
  l_res := dbms_spm.load_plans_from_cursor_cache( 
              sql_id => l_sql_id_src, 
              plan_hash_value => l_plan_hash_value_src, 
              sql_text => l_sql_text_trg);
  dbms_output.put_line(l_res);  
end;  


-- проверка плана
explain plan for select * from drop_tbl where n = 1;
select * from table(dbms_xplan.display(null,null,'basic +note')); -- см. значение SQL plan baseline

PLAN_TABLE_OUTPUT
Plan hash value: 2545387769
 
-------------------------------------
| Id  | Operation         | Name    |
-------------------------------------
|   0 | SELECT STATEMENT  |         |
|   1 |  TABLE ACCESS FULL| drop_tbl|
-------------------------------------
 
Note
-----
   - SQL plan baseline "SQL_PLAN_3dm7hzprspdufe13b857f" used for this statement


-- Проверка применения baseline к запросу (применяется со второго выполнения)
select * from drop_tbl where n = 1;

select sql_text, sql_id, plan_hash_value, sql_plan_baseline
  from v$sql 
 where sql_text like 'select%drop_tbl%' 
 order by last_active_time desc;


-- Удаление baseline
declare
  l_sql_plan_baseline varchar2(30) := 'SQL_PLAN_3dm7hzprspdufe13b857f';
  l_sql_handle varchar2(30);
  l_res number;
begin
  select sql_handle 
    into l_sql_handle
    from dba_sql_plan_baselines b
   where plan_name = l_sql_plan_baseline;
  l_res := dbms_spm.drop_sql_plan_baseline(sql_handle => l_sql_handle);
  dbms_output.put_line(l_res);
end;

В разделе Note отчета dbms_xplan.display указывается используемый SQL plan baseline.

Название SQL plan baseline для запроса отражается также в поле V$SQL.SQL_PLAN_BASELINE.

В Oracle 11 SE скрипты выполнятся, однако никакого эффекта на план запроса не окажут.

Cледует иметь в виду, что для такого типа запросов, как multitable insert, невозможно создать SQL plan baseline, см., например: http://jonathanlewis.wordpress.com/2011/01/12/fake-baselines/#comment-49382

Способ №2. Создание SQL патчей.

Еще один способ изменения планов запросов без правки исходного кода в Oracle 11g – использование функционала SQL Repair Advisor, а именно – создание SQL патчей, которые предназначены для изменения планов запросов в случае непредвиденных проблем. Принцип использования в нашем случае такой же как и ранее – получение набора хинтов настроенного запроса и создание на их основе SQL патча для проблемного запроса. Для создания SQL патча необходимы права на вызов пакета sys.dbms_sqldiag_internal.

Скрипт 2.1. Привилегии для использования SQL патчей

grant execute on dbms_sqldiag_internal to test;
grant execute on dbms_sqldiag to test;
grant select on v_$sql_plan to test;

Скрипт 2.2 показывает, как можно автоматизировать создание SQL патча:

-- conn test
declare
  l_sql_id_trg char(13) := '4aay3kxc7rddg'; -- запрос для настройки  (v$sql.sql_id)
  l_sql_id_src char(13) := 'g2u2fv1npc6q5'; -- настроенный запрос - источник хинтов: v$sql.sql_id,
  l_child_number_src number :=0;            -- v$sql.child_number  
  l_hints varchar2(32767);
  l_sql_text clob;
begin
  
  -- удаление SQL патча, если такой есть 
  dbms_sqldiag.drop_sql_patch(name => 'patch_'||l_sql_id_trg, ignore => true);
  
  -- список хинтов настроенного запроса в одной строке
  select listagg( hint_val,' ') within group (order by rn) 
    into l_hints
    from (select extractValue(value(d), '/hint') hint_val, rownum rn
            from (select other_xml
                    from v$sql_plan
                   where sql_id = l_sql_id_src
                     and child_number = l_child_number_src
                     and id = 1) add_data,
                table(XMLSequence(XMLType(add_data.other_xml).extract('other_xml/outline_data/hint'))) d);
  
  -- текст настраиваемого запроса
  select sql_fulltext into l_sql_text from v$sql where sql_id = l_sql_id_trg and rownum = 1;
  
  -- создание SQL патча        
  sys.dbms_sqldiag_internal.i_create_patch(
      sql_text  => l_sql_text,
      hint_text => l_hints,
      name      => 'patch_'||l_sql_id_trg);
  dbms_output.put_line('patch_'||l_sql_id_trg||' was created');    
end;

Для просмотра результата выполним следующие команды:

-- Просмотр плана запроса с патчем
explain plan for select * from drop_tbl where n = 1; 
select * from table(dbms_xplan.display(format=>'+outline'));

Plan hash value: 1871027057
 
------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |     1 |    12 |     9   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| DROP_TBL |     1 |    12 |     9   (0)| 00:00:01 |
------------------------------------------------------------------------------
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$1" "DROP_TBL"@"SEL$1")
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('11.2.0.2')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("N"=1)
 
Note
-----
   - SQL patch "patch_4aay3kxc7rddg" used for this statement
Название SQL патча для запроса отражается в поле V$SQL.SQL_PATCH.

Для удаления SQL патча можно выполнить скрипт:
begin
  dbms_sqldiag.drop_sql_patch('patch_4aay3kxc7rddg');
end;

Следует заметить, что используемые в SQL патче хинты должны быть именно в системном виде. При попытке использовать в SQL патче хинт в виде /*+ full(drop_tbl) */ патч будет создан, но план запроса не изменится.

SQL Repair Advisor является бесплатной опцией Oracle EE. Однако, функционалом создания SQL патчей удается воспользоваться и в других редакциях Oracle 11g, включая XE.

Способ №3. Настройка с использованием SQL profiles.

Возможность настройки запросов при помощи построения SQL profiles обеспечивается установленными Tuning& Diagnostic Pack (требуется версия Oracle 10 EE или 11 EE). С точки зрения рассматриваемой задачи интересным в использовании SQL profiles является тот факт, что профили можно создавать вручную через импорт. Недокументированная процедура dbms_sqltune.import_sql_profile обычно используется при импорте профиля, созданного ранее на основе списка специальных хинтов, предназначенных для корректировки статистики. Однако, этот же механизм может помочь включить в рассмотрение оптимизатором списка хинтов из плана настроенного запроса-образца. Также этот прием можно использовать при необходимости отмены действия хинтов в тексте проблемного запроса. Для этого достаточно использовать при создании профиля единственный хинт - IGNORE_OPTIM_EMBEDDED_HINTS.

Скрипт 3.1. Привилегии для использования профилей.

grant administer sql management object to test; 
grant select on v_$sql to test;
grant select on v_$sql_plan to test;

Скрипт 3.2. Пример использования настройки по образцу с помощью SQL profiles

-- исходный и настроенный запросы
select * from drop_tbl where n = 1; -- sql_id: '4aay3kxc7rddg'
select /*+ full(drop_tbl) */ * from drop_tbl where n = 1; -- sql_id: 'g2u2fv1npc6q5'

-- определение параметров запросов
select sql_text, sql_id, child_number from v$sql where sql_text like 'select%drop_tbl%';

-- загрузка хинтов через профиль
declare
  -- SQL для настройки  (v$sql.sql_id)
  l_sql_id_trg char(13) := '4aay3kxc7rddg';
  -- Настроенный SQL – источник хинтов (v$sql.sql_id и v$sql.child_number)
  l_sql_id_src char(13) := 'g2u2fv1npc6q5'; 
  l_child_number_src number :=0;  
  l_hints sys.sqlprof_attr;
  l_sql_text      clob;  
begin

  -- новые хинты в настроенном вручную варианте запроса
  select extractValue(value(d), '/hint') bulk collect into l_hints
  from (select other_xml
          from v$sql_plan
         where sql_id = l_sql_id_src
           and child_number = l_child_number_src
           and id = 1/*other_xml is not null*/) add_data,
       table(XMLSequence(XMLType(add_data.other_xml).extract('other_xml/outline_data/hint'))) d;

  -- исходный проблемный запрос
  select sql_fulltext into l_sql_text from v$sql
   where sql_id = l_sql_id_trg and rownum = 1;
  
  -- удаляем профиль, если такой был
  dbms_sqltune.drop_sql_profile('PROF_'||l_sql_id_trg,ignore => TRUE);

  -- создаем профиль на основе хинтов настроенного варианта
  dbms_sqltune.import_sql_profile(sql_text => l_sql_text
                                 ,profile  => l_hints
                                 ,category => 'DEFAULT'
                                 ,name     => 'PROF_'||l_sql_id_trg
                                 ,force_match => true);
end;

-- Убедимся в использовании SQL профиля
explain plan for select * from drop_tbl where n = 1;
select * from table(dbms_xplan.display(null,null,'basic +note'));

Plan hash value: 2545387769
 
-------------------------------------
| Id  | Operation         | Name     |
-------------------------------------
|   0 | SELECT STATEMENT  |          |
|   1 |  TABLE ACCESS FULL| DROP_tbl |
-------------------------------------
 
Note
-----
   - SQL profile "PROF_4aay3kxc7rddg" used for this statement

-- Удаление профиля
begin
  dbms_sqltune.drop_sql_profile(name => 'PROF_4aay3kxc7rddg',ignore => true);
end;

В разделе Note отчета указано имя используемого SQL profile. В примере используется категория профилей DEFAULT. При необходимости, можно изменить ее наименование. Категория профилей по умолчанию задается параметром sqltune_category:

alter session set sqltune_category = 'TEST';
alter system set sqltune_category = 'PROD' scope = both;

Название SQL профиля для запроса отражается также в поле V$SQL.SQL_PROFILE.

Иногда бывает также необходимо отменить действие хинтов, входящих в текст запроса.

Скрипт 3.3 Отмена действия хинтов

-- Запрос с хинтом, действие которого нужно отменить
select /*+ full(drop_tbl) */ * from drop_tbl where n = 1; -- SQL_ID = 'g2u2fv1npc6q5'

-- создание профиля с отменой хинтов
declare
  -- SQL для настройки  (v$sql.sql_id)
  l_sql_id_trg char(13) := 'g2u2fv1npc6q5';
  l_hints sys.sqlprof_attr := sys.sqlprof_attr('IGNORE_OPTIM_EMBEDDED_HINTS');
  l_sql_text      clob;  
begin
  -- проблемный запрос
  select sql_fulltext into l_sql_text from v$sql where sql_id = l_sql_id_trg and rownum = 1;  
  -- удаляем профиль, если такой был
  dbms_sqltune.drop_sql_profile('PROF_'||l_sql_id_trg,ignore => TRUE);
  -- создаем профиль на основе хинтов настроенного варианта
  dbms_sqltune.import_sql_profile(sql_text => l_sql_text
                                 ,profile  => l_hints
                                 ,category => 'DEFAULT'
                                 ,name     => 'PROF_'||l_sql_id_trg
                                 ,force_match => true);
end;

-- результат
explain plan for select /*+ full(drop_tbl) */ * from drop_tbl where n = 1;
select * from table(dbms_xplan.display(null,null,'basic +note'));

Plan hash value: 3053117682
 
-----------------------------------------------------
| Id  | Operation                   | Name          |
-----------------------------------------------------
|   0 | SELECT STATEMENT            |               |
|   1 |  TABLE ACCESS BY INDEX ROWID| DROP_TBL      |
|   2 |   INDEX RANGE SCAN          | I_DROP_TBL_ID |
-----------------------------------------------------
 
Note
-----
   - SQL profile "PROF_g2u2fv1npc6q5" used for this statement

-- удаление профиля
begin
  dbms_sqltune.drop_sql_profile(name => 'PROF_g2u2fv1npc6q5',ignore => true);
end;

Способ №4. Редактирование private outline и создание на его основе public outline.

Наконец, четвертый способ настройки основан на функционале Stored Outlines и заключается в подмене хинтов private outline проблемного запроса хинтами настроенного запроса и последующего создания public outline. Особенно актуален такой вариант для версии Standard Edition, где Plan Stability является доступной опцией. Этот способ настройки работает во всех редакциях версий 9,10,11.

Итак, допустим, нужно оптимизировать выполнение некоего запроса с помощью outline. План действий следующий:

  1. Оптимизация плана проблемного запроса с помощью хинтов
  2. Создание private outline для скорректированной версии запроса
  3. Создание private outline для исходного запроса, план которого нужно изменить
  4. Замена хинтов private outline настраиваемого запроса хинтами private outline настроенного запроса
  5. Обновление (refresh) скорректированного private outline для исходного запроса.
  6. Создание public outline с нужной категорией на основе измененного private outline для исходного запроса
  7. Выключение использования private outline
  8. Включение использования public outline с заданием категории outline
  9. Проверка работы запроса с public outline

Следует заметить, что замена содержимого OL$HINTS (временная таблица, отличающаяся от одноименной таблицы схемы OUTLN) в данном примере осуществляется без редактирования заголовка шаблона в OL$ ( в частности, не меняется число хинтов).

Скрипт 4.1. Предоставление привилегий для работы с outlines.

grant create any outline to test;
grant drop any outline to test;

Скрипт 4.2 пошагово реализует изложенный выше план.

-- удаление одноименных аутлайнов (при необходимости)
-- drop private outline p_outline_src_1;
-- drop private outline p_outline_trg_2;
-- drop public outline outline_drop_tbl;

-- настраиваемый запрос
select * from drop_tbl where n = 1;

-- корректируем запрос хинтами, добиваясь нужного плана. 
select /*+ full(drop_tbl) */ * from drop_tbl where n = 1;
 
-- создание айтлайна для скорректированного хинтами запроса
create or replace private outline p_outline_trg_2 on
select /*+ full(drop_tbl) */ * from drop_tbl where n = 1;

-- создание аутлайна для исходного запроса
create or replace private outline p_outline_src_1 on
  select * from drop_tbl where n = 1;
 
-- проверим использование созданного аутлайна
alter session set use_private_outlines = true;
explain plan for select * from drop_tbl where n = 1;
select * from table(dbms_xplan.display(null,null,'basic +note')); 
-- в конце будет замечание об использовании аутлайна

-- наши запросы
select sql_id, sql_text from v$sql where sql_text like 'select%drop_tbl%';
 
-- просмотр содержимого аутлайнов
select * from ol$;
select * from ol$hints; -- where ol_name = 'P_OUTLINE_SRC_1';
select * from ol$nodes;
 
-- удаление исходных хинтов в первом запросе и прописывание новых, взятых из второго:
delete from ol$hints where ol_name = 'P_OUTLINE_SRC_1';
delete from ol$nodes where ol_name = 'P_OUTLINE_SRC_1';
update ol$hints h set ol_name = 'P_OUTLINE_SRC_1' where ol_name = 'P_OUTLINE_TRG_2';
update ol$nodes h set ol_name = 'P_OUTLINE_SRC_1' where ol_name = 'P_OUTLINE_TRG_2';
 
-- удаляем второй аутлайн, который больше не нужен (необязательно)
drop private outline p_outline_trg_2;

-- просмотр содержимого аутлайнов после редактирования 
select * from ol$;
select * from ol$hints;
select * from ol$nodes;
 
-- обновление скорректированного аутлайна
begin
  dbms_outln_edit.refresh_private_outline('P_OUTLINE_SRC_1');
end;
 
-- создание public outline на основе private outline
create or replace public outline outline_drop_tbl from private p_outline_src_1 for category prod;
 
-- выключение использования private аутлайнов в базе
alter session set use_private_outlines = false;
 
-- включаем использование public outline, категория prod
alter session set use_stored_outlines = prod;
 
-- В плане запроса должен быть FULL SCAN при включенном outline
explain plan for select * from drop_tbl where n = 1;
select * from table(dbms_xplan.display(null,null,'basic +note'));

Plan hash value: 1871027057
 
--------------------------------------
| Id  | Operation         | Name     |
--------------------------------------
|   0 | SELECT STATEMENT  |          |
|   1 |  TABLE ACCESS FULL| DROP_TBL |
--------------------------------------
 
Note
-----
   - outline "OUTLINE_DROP_TBL" used for this statement
 
-- план запроса с отключенными аутлайнами (используется индекс)
alter session set use_stored_outlines = false;
explain plan for select * from drop_tbl where n = 1;
select * from table(dbms_xplan.display(null,null,'basic +note'));

Plan hash value: 3053117682 
-----------------------------------------------------
| Id  | Operation                   | Name          |
-----------------------------------------------------
|   0 | SELECT STATEMENT            |               |
|   1 |  TABLE ACCESS BY INDEX ROWID| DROP_TBL      |
|   2 |   INDEX RANGE SCAN          | I_DROP_TBL_ID |
-----------------------------------------------------

Для использования категории PROD на уровне всего инстанса необходимо выполнить:

alter system set use_stored_outlines = prod;

Таблицы, к которым ведут синонимы OL$, OL$HINTS, OL$NODES в Oracle 10 и Oracle 11 являются временными таблицами, и поскольку их содержимое очищается, необходимо выполнить весь скрипт в одной сессии.

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

drop public outline outline_drop_tbl;

Скрипт 4.3 объединяет шаги предыдущего скрипта в один PL/SQL скрипт:

declare
  i_sql_id_trg varchar2(13)     := '4aay3kxc7rddg'; -- проблемный sql_id
  i_sql_id_src varchar2(13)     := 'g2u2fv1npc6q5'; -- настроенный sql_id
  i_prod_category varchar2(30)  := 'PROD';          -- категория outline
  l_sql_fulltext_trg clob;
  l_sql_fulltext_src clob;
  l_result number;
  function exec_sql(i_sql in clob, i_show_err in number := 0) return number is
    l_sql varchar2(32767) := substr(i_sql, 1, 32767); 
  begin
    execute immediate l_sql; -- varchar2 for 10g, clob for 11g
    return 0;
  exception
    when others
      then if i_show_err = 0 then null;
           else dbms_output.put_line(sqlerrm);
                dbms_output.put_line(i_sql);
           end if;
           return -1;
  end;
begin
  -- очистка outlines
  l_result := exec_sql('drop private outline pr_ol_'||i_sql_id_trg);
  l_result := exec_sql('drop private outline pr_ol_'||i_sql_id_src);
  l_result := exec_sql('drop outline ol_'||i_sql_id_trg);
  -- определение текстов SQL запросов
  select sql_fulltext into l_sql_fulltext_trg from v$sql
   where sql_id = i_sql_id_trg and rownum = 1;
  select sql_fulltext into l_sql_fulltext_src from v$sql
   where sql_id = i_sql_id_src and rownum = 1;
  -- создание outlines
  l_result := exec_sql('create or replace private outline pr_ol_'||i_sql_id_trg
    ||' on '|| l_sql_fulltext_trg,1);
  l_result := exec_sql('create or replace private outline pr_ol_'||i_sql_id_src
    ||' on '|| l_sql_fulltext_src,1);
  -- смена хинтов outlines
  delete from ol$hints where ol_name = 'PR_OL_'||upper(i_sql_id_trg);
  delete from ol$nodes where ol_name = 'PR_OL_'||upper(i_sql_id_src);
  update ol$hints h set ol_name = 'PR_OL_'||upper(i_sql_id_trg) 
   where ol_name = 'PR_OL_'||upper(i_sql_id_src);
  update ol$nodes h set ol_name = 'PR_OL_'||upper(i_sql_id_trg) 
   where ol_name = 'PR_OL_'||upper(i_sql_id_src);
  -- обновление данных outline
  dbms_outln_edit.refresh_private_outline('PR_OL_'||upper(i_sql_id_trg));
  -- создание public outline на основе private outline
  l_result := exec_sql('create or replace public outline ol_'||upper(i_sql_id_trg)||' from private
        PR_OL_'||upper(i_sql_id_trg)||' for category '||i_prod_category,1);
  if l_result = 0 then
    dbms_output.put_line('Outline OL_'||upper(i_sql_id_trg)||' created for sql_id='||i_sql_id_trg);
  end if;
end;

При проверке работы настроенного запроса нужно не забывать устанавливать категорию для outline, если она не установлена на уровне инстанса.

Большим достоинством этого метода является то, что он подходит для разных версий и редакций Oracle – 9, 10, 11, EE, SE, XE. Недостатком этого способа, помимо большей сложности скрипта, является то, что в 11g функционал Stored Outlines официально объявлен устаревшим. В документации outlines настоятельно рекомендуется заменить на SQL plan baselines.

Выводы

Как было показано, в разных редакциях разных версий Oracle всегда найдется способ исправить план неправильно выполняющегося запроса без изменения кода, для этого имеются достаточно широкие возможности. В перспективе хотелось бы видеть, чтобы функционал SQL Plan Baselines работал не только в Enterprise Edition. Объявление функционала Stored Outlines устаревшим логически требует его замены на функционал SQL Plan Management во всех редакциях Oracle. Это было бы самым удобным вариантом для пользователей.

Таблица 1. Рассмотренные способы настройки и версии Oraсle.

Используемый функционал Редакции 9i Редакции 10g Редакции 11g Замечания
SQL Plan Baseline - - EE Простота, стандартный функционал
SQL patch - - EE, SE, XE Использование недокументированного внутреннего пакета
SQL Profiles - EE + Tuning & Diagnostic Packs EE + Tuning & Diagnostic Packs Использование недокументированных возможностей
Stored Outlines EE, SE EE, SE EE, SE, XE Универсальность. При этом - формально устаревший функционал.

Хочется надеяться, что данная заметка поможет сэкономить силы, время и нервы тем, кто столкнется с необходимостью правки планов запросов без изменения кода и поможет это сделать наиболее подходящим способом в каждой конкретной ситуации.

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

  1. Oracle® Database Performance Tuning Guide. 15 Using SQL Plan Management: docs.oracle.com/cd/E11882_01/server.112/e16638/optplanmgmt.htm
  2. Editing Stored Outlines in Oracle10g and Oracle11g Oracle Metalink, ID 726802.1
  3. Заметка из блога команды разработки оптимизатора о SQL патчах: https://blogs.oracle.com/optimizer/entry/how_can_i_hint_a
  4. Интересное исследование функционала SQL патчей: http://orastory.wordpress.com/2012/03/06/sql-patch-i/ (см. также следующие части статьи).
  5. Блог Kerry Osborne, сообщения, посвященные стабилизации планов запросов: http://kerryosborne.oracle-guy.com/category/oracle/plan-stability/
  6. Скрипт настройки c помощью SQL Profile через образец плана в AWR: http://www.sql.ru/forum/actualthread.aspx?tid=983050&pg=2&mid=13466846#13466846

Спасибо Тимуру Ахмадееву за интересные замечания и ссылки по данной тематике.