使用sqlbaseline替换执行计划

1.分别执行下列SQL

点击(此处)折叠或打开

专业从事成都做网站、成都网站建设、成都外贸网站建设,高端网站制作设计,微信小程序定制开发,网站推广的成都做网站的公司。优秀技术团队竭力真诚服务,采用H5技术+CSS3前端渲染技术,响应式网站建设,让网站在手机、平板、PC、微信下都能呈现。建站过程建立专项小组,与您实时在线互动,随时提供解决方案,畅聊想法和感受。

  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)

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

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

  49. Predicate Information (identified by operation id):
  50. ---------------------------------------------------
  51.    2 - access("SESSION_ID"=1273523)
  52. PLAN_TABLE_OUTPUT
  53. --------------------------------------------------------------------------------

  54. 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. -

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

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

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

  74. PLAN_TABLE_OUTPUT
  75. --------------------------------------------------------------------------------
  76. --------------------------------------------------------------------------------
  77. ------------

  78. Predicate Information (identified by operation id):
  79. ---------------------------------------------------
  80.    2 - access("SESSION_ID"=1273523)
  81. Note
  82. -----
  83. PLAN_TABLE_OUTPUT
  84. --------------------------------------------------------------------------------
  85.    - SQL plan baseline SQL_PLAN_d7sbcd0wrkvt47b166b46 used for this statement

  86. 43 rows selected.


网页标题:使用sqlbaseline替换执行计划
文章地址:http://scyanting.com/article/iecccg.html