SQL Tuning Advisor : dbms_sqltune

最新推荐文章于 2025-02-11 10:13:15 发布

转载最新推荐文章于 2025-02-11 10:13:15 发布 · 81 阅读

本文介绍如何使用Oracle DBMS_SQLTUNE包进行SQL优化，包括创建、执行优化任务、查看任务状态及结果等步骤，并提供了实际操作示例。

在Oracle10g之前，优化SQL是个比较费力的技术活，不停的分析执行计划，加hint，
分析统计信息等等，当然也有SQL调优辅助工具可以使用，只是要么价格昂贵，要么
效果不佳。在10g中，Oracle推出了自己的SQL优化辅助工具，这就是新的DBMS_SQLTUNE
包。执行DBMS_SQLTUNE包进行sql优化需要有advisor的权限：

SQL> grant advisor to SFIS1;

使用DBMS_SQLTUNE包进行SQL优化，大致可以分为四个步骤：

创建优化任务
执行优化任务
显示优化任务的结果
按照建议执行优化

一、创建优化任务

使用 dbms_sqltune.create_tuning_task 函数来创建优化任务，该参数的声明如下：

FUNCTION CREATE_TUNING_TASK RETURNS VARCHAR2
Argument Name Type In/Out Default?
SQLSET_NAME VARCHAR2 IN
BASIC_FILTER VARCHAR2 IN DEFAULT
OBJECT_FILTER VARCHAR2 IN DEFAULT
RANK1 VARCHAR2 IN DEFAULT
RANK2 VARCHAR2 IN DEFAULT
RANK3 VARCHAR2 IN DEFAULT
RESULT_PERCENTAGE NUMBER IN DEFAULT
RESULT_LIMIT NUMBER IN DEFAULT
SCOPE VARCHAR2 IN DEFAULT
TIME_LIMIT NUMBER IN DEFAULT
TASK_NAME VARCHAR2 IN DEFAULT
DESCRIPTION VARCHAR2 IN DEFAULT
PLAN_FILTER VARCHAR2 IN DEFAULT
SQLSET_OWNER VARCHAR2 IN DEFAULT

具体每个参数的含义请参考Oracle官方文档的说明。函数的返回值为创建的任务名。
下面我们创建一个叫sql_tuning_test的任务：

DECLARE
    my_task_name VARCHAR2 (30);
    my_sqltext CLOB;
   BEGIN
    my_sqltext := 'select sfis1.C_PCB_PRINT_T.*, sfis1.C_PCB_PRINT_T.ROWID
    from sfis1.C_PCB_PRINT_T
    WHERE IN_TIME>=:V0001
    AND IN_TIME<=:V0002 ' ;
    my_task_name := dbms_sqltune.create_tuning_task (sql_text=> my_sqltext,
    bind_list => sql_binds (anydata.convertnumber (9)),
    user_name => 'SFIS1',
    scope => 'COMPREHENSIVE',
    time_limit => 60,
    task_name => 'sql_tuning_test',
    description => 'Tuning Task'
    );
    END;

二、执行优化任务

通过调用dbms_sqltune.execute_tuning_task过程来执行前面创建好的优化任务。

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

三、检查优化任务的状态

通过查看user_advisor_tasks/dba_advisor_tasks视图可以查看优化任务的当前状态。

SQL> conn sfis1/sfis1

SQL> SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = 'sql_tuning_test' ;

STATUS
———–
COMPLETED

四、得到优化任务执行的结果

通过dbms_sqltune.report_tuning_task函数可以获得优化任务的结果。

SQL>SET LONG 999999
SQL>set serveroutput on size 999999
SQL>SET LINESIZE 100
SQL>SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('sql_tuning_test') FROM DUAL;
SQL> conn sfis1/sfis1

SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('sql_tuning_test') FROM DUAL;

DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_TUNING_TEST')
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name                  : sql_tuning_test
Tuning Task Owner                 : SFIS1
Scope                             : COMPREHENSIVE
Time Limit(seconds)               : 60
Completion Status                 : COMPLETED
Started at                        : 09/01/2011 10:33:40
Completed at                      : 09/01/2011 10:33:40
Number of Errors                  : 1

DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_TUNING_TEST')
--------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Schema Name: SFIS1
SQL ID     : bjbvdub79t3td
SQL Text   : select sfis1.C_PCB_PRINT_T.*, sfis1.C_PCB_PRINT_T.ROWID
                 from sfis1.C_PCB_PRINT_T
                 WHERE IN_TIME>=:V0001
                 AND IN_TIME<=:V0002

.......

然后我们就可以根据Recommendation部分的建议来执行优化操作了。

五、删除优化任务

通过调用dbms_sqltuen.drop_tuning_task可以删除已经存在的优化任务

SQL>exec dbms_sqltune.drop_tuning_task(‘sql_tuning_test’);

PL/SQL procedure successfully completed.

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

转载于:http://blog.itpub.net/35489/viewspace-706406/