Relation between parallel dml and extent allocation

Hi Guys,

Can any one please help me understand why on earth Oracle decides the extent size by its choice in case of Parallel DML?

below is a test case and am bit confused..

Code:

DBA> select segment_name, segment_type, .. from dba_segments where segment_name = upper('Temp_Open_Order_Detail'); SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BYTES BLOCKS EXTENTS INITIAL_EXTENT NEXT_EXTENT PCT_INCREASE -------------------------- ------------------ ----------------- ---------------- ------------ ---------- -------------------- ------------- -------------- TEMP_OPEN_ORDER_DETAIL TABLE WWCIW_TAB_2 287309824 35072 5 209715200 209715200 0 DBA> select segment_name, segment_type, .. from dba_extents where segment_name = upper('temp_Open_Order_Detail') order by EXTENT_ID; SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID BYTES BLOCKS ---------------------------- ------------------ ----------------- ----------- ------------ -------- TEMP_OPEN_ORDER_DETAIL TABLE WWCIW_TAB_2 0 209715200 25600 TEMP_OPEN_ORDER_DETAIL TABLE WWCIW_TAB_2 1 37216256 4543 TEMP_OPEN_ORDER_DETAIL TABLE WWCIW_TAB_2 2 37232640 4545 TEMP_OPEN_ORDER_DETAIL TABLE WWCIW_TAB_2 3 1581056 193 TEMP_OPEN_ORDER_DETAIL TABLE WWCIW_TAB_2 4 1564672 191 DBA> select TABLESPACE_NAME, EXTENT_MANAGEMENT, ALLOCATION_TYPE, SEGMENT_SPACE_MANAGEMENT from 2 dba_tablespaces where TABLESPACE_NAME = 'WWCIW_TAB_2'; TABLESPACE_NAME EXTENT_MAN ALLOCATIO SEGMEN ------------------------------ ---------- --------- ------ WWCIW_TAB_2 DICTIONARY USER MANUAL

If you see above initial and next are 200M and also pct increase is 0.

now why is oracle allocating those happazard sized extents??

Well if i insert into this table serially then extents are of 200M but if i say "Alter Session Force Parallel DML" then am seeing these happazard extents..

Abhay.

Quote:

Storage Space When Using Dictionary-Managed Tablespaces

When creating a table or index in parallel, each parallel execution server uses the values in the STORAGE clause of the CREATE statement to create temporary segments to store the rows. Therefore, a table created with a NEXT setting of 5 MB and a PARALLEL DEGREE of 12 consumes at least 60 megabytes (MB) of storage during table creation because each process starts with an extent of 5 MB. When the parallel execution coordinator combines the segments, some of the segments may be trimmed, and the resulting table may be smaller than the requested 60 MB.

Oracle9i Data Warehousing Guide Release 2 (9.2)

http://download-west.oracle.com/docs...ngpe.htm#64260

Note: The temporary segments they are talking about in the above paragraph become table extents in the final phase. So when you create table as CTAS in DMT, it is normal that some of the segments are smaller.

Quote:

Originally posted by jmodic
Oracle9i Data Warehousing Guide Release 2 (9.2)

http://download-west.oracle.com/docs...ngpe.htm#64260

Note: The temporary segments they are talking about in the above paragraph become table extents in the final phase. So when you create table as CTAS in DMT, it is normal that some of the segments are smaller.

Jurij,

I have had seen this.. but please note i said parallel DML not DDL..

Am inserting into the temp table with parallel option.. and getting hapazard extents :confused:

Abhay.

The principle is exactly the same as with CTAS. Each slave process allocates a temp segment and insert data into it. At the end, the coordinator process mereges those allocated temp segments into table's extents, trimming the last temp extent of each slave process.

For example, let's say your degree of parallelism is 4. Each new extent of the table ought to be 5M in size. Suppose each of those 4 slave processes allocated 5 temporary extents during insert. 4 of those eextents are totaly packed, while the last one is probably not. So at the end, the result will probably be 16 new extents of 5MB each and 4 new extents with sizes ranging form anything between couple of KB and 5MB.

This is how parallel loading is behaving since 8i (or maybe since 8.0, can't remember).

See also Metalink Note:50592.1

Whether it is parallel DML or parallel DDL, Oracle does release unused space only in the last extents of each parallel slaves, if DMT is used. If N is the number of extents for each slave, then Oracle does not release unused space in the (N-1) extents.

To aviod this, use minimum extent clause in the tablespace.
Set initial = next = min_extlen.
If the tablespace is already created, you can change by this command.

alter tablespace users minimum extent 5m;

Tamil

Thanks Jurij, Tamil.