Oracle mechanics

19.03.2012

Temp Table Transformation и запросы к удалённому VIEW

Filed under: CBO,heuristics,Oracle,SQL — Игорь Усольцев @ 23:25
Tags: , , ,

При анализе очередной (грустной) проблемы с некорректным результатом запроса к удалённому обзору, вызванным изменившимся планом выполнения, заметил что операция Temp Table Transformation, успешно применяемая при выполнении локальных запросов к обзору, построенному с использованием материализованного subquery factoring, не используется при выполнении запросов через db link

Получилось чуткосложноподчинённое предложение, проще проиллюстрировать проблему:

11.2.0.2.ORCL112@SYSTEM SQL> create or replace view SF_REMOTE_TEST
2  as
3  with v5 as
4  (
5      select--+ materialize
6      rownum from dual
7      connect by rownum <= 1000000
8  )
9  select count(*) c from v5
10  /

View created.

Запрос к локальному обзору:

SQL> select * from SF_REMOTE_TEST;

Plan hash value: 2357901317

----------------------------------------------------------------------
| Id  | Operation                        | Name                      |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                           |
|   1 |  VIEW                            | SF_REMOTE_TEST            |
|   2 |   TEMP TABLE TRANSFORMATION      |                           |
|   3 |    LOAD AS SELECT                | SYS_TEMP_0FD9D66D2_7A1969 |
|   4 |     COUNT                        |                           |
|*  5 |      CONNECT BY WITHOUT FILTERING|                           |
|   6 |       FAST DUAL                  |                           |
|   7 |    SORT AGGREGATE                |                           |
|   8 |     VIEW                         |                           |
|   9 |      TABLE ACCESS FULL           | SYS_TEMP_0FD9D66D2_7A1969 |
----------------------------------------------------------------------

При выполнении того же запроса к удалённому обзору:

SQL> set autotrace trace exp
SQL> select * from SF_REMOTE_TEST@LOOPBACK;

Plan hash value: 2761802298

--------------------------------------------------------------------
| Id  | Operation                        | Name           | Inst   |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT REMOTE          |                |        |
|   1 |  VIEW                            | SF_REMOTE_TEST | ORCL1~ |
|   2 |   SORT AGGREGATE                 |                |        |
|   3 |    VIEW                          |                |        |
|   4 |     COUNT                        |                |        |
|*  5 |      CONNECT BY WITHOUT FILTERING|                |        |
|   6 |       FAST DUAL                  |                |        |
--------------------------------------------------------------------

Note
-----
- fully remote statement

— поскольку запрос выполняется полностью на удалённом сайте, AUTOTRACE показывает план другого, рекурсивного запроса, находящегося в shared pool и выполняющегося на «удалённой» бд, который можно найти по plan_hash_value из autotrace и характерно-нормализованному тексту:

SQL> alter system flush shared_pool;

System altered.

SQL> select * from SF_REMOTE_TEST@LOOPBACK;

SQL> select sql_id, sql_text, child_number, plan_hash_value, executions
2    from v$sql
3   where (upper(sql_text) like 'SELECT%SF_REMOTE_TEST@LOOPBACK%'
4          and upper(sql_text) not like '%V$SQL%')
5      or plan_hash_value = 2761802298;

