-
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
-
-
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
-
No need to break up the data file.
sqlldr has "PARALLEL" option.
Use it.
Tamil
-
Originally posted by tamilselvan
No need to break up the data file.
sqlldr has "PARALLEL" option.
I don't think so.
Jeff Hunter
-
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?
Jeff Hunter
-
==
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
-
-
Originally posted by tamilselvan
SO YOU ALSO DO NOT KNOW WHAT IT IS FOR ?
RTFM
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|