SqlLdr and Extent Problems...
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: SqlLdr and Extent Problems...

Hybrid View

  1. #1
    Join Date
    Jan 2002
    Posts
    148
    hi,

    i cant understand why this happened please suggest.

    I am using sqlldr for populating a table for 800M rows.

    at the sqlldr...
    i have used parallel=true , direct=t

    at the table level
    (initial extent 2G next 2G minextents 11 ) and hash partitions 8.

    and altered the next to 500M after the table creation , so that during SqlLdr process will create a temp segment for min 500M .

    so if i inquiry BEFORE sqlldr process for segment_name in dba_segments i get
    tablespace bytes extents
    -----------------------------------------
    1 22G 11
    2 22G 11
    3 22G 11
    4 22G 11
    5 22G 11
    6 22G 11
    7 22G 11
    8 22G 11

    AND now aslong as sqlldr process is running, and the querr
    for dba_segmetns where segement_type='TEMPORARY' - i get

    tablespace bytes extents
    -----------------------------------------
    1 500m 1
    2 500m 1
    3 500m 1
    4 500m 1
    5 500m 1
    6 500m 1
    7 500m 1
    8 500m 1

    and the extents increase in the incrementals of 500M as pctincrease is 0.

    Till here ever thing is normal.

    but after the completion of process when i inquire the batch_table its with surprising number of extents cause the
    extents size ranged from max of 2G to min of 12K, while it would have not dropped below 500M minimum.
    and the total extents came upto 800+.

    Please advise me whether i was wrong some where ?


    Thanx
    Jr.

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    When you run parallel insert through SQL*LOADER, Oracle creates as many extents equal to the parallel processes. If the number of parallel processes is 5, then 5 extents will be created in the tablespace for the table to start with. Then extents will be added once a extent is filled. And finally when the job is done, the free space in the last extents of each parallel proces is freed. The last extent size may be equal to initial or next extent size even though pctincrease is set to 0. This leads to unequal extent sizes within the tablespace.

    To over come this flaw, you need to set the event 10901 at the system level before you start laoding data into the table.



    [Edited by tamilselvan on 02-13-2002 at 11:34 AM]

  3. #3
    Join Date
    Jan 2002
    Posts
    148
    Thanx.
    i guess,if i am goint to set the event, i could avoid the trimming of extents and i will not be using these partial free extents anymore in my next sqlloader process.
    But I am planning to avoid fragmentation, and want oracle to use the pre allocated extents.
    Will Insert in the ctl file and direct=true without parallel options would help me to do that?



  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    Please correct me if I am wrong.
    You try to insert 800 Million rows. I think the source data are in multiple flat files. Avoid using parallel option if you want unequal extents in a tablespace. Use DIRECT load option with unrecoverable defined in the control file.

    You can run multiple direct loads if you do not want to use parallel option.

    Set INITIAL and NEXT 500MB size at table level.


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