Ещё один взгляд на Oracle SQL Plan Management

Моя старая статья в журнале Форс http://www.fors.ru/upload/magazine/06/http_texts/blog_borchuk_2012_example_SPM.html 🙂

Профили — новое (относительно) слово в БД. Профили нагрузки на файловую систему, профили работы сессии, профили SQL-запросов, … Наконец, Baseline или Oracle SQL Plan Management — новое средство работы с планами выполнения запросов в процессе жизни приложения, позволяющее управлять выбором плана выполнения и его эволюциями.

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

  1. Документация http://docs.oracle.com/cd/E11882_01/server.112/e23633/preup.htm#UPGRD00232
  2. Maria Kolgan
    https://blogs.oracle.com/optimizer/entry/sql_plan_management_part_1_of_4_creating_sql_plan_baselines
  3. Johathan Lewis http://jonathanlewis.wordpress.com/2011/01/12/fake-baselines/
  4. NoCOUG 2012, №8 SQL Plan Management for Performance Testing
  5. Tim Hall http://www.oracle-base.com/articles/11g/sql-plan-management-11gr1.php
  6. Coskan Gundogar http://coskan.wordpress.com/2012/04/11/when-dbms_xplan-display_sql_plan_baseline-fails-to-show-the-plan

Ниже предпринимается ещё одна попытка рассказать о принципах работы SPM на одном простом примере.

Чем принципиально SPM отличается от Outline? SPM не хранит ни плана выполнения, ни каких-либо хинтов оптимизатору, которые нужно будет вставить в запрос. В действительности это и не нужно: среда выполнения может измениться, и хинты либо перестанут работать, либо будут работать не так, как ожидалось.

SPM хранит код плана выполнения, который мы хотели бы получить. Т.е. по сути является целевой функцией. Т.е. теперь помимо first_rows, all_rows есть возможность указать код плана выполнения, который мы хотели бы получить.
Работает это так. Предположим, у нас есть запрос (1):

SELECT EMP.ENAME
FROM SCOTT.EMP
WHERE EMP.DEPTNO <> ALL
  (SELECT DEPTNO FROM SCOTT.DEPT WHERE LOC='DALLAS')

С hash anti-join в плане выполнения:

SQL_ID  6gcraaf1apfws, child number 0

Plan hash value: 1543991079

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     7 (100)|          |
|*  1 |  HASH JOIN ANTI    |      |     5 |   100 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   126 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| DEPT |     3 |    33 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

И мы хотели бы получить nested loop в плане выполнения этого запроса (это просто пример).
Берём исходный запрос и прописываем туда нужные хинты (2):

SELECT /*+ USE_NL(DEPT@Q1 EMP@SEL$1) */EMP.ENAME
FROM SCOTT.EMP
WHERE EMP.DEPTNO <> ALL
(SELECT /*+ QB_NAME(Q1) */ DEPTNO FROM SCOTT.DEPT WHERE LOC='DALLAS')

Получаем план выполнения

SQL_ID  000q6z5d9z48h, child number 0
Plan hash value: 2649839948

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |       |       |    17 (100)|          |
|   1 |  NESTED LOOPS ANTI           |         |     5 |   100 |    17   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL          | EMP     |    14 |   126 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     2 |    22 |     1   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)|          |
----------------------------------------------------------------------------------------

Для одного и того же самого запроса мы можем легко найти в документации советы по созданию baseline. Но что, если мы хотим получить такой-же план выполнения для другого запроса (1)? А влиять на код приложения и вставить хинт напрямую в текст запроса нельзя (нет времени и т.д.).

С помощью SPM это делается достаточно легко — для текста запроса (1) мы указываем в качестве цели план выполнения запроса (2):

declare
i number;
n clob;
begin
select sql_text into n from v$sql where sql_id = '6gcraaf1apfws' and child_number = 0;
i:= DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => '000q6z5d9z48h', 
           plan_hash_value =>2649839948, sql_text => n , fixed => 'YES');
end;

Иногда этого достаточно, но можете убедиться сами: для запроса (1) такой SPM не работает.

Разобраться, в чём дело, помогает трассировка 10053. Но прежде, чем лезть в файлы отладки, давайте посмотрим на предполагаемый план выполнения запроса:

SELECT *
FROM TABLE(DBMS_XPLAN.display_sql_plan_baseline(plan_name=>'SQL_PLAN_d4jm9zdn0kydz9b8c4c13'));

--------------------------------------------------------------------------------
SQL handle: SQL_d24669fb680979bf
SQL text: SELECT EMP.ENAME FROM SCOTT.EMP WHERE EMP.DEPTNO <> ALL (SELECT DEPTNO
          FROM SCOTT.DEPT WHERE LOC='DALLAS')
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_d4jm9zdn0kydz9b8c4c13         Plan id: 2609662995
Enabled: YES     Fixed: YES     Accepted: YES     Origin: MANUAL-LOAD
--------------------------------------------------------------------------------

