Sunday, November 25, 2012

hang in sql tuning advisor / bunch of cpu chewed up for nada

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 ( 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 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?

*** 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.

No comments:

Post a Comment