oracle sql优化软件,Oracle的SQL Tuning Advisor STA（SQL语句优化工具）

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

n NUMBER;

BEGIN

FOR n IN 1..100

LOOP

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

COMMIT;

END LOOP;

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