Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
 проблемка - pipelined с remote внутри, direct-path insert  [new]
bdsm_sql
Member

Откуда:
Сообщений: 911
сорри если тема уже поднималась.
имею запрос вида
insert /*+ append */ into x
select * from table(pip)

где внутри pip уже запрос вида
select .. from view@remote where col in (..,..)

в общем-то, ситуация почти аналогичная этой - http://jonathanlewis.wordpress.com/2010/10/07/distributed-pipelines/

но все валится на ora-12840
автор
ORA-12840: cannot access a remote table after parallel/insert direct load txn.


из комментов к этой же статье по данной ошибке пробую сделать pipeline в автономной транзакции но на первом же pipe валится на
автор
ORA-06519: active autonomous transaction detected and rolled back


и не важно, где стоит коммит в pipelined и стоит ли он там вообще. в принципе, до сего дня ни разу автономки в pipelined не использовал, может это вообще нельзя, склоняюсь к этому..

в общем, кто может что-то посоветовать - буду рад

---------------------------------------
мистер Петя
20 сен 12, 15:12    [13196752]     Ответить | Цитировать Сообщить модератору
 Re: проблемка - pipelined с remote внутри, direct-path insert  [new]
_Nikotin
Member

Откуда: СПб
Сообщений: 2954
Выложи минимальный тесткейс. Возможно проблема с декларацией переменных через линк, что тоже начинает распределенную транзакцию.
20 сен 12, 15:20    [13196832]     Ответить | Цитировать Сообщить модератору
 Re: проблемка - pipelined с remote внутри, direct-path insert  [new]
bdsm_sql
Member

Откуда:
Сообщений: 911
_Nikotin
Выложи минимальный тесткейс. Возможно проблема с декларацией переменных через линк, что тоже начинает распределенную транзакцию.

не, там все просто.. распределенную транзакцию начинает сам select. почему - хз.

Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 
Connected as xxx
 
SQL> drop database link pip_test_link
  2  /
 
Database link dropped
 
SQL> drop package pip_remote_test
  2  /
 
Package dropped
 
SQL> drop table pip_test_tab
  2  /
 
Table dropped
 
SQL> create database link pip_test_link connect to ... identified by ... using '...:1521/orcl'
  2  /
 
Database link created
 
SQL> 
SQL> create or replace package pip_remote_test is
  2  type rec_t is record(dummy varchar2(4000));
  3  type tab_t is table of rec_t;
  4  function pip return tab_t pipelined;
  5  end;
  6  /
 
Package created
 
SQL> 
SQL> create or replace package body pip_remote_test is
  2  function pip return tab_t pipelined is
  3    v_cur sys_refcursor;
  4    v_row rec_t;
  5  begin
  6    open v_cur for 'select * from dual@pip_test_link';
  7    fetch v_cur into v_row;
  8    pipe row(v_row);
  9    close v_cur;
 10  end;
 11  end;
 12  /
 
Package body created
 
SQL> select * from table(pip_remote_test.pip)
  2  /
 
DUMMY
--------------------------------------------------------------------------------
X
 
SQL> create table pip_test_tab(dummy varchar2(4000))
  2  /
 
Table created
 
SQL> insert /*+ append */ into pip_test_tab select * from table(pip_remote_test.pip)
  2  /
 
insert /*+ append */ into pip_test_tab select * from table(pip_remote_test.pip)
 
ORA-12840: cannot access a remote table after parallel/insert direct load txn
ORA-06512: at "DAVID.PIP_REMOTE_TEST", line 6
 
SQL> 
SQL> create or replace package body pip_remote_test is
  2  function pip return tab_t pipelined is
  3    pragma autonomous_transaction;
  4    v_cur sys_refcursor;
  5    v_row rec_t;
  6  begin
  7    open v_cur for 'select * from dual@pip_test_link';
  8    fetch v_cur into v_row;
  9    pipe row(v_row);
 10    close v_cur;
 11  end;
 12  end;
 13  /
 
Package body created
 
