DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 20

Thread: Rollback Segments

  1. #1
    Join Date
    Feb 2003
    Posts
    19

    Rollback Segments

    I am running a query selecting well over 150 columns of data out of our database. Hence it takes forever to run. I am getting the 'unable to extend rollback segment'...... and therefore it never completes. I have set the rollback space to unlimited and even added datafiles and still nothing. I know the suggestion has been to do commits (ie when inserting or updating) but what about on a single select statement?

  2. #2
    Join Date
    May 2002
    Posts
    2,645
    See Note 1066542.6 on MetaLink.

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    You are performing SELECT and you are getting "unable to extend rollback segment"? That sounds quite impossible! Can you give us some more detail about your problem? Does your select use some user created functions that might be writting something to the database?

    And BTW, the number of columns you are selecting has practicaly no influence on the execution time of your query. You select 1 or 10 or 150 columns - the execution time will be (roughly) the same.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Feb 2003
    Posts
    19
    Believe it or not it's on a select statement. The only thing I can think of is that there are some changes being made to the rows while I am selecting them. These are bank accounts so I am wondering if that could be causing the problem, since they are updated as transactions are performed. But I am not sure of a way around them. Since we are online 24 x 7 I could be running into this problem constantly so there isn't a time when I can run this where there might not be a conflict. Any ideas how to get around this?

  5. #5
    Join Date
    Jul 2000
    Posts
    521
    How long it takes before the SELECT fails ? Have you optimised the SELECT statement itself ? If not, that should be the first thing.

    Whats the reason behind doing this huge SELECT as ONE query ? Can you do it in separate SELECTs ?

    Is your rollback tablespace and the segments sized adequately ?
    svk

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by bfunky
    Believe it or not it's on a select statement. The only thing I can think of is that there are some changes being made to the rows while I am selecting them.
    Belive it or not, this is simply impossible. Can you post exact ORA-n error messsage you are getting, with exact error text?

    I belive you are hitting the infamous ORA-1555 "snapshot too old, rollback segment to small" error, but not the one that says "unable to extend rollback segment" as you specified in your first post. Those are two different errors that have two fundamentaly different causes. The first one happens only to selects but never to inserts/updates/deletes, while the other one happens only to inserts/updates/deletes, but never to selects.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  7. #7
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    Originally posted by bfunky
    Believe it or not it's on a select statement. The only thing I can think of is that there are some changes being made to the rows while I am selecting them. These are bank accounts so I am wondering if that could be causing the problem, since they are updated as transactions are performed. But I am not sure of a way around them. Since we are online 24 x 7 I could be running into this problem constantly so there isn't a time when I can run this where there might not be a conflict. Any ideas how to get around this?
    Do u have (Manuel) auditing (For SELECT statement) enabled. For more details read "Oracle One-on-One". But this is not an Oracle generaic behavior. Can be a site specific implementation of auditing on SELECT statements and the auditing table may hitting the error. Are you selecting from a 'View'..? Check for any "INSTEAD OF" triggers on the view. Also check the alert log, on which table is getting the error. The query table and the error hitting table must be different.

    keep in mind that a SELECT statement cann't produce this error.

    Tom Kyte explaind, how you can implement auditing on "SELECT"

    Regards

    Thomas
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  8. #8
    Join Date
    Feb 2003
    Posts
    19
    Hi All,
    Thanks for all the help I have since figured this out. Did a little tweaking with the sql and got it to stop looping and running up my temp database file.

  9. #9
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by bfunky
    Hi All,
    Thanks for all the help I have since figured this out. Did a little tweaking with the sql and got it to stop looping and running up my temp database file.
    Ah! So it was nothing about 'unable to extend rollback segment', right? You were in fact running out of temp space, not rollback segments, yes?
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  10. #10
    Join Date
    Feb 2003
    Posts
    19
    Yes it was about the 'unable to extend rollback segments' on a select statement. But I think that once the database file had grown to an unimagineable size that my extents maxed out on that file and caused the error to appear. All is good now.

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