ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > sql tuning advisor(STA) 建议 创建sql profile

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

原创 Oracle 作者：huyi_tony 时间：2014-06-03 18:33:41 0 删除 编辑
    sql tuning advisor(STA)，是Oracle 10g起 提供的SQL自动优化建议工具。sql profile是用来稳定稳定（或者说固化）sql的执行计划的工具。它是对outline功能的增强。
今天带来一个 STA 推荐使用 sql profile 来优化sql的例子。

 环境准备：
 创建测试表

点击(此处)折叠或打开

  1. scott@ORCL>create table t1 as select * from dba_objects;

  3. Table created.

  5. scott@ORCL>create table t2 as select * from dba_objects;

  7. Table created.

  9. scott@ORCL>create index ind_t2 on t2(object_id);

  11. Index created.
搜集统计信息

点击(此处)折叠或打开

  1. scott@ORCL> exec dbms_stats.gather_table_stats(user,\'t1\',cascade=>true,method_opt=>\'for all indexed columns size auto\');

  3. PL/SQL procedure successfully completed.

  5. scott@ORCL>exec dbms_stats.gather_table_stats(user,\'t2\',cascade=>true,method_opt=>\'for all indexed columns size auto\');

  7. PL/SQL procedure successfully completed.
执行SQL，优化器选择了 Hash join。估计的成本 是611， 逻辑读是2192。

点击(此处)折叠或打开

  1. scott@ORCL>set autotrace traceonly
  2. scott@ORCL>select t1.*,t2.owner from t1,t2 where t1.object_name like \'%T1%\' and t1.object_id=t2.object_id;

  4. 51 rows selected.


  7. Execution Plan
  8. ----------------------------------------------------------
  9. Plan hash value: 1838229974

  11. ---------------------------------------------------------------------------
  12. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  13. ---------------------------------------------------------------------------
  14. | 0 | SELECT STATEMENT | | 3824 | 407K| 611 (1)| 00:00:08 |
  15. |* 1 | HASH JOIN | | 3824 | 407K| 611 (1)| 00:00:08 |
  16. |* 2 | TABLE ACCESS FULL| T1 | 3824 | 365K| 305 (1)| 00:00:04 |
  17. | 3 | TABLE ACCESS FULL| T2 | 76489 | 821K| 305 (1)| 00:00:04 |
  18. ---------------------------------------------------------------------------

  20. Predicate Information (identified by operation id):
  21. ---------------------------------------------------

  23.    1 - access(\"T1\".\"OBJECT_ID\"=\"T2\".\"OBJECT_ID\")
  24.    2 - filter(\"T1\".\"OBJECT_NAME\" LIKE \'%T1%\' AND \"T1\".\"OBJECT_NAME\" IS
  25.               NOT NULL)


  28. Statistics
  29. ----------------------------------------------------------
  30.           1 recursive calls
  31.           0 db block gets
  32.        2192 consistent gets
  33.           0 physical reads
  34.           0 redo size
  35.        5305 bytes sent via SQL*Net to client
  36.         556 bytes received via SQL*Net from client
  37.           5 SQL*Net roundtrips to/from client
  38.           0 sorts (memory)
  39.           0 sorts (disk)
  40.          51 rows processed
用HINT 强制做 Nested Loop方式。成本估算为7956 （大于Hash Join 的611，难怪没走 NL方式）。但是实际上，逻辑读1187（比 HJ的 2192 的少）。说明，优化器估算错误。这里是因为，t1.object_name like '%T1%'，Oracle对like的估算是 按照表行数的 5%。这里过高的估计了，t1表返回的记录数，也就过高估计嵌套的次数。

点击(此处)折叠或打开

  1. scott@ORCL>select /*+ use_nl(t1 t2) index(t2) */ t1.*,t2.owner
  2.   2 from t1,t2
  3.   3 where t1.object_name like \'%T1%\'
  4.   4 and t1.object_id=t2.object_id;

  6. 51 rows selected.


  9. Execution Plan
  10. ----------------------------------------------------------
  11. Plan hash value: 1386590592

  13. ---------------------------------------------------------------------------------------
  14. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  15. ---------------------------------------------------------------------------------------
  16. | 0 | SELECT STATEMENT | | 3824 | 407K| 7956 (1)| 00:01:36 |
  17. | 1 | NESTED LOOPS | | | | | |
  18. | 2 | NESTED LOOPS | | 3824 | 407K| 7956 (1)| 00:01:36 |
  19. |* 3 | TABLE ACCESS FULL | T1 | 3824 | 365K| 305 (1)| 00:00:04 |
  20. |* 4 | INDEX RANGE SCAN | IND_T2 | 1 | | 1 (0)| 00:00:01 |
  21. | 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 11 | 2 (0)| 00:00:01 |
  22. ---------------------------------------------------------------------------------------

  24. Predicate Information (identified by operation id):
  25. ---------------------------------------------------

  27.    3 - filter(\"T1\".\"OBJECT_NAME\" LIKE \'%T1%\' AND \"T1\".\"OBJECT_NAME\" IS NOT
  28.               NULL)
  29.    4 - access(\"T1\".\"OBJECT_ID\"=\"T2\".\"OBJECT_ID\")


  32. Statistics
  33. ----------------------------------------------------------
  34.           1 recursive calls
  35.           0 db block gets
  36.        1187 consistent gets
  37.           0 physical reads
  38.           0 redo size
  39.        5305 bytes sent via SQL*Net to client
  40.         556 bytes received via SQL*Net from client
  41.           5 SQL*Net roundtrips to/from client
  42.           0 sorts (memory)
  43.           0 sorts (disk)
  44.          51 rows processed
处理方法，用sql tuning advisor(STA)来搞定它。

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

点击(此处)折叠或打开

  1. sys@ORCL>select sql_id, sql_text from v$sql where sql_text like \'select t1.*,t2.owner%\';

  3. SQL_ID
  4. -------------
  5. SQL_TEXT
  6. -----------------------------------------------------------------------------------------------
  7. 4zbqykx89yc8v
  8. select t1.*,t2.owner from t1,t2 where t1.object_name like \'%T1%\' and t1.object_id=t2.object_id

sys用户调用STA

点击(此处)折叠或打开

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

查看优化成果

点击(此处)折叠或打开

  1. sys@ORCL>SELECT dbms_sqltune.report_tuning_task(:tuning_task) FROM dual;

  3. DBMS_SQLTUNE.REPORT_TUNING_TASK(:TUNING_TASK)
  4. --------------------------------------------------------------------------------
  5. GENERAL INFORMATION SECTION
  6. -------------------------------------------------------------------------------
  7. Tuning Task Name : TASK_567
  8. Tuning Task Owner : SYS
  9. Workload Type : Single SQL Statement
  10. Scope : COMPREHENSIVE
  11. Time Limit(seconds): 1800
  12. Completion Status : COMPLETED
  13. Started at : 06/03/2014 02:43:02
  14. Completed at : 06/03/2014 02:43:06

  16. -------------------------------------------------------------------------------
  17. Schema Name: SCOTT
  18. SQL ID : 4zbqykx89yc8v
  19. SQL Text : select t1.*,t2.owner from t1,t2 where t1.object_name like \'%T1%\'
  20.              and t1.object_id=t2.object_id

  22. -------------------------------------------------------------------------------
  23. FINDINGS SECTION (1 finding)
  24. -------------------------------------------------------------------------------

  26. 1- SQL Profile Finding (see explain plans section below)
  27. --------------------------------------------------------
  28.   A potentially better execution plan was found for this statement.

  30.   Recommendation (estimated benefit: 46.23%)                                    -- STA给出的建议 创建 sql profile
  31.   ------------------------------------------
  32.   - Consider accepting the recommended SQL profile.
  33.     execute dbms_sqltune.accept_sql_profile(task_name => \'TASK_567\',
  34.             task_owner => \'SYS\', replace => TRUE);

  36.   Validation results
  37.   ------------------
  38.   The SQL profile was tested by executing both its plan and the original plan
  39.   and measuring their respective execution statistics. A plan may have been
  40.   only partially executed if the other could be run to completion in less time.

  42.                            Original Plan With SQL Profile % Improved
  43.                            ------------- ---------------- ----------
  44.   Completion Status: COMPLETE COMPLETE
  45.   Elapsed Time (s): .069781 .061671 11.62 %
  46.   CPU Time (s): .069689 .06179 11.33 %
  47.   User I/O Time (s): 0 0
  48.   Buffer Gets: 2188 1174 46.34 %
  49.   Physical Read Requests: 0 0
  50.   Physical Write Requests: 0 0
  51.   Physical Read Bytes: 0 0
  52.   Physical Write Bytes: 0 0
  53.   Rows Processed: 51 51
  54.   Fetches: 51 51
  55.   Executions: 1 1

  57.   Notes
  58.   -----
  59.   1. Statistics for the original plan were averaged over 10 executions.
  60.   2. Statistics for the SQL profile plan were averaged over 10 executions.

  62. -------------------------------------------------------------------------------
  63. EXPLAIN PLANS SECTION
  64. -------------------------------------------------------------------------------

  66. 1- Original With Adjusted Cost
  67. ------------------------------
  68. Plan hash value: 1838229974

  70. ---------------------------------------------------------------------------
  71. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  72. ---------------------------------------------------------------------------
  73. | 0 | SELECT STATEMENT | | 56 | 6104 | 611 (1)| 00:00:08 |
  74. |* 1 | HASH JOIN | | 56 | 6104 | 611 (1)| 00:00:08 |
  75. |* 2 | TABLE ACCESS FULL| T1 | 56 | 5488 | 305 (1)| 00:00:04 |
  76. | 3 | TABLE ACCESS FULL| T2 | 76489 | 821K| 305 (1)| 00:00:04 |
  77. ---------------------------------------------------------------------------

  79. Predicate Information (identified by operation id):
  80. ---------------------------------------------------

  82.    1 - access(\"T1\".\"OBJECT_ID\"=\"T2\".\"OBJECT_ID\")
  83.    2 - filter(\"T1\".\"OBJECT_NAME\" LIKE \'%T1%\' AND \"T1\".\"OBJECT_NAME\" IS
  84.               NOT NULL)

  86. 2- Using SQL Profile                                                                -- sql profile 固化的执行计划 走NL
  87. --------------------
  88. Plan hash value: 1386590592

  90. --------------------------------------------------------------------------------
  91. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Tim
  92. e |
  93. --------------------------------------------------------------------------------
  94. | 0 | SELECT STATEMENT | | 56 | 6104 | 417 (1)| 00:
  95. 00:06 |
  96. | 1 | NESTED LOOPS | | | | |
  97.       |
  98. | 2 | NESTED LOOPS | | 56 | 6104 | 417 (1)| 00:
  99. 00:06 |

  101. DBMS_SQLTUNE.REPORT_TUNING_TASK(:TUNING_TASK)
  102. --------------------------------------------------------------------------------
  103. |* 3 | TABLE ACCESS FULL | T1 | 56 | 5488 | 305 (1)| 00:
  104. 00:04 |
  105. |* 4 | INDEX RANGE SCAN | IND_T2 | 1 | | 1 (0)| 00:
  106. 00:01 |
  107. | 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 11 | 2 (0)| 00:
  108. 00:01 |
  109. --------------------------------------------------------------------------------

  111. Predicate Information (identified by operation id):
  112. ---------------------------------------------------

  114.    3 - filter(\"T1\".\"OBJECT_NAME\" LIKE \'%T1%\' AND \"T1\".\"OBJECT_NAME\" IS NOT
  115.               NULL)
  116.    4 - access(\"T1\".\"OBJECT_ID\"=\"T2\".\"OBJECT_ID\")

安装STA建议，来创建sql profile

点击(此处)折叠或打开

  1. sys@ORCL>execute dbms_sqltune.accept_sql_profile(task_name => \'TASK_567\', task_owner => \'SYS\', replace => TRUE);

  3. PL/SQL procedure successfully completed.

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


点击(此处)折叠或打开

  1. scott@ORCL>set autotrace traceonly
  2. scott@ORCL>select t1.*,t2.owner from t1,t2 where t1.object_name like \'%T1%\' and t1.object_id=t2.object_id;

  4. 51 rows selected.


  7. Execution Plan
  8. ----------------------------------------------------------
  9. Plan hash value: 1386590592

  11. ---------------------------------------------------------------------------------------
  12. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  13. ---------------------------------------------------------------------------------------
  14. | 0 | SELECT STATEMENT | | 56 | 6104 | 417 (1)| 00:00:06 |
  15. | 1 | NESTED LOOPS | | | | | |
  16. | 2 | NESTED LOOPS | | 56 | 6104 | 417 (1)| 00:00:06 |
  17. |* 3 | TABLE ACCESS FULL | T1 | 56 | 5488 | 305 (1)| 00:00:04 |
  18. |* 4 | INDEX RANGE SCAN | IND_T2 | 1 | | 1 (0)| 00:00:01 |
  19. | 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 11 | 2 (0)| 00:00:01 |
  20. ---------------------------------------------------------------------------------------

  22. Predicate Information (identified by operation id):
  23. ---------------------------------------------------

  25.    3 - filter(\"T1\".\"OBJECT_NAME\" LIKE \'%T1%\' AND \"T1\".\"OBJECT_NAME\" IS NOT
  26.               NULL)
  27.    4 - access(\"T1\".\"OBJECT_ID\"=\"T2\".\"OBJECT_ID\")

  29. Note
  30. -----
  31.    - SQL profile \"SYS_SQLPROF_01466078fe610000\" used for this statement                  -- SQL profile 生效了，按照固化的执行计划走


  34. Statistics
  35. ----------------------------------------------------------
  36.          35 recursive calls
  37.           0 db block gets
  38.        1198 consistent gets
  39.           1 physical reads
  40.           0 redo size
  41.        5305 bytes sent via SQL*Net to client
  42.         556 bytes received via SQL*Net from client
  43.           5 SQL*Net roundtrips to/from client
  44.           1 sorts (memory)
  45.           0 sorts (disk)
  46.          51 rows processed

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


点击(此处)折叠或打开

  1. scott@ORCL>select t1.*,t2.owner from t1,t2 where t1.object_name like \'%T2%\' and t1.object_id=t2.object_id;

  3. 124 rows selected.


  6. Execution Plan
  7. ----------------------------------------------------------
  8. Plan hash value: 1838229974

  10. ---------------------------------------------------------------------------
  11. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  12. ---------------------------------------------------------------------------
  13. | 0 | SELECT STATEMENT | | 3824 | 407K| 611 (1)| 00:00:08 |
  14. |* 1 | HASH JOIN | | 3824 | 407K| 611 (1)| 00:00:08 |
  15. |* 2 | TABLE ACCESS FULL| T1 | 3824 | 365K| 305 (1)| 00:00:04 |
  16. | 3 | TABLE ACCESS FULL| T2 | 76489 | 821K| 305 (1)| 00:00:04 |
  17. ---------------------------------------------------------------------------

  19. Predicate Information (identified by operation id):
  20. ---------------------------------------------------

  22.    1 - access(\"T1\".\"OBJECT_ID\"=\"T2\".\"OBJECT_ID\")
  23.    2 - filter(\"T1\".\"OBJECT_NAME\" LIKE \'%T2%\' AND \"T1\".\"OBJECT_NAME\" IS
  24.               NOT NULL)


  27. Statistics
  28. ----------------------------------------------------------
  29.           1 recursive calls
  30.           0 db block gets
  31.        2197 consistent gets
  32.           0 physical reads
  33.           0 redo size
  34.        9356 bytes sent via SQL*Net to client
  35.         611 bytes received via SQL*Net from client
  36.          10 SQL*Net roundtrips to/from client
  37.           0 sorts (memory)
  38.           0 sorts (disk)
  39.         124 rows processed

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

点击(此处)折叠或打开

  1. sys@ORCL>execute dbms_sqltune.accept_sql_profile(task_name =>\'TASK_567\', task_owner=>\'SYS\', replace=>TRUE,force_match=>true);

  3. PL/SQL procedure successfully completed.

处理过后

点击(此处)折叠或打开

  1. scott@ORCL>select t1.*,t2.owner from t1,t2 where t1.object_name like \'%T2%\' and t1.object_id=t2.object_id;

  3. 124 rows selected.


  6. Execution Plan
  7. ----------------------------------------------------------
  8. Plan hash value: 1386590592

  10. ---------------------------------------------------------------------------------------
  11. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  12. ---------------------------------------------------------------------------------------
  13. | 0 | SELECT STATEMENT | | 56 | 6104 | 417 (1)| 00:00:06 |
  14. | 1 | NESTED LOOPS | | | | | |
  15. | 2 | NESTED LOOPS | | 56 | 6104 | 417 (1)| 00:00:06 |
  16. |* 3 | TABLE ACCESS FULL | T1 | 56 | 5488 | 305 (1)| 00:00:04 |
  17. |* 4 | INDEX RANGE SCAN | IND_T2 | 1 | | 1 (0)| 00:00:01 |
  18. | 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 11 | 2 (0)| 00:00:01 |
  19. ---------------------------------------------------------------------------------------

  21. Predicate Information (identified by operation id):
  22. ---------------------------------------------------

  24.    3 - filter(\"T1\".\"OBJECT_NAME\" LIKE \'%T2%\' AND \"T1\".\"OBJECT_NAME\" IS NOT
  25.               NULL)
  26.    4 - access(\"T1\".\"OBJECT_ID\"=\"T2\".\"OBJECT_ID\")

  28. Note
  29. -----
  30.    - SQL profile \"SYS_SQLPROF_0146607cb3e40001\" used for this statement                     -- 这里 SQL profile又生效了


  33. Statistics
  34. ----------------------------------------------------------
  35.           6 recursive calls
  36.           0 db block gets
  37.        1253 consistent gets
  38.           1 physical reads
  39.           0 redo size
  40.        9356 bytes sent via SQL*Net to client
  41.         611 bytes received via SQL*Net from client
  42.          10 SQL*Net roundtrips to/from client
  43.           0 sorts (memory)
  44.           0 sorts (disk)
  45.         124 rows processed




来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/12238525/viewspace-1174894/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： 自动任务出错 ORA-12012 ORA-04063
下一篇： STA 建议收集统计信息
请登录后发表评论 登录
全部评论
huyi_tony

注册时间：2011-07-21

  • 博文量
    22
  • 访问量
    70546

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593