--一、建立测试表
CREATE TABLE T11(ID INT , NAME VARCHAR2(1000));
/
CREATE TABLE T21(ID INT , NAME CHAR(1000));
/
BEGIN
    FOR I IN 1..10000 LOOP
    INSERT INTO T11 VALUES(I,sysdate||I);
    INSERT INTO T21 VALUES(I,sysdate||I);
    END LOOP;
    COMMIT;
END;
/
--二、赋权限
GRANT EXECUTE ON DBMS_SQLTUNE TO JASON
/
GRANT ADVISOR TO JASON
/
--三、创建任务
DECLARE
     MY_TASK_NAME VARCHAR2(30);
     MY_SQLTEXT CLOB;
     BEGIN
     MY_SQLTEXT := 'SELECT /*+ USE_NL(T11 ,T21)*/ COUNT(*) FROM T11,T21 WHERE T11.ID=T21.ID AND T11.ID<:ID';
   
     MY_TASK_NAME := DBMS_SQLTUNE.CREATE_TUNING_TASK(
     SQL_TEXT => MY_SQLTEXT,
     BIND_LIST => SQL_BINDS(ANYDATA.CONVERTNUMBER(10000)),
     USER_NAME => 'JASON',
     SCOPE => 'COMPREHENSIVE',
     TIME_LIMIT => 60,
     TASK_NAME => 'MY_SQL_TUNING_TASK',
     DESCRIPTION => 'TASK TO TUNE A QUERY ON A SPECIFIED T11 AND T21');
 END;
 /
 SELECT OWNER,TASK_NAME,STATUS FROM DBA_ADVISOR_TASKS WHERE WNER='JASON' AND TASK_NAME='MY_SQL_TUNING_TASK';
/
--四、执行任务
BEGIN
 DBMS_SQLTUNE.EXECUTE_TUNING_TASK( TASK_NAME => 'MY_SQL_TUNING_TASK' );
END;
 /
SELECT MESSAGE,MORE_INFO FROM DBA_ADVISOR_FINDINGS WHERE WNER='JASON' AND TASK_NAME='MY_SQL_TUNING_TASK';
/
SELECT TYPE,BENEFIT FROM DBA_ADVISOR_RECOMMENDATIONS WHERE TASK_NAME='MY_SQL_TUNING_TASK';
/
--五、查询建议结果
SET LONG 1000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'MY_SQL_TUNING_TASK') FROM DUAL;
/
--六、删除任务的方法
BEGIN  DBMS_SQLTUNE.DROP_TUNING_TASK('MY_SQL_TUNING_TASK'); END;
/
SELECT /*+ USE_NL(T11 ,T21)*/ COUNT(*) FROM T11,T21 WHERE T11.ID=T21.ID AND T11.ID<3