Automatic SQL Tuning in Oracle Database
oracle 10g可以使优化器运行在优化模式收集额外的信息，并且对当前sql做出一些建议，使其运行的更好。使用STA一定要保证优化器是CBO模式下。
在优化模式下，优化器可以给出以下的一些建议：
Statistics Analysis - The optimizer recommends the gathering of statistics on objects with missing or stale statistics. Additional statistics for these objects are stored in an SQL profile.
SQL Profiling - The optimizer may be able to improve performance by gathering additional statistics and altering session specific parameters such as the OPTIMIZER_MODE. If such improvements are possible the information is stored in an SQL profile. If accepted this information can then used by the optimizer when running in normal mode. Unlike a stored outline which fixes the execution plan, an SQL profile may still be of benefit when the contents of the table alter drastically. Even so, it's sensible to update profiles periodically. The SQL profiling is not performed when the tuining optimizer is run in limited mode.
Access Path Analysis - The optimizer investigates the effect of new or modified indexes on the access path. It's index recommendations relate to a specific statement so where necessary it will also suggest the use of the SQL Access Advisor to check the impact of these indexes on a representative SQL workload.
SQL Structure Analysis - The optimizer suggests alternatives for SQL statements that contain structures that may impact on performance. The implementation of these suggestions requires human intervention to check their validity.
执行DBMS_SQLTUNE包进行sql优化需要有advisor的权限：
CONN sys/password AS SYSDBA；
GRANT ADVISOR TO scott;
CONN scott/tiger；
我们使用dba_objects 来创建两张表，一张大表，一张小表，不去收集统计信息，不去创建索引。其中t1为小表，t为大表
SQL> select count(*) from t1;
COUNT(*)
----------
753888
SQL> select count(*) from t;
COUNT(*)
----------
1884641
SQL> select count(*) from t,t1 where t.object_id=t1.object_id;
已用时间: 00: 00: 29.92
执行计划
----------------------------------------------------------
Plan hash value: 949044725
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | | 33921 (1)|999:5959 |
| 1 | SORT AGGREGATE | | 1 | 26 | | | |
|* 2 | HASH JOIN | | 940M| 22G| 184M| 33921 (1)|999:59 59 |
| 3 | TABLE ACCESS FULL| T1 | 7751K| 96M| | 3716 (0)|210:1045 |
| 4 | TABLE ACCESS FULL| T | 10M| 124M| | 9156 (0)|517:5206|
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."OBJECT_ID"="T1"."OBJECT_ID")
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
2 recursive calls
2 db block gets
37632 consistent gets
10603 physical reads
352 redo size
530 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
1.创建一个新的tuning task，使用到的函数为CREATE_TUNING_TASK function
创建任务有以下几种方式：
SQL text format:
DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text IN CLOB,
bind_list IN sql_binds := NULL,
user_name IN VARCHAR2 := NULL,
scope IN VARCHAR2 := SCOPE_COMPREHENSIVE,
time_limit IN NUMBER := TIME_LIMIT_DEFAULT,
task_name IN VARCHAR2 := NULL,
description IN VARCHAR2 := NULL)
RETURN VARCHAR2;
SQL ID format:
DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_id IN VARCHAR2,
plan_hash_value IN NUMBER := NULL,
scope IN VARCHAR2 := SCOPE_COMPREHENSIVE,
time_limit IN NUMBER := TIME_LIMIT_DEFAULT,
task_name IN VARCHAR2 := NULL,
description IN VARCHAR2 := NULL)
RETURN VARCHAR2;
Workload Repository format:
DBMS_SQLTUNE.CREATE_TUNING_TASK(
begin_snap IN NUMBER,
end_snap IN NUMBER,
sql_id IN VARCHAR2,
plan_hash_value IN NUMBER := NULL,
scope IN VARCHAR2 := SCOPE_COMPREHENSIVE,
time_limit IN NUMBER := TIME_LIMIT_DEFAULT,
task_name IN VARCHAR2 := NULL,
description IN VARCHAR2 := NULL)
RETURN VARCHAR2;
SQLSET format:
DBMS_SQLTUNE.CREATE_TUNING_TASK(
sqlset_name IN VARCHAR2,
basic_filter IN VARCHAR2 := NULL,
object_filter IN VARCHAR2 := NULL,
rank1 &n
