Solved

sql loader time

Posted on 2006-06-21
3
677 Views
Last Modified: 2008-02-01
Hi ,
   I am using sql loader to load a table having 10 columns.

data is by month and each month has approx 5 million rows.
It is taking 2.5 hours each to load the first and second month
With 3rd month time starts increasing by 1 hour which means 3rd month takes 3.5 hour fourth minth takes 4.5 hours.

Basically deterioration in the loading time.

Can anybody tell me why is this happeneing and how can this be avoided.

Also, I think it has anyhthing to do with index in the table. Every row loaded has to be updated in the index also.
If yes than how can this be avoided.

Thanks in advance
Deepak
0
Comment
Question by:dgupta70
  • 2
3 Comments
 
LVL 14

Expert Comment

by:GGuzdziol
ID: 16954940
Did You try to use direct path loading? (options (DIRECT=TRUE) in control file)

Time should increase because table is getting bigger and index is getting bigger so it's harder to update it. If You build table from scratch it could be worth dropping indexes and rebuilding them after load is finished.
0
 
LVL 3

Expert Comment

by:hardikbeit
ID: 16957305

Because of Index updation, it takes lots of time. Every row is updating the index.

You can improve your performance trought Direct path loading. DIRECT=TRUE bypass all the checking.

With regards,
Hardik
0
 
LVL 3

Accepted Solution

by:
hardikbeit earned 500 total points
ID: 16957314
Maximizing SQL*Loader Performance:

SQL*Loader is flexible and offers many options that should be considered to maximize the speed of data loads.  These include:

1.   Use Direct Path Loads - The conventional path loader essentially loads the data by using standard insert statements.  The direct path loader (direct=true) loads directly into the Oracle data files and creates blocks in Oracle database block format.  The fact that SQL is not being issued makes the entire process much less taxing on the database.  

2.   Disable Indexes and Constraints.  For conventional data loads only, the disabling of indexes and constraints can greatly enhance the performance of SQL*Loader.  

3.   Use a Larger Bind Array.  For conventional data loads only, larger bind arrays limit the number of calls to the database and increase performance.  The size of the bind array is specified using the bindsize parameter.  The bind array's size is equivalent to the number of rows it contains (rows=) times the maximum length of each row.

4.   Use ROWS=n to Commit Less Frequently.  For conventional data loads only, the rows parameter specifies the number of rows per commit.  Issuing fewer commits will enhance performance.  

5.   Use Parallel Loads.  Available with direct path data loads only, this option allows multiple SQL*Loader jobs to execute concurrently.
$ sqlldr control=first.ctl  parallel=true direct=true

7.   Disable Archiving During Load.  While this may not be feasible in certain environments, disabling database archiving can increase performance considerably.

8.   Use unrecoverable.  The unrecoverable option (unrecoverable load data) disables the writing of the data to the redo logs.  This option is available for direct path loads only.

This helps you.

Let me know in case of any query.

With regards,
Hardik
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Oracle Pivot Question 8 71
Oracle Insert not working 10 30
Email query results in HTML 6 29
oracle sqlplus query delimiter 8 17
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question