This discussion is archived
1 2 Previous Next 22 Replies Latest reply: Oct 8, 2012 12:49 AM by Ravetd RSS

Performance issue while inserting data

Rahul_India Journeyer
Currently Being Moderated
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

Edited by: Rahul_India on Oct 6, 2012 1:56 AM
  • 1. Re: Performance issue while inserting data
    phaeus Pro
    Currently Being Moderated
    Hello,
    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.

    Sample:
    http://www.adp-gmbh.ch/ora/misc/ext_table.html

    regards
    Peter
  • 2. Re: Performance issue while inserting data
    damorgan Oracle ACE Director
    Currently Being Moderated
    I have no idea.

    I have no idea about:

    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
    9. Indexes
    10. Constraints
    11. Triggers
    12. Views
    13. Materialized Views
    14. Replication
    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.
  • 3. Re: Performance issue while inserting data
    Rahul_India Journeyer
    Currently Being Moderated
    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.
  • 4. Re: Performance issue while inserting data
    damorgan Oracle ACE Director
    Currently Being Moderated
    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.
  • 5. Re: Performance issue while inserting data
    Rahul_India Journeyer
    Currently Being Moderated
    lol weekends.

    On monday i have to migrate data to more than 100 tables .some of the tables have moore than a million rows :O
  • 6. Re: Performance issue while inserting data
    damorgan Oracle ACE Director
    Currently Being Moderated
    On my laptop, Windows 7 w/ 7200RPM drive, I can easily demonstrate loading 500K rows/second from one table to another.

    Looks like you don't have much work on Monday so you should have plenty of time. ;-)
  • 7. Re: Performance issue while inserting data
    Rahul_India Journeyer
    Currently Being Moderated
    lol
    If you know any faster ways to speed up the insertion you can share it with me :)
  • 8. Re: Performance issue while inserting data
    onedbguru Pro
    Currently Being Moderated
    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...
  • 9. Re: Performance issue while inserting data
    phaeus Pro
    Currently Being Moderated
    Hello,
    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
  • 10. Re: Performance issue while inserting data
    damorgan Oracle ACE Director
    Currently Being Moderated
    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.
  • 11. Re: Performance issue while inserting data
    damorgan Oracle ACE Director
    Currently Being Moderated
    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?
    or
    B) Are you going to increase the current 70 minute load time by utilizing an External Table?
    or
    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.
  • 12. Re: Performance issue while inserting data
    onedbguru Pro
    Currently Being Moderated
    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.
  • 13. Re: Performance issue while inserting data
    damorgan Oracle ACE Director
    Currently Being Moderated
    True but irrelevant.

    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.
  • 14. Re: Performance issue while inserting data
    Stew Ashton Expert
    Currently Being Moderated
    onedbguru wrote:
    Another feature I use extensively in 11gR2 for moving/updating data is dbms_parallel_execute - but, I have not tested this using external tables.
    The important thing is for the OP to answer Dan's questions as clearly as possible.

    I would just like to add a word about dbms_parallel_execute: I don't see how it adds anything for external tables. External tables are based on files, which are read serially from the beginning.

    If the external table is based on a list of files, which is possible, then native SQL parallelism could be used - which is not a recommendation because there is nothing to recommend at this point.

    Since the OP has to load many tables, do-it-yourself parallelism is easy by doing more than one load at a time. No need for dbms_parallel_execute.

    The important thing is for the OP to answer Dan's questions as clearly as possible.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points