Wednesday, July 20, 2011

Just CREATE TUNING TASK with SQL_ID

This is nothing new, I just traced some session in database and needed to create Tuning Task from SQL_ID. I checked SQL_ID from V$SQLSTATS:
select sql_id, sql_text from V$SQLSTATS;
then used SQL_ID with DBMS_SQLTUNE.CREATE_TUNING_TASK
SET SERVEROUTPUT ON
declare
stmt_task VARCHAR2(100);
begin
stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => '0pqarh6218xjx');
DBMS_OUTPUT.put_line('task_id: ' || stmt_task );
end;
/

task_id: TASK_15485
then used DBMS_SQLTUNE.EXECUTE_TUNING_TASK
EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'TASK_15485');
while using DBMS_SQLTUNE.EXECUTE_TUNING_TASK ... can check status in DBA_ADVISOR_LOG view,
SELECT task_name, status FROM DBA_ADVISOR_LOG;
when status=COMPLETED ... and then got recommendations!!!
SET LINESIZE 200
SELECT DBMS_SQLTUNE. REPORT_TUNING_TASK('TASK_15485') AS recommendations FROM dual;

3 comments:

hillbillyToad said...

Did the recommendations work, as in, did it fix your performance issue?

What was the recommendation?

Surachart said...

> What was the recommendation?
It recommended to create 2 Indexes. But I did not create them. I sent this SQL statement to developer.

I believe, If SQL statement can change, It's better than create Index.

Anonymous said...

SET LONG 10000 PAGESIZE 1000 LINESIZE 200
SELECT
DBMS_SQLTUNE.report_tuning_task('earl_tuning_task1') AS recommendations FROM dual
/
SET LONG 10000 PAGESIZE 60 LINESIZE 80