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

The SPM launched by 11g is an active and stable means of implementing the plan, ensuring that only the verified execution plan will be enabled. SPM not only takes the initiative to implement the plan steadily, but also retains the opportunity to continue to use the new more efficient execution plan.

When SPM is enabled, there is a corresponding SQL PLAN Baseline for each SQL, which is stored in the DBA_SQL_PLAN_BASELINES view.

The execution plan corresponding to the SQL PLAN Baseline in which the enable and accept columns of the view are both YES will be executed. If more than one is YES, then oracle will choose the one with the lowest cost as the execution plan.

There are two ways to generate SQL PLAN Baseline

1. Automatic capture

two。 Manual generation / batch import

Let's start with automatic capture. The parameter optimizer_capture_sql_plan_baselines is used to control whether automatic capture is enabled. The default is false. The parameter optimizer_use_sql_plan_baselines is used to control whether SPM is enabled, and the default is TRUE, which means that by default Oracle will enable SPM and use the existing SQL PLAN Baseline when generating the execution plan.

Create an auto-capture SQL PLAN Baseline and use it to stabilize the execution plan.

Default parameter

Sys@TEST:test > show parameter sql_planNAME TYPE VALUE-- optimizer_capture_sql_plan_baselines boolean FALSEoptimizer_use_sql_plan_baselines boolean TRUE

Disable SPM in the current session and turn on automatic capture.

Aaa@TEST:test > alter session set optimizer_capture_sql_plan_baselines=true;Session altered.aaa@TEST:test > alter session set optimizer_use_sql_plan_baselines=false;Session altered.

Create test table T2 and create an index

Aaa@TEST:test > create table T2 as select * from dba_objects;Table created.aaa@TEST:test > create index idx_t2 on T2 (object_id); Index created.

Collect statistics for T2