SQL_ID        SQL_TEXT                                         CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS
------------- ------------------------------------------------ ------------ --------------- ----------
3rywtpy96cvag select * from SF_REMOTE_TEST@LOOPBACK                       0               0          1
dpc2b0b8hb174 SELECT /*+ FULL(P) +*/ * FROM "SF_REMOTE_TEST" P            1      2761802298          0
dvhxps45r7j8s SELECT "A1"."C" FROM "SF_REMOTE_TEST" "A1"                  1      2761802298          1

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor( 'dvhxps45r7j8s','1','basic'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
SELECT "A1"."C" FROM "SF_REMOTE_TEST" "A1"

Plan hash value: 2761802298

-----------------------------------------------------------
| Id  | Operation                        | Name           |
-----------------------------------------------------------
|   0 | SELECT STATEMENT                 |                |
|   1 |  VIEW                            | SF_REMOTE_TEST |
|   2 |   SORT AGGREGATE                 |                |
|   3 |    VIEW                          |                |
|   4 |     COUNT                        |                |
|   5 |      CONNECT BY WITHOUT FILTERING|                |
|   6 |       FAST DUAL                  |                |
-----------------------------------------------------------

— рекурсивный запрос dvhxps45r7j8s (plan_hash_value = 2761802298), выполняющийся на «дальней» стороне линка

При сранении трейсов оптимизатора мне не удалось обнаружить оснований для неиспользования Temp Table Transformation при выполнении «удалённого» рекурсивного запроса:

...
sql=SELECT "A1"."C" FROM "SF_REMOTE_TEST" "A1"
...

— предположительно, это hard-coded поведение, одна из разновидностей optimizer heuristic, как и появление в shared pool удалённой бд другого рекурсивного неисполняемого запроса

SELECT /*+ FULL(P) +*/ * FROM "SF_REMOTE_TEST" P

с тем же значением plan_hash_value = 2761802298 и V$SQL.EXECUTIONS = 0

Проблема неновая, Randolf Geist описал аналогичное поведение для случая удалённого вызова процедур — TEMP Table Transformation and Remote Procedure Calls.

Там же в обсуждении в качестве workaround’а предлагалось использование автономных транзакций, что можно легко протестировать:

SQL> alter system flush shared_pool;

System altered.

SQL> select sql_id, sql_text, child_number child, plan_hash_value plan_hash, parse_calls parses, executions execs, last_active_time
2  from v$sql where sql_id = 'dvhxps45r7j8s' or plan_hash_value = 2761802298;

no rows selected

SQL> -- выполняем неизменённый запрос к удалённому обзору через db link
SQL> select C from SF_REMOTE_TEST@loopback;

SQL> select sql_id, sql_text, child_number child, plan_hash_value plan_hash, parse_calls parses, executions execs, last_active_time
2  from v$sql where sql_id = 'dvhxps45r7j8s' or plan_hash_value = 2761802298;

SQL_ID        SQL_TEXT                                                          CHILD  PLAN_HASH     PARSES      EXECS LAST_ACT
------------- ------------------------------------------------------------ ---------- ---------- ---------- ---------- --------
dpc2b0b8hb174 SELECT /*+ FULL(P) +*/ * FROM "SF_REMOTE_TEST" P                      1 2761802298       0             0 04:00:00 -- вспомогательный запрос для парсинга удалённого выполнения
dvhxps45r7j8s SELECT "A1"."C" FROM "SF_REMOTE_TEST" "A1"                            0 2761802298       1             1 21:12:59 -- собственно рекурсивный запрос

SQL> -- тестирование автономной транзакции
SQL> exec dbms_lock.sleep(1); -- для получения различных v$SQL.LAST_ACTIVE_TIME
SQL> declare
2    r number;
3    pragma AUTONOMOUS_TRANSACTION;
4  begin
5    select /* AUTONOMOUS_TRANSACTION */ C into R from SF_REMOTE_TEST@loopback;
6    rollback;
7  end;
8  /

PL/SQL procedure successfully completed.

SQL> select sql_id, sql_text, child_number child, plan_hash_value plan_hash, parse_calls parses, executions execs, last_active_time
2  from v$sql where sql_id = 'dvhxps45r7j8s' or plan_hash_value = 2761802298;

SQL_ID        SQL_TEXT                                                          CHILD  PLAN_HASH     PARSES      EXECS LAST_ACT
------------- ------------------------------------------------------------ ---------- ---------- ---------- ---------- --------
dpc2b0b8hb174 SELECT /*+ FULL(P) +*/ * FROM "SF_REMOTE_TEST" P                      1 2761802298       1             0 21:13:01 -- вспомогательный запрос, разбирается, не выполняется
dvhxps45r7j8s SELECT "A1"."C" FROM "SF_REMOTE_TEST" "A1"                            0 2761802298       2             2 21:13:01 -- ещё раз выполнился тот же рекурсивный запрос

— к сожалению, в случае с запросом к удалённому обзору, использование автономной транзакции никак не влияет на применение Temp Table Transformation при выполнении удалённого рекурсивного запроса

Если продолжить тесты, пытаясь повлиять на план выполнения того же запроса с помощью подсказки:

SQL> -- запрос к удалённой бд с подсказкой в рамках автономной транзакции
SQL> exec dbms_lock.sleep(1);

SQL> declare
2    r number;
3    pragma AUTONOMOUS_TRANSACTION;
4  begin
5    select /*+ no_query_transformation */ C into R from SF_REMOTE_TEST@loopback;
6    rollback;
7  end;
8  /

PL/SQL procedure successfully completed.

SQL> select sql_id, sql_text, child_number child, plan_hash_value plan_hash, parse_calls parses, executions execs, last_active_time
2  from v$sql where sql_id = 'dvhxps45r7j8s' or plan_hash_value = 2761802298;

SQL_ID        SQL_TEXT                                                          CHILD  PLAN_HASH     PARSES      EXECS LAST_ACT
------------- ------------------------------------------------------------ ---------- ---------- ---------- ---------- --------
dpc2b0b8hb174 SELECT /*+ FULL(P) +*/ * FROM "SF_REMOTE_TEST" P                      1 2761802298       2             0 21:13:02
dvhxps45r7j8s SELECT "A1"."C" FROM "SF_REMOTE_TEST" "A1"                            0 2761802298       3             3 21:13:02 -- ещё раз выполнился тот же рекурсивный запрос

SQL> -- тот же запрос с хинтом из SQL*Plus
SQL> exec dbms_lock.sleep(1);
SQL> select /*+ no_query_transformation */ C from SF_REMOTE_TEST@loopback;

SQL> select sql_id, sql_text, child_number child, plan_hash_value plan_hash, parse_calls parses, executions execs, last_active_time
2  from v$sql where sql_id = 'dvhxps45r7j8s' or plan_hash_value = 2761802298;

SQL_ID        SQL_TEXT                                                          CHILD  PLAN_HASH     PARSES      EXECS LAST_ACT
------------- ------------------------------------------------------------ ---------- ---------- ---------- ---------- --------
dpc2b0b8hb174 SELECT /*+ FULL(P) +*/ * FROM "SF_REMOTE_TEST" P                      1 2761802298       3             0 21:13:04
dvhxps45r7j8s SELECT "A1"."C" FROM "SF_REMOTE_TEST" "A1"                            0 2761802298       4             4 21:13:04 -- и ещё раз выполнился тот же рекурсивный запрос

— можно заметить, что во всех протестированных вариантах в удалённой бд выполняется один и тот же рекурсивный запрос (sql_id=’dvhxps45r7j8s’ child_number=0 plan_hash_value = 2761802298) — при генерации плана выполнения которого не рассматривается  не только Temp Table Transformation, но и подсказки, передаваемые в теле запроса — очень похоже на то, что все комментарии, включая хинты исключаются при нормализации запроса)

