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

Automatic scheduled capture:

SQL > sho parameter OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES

NAME TYPE VALUE

-

Optimizer_capture_sql_plan_baselines boolean FALSE

Query session sid:

SQL > select userenv ('SID') from dual

USERENV ('SID')

-

thirty-nine

Create a test table:

SQL > sho user

USER is "SCOTT"

SQL > create table ming as select * from emp

Table created.

Query:

SQL > set autot traceonly

SQL > select * from ming where empno=7934

Execution Plan

Plan hash value: 406648510

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

| | 0 | SELECT STATEMENT | | 1 | 87 | 3 (0) | 00:00:01 |

| | * 1 | TABLE ACCESS FULL | MING | 1 | 87 | 3 (0) | 00:00:01 |

Predicate Information (identified by operation id):

1-filter ("EMPNO" = 7934)

Note

-

-dynamic sampling used for this statement (level=2)

Statistics

5 recursive calls

0 db block gets

7 consistent gets

0 physical reads

0 redo size

1022 bytes sent via SQL*Net to client

519 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

Another session query sql_id:

SQL > select PREV_SQL_ID,SID FROM v$session where sid=39

PREV_SQL_ID SID

--

4dmybh8upytx7 39

Manually capture the execution plan from cursor cache using SQL_ID:

SET SERVEROUTPUT ON

DECLARE

L_plans_loaded PLS_INTEGER

BEGIN

L_plans_loaded: = DBMS_SPM.load_plans_from_cursor_cache (sql_id = >'& sql_id')

DBMS_OUTPUT.put_line ('Plans Loaded:' | | l_plans_loaded)

END

/

Old 4: l_plans_loaded: = DBMS_SPM.load_plans_from_cursor_cache (sql_id = >'& sql_id')

New 4: l_plans_loaded: = DBMS_SPM.load_plans_from_cursor_cache (sql_id = > '4dmybh8upytx7')

Plans Loaded: 1

PL/SQL procedure successfully completed.

Use the DBA_SQL_PLAN_BASELINES view to view SPM information:

Col sql_handle for a35

Col plan_name for a35

Set lin 300

SELECT SQL_HANDLE,plan_name,origin,enabled,accepted,fixed

FROM dba_sql_plan_baselines

WHERE sql_text LIKE 'select * from ming where empno=79%'

AND sql_text NOT LIKE'%dba_sql_plan_baselines%'

SQL_HANDLE PLAN_NAME ORIGIN ENA ACC FIX

SYS_SQL_cbb79f0d76388c93 SQL_PLAN_crdwz1pv3j34m7c0756fd MANUAL-LOAD YES YES NO

Query again to see if sql plan baseline is being used:

SQL > set autotrace on

SQL > select * from ming where empno=7934

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--

7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10

Execution Plan

Plan hash value: 406648510

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

| | 0 | SELECT STATEMENT | | 1 | 87 | 3 (0) | 00:00:01 |

| | * 1 | TABLE ACCESS FULL | MING | 1 | 87 | 3 (0) | 00:00:01 |

Predicate Information (identified by operation id):

1-filter ("EMPNO" = 7934)

Note

-

-dynamic sampling used for this statement (level=2)

-SQL plan baseline "SQL_PLAN_crdwz1pv3j34m7c0756fd" used for this statement-baseline is used

Statistics

18 recursive calls

13 db block gets

15 consistent gets

0 physical reads

4828 redo size

1022 bytes sent via SQL*Net to client

519 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

Create an index:

SQL > create index idx_ming_empno on ming (empno)

Index created.

At this point, the optimal execution plan is obviously indexed, but because of the existence of sql plan baseline, the optimal execution plan will be discarded directly. Check it out, but you're still doing a full table scan.

-SQL plan baseline "SQL_PLAN_crdwz1pv3j34m7c0756fd" used for this statement

Then I add the index-walking execution plan to the baseline, and it is estimated that the optimizer will select the index at this time, and then I mark the full-table scan execution plan as fixed, and then continue to run the full-table scan, which is verified below:

Query the baseline at this time:

Col sql_handle for a35

Col plan_name for a35

Set lin 300

SELECT SQL_HANDLE,plan_name,origin,enabled,accepted,fixed

FROM dba_sql_plan_baselines

WHERE sql_text LIKE 'select * from ming where empno=79%'

AND sql_text NOT LIKE'%dba_sql_plan_baselines%'

SQL_HANDLE PLAN_NAME ORIGIN ENA ACC FIX

The SYS_SQL_cbb79f0d76388c93 SQL_PLAN_crdwz1pv3j34m7c0756fd MANUAL-LOAD YES YES NO-Accept column is yes

The SYS_SQL_cbb79f0d76388c93 SQL_PLAN_crdwz1pv3j34md2cc3f2f AUTO-CAPTURE YES NO NO-Accept column is no

To index the execution plan evolve,evolve is to change the execution plan to accept.

SQL > SELECT DBMS_SPM.evolve_sql_plan_baseline (sql_handle = >'& sql_handle') FROM dual

Enter value for sql_handle: SYS_SQL_cbb79f0d76388c93

Old 1: SELECT DBMS_SPM.evolve_sql_plan_baseline (sql_handle = >'& sql_handle') FROM dual

New 1: SELECT DBMS_SPM.evolve_sql_plan_baseline (sql_handle = > 'SYS_SQL_cbb79f0d76388c93') FROM dual

DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE (SQL_HANDLE= > 'SYS_SQL_CBB79F0D76388C93')

Evolve SQL Plan Baseline Report

Inputs:

-

SQL_HANDLE = SYS_SQL_cbb79f0d76388c93

PLAN_NAME =

TIME_LIMIT = DBMS_SPM.AUTO_LIMIT

VERIFY = YES

COMMIT = YES

Report Summary

There were no SQL plan baselines that required processing.

Query again:

SQL > col sql_handle for A35

SQL > col plan_name for A35

SQL > set lin 300

SQL > SELECT SQL_HANDLE,plan_name,origin,enabled,accepted,fixed

2 FROM dba_sql_plan_baselines

3 WHERE sql_text LIKE 'select * from ming where empno=79%'

4 AND sql_text NOT LIKE'%dba_sql_plan_baselines%'

SQL_HANDLE PLAN_NAME ORIGIN ENA ACC FIX

SYS_SQL_cbb79f0d76388c93 SQL_PLAN_crdwz1pv3j34m7c0756fd MANUAL-LOAD YES YES NO

SYS_SQL_cbb79f0d76388c93 SQL_PLAN_crdwz1pv3j34md2cc3f2f AUTO-CAPTURE YES YES NO

Now accept is all yes.

It should all be indexed by now:

SQL > set autotrace on

SQL > select * from ming where empno=7934

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--

7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10

Execution Plan

Plan hash value: 4239086873

-

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

-

| | 0 | SELECT STATEMENT | | 1 | 87 | 2 (0) | 00:00:01 |

| | 1 | TABLE ACCESS BY INDEX ROWID | MING | 1 | 87 | 2 (0) | 00:00:01 |

| | * 2 | INDEX RANGE SCAN | IDX_MING_EMPNO | 1 | | 1 (0) | 00:00:01 |

-

Predicate Information (identified by operation id):

2-access ("EMPNO" = 7934)

Note

-

-dynamic sampling used for this statement (level=2)

-SQL plan baseline "SQL_PLAN_crdwz1pv3j34md2cc3f2f" used for this statement-- now the baseline of the index.

Statistics

19 recursive calls

13 db block gets

17 consistent gets

0 physical reads

4876 redo size

1026 bytes sent via SQL*Net to client

519 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

Next, change the full table scan to fixed.

Query the execution plan in baseline:

SQL > select * from table (dbms_xplan.display_sql_plan_baseline (sql_handle = >'& sql_handle', format = > 'basic'))

Enter value for sql_handle: SYS_SQL_cbb79f0d76388c93

Old 1: select * from table (dbms_xplan.display_sql_plan_baseline (sql_handle = >'& sql_handle', format = > 'basic'))

New 1: select * from table (dbms_xplan.display_sql_plan_baseline (sql_handle = > 'SYS_SQL_cbb79f0d76388c93', format = >' basic'))

PLAN_TABLE_OUTPUT

-

SQL handle: SYS_SQL_cbb79f0d76388c93

SQL text: select * from ming where empno=7934

Plan name: SQL_PLAN_crdwz1pv3j34m7c0756fd Plan id: 2080855805

Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD

Plan hash value: 406648510

-

| | Id | Operation | Name | |

-

| | 0 | SELECT STATEMENT |

| | 1 | TABLE ACCESS FULL | MING |

-

Plan name: SQL_PLAN_crdwz1pv3j34md2cc3f2f Plan id: 3536600879

Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE

Plan hash value: 4239086873

| | Id | Operation | Name | |

| | 0 | SELECT STATEMENT |

| | 1 | TABLE ACCESS BY INDEX ROWID | MING |

| | 2 | INDEX RANGE SCAN | IDX_MING_EMPNO |

34 rows selected.

There are two execution plans in baseline.

Fixed the execution plan of the full table:

SET SERVEROUTPUT ON

DECLARE

L_plans_altered PLS_INTEGER

BEGIN

L_plans_altered: = DBMS_SPM.alter_sql_plan_baseline (

Sql_handle = >'& sql_handle'

Plan_name = >'& plan_name'

Attribute_name = > 'fixed'

Attribute_value = > 'YES')

DBMS_OUTPUT.put_line ('Plans Altered:' | | l_plans_altered)

END

/

Enter value for sql_handle: SYS_SQL_cbb79f0d76388c93

Old 5: sql_handle = >'& sql_handle'

New 5: sql_handle = > 'SYS_SQL_cbb79f0d76388c93'

Enter value for plan_name: SQL_PLAN_crdwz1pv3j34m7c0756fd

Old 6: plan_name = >'& plan_name'

New 6: plan_name = > 'SQL_PLAN_crdwz1pv3j34m7c0756fd'

Plans Altered: 1

PL/SQL procedure successfully completed.

The following verifies the execution plan:

SQL > set autot traceonly

SQL > select * from ming where empno=7934

Execution Plan

Plan hash value: 406648510

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

| | 0 | SELECT STATEMENT | | 3 | 261 | 3 (0) | 00:00:01 |

| | * 1 | TABLE ACCESS FULL | MING | 3 | 261 | 3 (0) | 00:00:01 |

Predicate Information (identified by operation id):

1-filter ("EMPNO" = 7934)

Note

-

-SQL plan baseline "SQL_PLAN_crdwz1pv3j34m7c0756fd" used for this statement

Statistics

13 recursive calls

0 db block gets

9 consistent gets

0 physical reads

0 redo size

1022 bytes sent via SQL*Net to client

519 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

It can be found that it has become a full table scan again.

There can be multiple execution plans for fixed, and the optimizer chooses among the fixed execution plans and ignores other execution plans, even if those execution plans are best-cost plan.

Delete the baseline of a SQL

SET SERVEROUTPUT ON

DECLARE

V_plans_dropped PLS_INTEGER

BEGIN

V_plans_dropped: = DBMS_SPM.drop_sql_plan_baseline (

Sql_handle = > 'SYS_SQL_cbb79f0d76388c93'

Plan_name = > NULL)

DBMS_OUTPUT.put_line (v_plans_dropped)

END

/

SQL > select * from ming where empno=7934

Execution Plan

Plan hash value: 4239086873

-

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

-

| | 0 | SELECT STATEMENT | | 1 | 87 | 2 (0) | 00:00:01 |

| | 1 | TABLE ACCESS BY INDEX ROWID | MING | 1 | 87 | 2 (0) | 00:00:01 |

| | * 2 | INDEX RANGE SCAN | IDX_MING_EMPNO | 1 | | 1 (0) | 00:00:01 |

-

Predicate Information (identified by operation id):

2-access ("EMPNO" = 7934)

Note

-

-dynamic sampling used for this statement (level=2)-- there is no trace of baseline, according to the index used by cost.

Statistics

11 recursive calls

0 db block gets

9 consistent gets

0 physical reads

0 redo size

1026 bytes sent via SQL*Net to client

519 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

There are no records of those two in dba_sql_plan_baselines.

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.