不稳定的执行计划（Oracle执行计划稳定性/不稳定性）

莫张周刘王  论坛元老 | 2025-2-12 11:35:24 | 来自手机 | 显示全部楼层 | 阅读模式
打印 上一主题 下一主题
楼主

主题 1798|帖子 1798|积分 5394

马上注册，结交更多好友，享用更多功能，让你轻松玩转社区。

您需要 登录 才可以下载或查看，没有账号？立即注册

x
本文是翻译Kerry Osborne的Unstable Plans (Oracle Plan Stability/Instability)[1]这篇文章，翻译如有不当的地方，敬请谅解，请尊重原创和翻译劳动成果，转载的时候请注明出处。谢谢！文中有些翻译的地方按自己的理解进行了调解，但是只管符合原文意思。请知晓！
Oracle基于成本的优化器（Cost Based Optimizer，CBO）偶然候表现得非常不稳定，这可能是最令人沮丧的事变之一。它的性能表现偶然似乎是随机的。解决这些问题也颇具挑衅性。这有点像把你的车送去修车厂，因为它发出奇怪的声音，但当修车师傅在场时，它又从不发出那种声音。幸运的是，我们有ASH（Active Session History）和AWR（Automatic Workload Repository）工具，它们可以捕获大量关于数据库其时正在做什么的信息。
这里有两个脚本，我发现它们很有用。
脚本unstable_plans.sql

第一个脚本我定名为unstable_plans.sql. 如下所示
  1. ----------------------------------------------------------------------------------------
  2. --
  3. -- File name:   unstable_plans.sql
  4. --
  5. -- Purpose:     Attempts to find SQL statements with plan instability.
  6. --
  7. -- Author:      Kerry Osborne
  8. --
  9. -- Usage:       This scripts prompts for two values, both of which can be left blank.
  10. --
  11. --              min_stddev: the minimum "normalized" standard deviation between plans 
  12. --                          (the default is 2)
  13. --
  14. --              min_etime:  only include statements that have an avg. etime > this value
  15. --                          (the default is .1 second)
  16. --
  17. -- See http://kerryosborne.oracle-guy.com/2008/10/unstable-plans/ for more info.
  18. ---------------------------------------------------------------------------------------

  20. set lines 155
  21. col execs for 999,999,999
  22. col min_etime for 999,999.99
  23. col max_etime for 999,999.99
  24. col avg_etime for 999,999.999
  25. col avg_lio for 999,999,999.9
  26. col norm_stddev for 999,999.9999
  27. col begin_interval_time for a30
  28. col node for 99999
  29. break on plan_hash_value on startup_time skip 1
  30. select * from (
  31. select sql_id, sum(execs), min(avg_etime) min_etime, max(avg_etime) max_etime, stddev_etime/min(avg_etime) norm_stddev
  32. from (
  33. select sql_id, plan_hash_value, execs, avg_etime,
  34. stddev(avg_etime) over (partition by sql_id) stddev_etime
  35. from (
  36. select sql_id, plan_hash_value,
  37. sum(nvl(executions_delta,0)) execs,
  38. (sum(elapsed_time_delta)/decode(sum(nvl(executions_delta,0)),0,1,sum(executions_delta))/1000000) avg_etime
  39. -- sum((buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta))) avg_lio
  40. from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
  41. where ss.snap_id = S.snap_id
  42. and ss.instance_number = S.instance_number
  43. and executions_delta > 0
  44. group by sql_id, plan_hash_value
  45. )
  46. )
  47. group by sql_id, stddev_etime
  48. )
  49. where norm_stddev > nvl(to_number('&min_stddev'),2)
  50. and max_etime > nvl(to_number('&min_etime'),.1)
  51. order by norm_stddev
  52. /
复制代码
它可以用来表现那些执行时间存在显着/明显差异的SQL语句（它也可以修改为查找逻辑I/O存在差异的SQL，但我会留给读者来完成这个练习）。它使用分析函数来盘算SQL执行计划的平均执行时间的标准方差。因此，那些具有多个计划且计划之间响应时间差异很大的语句将被该脚本返回。脚本会提示输入两个值：第一个是最小标准差数量，第二个是最小执行时间（通常，如果一个语句偶然执行时间为0.005秒，偶然为0.02秒，我并不在意，只管从统计学上来说这是一个很大的颠簸）。顺便说一下，这两个输入都有默认值。
脚本awr_plan_change.sql

