Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: June 25, 2009 - 6:10 pm UTC

Last updated: June 06, 2013 - 2:55 pm UTC

Version: 10.2.0

Viewed 1000+ times

You Asked

Hi Tom,

I learned a lot from your site. Now I have a question.

We are trying to run multiple processes to insert into a same table with INSERT INTO... SELECT ..., with setting all selected two FROM tables and inserted into table as parallel, but after launch all 6 processes, only the first one went in parallel, the rest five was single thread. Based on the research from web site, this seems go to serialization. We want to run 6 processes at the same time with each process run in parallel to speed up our load process. Is there any way to do this?

We tried /*+ append */ hint first, but it went to serialization. Then, we tried to remove the hint, it still went to serialization. It's said if you are doing parallel dml insert, the Append mode is default.

-- Details:

all three tables are defined as parallel, nologging.
--process_1
Insert into tableA select from tableB, tableC... where id between n1 and n2;

-process_2
Insert into tableA select from tableB, tableC... where id between n3 and n4;
....

-- process_6
Insert into tableA select from tableB, tableC... where id between n12 and n13;

Thanks for your help!


and Tom said...

if you want to do a parallel insert, you just let US do it.


insert into tablea
select from tableb, tablec ... where join_conditions;


just do a SINGLE insert that uses PARALLEL - you need not break the query up as you are trying with "where id between", stop doing that, just do a single sql statement and WE can append in parallel.

Rating

  (1 rating)

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

Comments

Parallel DML

Rajeshwaran, June 06, 2013 - 1:07 pm UTC

Tom:

I was reading about parallel DML from your book, Expert oracle DB architecture 11g.

Questions:
q1) Why do I see predicate information in plan, when there is no Where clause in the sql statement?

q2) I have index on ID column, why the plan has "INDEX MAINTENANCE" step when my sql does update on "Status" column? what is the need for index maintenance when I update a non-indexed column?

Below is the code I execute from session#1
alter table big_table.big_table parallel;
alter session enable parallel dml;
update big_table.big_table
set status ='Done';

Explain plan what I see in Session#2
rajesh@ORA11G> select *
  2  from table( dbms_xplan.display_cursor('7rymppgbz61vy'))
  3  /

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
SQL_ID  7rymppgbz61vy, child number 0
-------------------------------------
update big_table.big_table set status ='Done'

Plan hash value: 2864480563

----------------------------------------------------------------------------
| Id  | Operation                | Name      | Rows  | Bytes | Cost (%CPU)| 
----------------------------------------------------------------------------
|   0 | UPDATE STATEMENT         |           |       |       | 11674 (100)| 
|   1 |  PX COORDINATOR          |           |       |       |            | 
|   2 |   PX SEND QC (RANDOM)    | :TQ10001  |    10M|    57M| 11674   (1)| 
|   3 |    INDEX MAINTENANCE     | BIG_TABLE |       |       |            | 
|   4 |     PX RECEIVE           |           |    10M|    57M| 11674   (1)| 
|   5 |      PX SEND RANGE       | :TQ10000  |    10M|    57M| 11674   (1)| 
|   6 |       UPDATE             | BIG_TABLE |       |       |            | 
|   7 |        PX BLOCK ITERATOR |           |    10M|    57M| 11674   (1)| 
|*  8 |         TABLE ACCESS FULL| BIG_TABLE |    10M|    57M| 11674   (1)| 
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   8 - access(:Z>=:Z AND :Z<=:Z)

rajesh@ORA11G> select table_name,index_name,column_name
  2  from all_ind_columns
  3  where table_name ='BIG_TABLE' ;

TABLE_NAME INDEX_NAME COLUMN_NAM
---------- ---------- ----------
BIG_TABLE  BIG_IDX_01 ID

1 row selected.


Tom Kyte
June 06, 2013 - 2:55 pm UTC

the z big has to do with parallel query row distribution - here is a discussion on it:

http://kerryosborne.oracle-guy.com/2011/01/storagezz-and-z/

Note: it is not exadata specific, and it only needs parallel query - not parallel DML to happen:

big_table%ORA11GR2> alter table big_table noparallel;

Table altered.

big_table%ORA11GR2> alter session disable parallel dml;

Session altered.

big_table%ORA11GR2> 
big_table%ORA11GR2> select count(*) from big_table;

  COUNT(*)
