Oracle DB. Изменение и фиксация плана выполнения запроса при помощи dbms_spm.

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


Для того чтобы следовать инструкциям в статье и выполнять их не из под пользователя SYS, выдайте необходимые привилегии вашему пользователю:
grant select on V_$SESSION to test;
grant select on V_$SQL_PLAN to test;
grant select on V_$SQL_PLAN_STATISTICS_ALL to test;
grant select on V_$SQL to test;


Итак, у нас есть тестовая таблица с набором данных и двумя индексами:

create table t_plan_test
(
n1 number not null,
n2 number not null
);

create index idx_t_plan_test_1 on t_plan_test(n1);
create index idx_t_plan_test_2 on t_plan_test(n2, n1);

insert into t_plan_test
select rownum, rownum
from dual
connect by rownum <=1000;

commit;

Выполним запрос к этой таблице:

select t.* from t_plan_test t where t.n1 = 100;
100	100

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

explain plan for select t.* from t_plan_test t where t.n1 = 100;
select * from table(dbms_xplan.display);
Plan hash value: 2822388801

-------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                   |     1 |    26 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_PLAN_TEST       |     1 |    26 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_T_PLAN_TEST_1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("T"."N1"=100)

Note
-----
- dynamic sampling used for this statement (level=2)

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

Для начала узнаем sql_id запроса, который мы хотим поменять. Для этого возьмем значение Plan hash value из вывода предыдущего запроса и подставим его в следующий запрос:

select s.sql_text, s.sql_id, s.plan_hash_value
from v$sql s where s.plan_hash_value = 2822388801;
select t.* from t_plan_test t where t.n1 = 100 	2w8k1yhuk8k1v	2822388801
explain plan for select t.* from t_plan_test t where t.n1 = 100 	2rcswv78fcp13	2822388801

Обратите внимание на то, что нас интересует именно sql_id оригинального запроса, а не запроса explain plan для него.

Запомнив sql_id и plan_hash_value из запроса приступим к его оптимизации. Для начала добавим хинт на использование индекса idx_t_plan_test_2 и выполним его:

select /*+INDEX(t idx_t_plan_test_2)*/ t.* from t_plan_test t where t.n1 = 100;

После этого проверим что запрос использовал именно этот индекс:

explain plan for select /*+INDEX(t idx_t_plan_test_2)*/t.* from t_plan_test t where t.n1 = 100;
select * from table(dbms_xplan.display);
Plan hash value: 3105628069

--------------------------------------------------------------------------------------
| Id  | Operation        | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                   |     1 |    26 |     5   (0)| 00:00:01 |
|*  1 |  INDEX FULL SCAN | IDX_T_PLAN_TEST_2 |     1 |    26 |     5   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("T"."N1"=100)
filter("T"."N1"=100)

Note
-----
- dynamic sampling used for this statement (level=2)

Всё верно, используется индекс idx_t_plan_test_2.
В очередной раз взяв значение Plan hash value определим sql_id нового запроса.

select s.sql_text, s.sql_id, s.plan_hash_value
from v$sql s where s.plan_hash_value = 3105628069;
explain plan for select /*+INDEX(t idx_t_plan_test_2)*/t.* from t_plan_test t where t.n1 = 100 	4jnycbw4j9cav	3105628069
select /*+INDEX(t idx_t_plan_test_2)*/ t.* from t_plan_test t where t.n1 = 100 	43x83pbfc3x5n	3105628069

Отлично. Теперь у нас есть все необходимые параметры для изменения и фиксации плана запроса.


Следующие инструкции выполняются из под пользователя SYS. Выдайте необходимые привилегии вашему пользователю, если хотите выполнять их из под него.


Во-первых, необходимо запомнить план первого запроса, взяв его sql_id и plan_hash_value:

begin
  dbms_output.put_line(dbms_spm.load_plans_from_cursor_cache(sql_id => '2w8k1yhuk8k1v', plan_hash_value => 2822388801)); 
end;

Во-вторых, необходимо определить sql_handle нашего запроса и отключить этот план:

select sql_handle, to_char(sql_text), plan_name from DBA_SQL_PLAN_BASELINES;
SQL_cbead429bd574d05	"select t.* from t_plan_test t where t.n1 = 100"	SQL_PLAN_cruqn56ypfm8591102725
begin
  dbms_output.put_line(dbms_spm.alter_sql_plan_baseline(sql_handle => 'SQL_cbead429bd574d05',attribute_name => 'enabled',attribute_value => 'NO' ));
end;

В-третьих, необходимо зафиксировать план второго запроса, указав в качестве sql_handle значение предыдущего плана и взяв sql_id и plan_hash_value второго запроса:

begin
  dbms_output.put_line(dbms_spm.load_plans_from_cursor_cache(sql_id => '43x83pbfc3x5n', plan_hash_value => 3105628069, fixed => 'YES', sql_handle => 'SQL_cbead429bd574d05')); 
end;

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

explain plan for select t.* from t_plan_test t where t.n1 = 100;
select * from table(dbms_xplan.display);
Plan hash value: 3105628069

--------------------------------------------------------------------------------------
| Id  | Operation        | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                   |     4 |   104 |     0   (0)| 00:00:01 |
|*  1 |  INDEX FULL SCAN | IDX_T_PLAN_TEST_2 |     4 |   104 |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("T"."N1"=100)
filter("T"."N1"=100)

Note
-----
- SQL plan baseline "SQL_PLAN_cruqn56ypfm8500b9313e" used for this statement

1 комментарий к “Oracle DB. Изменение и фиксация плана выполнения запроса при помощи dbms_spm.”

Оставьте комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *