6 Replies Latest reply: Jan 16, 2013 9:35 AM by mtefft RSS

    Is it possible to pin UNDO?

    mtefft
      We are heavy users of Total Recall (Flashback Data Archive). We have been advised that a key to suitable performance is to have the necessary UNDO still in the buffer cache when the FBDA background process kicks in to sweep the UNDO into the archive.

      Is there a way to 'pin' our UNDO so that we have greater assurance on this?

      Thanks,
      Mike
        • 1. Re: Is it possible to pin UNDO?
          damorgan
          Pin UNDO has no meaning. It sounds like "Pin the sysaux tablespace" which is equally meaningless.

          If you want to throw money at a problem you likely do not have buy some solid state drives, use them to build a tablespace, and put your flashback archive tablespace there. But unless you can provide metrics demonstrating you have a specific issue this looks a case of compulsive tuning disorder in which case you should take two beers, a long nap, and call us in the morning.
          • 2. Re: Is it possible to pin UNDO?
            mtefft
            Many beers have been consumed, and many more will be. We do have real and serious problems on this.

            It is not the flashback archive tablespace that is the issue. It is UNDO.

            The advice from the Total Recall development team was: it is important that to-be-processed UNDO should still be available in the buffer cache by the time the FBDA background process needs it. We are looking for ways to achieve that.
            • 3. Re: Is it possible to pin UNDO?
              damorgan
              If the devs at Oracle gave you that advise then go right back to them and ask "How?"

              To be quite honest if their product does not work with the default database configuration it is their responsibility them to tell the community how to use it. Surely they don't expect all of us to spend money for a license, try to use it, fail, and then hopefully find the answer by bumping into them at OpenWorld.

              They need to give you the answer (one that hopefully you will share with us) and publish a white paper. Tell them they need to contact Eric P ... they'll know who I am referring to and get this into the online docs at Tahiti.
              • 4. Re: Is it possible to pin UNDO?
                Jonathan Lewis
                mtefft wrote:
                We are heavy users of Total Recall (Flashback Data Archive). We have been advised that a key to suitable performance is to have the necessary UNDO still in the buffer cache when the FBDA background process kicks in to sweep the UNDO into the archive.

                Is there a way to 'pin' our UNDO so that we have greater assurance on this?
                Like Dan, I am a little surprised (though not totally astounded) that Oracle hadn't thought of this possibility and come up with a strategy to deal with it.

                If you're confident that keeping X GB of recent undo in memory is a good idea, then you could simply create an undo tablespace with a non-standard block size and set up a separate cache of slightly more than X GB for that block size. It's probably rarely done, and may reveal odd little bugs that have never been stressed before (there's a quirky little one you can get with a 32KB block size for undo on my blog: http://jonathanlewis.wordpress.com/2009/03/22/block-size-again/ ) but it might give you the performance edge you need at the moment you need it. The drawback is the amount of cache (for a fixed amount of memory) you deny to everything else.

                Regards
                Jonathan Lewis
                • 5. Re: Is it possible to pin UNDO?
                  Cjmurray-Oracle
                  The Flashback material in the manual in question is being revised for the next release to include additional usage information and guidance, including in the section on performance guidelines. The revisions reflect significant helpful documentation review comments from the Flashback team.
                  • 6. Re: Is it possible to pin UNDO?
                    mtefft
                    Is there any way to get the performance guidance information now? We are a Total Recall customer on 11.2.0.3, we have participated in Total Recall 12c beta event, etc.