This content has been marked as final. Show 6 replies
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.
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.
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.
mtefft wrote: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.
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?
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.