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

This article shows you what SPM refers to in Oracle 11g. The content is concise and easy to understand. It will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

Preface

We know that the performance of SQL statements largely depends on the execution plan of SQL statements. If the execution plan of SQL statements changes, then the performance of SQL statements may change greatly. There are many factors that affect the execution plan of SQL statements, and the common ones are:

Changes in the version of ▪ optimizer

Changes in ▪ statistics

Changes of various parameters related to ▪ optimizer

Modification of ▪ object definition

▪ adds and deletes related indexes

▪ modified the settings of the system

"peep" function of ▪ binding variables

The definition of ▪ binding variables has changed (for example, a change in type or length)

▪ has settings such as outline or SQL Profile enabled

The way we handled it before 11g.

Before Oracle 11g, we can use the storage outline (Stored Outline) and SQL Profile to help us fix the execution plan of a SQL statement and prevent the performance of SQL from deteriorating due to changes in the execution plan. However, these techniques require manual processing by DBA, such as the manual creation of the storage outline by DBA, and the manual application of DBA is required for the SQL Profile provided in 10g to take effect.

In 11g, Oracle provides SPM

SPM is Sql Plan Management, which is a new function of Oracle self-management (or automatic) development. Through this feature, Oracle automatically judges whether the new execution plan of a SQL is more reasonable (lower cost), and it will be used only if the new execution plan is better than the original execution plan, thus effectively protecting the stability of the execution plan and ensuring the execution efficiency of the SQL statement.

About the working principle of SPM, this article does not give a detailed introduction, interested comrades can refer to the relevant materials (there are also many similar articles on the Internet)

Perform a plan management instance test

Test 1: automatically captured scenarios

Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0

Connected as study

SQL > create table test as select * from dba_objects

Table created

SQL > exec DBMS_STATS.gather_table_stats (USER, 'TEST', cascade= > TRUE)

PL/SQL procedure successfully completed

SQL > select object_name from test where object_id=100

OBJECT_NAME

-

FIXED_OBJ$

SQL > select object_name from test where object_id=100

OBJECT_NAME

-

FIXED_OBJ$

Although the above query statement has been executed twice, when we query dba_sql_plan_baseline at this time, we will find that we cannot find the records we need, because at this time optimizer_capture_sql_plan_baselines is set to false. The function of automatic capture is disabled. Let me set this parameter to true to continue testing:

SQL > alter session set optimizer_capture_sql_plan_baselines=true

Session altered

SQL > select object_name from test where object_id=100

OBJECT_NAME

-

FIXED_OBJ$

SQL > select object_name from test where object_id=100

OBJECT_NAME

-

FIXED_OBJ$

SQL > select signature,sql_handle,plan_name,origin,enabled,accepted,autopurge from dba_sql_plan_baselines

SIGNATURE SQL_HANDLE PLAN_NAME ORIGIN ENABLED ACCEPTED AUTOPURGE

4.32736134 SYS_SQL_3c0de038050ab8f1 SYS_SQL_PLAN_050ab8f197bbe3d0 AUTO-CAPTURE YES YES YES

At this point, when we query dba_sql_plan_baseline, we will find that the statement records an execution plan in plan history.

Description of several key fields

Sql_handle: a handle that represents a SQL statement

Plan_name: indicates the name of the execution plan for the SQL statement

Origin: indicates how the execution plan entered the plan history. AUTO-CAPTURE: optimizer is added automatically; MANUAL:DBA is added manually

Enabled: indicates whether it is enabled. YES: enable NO: disable. If an execution plan is disabled, the optimizer will not consider using that execution plan at all

Accepted: indicates whether to accept it, that is, whether it has entered the plan baseline. YES indicates acceptance, and NO indicates no acceptance.

Autopurge: indicates whether it is automatically deleted on a regular basis. YES: yes, NO: no.

Fixed: when there are multiple execution plans in plan baseline, it means that the optimizer only considers those plans that are set to FIXED and chooses the best one.

Continue the journey of testing, add a new index, and then execute the above SQL statement:

