利用Oracle 10g SQL优化器（STA）优化语句

SQL优化器(SQL Tuning Advisor STA)是Oracle10g中推出的帮助DBA优化工具，它的特点是简单、智能，DBA值需要调用函数就可以给出一个性能很差的语句的优化结果。下面介绍一下它的使用。

使用STA一定要保证优化器是CBO模式下。

一、利用STA优化语句

对于问题语句的收集，可以通过Oracle10g的另一个自动化工具：数据库自动诊断监视工具（ADDM）。它的使用可以参照我的另一篇文章《Oracle10g数据库自动诊断监视工具(ADDM)使用指南》。

我们下面简单介绍一下如何优化一条找到的问题语句。正如前面所述说的，STA是用起来很简单（只要你会调用存储过程，都能使用这个工具），三个步骤就可以完成一次语句调优。

测试环境创建：

SQL> CREATE TABLE bigtab AS SELECT rownum as "id", a.* FROM dba_objects a;

Table created.



SQL> create table smalltab as select rownum as "id", a.* FROM dba_tables a;

Table created.

SQL> ALTER TABLE bigtab MODIFY (empno NUMBER);

Table altered.





SQL> DECLARE

2       n NUMBER;

3    BEGIN

4       FOR n IN 1..100

5       LOOP

6           INSERT INTO bigtab SELECT rownum as "id", a.* FROM dba_objects a;

7           COMMIT;

8       END LOOP;

9   END;

/

PL/SQL procedure successfully completed.

这里创建一张大表和一张小表，并且都没有索引，下面执行一个查询：

SQL> set timing on

SQL> set autot on

SQL> select count(*) from bigtab a, smalltab b where a.object_name=b.table_name;

  COUNT(*)

----------

    135000

Elapsed: 00:00:05.59

Execution Plan

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

Plan hash value: 3089226980

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

| Id  | Operation           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT    |          |     1 |    36 |  3550   (2)| 00:00:43 |

|   1 |  SORT AGGREGATE     |          |     1 |    36 |            |          |

|*  2 |   HASH JOIN         |          |   155K|  5462K|  3550   (2)| 00:00:43 |

|   3 |    TABLE ACCESS FULL| SMALLTAB |  1223 | 22014 |    11   (0)| 00:00:01 |

|   4 |    TABLE ACCESS FULL| BIGTAB   |  1205K|    20M|  3526   (1)| 00:00:43 |

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

Predicate Information (identified by operation id):

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

   2 - access("A"."OBJECT_NAME"="B"."TABLE_NAME")

Statistics

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

          0  recursive calls

          0  db block gets

      16013  consistent gets

      14491  physical reads

          0  redo size

        412  bytes sent via SQL*Net to client

        385  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

可以看出这个语句执行性能很差：16013 consistent gets。

第一步：创建优化任务并执行

通过调用函数CREATE_TUNING_TASK来创建优化任务，调用存储过程EXECUTE_TUNING_TASK执行该任务：

SQL> set autot off

SQL> set timing off

SQL> DECLARE

  2    my_task_name VARCHAR2(30);

  3    my_sqltext   CLOB;

  4  BEGIN

  5    my_sqltext := 'select count(*) from bigtab a, smalltab b where a.object_name=b.table_name';

  6    my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(

  7            sql_text    => my_sqltext,

  8            user_name   => 'DEMO',

  9            scope       => 'COMPREHENSIVE',

 10            time_limit  => 60,

 11            task_name   => 'tuning_sql_test',

 12            description => 'Task to tune a query on a specified table');

 13

 14    DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'tuning_sql_test');

 15  END;

 16  /

PL/SQL procedure successfully completed.

在函数CREATE_TUNING_TASK，sql_text是需要优化的语句，user_name是该语句通过哪个用户执行，scope是优化范围（limited或comprehensive），time_limit优化过程的时间限制，task_name优化任务名称，description优化任务描述。

可以通过视图USER_ADVISOR_LOG和USER_ADVISOR_LOG来查看创建过的优化任务。

SQL> select task_name, status from USER_ADVISOR_LOG where task_name='tuning_sql_

test';

TASK_NAME                      STATUS

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

tuning_sql_test                COMPLETED

第二步：查看优化结果

通过函数可以查看优化结果。

SQL> set long 10000

SQL> set longchunksize 1000

SQL> set linesize 100

SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'tuning_sql_test') from DUAL;

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')

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

GENERAL INFORMATION SECTION

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

Tuning Task Name                  : tuning_sql_test

Tuning Task Owner                 : DEMO

Scope                             : COMPREHENSIVE

Time Limit(seconds)               : 60

Completion Status                 : COMPLETED

Started at                        : 11/30/2005 13:16:43

Completed at                      : 11/30/2005 13:16:44

Number of Index Findings          : 1

Schema Name: DEMO

SQL ID     : 6p64dnnsqf9pm

SQL Text   : select count(*) from bigtab a, smalltab b where

             a.object_name=b.table_name

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

FINDINGS SECTION (1 finding)

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

1- 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 DEMO.IDX$$_06C50001 on DEMO.SMALLTAB('TABLE_NAME');

  - Consider running the Access Advisor to improve the physical schema design

    or creating the recommended index.

    create index DEMO.IDX$$_06C50002 on DEMO.BIGTAB('OBJECT_NAME');

  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: 3089226980

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

| Id  | Operation           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT    |          |     1 |    36 |  3550   (2)| 00:00:43 |

|   1 |  SORT AGGREGATE     |          |     1 |    36 |            |          |

|*  2 |   HASH JOIN         |          |   155K|  5462K|  3550   (2)| 00:00:43 |

|   3 |    TABLE ACCESS FULL| SMALLTAB |  1223 | 22014 |    11   (0)| 00:00:01 |

|   4 |    TABLE ACCESS FULL| BIGTAB   |  1205K|    20M|  3526   (1)| 00:00:43 |

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

Predicate Information (identified by operation id):

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

   2 - access("A"."OBJECT_NAME"="B"."TABLE_NAME")

2- Using New Indices

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

Plan hash value: 494801882

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

| Id  | Operation              | Name           | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT       |                |     1 |    36 |  1108   (3)| 00:00:14 |

|   1 |  SORT AGGREGATE        |                |     1 |    36 |            |        |

|*  2 |   HASH JOIN            |                |   155K|  5462K|  1108   (3)| 00:00:14 |

|   3 |    INDEX FAST FULL SCAN| IDX$$_06C50001 |  1223 | 22014 |     3   (0)| 00:00:01 |

|   4 |    INDEX FAST FULL SCAN| IDX$$_06C50002 |  1205K|    20M|  1093   (2)| 00:00:14 |

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

Predicate Information (identified by operation id):

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

   2 - access("A"."OBJECT_NAME"="B"."TABLE_NAME")

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

看一下这个优化建议报告：

第一部分是关于这次优化任务的基本信息：如任务名称、执行时间、范围、涉及到的语句等等。

第二部分是关于这次优化任务的所找到的问题以及给出的优化建议。前面先给出了问题描述：可以通过建立更多的所引来提高性能；然后是建议的具体内容：在表smalltab的字段table_name上创建索引，在表bigtab的字段object_name上创建索引；最后是相关注意事项：此次优化虽然给出了创建索引的建议，但是最好通过SQL访问建议器(SQL Access Advisor SAA)结合整个数据库的工作量来深入分析，那样就能给出考虑了索引维护和空间消耗等因素的更加合理的建议。

最后，报告还给出了原有的查询计划，以及采用优化建议以后的查询计划的对比。可以看出COST值大大下降。