Replace the execution plan with sql baseline
1. Execute the following SQL respectively
Click (here) to collapse or open
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
two。 View SQL_ID and PLAN_HASH_VALUE
Click (here) to collapse or open
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. View the execution plan
Click (here) to collapse or open
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 | | |
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 |
| | |
| | 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. Create a baseline for SQL1 from the repository cache
Click (here) to collapse or open
DECLARE
L_plans_loaded PLS_INTEGER
BEGIN
L_plans_loaded: = DBMS_SPM.load_plans_from_cursor_cache (sql_id = > '2pqkr80bqn6wbmovie Magazine planetary hashworthy 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. Load the execution plan of the SQL2 that meets our expectations into the first generated sql baseline
Click (here) to collapse or open
DECLARE
K1 pls_integer
Begin
K1: = DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
Sql_id= > '7510s3wam524g'
Plan_hash_value= > 3865870674 Magi sqlforth handle = > 'SQL_d3e16c6839796f24'
);
End
/
There are 2 execution plans for baseline SQL_d3e16c6839796f24
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. Change the status of the previous SQL2 execution plan to fixed
Click (here) to collapse or open
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. The original SQL1 execution plan has been changed
Click (here) to collapse or open
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 | | |
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 |
| | |
| | 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.
