SQl*Loader poor perfomance
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: SQl*Loader poor perfomance

  1. #1
    Join Date
    Oct 2003
    Posts
    4

    SQl*Loader poor perfomance

    Hello

    I'm trying to understand why my server don't go faster
    I have checked cpu (via sar), disk i/o (via iostat) and memory (vmstat). the more cpu that it is used is around 25% and the %busy of disks around 20%.

    Our configuration is sun V880 with 4 cpu, 8go Ram and EMC bay disk with 4 controllers.

    Oracle 9.2.0.5 is installed.

    here's options used for sql*loader :
    DIRECT=TRUE DATE_CACHE=10000 COLUMNARRAYROWS=40000 READSIZE=20000000 STREAMSIZE=1024000

    UNRECOVERABLE LOAD DATA
    TRUNCATE INTO TABLE

    I have seen "SQL*Net message from client" and "SQL*Net more data from client" in v$session_wait, but don't know why : i am not using a sql*net connection (sqlldr user/password ), i'am on the server.

    Any help apprecied

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    Do you use RAID ?

    Tamil

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    Your RAID level is irrelevent in this context; you are cpu bound. If one cpu on a 4 cpu box is at 25%, then it's running at 100%. The only way you can utilize your multiple cpus is to break up the data file and load in parallel.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    No need to break up the data file.

    sqlldr has "PARALLEL" option.

    Use it.

    Tamil

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    Originally posted by tamilselvan
    No need to break up the data file.

    sqlldr has "PARALLEL" option.

    I don't think so.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  6. #6
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    PHP Code:
    TEST=>sqlldr

    SQL
    *LoaderRelease 9.2.0.5.0 Production on Fri Nov 5 12:33:36 2004

    Copyright 
    (c19822002Oracle Corporation.  All rights reserved.


    UsageSQLLDR keyword=value [,keyword=value,...]

    Valid Keywords:

        
    userid -- ORACLE username/password
       control 
    -- Control file name
           log 
    -- Log file name
           bad 
    -- Bad file name
          data 
    -- Data file name
       discard 
    -- Discard file name
    discardmax 
    -- Number of discards to allow          (Default all)
          
    skip -- Number of logical records to skip    (Default 0)
          
    load -- Number of logical records to load    (Default all)
        
    errors -- Number of errors to allow            (Default 50)
          
    rows -- Number of rows in conventional path bind array or between direct path data saves
                   
    (Default: Conventional path 64Direct path all)
      
    bindsize -- Size of conventional path bind array in bytes  (Default 256000)
        
    silent -- Suppress messages during run (header,feedback,errors,discards,partitions)
        
    direct -- use direct path                      (Default FALSE)
       
    parfile -- parameter filename of file that contains parameter specifications
      parallel 
    -- do parallel load                     (Default FALSE)
          
    file -- File to allocate extents from
    skip_unusable_indexes 
    -- disallow/allow unusable indexes or index partitions  (Default FALSE)
    skip_index_maintenance -- do not maintain indexesmark affected indexes as unusable  (Default FALSE
    )
    commit_discontinued -- commit loaded rows when load is discontinued  (Default FALSE)
      
    readsize -- Size of Read buffer                  (Default 1048576)
    external_table -- use external table for loadNOT_USEDGENERATE_ONLYEXECUTE  (Default NOT_USED)
    columnarrayrows -- Number of rows for direct path column array  (Default 5000)
    streamsize -- Size of direct path stream buffer in bytes  (Default 256000)
    multithreading -- use multithreading in direct path
     resumable 
    -- enable or disable resumable for current session  (Default FALSE)
    resumable_name -- text string to help identify resumable statement
    resumable_timeout 
    -- wait time (in seconds) for RESUMABLE  (Default 7200)
    date_cache -- size (in entriesof date conversion cache  (Default 1000
    Tamil

  7. #7
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    So??? You obviously know there is a PARALLEL parameter there, but do you know what it's for?
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  8. #8
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    ==
    So??? You obviously know there is a PARALLEL parameter there, but do you know what it's for?
    ==

    You mentioned about breaking of input file and load them in parallel. Why do you break the input file and do the work in parallel?
    SO YOU ALSO DO NOT KNOW WHAT IT IS FOR ?

    Tamil

  9. #9
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  10. #10
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by tamilselvan
    SO YOU ALSO DO NOT KNOW WHAT IT IS FOR ?
    RTFM
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

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