使用sql baseline替换执行计划

发布时间：2020-08-09 04:09:13 作者：wwjfeng
来源：ITPUB博客阅读：160

1.分别执行下列SQL

点击(此处)折叠或打开

SQL1:select /*www1*/ /*+ full(LOGIN_LOG) */IP_ADDRESS from LOGIN_LOG where session_id=1273523;
SQL2:select /*www2*/ /*+ index(LOGIN_LOG LOGIN_LOG_PK) */IP_ADDRESS from LOGIN_LOG where session_id=1273523;

2.查看SQL_ID和PLAN_HASH_VALUE

点击(此处)折叠或打开

select * from v$sql where sql_text like '%www1%'
select * from v$sql where sql_text like '%www2%'
SQL1: 2pqkr80bqn6wb 3779830307
SQL2: 7510s3wam524g 3865870674

3.查看执行计划

点击(此处)折叠或打开

SQL1
SQL> select * from table(dbms_xplan.display_cursor('2pqkr80bqn6wb','',''));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 2pqkr80bqn6wb, child number 0
-------------------------------------
select /*www1*/ /*+ full(LOGIN_LOG) */IP_ADDRESS from LOGIN_LOG where
session_id=1273523
Plan hash value: 3779830307
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 95461 (100)| |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 1 | TABLE ACCESS FULL| LOGIN_LOG | 286K| 10M| 95461 (1)| 00:19:06 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SESSION_ID"=1273523)
19 rows selected.
SQL2
SQL> select * from table(dbms_xplan.display_cursor('7510s3wam524g','',''));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 7510s3wam524g, child number 0
-------------------------------------
select /*www2*/ /*+ index(LOGIN_LOG LOGIN_LOG_PK) */IP_ADDRESS from
LOGIN_LOG where session_id=1273523
Plan hash value: 3865870674
--------------------------------------------------------------------------------
------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| Time |
--------------------------------------------------------------------------------
------------
| 0 | SELECT STATEMENT | | | | 3433 (100)
| |
| 1 | TABLE ACCESS BY INDEX ROWID| LOGIN_LOG | 286K| 10M| 3433 (1)
| 00:00:42 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 2 | INDEX RANGE SCAN | LOGIN_LOG_PK | 114K| | 3 (0)
| 00:00:01 |
--------------------------------------------------------------------------------
------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SESSION_ID"=1273523)
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
20 rows selected.

4.从库缓存中为SQL1创建baseline

点击(此处)折叠或打开

DECLARE
l_plans_loaded PLS_INTEGER;
BEGIN
l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(sql_id => '2pqkr80bqn6wb',plan_hash_value=>'3779830307');
END;
/
select sql_handle,plan_name,dbms_lob.substr(sql_text,60,1) sql_text,ACCEPTED from dba_sql_plan_baselines;
SQL_d3e16c6839796f24 SQL_PLAN_d7sbcd0wrkvt433a13db8 select /*www1*/ /*+ full(LOGIN_LOG) */IP_ADDRESS from LOGIN_ YES

5.将符合我们预期的SQL2的执行计划的载入到第一次生成的sql baseline中

点击(此处)折叠或打开

DECLARE
k1 pls_integer;
begin
k1 := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
sql_id=>'7510s3wam524g',
plan_hash_value=>3865870674,sql_handle=>'SQL_d3e16c6839796f24'
);
end;
/
基线SQL_d3e16c6839796f24出现2个执行计划
select sql_handle,plan_name,dbms_lob.substr(sql_text,60,1) sql_text,ACCEPTED from dba_sql_plan_baselines;
SQL_d3e16c6839796f24 SQL_PLAN_d7sbcd0wrkvt433a13db8 select /*www1*/ /*+ full(LOGIN_LOG) */IP_ADDRESS from LOGIN_ YES
SQL_d3e16c6839796f24 SQL_PLAN_d7sbcd0wrkvt47b166b46 select /*www1*/ /*+ full(LOGIN_LOG) */IP_ADDRESS from LOGIN_ YES

6.修改原先SQL2执行计划的状态为fixed

点击(此处)折叠或打开

SET SERVEROUTPUT ON
DECLARE
v_text PLS_INTEGER;
BEGIN
v_text := DBMS_SPM.alter_sql_plan_baseline(sql_handle => 'SQL_d3e16c6839796f24',plan_name => 'SQL_PLAN_d7sbcd0wrkvt47b166b46',
attribute_name => 'fixed',attribute_value => 'YES');
DBMS_OUTPUT.put_line('Plans Altered: ' || v_text );
END;
/
select sql_handle,plan_name,dbms_lob.substr(sql_text,60,1) sql_text,ACCEPTED,FIXED from dba_sql_plan_baselines;
SQL_d3e16c6839796f24 SQL_PLAN_d7sbcd0wrkvt433a13db8 select /*www1*/ /*+ full(LOGIN_LOG) */IP_ADDRESS from LOGIN_ YES NO
SQL_d3e16c6839796f24 SQL_PLAN_d7sbcd0wrkvt47b166b46 select /*www1*/ /*+ full(LOGIN_LOG) */IP_ADDRESS from LOGIN_ YES YES

7.原SQL1执行计划被改变

点击(此处)折叠或打开

SQL> select /*www1*/ /*+ full(LOGIN_LOG) */IP_ADDRESS from LOGIN_LOG where session_id=1273523;
Execution Plan
----------------------------------------------------------
--------------------------------------------------------------------------------
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
|
--------------------------------------------------------------------------------
-
| 0 | SELECT STATEMENT | | 286K| 10M| 3433 (1)
|
| 1 | TABLE ACCESS BY INDEX ROWID| LOGIN_LOG | 286K| 10M| 3433 (1)
|
| 2 | INDEX RANGE SCAN | LOGIN_LOG_PK | 114K| | 3 (0)
|
--------------------------------------------------------------------------------
-
Note
-----
- 'PLAN_TABLE' is old version
Statistics
----------------------------------------------------------
18 recursive calls
16 db block gets
19 consistent gets
4 physical reads
11856 redo size
541 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select * from table(dbms_xplan.display_cursor('2pqkr80bqn6wb','',''));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 2pqkr80bqn6wb, child number 0
-------------------------------------
select /*www1*/ /*+ full(LOGIN_LOG) */IP_ADDRESS from LOGIN_LOG where
session_id=1273523
Plan hash value: 3779830307
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 95461 (100)| |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 1 | TABLE ACCESS FULL| LOGIN_LOG | 286K| 10M| 95461 (1)| 00:19:06 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SESSION_ID"=1273523)
SQL_ID 2pqkr80bqn6wb, child number 2
-------------------------------------
select /*www1*/ /*+ full(LOGIN_LOG) */IP_ADDRESS from LOGIN_LOG where
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
session_id=1273523
Plan hash value: 3865870674
--------------------------------------------------------------------------------
------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
| Time |
--------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
------------
| 0 | SELECT STATEMENT | | | | 3433 (100)
| |
| 1 | TABLE ACCESS BY INDEX ROWID| LOGIN_LOG | 286K| 10M| 3433 (1)
| 00:00:42 |
|* 2 | INDEX RANGE SCAN | LOGIN_LOG_PK | 114K| | 3 (0)
| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SESSION_ID"=1273523)
Note
-----
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
- SQL plan baseline SQL_PLAN_d7sbcd0wrkvt47b166b46 used for this statement
43 rows selected.

使用sql baseline替换执行计划

相关阅读