Been running by default in 11.1 the sql tuning advisor but not doing anything really with results ... we are closing in on migration to 11.2 but not quite there yet.
My current 11.1 system just starting barfing last week after 1 piece
of horrendous sql ( looks like from hyperion ... some smart person
doing ad hoc analysis on production OLTP system ... thanks )
apparently caused it to lose its mind.
It chews cpu and chews cpu
until task terminated ...
Target Type=Database Instance
Metric=Generic Operational Error Status
Timestamp=Nov 25, 2012 7:12:11 AM EST
Message=1 distinct types of operational errors have been found in the
Notification Rule Name=Database Availability and Critical States
Notification Rule Owner=SYSMAN
Plus you get nice stuff in alert log ...
Process 0x0x2daa74298 appears to be hung in Auto SQL Tuning task
Current time = 1353557444, process death time = 1353557402
Attempting to kill process 0x0x2daa74298 with OS pid = 8008
OSD kill succeeded for process 0x2daa74298
Maria ( from europe ) blogged about it here ( thanks Maria! ).
http://oraclespot.wordpress.com/2012/02/10/auto_sql_tuning/ ) and
there is a brief oracle doc item on it here at 1344499.1 so looks like
can still occur in 18.104.22.168 but mine is 22.214.171.124.12 .
Looks like disabling the sql tuning task is quick fix ...
Not that I am using results in 11.1 ... but was hoping I could find
some way of just ditching the 1 bad piece of sql that was hanging it
and leaving it as is.
We restart instance every weekend and that did not cause it to go
away ... kicks off and chews cpu every maintenance windows until it
gets killed ... same 1 bad sql statement.
How long is the guess that this will stick around after I have
disabled it ... if I re enable it too quickly probably will jump back
*** Update ... well I turned back on the advisor yesterday ... and finally ... the hang is gone. The really bad piece of sql causing the looping apparently aged out and no more huge chewing of cpu while the oracle code tries ( and fails ) to find a better way to execute it.