第二个脚本为awr_plan_change.sql，如下所示：
  1. set lines 155
  2. col execs for 999,999,999
  3. col avg_etime for 999,999.999
  4. col avg_lio for 999,999,999.9
  5. col begin_interval_time for a30
  6. col node for 99999
  7. break on plan_hash_value on startup_time skip 1
  8. select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
  9. nvl(executions_delta,0) execs,
  10. (elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
  11. (buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio
  12. from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
  13. where sql_id = nvl('&sql_id','4dqs2k5tynk61')
  14. and ss.snap_id = S.snap_id
  15. and ss.instance_number = S.instance_number
  16. and executions_delta > 0
  17. order by 1, 2, 3
  18. /
复制代码
它表现了给定语句的执行计划随时间的变化信息，以及一些统计数据，例如平均执行时间(average elapsed time)和平均逻辑I/O读写(average lio’s)。
总之，这里有一个使用这两个脚本的示例（顺便说一下，示例是在11gR1数据库上进行的，但这些脚本在10g上也能正常工作）。
  1. > sqlplus / as sysdba

  3. SQL*Plus: Release 11.1.0.6.0 - Production on Tue Oct 7 15:44:20 2008

  5. Copyright (c) 1982, 2007, Oracle.  All rights reserved.

  7. Connected to:
  8. Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
  9. With the Partitioning, OLAP, Data Mining and Real Application Testing options

  11. SQL> alter system set optimizer_mode=rule;

  13. System altered.

  15. SQL> @awr_snap 

  17. PL/SQL procedure successfully completed.

  19. SQL> @unstable_plans
  20. Enter value for min_stddev: 
  21. Enter value for min_etime: 

  23. SQL_ID        SUM(EXECS)   MIN_ETIME   MAX_ETIME   NORM_STDDEV
  24. ------------- ---------- ----------- ----------- -------------
  25. c5by9gdw3814r         14         .03         .12        2.1274
  26. 848dyu9288c3h         16         .02         .16        2.1309
  27. 2am60vd2kw8ux         11         .05         .22        2.4976
  28. frxg53fac2n8j          5         .03         .30        4.2479
  29. 0qa98gcnnza7h         62       25.58      314.34        7.9833
  30. g0jvz8csyrtcf          2         .09        1.19        8.2304
  31. 2cn0kc8u4b81w        545         .02         .42       12.8022
  32. 9wt62290ah0f7          6         .01         .47       38.5857
  33. d8mayxqw0wnpv       1373         .01         .85       48.3874

  35. 9 rows selected.

  37. SQL> /
  38. Enter value for min_stddev: 
  39. Enter value for min_etime: 2

  41. SQL_ID        SUM(EXECS)   MIN_ETIME   MAX_ETIME   NORM_STDDEV
  42. ------------- ---------- ----------- ----------- -------------
  43. 0qa98gcnnza7h         62       25.58      314.34        7.9833

  45. SQL> @find_sql
  46. Enter value for sql_text: 
  47. Enter value for address: 
  48. Enter value for sql_id: 0qa98gcnnza7h

  50. SQL_ID         CHILD  PLAN_HASH        EXECS         ETIME     AVG_ETIME USERNAME      SQL_TEXT
  51. ------------- ------ ---------- ------------ ------------- ------------- ------------- -----------------------------------------
  52. 0qa98gcnnza7h      0 3723858078            5        356.53         71.31 SYS           select avg(pk_col) from kso.skew where co
  53.                                                                                        l1 > 0

  55. 0qa98gcnnza7h      1  568322376            1          7.92          7.92 SYS           select avg(pk_col) from kso.skew where co
  56.                                                                                        l1 > 0

  58. 0qa98gcnnza7h      2  568322376           10         52.14          5.21 SYS           select avg(pk_col) from kso.skew where co
  59.                                                                                        l1 > 0

  61. 0qa98gcnnza7h      3  568322376           30      1,064.19         35.47 KSO           select avg(pk_col) from kso.skew where co
  62.                                                                                        l1 > 0

  64. 0qa98gcnnza7h      4 3723858078           10      4,558.62        455.86 KSO           select avg(pk_col) from kso.skew where co
  65.                                                                                        l1 > 0

  67. SQL> @awr_plan_change
  68. Enter value for sql_id: 0qa98gcnnza7h

  70.    SNAP_ID   NODE BEGIN_INTERVAL_TIME            SQL_ID        PLAN_HASH_VALUE        EXECS    AVG_ETIME        AVG_LIO
  71. ---------- ------ ------------------------------ ------------- --------------- ------------ ------------ --------------
  72.       3206      1 02-OCT-08 08.00.38.743 AM      0qa98gcnnza7h       568322376            4       10.359      121,722.8
  73.       3235      1 03-OCT-08 01.00.44.932 PM      0qa98gcnnza7h                            1       10.865      162,375.0
  74.       3235      1 03-OCT-08 01.00.44.932 PM      0qa98gcnnza7h      3723858078            1      127.664   28,913,271.0
  75.       3236      1 03-OCT-08 01.28.09.000 PM      0qa98gcnnza7h       568322376            1        7.924      162,585.0
  76.       3236      1 03-OCT-08 01.28.09.000 PM      0qa98gcnnza7h      3723858078            1       86.682   27,751,123.0
  77.       3305      1 06-OCT-08 10.00.11.988 AM      0qa98gcnnza7h                            4       64.138   22,616,931.5
  78.       3305      1 06-OCT-08 10.00.11.988 AM      0qa98gcnnza7h       568322376            2        5.710       81,149.0
  79.       3306      1 06-OCT-08 11.00.16.490 AM      0qa98gcnnza7h                            6        5.512      108,198.5
  80.       3307      1 06-OCT-08 12.00.20.716 PM      0qa98gcnnza7h                            2        3.824       81,149.0
  81.       3328      1 07-OCT-08 08.39.20.525 AM      0qa98gcnnza7h                           30       35.473      156,904.7
  82.       3335      1 07-OCT-08 03.00.20.950 PM      0qa98gcnnza7h      3723858078           10      455.862   28,902,128.6

  84. 11 rows selected.

  86. SQL> @dplan_awr
  87. Enter value for sql_id: 0qa98gcnnza7h
  88. Enter value for plan_hash_value: 

  90. PLAN_TABLE_OUTPUT
  91. -----------------------------------------------------------------------------------------------------------------------------------------------------------
  92. SQL_ID 0qa98gcnnza7h
  93. --------------------
  94. select avg(pk_col) from kso.skew where col1 > 0

  96. Plan hash value: 568322376

  98. ---------------------------------------------------------------------------
  99. | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
  100. ---------------------------------------------------------------------------
  101. |   0 | SELECT STATEMENT   |      |       |       | 44497 (100)|          |
  102. |   1 |  SORT AGGREGATE    |      |     1 |    11 |            |          |
  103. |   2 |   TABLE ACCESS FULL| SKEW |    32M|   335M| 44497   (2)| 00:08:54 |
  104. ---------------------------------------------------------------------------

  106. SQL_ID 0qa98gcnnza7h
  107. --------------------
  108. select avg(pk_col) from kso.skew where col1 > 0

  110. Plan hash value: 3723858078

  112. --------------------------------------------------
  113. | Id  | Operation                    | Name      |
  114. --------------------------------------------------
  115. |   0 | SELECT STATEMENT             |           |
  116. |   1 |  SORT AGGREGATE              |           |
  117. |   2 |   TABLE ACCESS BY INDEX ROWID| SKEW      |
  118. |   3 |    INDEX RANGE SCAN          | SKEW_COL1 |
  119. --------------------------------------------------

  121. Note
  122. -----
  123.    - rule based optimizer used (consider using cbo)

  125. 33 rows selected.
复制代码
几点简短的评论，unstable_plan.sql 脚本表现了存在多个执行计划/执行计划存在切换的SQL语句。它们中的大多数都是亚秒级响应时间(大多数的响应时间都在一秒以内)。真正引人注目的是sql_id为0qa98gcnnza7h的语句。它被执行了62次，其中一个执行计划的平均执行时间约为25秒，而另一个计划的平均执行时间则约为314秒。awr_plan_changes.sql 脚本表现此语句不绝在两个计划（3723858078 和 568322376）之间切换。3723858078 是效率较低的计划（每次执行执行 25M 左右的逻辑 I/O），而计划 568322376要好得多（每次执行只执行大约 120K 的逻辑I/O）。
当绑定变量窥探（bind variable peeking）出现问题时，我们通常会看到这种现象，即在两个或三个选项之间来回切换执行计划。这篇文章不是专门讨论绑定变量窥探问题的，因此我不会深入先容所有细节（如果你想了解更多信息，只需在此站点上搜刮相关内容）。但我要说的是，无论使用何种绑定变量（在本例中为 568322376 的执行计划），单个计划通常是可以担当的。使用 Outline、SQL Profile 或 Baseline（取决于您使用的 Oracle 版本）让 Oracle 使用特定计划非常容易。而我们在这个案例中正是通过这种方式解决了问题。
参考资料[1] 1: http://kerryosborne.oracle-guy.com/2008/10/08/unstable-plans/
扫描上面二维码关注我如果你至心觉得文章写得不错，而且对你有所资助，那就不妨帮助“推荐"一下，您的“推荐”和”打赏“将是我最大的写作动力！本文版权归作者所有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面显着位置给出原文连接.
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
回复

举报

0 个回复

倒序浏览
返回列表

快速回复

高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 or 立即注册

本版积分规则

莫张周刘王

论坛元老
这个人很懒什么都没写!

楼主热帖

标签云

集成商 AI 运维 CIO 存储 服务器
微信订阅号
微信服务号
微信客服
小程序
H5
关于我们 商务合作 网站地图
©Copyright 2022-2024 杭州祥升科技有限公司 版权所有 浙ICP备20004199号
快速回复 返回顶部 返回列表