Oracle mechanics

14.12.2014

SPM подходы к изменению плана запроса с hardcoded подсказками

Filed under: Oracle,SQL Tuning — Игорь Усольцев @ 20:18
Tags: ,

Вопрос: как максимально корректно изменить план выполнения уже хинтованного запроса? Корректно, в смысле не меняя текста запроса (само собой) и, желательно, не меняя параметров сессии, т.е. используя, например, безконтактный SQL Plan Management (SPM)?

Простейшая тестовая схема:

11.2.0.3@SCOTT SQL> create table emp123
  2  as
  3  select * from emp
  4  /

Table created.

SQL> exec dbms_stats.gather_table_stats('','EMP123')

PL/SQL procedure successfully completed.

SQL> create index emp123_sal_idx on emp123(sal)
  2  /

Index created.

Простой запрос с использованием FTS:

SQL> select/*+ full(emp123)*/ * from emp123 where sal > 1000;

12 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 459354512

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |    13 |   494 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP123 |    13 |   494 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   1 - filter("SAL">1000)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
...
          0  sorts (memory)
          0  sorts (disk)
         12  rows processed

потребляет всего 3 consistent gets, но стоит 3 у.е., а тот же же запрос с индексным доступом (выбираемый Oracle по умолчанию) имеет стоимость 2:

SQL> select/*+ index(emp123 emp123_sal_idx)*/ * from emp123 where sal > 1000;

12 rows selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 3720127443

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

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

   2 - access("SAL">1000)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets

, читая при этом 4 блока, что может быть важно и, к примеру, требуется принудить последний хинтованный запрос выполняться по более выгодному с какой-либо точки зрения, но формально более дорогому по мнению CBO FTS-пути

В качестве варианта решения можно попробовать сконструировать SQL Patch, отключающий встроенный хинт и стимулирующий FTS:

SQL> select sql_id from v$sql where plan_hash_value = 3720127443 and sql_text like 'select%where sal > 1000%';

SQL_ID
-------------
60dqb8a0mtfbp

SQL> begin
  2    for reco in (select sql_fulltext from v$sqlarea where sql_id = '60dqb8a0mtfbp')
  3      loop
  4      sys.dbms_sqldiag_internal.i_create_patch(
  5        sql_text => reco.sql_fulltext,
  6        hint_text => 'OPT_PARAM(''_optimizer_ignore_hints'' ''true'') FULL(emp123)',
  7        name => 'TEST_IGNORE_HINTS');
  8      end loop;
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL> select/*+ index(emp123 emp123_sal_idx)*/ * from emp123 where sal > 1000;
...
12 rows selected.

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor('','','typical +outline last'));

Plan hash value: 3720127443

----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP123         |    13 |   494 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMP123_SAL_IDX |    13 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "EMP123"@"SEL$1" ("EMP123"."SAL"))
      END_OUTLINE_DATA
  */

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

   2 - access("SAL">1000)

Note
-----
   - SQL patch "TEST_IGNORE_HINTS" used for this statement

— несмотря на формальное свидетельство применения SQL patch, план остается прежним индексным, и, судя по секции Outline Data изменения параметра _OPTIMIZER_IGNORE_HINTS =TRUE также не происходит

Из чего можно видеть, что замечание Note — SQL patch «***» used for this statement носит скорее формальный характер, свидетельствуя лишь о существовании и попытке применения патча

В свежем документе поддержки How to Create a SQL Patch to add Hints to Application SQL Statements (Doc ID 1931944.1) можно найти важное замечание о требуемом формате подсказок в SQL patch:

However, for the hint to work in a SQL patch the hint needs to be specified with the relevant query block information i.e a /*+ FULL(A) */ hint might need to be supplied as : ‘FULL(@»SEL$1″ «A»@»SEL$1»)’

Ок, переделываю патч в соответствии с этой вводной:

SQL> exec dbms_sqldiag.drop_sql_patch('TEST_IGNORE_HINTS')

PL/SQL procedure successfully completed.

SQL> begin
  2    for reco in (select sql_fulltext from v$sqlarea where sql_id = '60dqb8a0mtfbp')
  3      loop
  4      sys.dbms_sqldiag_internal.i_create_patch(
  5        sql_text => reco.sql_fulltext,
  6        hint_text => 'OPT_PARAM(''_optimizer_ignore_hints'' ''true'') FULL(@"SEL$1" "EMP123"@"SEL$1")', -- полный формат хинта FULL
  7        name => 'TEST_IGNORE_HINTS');
  8      end loop;
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL> select/*+ index(emp123 emp123_sal_idx)*/ * from emp123 where sal > 1000;

12 rows selected.

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 3720127443

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

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

   2 - access("SAL">1000)

Note
-----
   - SQL patch "TEST_IGNORE_HINTS" used for this statement

— вновь не сработало, и есть идея почему:

Общее правило хинтования: ошибочная подсказка блокирует применение всех последующих, и поскольку изменение параметра _OPTIMIZER_IGNORE_HINTS на уровне запроса через OPT_PARAM принципиально невозможно, первая подсказка рассматривается как некорректная, и т.д. и т.п.

Проверяю, просто меняя хинты местами:

SQL> exec dbms_sqldiag.drop_sql_patch('TEST_IGNORE_HINTS')

PL/SQL procedure successfully completed.

SQL> begin
  2    for reco in (select sql_fulltext from v$sqlarea where sql_id = '60dqb8a0mtfbp')
  3      loop
  4      sys.dbms_sqldiag_internal.i_create_patch(
  5        sql_text => reco.sql_fulltext,
  6        hint_text => 'FULL(@"SEL$1" "EMP123"@"SEL$1") OPT_PARAM(''_optimizer_ignore_hints'' ''true'')',
  7        name => 'TEST_IGNORE_HINTS');
  8      end loop;
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL> select/*+ index(emp123 emp123_sal_idx)*/ * from emp123 where sal > 1000;

12 rows selected.

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 459354512

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |    13 |   494 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP123 |    13 |   494 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   1 - filter("SAL">1000)

Note
-----
   - SQL patch "TEST_IGNORE_HINTS" used for this statement -- теперь действительно used

— получаем ожидаемый результат

Содержимое патча из SYS.SQLOBJ$DATA (obj_type = 3), полученное скриптом sqlpatch_hints.sql, в точности отражает то, что было заложено при создании:

SQL> @sqlpatch_hints TEST_IGNORE_HINTS

SQL_PATCH_HINTS
---------------------------------------------------------------------------
FULL(@"SEL$1" "EMP123"@"SEL$1") OPT_PARAM('_optimizer_ignore_hints' 'true')

Другим методом изменения плпна через добавления требуемых подсказок к запросу является SQL Profile, гибко управляемый через процедуру DBMS_SQLTUNE.IMPORT_SQL_PROFILE:

SQL> declare
  2    sql_ftext clob;
  3  begin
  4    select sql_fulltext into sql_ftext from v$sqlarea where sql_id = '60dqb8a0mtfbp';
  5    dbms_sqltune.import_sql_profile(sql_text    => sql_ftext,
  6                                    profile     => sqlprof_attr('FULL(@"SEL$1" "EMP123"@"SEL$1")'),
  7                                    name        => 'profile_60dqb8a0mtfbp',
  8                                    replace     => true,
  9                                    force_match => true);
 10  end;
 11  /

PL/SQL procedure successfully completed.

SQL> select/*+ index(emp123 emp123_sal_idx)*/ * from emp123 where sal > 1000;

12 rows selected.

Plan hash value: 459354512

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |       |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| EMP123 |    13 |   494 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "EMP123"@"SEL$1")
      END_OUTLINE_DATA
  */

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

   1 - filter("SAL">1000)

Note
-----
   - SQL profile profile_60dqb8a0mtfbp used for this statement

— SQL profile действительно сработал, но применение профилей обладает теми же недостатками, что и SQL Patch: при создании некоректного профиля с ошибочным набором подсказок, например, OPT_PARAM(‘_optimizer_ignore_hints’ ‘true’) FULL(@»SEL$1″ «EMP123″@»SEL$1»), SQL Profile применён не будет несмотря на оптимистическое, но также формальное сообщение SQL profile *** used for this statement

Содержимое профиля из SYS.SQLOBJ$DATA с obj_type = 1 и может быть получено скриптом sql_profile_hints.sql:

SQL> @sql_profile_hints profile_60dqb8a0mtfbp

OUTLINE_HINTS
-------------------------------
FULL(@"SEL$1" "EMP123"@"SEL$1")

Фиксация планов выполнения через создание SQL profile с помощью DBMS_SQLTUNE.IMPORT_SQL_PROFILE активно рекомендуется — Best Practices for Minimizing Oracle E-Business Suite Release 12.1.3 Upgrade Downtime, при этом разработчики OEBS явно симпатизируют профилям и рекомендуют помещать в профиль не отдельные подсказки, а всю секцию Outline Data «правильного» плана выполнения, которая для последнего запроса с FTS-планом 459354512, например, может быть получена запросом
plan_ol_hints.sql:

SQL> @plan_ol_hints 60dqb8a0mtfbp 459354512

PLAN_HASH_VALUE HINT
--------------- -------------------------------------
      459354512 ALL_ROWS
      459354512 DB_VERSION('11.2.0.3')
      459354512 FULL(@"SEL$1" "EMP123"@"SEL$1")
      459354512 IGNORE_OPTIM_EMBEDDED_HINTS
      459354512 OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      459354512 OUTLINE_LEAF(@"SEL$1")

Третьим элементом SPM (last but not least) является SQL Plan Baseline, который в отличие от предыдущих конструкций создаётся только на основе цельного плана выполнения, каким-либо образом созданного, использованного и т.о. проверенного самим CBO:

SQL> exec dbms_sqltune.drop_sql_profile('profile_60dqb8a0mtfbp') -- предварительно удалив профиль

PL/SQL procedure successfully completed.

SQL> -- проще всего создать Baseline для sql_id='60dqb8a0mtfbp' plan_hash_value=459354512
SQL> -- из FTS-плана запроса, уже находящегося в Shared Pool
SQL>
SQL> @bl_create 60dqb8a0mtfbp 459354512 "Test Baseline"

Baseline SQL_238c751dabd2f87c SQL_PLAN_2733p3qpx5y3wcbec8f58 was [re]created
for SQL_ID=60dqb8a0mtfbp, SQL_PLAN_HASH=459354512

SQL> select/*+ index(emp123 emp123_sal_idx)*/ * from emp123 where sal > 1000;

12 rows selected.

Plan hash value: 459354512

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |       |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| EMP123 |    13 |   494 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "EMP123"@"SEL$1")
      END_OUTLINE_DATA
  */

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

   1 - filter("SAL">1000)

Note
-----
   - SQL plan baseline SQL_PLAN_2733p3qpx5y3wcbec8f58 used for this statement

— в этом случае сообщение used for this statement, как правило, является достоверным. Очевидно именно поэтому (наличию надёжных средств управления и мониторинга) SQL plan baseline является рекомендованным Oracle методом фиксации планов выполнения, однако, baseline-ы капризны, и диагностика причин невоспроизводимости (DBA_SQL_PLAN_BASELINES.REPRODUCED = ‘NO’) конкретного baseline, несмотря на ACCEPTED = ‘YES’ и ENABLED = ‘YES’, может оказаться отдельной нетривиальной задачей

Список подсказок, составляющий этот SPM Baseline ожидаемо совпадает с секцией Outline Data последнего выполнения, и опять же хранится в SQLOBJ$DATA с типом obj_type = 2, и может быть показана запросом bl_hints.sql:

SQL> @bl_hints SQL_PLAN_2733p3qpx5y3wcbec8f58

OUTLINE_HINTS
--------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "EMP123"@"SEL$1")

В итоге может показаться, что системная табличка SQLOBJ$DATA содержит «три источника и три составных части» SPM:

SQL> select distinct obj_type from SQLOBJ$DATA;
 
  OBJ_TYPE
----------
         1 -- SQL Profile
         2 -- SQL Plan Baseline
         3 -- SQL Patch

, хотя SQL Profile формально к SPM и не относится

Приведённые тесты воспроизводимы также на версии 12.1, замечания и предложения, как обычно, приветствуются)

3 комментария »

  1. Reblogged this on SoulUran.

    комментарий от souluran — 14.12.2014 @ 22:26 | Ответить

  2. Кстати, ключевой хинт для игнорирования уже встроенных в запрос хинтов — IGNORE_OPTIM_EMBEDDED_HINTS. Ну и, до кучи (вдруг пригодится): http://www.fors.ru/upload/magazine/05/http_texts/russia_ruoug_deev_sql_plans.html

    комментарий от ilya.deev — 18.12.2014 @ 10:41 | Ответить

    • Спасибо, Илья, отлично подходящий хинт! Забавно, что он всё время перед глазами)
      И статья твоя — точно в тему

      комментарий от Игорь Усольцев — 20.12.2014 @ 00:03 | Ответить


RSS feed for comments on this post. TrackBack URI

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

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

Логотип WordPress.com

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

Фотография Twitter

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

Фотография Facebook

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

Google+ photo

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

Connecting to %s

Создайте бесплатный сайт или блог на WordPress.com.

%d такие блоггеры, как: