Oracle mechanics

22.02.2015

Особенности SQL Patch

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

Лишь частично документированный, но достаточно удобный для простого манипулирования планами выполнения SQL Patch, как и всякий инструмент имеет свои ограничения/особенности

Так, например, длина добавляемой к запросу подсказки имеет точное ограничение:

12.1.0.2.@ SQL> select /*SQL Patch Test*/ dummy from dual; -- для версии 11.2 результат тот же

D
-
X

1 row selected.

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
SQL_ID  2kj0tst4rj96f, child number 0
-------------------------------------
select /*SQL Patch Test*/ dummy from dual

Plan hash value: 272002086
...
SQL> begin
  2    for reco in (select sql_fulltext
  3                   from v$sqlarea
  4                  where sql_id = '2kj0tst4rj96f') loop
  5      sys.dbms_sqldiag_internal.i_create_patch(sql_text  => reco.sql_fulltext,
  6                                               hint_text => rpad('x', 501, 'x'),
  7                                               name      => 'SQL_PATCH_TEST');
  8    end loop;
  9  end;
 10  /
begin
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SYS.DBMS_SQLDIAG_INTERNAL", line 245
ORA-06512: at line 5 -- 501 символ, очевидно too large

— длина подсказки ограничена 500 символами:

SQL> begin
  2    for reco in (select sql_fulltext
  3                   from v$sqlarea
  4                  where sql_id = '2kj0tst4rj96f') loop
  5      sys.dbms_sqldiag_internal.i_create_patch(sql_text  => reco.sql_fulltext,
  6                                               hint_text => rpad('x', 500, 'x'),
  7                                               name      => 'SQL_PATCH_TEST');
  8    end loop;
  9  end;
 10  /

PL/SQL procedure successfully completed.

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

SQL> select /*SQL Patch Test*/ dummy from dual;

D
-
X

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
SQL_ID  2kj0tst4rj96f, child number 0
-------------------------------------
select /*SQL Patch Test*/ dummy from dual

Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

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

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

— сгенерированная «подсказка» из крестиков:

SQL> @sqlpatch_hints SQL_PATCH_TEST

SQL_PATCH_HINTS
---------------------------------------------------------------------------------------------------------------------------------------------------------
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx...

— на план не повлияла, и в секцию Outline Data не попала, но отраженается как вполне работоспособная:

SQL> @shared_cu12 2kj0tst4rj96f
 
EXECS FIRST_LOAD_TIME     LAST_LOAD_TIME      LAST_ACTIVE_TIME    ELA_PER_EXEC PLAN_HASH_VALUE OPTIMIZER_COST CHILD SHAREABLE  SQL_PATCH      IS_OBSOLETE
----- ------------------- ------------------- ------------------- ------------ --------------- -------------- ----- ---------- -------------- -----------
    1 2015-02-16/15:42:16 2015-02-16/15:49:40 16.02.2015 15:49:39        18864       272002086              2     0 Y          SQL_PATCH_TEST N

