disable sql tuning advisor
Date: Sun, 25 Nov 2012 08:08:24 -0800 (PST)
Message-ID: <37ddf9d2-2de5-46c9-99a7-56a618e5c82d_at_y6g2000vbb.googlegroups.com>
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 Name=****************
Target Type=Database Instance
Host=********************
Metric=Generic Operational Error Status
Metric Value=1
Timestamp=Nov 25, 2012 7:12:11 AM EST
Severity=Critical
Message=1 distinct types of operational errors have been found in the alert log.
Notification Rule Name=Database Availability and Critical States Notification Rule Owner=SYSMAN
Notification Count=1
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 ( 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 11.2.0.3 but mine is 11.1.0.7.12 .
Looks like disabling the sql tuning task is quick fix ... dbms_auto_task_admin.disable
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.
Ideas anyone?
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 in? Received on Sun Nov 25 2012 - 17:08:24 CET