Relation between parallel dml and extent allocation

[abhaysk]

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..

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.

[jmodic]

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.

[abhaysk]

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

Abhay.

[jmodic]

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).

[jmodic]

See also Metalink Note:50592.1

[tamilselvan]

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

[abhaysk]

Thanks Jurij, Tamil.