使用sql baseline替换执行计划

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)
    
21. 
    
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 |
    
44. 
    
45. PLAN_TABLE_OUTPUT
    
46. --------------------------------------------------------------------------------
    
47. |* 2 | INDEX RANGE SCAN | LOGIN_LOG_PK | 114K| | 3 (0)
    
48. | 00:00:01 |
    
49. --------------------------------------------------------------------------------
    
50. ------------
    
51. 
    
52. Predicate Information (identified by operation id):
    
53. ---------------------------------------------------
    
54. 2 - access("SESSION_ID"=1273523)
    
55. PLAN_TABLE_OUTPUT
    
56. --------------------------------------------------------------------------------
    
57. 
    
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. -
    
18. 
    
19. Note
    
20. -----
    
21. - 'PLAN_TABLE' is old version
    
22. 
    
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
    
36. 
    
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 |
    
77. 
    
78. PLAN_TABLE_OUTPUT
    
79. --------------------------------------------------------------------------------
    
80. --------------------------------------------------------------------------------
    
81. ------------
    
82. 
    
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
    
91. 
    
92. 43 rows selected.