DBMS_PARALLEL_EXECUTE

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

DBMS_PARALLEL_EXECUTE

Postby JDSal » Fri May 10, 2013 3:27 pm

Hello All, I am new and have a question is using an insert possible without a start id or end id? Also I want to be able to retrieve a specified amount of records. If so I can't find anything to help me complete an insert using a
parallel process
Code: Select all
DECLARE
l_sql_stmt VARCHAR2(32767);
BEGIN
l_sql_stmt := 'Insert into table1(select * from table2)
DBMS_PARALLEL_EXECUTE.run_task(task_name => 'TEST_TASK',
sql_stmt => l_sql_stmt,
language_flag => DBMS_SQL.NATIVE,
PARALLEL_LEVEL => 10);
END;
JDSal
Member
 
Posts: 14
Joined: Fri May 10, 2013 2:37 pm

Re: DBMS_PARALLEL_EXECUTE

Postby Tim... » Fri May 10, 2013 3:53 pm

Hi.

DBMS_PARALLEL_EXECUTE provides a mechanism to solve specific issues. It should not be your first option. It is more efficient to use parallel DML. From what you are saying, it would appear to me a better option for you would be to do this.

Code: Select all
alter session enable parallel dml;
insert /*+ append parallel (table1) */ into table1 (select /*+ parallel (table2) */ * from table2);
commit;
alter session disable parallel dml;


This will query the data in parallel and insert the data in parallel.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17933
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: DBMS_PARALLEL_EXECUTE

Postby JDSal » Fri May 10, 2013 4:10 pm

Thanks Tim,
I will try that, I am looking to get some performance gain. I tried a cursor but to no avail. Then while researching I cam across doing a parallel execution.

thanks again.
JDSal
Member
 
Posts: 14
Joined: Fri May 10, 2013 2:37 pm

Re: DBMS_PARALLEL_EXECUTE

Postby Tim... » Fri May 10, 2013 4:45 pm

Hi.

Parallel execution can only improve performance if the server is capable of coping with the extra workload. If there are any bottlenecks on the server during this process, running it in parallel can actually make it run slower. :)

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17933
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: DBMS_PARALLEL_EXECUTE

Postby JDSal » Fri May 10, 2013 8:47 pm

What would you recommend to update a very large table in the quickest amount of time. I tried to truncate the main table and used a view to repopulate the original table with a hint. Then I tried a cursor but still had an issues with performance.

Thanks again :)
JDSal
Member
 
Posts: 14
Joined: Fri May 10, 2013 2:37 pm

Re: DBMS_PARALLEL_EXECUTE

Postby Tim... » Fri May 10, 2013 9:59 pm

Hi.

Probably the quickest approach is to create a new table and do a rename. For example:

Code: Select all
-- Make a new table.
CREATE TABLE emp_new .....

alter session enable parallel dml;

-- Modify the data using an insert. This can give a similar result to a delete or update depending on th query.
INSERT /*+ APPEND PARALLEL(emp_new) */ INTO emp_new SELECT /*+ PARALLEL(emp) */* FROM emp WHERE.....
COMMIT;

alter session disable parallel dml;

-- Switch the tables over.
RENAME TABLE emp TO emp_old;
RENAME TABLE emp_new TO emp;


Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17933
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: DBMS_PARALLEL_EXECUTE

Postby JDSal » Sat May 11, 2013 3:02 pm

Okay, I was informed not to delete or create a new table because this table has lots of unique constraints, indexes, dependencies...
JDSal
Member
 
Posts: 14
Joined: Fri May 10, 2013 2:37 pm

Re: DBMS_PARALLEL_EXECUTE

Postby Tim... » Sat May 11, 2013 3:43 pm

Hi.

The constraints and indexes don;t really make any difference as they can be rebuilt. Dependencies (like FKs to the table) are a problem as they would need to be recreated. These also represent a problem for a truncate and insert though too.

Looks like you may have to do a regular UPDATE. Using the PARALLEL hint may improve performance, if your server can cope with the extra workload.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17933
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: DBMS_PARALLEL_EXECUTE

Postby JDSal » Sat May 11, 2013 4:21 pm

Thanks for all the information, I really appreciate it. Also could I add chunk by size to pull say 5000 rows at a time? :)
JDSal
JDSal
Member
 
Posts: 14
Joined: Fri May 10, 2013 2:37 pm

Re: DBMS_PARALLEL_EXECUTE

Postby Tim... » Sat May 11, 2013 6:51 pm

Hi.

Probably better to use DBMS_PARALLEL_EXECUTE for that then. Create the chunks by ROWID, so you can have blocks of 5000. You can see an example of that in the article.

http://www.oracle-base.com/articles/11g ... s_by_rowid

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17933
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: DBMS_PARALLEL_EXECUTE

Postby JDSal » Sun May 12, 2013 12:40 pm

Thus I'm back to my original question how would I do that with a insert? I looked at the web address you provided but it only shows an update process
thanks
JDSal
Member
 
Posts: 14
Joined: Fri May 10, 2013 2:37 pm

Re: DBMS_PARALLEL_EXECUTE

Postby Tim... » Sun May 12, 2013 1:08 pm

Hi.

The technique is the same for INSERT ... SELECT, UPDATE or DELETE. Do the chunking by ROWID on the base table, then incorporate the ROWID into the WHERE clause. In the case of the INSERT SELECT, the table in the sub-select is the one that needs to be chunked, like this.

Code: Select all
DECLARE
  l_task_name VARCHAR2(50) := 'my_task';
  l_sql_stmt  VARCHAR2(32767);
BEGIN

  DBMS_PARALLEL_EXECUTE.create_task (task_name => l_task_name);
 
  DBMS_PARALLEL_EXECUTE.create_chunks_by_rowid(task_name   => l_task_name,
                                               table_owner => 'MYSCHEMA',
                                               table_name  => 'TABLE2',
                                               by_row      => TRUE,
                                               chunk_size  => 5000);

                                    
  l_sql_stmt := 'INSERT INTO table1 SELECT * FROM table2 WHERE rowid BETWEEN :start_id AND :end_id';
 
  DBMS_PARALLEL_EXECUTE.run_task(task_name      => l_task_name,
                                 sql_stmt       => l_sql_stmt,
                                 language_flag  => DBMS_SQL.NATIVE,
                                 parallel_level => 10);
END;
/


Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17933
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: DBMS_PARALLEL_EXECUTE

Postby JDSal » Sun May 12, 2013 1:41 pm

Thanks, I will try this and let you know how it works out. I appreciate your time and patients.
JDSal
Member
 
Posts: 14
Joined: Fri May 10, 2013 2:37 pm

Re: DBMS_PARALLEL_EXECUTE

Postby Tim... » Sun May 12, 2013 2:59 pm

Hi.

No worries. :)

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17933
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: DBMS_PARALLEL_EXECUTE

Postby JDSal » Mon May 13, 2013 1:11 pm

Okay, this is where I'm confused, I'm trying to insert from a view which has no primary-key how would this view pick-up the "WHERE rowid BETWEEN :start_id AND :end_id';"
JDSal
Member
 
Posts: 14
Joined: Fri May 10, 2013 2:37 pm

Next

Return to Oracle SQL and PL/SQL Development

Who is online

Users browsing this forum: No registered users and 5 guests