Aaa@TEST:test > exec dbms_stats.gather_table_stats (ownname = > 'AAA',tabname = >' T2 cascade = > 100prompt cascade = > true); PL/SQL procedure successfully completed.

Execute SQL

Aaa@TEST:test > select object_id,object_name from T2 where object_id between 103 and 108; OBJECT_ID OBJECT_NAME--103 MIGRATE$ 104 DEPENDENCY$ 105 ACCESS$ 106 I_DEPENDENCY1 107 I_DEPENDENCY2 108 I_ACCESS16 rows selected.

The execution plan is as follows

- -SQL_ID 8vtdn0kgytfxr Child number 0--select object_id Object_name from T2 where object_id between 103 and 108Plan hash value: 2008370210-| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |-| 0 | SELECT STATEMENT | | | 3 (100) | 1 | TABLE ACCESS BY INDEX ROWID | T2 | 6 | 180 | 3 (0) | 00:00:01 | * 2 | INDEX RANGE SCAN | IDX_T2 | 6 | | 2 (0) | 00:00:01 |

Because the SQL has been executed only once, Oracle does not automatically capture its SQL PLAN Baseline and verify:

Aaa@TEST:test > select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select object_id%';no rows selected

Once again, the execution plan does not change, because SQL has been executed repeatedly and Oracle will automatically capture its SQL PLAN Baseline

SQL > select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select object_id%' SQL_HANDLE PLAN_NAME ORIGIN ENABLED ACCEPTED SQL_TEXT -SYS_SQL_ac526b1e4be74880 SQL_PLAN_asnmb3t5yfk4024c6dbb6 AUTO-CAPTURE YES YES select object_id Object_name from t2 where object_id between 103 and 108

Here, the clustering factor of the modified index IDX_T2 is changed to 24 million, and the execution plan of the target SQL is changed to the full table.

SQL > exec dbms_stats.set_index_stats (ownname= > 'AAA',indname= >' IDX_T2',clstfct= > 2400000 recordings noisy invalidate = > false); PL/SQL procedure successfully completed

Re-execute SQL, the execution plan is as follows, for the full table

Select object_id Object_name from T2 where object_id between 103 and 108Plan hash value: 1513984157 Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | -| 0 | SELECT STATEMENT | 290 (100) | * 1 | TABLE ACCESS FULL | T2 | 6 | 180 | 290 ( 1) | 00:00:04 |

Because the target SQL has been executed repeatedly and another execution plan is generated, Oracle automatically captures and creates the SQL PLAN Baseline corresponding to this new execution plan, query view

SQL > select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select object_id%' SQL_HANDLE PLAN_NAME ORIGIN ENABLED ACCEPTED SQL_TEXT -SYS_SQL_ac526b1e4be74880 SQL_PLAN_asnmb3t5yfk4024c6dbb6 AUTO-CAPTURE YES YES select object_id Object_name from t2 where object_id between 103 and 108SYS_SQL_ac526b1e4be74880 SQL_PLAN_asnmb3t5yfk40b860bcf2 AUTO-CAPTURE YES NO select object_id,object_name from t2 where object_id between 103 and 108

Then turn off the current session automatic capture SQL PLAN Baseline, and enable SPM, that is, restore the 11g default

Aaa@TEST:test > alter session set optimizer_capture_sql_plan_baselines=false;Session altered.aaa@TEST:test > alter session set optimizer_use_sql_plan_baselines=true;Session altered.

Now the clustering factor of index IDX_T2 is still 24 million.

Aaa@TEST:test > select index_name,clustering_factor from user_indexes;INDEX_NAME CLUSTERING_FACTOR---IDX_T2 24000000

Execute the target SQL again, its execution plan

PLAN_TABLE_OUTPUT- -SQL_ID 8vtdn0kgytfxr Child number 2--select object_id Object_name from T2 where object_id between 103 and 108Plan hash value: 2008370210-| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |-| 0 | SELECT STATEMENT | | | 2069 | | 1 | TABLE ACCESS BY INDEX ROWID | T2 | 6 | 2069 (0) | 00:00:25 | * 2 | INDEX RANGE SCAN | IDX_T2 | 6 | | 2 (0) | 00:00:01 |-- | -Note--SQL plan baseline SQL_PLAN_asnmb3t5yfk4024c6dbb6 used for this statement

As you can see above, the execution plan of the target SQL has now changed from a full table scan of the table to an index range scan. And there is a "SQL plan baseline SQL_PLAN_asnmb3t5yfk4024c6dbb6 used for this statement" under the NOTE, which indicates that when SPM is enabled, even if the target SQL generates a new execution plan, Oracle will only use the execution plan corresponding to the SQL PLAN Baseline where both enabled and accepted are YES.

If you want to enable a new execution plan, the operation varies from version to version.

11gR1, you only need to change the defined value of the SQL PLAN Baseline named SQL_PLAN_asnmb3t5yfk4024c6dbb6 used by the target SQL lock to NO.

SQL > var temp number;SQL > exec: temp:=dbms_spm.alter_sql_plan_baseline (sql_handle = > 'SYS_SQL_ac526b1e4be74880',plan_name = >' SQL_PLAN_asnmb3t5yfk4024c6dbb6',attribute_name = > 'accepted',attribute_value = >' NO')

In 11gR2, execute the above code or report an error

SQL > var temp number;SQL > exec: temp:=dbms_spm.alter_sql_plan_baseline (sql_handle = > 'SYS_SQL_ac526b1e4be74880',plan_name = >' SQL_PLAN_asnmb3t5yfk4024c6dbb6',attribute_name = > 'accepted',attribute_value = >' NO'); begin: temp:=dbms_spm.alter_sql_plan_baseline (sql_handle = > 'SYS_SQL_ac526b1e4be74880',plan_name = >' SQL_PLAN_asnmb3t5yfk4024c6dbb6',attribute_name = > 'accepted',attribute_value = >' NO'); end ORA-38136: the specified attribute name ACCEPTED is invalid ORA-06512: in "SYS.DBMS_SPM", line 2469ORA-06512: in line 1temp-

In 11gR2, use dbms_spm.evolve_sql_plan_baseline and dbms_spm.alter_sql_plan_baseline to enable a new execution plan.

First set the scheduled of the new execution plan to YES with dbms_spm.evolve_sql_plan_baseline

SQL > var temp varchar2 (4000); SQL > exec: temp:=dbms_spm.evolve_sql_plan_baseline (sql_handle = > 'SYS_SQL_ac526b1e4be74880',plan_name = >' SQL_PLAN_asnmb3t5yfk40b860bcf2',verify = > 'NO',commit = >' YES') PL/SQL procedure successfully completedtemp- Evolve SQL Plan Baseline Report- -Inputs:- SQL_HANDLE = SYS_SQL_ac526b1e4be74880 PLAN_NAME = SQL_PLAN_asnmb3t5yfk40b860bcf2 TIME_LIMIT = DBMS_SPM.AUTO_LIMIT VERIFY = NO COMMIT = YESPlan: SQL_PLAN_asnmb3t5yfk40b860bcf2-- It is already an accepted plan. -Report Summary----There were no SQL plan baselines that required processing.SQL > select sql_handle Plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select object_id%' SQL_HANDLE PLAN_NAME ORIGIN ENABLED ACCEPTED SQL_TEXT -SYS_SQL_ac526b1e4be74880 SQL_PLAN_asnmb3t5yfk4024c6dbb6 AUTO-CAPTURE YES YES select object_id Object_name from t2 where object_id between 103 and 108SYS_SQL_ac526b1e4be74880 SQL_PLAN_asnmb3t5yfk40b860bcf2 AUTO-CAPTURE YES YES select object_id,object_name from t2 where ob

Then use dbms_spm.alter_sql_plan_baseline to set the enabled of the SQL PLAN Baseline whose PLAN_NAME is SQL_PLAN_asnmb3t5yfk4024c6dbb6 to NO.

SQL > var temp number;SQL > exec: temp:=dbms_spm.alter_sql_plan_baseline (sql_handle = > 'SYS_SQL_ac526b1e4be74880',plan_name = >' SQL_PLAN_asnmb3t5yfk4024c6dbb6',attribute_name = > 'ENABLED',attribute_value = >' NO'); PL/SQL procedure successfully completedtemp-1SQL > select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select object_id%' SQL_HANDLE PLAN_NAME ORIGIN ENABLED ACCEPTED SQL_TEXT -SYS_SQL_ac526b1e4be74880 SQL_PLAN_asnmb3t5yfk4024c6dbb6 AUTO-CAPTURE NO YES select object_id Object_name from t2 where object_id between 103 and 108SYS_SQL_ac526b1e4be74880 SQL_PLAN_asnmb3t5yfk40b860bcf2 AUTO-CAPTURE YES YES select object_id,object_name from t2 where object_id between 103 and 108

Execute the target SQL again

PLAN_TABLE_OUTPUT- -SQL_ID 8vtdn0kgytfxr Child number 0--select object_id Object_name from T2 where object_id between 103 and 108Plan hash value: 1513984157 Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | -| 0 | SELECT STATEMENT | 290 (100) | * 1 | TABLE ACCESS FULL | T2 | 6 | 180 | 290 ( 1) | 00:00:04 |-Note--SQL plan baseline SQL_PLAN_asnmb3t5yfk40b860bcf2 used for this statement

Using the new execution plan, note that the SQL_PLAN_asnmb3t5yfk40b860bcf2 in NOTE, that is, enabled and accepted in SQL PLAN Baseline, are the execution plan names corresponding to the SQL PLAN Baseline of YES.

From the test results, we can switch between multiple execution plans of the target SQL, so SPM can not only execute the plan steadily, but also retain the opportunity to continue to use the new execution plan.

Next, let's introduce the manual generation of SQL PLAN Baseline. Manual generation is very simple, the core is to call dbms_spm.load_plans_from_cursor_cache.

Use dbms_spm.load_plans_from_cursor_cache to manually generate the SQL PLAN Baseline corresponding to its initial execution plan for the target SQL.

PLAN_TABLE_OUTPUT- -SQL_ID 85htp4tya3uwm Child number 0--select / * + no_index (T2 idx_t2) * / object_name Object_id from T2 whereobject_id=4Plan hash value: 1513984157UV-| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | 290,100 | * 1 | TABLE ACCESS FULL | T2 | 1 | 30 | 290 (1) | 00:00:04 |

The SQL_ID of the above SQL is 85htp4tya3uwm, the journal planetary hashworthy value is 1513984157, and automatic capture SQL PLAN Baseline is not enabled now. The view dba_sql_plan_baselines should have no corresponding information.

Select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select object_name%' SQL_HANDLE PLAN_NAME ORIGIN ENABLED ACCEPTED SQL_TEXT

Use the SQL_ID and PLAN_HASH_VALUE of the target SQL to generate the corresponding SQL PLAN Baseline.

SQL > var temp number;SQL > exec: temp:=dbms_spm.load_plans_from_cursor_cache (sql_id= > '85htp4tya3uwmjournal planning hashworthy value= > 1513984157); PL/SQL procedure successfully completedtemp-1SQL > select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like' select / * + no_index (T2 idx_t2) * / object_name%' SQL_HANDLE PLAN_NAME ORIGIN ENABLED ACCEPTED SQL_TEXT -SYS_SQL_75b06ae056223f5f SQL_PLAN_7bc3aw1b24guzb860bcf2 MANUAL-LOAD YES YES select / * + no_index (T2 idx_t2) * / object_name Object_id from t2 where object_id=4

SQL PLAN Baseline with SQL_HANDLE of SYS_SQL_75b06ae056223f5f and PLAN_NAME of SQL_PLAN_7bc3aw1b24guzb860bcf2 has been generated.

Rewrite the original target SQL and add the forced walk index hint

PLAN_TABLE_OUTPUT- -SQL_ID 0argb4cn0sybz Child number 0--select / * + index (T2 idx_t2) * / object_name Object_id from T2 whereobject_id=4Plan hash value: 2008370210-| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |-| 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | 30 | 332 (0) | 00:00:04 | * 2 | INDEX RANGE SCAN | IDX_T2 | 1 | | 1 (0) | 00:00:01 | |

Go to the index after rewriting, SQL_ID=0argb4cn0sybz,Plan hash value: 2008370210.

Add the SQL_HANDLE of the original target SQL to generate a new SQL PLAN Baseline.

SQL > exec: temp:=dbms_spm.load_plans_from_cursor_cache (sql_id= > '0argb4cn0sybzprecold planetary hashworthy value = > 2008370210 script sqlbracket handle = >' SYS_SQL_75b06ae056223f5f'); PL/SQL procedure successfully completedtemp-1SQL > select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select / * + no_index (T2 idx_t2) * / object_name%' SQL_HANDLE PLAN_NAME ORIGIN ENABLED ACCEPTED SQL_TEXT -SYS_SQL_75b06ae056223f5f SQL_PLAN_7bc3aw1b24guz24c6dbb6 MANUAL-LOAD YES YES select / * + no_index (T2 idx_t2) * / object_name Object_id from T2 where object_id=4SYS_SQL_75b06ae056223f5f SQL_PLAN_7bc3aw1b24guzb860bcf2 MANUAL-LOAD YES YES select / * + no_index (T2 idx_t2) * / object_name,object_id from T2 where object_id=4

As you can see above, the original target SQL generates a new PLAN, and its PLAN_NAME is SQL_PLAN_7bc3aw1b24guz24c6dbb6. Note that the enabled and accepted of the newly generated SQL PLAN Baseline are both YES, which is a place to automatically capture differences.

Now DROP drops the SQL PLAN Baseline corresponding to the full table scan of the original target SQL.

SQL > exec: temp:=dbms_spm.drop_sql_plan_baseline (sql_handle = > 'SYS_SQL_75b06ae056223f5f',plan_name = >' SQL_PLAN_7bc3aw1b24guzb860bcf2'); PL/SQL procedure successfully completedtemp-1SQL > select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select / * + no_index (T2 idx_t2) * / object_name%' SQL_HANDLE PLAN_NAME ORIGIN ENABLED ACCEPTED SQL_TEXT -SYS_SQL_75b06ae056223f5f SQL_PLAN_7bc3aw1b24guz24c6dbb6 MANUAL-LOAD YES YES select / * + no_index (T2 idx_t2) * / object_name Object_id from t2 where object_id=4

Execute the original SQL again, and you can see that the index is gone.

PLAN_TABLE_OUTPUT- -SQL_ID 85htp4tya3uwm Child number 2--select / * + no_index (T2 idx_t2) * / object_name Object_id from T2 whereobject_id=4Plan hash value: 2008370210-| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |-| 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | 30 | 332 (0) | 00:00:04 | * 2 | INDEX RANGE SCAN | IDX_T2 | 1 | | 1 (0) | 00:00:01 |- -Note--SQL plan baseline SQL_PLAN_7bc3aw1b24guz24c6dbb6 used for this statement

Finally, notice that under NOTE, SQL PLAN Baeline is used.

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.