Very slow archives - the Very Old Page bug perhaps?

Our ontape archives have suddenly gone from about 3 hours to 40+ hours. Often they fail or we end up having to abort them because they chomp up all the temp space on the system. The tape drive seems to spend most of its time idle so teh problem seems to be at the database end of things. I've read back all the threads on the Very Old Page bug and it seems quite likely that this is what we're hitting. Questions: - Surely if what's happening is timestamps being updated, surely we'd get one very slow archive and then return to normal? - How old does a page have to be to be considered Very Old? - Is there some way we can circumvent the problem, e.g. force the pages to seem new? Would an UPDATE work (and would it reach all the indexes if we didn't actually change anything), or even a cold restore? We're on 7.31 and, for commercial reasons I can't make public, upgrading isn't an option. Andy Kent

Andy Kent wrote: > Our ontape archives have suddenly gone from about 3 hours to 40+ > hours. Often they fail or we end up having to abort them because they > chomp up all the temp space on the system. The tape drive seems to > spend most of its time idle so teh problem seems to be at the database > end of things. > > I've read back all the threads on the Very Old Page bug and it seems > quite likely that this is what we're hitting. Questions: > You can try to monitor the arcbackup threads stack with onstat -g stk... Maybe you can catch the call to the function in question. > - Surely if what's happening is timestamps being updated, surely we'd > get one very slow archive and then return to normal? Probably but not necessarily. It all depends on the timestamp growth rate. You can monitor it with some SQL: select value sh_stamp from sysmaster:sysshmhdr where name matches "stamp" I have a script that run from time to time, logging the previous value. When the value steps above a fixed threshold it alerts the DBAs Beware that the timestamp increase rate can be big. But maybe you can see "bigger" steps at specific times. There are some queries which can make it go wild. Some older versions do it with the use of temp tables. Even with current versions some queries ( wiht "IN" clause for example) can force big steps. > - How old does a page have to be to be considered Very Old? It's related to the algorithm itself. The timestamp can be represented by a circular range of values. Assuming last level 0 backup was in "0 degrees" it's somewhere in the third or fourth quadrant. (I'd like to draw you a picture ;) ) So, it's not a question of time. It all depend on the activity of your engine and the fact that it has (or not) pages that never change. This can happen when you store historic data in your instance. If it's possible you can move this data to another instance. But this have obvious disavantages... > - Is there some way we can circumvent the problem, e.g. force the > pages to seem new? Would an UPDATE work (and would it reach all the > indexes if we didn't actually change anything), or even a cold > restore? A dummy update (set column = column) will change the timestamp for data pages. I do not know at this time if index pages are changed... > We're on 7.31 and, for commercial reasons I can't make public, > upgrading isn't an option. Which 7.31? 7.31.ud1 had some fixes (and a lot of regressions) regarding this. My customer is using 7.31.FD4. The anormal timestamp increase was greatly reduced. We moved from a critical situation (a long backup per week) to a situation where we almost forgot about the problem. We keep the script monitoring it just to be informed ;) So, the specific release can make a lot of difference. Monitoring and looking for specific queries can also help. Regards.

The plot thickens. I didn't know the timestamp behaved this way. How can I find out what kind of queries cause this? Is it queries or inefficient execution plans that cause the rapid increase? I can certainly see the timestamp incrementing far more quickly on our live box compared to out test box. Also I can't see why we are getting very slow archives on consecutive days. Surely ARC_VERY_OLD_PAGE would catch a whole dbspace - or do only bits of each dbspace get updated each time? The timestamp can't be wrapping around within 24 hours - can it? We have had this problem before - typically we would get one slow archive a week but then the next ones would be OK. Why should we now be getting several consecutive slow archives? The system activity hasn't grown within the last few months. We're running 7.31.UC5 on Solaris 2.6. Andy Fernando Nunes <spam@domus.online.pt> wrote in message news:<br728u$1kj6$1@ID-161111.news.uni-berlin.de>... > Andy Kent wrote: > > Our ontape archives have suddenly gone from about 3 hours to 40+ > > hours. Often they fail or we end up having to abort them because they > > chomp up all the temp space on the system. The tape drive seems to > > spend most of its time idle so teh problem seems to be at the database > > end of things. > > > > I've read back all the threads on the Very Old Page bug and it seems > > quite likely that this is what we're hitting. Questions: > > > > You can try to monitor the arcbackup threads stack with onstat -g stk... > Maybe you can catch the call to the function in question. > > > - Surely if what's happening is timestamps being updated, surely we'd > > get one very slow archive and then return to normal? > > Probably but not necessarily. It all depends on the timestamp growth rate. > You can monitor it with some SQL: > > select value sh_stamp from sysmaster:sysshmhdr where name matches "stamp" > > I have a script that run from time to time, logging the previous value. When the value steps above a fixed threshold it alerts the DBAs > > Beware that the timestamp increase rate can be big. But maybe you can see "bigger" steps at specific times. > There are some queries which can make it go wild. > Some older versions do it with the use of temp tables. Even with current versions some queries ( wiht "IN" clause for example) can force big steps. > > > > - How old does a page have to be to be considered Very Old? > > It's related to the algorithm itself. The timestamp can be represented by a circular range of values. > Assuming last level 0 backup was in "0 degrees" it's somewhere in the third or fourth quadrant. (I'd like to draw you a picture ;) ) > So, it's not a question of time. It all depend on the activity of your engine and the fact that it has (or not) pages that never change. > This can happen when you store historic data in your instance. If it's possible you can move this data to another instance. But this have obvious disavantages... > > > > - Is there some way we can circumvent the problem, e.g. force the > > pages to seem new? Would an UPDATE work (and would it reach all the > > indexes if we didn't actually change anything), or even a cold > > restore? > > A dummy update (set column = column) will change the timestamp for data pages. I do not know at this time if index pages are changed... > > > > We're on 7.31 and, for commercial reasons I can't make public, > > upgrading isn't an option. > > Which 7.31? 7.31.ud1 had some fixes (and a lot of regressions) regarding this. > My customer is using 7.31.FD4. The anormal timestamp increase was greatly reduced. We moved from a critical situation (a long backup per week) to a situation where we almost forgot > about the problem. We keep the script monitoring it just to be informed ;) > > So, the specific release can make a lot of difference. Monitoring and looking for specific queries can also help. > > > Regards.