Oracle 10g sql tuning advisor

闲来无聊，突然在一篇文档中看到Oralce 10g sql tuning adviser，里面列举了该新特性的种种好处：

引用

- Determining stale or missing statistics
- Determining better execution plan
- Detecting better access paths and objects required to satisfy them (indexes, materialized views)
- Restructuring SQL

既然宣传的这么智能，那就体验一下呗。（不过宣传归选出，该特性还是有诸多bug）
使用Sql Tuning Adviser需要以下权限

引用

SQL> GRANT ADVISOR TO zhoul;

Grant succeeded.

SQL> GRANT SELECT_CATALOG_ROLE TO zhoul;

Grant succeeded.

SQL> GRANT EXECUTE ON DBMS_SQLTUNE TO zhoul;

Grant succeeded.

在zhoul用户下有zhoul表格

引用

SQL> conn zhoul/zhoul
Connected.
SQL> select obj#,count(*) from zhoul group by obj#;

      OBJ#   COUNT(*)
---------- ----------
         1     141325
         2          1

创建调优脚本和任务

引用

SQL> DECLARE
  2    my_task_name VARCHAR2(30);
  3    my_sqltext CLOB;
  4  BEGIN
  5    my_sqltext := 'SELECT * '   ||
  6                  'FROM ZHOUL ' || 'WHERE obj#= 1';
  7 
  8    my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
  9                             sql_text => my_sqltext,
10                             user_name => 'ZHOUL',
11                             scope => 'COMPREHENSIVE',
12                             time_limit => 60,
13                             task_name => 'TEST_sql_tuning_task',
14                             description => 'Task to tune a query on a specified PRODUCT');
15  END;
16  /

PL/SQL procedure successfully completed.

SQL> Execute dbms_sqltune.Execute_tuning_task (task_name => 'TEST_sql_tuning_task');

PL/SQL procedure successfully completed.

查询调优状态

引用

SQL> select status from dba_advisor_log where task_name='TEST_sql_tuning_task';

STATUS
-----------
COMPLETED

郁闷事情发生了，查询调优结果竟然没有值

引用

SQL> select count(*) from DBA_SQLTUNE_PLANS;

  COUNT(*)
----------
         2
SQL> set serveroutput on
SQL> set long 65536
SQL> set longchunksize 65536
SQL> set linesize 100
SQL> select dbms_sqltune.report_tuning_task('TEST_sql_tuning_task') from dual;





SQL>

难道我的测试案例太简单，Oracle认为没有必要提供建议？没道理啊。索性把测试案例弄得复杂点

引用

SQL> DECLARE
  2    my_task_name VARCHAR2(30);
  3    my_sqltext CLOB;
  4  BEGIN
  5    my_sqltext := 'SELECT a.* FROM ZHOUL a,TTT b where a.obj#=b.obj# and a.obj#=1';
  6 
  7    my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
  8                             sql_text => my_sqltext,
  9                             user_name => 'ZHOUL',
10                             scope => 'COMPREHENSIVE',
11                             time_limit => 60,
12                             task_name => 'TEST_sql_tuning_task2',
13                             description => 'Task to tune a query on a specified PRODUCT');
14  END;
15  /

测试结果依然，奇怪了。突然发现REPORT_TUNING_TASK返回的是clob类型，在本例sqlplus中long 为65536无法显示。

引用

FUNCTION REPORT_TUNING_TASK RETURNS CLOB
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
TASK_NAME                      VARCHAR2                IN
TYPE                           VARCHAR2                IN     DEFAULT
LEVEL                          VARCHAR2                IN     DEFAULT
SECTION                        VARCHAR2                IN     DEFAULT
OBJECT_ID                      NUMBER                  IN     DEFAULT
RESULT_LIMIT                   NUMBER                  IN     DEFAULT
OWNER_NAME                     VARCHAR2                IN     DEFAULT

继续在plsqldev中执行，结果显示出来了，当然我们将long设大也能在sqlplus中显示结果，当然我们要根据Oracle的建议做合理评估，不能听之任之。

引用

GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name                  : TEST_sql_tuning_task2
Tuning Task Owner                 : ZHOUL
Scope                             : COMPREHENSIVE
Time Limit(seconds)               : 60
Completion Status                 : COMPLETED
Started at                        : 11/26/2010 14:15:45
Completed at                      : 11/26/2010 14:15:48
Number of Statistic Findings      : 1
Number of Index Findings          : 1

-------------------------------------------------------------------------------
Schema Name: ZHOUL
SQL ID     : 0t9f1af0u9gqz
SQL Text   : SELECT a.* FROM ZHOUL a,TTT b where a.obj#=b.obj# and a.obj#=1

-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------

1- Statistics Finding
---------------------
  Table "ZHOUL"."ZHOUL" was not analyzed.

  Recommendation
  --------------
  - Consider collecting optimizer statistics for this table.
    execute dbms_stats.gather_table_stats(ownname => 'ZHOUL', tabname =>
            'ZHOUL', 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- Index Finding (see explain plans section below)
--------------------------------------------------
  The execution plan of this statement can be improved by creating one or more
  indices.

  Recommendation (estimated benefit: 100%)
  ----------------------------------------
  - Consider running the Access Advisor to improve the physical schema design
    or creating the recommended index.
    create index ZHOUL.IDX$$_812E0001 on ZHOUL.TTT('OBJ#');

  Rationale
  ---------
    Creating the recommended indices significantly improves the execution plan
    of this statement. However, it might be preferable to run "Access Advisor"
    using a representative SQL workload as opposed to a single statement. This
    will allow to get comprehensive index recommendations which takes into
    account index maintenance overhead and additional space consumption.

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original
-----------
Plan hash value: 3430618738

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |   222K|   276M|   133   (4)| 00:00:03 |
|*  1 |  HASH JOIN         |         |   222K|   276M|   133   (4)| 00:00:03 |
|*  2 |   INDEX RANGE SCAN | TTT_IDX |     2 |    10 |     2   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| ZHOUL   |   111K|   137M|   130   (3)| 00:00:03 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("A"."OBJ#"="B"."OBJ#")
   2 - access("B"."OBJ#"=1)
   3 - filter("A"."OBJ#"=1)

2- Using New Indices
--------------------
Plan hash value: 1793892349

-------------------------------------------------------------------------------------
| Id  | Operation          | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                |   222K|   276M|   132   (4)| 00:00:03 |
|*  1 |  HASH JOIN         |                |   222K|   276M|   132   (4)| 00:00:03 |
|*  2 |   INDEX RANGE SCAN | IDX$$_812E0001 |     2 |    10 |     1   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| ZHOUL          |   111K|   137M|   130   (3)| 00:00:03 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("A"."OBJ#"="B"."OBJ#")
   2 - access("B"."OBJ#"=1)
   3 - filter("A"."OBJ#"=1)

-------------------------------------------------------------------------------

最后删除sql优化任务

引用

SQL> exec dbms_sqltune.drop_tuning_task('TEST_sql_tuning_task2');

PL/SQL procedure successfully completed.