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

Do you use RAID ?

Tamil

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.

No need to break up the data file.

sqlldr has "PARALLEL" option.

Use it.

Tamil

Quote:

---

Originally posted by tamilselvan
No need to break up the data file.

sqlldr has "PARALLEL" option.

---

I don't think so.

PHP Code:

---

TEST=>sqlldr

SQL*Loader: Release 9.2.0.5.0 - Production on Fri Nov 5 12:33:36 2004

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Usage: SQLLDR 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 64, Direct 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 file: name 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 indexes, mark 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 load; NOT_USED, GENERATE_ONLY, EXECUTE  (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 entries) of date conversion cache  (Default 1000) 


---

Tamil

So??? You obviously know there is a PARALLEL parameter there, but do you know what it's for?

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

:rolleyes:

Quote:

---

Originally posted by tamilselvan
SO YOU ALSO DO NOT KNOW WHAT IT IS FOR ?

---

RTFM

Quote:

---

Originally posted by tamilselvan
==
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

---

At the risk of stepping on a hornet's nest, I read it as, if he only loads one file then Parallel won't help, because with parrallel mode each process can load a file, hence you need to load multiple files in order to load in parallel. Am I wrong?

Quote:

---

Initiating Multiple SQL*Loader Sessions
Each SQL*Loader session takes a different datafile as input. In all sessions executing a direct load on the same table, you must set PARALLEL to true. The syntax is:

http://download-west.oracle.com/docs...2/parallel.gif

---

btw, it seems that the more files you can break the load into, the easier it will be to increase parrallelism, and the better Oracle will be able to balance the load among the various processes. Although I'm sure that there is a diminishing return somewhere.

The parallel clause is just an enabler of parallelism in the load, in that it modifies the allocation of new extents (and presumably the locking as well).

Even only one input file is used but the table is partitioned, Oracle uses parallel threads for loading the data in to different partitions, if you use parallel=true option in sqlldr.

Tamil

Thanks for help, but i know these issues;

But i don't understand why the four CPU are not used. sqlldr is multithreading, so why don't it use the four CPUs ?

Another question is that event "SQL*Net more data from client" a sign of lack of CPU ?

thanks in advcance