SQL > create index inx_test_object_id on test (object_id)

Index created

SQL > exec DBMS_STATS.gather_table_stats (USER, 'TEST', cascade= > TRUE)

PL/SQL procedure successfully completed

SQL > select object_name from test where object_id=100

OBJECT_NAME

-

FIXED_OBJ$

SQL > select signature,sql_handle,plan_name,origin,enabled,accepted,autopurge from dba_sql_plan_baselines

SIGNATURE SQL_HANDLE PLAN_NAME ORIGIN ENABLED ACCEPTED AUTOPURGE

4.32736134 SYS_SQL_3c0de038050ab8f1 SYS_SQL_PLAN_050ab8f127b7cc01 AUTO-CAPTURE YES NO YES

4.32736134 SYS_SQL_3c0de038050ab8f1 SYS_SQL_PLAN_050ab8f197bbe3d0 AUTO-CAPTURE YES YES YES

SQL >

As you can see, there is an extra execution plan in the dba_sql_plan_baselines view (grayed out above), but the accepted of the execution plan is NO, indicating that it has not entered the plan baseline, but has entered the plan history.

If we want to get this project into plan baseline, we can do it with dbms_spm:

Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 Connected as study SQL > SQL > set serveroutput on long 100000 SQL > declare 2 report clob 3 begin 4 report: = dbms_spm.evolve_sql_plan_baseline (sql_handle = > 'SYS_SQL_3c0de038050ab8f1', 5 plan_name = >' SYS_SQL_PLAN_050ab8f127b7cc01'); 6 dbms_output.put_line (report); 7 end 8 /-Evolve SQL Plan Baseline Report -Inputs:-SQL_HANDLE = SYS_SQL_3c0de038050ab8f1 PLAN_NAME = SYS_SQL_PLAN_050ab8f127b7cc01 TIME_LIMIT = DBMS_SPM.AUTO_LIMIT VERIFY = YES COMMIT = YES Plan: SYS_SQL_PLAN_050ab8f127b7cc01-Plan was verified: Time used. 062 seconds. Passed performance criterion: Compound improvement ratio > = 337.17. Plan was changed to an accepted plan. Baseline Plan Test Plan Improv. Ratio-Execution Status: COMPLETE COMPLETE Rows Processed: 1 1 Elapsed Time (ms): 13 0 CPU Time (ms): 150 Buffer Gets: 1010 3 336.67 Disk Reads: 0 Direct Writes: 0 Fetches: 0 0 Executions: 1 1- -Report Summary-Number of SQL plan baselines verified: 1. Number of SQL plan baselines evolved: 1.PL/SQL procedure successfully completed SQL >

Take a look at dba_sql_plan_baselines again.

SQL > select signature,sql_handle,plan_name,origin,enabled,accepted,autopurge from dba_sql_plan_baselines

SIGNATURE SQL_HANDLE PLAN_NAME ORIGIN ENABLED ACCEPTED AUTOPURGE

4.32736134 SYS_SQL_3c0de038050ab8f1 SYS_SQL_PLAN_050ab8f197bbe3d0 AUTO-CAPTURE YES YES YES

4.32736134 SYS_SQL_3c0de038050ab8f1 SYS_SQL_PLAN_050ab8f127b7cc01 AUTO-CAPTURE YES YES YES

SQL >

Let's take a look at the details of these two implementation plans:

