温馨提示×

温馨提示×

您好，登录后才能下订单哦！

密码登录×
登录注册×
获取短信验证码
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》
登 录 注册有礼
云服务器 香港服务器 高防服务器
最新更新 网站标签 地图导航
产品

使用sql baseline替换执行计划

发布时间：2020-08-09 04:09:13 来源：ITPUB博客 阅读：175 作者：wwjfeng 栏目：关系型数据库

1.分别执行下列SQL

点击(此处)折叠或打开

  1. SQL1:select /*www1*/ /*+ full(LOGIN_LOG) */IP_ADDRESS from LOGIN_LOG where session_id=1273523;
  2. SQL2:select /*www2*/ /*+ index(LOGIN_LOG LOGIN_LOG_PK) */IP_ADDRESS from LOGIN_LOG where session_id=1273523;

2.查看SQL_ID和PLAN_HASH_VALUE

点击(此处)折叠或打开

  1. select * from v$sql where sql_text like '%www1%'
  2. select * from v$sql where sql_text like '%www2%'
  3. SQL1: 2pqkr80bqn6wb 3779830307
  4. SQL2: 7510s3wam524g 3865870674

3.查看执行计划

点击(此处)折叠或打开

  1. SQL1
  2. SQL> select * from table(dbms_xplan.display_cursor('2pqkr80bqn6wb','',''));
  3. PLAN_TABLE_OUTPUT
  4. --------------------------------------------------------------------------------
  5. SQL_ID 2pqkr80bqn6wb, child number 0
  6. -------------------------------------
  7. select /*www1*/ /*+ full(LOGIN_LOG) */IP_ADDRESS from LOGIN_LOG where
  8. session_id=1273523
  9. Plan hash value: 3779830307
  10. -------------------------------------------------------------------------------
  11. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  12. -------------------------------------------------------------------------------
  13. | 0 | SELECT STATEMENT | | | | 95461 (100)| |
  14. PLAN_TABLE_OUTPUT
  15. --------------------------------------------------------------------------------
  16. |* 1 | TABLE ACCESS FULL| LOGIN_LOG | 286K| 10M| 95461 (1)| 00:19:06 |
  17. -------------------------------------------------------------------------------
  18. Predicate Information (identified by operation id):
  19. ---------------------------------------------------
  20.    1 - filter("SESSION_ID"=1273523)

  22. 19 rows selected.
  23. SQL2
  24. SQL> select * from table(dbms_xplan.display_cursor('7510s3wam524g','',''));
  25. PLAN_TABLE_OUTPUT
  26. --------------------------------------------------------------------------------
  27. SQL_ID 7510s3wam524g, child number 0
  28. -------------------------------------
  29. select /*www2*/ /*+ index(LOGIN_LOG LOGIN_LOG_PK) */IP_ADDRESS from
  30. LOGIN_LOG where session_id=1273523
  31. Plan hash value: 3865870674
  32. --------------------------------------------------------------------------------
  33. ------------
  34. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)
  35. PLAN_TABLE_OUTPUT
  36. --------------------------------------------------------------------------------
  37. | Time |
  38. --------------------------------------------------------------------------------
  39. ------------
  40. | 0 | SELECT STATEMENT | | | | 3433 (100)
  41. | |
  42. | 1 | TABLE ACCESS BY INDEX ROWID| LOGIN_LOG | 286K| 10M| 3433 (1)
  43. | 00:00:42 |

  45. PLAN_TABLE_OUTPUT
  46. --------------------------------------------------------------------------------
  47. |* 2 | INDEX RANGE SCAN | LOGIN_LOG_PK | 114K| | 3 (0)
  48. | 00:00:01 |
  49. --------------------------------------------------------------------------------
  50. ------------

  52. Predicate Information (identified by operation id):
  53. ---------------------------------------------------
  54.    2 - access("SESSION_ID"=1273523)
  55. PLAN_TABLE_OUTPUT
  56. --------------------------------------------------------------------------------

  58. 20 rows selected.

4.从库缓存中为SQL1创建baseline