SQL> insert /*+ append */ into pip_test_tab select * from table(pip_remote_test.pip)
  2  /
 
insert /*+ append */ into pip_test_tab select * from table(pip_remote_test.pip)
 
ORA-06519: active autonomous transaction detected and rolled back
ORA-06512: at "DAVID.PIP_REMOTE_TEST", line 9
 
SQL> 
SQL> create or replace package body pip_remote_test is
  2  function pip return tab_t pipelined is
  3    pragma autonomous_transaction;
  4    v_cur sys_refcursor;
  5    v_row rec_t;
  6  begin
  7    open v_cur for 'select * from dual@pip_test_link';
  8    fetch v_cur into v_row;
  9    commit;
 10    pipe row(v_row);
 11    close v_cur;
 12  end;
 13  end;
 14  /
 
Package body created
 
SQL> insert /*+ append */ into pip_test_tab select * from table(pip_remote_test.pip)
  2  /
 
1 row inserted
 
SQL> rem в общем, ситуация более-менее ясна.. но в общем случае pipe row идет внутри цикла с fetch
SQL> rem и коммит там делать совершенно неохота (snapshot too old). если же делать commit до открытия курсора
SQL> rem - ошибка 06519 не исчезает
SQL> 
20 сен 12, 17:43    [13197868]     Ответить | Цитировать Сообщить модератору
 Re: проблемка - pipelined с remote внутри, direct-path insert  [new]
bdsm_sql
Member

Откуда:
Сообщений: 911
SQL> rem то есть
SQL> 
SQL> create or replace package body pip_remote_test is
  2  function pip return tab_t pipelined is
  3    pragma autonomous_transaction;
  4    v_cur sys_refcursor;
  5    v_row rec_t;
  6  begin
  7    commit;
  8    open v_cur for 'select * from dual@pip_test_link';
  9    fetch v_cur into v_row;
 10    pipe row(v_row);
 11    close v_cur;
 12  end;
 13  end;
 14  /
 
Package body created
 
SQL> insert /*+ append */ into pip_test_tab select * from table(pip_remote_test.pip)
  2  /
 
insert /*+ append */ into pip_test_tab select * from table(pip_remote_test.pip)
 
ORA-06519: active autonomous transaction detected and rolled back
ORA-06512: at "DAVID.PIP_REMOTE_TEST", line 10
 
SQL> 
SQL> create or replace package body pip_remote_test is
  2  function pip return tab_t pipelined is
  3    pragma autonomous_transaction;
  4    v_cur sys_refcursor;
  5    v_row rec_t;
  6  begin
  7    open v_cur for 'select * from dual@pip_test_link';
  8    commit;
  9    fetch v_cur into v_row;
 10    pipe row(v_row);
 11    close v_cur;
 12  end;
 13  end;
 14  /
 
Package body created
 
SQL> insert /*+ append */ into pip_test_tab select * from table(pip_remote_test.pip)
  2  /
 
1 row inserted
 
SQL> rollback
  2  /
 
Rollback complete
 
SQL> 
SQL> create or replace package body pip_remote_test is
  2  function pip return tab_t pipelined is
  3    pragma autonomous_transaction;
  4    v_cur sys_refcursor;
  5    v_row rec_t;
  6  begin
  7    open v_cur for 'select * from dual@pip_test_link';
  8    fetch v_cur into v_row;
  9    commit;
 10    pipe row(v_row);
 11    close v_cur;
 12  end;
 13  end;
 14  
 15  /
 
Package body created
 
SQL> insert /*+ append */ into pip_test_tab select * from table(pip_remote_test.pip)
  2  /
 
1 row inserted
 
SQL> rollback
  2  /
 
Rollback complete
 
SQL> 
SQL> create or replace package body pip_remote_test is
  2  function pip return tab_t pipelined is
  3    pragma autonomous_transaction;
  4    v_cur sys_refcursor;
  5    v_row rec_t;
  6  begin
  7    open v_cur for 'select * from dual@pip_test_link';
  8    fetch v_cur into v_row;
  9    pipe row(v_row);
 10    commit;
 11    close v_cur;
 12  end;
 13  end;
 14  /
 
