Re: Question regarding Grid Control Automatic SQL Tuning
- From: "Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>, "JBECKSTROM@xxxxxxxxx" <JBECKSTROM@xxxxxxxxx>
- Date: Wed, 9 Nov 2011 18:51:03 +0000
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@xxxxxxxxx>
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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
