sql tuning advisor(STA)，是Oracle 10g起提供的SQL自动优化建议工具。sql profile是用来稳定稳定（或者说固化）sql的执行计划的工具。它是对outline功能的增强。
今天带来一个 STA 推荐使用 sql profile 来优化sql的例子。

环境准备：
创建测试表

点击(此处)折叠或打开

scott@ORCL>create table t1 as select * from dba_objects;
Table created.
scott@ORCL>create table t2 as select * from dba_objects;
Table created.
scott@ORCL>create index ind_t2 on t2(object_id);
Index created.

搜集统计信息

点击(此处)折叠或打开

scott@ORCL> exec dbms_stats.gather_table_stats(user,\'t1\',cascade=>true,method_opt=>\'for all indexed columns size auto\');
PL/SQL procedure successfully completed.
scott@ORCL>exec dbms_stats.gather_table_stats(user,\'t2\',cascade=>true,method_opt=>\'for all indexed columns size auto\');
PL/SQL procedure successfully completed.

执行SQL，优化器选择了 Hash join。估计的成本是611，逻辑读是2192。

点击(此处)折叠或打开

scott@ORCL>set autotrace traceonly
scott@ORCL>select t1.*,t2.owner from t1,t2 where t1.object_name like \'%T1%\' and t1.object_id=t2.object_id;
51 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1838229974
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3824 | 407K| 611 (1)| 00:00:08 |
|* 1 | HASH JOIN | | 3824 | 407K| 611 (1)| 00:00:08 |
|* 2 | TABLE ACCESS FULL| T1 | 3824 | 365K| 305 (1)| 00:00:04 |
| 3 | TABLE ACCESS FULL| T2 | 76489 | 821K| 305 (1)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access(\"T1\".\"OBJECT_ID\"=\"T2\".\"OBJECT_ID\")
2 - filter(\"T1\".\"OBJECT_NAME\" LIKE \'%T1%\' AND \"T1\".\"OBJECT_NAME\" IS
NOT NULL)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2192 consistent gets
0 physical reads
0 redo size
5305 bytes sent via SQL*Net to client
556 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
51 rows processed

用HINT 强制做 Nested Loop方式。成本估算为7956 （大于Hash Join 的611，难怪没走 NL方式）。但是实际上，逻辑读1187（比 HJ的 2192 的少）。说明，优化器估算错误。这里是因为，t1.object_name like '%T1%'，Oracle对like的估算是按照表行数的 5%。这里过高的估计了，t1表返回的记录数，也就过高估计嵌套的次数。

点击(此处)折叠或打开

scott@ORCL>select /*+ use_nl(t1 t2) index(t2) */ t1.*,t2.owner
2 from t1,t2
3 where t1.object_name like \'%T1%\'
4 and t1.object_id=t2.object_id;
51 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1386590592
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3824 | 407K| 7956 (1)| 00:01:36 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 3824 | 407K| 7956 (1)| 00:01:36 |
|* 3 | TABLE ACCESS FULL | T1 | 3824 | 365K| 305 (1)| 00:00:04 |
|* 4 | INDEX RANGE SCAN | IND_T2 | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 11 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(\"T1\".\"OBJECT_NAME\" LIKE \'%T1%\' AND \"T1\".\"OBJECT_NAME\" IS NOT
NULL)
4 - access(\"T1\".\"OBJECT_ID\"=\"T2\".\"OBJECT_ID\")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1187 consistent gets
0 physical reads
0 redo size
5305 bytes sent via SQL*Net to client
556 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
51 rows processed

处理方法，用sql tuning advisor(STA)来搞定它。

用sys用户来查一下带优化sql的 id

点击(此处)折叠或打开

sys@ORCL>select sql_id, sql_text from v$sql where sql_text like \'select t1.*,t2.owner%\';
SQL_ID
-------------
SQL_TEXT
-----------------------------------------------------------------------------------------------
4zbqykx89yc8v
select t1.*,t2.owner from t1,t2 where t1.object_name like \'%T1%\' and t1.object_id=t2.object_id

sys用户调用STA

点击(此处)折叠或打开

var tuning_task varchar2(100); -- 创建优化任务时，返回的任务名用绑定变量保存
BEGIN
:tuning_task := dbms_sqltune.create_tuning_task(sql_id => \'4zbqykx89yc8v\'); -- 创建优化任务，输入待优化sql的id
dbms_sqltune.execute_tuning_task(:tuning_task); -- 执行优化任务
dbms_output.put_line(:tuning_task);
END;
/

查看优化成果

点击(此处)折叠或打开

sys@ORCL>SELECT dbms_sqltune.report_tuning_task(:tuning_task) FROM dual;
DBMS_SQLTUNE.REPORT_TUNING_TASK(:TUNING_TASK)
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : TASK_567
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 06/03/2014 02:43:02
Completed at : 06/03/2014 02:43:06
-------------------------------------------------------------------------------
Schema Name: SCOTT
SQL ID : 4zbqykx89yc8v
SQL Text : select t1.*,t2.owner from t1,t2 where t1.object_name like \'%T1%\'
and t1.object_id=t2.object_id
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 46.23%) -- STA给出的建议创建 sql profile
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => \'TASK_567\',
task_owner => \'SYS\', replace => TRUE);
Validation results
------------------
The SQL profile was tested by executing both its plan and the original plan
and measuring their respective execution statistics. A plan may have been
only partially executed if the other could be run to completion in less time.
Original Plan With SQL Profile % Improved
------------- ---------------- ----------
Completion Status: COMPLETE COMPLETE
Elapsed Time (s): .069781 .061671 11.62 %
CPU Time (s): .069689 .06179 11.33 %
User I/O Time (s): 0 0
Buffer Gets: 2188 1174 46.34 %
Physical Read Requests: 0 0
Physical Write Requests: 0 0
Physical Read Bytes: 0 0
Physical Write Bytes: 0 0
Rows Processed: 51 51
Fetches: 51 51
Executions: 1 1
Notes
-----
1. Statistics for the original plan were averaged over 10 executions.
2. Statistics for the SQL profile plan were averaged over 10 executions.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 1838229974
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 56 | 6104 | 611 (1)| 00:00:08 |
|* 1 | HASH JOIN | | 56 | 6104 | 611 (1)| 00:00:08 |
|* 2 | TABLE ACCESS FULL| T1 | 56 | 5488 | 305 (1)| 00:00:04 |
| 3 | TABLE ACCESS FULL| T2 | 76489 | 821K| 305 (1)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access(\"T1\".\"OBJECT_ID\"=\"T2\".\"OBJECT_ID\")
2 - filter(\"T1\".\"OBJECT_NAME\" LIKE \'%T1%\' AND \"T1\".\"OBJECT_NAME\" IS
NOT NULL)
2- Using SQL Profile -- sql profile 固化的执行计划走NL
--------------------
Plan hash value: 1386590592
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Tim
e |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 56 | 6104 | 417 (1)| 00:
00:06 |
| 1 | NESTED LOOPS | | | | |
|
| 2 | NESTED LOOPS | | 56 | 6104 | 417 (1)| 00:
00:06 |
DBMS_SQLTUNE.REPORT_TUNING_TASK(:TUNING_TASK)
--------------------------------------------------------------------------------
|* 3 | TABLE ACCESS FULL | T1 | 56 | 5488 | 305 (1)| 00:
00:04 |
|* 4 | INDEX RANGE SCAN | IND_T2 | 1 | | 1 (0)| 00:
00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 11 | 2 (0)| 00:
00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(\"T1\".\"OBJECT_NAME\" LIKE \'%T1%\' AND \"T1\".\"OBJECT_NAME\" IS NOT
NULL)
4 - access(\"T1\".\"OBJECT_ID\"=\"T2\".\"OBJECT_ID\")

安装STA建议，来创建sql profile

点击(此处)折叠或打开

sys@ORCL>execute dbms_sqltune.accept_sql_profile(task_name => \'TASK_567\', task_owner => \'SYS\', replace => TRUE);
PL/SQL procedure successfully completed.

检查测试结果。安装sql profile固化的执行计划来执行。而且，现在的成本估算比较客观（417，低于 Hash join的 611）

点击(此处)折叠或打开

scott@ORCL>set autotrace traceonly
scott@ORCL>select t1.*,t2.owner from t1,t2 where t1.object_name like \'%T1%\' and t1.object_id=t2.object_id;
51 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1386590592
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 56 | 6104 | 417 (1)| 00:00:06 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 56 | 6104 | 417 (1)| 00:00:06 |
|* 3 | TABLE ACCESS FULL | T1 | 56 | 5488 | 305 (1)| 00:00:04 |
|* 4 | INDEX RANGE SCAN | IND_T2 | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 11 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(\"T1\".\"OBJECT_NAME\" LIKE \'%T1%\' AND \"T1\".\"OBJECT_NAME\" IS NOT
NULL)
4 - access(\"T1\".\"OBJECT_ID\"=\"T2\".\"OBJECT_ID\")
Note
-----
- SQL profile \"SYS_SQLPROF_01466078fe610000\" used for this statement -- SQL profile 生效了，按照固化的执行计划走
Statistics
----------------------------------------------------------
35 recursive calls
0 db block gets
1198 consistent gets
1 physical reads
0 redo size
5305 bytes sent via SQL*Net to client
556 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
51 rows processed

新的问题：如果，替换like后面的字符串，sql profile就失效了。换成 t1.object_name like '%T2%'后，有走Hash join了

点击(此处)折叠或打开

scott@ORCL>select t1.*,t2.owner from t1,t2 where t1.object_name like \'%T2%\' and t1.object_id=t2.object_id;
124 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1838229974
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3824 | 407K| 611 (1)| 00:00:08 |
|* 1 | HASH JOIN | | 3824 | 407K| 611 (1)| 00:00:08 |
|* 2 | TABLE ACCESS FULL| T1 | 3824 | 365K| 305 (1)| 00:00:04 |
| 3 | TABLE ACCESS FULL| T2 | 76489 | 821K| 305 (1)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access(\"T1\".\"OBJECT_ID\"=\"T2\".\"OBJECT_ID\")
2 - filter(\"T1\".\"OBJECT_NAME\" LIKE \'%T2%\' AND \"T1\".\"OBJECT_NAME\" IS
NOT NULL)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2197 consistent gets
0 physical reads
0 redo size
9356 bytes sent via SQL*Net to client
611 bytes received via SQL*Net from client
10 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
124 rows processed

新问题的处理的方法。sys用户重新创建sql profile，force_match=>true强制匹配

点击(此处)折叠或打开

sys@ORCL>execute dbms_sqltune.accept_sql_profile(task_name =>\'TASK_567\', task_owner=>\'SYS\', replace=>TRUE,force_match=>true);
PL/SQL procedure successfully completed.

处理过后

点击(此处)折叠或打开

scott@ORCL>select t1.*,t2.owner from t1,t2 where t1.object_name like \'%T2%\' and t1.object_id=t2.object_id;
124 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1386590592
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 56 | 6104 | 417 (1)| 00:00:06 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 56 | 6104 | 417 (1)| 00:00:06 |
|* 3 | TABLE ACCESS FULL | T1 | 56 | 5488 | 305 (1)| 00:00:04 |
|* 4 | INDEX RANGE SCAN | IND_T2 | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 11 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(\"T1\".\"OBJECT_NAME\" LIKE \'%T2%\' AND \"T1\".\"OBJECT_NAME\" IS NOT
NULL)
4 - access(\"T1\".\"OBJECT_ID\"=\"T2\".\"OBJECT_ID\")
Note
-----
- SQL profile \"SYS_SQLPROF_0146607cb3e40001\" used for this statement -- 这里 SQL profile又生效了
Statistics
----------------------------------------------------------
6 recursive calls
0 db block gets
1253 consistent gets
1 physical reads
0 redo size
9356 bytes sent via SQL*Net to client
611 bytes received via SQL*Net from client
10 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
124 rows processed

sql tuning advisor(STA) 建议 创建sql profile

sql tuning advisor(STA) 建议创建sql profile