Relation between parallel dml and extent allocation
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Relation between parallel dml and extent allocation

  1. #1
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434

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

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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).
    Last edited by jmodic; 06-18-2004 at 07:39 AM.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    See also Metalink Note:50592.1
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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

  7. #7
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Thanks Jurij, Tamil.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width