--一、建立测试表
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
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25323853/viewspace-713298/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25323853/viewspace-713298/