Oracle 10g SQL优化器（STA）优化语句
SQL优化器(SQL Tuning Advisor STA)是Oracle10g中推出的帮助DBA优化工具，它的特点是简单、智能，DBA值需要调用函数就可以给出一个性能很差的语句的优化结果。下面介绍一下它的使用。
使用STA一定要保证优化器是CBO模式下。
一、利用STA优化语句
对于问题语句的收集，可以通过Oracle10g的另一个自动化工具：数据库自动诊断监视工具（ADDM）。它的使用可以参照我的另一篇文章《Oracle10g数据库自动诊断监视工具(ADDM)使用指南》。
我们下面简单介绍一下如何优化一条找到的问题语句。正如前面所述说的，STA是用起来很简单（只要你会调用存储过程，都能使用这个工具），三个步骤就可以完成一次语句调优。
测试环境创建：
?
使用Sql Tuning Adviser需要以下权限
??? using a representative SQL workload as opposed to a single statement. This二、利用sql profile存储优化策略
利用STA对语句进行优化后，STA会对语句进行分析，采用最优的优化策略，并给出优化后的查询计划。你可以按照STA给出的建议重写语句。但是，有些情况下，你可能无法重写语句（比如在生产环境中，你的语句又在一个包中）。这个时候就可以利用sql profile，将优化策略存储在profile中，Oracle在构建这条语句的查询计划时，就不会使用已有相关统计数据，而使用profile的策略，生成新的查询计划。
第一部分：profile的使用
SQL Profile对于一下类型语句有效：
???? SELECT语句；
???? UPDATE语句；
???? INSERT语句（仅当使用SELECT子句时有效）；
???? DELETE语句；
???? CREATE语句（仅当使用SELECT子句时有效）；
???? MERGE语句（仅当作UPDATE和INSERT操作时有效）。
另外，使用SQL Profile还必须有CREATE ANY SQL PROFILE、DROP ANY SQL PROFILE和ALTER ANY SQL PROFILE等系统权限。
还是举例说明吧：
第一步：给用户赋权限
SQL> conn sys/sys as sysdba
Connected.
SQL> GRANT CREATE ANY SQL PROFILE TO DEMO;
Grant succeeded.
SQL> GRANT DROP ANY SQL PROFILE TO DEMO;
Grant succeeded.
SQL> GRANT ALTER ANY SQL PROFILE TO DEMO;
Grant succeeded.
SQL> conn demo/demo
Connected.
SQL> create index smalltab_idx1 on smalltab(table_name);
Index created.
SQL> analyze table smalltab compute statistics;
Table analyzed.
SQL> set autot on
SQL> select /*+no_index(smalltab smalltab_idx1)*/count(*) from smalltab where ta
ble_name = 'TAB$';
? COUNT(*)
----------
???????? 1
Execution Plan
----------------------
Plan hash value: 2298554444
-------------------------------------------
| Id? | Operation????????? | Name???? | Rows? | Bytes | Cost (%CPU)| Time???? |
-------------------------------------------
|?? 0 | SELECT STATEMENT?? |????????? |???? 1 |??? 18 |??? 11?? (0)| 00:00:01 |
|?? 1 |? SORT AGGREGATE??? |????????? |???? 1 |??? 18 |??????????? |????????? |
|*? 2 |?? TABLE ACCESS FULL| SMALLTAB |???? 1 |??? 18 |??? 11?? (0)| 00:00:01 |
-------------------------------------------
第二步，创建、执行优化任务
SQL> DECLARE
? 2??? my_task_name VARCHAR2(30);
? 3??? my_sqltext?? CLOB;
? 4? BEGIN
? 5??? my_sqltext := 'select /*+no_index(smalltab smalltab_idx1)*/count(*) from
smalltab where table_name = ''TAB$''';
? 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?? => 'sql_profile_test',
12??????????? description => 'Task to tune a query on a specified table');
13
14??? DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'sql_profile_test');
15? END;
16? /
PL/SQL procedure successfully completed.
????
第三步：查看优化建议
SQL> set autot off
SQL> set long 10000
SQL> set longchunksize 1000
SQL> set linesize 100
SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'sql_profile_test') from DUAL;
DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST')
----------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------
Tuning Task Name????????????????? : sql_profile_test
Tuning Task Owner???????????????? : DEMO
Scope???????????????????????????? : COMPREHENSIVE
Time Limit(seconds)?????????????? : 60
Completion Status???????????????? : COMPLETED
Started at??????????????????????? : 11/29/2005 14:52:09
Completed at????????????????????? : 11/29/2005 14:52:09
Number of SQL Profile Findings??? : 1
DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST')
-------------------------------------------------------
----------------------------------------------------
Schema Name: DEMO
SQL ID???? : 3kta54ycuqccb
SQL Text?? : select /*+no_index(smalltab smalltab_idx1)*/count(*) from
???????????? smalltab where table_name = 'TAB$'
-------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------
1- SQL Profile Finding (see explain plans section below)
DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST')
--------------------------------------------
---------------------------------------
? A potentially better execution plan was found for this statement.
? Recommendation (estimated benefit: 90.94%)
? ------------------------------------------
? - Consider accepting the recommended SQL profile.
??? execute dbms_sqltune.accept_sql_profile(task_name => 'sql_profile_test',
??????????? replace => TRUE);
-------------------------------------------
EXPLAIN PLANS SECTION
DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST')
--------------------------------------------
---------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 2298554444
-------------------------------------------
| Id? | Operation????????? | Name???? | Rows? | Bytes | Cost (%CPU)| Time???? |
-------------------------------------------
|?? 0 | SELECT STATEMENT?? |????????? |???? 1 |??? 18 |??? 11?? (0)| 00:00:01 |
|?? 1 |? SORT AGGREGATE??? |????????? |???? 1 |??? 18 |??????????? |????????? |
DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST')
----------------------------------------------------------------
|*? 2 |?? TABLE ACCESS FULL| SMALLTAB |???? 1 |??? 18 |??? 11?? (0)| 00:00:01 |
-------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
?? 2 - filter("TABLE_NAME"='TAB$')
2- Using SQL Profile
--------------------
Plan hash value: 2664476518
DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST')
----------------------------------------------------------------
----------------------------------------------
| Id? | Operation???????? | Name????????? | Rows? | Bytes | Cost (%CPU)| Time
? |
-----------------------------------------------
|?? 0 | SELECT STATEMENT? |?????????????? |???? 1 |??? 18 |???? 1?? (0)| 00:00:01 |
|?? 1 |? SORT AGGREGATE?? |?????????????? |???? 1 |??? 18 |??????????? |? |
|*? 2 |?? INDEX RANGE SCAN| SMALLTAB_IDX1 |???? 1 |??? 18 |???? 1?? (0)| 00:00:01 |
-----------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST')
----------------------------------------------------------------
?? 2 - access("TABLE_NAME"='TAB$')
-------------------------------------------
这里可以看到，在优化建议中给出了新的查询计划。现在，我们决定接受这个建议，并且不重写语句。
第三步：接受profile
SQL> DECLARE
? 2??? my_sqlprofile_name VARCHAR2(30);
? 3? BEGIN
? 4??? my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
? 5?????? task_name => 'sql_profile_test',
? 6?????? name????? => 'my_sql_profile');
? 7? END;
? 8? /
PL/SQL procedure successfully completed.
在这里用了包DBMS_SQLTUNE的另一个函数：ACCEPT_SQL_PROFILE。其中，参数task_name即我们创建的优化建议任务的名称，name是profile的名字，可以是任意合法名称。此外这个函数还有其他一些函数，下面是这个函数的原型：
DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
?? task_name??? IN? VARCHAR2,
?? object_id??? IN? NUMBER?? := NULL,
?? name???????? IN? VARCHAR2 := NULL,
?? description? IN? VARCHAR2 := NULL,
?? category???? IN? VARCHAR2 := NULL;
?? task_owner?? IN VARCHAR2? := NULL,
?? replace????? IN BOOLEAN?? := FALSE,
?? force_match? IN BOOLEAN?? := FALSE)
RETURN VARCHAR2;
Description是profile的描述信息；task_owner是优化建议任务的所有者；replace为TRUE时，如果这个profile已经存在，就代替它；force_match为TURE时，表示与语句强制匹配，即强制使用绑定变量，和系统参数cursor_sharing设置为FORCE时类似，为FALSE时，与cursor_sharing设置为EXACT时类似，即完全匹配。
这里要特别提到的是category这个参数，你可以通过设置这个参数，制定特定会话使用这个profile。在10g中，每个会话都有一个新参数SQLTUNE_CATEGORY，他的默认值是DEFAULT。而我们在调用这个函数时，如果没有指定这个参数，那它的值也是DEFAULT，而如果我们给这个profile指定了一个其它的CATEGORY值，如FOR_TUNING，那么只有会话参数SQLTUNE_CATEGORY也为FOR_TUNING时，才会使用这个porfile。为什么说这个参数很有用呢？试想一个这样的环境：你在一个生产系统上利用STA调优一条语句，STA已经给出了优化建议，但是你又不敢贸然实施它给出的建议（毕竟它只是机器嘛，不能完全信任），你就可以创建一个有特殊CATEGORY的profile，然后在你自己的会话中制定SQLTUNE_CATEGORY为这个特殊的CATEGORY，那就既可以看优化建议的实际效果又不影响生产环境。
???? 此外可以通过视图DBA_SQL_PROFILES来查看已经创建的profile。
第四步：查看profile的效果
SQL> select /*+no_index(smalltab smalltab_idx1)*/count(*) from smalltab where ta
ble_name = 'TAB$';
? COUNT(*)
----------
???????? 1
Execution Plan
----------------------
Plan hash value: 2664476518
-----------------------------------------------
| Id? | Operation???????? | Name????????? | Rows? | Bytes | Cost (%CPU)| Time? |
-----------------------------------------------
|?? 0 | SELECT STATEMENT? |?????????????? |???? 1 |??? 18 |???? 1?? (0)| 00:00:01 |
|?? 1 |? SORT AGGREGATE?? |?????????????? |???? 1 |??? 18 |??????????? |? |
|*? 2 |?? INDEX RANGE SCAN| SMALLTAB_IDX1 |???? 1 |??? 18 |???? 1?? (0)| 00:00:01 |
-----------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
?? 2 - access("TABLE_NAME"='TAB$')
Note
-----
?? - SQL profile "my_sql_profile" used for this statement
可以看到，语句采用了profile中的数据，创建了新的查询计划。并且在查询计划中还有一些附加信息，表明这个语句是采用了’my_sql_profile’这个profile，而不是根据对象上面的统计数据来生成的查询计划。
第二部分：profile的控制
对profile的控制比较简单：修改和删除。包DBMS_SQLTUNE提供了两个存储过程来完成这两个操作：ALTER_SQL_PROFILE和DROP_SQL_PROFILE。
BEGIN
? DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'my_sql_profile');
END;
/
1．Profile的修改
ALTER_SQL_PROFILE的原型是：
DBMS_SQLTUNE.ALTER_SQL_PROFILE (
?? name???????????????? IN? VARCHAR2,
?? attribute_name?????? IN? VARCHAR2,
?? value??????????????? IN? VARCHAR2);
其中，name就是profile的名字；attribute_name是需要修改的属性的名字；value是修改后的值。例如，需要使’my_sql_profile’失效，可以修改STATUS属性为DISABLED：
SQL> BEGIN
? 2??? DBMS_SQLTUNE.ALTER_SQL_PROFILE(
? 3?????? name??????????? => 'my_sql_profile',
? 4?????? attribute_name? => 'STATUS',
? 5?????? value?????????? => 'DISABLED');
? 6? END;
? 7? /
PL/SQL procedure successfully completed.
SQL>
SQL> set autot on exp
SQL> select /*+no_index(smalltab smalltab_idx1)*/count(*) from smalltab where ta
ble_name = 'TAB$';
? COUNT(*)
----------
???????? 1
Execution Plan
----------------------
Plan hash value: 2298554444
-------------------------------------------
| Id? | Operation????????? | Name???? | Rows? | Bytes | Cost (%CPU)| Time???? |
-------------------------------------------
|?? 0 | SELECT STATEMENT?? |????????? |???? 1 |??? 18 |??? 11?? (0)| 00:00:01 |
|?? 1 |? SORT AGGREGATE??? |????????? |???? 1 |??? 18 |??????????? |????????? |
|*? 2 |?? TABLE ACCESS FULL| SMALLTAB |???? 1 |??? 18 |??? 11?? (0)| 00:00:01 |
-------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
?? 2 - filter("TABLE_NAME"='TAB$')
2．删除profile
DROP_SQL_PROFILE的原型是：
DBMS_SQLTUNE.DROP_SQL_PROFILE (
?? name????????? IN? VARCHAR2,
?? ignore??????? IN? BOOLEAN? := FALSE);
其中，name是profile的名字，ignore为TRUE时，当指定的profile不存在时不报错。
3．确认某条语句是否已经有相应profile
当然我们也希望能确认某条语句是否已经形成了profile，看是否有必要在对它进行tuning。这时就可以利用SQLTEXT_TO_SIGNATURE函数：
SQL> set serveroutput on
SQL> declare
? 2??? v_signature number;
? 3? begin
? 4??? v_signature:=DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE (
? 5????????????????????? sql_text??? => 'select /*+no_index(smalltab smalltab_idx1)*/count(*) from smalltab where table_name = ''TAB$''',
? 6????????????????????? force_match => FALSE);
? 7??? if v_signature is null then
? 8????? DBMS_OUTPUT.put_line('no such sql text in profile');
? 9??? else
10????? DBMS_OUTPUT.put_line('The sql text is in profile');
11??? end if;
12? end;
13? /
The sql text is in profile
PL/SQL procedure successfully completed.
其中，sql_text就是需要检测的内容；force_match的解释与ACCEPT_SQL_PROFILE中相应参数是一样的。
第三部分：profile的转储与移植
在某些环境下，比如生产环境的安全要求非常高，无法直接对生产环境进行优化，只能在一个与生产环境保持一致的镜像环境中作优化，那么，希望将优化结果实施到生产环境中去，该怎么办呢？DBMS_SQLTUNE包提供了另外几个函数用于将profile的数据导出到表中，然后可以再将表中的数据移植到其它环境中，下面介绍一下如何使用它们。
第一步：创建存储表
先利用存储过程创建一张存储profile的表：
SQL> begin
? 2? DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF (
? 3???? table_name??????????? => 'PROFILE_STGTAB',
? 4???? schema_name?????????? => 'DEMO',
? 5???? tablespace_name?????? => 'EDGARDEMO');
? 6? end;
? 7? /
PL/SQL procedure successfully completed.
SQL> desc PROFILE_STGTAB
Name????????????????????????????????????????????????? Null???? Type
----------------------------------------------------- -------- ----------------
--------------------
PROFILE_NAME?????????????????????????????????????????????????? VARCHAR2(30)
CATEGORY?????????????????????????????????????????????????????? VARCHAR2(30)
SIGNATURE????????????????????????????????????????????????????? NUMBER
SQL_TEXT?????????????????????????????????????????????????????? CLOB
DESCRIPTION??????????????????????????????????????????????????? VARCHAR2(500)
TYPE?????????????????????????????????????????????????????????? VARCHAR2(9)
STATUS???????????????????????????????????????????????????????? VARCHAR2(8)
BOOLEAN_FLAGS????????????????????????????????????????????????? NUMBER
ATTRIBUTES???????????????????????????????????????????????????? SQLPROF_ATTR
VERSION??????????????????????????????????????????????????????? NUMBER
SPARE1???????????????????????????????????????????????????????? CLOB
SPARE2???????????????????????????????????????????????????????? BLOB
Table_name是需要创建的存储表的名字，schema_name是它所属schema的名字，tablespace_name是所属表空间。
此外，需要注意的是，这个存储过程实际上做的是create table操作，也就是DDL操作，所以不要在一个事务中调用它。使用这个存储过程需要有CREATE ANY TABLE的权限。
第二步：将profile中数据存入存储表中
利用存储过程可以将profile中数据存储刚才建的那张存储表中：
SQL> BEGIN
? 2? DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (
? 3???? profile_name????????? => '%',
? 4???? profile_category????? => 'DEFAULT',
? 5???? staging_table_name??? => 'PROFILE_STGTAB',
? 6???? staging_schema_owner? => 'DEMO');
? 7? END;
? 8? /
PL/SQL procedure successfully completed.
SQL> set long 10000
SQL> set longchunksize 1000
SQL> set linesize 100
SQL> select profile_name, category, sql_text from PROFILE_STGTAB;
PROFILE_NAME???????????? CATEGORY???????????? SQL_TEXT
------------------------------ ---------------------------------------------------
my_sql_profile???????????????? DEFAULT???????????????? select /*+no_index(smalltab smalltab_idx1)*/count(*)
????????????????????????????????????????????????????????????????????????? frommalltab where table_name = 'TAB$'
profile_name是需要存储的profile的名字（大小写敏感），默认为’%’，即这个CATEGORY下的所有profile；profile_category即需要存储的profile所在category名字（大小写敏感），默认是DEFAULT；staging_table_name就是用于存储profile数据的表名（大小写敏感）；staging_schema_owner是该表所属的schema。调用该函数需要有的CREATE ANY SQL PROFILE系统权限，并且对存储表要有SELECT权限。
要注意一点：调用了这个存储过程，会执行COMMIT，所以要注意对前面事务的影响。
另外，可以通过存储过程来修改存储表中的信息：
SQL> begin
? 2? DBMS_SQLTUNE.REMAP_STGTAB_SQLPROF (
? 3??? old_profile_name????? => 'my_sql_profile',
? 4??? new_profile_name????? => 'new_sql_profile',
? 5??? new_profile_category? => 'DEV',
? 6??? staging_table_name??? => 'PROFILE_STGTAB',
? 7??? staging_schema_owner? => 'DEMO');
? 8? end;
? 9? /
PL/SQL procedure successfully completed.
Old_profile_name是需要修改的存储在存储表中的profile的名字（大小写敏感）;new_profile_name是需要修改为的新名字，默认是NULL，既不修改名字；new_profile_category是需要修改为的新目录名字，默认是NULL，既不修改；staging_table_name是需要修改的存储表的名字（大小写敏感）；staging_schema_owner是存储表所属的schema。
当然，调用这个函数需要有对存储表的UPDATE权限。
第三步：从存储表中导入profile数据
将profile中的数据导入到存储表中后，我们就可以将存储表中的数据导到其他数据库中去了：
SQL> create table STGTAB as select * from PROFILE_STGTAB@EDGAR;
Table created.
然后将数据导出为profile：
SQL> begin
? 2? DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF (
? 3???? profile_name????????? => 'new_sql_profile',
? 4???? profile_category????? => 'DEV',
? 5???? replace?????????????? => FALSE,
? 6???? staging_table_name??? => 'STGTAB',
? 7???? staging_schema_owner? => 'DEMO');
? 8? end;
? 9? /
PL/SQL procedure successfully completed.
SQL> select name, category, sql_text from DBA_SQL_PROFILES
? 2? ;
NAME?????????????????????????? CATEGORY?????????????? SQL_TEXT
------------------------------ ------------------------------ ----------------------------------------------------
new_sql_profile??????????????? DEV???????????????????????? select /*+no_index(smalltab smalltab_idx1)*/count(*)
???????????????????????????????????????????????????????????????????????? from smalltab where table_name = 'TAB$'
可以看到，profile已经被成功导入。调用该存储过程需要有的CREATE ANY SQL PROFILE系统权限。参数的含义与前面的函数是一样的。
补充：存储表中的数据的删除和存储表的删除。
实际上，存储表数据的删除和存储表的删除和普通表的操作是一样的，使用DELETE和DROP就可以了：
SQL> delete from STGTAB;
1 row deleted.
SQL> drop table STGTAB;
Table dropped.
三、总结
正如文章开始提到的，这个工具让语句调优工作变得非常简单，DBA可以用最短的时间、最好的方式给出优化建议，并有最安全的方式来调试优化结果。
此外，STA还有一套对于数据仓库环境下调忧十分有用的工具：SQL Tuning Set。
