Thanks for the question, Leo.
Asked: March 24, 2016 - 5:27 pm UTC
Last updated: March 29, 2016 - 5:23 am UTC
Version: 11.2.0.4
Viewed 1000+ times
You Asked
Hi Tom,
I read that when Direct-Path Insert is performed in parallel into a partitioned table, each parallel server is assigned to only one partition. Is that correct?
If so, then...
Since each partition is its own segment, then I should not have to worry about segment header contention (mainly for freelist) in this scenario (where I intend to do a very large insert SQL using Direct-Path Insert in parallel). Am I correct in that assumption?
If so, then...
If a table has 4 partition, would there any benefit to having a DOP more than 4 for the Direct-Path insert? (Given my server has 4 or more CPUs).
Lastly, in another note you wrote, I thought I read you say that Freelist only manages free blocks under the HWM. I also read somewhere that when Direct-Path Insert is performed (serial or in parallel), Oracle grabs a new extent above the HWM. If it's grabbing extents above the HWM, would Freelist be a factor at all (in this particular scenario)? If so, can you explain.
and Connor said...
You can have parallel larger than the number of partitions, eg
<code>
SQL> create table t1 (
2 x date,
3 y int )
4 partition by range (x )
5 interval (numtoyminterval(1,'YEAR'))
6 (
7 partition p1 values less than ( date '2010-01-01' )
8 );
Table created.
SQL>
SQL> insert into t1 values ( date '2009-12-31', 1 );
1 row created.
SQL> insert into t1 values ( date '2010-12-31', 1 );
1 row created.
SQL> insert into t1 values ( date '2011-12-31', 1 );
1 row created.
SQL> insert into t1 values ( date '2012-12-31', 1 );
1 row created.
SQL>
SQL>
SQL> select partition_name
2 from user_tab_partitions
3 where table_name = 'T1';
PARTITION_NAME
------------------------------
P1
SYS_P3716
SYS_P3717
SYS_P3718
SQL> create table staging pctfree 0 as
2 select date '2009-01-01' + rownum / 10000 x1, rownum y1
3 from
4 ( select 1 from dual connect by level <= 10000 ),
5 ( select 1 from dual connect by level <= 1000 )
6 /
Table created.
SQL>
SQL> commit;
Commit complete.
SQL> alter session enable parallel dml;
Session altered.
SQL> explain plan for
2 insert /*+ APPEND PARALLEL(t1,8) */ into t1
3 select /*+ PARALLEL(s,8) */ * from staging s;
Explained.
SQL> @exp9
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
---------------------------------------------------------------------------------
--------------------
Plan hash value: 3812613823
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
---------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 10M| 133M|
| 1 | PX COORDINATOR | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 10M| 133M|
| 3 | LOAD AS SELECT (HIGH WATER MARK BROKERED)| T1 | | |
| 4 | OPTIMIZER STATISTICS GATHERING | | 10M| 133M|
| 5 | PX RECEIVE | | 10M| 133M|
| 6 | PX SEND RANDOM LOCAL | :TQ10000 | 10M| 133M|
| 7 | PX BLOCK ITERATOR | | 10M| 133M|
| 8 | TABLE ACCESS FULL | STAGING | 10M| 133M|
---------------------------------------------------------------------------------
Note
-----
- Degree of Parallelism is 8 because of table property
19 rows selected.
--
-- and we'll try it for real
--
SQL> insert /*+ APPEND PARALLEL(t1,8) */ into t1
2 select /*+ PARALLEL(s,8) */ * from staging s;
10000000 rows created.
SQL> commit;
Commit complete.
--
-- and whilst it's running from another session
--
SQL> select * from v$px_session;
SADDR SID SERIAL# QCSID QCSERIAL#
---------------- ---------- ---------- ---------- ----------
000007FFBD277CF0 17 51292 216 45321
000007FFBD58F690 403 35869 216 45321
000007FFBD719290 597 25289 216 45321
000007FFBD275C20 18 48418 216 45321
000007FFBD41A2B0 199 26319 216 45321
000007FFBD58D5C0 404 2746 216 45321
000007FFBD71F500 594 64699 216 45321
000007FFBD27DF60 14 7033 216 45321
000007FFBD3FB680 214 15740 216 45321
000007FFBD58B4F0 405 15667 216 45321
000007FFBD72B9E0 588 41438 216 45321
000007FFBD273B50 19 16708 216 45321
000007FFBD3F5410 217 63706 216 45321
000007FFBD589420 406 65439 216 45321
000007FFBD72DAB0 587 10418 216 45321
000007FFBD271A80 20 23328 216 45321
000007FFBD3F74E0 216 45321 216
17 rows selected.
<code>
In terms of choosing the 'best' DOP ? Easy to work out - benchmark, benchmark, benchmark...
And you are correct - you should not need worry about freelist contention etc. There might be a little bit of competition for the segment header as slaves complete their task, but it would be negligible compared to the cost of adding running the large parallel task anyway.
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment