Почему при вызове процедуры по db-link не работает параллельная вставка?

Question

Существует процедура в пакете, которая вставляет данные в таблицу используя подсказки оптимизатору:

INSERT /*+ PARALLEL(8) APPEND ENABLE_PARALLEL_DML */ INTO TBL (field_1, field_2, ...) 
  SELECT VAL_1, VAL_2, ... FROM ...

При вызове процедуры вставка идет прямая в параллельном режиме. В плане это видно:

|   0 | INSERT STATEMENT
|   1 |  PX COORDINATOR
|   2 |   PX SEND QC (RANDOM)
|   3 |    LOAD AS SELECT (HYBRID TSM/HWMB)
...    
Note
-----
   - dynamic statistics used: dynamic sampling (level=5)
   - Degree of Parallelism is 8 because of hint

Однако, если вызвать процедуру в дугой БД по db-link, то получаем непараллельную вставку с параллельным выполнением запроса. В плане:

|   0 | INSERT STATEMENT
|   1 |  LOAD TABLE CONVENTIONAL
...
|  15 |   PX COORDINATOR
|  16 |    PX SEND QC (RANDOM)
...
Note
-----
   - PDML is disabled in current session
   - Direct Load disabled because remote table used
   - parallel query server generated this plan using optimizer hints from coordinator

Получается, что оптимизатор "думает" не верно.

Как заставить запрос выполняться в режиме прямой параллельной вставки (direct-path parallel insert), если вызов идет по db-likn?

И где можно почитать, как это работает?

Answer 1

Как сказано в подглаве Direct-path INSERT документации:

Direct-path INSERT is subject to a number of restrictions. If any of these restrictions is violated, then Oracle Database executes conventional INSERT serially without returning any message, unless otherwise noted:
[...]

A transaction containing a direct-path INSERT statement cannot be or become distributed.

Запуская процедуру на удалённой БД будет открыта распределённая транзакция (distributed transaction), в этом случае прямая вставка невозможна.

Возможно запустить процедуру в JOB на удалённой БД, в этом случае процедура будет запущена локально в нераспределённой транзакции и её поведение не изменится.

Воспроизводимый пример. Тестовые данные и процедура:

create table t1 (id, val) as
    select rownum, 'value '||rownum from dual connect by level<=1e5
/
create table t2 as select * from t1 where 1=0
/
create or replace procedure loadtab is
begin
    insert /*+ parallel(8) append enable_parallel_dml */ into t2 
        select * from t1;
    dbms_output.put_line (sql%rowcount||' rows loaded in t2.'); 
end;
/

Запуск одноразового задания на удалённой БД:

create database link loopback using 'localhost/pdb1';

begin
    dbms_scheduler.create_job@loopback (  
        job_name      =>  'load_tab_once',  
        job_type      =>  'plsql_block',  
        job_action    =>  'begin loadtab; end;',  
        start_date    =>  sysdate,  
        enabled       =>  true,  
        auto_drop     =>  true,  
        comments      =>  'one time job to load table');
    dbms_output.put_line ('load started.'); 
end;
/
load started.

Контролировать завершение можно следующими запросами. Из user_scheduler_jobs после завершения задания запрос ничего не вернёт, т.к. задание будет удалено, а в user_scheduler_job_run_details логи задания определённое время сохранятся:

select * from user_scheduler_jobs@loopback
where job_name = 'LOAD_TAB_ONCE';

no rows selected

select job_name, status, run_duration, session_id, errors, output 
from  user_scheduler_job_run_details@loopback
where job_name = 'LOAD_TAB_ONCE'
order by log_date desc;

JOB_NAME      STATUS    RUN_DURATION        SESSION_ID ERRORS OUTPUT                   
------------- --------- ------------------- ---------- ------ -------------------------
LOAD_TAB_ONCE SUCCEEDED +00 00:00:01.000000 272,45091         100000 rows loaded in t2.

Answer 2

Свой вариант решения.

Необходимо создать новую хранимую процедуру, которая вызывает пакет и сделать ее автономной.

И при вызове новой процедуры мы получаем:

Инициируется распределенная транзакция на удаленной БД.
Вызываем по db-link хранимую процедуру.
На удаленной БД начинает автономная транзакция без ограничений на параллельную работу.
Все работает.

БЛОГ НА HUSL

Почему при вызове процедуры по db-link не работает параллельная вставка?

Ответы (2 шт):