Для сравнения, при локальном выполнении запроса использованная в предыдущих тестах подсказка no_query_transformation меняет план выполнения — по крайней мере, судя по изменившемуся PLAN_HASH_VALUE :)

SQL> alter system flush shared_pool;

System altered.

SQL> select C from SF_REMOTE_TEST;

SQL> select /*+ no_query_transformation */ C from SF_REMOTE_TEST;

SQL> select sql_id, sql_text, child_number, plan_hash_value, executions from v$sql where sql_text like 'select%from SF_REMOTE_TEST%';

SQL_ID        SQL_TEXT                                                     CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS
------------- ------------------------------------------------------------ ------------ --------------- ----------
bhhk4699rpgdd select C from SF_REMOTE_TEST                                            0      2694163852          1
444pmzbd6mwvf select /*+ no_query_transformation */ C from SF_REMOTE_TEST             0      1507994921          1

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

  1. Проверил сейчас с /*+ opt_param(‘_with_subquery’ ‘materialize’) */ и alter system set «_with_subquery»=inline scope=memory; — к сожалению, не помогает в случае c remote. Однако, в случае, если отстутствует хинтв тексте вьюхи, то этими параметрами можно успешно играть

    комментарий от Sayan Malakshinov — 25.07.2013 @ 20:15 | Ответить

    • Привет, Саян, не совсем понял последнее предложение — ты хочешь сказать что без подсказок в DDL обзора, возможна «материализация» запроса на remote side?

      У меня на 11.2.0.3 не получилось так:

      11.2.0.3.ORCL112@SCOTT SQL> show parameter "_with_subquery"
      
      NAME                                 TYPE        VALUE
      ------------------------------------ ----------- -----------
      _with_subquery                       string      MATERIALIZE -- установил на локальном и "удалённом" сайте
      
      SQL> create or replace view SF_REMOTE_TEST                   -- переделал обзор без подсказки
        2  as
        3  with v5 as
        4  (
        5      select
        6      rownum from dual
        7      connect by rownum <= 1000000
        8  )
        9  select count(*) c from v5
       10  /
      
      View created.
      
      SQL> alter system flush shared_pool;                         -- зачистил концы
       
      System altered
      
      SQL> select /*+ opt_param('_with_subquery' 'materialize') */ * from SF_REMOTE_TEST@LOOPBACK; -- усилил подсказкой
      
               C
      ----------
         1000000
      
      SQL> select sql_id, sql_text, child_number, plan_hash_value, executions
        2    from v$sql
        3   where (upper(sql_text) like 'SELECT%SF_REMOTE_TEST@LOOPBACK%' and
        4         upper(sql_text) not like '%V$SQL%')
        5      or plan_hash_value = 2761802298;
       
      SQL_ID        SQL_TEXT                                                                         CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS
      ------------- -------------------------------------------------------------------------------- ------------ --------------- ----------
      cxp0k2syv8h6x select /*+ opt_param('_with_subquery' 'materialize') */ * from SF_REMOTE_TEST@LO            0               0          1
      dpc2b0b8hb174 SELECT /*+ FULL(P) +*/ * FROM "SF_REMOTE_TEST" P                                            0      2761802298          0
      dvhxps45r7j8s SELECT "A1"."C" FROM "SF_REMOTE_TEST" "A1"                                                  0      2761802298          1
      -- последние 2 запроса появились в "удалённом" Shared Pool; последний SQL_ID=dvhxps45r7j8s выполнился с INLINE планом:
      
      SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor( 'dvhxps45r7j8s'));
       
      Plan hash value: 2761802298
      
      ---------------------------------------------------------------------------------------------------
      | Id  | Operation                        | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
      ---------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT                 |                |       |       |     2 (100)|          |
      |   1 |  VIEW                            | SF_REMOTE_TEST |     1 |    13 |     2   (0)| 00:00:01 |
      |   2 |   SORT AGGREGATE                 |                |     1 |       |            |          |
      |   3 |    VIEW                          |                |     1 |       |     2   (0)| 00:00:01 |
      |   4 |     COUNT                        |                |       |       |            |          |
      |   5 |      CONNECT BY WITHOUT FILTERING|                |       |       |            |          |
      |   6 |       FAST DUAL                  |                |     1 |       |     2   (0)| 00:00:01 |
      ---------------------------------------------------------------------------------------------------
      
      SQL> create or replace view SF_REMOTE_TEST
        2  as
        3  with v5 as
        4  (
        5      select
        6      rownum from dual
        7      connect by rownum <= 1000000
        8  )
        9  select /*+ opt_param('_with_subquery' 'materialize') */ count(*) c from v5  -- такой вариант обзора также ничего не меняет
       10  /
      
      -- 10053 trace on "remote" site:
      ***************************************
      PARAMETERS USED BY THE OPTIMIZER
      ********************************
        *************************************
        PARAMETERS WITH ALTERED VALUES
        ******************************
      Compilation Environment Dump
      parallel_threads_per_cpu            = 16
      _with_subquery                      = MATERIALIZE                   -- хинт OPT_PARAM дошёл до удалённого оптимизатора
      ...
      ******************************************
      ----- Current SQL Statement for this session (sql_id=dvhxps45r7j8s) -----
      SELECT "A1"."C" FROM "SF_REMOTE_TEST" "A1"                          -- в таком виде запрос появляется на удалённом сайте,
      *******************************************
      ...
      Final query after transformations:******* UNPARSED QUERY IS ******* -- а таким становится после "преобразований для удалённых запросов":
      SELECT "A1"."C" "C" FROM  (SELECT COUNT(*) "C" FROM  (SELECT ROWNUM "ROWNUM" FROM "SYS"."DUAL" "DUAL" CONNECT BY ROWNUM<=1000000) "V5") "A1"
      -- и, соответственно, не очень годится для материализации, даже насильственной ;)

      комментарий от Igor Usoltsev — 25.07.2013 @ 23:47 | Ответить

      • Игорь, я имел ввиду второй вопрос с семинара с Льюисом — насчет запрета материализации на стендбае. То есть вообще в принципе управление материализацией локальных запросов

        комментарий от Sayan Malakshinov — 26.07.2013 @ 00:13 | Ответить

      • Привет, Игорь! :)
        Кстати, Джонатан не отвечал больше по этой теме?
        зы. Как-то не привык в комментариях здороваться, потому что, как правило, их читают и отвечают поздно :)

        комментарий от Sayan Malakshinov — 26.07.2013 @ 00:15 | Ответить

        • нет, не отвечал
          В общем-то, недаром Oracle хинты MATERIALIZE / INLINE и в 12c не документированы — по причине их слабой управляемости , несмотря на все очевидные преимущества. Подсказки «прямого действия» :)

          комментарий от Igor Usoltsev — 26.07.2013 @ 00:46 | Ответить

      • Насчёт «не очень годится для материализации» — неверно, погорячился, был неправ ;)
        И при локальном, и при удалённом выполнении, запросы после преобразования выглядят аналогично независимо от использования подсказок в тексте запроса

        Интересно, что для запроса с подсказкой MATERIALIZE при запросах и к локальному, и к удалённому обзорам, в трейсе оптимизатора отмечается успешное использование подсказки:

        SQL> select * from SF_REMOTE_TEST@LOOPBACK; -- запрос к удалённому обзору
        ...
        Dumping Hints
        =============
          atom_hint=(@=000000000D499DC0 err=0 resol=1 used=1 token=855 org=1 lvl=2 txt=MATERIALIZE ())
        ====================== END SQL Statement Dump ======================
        
        SQL> select * from SF_REMOTE_TEST;                      -- запрос к локальному обзору
        ...
        Dumping Hints
        =============
          atom_hint=(@=0000000008944B40 err=0 resol=1 used=0 token=819 org=1 lvl=3 txt=CACHE_TEMP_TABLE ("T1") )
          atom_hint=(@=00000000089454B0 err=0 resol=1 used=1 token=855 org=1 lvl=2 txt=MATERIALIZE ())

        комментарий от Igor Usoltsev — 27.07.2013 @ 00:42 | Ответить

        • Привет, Игорь!

          Сегодня еще раз пришлось столкнуться с материализацией и вьюхой с with_subquery: что-то у меня вышло что хинт opt_param не может менять параметра «_with_subquery», я пробовал так:

          create or replace view xt_test_materialize
          as
             with v5 as
             (
                 select  level n from dual
                 connect by level <= 5
             )
             select 1 x, n from v5
             union 
             select 2 x, n from v5
             union 
             select 3 x, n from v5
             union 
             select 4 x, n from v5
             union 
             select 5 x, n from v5
          /
          select/*+ opt_param('_with_subquery' 'inline') */ count(distinct n) from xt_test_materialize
          

          комментарий от Sayan Malakshinov — 19.01.2015 @ 17:33 | Ответить

          • Посмотреть бы где-нибудь Note:986618.1 Parameters useable by OPT_PARAM hint

            комментарий от Sayan Malakshinov — 19.01.2015 @ 17:51 | Ответить

          • Привет, Саян!

            Да, _with_subquery через opt_param не пробрасывается :(

            К слову, насчёт вопроса с семинара с Льюисом (проблемы ORA-16000 с материализацией на Read-Only стендбае),
            проблема исправлена производителем в 11.2.0.4, а для 11.2.0.3 есть работающий (ставили/проверяли) патч — Bug 13847666 : ORA-16000 IN ACTIVE DATAGUARD AFTER UPGRADE TO 11.2.0.3

            комментарий от Игорь Усольцев — 19.01.2015 @ 18:32 | Ответить


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