Professional Web Applications Themes

Intermittent Commits - Oracle Server

Hye ! I have a query which selects from several tables and inserts into a table X. Two ways of writing this query are (A) fast but risky (B) slow but certain (A) Writing this select in one bulk INSERT SELECT statement will be the fastest way, however we only commit once at the end. If something wrong happens then nothing will be stored in the new table and in addition we should wait a period of time for the database to rollback its work. (B) Now performing the same INSERT in a cursor and COMMITing after each INSERT is ...

  1. #1

    Default Intermittent Commits

    Hye !


    I have a query which selects from several tables and inserts into a
    table X. Two ways of writing this query are (A) fast but risky (B)
    slow but certain


    (A) Writing this select in one bulk INSERT SELECT statement will be
    the fastest way, however we only commit once at the end. If something
    wrong happens then nothing will be stored in the new table and in
    addition we should wait a period of time for the database to rollback
    its work.

    (B) Now performing the same INSERT in a cursor and COMMITing after
    each INSERT is slower but more certain, because if something happens
    which will stop
    the query, then it is possible to restart from where we stopped.

    What I am looking for is a solution in between, that will balance
    speed and risk for example to commit every chunk of records. The
    reason for that is not space but time. When this query is run the
    database should be turned offline and the downtime is minimal. By
    introducing more commits, it will be much simpler to forecast how much
    time will it take to perform this INSERT, better feedback of what is
    happening. Any ideas??

    I am using Oracle 8i and the INSERT varies from 100000 to 500000
    records.


    TIA
    Michal
    Michael Guest

  2. #2

    Default Re: Intermittent Commits

    Michael wrote:
    >
    > Hye !
    >
    > I have a query which selects from several tables and inserts into a
    > table X. Two ways of writing this query are (A) fast but risky (B)
    > slow but certain
    >
    > (A) Writing this select in one bulk INSERT SELECT statement will be
    > the fastest way, however we only commit once at the end. If something
    > wrong happens then nothing will be stored in the new table and in
    > addition we should wait a period of time for the database to rollback
    > its work.
    >
    > (B) Now performing the same INSERT in a cursor and COMMITing after
    > each INSERT is slower but more certain, because if something happens
    > which will stop
    > the query, then it is possible to restart from where we stopped.
    >
    > What I am looking for is a solution in between, that will balance
    > speed and risk for example to commit every chunk of records. The
    > reason for that is not space but time. When this query is run the
    > database should be turned offline and the downtime is minimal. By
    > introducing more commits, it will be much simpler to forecast how much
    > time will it take to perform this INSERT, better feedback of what is
    > happening. Any ideas??
    >
    > I am using Oracle 8i and the INSERT varies from 100000 to 500000
    > records.
    >
    > TIA
    > Michal
    If you can use insert with the 'append' hint then the rollback process
    is virtually nil - but have a read up on the implications of using this.

    otherwise take a look at bulk binding / bulk collection in the PL/SQL
    manual with the LIMIT clause

    hth
    connor
    --
    =========================
    Connor McDonald
    [url]http://www.oracledba.co.uk[/url]

    "Some days you're the pigeon, some days you're the statue"
    Connor McDonald Guest

  3. #3

    Default Re: Intermittent Commits

    [email]mikeeriainteria.pl[/email] (Michael) wrote in message news:<875e251b.0307070637.620d8c49posting.google. com>...
    > Hye !
    >
    >
    > I have a query which selects from several tables and inserts into a
    > table X. Two ways of writing this query are (A) fast but risky (B)
    > slow but certain
    >
    >
    > (A) Writing this select in one bulk INSERT SELECT statement will be
    > the fastest way, however we only commit once at the end. If something
    > wrong happens then nothing will be stored in the new table and in
    > addition we should wait a period of time for the database to rollback
    > its work.
    >
    > (B) Now performing the same INSERT in a cursor and COMMITing after
    > each INSERT is slower but more certain, because if something happens
    > which will stop
    > the query, then it is possible to restart from where we stopped.
    >
    > What I am looking for is a solution in between, that will balance
    > speed and risk for example to commit every chunk of records. The
    > reason for that is not space but time. When this query is run the
    > database should be turned offline and the downtime is minimal. By
    > introducing more commits, it will be much simpler to forecast how much
    > time will it take to perform this INSERT, better feedback of what is
    > happening. Any ideas??
    >
    > I am using Oracle 8i and the INSERT varies from 100000 to 500000
    > records.
    >
    >
    > TIA
    > Michal

    500000 inserts is not THAT many. how fast is your machine? how long
    does it take to do this insert?

    here is a 'possible' middle ground. This is really only practicle if
    there is a limited number of fields that you are inserting.

    its partially explained on asktom.oracle.com

    bulk collect the records into a series of pl/sql tables(in 8i, i think
    you can only bulk collect into 1 dimensional arrays... i keep
    forgetting when they changed it). this is where the slow down comes
    from. how many pl/sql tables do you need?

    use the limit clause. so you get say 2000 at a time.

    then do a forall insert
    commit

    bulk collect into is an array select and forall allows you to insert
    as an array.

    then you can have a loop and keep running until the query is complete.
    See that website for details.

    if you have alot of columns... its not worth it.

    500k records isnt that many... Id just run it as an insert select. how
    often does your server go down? are you on windows and you have to do
    periodic reboots?
    Ryan Gaffuri Guest

  4. #4

    Default Re: Intermittent Commits

    Michael wrote:
    > Hye !
    >
    >
    > I have a query which selects from several tables and inserts into a
    > table X. Two ways of writing this query are (A) fast but risky (B)
    > slow but certain
    >
    >
    > (A) Writing this select in one bulk INSERT SELECT statement will be
    > the fastest way, however we only commit once at the end. If something
    > wrong happens then nothing will be stored in the new table and in
    > addition we should wait a period of time for the database to rollback
    > its work.
    >
    > (B) Now performing the same INSERT in a cursor and COMMITing after
    > each INSERT is slower but more certain, because if something happens
    > which will stop
    > the query, then it is possible to restart from where we stopped.
    >
    > What I am looking for is a solution in between, that will balance
    > speed and risk for example to commit every chunk of records. The
    > reason for that is not space but time. When this query is run the
    > database should be turned offline and the downtime is minimal. By
    > introducing more commits, it will be much simpler to forecast how much
    > time will it take to perform this INSERT, better feedback of what is
    > happening. Any ideas??
    >
    > I am using Oracle 8i and the INSERT varies from 100000 to 500000
    > records.
    >
    >
    > TIA
    > Michal
    I've done inserts via selects over a database link; about 7 million
    records in 5 to 10 minutes.
    Commit after all is done (hence: scenario A); B is hard to restart,
    and anything in between a certain recipe for ora-1555

    --
    Regards, Frank van Bortel

    Frank Guest

  5. #5

    Default Re: Intermittent Commits

    "Michael" <mikeeriainteria.pl> wrote in message
    news:875e251b.0307070637.620d8c49posting.google.c om...
    > Hye !
    >
    >
    > I have a query which selects from several tables and inserts into a
    > table X. Two ways of writing this query are (A) fast but risky (B)
    > slow but certain
    >
    >
    > (A) Writing this select in one bulk INSERT SELECT statement will be
    > the fastest way, however we only commit once at the end. If something
    > wrong happens then nothing will be stored in the new table and in
    > addition we should wait a period of time for the database to rollback
    > its work.
    >
    > (B) Now performing the same INSERT in a cursor and COMMITing after
    > each INSERT is slower but more certain, because if something happens
    > which will stop
    > the query, then it is possible to restart from where we stopped.
    >
    > What I am looking for is a solution in between, that will balance
    > speed and risk for example to commit every chunk of records. The
    > reason for that is not space but time. When this query is run the
    > database should be turned offline and the downtime is minimal. By
    > introducing more commits, it will be much simpler to forecast how much
    > time will it take to perform this INSERT, better feedback of what is
    > happening. Any ideas??
    >
    > I am using Oracle 8i and the INSERT varies from 100000 to 500000
    > records.
    >
    The answer should be determined by the logical requirements, not the
    physical.
    If you want feedback on progress on an uncommitted long-running transaction,
    there are plenty of ways to provide that. But how long is a half-million row
    insert taking anyway?

    Regards,
    Paul



    Paul Brewer Guest

  6. #6

    Default Re: Intermittent Commits

    Hello,

    I think you should keep the solution A. You have 2 possibilities to make it
    work for sure.
    (A1) You can have a big rolback segment that can sure handle the maximum
    transaction size and you attach it to your transaction
    SET TRANSACTION USE ROLLBACK SEGMENT rbsbig ;
    INSERT INTO a SELECT * FROM B ;
    COMMIT ;
    You can take the big rollback segment online before the transaction and
    offline just after

    (A2) If you are using Oracle 9i you can use a new feature called Resumable
    Space Allocation.
    Before you begin your transaction you ALTER SESSION ENABLE RESUMABLE (or
    some thing like that).
    The effect is that when you encounter an error such as a rollback segment
    that cannot extend, your transaction is suspended instead of rollbacked.
    This leaves you the time to solve your problem. When the problem is solved,
    the transaction goes on automatically.


    "Connor McDonald" <connor_mcdonald> wrote in message
    news:3F099308.73BE...
    > Michael wrote:
    > >
    > > Hye !
    > >
    > > I have a query which selects from several tables and inserts
    into a
    > > table X. Two ways of writing this query are (A) fast but risky (B)
    > > slow but certain
    > >
    > > (A) Writing this select in one bulk INSERT SELECT statement will be
    > > the fastest way, however we only commit once at the end. If something
    > > wrong happens then nothing will be stored in the new table and in
    > > addition we should wait a period of time for the database to rollback
    > > its work.
    > >
    > > (B) Now performing the same INSERT in a cursor and COMMITing after
    > > each INSERT is slower but more certain, because if something happens
    > > which will stop
    > > the query, then it is possible to restart from where we stopped.
    > >
    > > What I am looking for is a solution in between, that will balance
    > > speed and risk for example to commit every chunk of records. The
    > > reason for that is not space but time. When this query is run the
    > > database should be turned offline and the downtime is minimal. By
    > > introducing more commits, it will be much simpler to forecast how much
    > > time will it take to perform this INSERT, better feedback of what is
    > > happening. Any ideas??
    > >
    > > I am using Oracle 8i and the INSERT varies from 100000 to 500000
    > > records.
    > >
    > > TIA
    > > Michal
    >
    > If you can use insert with the 'append' hint then the rollback process
    > is virtually nil - but have a read up on the implications of using this.
    >
    > otherwise take a look at bulk binding / bulk collection in the PL/SQL
    > manual with the LIMIT clause
    >
    > hth
    > connor
    > --
    > =========================
    > Connor McDonald
    > [url]http://www.oracledba.co.uk[/url]
    >
    > "Some days you're the pigeon, some days you're the statue"

    David COSTE Guest

Similar Threads

  1. #39910 [NEW]: latest filter commits breaks compilation
    By judas dot iscariote at gmail dot com in forum PHP Bugs
    Replies: 1
    Last Post: December 20th, 07:48 PM
  2. Intermittent stalling
    By CreatopChris in forum Coldfusion Server Administration
    Replies: 0
    Last Post: January 10th, 01:55 AM
  3. Intermittent Network
    By Gary Sprigg in forum Windows Networking
    Replies: 0
    Last Post: July 14th, 03:06 AM
  4. Intermittent Postback Bug
    By Al in forum ASP.NET General
    Replies: 0
    Last Post: July 1st, 07:42 PM
  5. autocommit ON but no commits visible in v$sqlarea
    By Hans de Git in forum Oracle Server
    Replies: 11
    Last Post: January 8th, 08:57 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139