disable sql tuning advisor

[John Hurley]

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?

[Mladen Gogala]

On Sun, 25 Nov 2012 08:08:24 -0800, John Hurley wrote:

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

That's a very bad news for DBA 2.0. They'll have to do some tuning
manually. BTW, with the proliferation of tuning tools and demons and
other automated ways for SQL exorcism, people seem to have forgotten that
SQL tuning starts with the application design.
I believe that running application which utilize a relational database
still requires significant degree of skills and knowledge of database,
the underlying operating systems and programming concepts.
Also, knowledge of the scripting languages is required. Scripting
languages like Perl, PHP or Python are the glue to integrate all kinds of
data formats and data sources. OEM is a smart tool, but not smart enough
to receive an email from external data source and insert the relevant
data from the attached spreadsheet into the database directly. Such tasks
are more and more frequent for the DBA.


--
Mladen Gogala
The Oracle Whisperer
http://mgogala.byethost5.com