Re: Question regarding Grid Control Automatic SQL Tuning

From: Allen, Brandon <Brandon.Allen_at_OneNeck.com>
Date: Wed, 9 Nov 2011 18:51:03 +0000
Message-ID: <A250F0C68C23514CA9F3DF63D60EE10E0287FC_at_ONEWS31.oneneck.corp>



Hi Jeffrey, I just ran into the same problem and came across your post from last month so figured I'd share some relevant findings.
  1. I found bug 12327634 already filed for this issue. There is no solution given - just a very lazy and useless workaround.
  2. I found/tweaked the query below to provide the relevant information:
select f.execution_end, d.type, b.execution_name,
'Message           : '||b.message MESSAGE,
'Command To correct: '||c.command COMMAND,
'Action Message    : '||c.message ACTION_MESSAGE
from dba_advisor_tasks a, dba_advisor_findings b, dba_advisor_actions c, dba_advisor_recommendations d, dba_advisor_objects e, dba_advisor_executions f
where a.owner=b.owner and a.task_id=b.task_id
and b.task_id=d.task_id and b.finding_id=d.finding_id
and a.task_id=c.task_id and d.rec_id=c.rec_Id
and b.object_id = e.object_id
and b.execution_name = f.execution_name

and a.task_name = 'SYS_AUTO_SQL_TUNING_TASK' and a.status='COMPLETED' and e.attr1 = '&sql_id'
order by 1;

I'm just now really digging into this automatic SQL tuning functionality so I'm by no means an expert, but I believe the message you received ("This statement was skipped...") does NOT mean that the system already did any tuning automatically - it just means that it already ran the tuning advisor on that statement and provided recommendations. The only tuning recommendations that can be implemented automatically are SQL Profiles and you can verify if any of those have been implemented by checking dba_sql_profiles (filter for type = 'AUTO' in order to see only the profiles implemented by automatic SQL tuning) or by looking for queries in v$sql where sql_profile is not null.

I hope that helps.

Regards,
Brandon


Date: Fri, 14 Oct 2011 09:29:05 -0400
From: "Jeffrey Beckstrom" <JBECKSTROM_at_gcrta.org> Subject: Re: Question regarding Grid Control Automatic SQL Tuning

Looking at dba_advisor_findings I see the below. So where is this task??? Looking for something called EXEC_61% for an execution name in this table finds nothing.

select * from sys.dba_advisor_findings where message like '%6172%';

OWNER                             TASK_ID TASK_NAME

------------------------------ ---------- ------------------------------
EXECUTION_NAME FINDING_ID
------------------------------ ----------
FINDING_NAME

TYPE TYPE_ID PARENT OBJECT_ID
----------- ---------- ---------- ----------
IMPACT_TYPE

    IMPACT



MESSAGE

MORE_INFO

F FLAGS
- ----------
SYS                                 11482 SYS_AUTO_SQL_TUNING_TASK
EXEC_6692                           33354
normal, successful completion
INFORMATION          4          0      34938

         0

This statement was skipped because it has already been tuned recently. See task  execution "EXEC_6172" for the most recent tuning results.

N 1

>>> Jeffrey Beckstrom 10/14/11 8:58 AM >>> In Grid Control, looking at "Automatic SQL Tuning Result Details". I click on sql_id and then the Advisor Task Name, under findings it states:

This statement was skipped because it has already been tuned recently. See task execution "EXEC_6172" for the most recent tuning results.

Does this mean that the system automatically did something and if so what?

Jeffrey Beckstrom
Database Administrator
Greater Cleveland Regional Transit Authority Information Systems
1240 W. 6th Street
Cleveland, Ohio 44113


Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Nov 09 2011 - 12:51:03 CST

Original text of this message