Insert taking too much of time.
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 23

Thread: Insert taking too much of time.

Hybrid View

  1. #1
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    Hi there,

    It takes less than 1 second when I run one query which has two UNION ALL.

    But, when I use the same Query to insert the rows into a table, it takes too much of time and seems like it hangs. It does not come out. Then, I have kill the session.

    I altered the table and made it LOGGING = NO to avoid Roll back segment and Redo operations.

    I have to insert the queries rows in one temp. table in Pre-report trigger.

    Total rows = 79000.

    Is there any way to accelerate Insert operation?

    Thanks in Adv.

    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  2. #2
    Join Date
    Jan 2001
    Posts
    3,131
    SQL* copy command, you can set the arraysize and copycommit. I'm not sure if this will speed it up.

    What about adding a query to an export file and using IMP/EXP, providing this is 8i.

    MH

  3. #3
    Join Date
    Dec 2000
    Location
    Virginia, USA
    Posts
    455
    You may have lots of indexes created, also check that.

  4. #4
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    I forgot to write that we are on 8.0.5.

    Mr.Hanky,

    I have to run Insert statement from the D2K reports 2.5 in Before-Report trigger to insert the rows as per User inputs and then generate the report.

    So, I am not sure whether I can use SQL* copy command.

    I cannot use a query to an export file and using IMP/EXP, as I am not on 8i.

    irehman,

    The table in which I am inserting rows is a temp.table without any Indexes.

    Thanks both,

    Any more ideas?



    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Make sure your db is tuned; i/o is distributed, no rbs contention, good buffer hit ratio, etc.
    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
    USA
    Posts
    620
    Originally posted by marist89
    Make sure your db is tuned; i/o is distributed, no rbs contention, good buffer hit ratio, etc.
    Hi,

    I think Database and buffer hit ratio are fine. I doubt about RBS.

    SELECT CLASS, COUNT FROM V$WAITSTAT WHERE CLASS like '%undo%';

    shows foll. result

    save undo block 0
    save undo header 0
    system undo header 0
    system undo block 0
    undo header 5 <-- Problem?
    undo block 0

    But the table in which I am inserting rows is defined as LOGGING = NO at the time of creation.

    Any suggestion?

    Thanks,
    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  7. #7
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    NOLOGGING is one of the most misunderstood topics in Oracle Database Administration. Even if you have NOLOGGING turned on, not all operations are NOLOG operations. See http://technet.oracle.com/docs/produ...dlins.htm#4418 for details.
    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."

  8. #8
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    Originally posted by marist89
    NOLOGGING is one of the most misunderstood topics in Oracle Database Administration. Even if you have NOLOGGING turned on, not all operations are NOLOG operations. See http://technet.oracle.com/docs/produ...dlins.htm#4418 for details.
    Thanks for clearing the subject marist89. I will try Direct-Load Insert with Append hint. I also think that because I am inserting about 80000 rows in one shot / one transaction, it is taking this much time. I commit transaction after insert. Will it make any difference if I divide single 80000 rows insert transaction into 1000 rows (commit) per transaction?

    Pl. advise.

    Thanks,
    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  9. #9
    Join Date
    Jan 2001
    Posts
    3,131
    [[/B][/QUOTE]
    Thanks for clearing the subject marist89. I will try Direct-Load Insert with Append hint. I also think that because I am inserting about 80000 rows in one shot / one transaction, it is taking this much time. I commit transaction after insert. Will it make any difference if I divide single 80000 rows insert transaction into 1000 rows (commit) per transaction?

    Pl. advise.

    Thanks, [/B][/QUOTE]

    Setting up a commit will certainly prevent you from blowing up your rollback segment, I don't think it will speed it up though. If anything it will slow it down some, Jeff?

    MH

  10. #10
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by Mr.Hanky
    If anything it will slow it down some, Jeff?
    Agree.
    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."

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