Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 передача xmltype из plsql в sql  [new]
_Nikotin
Member

Откуда: СПб
Сообщений: 2965
Столкнулся со странной проблемой, MOS пестит багами, но мало ли есть простой workround?
Смысл в том что передать xmltype из PLSQL в SQL оказывается быстрее через таблицу, чем простым биндом.

from varchar2 table
speed = 45.872 files per sec

from clob table
speed = 45.045 files per sec

from l_xmltype
speed = 53.191 files per sec

generate l_xmltype
speed = 5000 files per sec

insert l_xmltype into txml
speed = 1666.667 files per sec

insert directly into txml
speed = 2500 files per sec

insert + select from txml
speed = 1000 files per sec

create table tclob(c clob);
create table tvc(c varchar2(4000));
create table txml (x xmltype);

declare
  l_xml varchar2(4000) := '<x>'||lpad('<y>1</y>', 600,'<y>1</y>')||'</x>';
begin
  insert into tclob values (l_xml);
  insert into tvc values (l_xml); 
  commit;
end;
/


declare
  l_xml varchar2(4000) := '<x>'||lpad('<y>1</y>', 600,'<y>1</y>')||'</x>';
  iters number := 100;
  t1 number;
  l_xmltype xmltype;
  procedure start_test is
  begin
    t1 := dbms_utility.get_time;
  end;
  procedure report_test(i_msg varchar2 := 'report for ['||t1||']') is
    t2 number;
  begin    
    t2 := dbms_utility.get_time;
    dbms_output.put_line(i_msg);
    dbms_output.put_line('speed = ' || nvl(to_char(round(100 * iters / (nullif(t2 - t1, 0)), 3)), 'inf') || ' files per sec');
    dbms_output.put_line('');
  end;
begin
  start_test;
  for i in 1 .. iters loop
    for rec in (
      select t.* from tvc, xmltable('x/y' passing XMLType(c) columns id for ordinality, elem int path '.') t
    ) loop
      null;
    end loop;
  end loop;
  report_test('from varchar2 table');  

  start_test;
  for i in 1 .. iters loop
    for rec in (
      select t.* from tclob, xmltable('x/y' passing XMLType(c) columns id for ordinality, elem int path '.') t
    ) loop
      null;
    end loop;
  end loop;
  report_test('from clob table');

  l_xmltype := xmltype(l_xml);
  start_test;
  for i in 1 .. iters loop
    for rec in (
      select t.* from xmltable('x/y' passing l_xmltype columns id for ordinality, elem int path '.') t
    ) loop
      null;
    end loop;
  end loop;
  report_test('from l_xmltype');
  
  start_test;
  for i in 1 .. iters loop
    l_xmltype := sys.xmltype(l_xml);
  end loop;
  report_test('generate l_xmltype');
  
  execute immediate 'truncate table txml';
  start_test;
  for i in 1 .. iters loop
    l_xmltype := xmltype(l_xml);
    insert into txml values (l_xmltype);
  end loop;
  commit;
  report_test('insert l_xmltype into txml');
  
  execute immediate 'truncate table txml';
  start_test;
  for i in 1 .. iters loop
    insert into txml values (xmltype(l_xml));
  end loop;
  commit;
  report_test('insert directly into txml');
  
  execute immediate 'truncate table txml';
  start_test;
  for i in 1 .. iters loop
    insert into txml values (xmltype(l_xml));
  end loop;
  commit;
  for rid in (select rowid rid from txml) loop
    for rec in (
      select t.* from txml, xmltable('x/y' passing x columns id for ordinality, elem int path '.') t where txml.rowid = rid.rid
    ) loop
      null;
    end loop;
  end loop;
  report_test('insert + select from txml');
end;
/
15 ноя 13, 18:36    [15138746]     Ответить | Цитировать Сообщить модератору
 Re: передача xmltype из plsql в sql  [new]
-2-
Member

Откуда:
Сообщений: 15330
_Nikotin
но мало ли есть простой workround
не предавать в sql. Если вопрос в только в xmltable ради парса, то решать не вылезая из plsql через dbms_xmldom.
Скорее всего поблема в многомногомногократном переключении контекста при обращении к "объектной" plsql-переменной. У меня разбор мегабайтного эксела через xmltable занял 4.5 часа. Через dbms_xmldom 4.5 секунды, включая обновление целевых таблиц.
15 ноя 13, 18:46    [15138794]     Ответить | Цитировать Сообщить модератору
 Re: передача xmltype из plsql в sql  [new]
_Nikotin
Member

Откуда: СПб
Сообщений: 2965
тестировал на 11.2.0.3 и 11.2.0.4, от последней результаты несколько скромнее:

from varchar2 table
speed = 185.874 files per sec

from clob table
speed = 168.35 files per sec

from l_xmltype
speed = 296.736 files per sec

generate l_xmltype
speed = 869.565 files per sec

insert l_xmltype into txml
speed = 283.286 files per sec

insert directly into txml
speed = 518.135 files per sec

insert + select from txml
speed = 183.824 files per sec

insert + select from txml
speed = 253.165 files per sec
15 ноя 13, 18:50    [15138813]     Ответить | Цитировать Сообщить модератору
 Re: передача xmltype из plsql в sql  [new]
xtender
Member

Откуда: Мск
Сообщений: 5704
-2-
У меня разбор мегабайтного эксела через xmltable занял 4.5 часа.
как-то чересчур плохо... может оптимизация какая сработала?
15 ноя 13, 18:51    [15138824]     Ответить | Цитировать Сообщить модератору
 Re: передача xmltype из plsql в sql  [new]
xtender
Member

Откуда: Мск
Сообщений: 5704
_Nikotin,

так у тебя несколько несравнимые вещи же:
парсинг реально идет только в:
1.from varchar2 table
2.from clob table
3.from l_xmltype
4.insert + select from txml
15 ноя 13, 18:54    [15138836]     Ответить | Цитировать Сообщить модератору
 Re: передача xmltype из plsql в sql  [new]
_Nikotin
Member

Откуда: СПб
Сообщений: 2965
-2-,

Это была первоначальная идея. Сейчас попробую ещё с dbms_xmldom вроде бы совсем всё медленно было.
Ещё как вариант рассматривал toObject. Но:
- в 11.2 он совсем пропал из документации, остально только в packages
- про типы создаваемы при регистрации схемы оракл стал заявлять что они INTERNAL и их использовать не стоит
- сам toObject стал неожиданно работать в 10 раз медлеенее когда я вместо временной схемы сгенеренной по конкретной XML, стал использовать настоящую со всеми опциональными кусками. После ковыряния, нашёл что перформанс ухудшаеся вдвое при добавлении ветки с maxOccurs="unbounded", хотя реально в XML никаких данных там не было.

Про dbms_xmldom ещё раз погляжу.

P.S. А ни у кого не было опыта с внешними парсерами или парсингом на яве?
Задача тривиальная - полностью парсить прилетающие XML и распихивать их по таблицам.
15 ноя 13, 18:58    [15138855]     Ответить | Цитировать Сообщить модератору
 Re: передача xmltype из plsql в sql  [new]
_Nikotin
Member

Откуда: СПб
Сообщений: 2965
xtender,

всё верно, я не всё из того что в тесте сравнивал друг с другом. Просто показал что отдельно шаги довольно шустрые сами по себе.
15 ноя 13, 19:00    [15138862]     Ответить | Цитировать Сообщить модератору
 Re: передача xmltype из plsql в sql  [new]
xtender
Member

Откуда: Мск
Сообщений: 5704
Для них у меня как раз и вышли сопоставимые результаты:
from varchar2 table
speed = 32.154 files per sec
 
from clob table
speed = 32.051 files per sec
 
from l_xmltype
speed = 19.802 files per sec

insert + select from txml
speed = 33.113 files per sec
 
15 ноя 13, 19:00    [15138863]     Ответить | Цитировать Сообщить модератору
 Re: передача xmltype из plsql в sql  [new]
_Nikotin
Member

Откуда: СПб
Сообщений: 2965
А какая версия?
в 11.2.0.4 тоже вроде бы сопоставимые и достаточно высокие, но там сейчас во всю забиты CPU так что чистого теста пока не сделать. Пробовал меня OBJECT_CACHE_OPTIMAL_SIZE - не повлияло никак.
15 ноя 13, 19:04    [15138881]     Ответить | Цитировать Сообщить модератору
 Re: передача xmltype из plsql в sql  [new]
_Nikotin
Member

Откуда: СПб
Сообщений: 2965
В принципе вот это даёт приемлемую скорость - speed = 1162.791 files per sec;
Но несколько напрягает лишняя писанина.

declare
  l_xml varchar2(4000) := '<x>'||lpad('<y>1</y>', 600,'<y>1</y>')||'</x>';
  iters number := 500;
  t1 number;
  l_xmltype xmltype;
  procedure start_test is
  begin
    t1 := dbms_utility.get_time;
  end;
  procedure report_test(i_msg varchar2 := 'report for ['||t1||']') is
    t2 number;
  begin    
    t2 := dbms_utility.get_time;
    dbms_output.put_line(i_msg);
    dbms_output.put_line('speed = ' || nvl(to_char(round(100 * iters / (nullif(t2 - t1, 0)), 3)), 'inf') || ' files per sec');
    dbms_output.put_line('');
  end;
begin
  start_test;
  for i in 1 .. iters loop
    declare
      l_doc dbms_xmldom.DOMDocument;
      l_ndoc dbms_xmldom.DOMNode;
      l_docelem dbms_xmldom.DOMElement;
      l_nodelist dbms_xmldom.DOMNodelist;
      l_node dbms_xmldom.DOMNode;
      x number;
    begin
      l_xmltype := xmltype(l_xml);
      l_doc := dbms_xmldom.newDOMDocument(l_xmltype);
      l_ndoc := dbms_xmldom.makeNode(l_doc);
      l_nodelist := dbms_xslprocessor.selectNodes(l_ndoc, '/x/y');
      for i in 0 .. dbms_xmldom.getLength( l_nodelist ) - 1 loop
        l_node := dbms_xmldom.item (l_nodelist, i);
        x := DBMS_XMLDOM.getNodeValue(dbms_xmldom.getfirstchild(l_node));
      end loop;
      DBMS_XMLDOM.freeDocument(l_doc);
    end;
  end loop;
  report_test('newDOMDocument');  
end;
/
15 ноя 13, 20:54    [15139310]     Ответить | Цитировать Сообщить модератору
 Re: передача xmltype из plsql в sql  [new]
-2-
Member

Откуда:
Сообщений: 15330
_Nikotin
Но несколько напрягает лишняя писанина.
От части лишней писанины могут избавить смежные пакеты, поддерживающие xpath для DOM-хандлов с небольшой потерей производительности.
Меня больше напрягает неочевидность freeXXX. Чтобы добраться до чего-то нужно его то makeElement, то makeNode, то какой makeNodelist. При этом память занята:
? все данные от newDocument до freeDocument - не приться о free промежуточных объектов, все равно в памяти весь документ разобран.
? от make до free для каждого объекта - в многовложенных циклах стоит освобождать пройденное, память расходуется пропорционально глубине вложенности.
? непонятно как есть сборка мусора (например, после парса в памяти только индекс, а хандлы есть позиция в нем) - и париться не надо и память на уровне.
15 ноя 13, 21:20    [15139386]     Ответить | Цитировать Сообщить модератору
 Re: передача xmltype из plsql в sql  [new]
_Nikotin
Member

Откуда: СПб
Сообщений: 2965
-2-
_Nikotin
Но несколько напрягает лишняя писанина.
От части лишней писанины могут избавить смежные пакеты, поддерживающие xpath для DOM-хандлов с небольшой потерей производительности.

Вот попробовал dbms_xslprocessor.valueOf вместо dbms_xmldom.getfirstchild и получил проседание в 10 раз.
Может авто-генератор писанины написать. Схема-то есть готова. Да и дерево объектов при регистрации вполне разумное генерируется. Но с отладкой возиться придётся же. С другой стороны проталкиваение через таблицу и исользование XMLTable всего лишь в 2 раза медленее. На реальных тестах XMLTable почти то же самое даёт, а возню с лазаньем по дереву с getfirstchild и ифами страшновато представить.
15 ноя 13, 21:27    [15139406]     Ответить | Цитировать Сообщить модератору
 Re: передача xmltype из plsql в sql  [new]
xtender
Member

Откуда: Мск
Сообщений: 5704
+ just 4 lulz: 12c
from varchar2 table
speed = 192.308 files per sec

from clob table
speed = 384.615 files per sec

from l_xmltype
speed = 714.286 files per sec

generate l_xmltype
speed = 3333.333 files per sec

insert l_xmltype into txml
speed = 303.03 files per sec

insert directly into txml
speed = 434.783 files per sec

insert + select from txml
speed = 285.714 files per sec
15 ноя 13, 22:30    [15139614]     Ответить | Цитировать Сообщить модератору
 Re: передача xmltype из plsql в sql  [new]
xtender
Member

Откуда: Мск
Сообщений: 5704
_Nikotin
А какая версия?
предыдущие с тормозным "insert + select from txml" была 11.2.0.1. Проверил на 11.2.0.3 и получил приблизительно такое же соотношение как у тебя
15 ноя 13, 22:36    [15139628]     Ответить | Цитировать Сообщить модератору
 Re: передача xmltype из plsql в sql  [new]
_Nikotin
Member

Откуда: СПб
Сообщений: 2965
Попробовал с

create or replace  PROCEDURE RecursiveTree(i_node IN DBMS_XMLDOM.DOMNode)
IS
  l_nlist DBMS_XMLDOM.DOMNodeList; 
  l_node  DBMS_XMLDOM.DOMNode;          
BEGIN
  l_nlist := DBMS_XMLDOM.getchildnodes(i_node); 
  FOR i IN 0..DBMS_XMLDOM.getLength(l_nlist)-1 LOOP           
    l_node := DBMS_XMLDOM.item(l_nlist,i);         
    RecursiveTree(l_node); 
  END LOOP; 
END; 
/



По реальным данным

parse with DBMS_XMLDOM
speed = 1.004 Mb per sec
speed = 64.935 Files per sec


На тесте:


declare
  l_xml varchar2(4000) := '<x>'||lpad('<y>1</y>', 600,'<y>1</y>')||'</x>';
  iters number := 100;
  t1 number;
  l_xmltype xmltype;
  procedure start_test is
  begin
    t1 := dbms_utility.get_time;
  end;
  procedure report_test(i_msg varchar2 := 'report for ['||t1||']') is
    t2 number;
  begin    
    t2 := dbms_utility.get_time;
    dbms_output.put_line(i_msg);
    dbms_output.put_line('speed = ' || nvl(to_char(round(100 * iters / (nullif(t2 - t1, 0)), 3)), 'inf') || ' files per sec');
    dbms_output.put_line('');
  end;
begin
  start_test;
  for rec in 1 .. iters loop
    declare
      l_doc dbms_xmldom.DOMDocument;

      l_node dbms_xmldom.DOMNode;
      x number;
      l_xmltype xmltype;
    begin
      l_xmltype := xmltype(l_xml);
      l_doc := dbms_xmldom.newDOMDocument(l_xmltype);
      l_node := dbms_xmldom.makeNode(l_doc);
      RecursiveTree(l_node);
      dbms_xmldom.freeDocument(l_doc);
    end;
  end loop;
  report_test('tree with dbms_xmldom');
end;
/



tree with dbms_xmldom
speed = 454.545 files per sec

То есть примерно в 2 раза хуже чем через insert + select ...

12C - не вариант.
15 ноя 13, 23:23    [15139751]     Ответить | Цитировать Сообщить модератору
 Re: передача xmltype из plsql в sql  [new]
_Nikotin
Member

Откуда: СПб
Сообщений: 2965
По тем де реальным данным но и с точно подогнанной схемой toObect был в 5 раз быстрее. Потом от xmldom на тот момент и отказался в пользу toObject.

P.S. А точно внешние парсеры никто не привинчивал? Коллега libxml советует.
15 ноя 13, 23:26    [15139759]     Ответить | Цитировать Сообщить модератору
 Re: передача xmltype из plsql в sql  [new]
_Nikotin
Member

Откуда: СПб
Сообщений: 2965
на реальных размерах (16к) и вставка XMLType просела.
16 ноя 13, 01:32    [15140326]     Ответить | Цитировать Сообщить модератору
 Re: передача xmltype из plsql в sql  [new]
_Nikotin
Member

Откуда: СПб
Сообщений: 2965
Поменяв DDL для таблички - получил ещё более крутые результаты.

CREATE TABLE "U1"."TXMLTYPE1" 
   (  "X" "XMLTYPE"
   ) SEGMENT CREATION IMMEDIATE  
 XMLTYPE COLUMN "X" STORE AS SECUREFILE BINARY XML (
  TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192
  CACHE) ALLOW NONSCHEMA DISALLOW ANYSCHEMA;




insert into TXMLTYPE1
speed = 6.184 Mb per sec
speed = 400 Files per sec
parse from TXMLTYPE1
speed = 64.413 Mb per sec
speed = 4166.667 Files per sec
16 ноя 13, 02:33    [15140493]     Ответить | Цитировать Сообщить модератору
 Re: передача xmltype из plsql в sql  [new]
_Nikotin
Member

Откуда: СПб
Сообщений: 2965
Есть еще вопрос, как наиболее оптимально достать значения элементов по пути и часть значений их предков? Например
<x><id>1</id><chld>1</chld><chld>2</chld></x>

Достать всех chld +id parent. Я пробовал несколько вариантов, но на моих данных они просаживали перформанс в 50 раз. В плане появлялось два шага с NL к xmltable.
16 ноя 13, 10:52    [15140909]     Ответить | Цитировать Сообщить модератору
 Re: передача xmltype из plsql в sql  [new]
_Nikotin
Member

Откуда: СПб
Сообщений: 2965
Повертев разные варианты с двумя for + return, c parent::node() итп - на моих тестах давали максимум 300 попугаев.
Идеальныа решением оказался join c ещё одним XMLtable, он дал 8000 попугаев.

speed = 14285.714 Files per sec <-- отдельно "parent"
speed = 16666.667 Files per sec <-- отдельно "child"
speed = 327.869 Files per sec <-- parent::node()
speed = 8333.333 Files per sec <-- два xmltable

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                       |  8351K|    16G| 29831   (1)| 00:05:58 |
|   1 |  NESTED LOOPS                          |                       |  8351K|    16G| 29831   (1)| 00:05:58 |
|   2 |   TABLE ACCESS FULL                    | TXMLTYPE3             |  1022 |  1998K|    68   (0)| 00:00:01 |
|   3 |   VIEW                                 |                       |  8168 |   582K|    29   (0)| 00:00:01 |
|   4 |    COUNT                               |                       |       |       |            |          |
|   5 |     COLLECTION ITERATOR XMLSEQ FETCH   | XQSEQUENCEFROMXMLTYPE |  8168 | 16336 |    29   (0)| 00:00:01 |
|   6 |      SORT AGGREGATE                    |                       |     1 |     2 |            |          |
|   7 |       COLLECTION ITERATOR PICKLER FETCH| XQSEQUENCEFROMXMLTYPE |  8168 | 16336 |    29   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------
 
Note
-----
   - dynamic sampling used for this statement (level=2)
   - Unoptimized XML construct detected (enable XMLOptimizationCheck for more information)


Через два join

-----------------------------------------------------------------------------------
| Id  | Operation             | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |           |    68G|   129T|   243M  (1)|811:40:34 |
|   1 |  NESTED LOOPS         |           |    68G|   129T|   243M  (1)|811:40:34 |
|   2 |   NESTED LOOPS        |           |  8351K|    15G| 29852   (1)| 00:05:59 |
|   3 |    TABLE ACCESS FULL  | TXMLTYPE3 |  1022 |  1998K|    68   (0)| 00:00:01 |
|   4 |    VIEW               |           |  8168 |   366K|    29   (0)| 00:00:01 |
|   5 |     COUNT             |           |       |       |            |          |
|   6 |      XPATH EVALUATION |           |       |       |            |          |
|   7 |   VIEW                |           |  8168 |   342K|    29   (0)| 00:00:01 |
|   8 |    COUNT              |           |       |       |            |          |
|   9 |     XPATH EVALUATION  |           |       |       |            |          |
-----------------------------------------------------------------------------------
 
Note
-----
   - dynamic sampling used for this statement (level=2)
   - Unoptimized XML construct detected (enable XMLOptimizationCheck for more information)
16 ноя 13, 23:24    [15142293]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить