【dbms包】DBMS_ADVISOR

DBMS_ADVISOR

add_sqlwkld_ref

DECLARE

task_id NUMBER;

task_name VARCHAR2(30);

workload_name VARCHAR2(30);

BEGIN

task_name := 'My Task';

workload_name := 'My Workload';

DBMS_ADVISOR.CREATE_TASK(DBMS_ADVISOR.SQLACCESS_ADVISOR, task_id, task_name);

DBMS_ADVISOR.CREATE_SQLWKLD(workload_name, 'My Workload');

DBMS_ADVISOR.ADD_SQLWKLD_REF(task_name, workload_name);

END;

/

 

Add_sqlwkld_statement

 

 

DECLARE

workload_name VARCHAR2(30);

BEGIN

workload_name := 'My Workload2';

DBMS_ADVISOR.CREATE_SQLWKLD(workload_name, 'My Workload1');

DBMS_ADVISOR.ADD_SQLWKLD_STATEMENT(workload_name, 'MONTHLY', 'ROLLUP',

100,400,5041,103,640445,680000,2,

1,SYSDATE,1,'test','SELECT count(object_id)

FROM t_test_14');

END;

/

 

CANCEL_TASK

DECLARE

    TASK_ID       NUMBER;

    TASK_NAME     VARCHAR2(30);

    WORKLOAD_NAME VARCHAR2(30);

BEGIN

    TASK_NAME     := 'My Task2';

    WORKLOAD_NAME := 'My Workload3';

    DBMS_ADVISOR.CREATE_TASK(DBMS_ADVISOR.SQLACCESS_ADVISOR,

                             TASK_ID,

                             TASK_NAME);

    DBMS_ADVISOR.CANCEL_TASK('My Task2');

END;

/

 

Execute_task

DECLARE

BEGIN

    DBMS_ADVISOR.EXECUTE_TASK('My Task3');

END;

/

 

 

Get_task_report

 

SELECT DBMS_ADVISOR.GET_TASK_REPORT(/*task_name => */'My Task3',

                                    /*type =>*/ 'TEXT',

                                    /*level =>*/ 'TYPICAL',

                                    /*section =>*/ 'ALL',

                                    /*owner_name => */'TEST') rpt

  FROM DUAL;

 

 

 

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16179598/viewspace-722208/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16179598/viewspace-722208/