oracle sqltune,DBMS_SQLTUNE

保证优化器是CBO模式下,执行DBMS_SQLTUNE包进行sql优化需要有advisor的权限：

这里对DBMS_SQLTUNE不做描述，请看实验：

create table aaa as select from sys.all_objects a;

create table bbb as select FROM dba_tables a;

运行多次 模拟数据：

insert into aaa select from dba_objects ;

insert into bbb select FROM dba_tables;

commit;

SQL> DECLARE

my_task_name VARCHAR2(30);

my_sqltext CLOB;

BEGIN

my_sqltext := 'select count(*) from aaa a, bbb b where a.object_name=b.table_name';

my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(

sql_text => my_sqltext,

user_name => 'XXX', -- 大写

scope => 'COMPREHENSIVE',

time_limit => 60,

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;

/

SQL> exec dbms_sqltune.execute_tuning_task('tuning_sql_test');

SQL> SELECT task_name,status FROM USER_ADVISOR_TASKS WHERE task_name ='tuning_sql_test';

SQL> SET LONG 999999

SQL> set serveroutput on size 999999

SQL> SET LINESIZE 100

SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'tuning_sql_test') from DUAL;

SQL>exec dbms_sqltune.drop_tuning_task('tuning_sql_test');