Oracle mechanics

07.09.2014

Материализация XMLTYPE запросов

Filed under: error,heuristics,Oracle,XML DB — Игорь Усольцев @ 19:10
Tags: ,

Попытка выполнение стандартного запроса по извлечению текста подсказок (outline hint) из плана выполнения (V$SQL_PLAN.OTHER_XML) до версии 12.1.0.1 включительно выдаёт характерную при материализации подзапроса с XMLTYPE ошибку — XMLTYPE in a WITH Clause Fails with ORA-06512 & ORA-06502 (Doc ID 1640869.1):

11.2.0.3.@ SQL> with ol as
  2   (select--+ materialize
  3           plan_hash_value, b.hint
  4      from v$sql_plan m,
  5           xmltable('/other_xml/outline_data/hint' passing
  6                    xmltype(m.OTHER_XML) columns hint varchar2(4000) path '/hint') b
  7     where trim(OTHER_XML) is not null
  8       and rownum <= 2)
  9  select * from ol
 10  /
                  xmltype(m.OTHER_XML) columns hint varchar2(4000) path '/hint') b
                  *
ERROR at line 6:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.XMLTYPE", line 272
ORA-06512: at line 1

При этом используется завышенная (некая стандартная) оценка кол-ва строк, возвращаемых функцией XQSEQUENCEFROMXMLTYPE в материализованной части запроса:

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                            |       |       |     3 (100)|          |
|   1 |  TEMP TABLE TRANSFORMATION             |                            |       |       |         |     |
|   2 |   LOAD AS SELECT                       |                            |       |       |         |     |
|*  3 |    COUNT STOPKEY                       |                            |       |       |         |     |
|   4 |     NESTED LOOPS                       |                            |     2 |   998 |     1   (0)| 00:00:01 |
|*  5 |      FIXED TABLE FULL                  | X$KQLFXPL                  |     1 |   465 |     0   (0)|          |
|   6 |      VIEW                              |                            |     2 |    68 |     1   (0)| 00:00:01 |
|   7 |       COLLECTION ITERATOR PICKLER FETCH| XQSEQUENCEFROMXMLTYPE      |  4072 |       |    22   (0)| 00:00:01 | -- wrong (default) cardinality
|   8 |   VIEW                                 |                            |     2 |  4030 |     2   (0)| 00:00:01 |
|   9 |    TABLE ACCESS FULL                   | SYS_TEMP_0FD9FCD0F_23552E4 |     2 |   998 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------

Без подсказки /*+ MATERIALIZE */ или, что эквивалентно, при /*+ INLINE */ выполнении — всё работает хорошо, без ошибок:

SQL> with ol as
  2   (select plan_hash_value, b.hint
  3      from v$sql_plan m,
  4           xmltable('/other_xml/outline_data/hint' passing
  5                    xmltype(m.OTHER_XML) columns hint varchar2(4000) path '/hint') b
  6     where trim(OTHER_XML) is not null
  7       and rownum <= 2)
  8  select * from ol;

PLAN_HASH_VALUE HINT
--------------- ------------------------------------------------------------
     4215749665 IGNORE_OPTIM_EMBEDDED_HINTS
     4215749665 OPTIMIZER_FEATURES_ENABLE('11.2.0.3')

2 rows selected.

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                       |       |       |     2 (100)|       |
|   1 |  VIEW                                |                       |     2 |  4030 |     2   (0)| 00:00:01 |
|*  2 |   COUNT STOPKEY                      |                       |       |       |            |       |
|   3 |    NESTED LOOPS                      |                       |     2 |   934 |     2   (0)| 00:00:01 |
|*  4 |     FIXED TABLE FULL                 | X$KQLFXPL             |     1 |   465 |     0   (0)|       |
|   5 |     COLLECTION ITERATOR PICKLER FETCH| XQSEQUENCEFROMXMLTYPE |     2 |     4 |     2   (0)| 00:00:01 | -- correct
--------------------------------------------------------------------------------------------------------------

+ план в части числа строк, возвращаемых xmltable точен в отличие от запроса с материализацией

Точно такая же и, видимо, по той же причине, ошибка возникает при совместном использовании XMLTYPE/xmltable с GV$-функцией, даже без WITH и прочей магии материализации:

SQL> select plan_hash_value, b.hint
  2    from TABLE(GV$(CURSOR (select plan_hash_value, OTHER_XML
  3                      from v$sql_plan
  4                     where trim(OTHER_XML) is not null))) m,
  5         xmltable('/other_xml/outline_data/hint' passing xmltype(m.OTHER_XML)
  6                  columns hint varchar2(4000) path '/hint') b
  7   where rownum <= 2
  8  /
       xmltable('/other_xml/outline_data/hint' passing xmltype(m.OTHER_XML)
                                                       *
ERROR at line 5:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.XMLTYPE", line 272
ORA-06512: at line 1


-------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                       |       |       |     1 (100)|       |           |      |            |
|*  1 |  COUNT STOPKEY                       |                       |       |       |            |       |           |      |            |
|   2 |   NESTED LOOPS                       |                       |     3 |   141 |     1   (0)| 00:00:01 |        |      |            |
|   3 |    PX COORDINATOR                    |                       |     1 |    13 |     1 (100)| 00:00:01 |        |      |            |
|   4 |     PX SEND QC (RANDOM)              | :TQ10000              |     3 |    39 |     0   (0)|       |     Q1,00 | P->S | QC (RAND)  |
|   5 |      VIEW                            |                       |       |       |            |       |     Q1,00 | PCWP |            |
|   6 |       VIEW                           | V$SQL_PLAN            |     3 |    39 |     0   (0)|       |     Q1,00 | PCWP |            |
|   7 |        VIEW                          | GV$SQL_PLAN           |     3 |  6045 |     0   (0)|       |     Q1,00 | PCWP |            |
|*  8 |         FIXED TABLE FULL             | X$KQLFXPL             |     3 |  1395 |     0   (0)|       |     Q1,00 | PCWP |            |
|   9 |    VIEW                              |                       |     3 |   102 |     1   (0)| 00:00:01 |        |      |            |
|  10 |     COLLECTION ITERATOR PICKLER FETCH| XQSEQUENCEFROMXMLTYPE |  4072 |       |    22   (0)| 00:00:01 |        |      |            | -- неточно, по умолчанию
-------------------------------------------------------------------------------------------------------------------------------------------

— с той же ошибочной cardinality XMLTYPE-функции, но GV$-функции не документированы и претензий быть не может

Указанный баг 16342156 планировали поправить в 12.2, но поспешили — 12.1.0.2 Patch Set — List of Bug Fixes by Problem Type (Doc ID 1683802.1), и сделали это быстро, но странно:

12.1.0.2.@ SQL> with ol as
  2   (select /*+ materialize*/
  3     plan_hash_value, b.hint
  4      from v$sql_plan m,
  5           xmltable('/other_xml/outline_data/hint' passing
  6                    xmltype(m.OTHER_XML) columns hint varchar2(4000) path
  7                    '/hint') b
  8     where trim(OTHER_XML) is not null
  9       and rownum <= 2)
 10  select * from ol;

PLAN_HASH_VALUE HINT
--------------- ------------------------------------------------------------
     1971412026 IGNORE_OPTIM_EMBEDDED_HINTS
     1971412026 OPTIMIZER_FEATURES_ENABLE('12.1.0.2')

2 rows selected.

-------------------------------------------------------------------------------------
| Id  | Operation               | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |           |       |       |     2 (100)|          |
|   1 |  VIEW                   |           |     2 |  4030 |     2   (0)| 00:00:01 |
|*  2 |   COUNT STOPKEY         |           |       |       |            |          |
|   3 |    NESTED LOOPS         |           |     3 |  2193 |     2   (0)| 00:00:01 |
|*  4 |     FIXED TABLE FULL    | X$KQLFXPL |     1 |   729 |     0   (0)|          |
|   5 |     XMLTABLE EVALUATION |           |       |       |            |          | -- похоже, новая операция оптимизатора 12c
-------------------------------------------------------------------------------------

— похоже, что от материализации запроса к xmltable просто отказались, по крайней мере, трейс показывает, что оптимизатор успешно опознал, но не использовал подсказку /*+ MATERIALIZE*/:

Dumping Hints
=============
  atom_hint=(@=0x7f02f7b9d1e8 err=0 resol=1 used=0 token=855 org=1 lvl=2 txt=MATERIALIZE ())

Интересно, а будет ли Oracle использовать TEMP TABLE TRANSFORMATION для экономии ресурсов в случае многократных использованиях subquery factoring в основном запросе, т.е. в таком случае, для которого материализация собств-но и предназначалась?

12.1.0.2.@ SQL> with ol as
  2   (select /*+ materialize*/
  3     plan_hash_value, b.hint
  4      from v$sql_plan m,
  5           xmltable('/other_xml/outline_data/hint' passing
  6                    xmltype(m.OTHER_XML) columns hint varchar2(4000) path '/hint') b
  7     where trim(OTHER_XML) is not null
  8       and rownum <= 2)
  9  select * from ol
 10  union all
 11  select * from ol where hint like 'IGNOR%'
 12  union all
 13  select * from ol where hint like 'OPTIMIZER%'
 14  /

PLAN_HASH_VALUE HINT
--------------- ------------------------------------------------------------
     1971412026 IGNORE_OPTIM_EMBEDDED_HINTS
     1971412026 OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
     1971412026 IGNORE_OPTIM_EMBEDDED_HINTS
     1971412026 OPTIMIZER_FEATURES_ENABLE('12.1.0.2')

4 rows selected.

--------------------------------------------------------------------------------------
| Id  | Operation                | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |           |       |       |     6 (100)|          |
|   1 |  UNION-ALL               |           |       |       |            |          |
|   2 |   VIEW                   |           |     2 |  4030 |     2   (0)| 00:00:01 |
|*  3 |    COUNT STOPKEY         |           |       |       |            |          |
|   4 |     NESTED LOOPS         |           |     3 |  2193 |     2   (0)| 00:00:01 |
|*  5 |      FIXED TABLE FULL    | X$KQLFXPL |     1 |   729 |     0   (0)|          |
|   6 |      XMLTABLE EVALUATION |           |       |       |            |          |
|*  7 |   VIEW                   |           |     2 |  4030 |     2   (0)| 00:00:01 |
|*  8 |    COUNT STOPKEY         |           |       |       |            |          |
|   9 |     NESTED LOOPS         |           |     3 |  2193 |     2   (0)| 00:00:01 |
|* 10 |      FIXED TABLE FULL    | X$KQLFXPL |     1 |   729 |     0   (0)|          |
|  11 |      XMLTABLE EVALUATION |           |       |       |            |          |
|* 12 |   VIEW                   |           |     2 |  4030 |     2   (0)| 00:00:01 |
|* 13 |    COUNT STOPKEY         |           |       |       |            |          |
|  14 |     NESTED LOOPS         |           |     3 |  2193 |     2   (0)| 00:00:01 |
|* 15 |      FIXED TABLE FULL    | X$KQLFXPL |     1 |   729 |     0   (0)|          |
|  16 |      XMLTABLE EVALUATION |           |       |       |            |          |
--------------------------------------------------------------------------------------

— увы, похоже, что операция TEMP TABLE TRANSFORMATION для запросов с XMLTYPE/xmltable просто запрещена

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

  1. Игорь, попробуй вместо passing(xmltype(m.other_xml)) передать xmltype(nvl(trim(m.OTHER_XML),’‘)) или вместо trim(m.other_xml) is not null поставить length(trim(OTHER_XML))>0

    комментарий от Sayan Malakshinov — 07.09.2014 @ 19:31 | Ответить

    • c nvl не пойдет по длине наверное, поэтому только с length попробовать

      комментарий от Sayan Malakshinov — 07.09.2014 @ 19:35 | Ответить

      • спасибо, Саян, результат — тот же, т.к. проблема имхо именно на пересечении XMLTYPE и MATERIALIZE (о чём Oracle-овцы и пишут):

        12.1.0.1.@ SQL> with ol as
          2   (select--+ materialize
          3           plan_hash_value, b.hint
          4      from v$sql_plan m,
          5           xmltable('/other_xml/outline_data/hint' passing xmltype(nvl(trim(m.OTHER_XML),''))
          6                    columns hint varchar2(4000) path '/hint') b
          7     where length(trim(OTHER_XML))>0
          8       and rownum <= 2)
          9  select * from ol
         10  /
                 xmltable('/other_xml/outline_data/hint' passing xmltype(nvl(trim(m.OTHER_XML),''))
                                                                 *
        ERROR at line 5:
        ORA-06502: PL/SQL: numeric or value error
        ORA-06512: at "SYS.XMLTYPE", line 272
        ORA-06512: at line 1

        меня больше позабавил метод, избранный производителем для решения проблемы/бага :)

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

        • Забавно, а у меня так:

          SQL> with ol as
            2   (select--+ materialize
            3           plan_hash_value, b.hint
            4      from v$sql_plan m,
            5           xmltable('/other_xml/outline_data/hint'
            6                    passing xmltype(nvl(trim(m.OTHER_XML),'<a/>'))
            7                    columns hint varchar2(4000) path '/hint') b
            8     where length(trim(OTHER_XML))>0
            9       and rownum <= 2)
           10  select * from ol
           11  /
          
          no rows selected
          
          SQL> with ol as
            2   (select--+ inline
            3           plan_hash_value, b.hint
            4      from v$sql_plan m,
            5           xmltable('/other_xml/outline_data/hint'
            6                    passing xmltype(nvl(trim(m.OTHER_XML),'<a/>'))
            7                    columns hint varchar2(4000) path '/hint') b
            8     where length(trim(OTHER_XML))>0
            9       and rownum <= 2)
           10  select * from ol
           11  /
          
          PLAN_HASH_VALUE HINT
          --------------- ------------------------------
                702510694 IGNORE_OPTIM_EMBEDDED_HINTS
                702510694 OPTIMIZER_FEATURES_ENABLE('11.
                          2.0.4')
          
          
          2 rows selected.
          

          комментарий от Sayan Malakshinov — 07.09.2014 @ 23:38 | Ответить

        • То есть как бы и ошибка не вылазит, но и строк не выдает :)

          комментарий от Sayan Malakshinov — 07.09.2014 @ 23:38 | Ответить

        • А у тебя в nvl второй параметр пустой что ль?
          тогда ожидаемо:

          SQL> select xmltype(to_clob('')) x from dual;
          ERROR:
          ORA-06502: PL/SQL: numeric or value error
          ORA-06512: at "SYS.XMLTYPE", line 272
          ORA-06512: at line 1
          

          комментарий от Sayan Malakshinov — 07.09.2014 @ 23:40 | Ответить

          • Ааа… понял — в моем комментарии первом просто был съеден тег вордпрессом, изначально я писал именно

            xmltype(nvl(trim(m.OTHER_XML),'<a/>')) 
            

            комментарий от Sayan Malakshinov — 07.09.2014 @ 23:43 | Ответить

  2. Кстати, я нашел воркэраунд:

    with ol as
     (select--+ materialize
             plan_hash_value, b.hint
        from v$sql_plan m,
             xmltable('/other_xml/outline_data/hint' 
                      passing xmltype(nvl(trim(m.OTHER_XML),'<a/>'))
                      columns hint varchar2(4000) path '/hint')(+) b
       where length(trim(OTHER_XML))>0
         and rownum <= 2)
    select * from ol
    /
    

    комментарий от Sayan Malakshinov — 07.09.2014 @ 23:44 | Ответить

    • Спасибо, Саян, отличное решение + полезный метод внешнего соединения с xmltable!
      на 11.2.0.4 кроме того, что запрос без ошибок не возвращает строк, ещё и stopkey, похоже, не отрабатывает:

      11.2.0.4.@ SQL> with ol as
        2   (select--+ materialize
        3           plan_hash_value, b.hint
        4      from v$sql_plan m,
        5           xmltable('/other_xml/outline_data/hint'
        6                    passing xmltype(nvl(trim(m.OTHER_XML),'<a/>'))
        7                    columns hint varchar2(4000) path '/hint') b
        8     where length(trim(OTHER_XML))>0
        9       and rownum <= 2)
       10  select/*+ gather_plan_statistics*/ * from ol
       11  /
      
      no rows selected
      
      Elapsed: 00:00:18.90 -- неожиданно
      
      ------------------------------------------------------------------------------------------------------------------------------------------------------
      | Id  | Operation                              | Name                        | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   |
      ------------------------------------------------------------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT                       |                             |      1 |        |       |     3 (100)|          |      0 |00:00:18.87 |
      |   1 |  TEMP TABLE TRANSFORMATION             |                             |      1 |        |       |            |          |      0 |00:00:18.87 |
      |   2 |   LOAD AS SELECT                       |                             |      1 |        |       |            |          |      0 |00:00:18.87 |
      |*  3 |    COUNT STOPKEY                       |                             |      1 |        |       |            |          |      0 |00:00:18.87 |
      |   4 |     NESTED LOOPS                       |                             |      1 |      3 |  1242 |     1   (0)| 00:00:01 |      0 |00:00:18.87 |
      |*  5 |      FIXED TABLE FULL                  | X$KQLFXPL                   |      1 |      1 |   380 |     0   (0)|          |  25578 |00:00:04.28 | -- stopkey?
      |   6 |      VIEW                              |                             |  25578 |      3 |   102 |     1   (0)| 00:00:01 |      0 |00:00:14.61 |
      |   7 |       COLLECTION ITERATOR PICKLER FETCH| XQSEQUENCEFROMXMLTYPE       |  25578 |   8168 |       |     7   (0)| 00:00:01 |      0 |00:00:14.59 |
      |   8 |   VIEW                                 |                             |      1 |      2 |  4030 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |
      |   9 |    TABLE ACCESS FULL                   | SYS_TEMP_0FD9FC93D_AFC51769 |      1 |      2 |   828 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |
      ------------------------------------------------------------------------------------------------------------------------------------------------------

      комментарий от Игорь Усольцев — 08.09.2014 @ 08:20 | Ответить

  3. with ol as
    (
    select /*+ materialize*/
    plan_hash_value, b.hnt
    from v$sql_plan m,
    xmltable(
    ‘/other_xml/outline_data/hint’
    passing xmltype(m.OTHER_XML)
    returning sequence by ref
    columns
    hnt varchar2(4000) path ‘/hint’
    ) b
    where trim(OTHER_XML) is not null
    and rownum <= 2
    )
    select * from ol

    комментарий от Аноним — 08.09.2014 @ 22:08 | Ответить

    • Спасибо за ещё один вариант решения, доступный вместе с returning sequence by ref в 12c — признаюсь, заблуждался на предмет несовместимости XMLTYPE с материализацией:

      SQL> with ol as
        2   (select /*+ materialize*/
        3     plan_hash_value, b.hnt
        4      from v$sql_plan m,
        5           xmltable('/other_xml/outline_data/hint' passing
        6                    xmltype(m.OTHER_XML) returning sequence by ref
        7                    columns hnt varchar2(4000) path '.') b
        8     where trim(OTHER_XML) is not null
        9       and rownum <= 2)
       10  select * from ol
       11  /
      
      PLAN_HASH_VALUE HNT
      --------------- ----------------------------------------
           1120054736 IGNORE_OPTIM_EMBEDDED_HINTS
           1120054736 OPTIMIZER_FEATURES_ENABLE('12.1.0.1')
      
      --------------------------------------------------------------------------------------------------------------------
      | Id  | Operation                             | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
      --------------------------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT                      |                            |       |       |     4 (100)|          |
      |   1 |  TEMP TABLE TRANSFORMATION            |                            |       |       |         |     |
      |   2 |   LOAD AS SELECT                      |                            |       |       |         |     |
      |*  3 |    COUNT STOPKEY                      |                            |       |       |         |     |
      |   4 |     NESTED LOOPS                      |                            |     2 |    48 |     2   (0)| 00:00:01 |
      |*  5 |      FIXED TABLE FULL                 | X$KQLFXPL                  |     1 |    22 |     0   (0)|          |
      |   6 |      COLLECTION ITERATOR PICKLER FETCH| XQSEQUENCEFROMXMLTYPE      |     2 |     4 |     2   (0)| 00:00:01 |
      |   7 |   VIEW                                |                            |     2 |  4030 |     2   (0)| 00:00:01 |
      |   8 |    TABLE ACCESS FULL                  | SYS_TEMP_0FD9D669B_1130890 |     2 |    48 |     2   (0)| 00:00:01 |
      --------------------------------------------------------------------------------------------------------------------

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

      • на 12.1.0.2 materialize игнорируется и не работает ни один из приведенных вариантов :)

        комментарий от Sergey.K — 09.09.2014 @ 10:16 | Ответить

        • Благодарю, Сергей, действительно: в 12.1.0.2 варианты все работают по-прежнему исправно, только без материализации и фактически (кроме left join-а у Саяна) по одному плану с XMLTABLE EVALUATION — остаётся вероятность, что в новой версии materialize и XMLTYPE противопоказаны друг другу)

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


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