----------
   1000000

big_table%ORA11GR2> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  6fxg0qj7j7ftd, child number 0
-------------------------------------
select count(*) from big_table

Plan hash value: 599409829

------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |       |  3989 (100)|          |
|   1 |  SORT AGGREGATE    |           |     1 |            |          |
|   2 |   TABLE ACCESS FULL| BIG_TABLE |  1000K|  3989   (1)| 00:00:48 |
------------------------------------------------------------------------


14 rows selected.

big_table%ORA11GR2> alter table big_table parallel;

Table altered.

big_table%ORA11GR2> select count(*) from big_table;

  COUNT(*)
----------
   1000000

big_table%ORA11GR2> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  6fxg0qj7j7ftd, child number 0
-------------------------------------
select count(*) from big_table

Plan hash value: 2894119656

---------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |           |       |   553 (100)|          |        |      |            |
|   1 |  SORT AGGREGATE        |           |     1 |            |          |        |      |            |
|   2 |   PX COORDINATOR       |           |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM) | :TQ10000  |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |           |     1 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR |           |  1000K|   553   (0)| 00:00:07 |  Q1,00 | PCWC |            |
|*  6 |       TABLE ACCESS FULL| BIG_TABLE |  1000K|   553   (0)| 00:00:07 |  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - access(:Z>=:Z AND :Z<=:Z)


23 rows selected.

big_table%ORA11GR2> 
big_table%ORA11GR2> update big_table set status = 'done';

1000000 rows updated.

big_table%ORA11GR2> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  788gfnf63d1gu, child number 0
-------------------------------------
update big_table set status = 'done'

Plan hash value: 2685615093

----------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name      | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT      |           |       |       |   555 (100)|          |        |      |            |
|   1 |  UPDATE               | BIG_TABLE |       |       |            |          |        |      |            |
|   2 |   PX COORDINATOR      |           |       |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)| :TQ10000  |  1000K|    94M|   555   (1)| 00:00:07 |  Q1,00 | P->S | QC (RAND)  |
|   4 |     PX BLOCK ITERATOR |           |  1000K|    94M|   555   (1)| 00:00:07 |  Q1,00 | PCWC |            |
|*  5 |      TABLE ACCESS FULL| BIG_TABLE |  1000K|    94M|   555   (1)| 00:00:07 |  Q1,00 | PCWP |            |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access(:Z>=:Z AND :Z<=:Z)


22 rows selected.

big_table%ORA11GR2> commit;

Commit complete.

big_table%ORA11GR2> 
big_table%ORA11GR2> alter session enable parallel dml;

Session altered.

big_table%ORA11GR2> update big_table set status = 'Done';

1000000 rows updated.

big_table%ORA11GR2> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  d6fcvy275vwmn, child number 0
-------------------------------------
update big_table set status = 'Done'

Plan hash value: 2864480563

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name      | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT         |           |       |       |   555 (100)|          |        |      |            |
|   1 |  PX COORDINATOR          |           |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)    | :TQ10001  |  1000K|    94M|   555   (1)| 00:00:07 |  Q1,01 | P->S | QC (RAND)  |
|   3 |    INDEX MAINTENANCE     | BIG_TABLE |       |       |            |          |  Q1,01 | PCWP |            |
|   4 |     PX RECEIVE           |           |  1000K|    94M|   555   (1)| 00:00:07 |  Q1,01 | PCWP |            |
|   5 |      PX SEND RANGE       | :TQ10000  |  1000K|    94M|   555   (1)| 00:00:07 |  Q1,00 | P->P | RANGE      |
|   6 |       UPDATE             | BIG_TABLE |       |       |            |          |  Q1,00 | PCWP |            |
|   7 |        PX BLOCK ITERATOR |           |  1000K|    94M|   555   (1)| 00:00:07 |  Q1,00 | PCWC |            |
|*  8 |         TABLE ACCESS FULL| BIG_TABLE |  1000K|    94M|   555   (1)| 00:00:07 |  Q1,00 | PCWP |            |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   8 - access(:Z>=:Z AND :Z<=:Z)


25 rows selected.




the index maintenance step just appears in plans against indexed tables, it represents the deferred index maintenance that would take place if necessary (which it isn't in this case). Just a plan artifact in this case.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Hints

Need more information on hints? Check out the complete hint descriptions