I have a .dat file containing the required data for the table.It has almost half a million rows.When i inserted the data using SQL LOADER it took
almost whopping 70 minutes .How can i insert the data fast.what should i include in my control file to insert data fast.
Or any other method?2
oracle - 11gR2
Cant use external tables as data is not of fixed length
i did not know your database version or your format in the text file. But one thing can be to create a external table in the database which reference your file and then load the data with for example a CTAS.
1. Server hardware
2. Storage hardware
3. Network hardware, bandwidth, and latency
4. File placement
5. Available CPU
6. Database version number
7. SQL*Loader control file
8. Table structure
13. Materialized Views
15. RAC or single instance
16. ASM or ZFS or NTFS or other dNFS
I can keep going for the next five minutes but I think you get the idea.
We don't know if you have an Exadata connecting to ZFS 7420 with 40GB InfiniBand or a laptop running Windows XP Home Edition. How can we possibly help you?
PS: Answer ALL of the questions I asked ... if you answer only one or two you waste everyone's time as you will still get no useful answer.
PPS: What does fixed length have to do with External Tables? One has nothing to do with the other.
Yes sorry for the vague description.I cant tell you any details now as i am in my home (2 am).I read one article that asked to include direct=true and keep the value of commit point as one of the solutions.
DIRECT=TRUE will give you the direct load ... that will speed things up by throwing everything above the current high water mark ... but will also require locking.
If you want to solve the problem by trying random stuff you can knock yourself out ... maybe you get there ... maybe you don't. My recommendation is you get some sleep and do some research in the morning.
you can use the definitions in your sql*loader file to create the external table. ExTables actually use SQL*Loader as the mechanics that make ET function. Then just insert /* append */ into tableX (select a,b,c,...z from ExtTable); As damorgan stated, there lots of reasons why it is slow. Indexes and triggers being the biggest. I have loaded > 1.8M/sec on a linux desktop system, but it really depends on the damorgan list plus some...
just for information.
if you use the right database version sqlldr has a external_table parameter which helps you. at the other side, if you have the possibiliie a ash or a trace will maybe help to identifiy why your sqlldr gets slow
Only if you answer the questions which is precisely the point of my initial response to your inquiry ... I understand it is 3:00am where you are but you are totally unprepared to ask the question you posted. That is something you must do if you want help other than random suggestions from people who still don't know whether you have an Exadata, a Z11, or an old Tandem running Oracle 7.
Does anyone really think an External Table is so significantly different from SQL*Loader that it has anything to do with the question the OP asked in the original post?
If so please explain it to me because I have no idea what you are thinking.
A) You are going to cut the current 70 minute load time by what percentage by using an External Table?
B) Are you going to increase the current 70 minute load time by utilizing an External Table?
C) Is the load time going to remain essentially the same because the technology chosen has nothing to do with the reason it is taking 70 minutes?
I don't intend to be rude here but there is not a single person on this planet that can answer the question I just posed. If the root cause for the slow load is CPU starvation which of my three options is most likely the right answer? Who has asked the OP for CPU utilization? Page swapping? AWR report? SAN read-write cache utilization? Network utilization?
My plea to everyone is that you gather the facts before you offer recommendations.
having used ext tab extensively, there are a number of things that can be utilized to get the performance to where it needs to be. Like I said 1.8M/sec loaded is a pretty significant accomplishment. It has a lot to do with partitions, indexes (or lack thereof) and sometimes the file system. That being said, not every environment is going to be able to get the speed because of their configurations (table,indexes, tablespaces, storage, to name a few). As we all know, there is no "EASY" button or magic command that can fix poor designs. Not saying they have a poor design, just that each site will be different.
Another feature I use extensively in 11gR2 for moving/updating data is dbms_parallel_execute - but, I have not tested this using external tables.
If the reason for the 70 minute load is CPU starvation the load time will remain 70 minutes.
If the reason for the 70 minute load time is that a DBA implemented workload management the load time will remain 70 minutes.
If the reason for the 70 minute load time is network saturation the load time will remain 70 minutes.
If the reason for the 70 minute load time is the SAN bus is saturated the load time will remain 70 minutes.
If the OP is on a RAC cluster and loading is happening on the wrong node causing remastering the load time will remain 70 minutes.
and I could add many dozens more to this list without breaking a sweat.
None of us have any basis for making any recommendation and again my intent here is not to be rude but sending the OP off on a wild goose chase saying DIRECT LOAD is faster, of course it is, or External Tables are faster, or whatever has no value if you do not first stop and ask the single most important question which is: "Why is the load taking 70 minutes?" Which is a question none of us can answer.
What I am encouraging you to do is stop giving advice when you don't have a single byte of relevant information upon which to base a recommendation.
Are you serious suggesting parallel execution? I do have experience with it. If CPU starvation is the root cause I recommend you try to work through the impact of your suggestion ... you will bring the server to its knees begging for mercy. So again please stop making recommendations until the OP responds with facts.