SQL > select * from table (DBMS_XPLAN.display_sql_plan_baseline (plan_name= > 'SYS_SQL_PLAN_050ab8f127b7cc01')) PLAN_TABLE_OUTPUT-SQL handle: SYS_SQL_3c0de038050ab8f1 SQL text: select object_name from test where object_id=100- -Plan name: SYS_SQL_PLAN_050ab8f127b7cc01 Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE-- -Plan hash value: 2422726699- -| Id | Operation | Name | Rows | Bytes | Cost-- | 0 | SELECT STATEMENT | | 1 | 30 | 2 | 1 | TABLE ACCESS BY INDEX ROWID | TEST | 1 | 30 | 2 | * 2 | INDEX RANGE SCAN | INX_TEST_OBJECT_ID | 1 | | 1-PLAN_TABLE_OUTPUT- -Predicate Information (identified by operation id):- -2-access ("OBJECT_ID" = 100) 25 rows selected SQL > SQL > select * from table (DBMS_XPLAN.display_sql_plan_baseline (plan_name= > 'SYS_SQL_PLAN_050ab8f197bbe3d0')) PLAN_TABLE_OUTPUT- -SQL handle: SYS_SQL_3c0de038050ab8f1 SQL text: select object_name from test where object_id=100-- -Plan name: SYS_SQL_PLAN_050ab8f197bbe3d0 Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE-Plan hash value: 1357081020 -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 1 | 30 | 282 (1) | 00:00:04 | | * 1 | TABLE ACCESS FULL | TEST | 1 | 30 | 282 (1) | 00:00:04 |-- -PLAN_TABLE_OUTPUT-Predicate Information (identified by operation id):- -1-filter ("OBJECT_ID" = 100) 24 rows selected SQL >

Take a look at which baseline is selected when this SQL is executed:

SQL > conn study/study is connected. SQL > set autotrace trace SQL > select object_name from test where object_id=100 Implementation Plan-Plan hash value: 2422726699- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- | 0 | SELECT STATEMENT | | 1 | 30 | 2 (0) | 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID | TEST | 1 | 30 | 2 (0) | 00:00:01 | | * 2 | INDEX RANGE SCAN | INX_TEST_OBJECT_ID | 1 | 1 (0) | 00:00:01 | |-Predicate Information (identified by operation id):- -- 2-access ("OBJECT_ID" = 100) Note-- SQL plan baseline "SYS_SQL_PLAN_050ab8f127b7cc01" used for this statement SQL >

Let's familiarize ourselves with the execution plan in manually deleting plan baseline, which can also be done with dbms_spm. The following DEMO clears the execution plan of full table scan in the above baseline:

SQL > var ret number

SQL > exec: ret:=dbms_spm.drop_sql_plan_baseline (sql_handle = > 'SYS_SQL_3c0de038050ab8f1'

Plan_name = > 'SYS_SQL_PLAN_050ab8f197bbe3d0')

PL/SQL procedure successfully completed

Ret

-

one

SQL >

SQL > select signature,sql_handle,plan_name,origin,enabled,accepted,autopurge from dba_sql_plan_baselines

SIGNATURE SQL_HANDLE PLAN_NAME ORIGIN ENABLED ACCEPTED AUTOPURGE

4.32736134 SYS_SQL_3c0de038050ab8f1 SYS_SQL_PLAN_050ab8f127b7cc01 AUTO-CAPTURE YES YES YES

SQL >

Test 2: manually captured scenarios

Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 Connected as study SQL > select sql_text,sql_id from v$sql where sql_text like'% select object_name from test where%' SQL_TEXT SQL_ID-select object_name from test where object_id=100 7j7jc706upva2 SQL > SQL > set serveroutput on SQL > declare 2 l_plans_loaded PLS_INTEGER 3 begin 4 l_plans_loaded: = DBMS_SPM.load_plans_from_cursor_cache (5 sql_id = > '7j7jc706upva2'); 6 7 DBMS_OUTPUT.put_line (' Plans Loaded:'| | l_plans_loaded); 8 END 9 / Plans Loaded: 1 PL/SQL procedure successfully completed SQL > SQL > select signature,sql_handle,plan_name,origin,enabled,accepted,autopurge 2 from dba_sql_plan_baselines SIGNATURE SQL_HANDLE PLAN_NAME ORIGIN ENABLED ACCEPTED AUTOPURGE -4.32736134 SYS_SQL_3c0de038050ab8f1 SYS_SQL_PLAN_050ab8f127b7cc01 MANUAL-LOAD YES SQL > the above is what SPM refers to in Oracle 11g Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

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.