咨询热线 ：
028-86922220
×
01
关于我们

公司简介

发展历程

02
服务项目

高端网站建设

微信开发

APP开发

网络营销服务

电商网站定制

生物医药网站建设

外贸网站建设

教育培训网站建设

03
小程序开发

小程序开发

功能应用

客户案例

04
案例展示

网站建设案例

小程序案例

电商平台案例

APP案例

系统平台案例

05
建站动态

网站建设

网站设计

网站营销

小程序

06
联系我们

公司地址

人才招聘

地址：成都市太升南路288号锦天国际A幢1002号

电话：028-86922220

028-86922220

大橙子网站建设，新征程启航

为企业提供网站建设、域名注册、服务器等服务

使用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替换执行计划
URL链接：http://dzwzjz.com/article/iecccg.html

其他资讯

分享至：
关于我们
公司简介 发展历程
服务项目
高端网站建设 小程序开发 APP开发 网络营销
建站知识
行业新闻 建站学堂 常见问题
联系我们
公司地址 人才招聘
成都 达州
电话：028-86922220
地址：成都市太升南路288号锦天国际A幢1002号
电话：028-86922220
地址：达州市南岸区弹子石腾龙大道58号2栋21-6
友情链接： 成都模版网站建设   凯亚自动化控制   微信小程序   手机网站制作   发光字制作   成都网站建设公司   网站建设   校园网站建设方案   郫县门窗定制   成都橡塑保温管   
版权所有：青羊区大橙子信息咨询工作室 蜀ICP备2022028542号-14
在线咨询
服务热线
服务热线：028-86922220
TOP