Loading Data through sql loader very very slow.... Please Help!!!
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Loading Data through sql loader very very slow.... Please Help!!!

  1. #1
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577

    Angry

    Hi,
    I am trying to load around 3 million records in a table using sql loader (Oracle 8.1.7). It is a simple load of a comma delimited file.

    I was able to load the same data before with the speed atleast 100 times faster

    I dont know what happenned and what do do to fix this thing.

    I have to deliver it today, so if please if someone can help me out with it.

    Infact I even tried direct=y in the parfile but the load does not progress at all, it just hangs.

    Thanks
    Ronnie

    Here is the control file
    --------------------------
    load data
    infile 'loadhfod.dat'
    badfile 'hfod.bad'
    discardfile 'hfod.dcs'
    truncate
    into table ofas
    fields terminated by ","
    (company,
    time,
    region,
    lineitem,
    actual
    nullif (actual = BLANKS),
    actualus
    nullif (actualus = BLANKS),
    year_quarter)


    Parameter File
    ----------------
    userid=hfod/hfod@louise_company
    control=loaddata.ctl
    errors=25000
    log=loaddata.log

    Sample Data
    --------------
    MBOC,1996,REGROLL,COFYE, 12.000000, 12.000000,YR
    MBOC,1996,REGROLL,COLOP, 12.000000, 12.000000,YR
    MBOC,1996,REGROLL,COEPAM, 1.000000, 1.000000,YR
    MBOC,1996,REGROLL,COCAI, 1.000000, 1.000000,YR
    MBOC,1996,REGROLL,COAAER, 1.000000, 1.000000,YR
    MBOC,1996,REGROLL,COYEER, 1.000000, 1.000000,YR

    Table Structure
    ----------------
    SQL> desc ofas
    Name Null? Type
    ----------------------------------------- -------- -----------------
    COMPANY VARCHAR2(10)
    TIME VARCHAR2(10)
    REGION VARCHAR2(10)
    LINEITEM VARCHAR2(15)
    ACTUAL NUMBER(20,6)
    ACTUALUS NUMBER(20,6)
    YEAR_QUARTER VARCHAR2(5)

    important init.ora parameters
    ---------------------------------
    db_block_buffers = 13124
    shared_pool_size = 35837337
    large_pool_size = 614400
    log_buffer = 32768
    db_block_size = 8192
    compatible = 8.1.0
    sort_area_size = 65536
    sort_area_retained_size = 65536

    The database is in no archive mode.

    PLEASE HELP


  2. #2
    Join Date
    May 2001
    Posts
    82
    Hi,

    I am not sure about the syntax of the control file...

    You are using TRUNCATE INTO [table_name]

    Should it be INSERT or APPEND or REPLACE

    Please check the syntax

    Radha.

  3. #3
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    s_radhak,

    Its truncating the data in the table before loading the recordsAny suggesstions why it is sooooooooo sloooooooooooow

    Ronnie

  4. #4
    Join Date
    May 2001
    Posts
    82
    Ronnie,

    It might be because of the table's referential integrity constraints.

    Radha.

  5. #5
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    There were no indexes or constraints on the table.

    Anyways I dropped and recreated the table and now its fine.

    I have no clue why. Any Ideas?????

    Ronnie

  6. #6
    Join Date
    Feb 2001
    Posts
    82
    how many extents does the table has? b4 u dropped it?

  7. #7
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Do you have any triggers against the table? which you have forgot to disable?


    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


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