sql profile

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

SYS@ning>grant advisor to ning;
Grant succeeded.

使用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的任务：

NING@ning>DECLARE
 2 my_task_name VARCHAR2 (30);
 3 my_sqltext CLOB;
 4 BEGIN
 5 my_sqltext :='SELECT e.last_name, d.department_name, d.department_id
 6  FROM employees e, departments d
 7  WHERE e.department_id = d.department_id
 8  AND d.department_id = :bnd';
 9 my_task_name :=dbms_sqltune.create_tuning_task (sql_text=>my_sqltext,
 10 bind_list =>sql_binds (anydata.convertnumber (9)),
 11 user_name =>'NING',
 12 scope =>'COMPREHENSIVE',
 13 time_limit =>60,
 14 task_name =>'sql_tuning_test',
 15 description =>'Tuning Task'
 16 );
 17 END;
 18  /
 
PL/SQL procedure successfully completed.

二、执行优化任务

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

PROCEDURE EXECUTE_TUNING_TASK
Argument Name Type In/Out Default?
—————– ————- —— ——–
TASK_NAME VARCHAR2 IN

NING@ning>exec dbms_sqltune.execute_tuning_task('sql_tuning_test');
 
PL/SQL procedure successfully completed.

三、检查优化任务的状态

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

NING@ning>SELECT status FROM USER_ADVISOR_TASKS WHERE task_name ='sql_tuning_test';
 
STATUS
-----------
COMPLETED

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

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

NING@ning>SET LONG 999999
NING@ning>set serveroutput on size 999999
NING@ning>SET LINESIZE 100
NING@ning>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                 :NING
Scope                             :COMPREHENSIVE
Time Limit(seconds)               :60
Completion Status                 :COMPLETED
Started at                        :04/30/2007 10:42:48
Completed at                      :04/30/2007 10:42:49
Number of Statistic Findings      :2
Number of SQL Profile Findings    :1
 
DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_TUNING_TEST')
-------------------------------------------------------------------------------
 
-------------------------------------------------------------------------------
Schema Name: NING
SQL ID     :6rbh5v8smjpdq
SQL Text   :SELECT e.last_name,d.department_name,d.department_id
            FROM employees e,departments d
            WHERE e.department_id =d.department_id
            AND d.department_id = :bnd
 
-------------------------------------------------------------------------------
FINDINGS SECTION (3 findings)
 
DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_TUNING_TEST')
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
 
1-Statistics Finding
---------------------
  Table "NING"."DEPARTMENTS" was not analyzed.
 
 Recommendation
  --------------
  - Consider collecting optimizer statistics for this table.
    execute dbms_stats.gather_table_stats(ownname =>'NING',tabname =>
            'DEPARTMENTS',estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE,
            method_opt =>'FOR ALL COLUMNS SIZE AUTO');
 
 Rationale
  ---------
    The optimizer requires up-to-date statistics for the table in order to
    select a good execution plan.
 
2-Statistics Finding
---------------------
  Table "NING"."EMPLOYEES" was not analyzed.
 
 Recommendation
  --------------
  - Consider collecting optimizer statistics for this table.
    execute dbms_stats.gather_table_stats(ownname =>'NING',tabname =>
            'EMPLOYEES',estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE,
            method_opt =>'FOR ALL COLUMNS SIZE AUTO');
 
 Rationale
  ---------
    The optimizer requires up-to-date statistics for the table in order to
    select a good execution plan.
 
DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_TUNING_TEST')
-------------------------------------------------------------------------------
 
3-SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
  A potentially better execution plan was found for this statement.
 
 Recommendation (estimated benefit<=10%)
  ---------------------------------------
  - Consider accepting the recommended SQL profile.
    execute dbms_sqltune.accept_sql_profile(task_name =>'sql_tuning_test',
            replace =>TRUE);
 
 
DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_TUNING_TEST')
------------------------------------------------------------------------------
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
 
1-Original With Adjusted Cost
------------------------------
Plan hash value: 2052257371
 
----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
 
DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_TUNING_TEST')
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |     1 |    26 |    22   (5)| 00:00:01 |
|* 1 | HASH JOIN         |             |     1 |   26 |   22   (5)|00:00:01 |
|* 2 |   TABLE ACCESS FULL|DEPARTMENTS |     1 |     7 |     3   (0)|00:00:01 |
|* 3 |   TABLE ACCESS FULL|EMPLOYEES   |     1 |   19 |   18   (0)|00:00:01 |
----------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
  1 -access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
  2 -filter("D"."DEPARTMENT_ID"=:BND)
 
DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_TUNING_TEST')
---------------------------------------------------------------------------------
   3 - filter("E"."DEPARTMENT_ID"=:BND)
 
2-Using SQL Profile
--------------------
Plan hash value: 1782137809
 
----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |     1 |    26 |    22   (5)| 00:00:01 |
|* 1 | HASH JOIN         |             |     1 |   26 |   22   (5)|00:00:01 |
 
DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_TUNING_TEST')
----------------------------------------------------------------------------------
|*  2 |   TABLE ACCESS FULL| EMPLOYEES   |     1 |    19 |    18   (0)| 00:00:01 |
|* 3 |   TABLE ACCESS FULL|DEPARTMENTS |     1 |     7 |     3   (0)|00:00:01 |
----------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
  1 -access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
  2 -filter("E"."DEPARTMENT_ID"=:BND)
  3 -filter("D"."DEPARTMENT_ID"=:BND)
 
 
DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_TUNING_TEST')

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

五、删除优化任务

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

NING@ning>exec dbms_sqltune.drop_tuning_task('sql_tuning_test');
 
PL/SQL procedure successfully completed.

—————————————————————————–