关闭

Sql Tuning Advisor

标签: sqloracle任务userquerytable
418人阅读 评论(0) 收藏 举报
分类:

从oracle 10g开始,oracle推出了自己的SQL优化工具,即使用DBMS_SQLTUNE包。执行DBMS_SQLTUNE包需要授权advisor。

创建任务

declare
 task_name varchar2(300);
 tune_sqltext CLOB;
begin
        tune_sqltext := 'SQL文本';
        task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
            sql_text    => sqltext,
            user_name   => 'SYS',
            scope       => 'COMPREHENSIVE',
            time_limit  => 60,
            task_name   => 'tuning_sql',
            description => 'Task to tune a query on a specified table');
            DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'tuning_sql');
 END;
/

执行任务

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

PL/SQL procedure successfully completed.

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

TASK_NAME                      STATUS
------------------------------ -----------
tuning_sql                COMPLETED

SQL> SET LONG 999999
SQL> set serveroutput on size 999999
SQL> SET LINESIZE 100
SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'tuning_sql') from DUAL;

删除任务

SQL>exec dbms_sqltune.drop_tuning_task('tuning_sql');
PL/SQL procedure successfully completed.



0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:105868次
    • 积分:1867
    • 等级:
    • 排名:千里之外
    • 原创:82篇
    • 转载:8篇
    • 译文:0篇
    • 评论:27条
    文章分类