sql tuning advisor(STA),是Oracle 10g起 提供的SQL自动优化建议工具。sql profile是用来稳定稳定(或者说固化)sql的执行计划的工具。它是对
outline功能的增强。
今天带来一个 STA 推荐使用 sql profile 来优化sql的例子。
环境准备:
创建测试表
搜集统计信息
执行SQL,优化器选择了 Hash join。估计的成本 是611, 逻辑读是
2192。
用HINT 强制做 Nested Loop方式。成本估算为
7956 (大于Hash Join 的611,难怪没走 NL方式)。但是实际上,逻辑读
1187(比 HJ的
2192 的少)。说明,优化器估算错误。这里是因为,
t1
.
object_name
like
'%T1%',Oracle对like的估算是 按照表行数的 5%。这里过高的估计了,t1表返回的记录数,也就过高估计嵌套的次数。
处理方法,用sql tuning advisor(STA)来搞定它。
用sys用户来查一下 带优化sql的 id
sys用户调用STA
查看优化成果
安装STA建议,来创建sql profile
检查测试结果。安装sql profile固化的执行计划来执行。而且,现在的成本估算比较客观(417, 低于 Hash join的 611)
新的问题: 如果,替换like后面的字符串,sql profile就失效了。换成 t1 . object_name like '%T2%'后,有走Hash join了
新问题的处理的方法。sys用户重新创建sql profile, force_match = > true强制匹配
处理过后
今天带来一个 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.
点击(此处)折叠或打开
- 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
点击(此处)折叠或打开
- 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
用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\")
-
点击(此处)折叠或打开
- 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
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12238525/viewspace-1174894/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/12238525/viewspace-1174894/