Search code examples
oracleplsqloracle11gdatabase-administrationsql-tuning

Is it possible to skip the particular SQL statement from auto sql tuning advisor job

I am facing an ORA:7445 issue with auto sql tuning advisor. Auto sql tuning advisor keeps failing with

ORA:7445

while it's tries to tune a particular SQL.

Is there any way to skip this sql statement from auto sql tuning advisor job?

Solution

  • The simplest way to avoid the Automatic SQL Tuning Advisor may be to convert the query into a form that is not supported by the program.

    According to the "Automatic SQL Tuning" chapter of the "Database Performance Tuning Guide":

    The database ignores recursive SQL and statements that have been tuned recently (in the last month), parallel queries, DML, DDL, and SQL statements with performance problems caused by concurrency issues.

    If the query select * from dba_objects was causing problems, try re-writing it like this:

    select * from dba_objects
union all
--This query block only exists to avoid the Automatic SQL Tuning Advisor.
select /*+ parallel(dba_objects 2) */ * from dba_objects 
where 1=0;

    It is now a "parallel query" although it will not truly run in parallel because of the 1=0. I haven't tested this, and I imagine it will be difficult for you to test, because you'll need to flush the existing AWR data to prevent the errors.

    This is one of the reasons why I usually disable the Automatic SQL Tuning Advisor. I like the idea of it, but in practice I've literally never seen the tuning advisor provide useful information. All it has ever done for me is generate alerts.

    In theory,the package DBMS_AUTO_SQLTUNE contains the parameters BASIC_FILTER, OBJECT_FILTER, and PLAN_FILTER. I assume one of those could be useful but I don't think they are implemented yet. I can't find any references to them on Google or My Oracle Support. And when I entered random text for the values there were no errors.

    Ideally we would look up every ORA-00600 and ORA-07445 error, create an SR, and fix the underlying problem. But who has time for that? when you encounter a database "bug", the best solution is usually to avoid it as quickly as possible.