My respect if you can have terrabyte OLTP system running stable with auto gather stats job. Quite challenging. :)
If you have bug # - please share. Stats gathering doesn't do "massive DML". What it does is a lot of selects and assuming those are long running, one can expect it to fail with ORA-1555 (See ORA-1555.ora-code.com) on a busy OLTP system. The only DMLs are done to dictionary tables to update stats (unless I seriously missing something). Invalidation of execution plans caused by updating statistics should have far more noticeable impact I believe.
2006/6/27, Wittenmyer Joel - CO <WITTENMYERJ@(protected)>: > This sounds similar to a known bug with ASSM tablespaces. The stats job > updates tables in SYSAUX. Those tables have indexes and up to 10.2.0.2 if > an instance does massive DML (which the stats job does in our case. > Terabyte oltp with daily data loads and many schemas) on segments in an ASSM > tablespace that has indexes, the undo is unbelievable. In our case it > produces 10x the undo / redo it normally would. It can cause not only > ora-1555 (See ora-1555.ora-code.com), but you can blow out the max extents (32767) for an undo segment. > The 'workaround' of coalescing the indexes prior to running such DML is only > minimally effective. The backport patch for previous versions in some cases > flat doesn't work (I know from experience.) The only sure fix is to upgrade > to 10.2.0.2, which I am told definitely fixes the issue, and which I am > preparing to do on several production instances even thought I HATE applying > the latest patchset (hornet's nest). Of course, if you are already on > 10.2.0.2 and are seeing this, please let me know!