Package body created
 
SQL> insert /*+ append */ into pip_test_tab select * from table(pip_remote_test.pip)
  2  /
 
insert /*+ append */ into pip_test_tab select * from table(pip_remote_test.pip)
 
ORA-06519: active autonomous transaction detected and rolled back
ORA-06512: at "DAVID.PIP_REMOTE_TEST", line 9
 
SQL> 


получается, ошибки нет только если я завершаю транзакцию после открытия курсора но до pipe. но понятное дело, что делать коммит внутри фетча курсора, равно как и чтение абсолютно всех данных в память перед pipe - это не комильфо
20 сен 12, 17:52    [13197939]     Ответить | Цитировать Сообщить модератору
 Re: проблемка - pipelined с remote внутри, direct-path insert  [new]
-2-
Member

Откуда:
Сообщений: 12860
bdsm_sql,

коммит и распределенный фетч несовместимы. либо отказывайся от распараллеливания dml, тем более, что пипелац все равно не parallel_enable. Либо, если объем допускает, сохраняй в массив и закрывай дблинк, потом уже pipe row.
20 сен 12, 18:15    [13198062]     Ответить | Цитировать Сообщить модератору
 Re: проблемка - pipelined с remote внутри, direct-path insert  [new]
KoTTT
Member

Откуда: Екб
Сообщений: 1432
bdsm_sql
не, там все просто.. распределенную транзакцию начинает сам select. почему - хз.

Так всегда было.
http://www.jlcomp.demon.co.uk/faq/dblink_commit.html
20 сен 12, 18:24    [13198109]     Ответить | Цитировать Сообщить модератору
 Re: проблемка - pipelined с remote внутри, direct-path insert  [new]
bdsm_sql
Member

Откуда:
Сообщений: 911
KoTTT
bdsm_sql
не, там все просто.. распределенную транзакцию начинает сам select. почему - хз.

Так всегда было.
http://www.jlcomp.demon.co.uk/faq/dblink_commit.html

ключевое слово тут "распределенная".. то что просто транзакцию он начинает, я в курсе..
хотя наверное тут все же дело не в распределенной, а просто на незакрытую транзакцию ругается.
20 сен 12, 19:23    [13198324]     Ответить | Цитировать Сообщить модератору
 Re: проблемка - pipelined с remote внутри, direct-path insert  [new]
bdsm_sql
Member

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

коммит и распределенный фетч несовместимы. либо отказывайся от распараллеливания dml, тем более, что пипелац все равно не parallel_enable. Либо, если объем допускает, сохраняй в массив и закрывай дблинк, потом уже pipe row.

печально. попробую с bulk без лимита - может не ляжет..
непонятно мне, почему валится на pipe, т.е. почему именно к моменту pipe транзакция должна быть завершена
20 сен 12, 19:25    [13198332]     Ответить | Цитировать Сообщить модератору
 Re: проблемка - pipelined с remote внутри, direct-path insert  [new]
bdsm_sql
Member

Откуда:
Сообщений: 911
а маюсь не параллели ради а просто ради direct-path insert. даже без параллели существенно быстрее на больших объемах.
20 сен 12, 19:26    [13198337]     Ответить | Цитировать Сообщить модератору
 Re: проблемка - pipelined с remote внутри, direct-path insert  [new]
KoTTT
Member

Откуда: Екб
Сообщений: 1432
Несколько раз сталкивались с аналогичными задачами. И в вариантах с pipeline, direct-path и т.п. прелестями.
Всех приключений уже не вспомню, но с каких-то пор делаем "в лоб": выгрузка в stage-таблицу на локальном хосте, затем insert /*+append*/ select from table@link уже по линку.
20 сен 12, 19:31    [13198352]     Ответить | Цитировать Сообщить модератору
 Re: проблемка - pipelined с remote внутри, direct-path insert  [new]
