Re: SQL-LOADER, UNIX ascii file 1.2M liness takes more than 2 days..HELP!

On Sat, 08 Jun 1996 08:39:49 GMT, shewring_at_wmcpet.dialix.oz.au (Marc Shewring) wrote:

>Hi there I am traying to load in data using sql-loader under SUNOS
>4.1.3.u on a Sparc20 and the load is taking forever. Admitedly the
>file is big 1.2 Million lines and there are only 4 fields on each
>line. Is there a faster way of getting this nav-data into oracle?
>
A couple of things (control file and database version would always useful in a question like this...)

• are you using direct=true on the command line when running sqlloader? That will bypass sql and write directly to the database file. direct=true is like setting fast=yes :)
• if you are running in noarchivelog mode and use direct=true you will bypass redo-log generation as well.
• if you must use the conventional path loader for whatever reason, look using the skip/load parameters and load in parallel, for example:

sqlldr user/pass xxx.ctl skip=0 load=100000 & sqlldr user/pass xxx.ctl skip=100000 load=100000 & ....

If you do this, consider setting multiple freelists on your database table (see th create table command syntax for explaination of freelists).

• look in your $ORACLE_HOME/rdbms/log directory for the alert file for your database. make sure you don't see lots of "checkpoint not completes". If you do, add more log files or make your current set of log files larger. "checkpoint not complete" will definitely slow down the load.
• if you must use conventional path load (no direct=true) increase your rows= parameter to around 100 or so.
• increase the bindsize= parameter to a larger number (bigger then 64k anyway) in conjunction with the rows= parameter.

>regards Marc
>------------------------------------------------------------------------------
>Marc Shewring
>Information Technology Officer "If you build it...
>Western Mining Corporation - Petroleum Division They will surf..."
>
>Marc.Shewring_at_p085.aone.net.au
>shewring_at_wmcpet.dialix.oz.au
>mcs_at_interlink.com.au
>------------------------------------------------------------------------------
>

Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com                          

http://govt.us.oracle.com -- Check out our web site! Brand new, uses Oracle Web Server and Database

statements and opinions are mine and do not necessarily reflect the opinions of Oracle Corporation Received on Sat Jun 08 1996 - 00:00:00 CEST