Re: Very slow archives - the Very Old Page bug perhaps?

Andy Kent wrote: > The plot thickens. I didn't know the timestamp behaved this way. There are lots of operations that increment the timestamp. Even simple queries with only select. This is normal and expected (although a bit undesireble) behavior. > How can I find out what kind of queries cause this? Is it queries or > inefficient execution plans that cause the rapid increase? It's a hard job. We tried to map some jumps with some batch jobs. Queries involving temporary tables (specially in your version) and other queries that force the engine to create temporary space (that use IN for example). > I can certainly see the timestamp incrementing far more quickly on our > live box compared to out test box. Perfectly normal. Timestamp is proporcional to database activity. > 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? Well... teorectically (is this rightly written? ;) ) it could. But I never seen neither heard about it happening. My personal experience was one of the worst I've heard about. But you can monitor how long it takes to wrap around. Create an history of it in some log file with a real timestamp (day, hour) associated. > 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. This could point to another cause for the slowness in your backups. Nevertheless you should take this in consideration. > We're running 7.31.UC5 on Solaris 2.6. Ouch... 7.31.UC5 was the version "we" used when we hit the problem. There were fixes for anormal timestamp jumps and with some special flags you can change the backup behavior. I suppose this would eliminate the problem completely. But didn't use this. The patches fixed most of the problem. Regards.

> Queries involving temporary tables (specially in your version) and other queries that force the engine to create temporary space (that use IN for example). So basically queries that update something - even if that something is only a temporary resource ... ? > Ouch... 7.31.UC5 was the version "we" used when we hit the problem. There were fixes for anormal timestamp jumps and with some special flags you can change the backup behavior. What are the values of these flags please? And by a "fix" do you mean something we can do at this end, or some kind of upgrade? Thanks for your information Andy

Andy Kent wrote: > So basically queries that update something - even if that something is > only a temporary resource ... ? Mainly yes. > What are the values of these flags please? > And by a "fix" do you mean something we can do at this end, or some > kind of upgrade? The flags are only usable in versions >= 7.31.UD1 And they should only be used after technical support advice. The parameter is called CCFLAGS. Here where I am I can't get the value... But it's probably in the release notes of recent versions. Regards.