5 комментариев »

  1. Можно как-то так впихнуть:

    declare
    l_hint_text_0 varchar2 (500);
    l_hint_text_1 varchar2 (500);
    …..
    l_hintset sys.sqlprof_attr;
    …..
    begin
    l_hint_text_0 := ‘…..’;
    l_hint_text_1 := ‘…..’;
    …..

    l_hintset :=
    sys.sqlprof_attr (l_hint_text_0,
    l_hint_text_1,
    ….);
    l_ret_name :=
    sys.dbms_sqltune_internal.i_create_sql_profile
    (sql_text => …,
    profile_xml => sys.dbms_smb_internal.varr_to_hints_xml (l_hintset),
    ….
    type => ‘PATCH’,
    is_patch => true);
    end;

    комментарий от Константин — 24.02.2015 @ 09:07 | Ответить

    • Спасибо, Костантин, всё верно, 500 — ограничение не SQL Patch, а процедуры DBMS_SQLDIAG_INTERNAL.I_CREATE_PATCH,

      С помощью указанной DBMS_SQLTUNE_INTERNAL.I_CREATE_SQL_PROFILE можно делать большие патчи, вплоть до помещения в SQL Patch полного Outline плана:

      SQL> DECLARE
        2    cur        dbms_sqltune.sqlset_cursor;
        3    l_hintset  sys.sqlprof_attr;
        4    l_ret_name varchar2(30);
        5    l_sqlset_row sqlset_row;
        6  BEGIN
        7    OPEN cur FOR
        8      SELECT VALUE(P)
        9        FROM table(dbms_sqltune.select_cursor_cache(q'[sql_id = '2kj0tst4rj96f' and plan_hash_value = 272002086]')) P;
       10    FETCH cur
       11      INTO l_sqlset_row;
       12    for i in 1 .. l_sqlset_row.sql_plan.count loop
       13      if l_sqlset_row.sql_plan(i).other_xml is not null then
       14        select extractvalue(value(d), '/hint') as outline_hints
       15          bulk collect
       16          into l_hintset
       17          from xmltable('/*/outline_data/hint' passing
       18                        (select xmltype(l_sqlset_row.sql_plan(i).other_xml) as xmlval
       19                           from dual)) d;
       20      end if;
       21    end loop;
       22  -- L_HINTSET contents
       23    for i in 1 .. l_hintset.count loop
       24      dbms_output.put_line(l_hintset(i));
       25    end loop;
       26  --
       27    l_ret_name := sys.dbms_sqltune_internal.i_create_sql_profile(sql_text    => l_sqlset_row.sql_text,
       28                                                                 profile_xml => sys.dbms_smb_internal.varr_to_hints_xml(l_hintset),
       29                                                                 name        => 'PATCH_FROM_PROFILE',
       30                                                                 type        => 'PATCH',
       31                                                                 is_patch    => true,
       32                                                                 validate    => true,                   -- does not work (*)
       33                                                                 replace     => true,                   -- успешно пересоздаёт
       34                                                                 force_match => true,                   -- works (**)
       35                                                                 plan_rows   => l_sqlset_row.sql_plan); -- does not work (***)
       36  /* L_SQLSET_ROW.SQL_PLAN contents
       37    for i in 1 .. l_sqlset_row.sql_plan.count loop
       38      dbms_output.put_line(l_sqlset_row.sql_plan(i).operation   || ' ' ||
       39                           l_sqlset_row.sql_plan(i).options     || ' ' ||
       40                           l_sqlset_row.sql_plan(i).object_name);
       41    end loop;
       42  */
       43    CLOSE cur;
       44  END;
       45  /
      
      PL/SQL procedure successfully completed.
      
      IGNORE_OPTIM_EMBEDDED_HINTS                   -- L_HINTSET contents
      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
      DB_VERSION('12.1.0.2')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "DUAL"@"SEL$1")
      
      SQL> -- содержимое Outline выбранного плана выполнения успешно попало в SQL Patch:
      SQL> @sqlpatch_hints PATCH_FROM_PROFILE
      
      SQL_PATCH_HINTS
      ---------------------------------------
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
      DB_VERSION('12.1.0.2')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "DUAL"@"SEL$1")

      *) — т.е. по-прежнему можно запихнуть всё что душа пожелает
      **) работает, SQL Patch and Force Match
      ***) входящий/IN параметр типа SQL_PLAN_TABLE_TYPE не совсем мне понятен, значение L_SQLSET_ROW.SQL_PLAN он успешно принимает,
      но патча из этого плана не создаёт (что было бы крайне удобно), требуя непустое значение PROFILE_XML ошибкой:

      ORA-13827: null or zero length attribute not allowed
      ORA-06512: at "SYS.DBMS_SQLTUNE_INTERNAL", line 17518

      комментарий от Игорь Усольцев — 25.02.2015 @ 11:26 | Ответить

      • Очень полезный коммент! :) Будем использовать на основе этого скриптик, которым будем при необходимости накатывать старые планы при переходе на 12с (у нас SE, поэтому SQL-патчи c полным набором хинтов — это то, что нужно). До этого реально не так много пользовались SQL-патчами, хватало и 500 символов…

        комментарий от Деев Илья — 25.11.2015 @ 18:45 | Ответить

        • Илья, у меня в скриптах есть готовый скрипт для создания SQL Profile по sql_id и plan_hash_value из Shared Pool или AWR-репозитория, если что:

          SQL> @sql_profile_from_sql 1mbx3m11g17vn 547348540 "profile_for_1mbx3m11g17vn" "profile_for_1mbx3m11g17vn"
           
          PL/SQL procedure successfully completed

          комментарий от Игорь Усольцев — 25.11.2015 @ 21:59 | Ответить

  2. Причём тут sys.dbms_sqltune_internal.i_create_sql_profile и SQL Patch?
    Хм..

    комментарий от qq — 10.11.2016 @ 16:35 | Ответить


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 такие блоггеры, как: