Длительная вставка из-за долгого парсинга?

[AntonS]

Длительная вставка из-за долгого парсинга?

Разработчик показал случай тормозящей вставки в одну таблицу. Время от времени такая вставка вылетала с сообщением об ошибке ORA-06550: program too large (codegen operands)

Проверил у себя на тесте, на одиночные вставки с коммитом в конце 50 тыс строк одной транзакцией было затрачено времени менее 1 мин. Тем же способом 1 млн строк вставились примерно за 40 мин.

Оказывается, вставки запускались по шаблону:

Код: Выделить всё

begin
	INSERT ... 
	<50 тыс команд INSERT>
commit;
	exception
	when others then
		dbms_output.put_line('Error: ' || sqlerrm);
	rollback;
end;
/

Во время работы вставки в БД ждала неактивная сессия с событием ожидания SQL*Net message from client

Трасировка 10046 показала, что основное время вставки даже 50 тыс строк проводят на долгом парсинге:

Код: Выделить всё

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1    371.66     372.00          0          0          0           0
Execute      1      5.24       5.30          0          0          0           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2    376.90     377.30          0          0          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 1250  

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  PGA memory operation                          246        0.16          0.17
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00
********************************************************************************

Разработчик объяснил, что такая PLSQL обертка необходима, чтобы выводить в блоке EXCEPTION через dbms_output.put_line код сообщения об ошибках вставок. На данный момент рекомендация состоит в том, чтобы не использовать PLSQL блок при большом числе вставок.

[SQL*Plus]

Посоветуйте разработчику использовать конструкцию с переменными привязки (Bind Variables).
Это сведет парсинг почти к нулю - парсинг будет выполняться один раз при первом выполнении INSERT.
Если команда выполняется часто, она всегда будет в SQL Area и останется только её выполнять.

Код: Выделить всё

INSERT INTO my_tab(a, b, c, d) 
VALUES(:1, :2, :3, :4) 
USING var_a, var_b, var_c, var_d;

Ниже приведу пример контрольного прогона на вставку 50 000 строк.

[SQL*Plus]

Пример контрольного прогона на вставку 50 000 строк:

Код: Выделить всё

SQL> SET ECHO ON;
SQL> SET SERVEROUTPUT ON;
SQL> 
SQL> SELECT BANNER_FULL FROM v$version;

BANNER_FULL
----------------------------------------------------------------------------------------
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.4.0.0.0                                                                                                                                                                                                                                          

SQL> CREATE TABLE killme_ins(id NUMBER(10), txt VARCHAR2(100));
Table KILLME_INS created.

SQL> SET TIMING ON;

SQL> BEGIN
  2     FOR k IN 1 .. 50000 LOOP
  3        EXECUTE IMMEDIATE
  4           'INSERT INTO killme_ins(id, txt) VALUES(' ||k|| ', ''Test - ' || k ||''')';
  5     END LOOP;
  6     COMMIT;
  7  END;
  8  /
PL/SQL procedure successfully completed.

Elapsed: 00:00:27.510

SQL> BEGIN
  2     FOR k IN 1 .. 50000 LOOP
  3        EXECUTE IMMEDIATE 
  4           'INSERT INTO killme_ins(id, txt) VALUES(:1, :2)'
  5           USING k, 'Test - ' || k;
  6     END LOOP;
  7     COMMIT;
  8  END;
  9  /
PL/SQL procedure successfully completed.

Elapsed: 00:00:00.602

SQL> SELECT COUNT(*) FROM killme_ins;

  COUNT(*)
----------
    100000

В этом примере получили:
За счет исключения излишнего парсинга скорость выполнения стала выше в 45 раз.

[AntonS]

Спасибо!

Если все значения, во всех INSERT-ах уникальные, будет ли бенефит от переписывания вставок на использование bind-переменных?

[SQL*Plus]

Спасибо!

Если все значения, во всех INSERT-ах уникальные, будет ли бенефит от переписывания вставок на использование bind-переменных?

На доброе здоровье!

Выигрыш будет независимо от значений данных, поскольку будет исключена фаза парсинга.
То есть в уже подготовленный к выполнению ("распаршенный") курсор будут подставляться значения,
после чего будет выполняться полученная команда INSERT.

[AntonS]

На этапе парсинга сессия активна, с событием ожидания PGA memory operation и соответствующим стеком вызова:

Parsed short stack:
ksedsts()+426 kernel service error debug dump the call stack short stack
ksdxfstk()+58 kernel service debug internal errors dump abridged os stack
ksdxcb()+872 kernel service debug internal errors ksdx callback for sosd layer signal handler
sspuser()+223 operating system dependent system process management handle SIGUSR2 for Oracle
__sighandler() (?) [partial hit for: ]
qcpiuco()+130 query compile parse interim [partial hit for: qcpi ]
qcpiins()+704 query compile parse interim [partial hit for: qcpi ]
qcpicmd()+262 query compile parse interim command
qcpidrv()+262 query compile parse interim [partial hit for: qcpi ]
qcitrans()+453 query compile language parser and semantic analyser [partial hit for: qci ]
ph1csf_parse()+805 PLSQL phase 1 (parser) [partial hit for: ph1 ]
ph1csf_match_stmt()+930 PLSQL phase 1 (parser) [partial hit for: ph1 ]
ph1lex()+603 PLSQL phase 1 (parser) lexical analyzer
plsql()+278 PLSQL infrastructure PLSQL PSD standalones [partial hit for: pls ]
ph1run()+472 PLSQL phase 1 (parser) parser executor
ph1drv()+359 PLSQL phase 1 (parser) parser driver
phpsyn()+84 PLSQL [partial hit for: ph ]
phpcmp()+972 PLSQL [partial hit for: ph ]
pcisyn()+350 PLSQL infrastructure [partial hit for: pci ]
kkxsyn()+457 kernel compile execute [partial hit for: kkx ]
prscmd()+1521 parse SQL command (syntax check)
prsdrv()+304 parse driver (?)
opiprs()+1119 oracle program interface parse
kksParseChildCursor()+527 kernel compile shared objects (cursor) parse child cursor
rpiswu2()+2004 recursive program interface switch user in recursive sql
kksLoadChild()+5287 kernel compile shared objects (cursor) load child
kxsGetRuntimeLock()+1982 kernel execution shared cursor get runtime lock
kksfbc()+23059 kernel compile shared objects (cursor) find bound cursor
kkspsc0()+3755 kernel compile shared objects (cursor) parse shared cursor
kksParseCursor()+3682 kernel compile shared objects (cursor) parse cursor
opiosq0()+2310 oracle program interface prepare to parse a sql command 0
kpooprx()+287 kernel programmatic interface oracle open, parse, and optionally execute
kpoal8()+821 kernel programmatic interface oracle V8 bundled execution
opiodr()+1202 oracle program interface oracle code request driver, route the current request
ttcpip()+1218 two task common pipe read/write
opitsk()+1900 oracle program interface two task function dispatcher
opiino()+936 oracle program interface initialize opi
opiodr()+1202 oracle program interface oracle code request driver, route the current request
opidrv()+1094 oracle program interface route current request driver, entry side into two task interface
sou2o()+165 main oracle executable entry point. reads environment var ORACLE_SPAWNED_PROCESS.
opimai_real()+422 oracle program interface main real oracle start point
ssthrdmain()+417 operating system dependent system main for every thread in a threaded oracle
main()+256 (non oracle)general c starting function
__libc_start_main()+245 (?) [partial hit for: ]

[SQL*Plus]

На этапе парсинга сессия активна, с событием ожидания PGA memory operation и соответствующим стеком вызова:
. . .

Это вы наблюдаете при парсинге блока с INSERT'ами?
Вроде такого?

Код: Выделить всё

BEGIN
   -- 10 тысяч вставок
   INSERT INTO my_tab(col1, col_2, ... col_N) VALUES(1, 'Text-1', ... '2021-01-01');
   INSERT INTO my_tab(col1, col_2, ... col_N) VALUES(2, 'Text-2', ... '2022-01-02');
   . . .
   INSERT INTO my_tab(col1, col_2, ... col_N) VALUES(10000, 'Text-10000', ... '2023-12-31');
   --
   COMMIT;
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
      ROLLBACK;
END;
/

[AntonS]

Да, это именно анонимный PLSQL блок с инсертами, а не одиночные инсерты.

[SQL*Plus]

Поскольку эти INSERT'ы не вручную набираются, можно их заменить на
EXECUTE IMMEDIATE 'INSERT ...' USING ...;

Парсинг самого PL/SQL блока должен выпониться быстро,
поскольку там будут почти сплошные EXECUTE IMMEDIATE,
для которых парсинг будет только тогда, когда начнется их выполнние.

Для самих совершенно одинаковых INSERT из EXECUTE IMMEDIATE парсинга почти не будет,
ибо будет для каждого использоваться уже подготовленной к выполнению командой (курсором?)

[SQL*Plus]

Да, это именно анонимный PLSQL блок с инсертами, а не одиночные инсерты.

Какого же размера получается текст этого самого PL/SQL-блока?
Он за пределы допустимого иногда не выходит?

Кстати, а сколько килобайт (мегабайт) допустимый размер PL/SQL-блока для используемой вами версии Oracle Database?