Shulou(Shulou.com)06/01 Report--

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.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.