久久久精品一区ed2k-女人被男人叉到高潮的视频-中文字幕乱码一区久久麻豆樱花-俄罗斯熟妇真实视频

使用sqlbaseline替換執(zhí)行計(jì)劃

1.分別執(zhí)行下列SQL

點(diǎn)擊(此處)折疊或打開

專業(yè)從事成都做網(wǎng)站、成都網(wǎng)站建設(shè)、成都外貿(mào)網(wǎng)站建設(shè),高端網(wǎng)站制作設(shè)計(jì),微信小程序定制開發(fā),網(wǎng)站推廣的成都做網(wǎng)站的公司。優(yōu)秀技術(shù)團(tuán)隊(duì)竭力真誠服務(wù)，采用H5技術(shù)+CSS3前端渲染技術(shù)，響應(yīng)式網(wǎng)站建設(shè)，讓網(wǎng)站在手機(jī)、平板、PC、微信下都能呈現(xiàn)。建站過程建立專項(xiàng)小組，與您實(shí)時(shí)在線互動(dòng)，隨時(shí)提供解決方案，暢聊想法和感受。

  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

點(diǎn)擊(此處)折疊或打開

  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.查看執(zhí)行計(jì)劃

點(diǎn)擊(此處)折疊或打開

  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創(chuàng)建baseline

點(diǎn)擊(此處)折疊或打開

  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.將符合我們預(yù)期的SQL2的執(zhí)行計(jì)劃的載入到第一次生成的sql baseline中

點(diǎn)擊(此處)折疊或打開

  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出現(xiàn)2個(gè)執(zhí)行計(jì)劃
  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執(zhí)行計(jì)劃的狀態(tài)為fixed

點(diǎn)擊(此處)折疊或打開

  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執(zhí)行計(jì)劃被改變

點(diǎn)擊(此處)折疊或打開

  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替換執(zhí)行計(jì)劃
文章位置：http://sd-ha.com/article26/iecccg.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站制作、面包屑導(dǎo)航、企業(yè)建站、標(biāo)簽優(yōu)化、網(wǎng)站策劃、自適應(yīng)網(wǎng)站

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)

網(wǎng)站托管運(yùn)營