中文字幕av专区_日韩电影在线播放_精品国产精品久久一区免费式_av在线免费观看网站

溫馨提示×

溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊×
其他方式登錄
點擊 登錄注冊 即表示同意《億速云用戶服務條款》

使用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問一下細節

免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。

AI
助
手
宁波市| 美姑县| 武山县| 朔州市| 昌都县| 澜沧| 嘉善县| 黔江区| 富平县| 赞皇县| 阳高县| 平山县| 巩留县| 曲阜市| 满城县| 和平县| 苏州市| 南华县| 松原市| 醴陵市| 威信县| 花莲市| 哈尔滨市| 灵山县| 阿坝| 天津市| 乌审旗| 额尔古纳市| 德安县| 临颍县| 诸暨市| 岱山县| 微博| 琼结县| 内江市| 高青县| 高安市| 河北区| 仁怀市| 沂水县| 迭部县|