利用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 \ Table created.

SQL> create table smalltab as select rownum as \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 \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(\ 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 \ 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(\

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(\

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

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

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

的语句等等。

第二部分是关于这次优化任务的所找到的问题以及给出的优化建议。前面先给出了

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

最后，报告还给出了原有的查询计划，以及采用优化建议以后的查询计划的对比。

可以看出COST值大大下降。

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

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

2 - access(\

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

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

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

的语句等等。

第二部分是关于这次优化任务的所找到的问题以及给出的优化建议。前面先给出了

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

最后，报告还给出了原有的查询计划，以及采用优化建议以后的查询计划的对比。

可以看出COST值大大下降。

本文来源：https://www.bwwdw.com/article/6vzx.html