| sql loader performance help!! [message #57306] |
Wed, 04 June 2003 13:24  |
sidd
Messages: 130
Registered: May 2003
|
Senior Member |
|
|
I am loading 2 million rows using sqlloader, how do I improve the performance, what parameters I should set, and I want to do this in a parfile, how do I do this. any suggestions are appreciated
Thanks
|
|
|
|
|
|
|
|
| Re: sql loader performance help!! [message #116188 is a reply to message #57306] |
Mon, 18 April 2005 09:10  |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
2 million in direct path is really nothing, assuming you have a reasonably fast computer and disk io system. I'd have to double check, but I believe the indexes get rebuild at the end of the process in direct path, not row by row as conventional.
The less things you put in the control file, in general, the faster. That means not a lot of when conditions, or continueif's and all the other fancy things. If you have the luxery of defining the format of the file you are loading.
Also fixed position (position x:y) is faster than delimitted, so use that if at all possible. Oh and if you are replacing data in a table, use truncate into. If adding it, use append.
|
|
|
|
|
|
| Re: sql loader performance help!! [message #116403 is a reply to message #57306] |
Tue, 19 April 2005 17:09 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
Don't really know enough about your situation, but is your table partitioned? If not, then why not?
And 35k rows a second is only about 1 minute for your entire load. You are rebuilding the index in parallel right?
And have you tested letting sqlloader maintain your index for you rather than doing a manual rebuild yourself after the load?
And are all of the indexes you are maintaining really needed for access purposes? Sure you want to keep integrity constraints, but if you lose some other indexes, does it really hurt the speed of your report run badly enough to outweigh the slowdown they impose on your load?
|
|
|
|
 Re: sql loader performance help!! [message #116748 is a reply to message #57306] |
Thu, 21 April 2005 16:59 |
dbconstructer
Messages: 11
Registered: April 2005
|
Junior Member |
|
|
About the requirement, I am not the original question starter who wants to load 2 million records only. In my situation, I need to hand 10k events loading and the loading lasts at least 4 hours per day. At the meantime, the customer may generate the real time report on the table. The conflict of loading and report is the biggest headache for me.
I want to use partition table but the problem is I need to find a standard to partition the table. The ideal way to partition for me is to generate the partition every 15 minute and keep 96 partitions rolling every day. The concept is similar with mysql's rolling table.
The reasons are:
1. The application cannot afford keep all the data which is fed into system 10k/sec.
2. While loading data by using SQL*Loader Direct=true, all the index are marked as unused. The report cannot use the index while
The loading run and the loading will not stop. So I have to keep the partition as small as possible.
About the index parallel rebuilding
On a 250000 record table, a index rebuilding
Without parallel took 40 seconds.
With parallel took more than 5 minutes
It's hard to explain.
About sqlloader maintain index automatically
First, I don't how to do this. Could you give me the command sample? Appreciate it.
Second, I'm running 5 sqlloader parallel to get 35k/sec. I'm wondering if I ask sqlloader maintain the index I cannot use 5 sqlloader at same time.
Thank you very much for your reply.
|
|
|
|
) 1 Vote
] 
Blog
Search
Help
Members
Register
Login
Home
