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;

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

  1. create table t_plan_test
  2. (
  3. n1 number not null,
  4. n2 number not null
  5. );
  6. create index idx_t_plan_test_1 on t_plan_test(n1);
  7. create index idx_t_plan_test_2 on t_plan_test(n2, n1);
  8. insert into t_plan_test
  9. select rownum, rownum
  10. from dual
  11. connect by rownum <=#cc66cc;">1000;
  12. commit;
  13.  

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

  1. select t.* from t_plan_test t where t.n1 = #cc66cc;">100;
  2.  

100 100

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

  1. explain plan #b1b100;">for select t.* from t_plan_test t where t.n1 = #cc66cc;">100;
  2. 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 из вывода предыдущего запроса и подставим его в следующий запрос:

  1. select s.sql_text, s.sql_id, s.plan_hash_value
  2. from v$sql s where s.plan_hash_value = <strong>#cc66cc;">2822388801</strong>;

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 и выполним его:

  1. select /*+INDEX(t idx_t_plan_test_2)*/ t.* from t_plan_test t where t.n1 = #cc66cc;">100;
  2.  

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

  1. explain plan #b1b100;">for select /*+INDEX(t idx_t_plan_test_2)*/t.* from t_plan_test t where t.n1 = #cc66cc;">100;
  2. 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 нового запроса.

  1. select s.sql_text, s.sql_id, s.plan_hash_value
  2. from v$sql s where s.plan_hash_value = <strong>#cc66cc;">3105628069</strong>;

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 43x83pbfc3x5n3105628069

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

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

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

  1. begin
  2. dbms_output.put_line(dbms_spm.load_plans_from_cursor_cache(sql_id => '<strong>2w8k1yhuk8k1v</strong>', plan_hash_value => <strong>#cc66cc;">2822388801</strong>));
  3.  

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

  1. 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

  1. begin
  2. dbms_output.put_line(dbms_spm.alter_sql_plan_baseline(sql_handle => '<strong>SQL_cbead429bd574d05</strong>',attribute_name => 'enabled',attribute_value => 'NO' ));
  3.  

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

  1. begin
  2. dbms_output.put_line(dbms_spm.load_plans_from_cursor_cache(sql_id => '<strong>43x83pbfc3x5n</strong>', plan_hash_value => <strong>#cc66cc;">3105628069</strong>, fixed => 'YES', sql_handle => '<strong>SQL_cbead429bd574d05</strong>'));
  3.  

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

  1. explain plan #b1b100;">for select t.* from t_plan_test t where t.n1 = #cc66cc;">100;
  2. 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

Источники
http://innerlife.io/oracle-db-dbms_spm-fix-plan/ http://www.fors.ru/upload/magazine/05/http_texts/russia_ruoug_deev_sql_plans.html
12:40
6.64K
Нет комментариев. Ваш будет первым!
Используя этот сайт, вы соглашаетесь с тем, что мы используем файлы cookie.