点击(此处)折叠或打开

  1. DECLARE
  2.   l_plans_loaded PLS_INTEGER;
  3. BEGIN
  4.   l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(sql_id => '2pqkr80bqn6wb',plan_hash_value=>'3779830307');
  5. END;
  6. /
  7. select sql_handle,plan_name,dbms_lob.substr(sql_text,60,1) sql_text,ACCEPTED from dba_sql_plan_baselines;
  8. SQL_d3e16c6839796f24 SQL_PLAN_d7sbcd0wrkvt433a13db8 select /*www1*/ /*+ full(LOGIN_LOG) */IP_ADDRESS from LOGIN_ YES

5.将符合我们预期的SQL2的执行计划的载入到第一次生成的sql baseline中

点击(此处)折叠或打开

  1. DECLARE
  2.  k1 pls_integer;
  3.  begin
  4.   k1 := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
  5.   sql_id=>'7510s3wam524g',
  6.   plan_hash_value=>3865870674,sql_handle=>'SQL_d3e16c6839796f24'
  7.   );
  8. end;
  9. /
  10. 基线SQL_d3e16c6839796f24出现2个执行计划
  11. select sql_handle,plan_name,dbms_lob.substr(sql_text,60,1) sql_text,ACCEPTED from dba_sql_plan_baselines;
  12. SQL_d3e16c6839796f24 SQL_PLAN_d7sbcd0wrkvt433a13db8 select /*www1*/ /*+ full(LOGIN_LOG) */IP_ADDRESS from LOGIN_ YES
  13. SQL_d3e16c6839796f24 SQL_PLAN_d7sbcd0wrkvt47b166b46 select /*www1*/ /*+ full(LOGIN_LOG) */IP_ADDRESS from LOGIN_ YES

6.修改原先SQL2执行计划的状态为fixed

点击(此处)折叠或打开

  1. SET SERVEROUTPUT ON
  2. DECLARE
  3.  v_text PLS_INTEGER;
  4. BEGIN
  5.  v_text := DBMS_SPM.alter_sql_plan_baseline(sql_handle => 'SQL_d3e16c6839796f24',plan_name => 'SQL_PLAN_d7sbcd0wrkvt47b166b46',
  6.       attribute_name => 'fixed',attribute_value => 'YES');
  7.   DBMS_OUTPUT.put_line('Plans Altered: ' || v_text );
  8. END;
  9. /
  10. select sql_handle,plan_name,dbms_lob.substr(sql_text,60,1) sql_text,ACCEPTED,FIXED from dba_sql_plan_baselines;
  11. SQL_d3e16c6839796f24 SQL_PLAN_d7sbcd0wrkvt433a13db8 select /*www1*/ /*+ full(LOGIN_LOG) */IP_ADDRESS from LOGIN_ YES NO
  12. SQL_d3e16c6839796f24 SQL_PLAN_d7sbcd0wrkvt47b166b46 select /*www1*/ /*+ full(LOGIN_LOG) */IP_ADDRESS from LOGIN_ YES YES

7.原SQL1执行计划被改变

