Think different

困难并没有自己想得那么难,所谓的困难,很多都是自己想像出来的。

Sql Tuning Advisor

从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.



阅读更多
个人分类: Oracle Management
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

不良信息举报

Sql Tuning Advisor

最多只允许输入30个字

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