Solved

sql loader time

Posted on 2006-06-21
3
668 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

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