Plan hash value: 2809975276

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     9 |    81 |     6   (0)| 00:00:01 |
|*  1 |  FILTER                      |         |       |       |            |          |
|   2 |   TABLE ACCESS FULL          | EMP     |    14 |   126 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    11 |     1   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Видим, что план отличается от того, на который мы рассчитывали — вместо операции Nested loop в нём Filter. Это не ошибка. Смотрим секцию SPM в трассировке 10053:

SPM: planId in plan baseline = 2609662995, planId of reproduced plan = 1763180032
------- START SPM Plan Dump -------
SPM: failed to reproduce the plan using the following info:
  parse_schema name        : SYS
  plan_baseline signature  : 15151914524837771711
  plan_baseline plan_id    : 2609662995
  plan_baseline hintset    :
    hint num  1 len 27 text: IGNORE_OPTIM_EMBEDDED_HINTS
    hint num  2 len 37 text: OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
    hint num  3 len 22 text: DB_VERSION('11.2.0.3')
    hint num  4 len  8 text: ALL_ROWS
    hint num  5 len 29 text: OUTLINE_LEAF(@"SEL$AF79D2C9")
    hint num  6 len 13 text: UNNEST(@"Q1")
    hint num  7 len 17 text: OUTLINE(@"SEL$1")
    hint num  8 len 14 text: OUTLINE(@"Q1")
    hint num  9 len 35 text: FULL(@"SEL$AF79D2C9" "EMP"@"SEL$1")
    hint num 10 len 59 text: INDEX_RS_ASC(@"SEL$AF79D2C9" "DEPT"@"Q1" ("DEPT"."DEPTNO"))
    hint num 11 len 50 text: LEADING(@"SEL$AF79D2C9" "EMP"@"SEL$1" "DEPT"@"Q1")
    hint num 12 len 35 text: USE_NL(@"SEL$AF79D2C9" "DEPT"@"Q1")
SPM: generated non-matching plan:
----- Explain Plan Dump -----
----- Plan Table -----

Что произошло? Оптимизатор запросов на этапе выбора плана выполнения определил, что есть SPM для данного sql_id. На основе подсказок из профиля SPM после этапа перечисления попытался получить план с заданным номером и не смог этого сделать. То же самое мы видели, когда пытались вывести план выполнения SPM с помощью пакета DBMS_XPLAN. В этот момент уже невозможно сказать, каким должен был быть план выполнения (известен только его идентификатор). Достоверно известно, что для заданного sql_id в текущем окружении его получить невозможно. Так что стандартно выбирается план выполнения с минимальной стоимостью.

Причины подобного поведения с запросом (1) — эвристики оптимизатора. Часть операций в определённых условиях никогда не рассматриваются. С помощью хинтов нам удалось в запросе (2) заставить оптимизатор выбрать одну из таких операций, но при попытке сказать оптимизатору для запроса (1): «Используй план с номером n», мы потерпели неудачу — план с номером n в нормальной ситуации вообще не рассматривается.

Другие причины, почему может не использоваться SPM:

  1. Несовпадение sql_id. При любом изменении текста запроса меняется его sql_id. Такое бывает, например, если не использовать bind переменные, изменить порядок сортировки или добавить столбцов в секцию select.
  2. Изменился список объектов. Например, был изменён или удалён один из индексов.
  3. Изменился тип объектов. Например, столбец стал not null или одна из bind переменных изменила свой тип.
  4. Изменились параметры оптимизатора. Например, отключили преобразование group-by placement.

От статистики и данных в таблицах SPM не зависит (хорошие новости) 🙂

Постскриптум

Вопрос 1. Что же за хинты в таком случае хранятся в виде XML в профиле? Хинты оптимизатору, с помощью которых он пытается получить целевой план выполнения. Эти хинты не влияют на запрос и нужны лишь для ускорения поиска. Действительно, не выполнять же дорогостоящую фазу оптимизации каждый раз заново.

Вопрос 2. А что же в таком случае показывает display_sql_plan_baseline? План выполнения, который удалось получить с помощью этих хинтов. Бывает, как в примере выше, что эти хинты действуют совсем не так, как задумывалось. Хорошо, что в этом случае SPM не оказывает влияния на план выполнения.

Реклама

Добавить комментарий

Заполните поля или щелкните по значку, чтобы оставить свой комментарий:

Логотип WordPress.com

Для комментария используется ваша учётная запись WordPress.com. Выход /  Изменить )

Google photo

Для комментария используется ваша учётная запись Google. Выход /  Изменить )

Фотография Twitter

Для комментария используется ваша учётная запись Twitter. Выход /  Изменить )

Фотография Facebook

Для комментария используется ваша учётная запись Facebook. Выход /  Изменить )

Connecting to %s