direct load
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: direct load

  1. #1
    Join Date
    Nov 2000
    Location
    Birmingham, UK
    Posts
    360

    direct load

    We've got a load process thats is taking a huge amount of time (9.2.0.4 Solaris 8) and from looking at the code the statements used are just using insert append without disabling indexes etc. My thoughts on speeding this up are:
    Turn logging off for tables invlolved
    mark indexes unusable
    alter session set skip_unusable_indexes=true
    load data using insert append
    rebuild indexes unrecoverable
    turn logging back on for tables

    any comments on this being a reasonable high level approach or not?

  2. #2
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    Hi.

    Alot depends on how the DB is being used during this load. If you have got users on it then disabling the indexes is really going to p*ss them off.

    Also, you skip unusable indexes at session level. What about the other sessions accessing the data?

    You've not said what the source of the data is. Have you identified that it is the insert portion of the process that is definitely at fault, not the read.

    I don't want to sound like I'm teaching your grandmother to suck eggs, but have you tried this out and measured the total time. I've worked on a few systems where the index rebuilds alone would take many hours. It may be that using your method the data would get in quicker, but you would be rebuilding indexes for many hours after...

    Also, do you have any triggers on the tables? Can they be diabled and reenabled without affecting the integrity of the data? What happens if they are disabled and a user does somethig that would normally fire the trigger?

    If possible I try to leave the schema alone during data loads. My approach is, if the system can't deal with the data loads using "normal" methods, I've probably got the system design wrong, but there are always exceptions ;-)

    Cheers

    Tim...
    Last edited by TimHall; 06-21-2005 at 04:00 AM.
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  3. #3
    Join Date
    Nov 2000
    Location
    Birmingham, UK
    Posts
    360
    Quote Originally Posted by TimHall
    Hi.

    Alot depends on how the DB is being used during this load. If you have got users on it then disabling the indexes is really going to p*ss them off.

    Also, you skip unusable indexes at session level. What about the other sessions accessing the data?

    You've not said what the source of the data is. Have you identified that it is the insert portion of the process that is definitely at fault, not the read.

    I don't want to sound like I'm teaching your grandmother to suck eggs, but have you tried this out and measured the total time. I've worked on a few systems where the index rebuilds alone would take many hours. It may be that using your method the data would get in quicker, but you would be rebuilding indexes for many hours after...

    Also, do you have any triggers on the tables? Can they be diabled and reenabled without affecting the integrity of the data? What happens if they are disabled and a user does somethig that would normally fire the trigger?

    If possible I try to leave the schema alone during data loads. My approach is, if the system can't deal with the data loads using "normal" methods, I've probably got the system design wrong, but there are always exceptions ;-)

    Cheers

    Tim...
    Thanks for the reply Tim,

    We're planning to have the system "to ourselves" for this process so there should be no other sessions accessing the data (its a one off data conversion). I've yet to see the breakdown of timings of the last attempt (80 hrs!) but was just thinking of a high level process that we could benchmark against the current method. The data is initally loaded into staging tables then inserted into the application tables using direct insert.

    Fraze

  4. #4
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    OK. So the read should not be a major problem. I was just concerned you might have some queries from inefficient pipelined table functions or something like that.

    If you have the system to yourself then you are laughing.

    One thing to consider, if you are removing indexes from a number of tables you are going to affect you FK validations. You will need to way up the impact of this compared to write speed improvements. It may be that you could disable the FKs also, but be careful not to load rubbish data or you will spend the rest of your life fixing the FK issues.

    Cheers

    Tim...
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    I would trace your load process and see where most of the time is being spent. You may be trying to speed up something that doesn't take the greatest amount of time.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  6. #6
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    If you've got alot of PL/SQL running then the profiler is a great way to see what lines of code are taking the longest to complete. See:

    http://www.oracle-base.com/articles/...S_PROFILER.php

    As Jeff says, tracing the SQL is a very good idea also. This might help:

    http://www.oracle-base.com/articles/...dTkprof10g.php

    Cheers

    Tim...
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  7. #7
    Join Date
    Nov 2000
    Location
    Birmingham, UK
    Posts
    360
    Thanks for the replies

  8. #8
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Have you considered, splitting the flat file into multiple files, and load them in parallel?
    How big is the flat file?

    Drop indexes.
    Use direct path option of SQL LOADER, and recreate the indexes in parallel later. This approach works faster than any other method.

    Tamil

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width