bdsm_sql
Member

Откуда:
Сообщений: 911
KoTTT
Несколько раз сталкивались с аналогичными задачами. И в вариантах с pipeline, direct-path и т.п. прелестями.
Всех приключений уже не вспомню, но с каких-то пор делаем "в лоб": выгрузка в stage-таблицу на локальном хосте, затем insert /*+append*/ select from table@link уже по линку.

я бы давно уже сделал через цикл обычных insert /*+ append */ select .. / commit, и даже сделал, но там такой механизм который в OWB не реализуешь без геммороя, PL/SQL ненагляден (заказчик фанат наглядности), а pipeline с этим механизмом внутри вроде как компромиссное решение.. осталось довести до ума - по ходу выбрать, без direct-path или если позволит память то читать выборки из курсоров целиком в массив
20 сен 12, 19:38    [13198374]     Ответить | Цитировать Сообщить модератору
 Re: проблемка - pipelined с remote внутри, direct-path insert  [new]
xtender
Member

Откуда: Мск
Сообщений: 4250
KoTTT
bdsm_sql
не, там все просто.. распределенную транзакцию начинает сам select. почему - хз.

Так всегда было.
http://www.jlcomp.demon.co.uk/faq/dblink_commit.html
инсерт открывает локальную , и как раз в процедуре другая с дблинком = уже распределенная
20 сен 12, 20:40    [13198583]     Ответить | Цитировать Сообщить модератору
 Re: проблемка - pipelined с remote внутри, direct-path insert  [new]
bdsm_sql
Member

Откуда:
Сообщений: 911
xtender
KoTTT
пропущено...

Так всегда было.
http://www.jlcomp.demon.co.uk/faq/dblink_commit.html
инсерт открывает локальную , и как раз в процедуре другая с дблинком = уже распределенная

автор
A distributed transaction includes one or more statements that, individually or as a group, update data on two or more distinct nodes of a distributed database

инсерт из таблицы@dblink - это не распределенная транзакция
в моем случае это вообще автономка с селектом из таблица@dblink - тоже не распределенная, а просто транзакция
20 сен 12, 20:56    [13198644]     Ответить | Цитировать Сообщить модератору
 Re: проблемка - pipelined с remote внутри, direct-path insert  [new]
xtender
Member

Откуда: Мск
Сообщений: 4250
bdsm_sql
инсерт из таблицы@dblink - это не распределенная транзакция
речь про это и не идет. Я же сказал - "в процедуре", именно попытка в ней открытия удаленной при открытой локальной с append и наворачивается.
20 сен 12, 21:12    [13198695]     Ответить | Цитировать Сообщить модератору
 Re: проблемка - pipelined с remote внутри, direct-path insert  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 8458
bdsm_sql
инсерт из таблицы@dblink - это не распределенная транзакция


А что тогда в твоем понимании распределенная транзакция?

SY.
20 сен 12, 21:14    [13198699]     Ответить | Цитировать Сообщить модератору
 Re: проблемка - pipelined с remote внутри, direct-path insert  [new]
bdsm_sql
Member

Откуда:
Сообщений: 911
xtender
bdsm_sql
инсерт из таблицы@dblink - это не распределенная транзакция
речь про это и не идет. Я же сказал - "в процедуре", именно попытка в ней открытия удаленной при открытой локальной с append и наворачивается.

ну в процедуре-то автономка.. по идее должна быть отдельно от инсерта и ничего про него не знать
20 сен 12, 21:33    [13198766]     Ответить | Цитировать Сообщить модератору
 Re: проблемка - pipelined с remote внутри, direct-path insert  [new]
bdsm_sql
Member

Откуда:
Сообщений: 911
SY
bdsm_sql
инсерт из таблицы@dblink - это не распределенная транзакция


А что тогда в твоем понимании распределенная транзакция?

SY.

а я в посте выше доку процитировал
автор
A distributed transaction includes one or more statements that, individually or as a group, update data on two or more distinct nodes of a distributed database


для меня распределенная транзакция это транзакция в которой меняются данные на двух и более бд
20 сен 12, 21:33    [13198770]     Ответить | Цитировать Сообщить модератору
 Re: проблемка - pipelined с remote внутри, direct-path insert  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 8458
bdsm_sql,

Ну это явный баг в доке. Не update data, a access data. И косвенно это следует из определения distributed statement: A statement that accesses data on two or more distinct nodes/instances of a distributed database. A remote statement accesses data on one remote node of a distributed database.
И это есть ответ на твой "распределенную транзакцию начинает сам select. почему - хз".

SY.
20 сен 12, 21:59    [13198843]     Ответить | Цитировать Сообщить модератору
 Re: проблемка - pipelined с remote внутри, direct-path insert  [new]
bdsm_sql
Member

Откуда:
Сообщений: 911
SY
bdsm_sql,

Ну это явный баг в доке. Не update data, a access data. И косвенно это следует из определения distributed statement: A statement that accesses data on two or more distinct nodes/instances of a distributed database. A remote statement accesses data on one remote node of a distributed database.
И это есть ответ на твой "распределенную транзакцию начинает сам select. почему - хз".

SY.

не знаю.. всегда казалось что remote statement и distributed transaction это из разных опер..
distributed transaction требует одновременного commit на всех распределенных бд участвующих в транзакции. в insert into a select from b@remote требуется коммит только на базе где таблица a.
в случае с select * from a@remote, rollback сегмент ведь создается на базе, на которой выполняется селект, не?
20 сен 12, 22:25    [13198912]     Ответить | Цитировать Сообщить модератору
 Re: проблемка - pipelined с remote внутри, direct-path insert  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 8458
bdsm_sql
не знаю.. всегда казалось что remote statement и distributed transaction это из разных опер..
distributed transaction требует одновременного commit на всех распределенных бд участвующих в транзакции.


And what does it mean "требует одновременного commit на всех распределенных бд участвующих в транзакции"? It sounds you thing each node involved in distributed transaction must issue commit. Not true. Commit is issued once on ANY node involved in distributed transaction. ANY transation (distibuted, remote, local) is committed as a whole and to ensure that distributed transations use two-phase commit mechanism but it is transparent to code. Code simply issues commit once.


bdsm_sql
в insert into a select from b@remote требуется коммит только на базе где таблица a.
в случае с select * from a@remote, rollback сегмент ведь создается на базе, на которой выполняется селект, не?


Not true:

SQL> connect scott@orcl
Enter password: *****
Connected.
SQL> create or replace
  2    procedure do_commit
  3      is
  4      begin
  5          commit;
  6  end;
  7  /

Procedure created.

SQL> connect scott@sol10
Enter password: *****
Connected.
SQL> drop table emp1 purge
  2  /

Table dropped.

SQL> create table emp1
  2    as
  3      select  *
  4        from  emp
  5        where 1 = 2
  6  /

Table created.

SQL> insert
  2    into emp1
  3    select  *
  4      from  emp
  5  /

14 rows created.

SQL> exec do_commit@orcl

PL/SQL procedure successfully completed.

SQL> rollback
  2  /

Rollback complete.

SQL> select  count(*)
  2    from  emp1
  3  /

COUNT(*)
--------
      14

SQL> 


So which node committed transaction?

SY.
20 сен 12, 23:00    [13198996]     Ответить | Цитировать Сообщить модератору
 Re: проблемка - pipelined с remote внутри, direct-path insert  [new]
xtender
Member

Откуда: Мск
Сообщений: 4250
кстати, имхо, интересная деталь: при append или append_values транзакция сначала открывается инсертом и только потом функция обламывается, но без них открывается транзакция обращением к дблинку.
Тестовые функции:
+
create or replace function test_pipelined return number_table pipelined is
  cur sys_refcursor;

  procedure tlog(p varchar2) is
    pragma autonomous_transaction;
  begin
    insert into tr_log select p descr,t.* from v$transaction t;
    commit;
  end;

begin
  tlog('pipe-append: start');
  open cur for select object_id 
               from all_objects@my_loopback;
  tlog('pipe-append: finish');
  pipe row(1);
  close cur;
end;
/
create or replace function test_pipelined2 return number is
  cur sys_refcursor;
  procedure tlog(p varchar2) is
    pragma autonomous_transaction;
  begin
    insert into tr_log select p descr,t.* from v$transaction t;
    commit;
  end;
begin
  tlog('+append: start');
  open cur for select object_id 
               from all_objects@my_loopback;
  tlog('+append: finish');
  return 1;
end;
/
-- тестовые запросы:
insert /* without append */ into t_test_pipelined
select * from table(test_pipelined);
insert /*+ append */ into t_test_pipelined
select * from table(test_pipelined);

insert /* without append_values */ into t_test_pipelined values(test_pipelined2);
insert /*+ append_values */ into t_test_pipelined values(test_pipelined2);
21 сен 12, 00:57    [13199337]     Ответить | Цитировать Сообщить модератору
 Re: проблемка - pipelined с remote внутри, direct-path insert  [new]
bdsm_sql
Member

Откуда:
Сообщений: 911
SY,

не очень понял английский но если ты считаешь будто я думаю что распределенные транзакции надо коммитить самому на всех базах то это не так. понятно что я имел в виду двух фазовый коммит. просто для инсерта с дблинка он имхо не нужен..

да, и после инсерта с дблинк транзакция не появляется в DBA_2PC_PENDING.

однако обращение к типам с удаленной бд вроде как может начать распределенную транзакцию.

пример замечательный но не по теме.

под роллбэк-сегментом я имел в виду
автор
When Oracle performs a distributed SQL statement Oracle reserves an entry in the rollback segment area for the two-phase commit processing
по этой ссылке http://www.jlcomp.demon.co.uk/faq/dblink_commit.html.
21 сен 12, 07:34    [13199539]     Ответить | Цитировать Сообщить модератору
 Re: проблемка - pipelined с remote внутри, direct-path insert  [new]
bdsm_sql
Member

Откуда:
Сообщений: 911
ну и кстати, цитата из доки опять же
автор
A transaction containing a direct-path INSERT statement cannot be or become distributed

однако insert /*+ append */ into x select * from y@remote успешно работает

ну и кайта могу процитировать напоследок..
автор
In the following, we do not have a distributed transaction when we just insert /*+ append */ into local_table select * from remote@table; - we just have a single site transaction.


http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:549493700346053658
21 сен 12, 07:47    [13199564]     Ответить | Цитировать Сообщить модератору
 Re: проблемка - pipelined с remote внутри, direct-path insert  [new]
-2-
Member

Откуда:
Сообщений: 12860
bdsm_sql
однако обращение к типам с удаленной бд вроде как может начать распределенную транзакцию.
любое обращение к оракловому дблинку начинает распределенную транзакцию. но можешь сделать гетерогенное соединение, в котором можно указать тип "распределенности".

bdsm_sql
однако insert /*+ append */ into x select * from y@remote успешно работает
где x plsql-переменная?
21 сен 12, 08:20    [13199618]     Ответить | Цитировать Сообщить модератору
 Re: проблемка - pipelined с remote внутри, direct-path insert  [new]
xtender
Member

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

автор
In the following, we do not have a distributed transaction when we just insert /*+ append */ into local_table select * from remote@table; - we just have a single site transaction.
это неверно, и легко проверяется:
create table xt_test as select * from all_users where 1=0;
insert into xt_test select * from all_users@remote;
select * from v$lock l where l.type='DX' and sid=sys_context('userenv','sid');
rollback;
insert/*+ append */ into xt_test select * from all_users@remote;
select * from v$lock l where l.type='DX' and sid=sys_context('userenv','sid');
rollback;
Оба инсерта покажут DX'ы
21 сен 12, 09:09    [13199743]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
Все форумы / Oracle Ответить