Skip to Main Content
  • Questions
  • DB Link: ALTER SESSION ENABLE PARALLEL DML

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question.

Asked: May 30, 2017 - 11:57 am UTC

Last updated: June 01, 2017 - 2:52 am UTC

Version: 11.2

Viewed 1000+ times

You Asked

Hi Chris/Connonr,

We have two databases namely Primary & Secondary.

On Primary, There is scheduled Job which select data from Primary and Insert it into Secondary then inserted records gets delete from Primary.

here are the steps written in procedure -
1. EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML'

2.INSERT /*+ APPEND PARALLEL(tb_test_transaction, 8) NOLOGGING */ INTO tb_test_transaction @remote_link
SELECT /*+ PARALLEL(tb_test_transaction, 8) */ * FROM tb_test_transaction WHERE ROWID IN (SELECT row_id FROM tb_archive_staging);

3. DELETE /*+ PARALLEL(tb_test_transaction, 8) */ FROM tb_test_transaction WHERE ROWID IN (SELECT row_id FROM tb_archive_staging);

My query here is Since this job gets invoked in Primary, the ALTER SESSION ENABLE PARALLEL DML will be applicable only for the Primary DB queries like SELECT /*+ PARALLEL(tb_test_transaction, 8) */ * FROM tb_test_transaction WHERE ROWID IN (SELECT row_id FROM tb_archive_staging);

I want to get the benefits of PARALLELISM on remote database using below INSERT-
INSERT /*+ APPEND PARALLEL(tb_test_transaction, 8) NOLOGGING */ INTO tb_test_transaction @remote_link;

Can you please help how to alter PARALLEL DML session on Secondary DB.

and Connor said...

Things are generally going to be serial across a dblink. So you need multiple sessions, so each can use a db link to get a partnering remote session.

Which sounds like a good use case for DBMS_PARALLEL_EXECUTE. Check the link below for a full example, which you can tailor for your needs.

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4248554900346593542

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

CHUNKS over specific set of table data

A reader, May 31, 2017 - 12:54 pm UTC

Thanks Connor for sharing examples on DBMS_PARALLEL_EXECUTE.

I tried creating sample script to create chunk by rowid but problem is dbms_parallel_execute.create_chunks_by_rowid create thread of entire table data.

Here i have a table tb_x which i shaving 10000k records, out of which i need to only move 5000k records (using WHERE date condition e.g. SELECT * FROM tb_x where date between ? and ?).

can you please suggest how to achieve this.
Connor McDonald
June 01, 2017 - 2:52 am UTC

Here's an example where I process rows in monthly batches by using 'create_chunks_by_sql'.

SQL> create table t as select * From dba_objects;

Table created.

SQL>
SQL> declare
  2    l_update_statement constant varchar2(1000)
  3        := 'update t set object_id = -object_id '||
  4           'where created >= add_months(date ''2017-01-01'',:start_id) '||
  5           'and   created <  add_months(date ''2017-01-01'',:end_id)';
  6
  7    l_task_name   constant varchar2(20) := 'task1';
  8  begin
  9    begin
 10      dbms_parallel_execute.drop_task(l_task_name);
 11    exception
 12      when others then null;
 13    end;
 14
 15    dbms_parallel_execute.create_task(l_task_name);
 16
 17    dbms_parallel_execute.
 18    create_chunks_by_sql (
 19        task_name=>l_task_name
 20       , sql_stmt=>'select rownum-1,rownum from dual connect by level <= 8'
 21       , by_rowid=>false
 22    );
 23
 24   dbms_parallel_execute.
 25       run_task(task_name=>l_task_name
 26               , sql_stmt=> l_update_statement
 27               , language_flag=>dbms_sql.native
 28               , parallel_level=>3
 29                );
 30  end;
 31  /

PL/SQL procedure successfully completed.



More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library