189 8069 5689

使用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)

  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.


分享题目：使用sqlbaseline替换执行计划
网站网址：http://cdxtjz.cn/article/iecccg.html

其他资讯