July 29 2014

sql tuning advisor

DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext := ‘select count(*) from bigtab a, smalltab b where a.object_name=b.table_name’;
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
–sql_text => my_sqltext,
sql_id => ‘5a0mmr2ga93j7’,
— user_name => ‘UCJMH’,
scope => ‘COMPREHENSIVE’,
time_limit => 500,
task_name => ‘tuning_sql_test’,
description => ‘Task to tune a query on a specified table’);
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => ‘tuning_sql_test’);
END;

BEGIN
dbms_sqltune.execute_tuning_task(‘tuning_sql_test’);
END;

SELECT task_name,status FROM USER_ADVISOR_TASKS WHERE task_name =’tuning_sql_test’;
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( ‘tuning_sql_test’) from DUAL;

BEGIN
dbms_sqltune.drop_tuning_task(‘tuning_sql_test’);
END;



Copyright 2019. All rights reserved.

Posted 2014年7月29日 by ucjmh in category "oracle

Leave a Reply

Your email address will not be published. Required fields are marked *