Re: adding rowids to a frag'd table

I'm very rusty, but the other guys should be able to yay or nay my scenario All changed pages go through the physical log, even for raw tables (am I right guys?) adding rowids forces a physical rewrite (right guys?) so it's going to pump a lot of pages through the physical log. This will proceed fairly bloody quickly. When the physical log hits 75% (very quickly if it's small) then it will force a checkpoint. If there are any open transactions from anyone on the engine, then .... now I'm starting to lose the plot. Anyway, have you tried this process on a totally quiet engine? Nobody on any db at all? If the answer is no, maybe you need to make it happen.

Andrew Clarke wrote: > I'm very rusty, but the other guys should be able to yay or nay my scenario > > All changed pages go through the physical log, even for raw tables (am I > right guys?) Physical log will get before images of any changed page... > adding rowids forces a physical rewrite (right guys?) so it's going to > pump a lot of pages through the physical log. This will proceed fairly > bloody quickly. Adding rowids is a "slow" alter. > > When the physical log hits 75% (very quickly if it's small) then it will > force a checkpoint. If there are any open transactions from anyone on > the engine, then .... now I'm starting to lose the plot. Wow... You must get the plot! :) Having open transactions at checkpoint time is normal/usual/trivial/etc... > > Anyway, have you tried this process on a totally quiet engine? Nobody on > any db at all? If the answer is no, maybe you need to make it happen. > > I liked your previous post better :) A LTX is a transaction that "has seen" LTXHWM % logs advance since it started. A simple "begin work;" without anything else, will surely be considered a long tx.... just give it enough time and some engine activity. Regards.

> Fernando wrote: > > > When the physical log hits 75% (very quickly if it's small) then it will > > force a checkpoint. If there are any open transactions from anyone on > > the engine, then .... now I'm starting to lose the plot. > > Wow... You must get the plot! :) > I swear, I haven't really touched an engine in earnest since version 9.3. I can probably legitimately tell the young whipper-snappers that I've forgotten more than they've learned.... /adjusts bifocals, braces and seat donut. > Having open transactions at checkpoint time is normal/usual/trivial/etc... > Yeah, but the bit I'm missing is what exactly will happen if the physical log is the choke-point. Dammit. > > I liked your previous post better :) > A LTX is a transaction that "has seen" LTXHWM % logs advance since it > started. A simple "begin work;" without anything else, will surely be > considered a long tx.... just give it enough time and some engine activity. > I'm trying, fuzzily, to picture how other sessions might be screwing with his process or vice-versa. But the physical log can easily recycle if the checkpoint succeeds. A little help here? What could be happening to him? PS - browsed your page/blog the other day. Nice collection of stuff. bookmarked!

Andrew Clarke wrote: > > Fernando wrote: > > > > > > > When the physical log hits 75% (very quickly if it's small) then it > will > > > > force a checkpoint. If there are any open transactions from anyone on > > > > the engine, then .... now I'm starting to lose the plot. > > > > > > Wow... You must get the plot! :) > > > > > I swear, I haven't really touched an engine in earnest since version > 9.3. I can probably legitimately tell the young whipper-snappers that > I've forgotten more than they've learned.... /adjusts bifocals, braces > and seat donut. > > > Having open transactions at checkpoint time is > normal/usual/trivial/etc... > > > > > Yeah, but the bit I'm missing is what exactly will happen if the > physical log is the choke-point. Dammit. When the physical log fills up too quickly you may get more checkpoints... And in version 11+ you may start to have blocking checkpoints instead of the new non-blocking checkpoints. But it will not cause long txs > > I liked your previous post better :) > > > A LTX is a transaction that "has seen" LTXHWM % logs advance since it > > > started. A simple "begin work;" without anything else, will surely be > > > considered a long tx.... just give it enough time and some engine > activity. > > > > > I'm trying, fuzzily, to picture how other sessions might be screwing > with his process or vice-versa. But the physical log can easily recycle > if the checkpoint succeeds. A little help here? What could be happening > to him? > You are possibly on the right track. You noticed, and well in my opinion, that by starting an explicit transaction he may get into an unecessary long tx. But as the OP wrote in another post, it's a way to avoid blowing the locks... > PS - browsed your page/blog the other day. Nice collection of stuff. > bookmarked! Thanks a lot. I only wish I had more time for it...