点击(此处)折叠或打开

  1. SQL> select /*www1*/ /*+ full(LOGIN_LOG) */IP_ADDRESS from LOGIN_LOG where session_id=1273523;
  2. Execution Plan
  3. ----------------------------------------------------------
  4. --------------------------------------------------------------------------------
  5. -
  6. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)
  7. |
  8. --------------------------------------------------------------------------------
  9. -
  10. | 0 | SELECT STATEMENT | | 286K| 10M| 3433 (1)
  11. |
  12. | 1 | TABLE ACCESS BY INDEX ROWID| LOGIN_LOG | 286K| 10M| 3433 (1)
  13. |
  14. | 2 | INDEX RANGE SCAN | LOGIN_LOG_PK | 114K| | 3 (0)
  15. |
  16. --------------------------------------------------------------------------------
  17. -

  19. Note
  20. -----
  21.    - 'PLAN_TABLE' is old version

  23. Statistics
  24. ----------------------------------------------------------
  25.          18 recursive calls
  26.          16 db block gets
  27.          19 consistent gets
  28.           4 physical reads
  29.       11856 redo size
  30.         541 bytes sent via SQL*Net to client
  31.         524 bytes received via SQL*Net from client
  32.           2 SQL*Net roundtrips to/from client
  33.           0 sorts (memory)
  34.           0 sorts (disk)
  35.           1 rows processed

  37. SQL> select * from table(dbms_xplan.display_cursor('2pqkr80bqn6wb','',''));
  38. PLAN_TABLE_OUTPUT
  39. --------------------------------------------------------------------------------
  40. SQL_ID 2pqkr80bqn6wb, child number 0
  41. -------------------------------------
  42. select /*www1*/ /*+ full(LOGIN_LOG) */IP_ADDRESS from LOGIN_LOG where
  43. session_id=1273523
  44. Plan hash value: 3779830307
  45. -------------------------------------------------------------------------------
  46. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  47. -------------------------------------------------------------------------------
  48. | 0 | SELECT STATEMENT | | | | 95461 (100)| |
  49. PLAN_TABLE_OUTPUT
  50. --------------------------------------------------------------------------------
  51. |* 1 | TABLE ACCESS FULL| LOGIN_LOG | 286K| 10M| 95461 (1)| 00:19:06 |
  52. -------------------------------------------------------------------------------
  53. Predicate Information (identified by operation id):
  54. ---------------------------------------------------
  55.    1 - filter("SESSION_ID"=1273523)
  56. SQL_ID 2pqkr80bqn6wb, child number 2
  57. -------------------------------------
  58. select /*www1*/ /*+ full(LOGIN_LOG) */IP_ADDRESS from LOGIN_LOG where
  59. PLAN_TABLE_OUTPUT
  60. --------------------------------------------------------------------------------
  61. session_id=1273523
  62. Plan hash value: 3865870674
  63. --------------------------------------------------------------------------------
  64. ------------
  65. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)
  66. | Time |
  67. --------------------------------------------------------------------------------
  68. PLAN_TABLE_OUTPUT
  69. --------------------------------------------------------------------------------
  70. ------------
  71. | 0 | SELECT STATEMENT | | | | 3433 (100)
  72. | |
  73. | 1 | TABLE ACCESS BY INDEX ROWID| LOGIN_LOG | 286K| 10M| 3433 (1)
  74. | 00:00:42 |
  75. |* 2 | INDEX RANGE SCAN | LOGIN_LOG_PK | 114K| | 3 (0)
  76. | 00:00:01 |

  78. PLAN_TABLE_OUTPUT
  79. --------------------------------------------------------------------------------
  80. --------------------------------------------------------------------------------
  81. ------------

  83. Predicate Information (identified by operation id):
  84. ---------------------------------------------------
  85.    2 - access("SESSION_ID"=1273523)
  86. Note
  87. -----
  88. PLAN_TABLE_OUTPUT
  89. --------------------------------------------------------------------------------
  90.    - SQL plan baseline SQL_PLAN_d7sbcd0wrkvt47b166b46 used for this statement

  92. 43 rows selected.

向AI问一下细节
推荐阅读：
  1. Oracle固定SQL的执行计划(二)---SPM
  2. ORACLE 11G SPM(SQL PLAN manager)浅析

免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。

baseline sql 使用

猜你喜欢

最新资讯
相关推荐

相关标签

timeline pipeline offline informix-online command-line baselines inline linearlayout lines sparklines display:inline subline linear-gradient #line nextline() line-height inline-block beeline getline outline-offset
AI
助
手
产品服务
地区划分
专题活动
帮助支持
关于我们
售后咨询
7*24小时在线电话：400-100-2938
7*24小时在线 QQ：800811969
关注亿速云

亿速云公众号

手机网站二维码

Copyright © Yisu Cloud Ltd. All Rights Reserved. 2018 版权所有

广州亿速云计算有限公司粤ICP备17096448号-1 粤公网安备 44010402001142号增值电信业务经营许可证编号：B1-20181529