使用sqlbaseline替换执行计划
1.分别执行下列SQL
点击(此处)折叠或打开
专业从事成都做网站、成都网站建设、成都外贸网站建设,高端网站制作设计,微信小程序定制开发,网站推广的成都做网站的公司。优秀技术团队竭力真诚服务,采用H5技术+CSS3前端渲染技术,响应式网站建设,让网站在手机、平板、PC、微信下都能呈现。建站过程建立专项小组,与您实时在线互动,随时提供解决方案,畅聊想法和感受。
-
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.
分享名称:使用sqlbaseline替换执行计划
文章源于:http://myzitong.com/article/iecccg.html