RE: Moving OPTSTAT objects from SYSAUX

Joel,

I was the author of that sidebar :-) Oracle 10g Insider solutions was written with 10.1.0.2 (patched to .4 later) and the Author's Beta release 2 (10.2.0.0). This particular info was based on Metalink Note 243246.1 (last updated 02-May-06 so nothing has changed "officially"). I re-looked at this on a 10.2.0.1.0 instance and it hadn't changed.... The bug is also sparse on details :D

Information about OPTSTAT seems to be quite hard to come by, so please share what you learnt with this list!

Thanks,

John Kanagaraj <><
DB Soft Inc
Phone: 408-970-7002 (W)

Co-Author: Oracle Database 10g Insider Solutions http://www.amazon.com/gp/product/0672327910/

** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers **

From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Wittenmyer Joel - CO
Sent: Sunday, June 04, 2006 7:08 AM
To: 'oracle-l@freelists.org'
Subject: Moving OPTSTAT objects from SYSAUX

We encountered bug 4288876 in 10.2.0.1.0 and applied the one-off patch, which failed to have any effect on the bug at all. Apparently this is not uncommon with this bug / patch. The next step is to apply patchset 10.2.0.2, which apparently succeeds even where the one-off patch fails. I'm always loathe to: 1) install / use version 1 of anything Oracle; 2) apply the latest patchset. In our architecture I was able to isolate the bug behaviour to the statistics gathering processes doing DML on the OPTSTAT objects in the SYSAUX tablespace. I therefore tested moving the OPTSTAT objects out of SYSAUX, exercising the statistics gathering processes, moving the objects back into SYSAUX, and again exercising the processes. Everything seems to be fine. However, I read a note in "10g Insider Solutions" that states that these particular objects CANNOT be moved from SYSAUX.

Finally, the questions:

Has anyone had experiences one way or the other moving these objects out of SYSAUX?

Does anyone know why it would be a bad idea to do so?

Thanks in advance,

Joel Wittenmyer

RE: Moving OPTSTAT objects from SYSAUX

John Kanagaraj

2006-06-05