How to Use SQL Plan Management (SPM) - Plan Stability Worked Example (文档 ID 456518.1)

ziyoo0830

1725

5页

0次

2019-10-24

5墨值下载

2019/6/27 文档 456518.1

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=445178166272384&parent=DOCUMENT&sourceId=1905305.1&id=456518.

…

1/5

PowerView 已关闭

(0)

tong (可用) 与我们联系帮助

APPLIES TO:

Oracle Database - Enterprise Edition - Version 11.1.0.6 and later

Information in this document applies to any platform.

GOAL

SQL Plan management (SPM) is a preventative mechanism that records and evaluates the execution plans of SQL

statements over time. SPM builds SQL plan baselines composed of a set of existing plans that are known to be

efficient. The SQL plan baselines are then used to preserve performance of corresponding SQL statements, regardless

of changes occurring in the system, providing "plan stability".

Common usage scenarios where SQL plan management can improve or preserve SQL performance include:

Database Upgrade

A database upgrade that installs a new optimizer version usually results in plan changes for a small percentage

of SQL statements, with most of the plan changes resulting in either no performance change or improvement.

However, certain plan changes may cause performance regressions. The use of SQL plan baselines significantly

minimizes potential performance regressions resulting from a database upgrade.

System/Data Changes

Ongoing system and data changes can impact plans for some SQL statements, potentially causing performance

regressions. The use of SQL plan baselines will help to minimize performance regressions and stabilize SQL

performance.

Application upgrade

Deployment of new application modules means introducing new SQL statements into the system. Application

software may use appropriate SQL execution plans developed under a standard test configuration for the new

SQL statements. If your system configuration is significantly different from the test configuration, the SQL plan

baselines can be evolved over time to produce better performance.

If you have a good plan in the cursor cache, then you can load these into SPM so that you can use this baseline to

preserve the performance. The following example command illustrates this:

set serveroutput on

var n number

begin

:n:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'&sql_id', plan_hash_value=>&plan_hash_value,

fixed =>'NO', enabled=>'YES');

end;

EXEC dbms_output.put_line('Number of plans loaded: ' || :n);

After enabling the SPM, for those SQL ID's that have SQL plan management baselines, the database will not collect

new access plans (even after SPM is disabled - assuming the baseline is active)

The purpose of this Document is to show an example of SQL PLAN MANAGEMENT.

NOTE: The steps to accept and un-accept the baseline have changed in 11gR2. For information on how to run

these steps in 11gR2, please see:

Document 1309799.1 How to Accept & Unaccept the plans in sql baselines in 11.2.0.1

SOLUTION

The following is a script that you can run to demonstrate how SPM operates and then apply the base principles to your

actual code. The script contains remarks to explain what is happening as it runs. The script was originally created and

tested on 11.1.0.6.0 in the SH schema. Output may be slightly different on other versions.

SCRIPT spm.sql

此文档是否有帮助?

文档详细信息

类型:

状态:

上次主更新:

上次更新:

HOWTO

PUBLISHED

2018-8-4

2018-9-26

最近查看

---------CUT HERE START------------------------------------------------

set pages 10000 lines 140 long 100000

set echo on

How to Use SQL Plan Management (SPM) - Plan Stability Worked Example (文档 ID 456518.1)

转到底部

是

否

Using SQL Plan

Management (SPM)

面板服务请求补丁程序和更新程序社区认证系统知识

提供反馈...

2019/6/27 文档 456518.1

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=445178166272384&parent=DOCUMENT&sourceId=1905305.1&id=456518.

…

2/5

spool spm.out

--Setting optimizer_capture_sql_plan_baselines=TRUE

--Automatically Captures the Plan for any

--Repeatable SQL statement.

--By Default optimizer_capture_sql_plan_baselines is False.

--A repeatable sql here means which is executed more then once.

--Now we will Capture various Optimizer plans in SPM Baseline.

--Plans are captured into the Baselines as New Plans are found.

pause

alter session set optimizer_capture_sql_plan_baselines = TRUE;

alter session set optimizer_use_sql_plan_baselines=FALSE;

alter session set optimizer_features_enable='11.1.0.6';

pause

set autotrace on

SELECT *

from sh.sales

where quantity_sold > 30

order by prod_id;

pause

SELECT *

from sh.sales

where quantity_sold > 30

order by prod_id;

set autotrace off

pause

select sql_handle,sql_text, plan_name,

origin, enabled, accepted

from dba_sql_plan_baselines

where sql_text like 'SELECT%sh.sales%';

pause

--SYS_SQL_PLAN_0f3e54d254bc8843 is the Very First Plan that

--is inserted in the Plan baseline.

--Note the Very First Plan is ENABLED=YES AND ACCEPTED=YES

--Note the ORIGIN is AUTO-CAPTURE WHICH MEANS The plan was captured

--Automatically when optimizer_capture_sql_plan_baselines = TRUE.

--Note The Plan hash value: 3803407550

pause

alter session set optimizer_features_enable='10.2.0.3';

alter session set optimizer_index_cost_adj=1;

pause

set autotrace on

SELECT *

from sh.sales

where quantity_sold > 30

order by prod_id;

set autotrace off

pause

select sql_handle,sql_text, plan_name,

origin, enabled, accepted

from dba_sql_plan_baselines

where sql_text like 'SELECT%sh.sales%';

pause

---A New Plan SYS_SQL_PLAN_0f3e54d211df68d0 was Found here

---And inserted in the Plan Baseline.

---Note the ACCEPTED IS NO,as This is second plan added to the base line.

---Note The Plan hash value: 899219946

pause

alter session set optimizer_features_enable='9.2.0';

alter session set optimizer_index_cost_adj=50;

alter session set optimizer_index_caching=100;

pause

set autotrace on

SELECT *

from sh.sales

where quantity_sold > 30

order by prod_id;

set autotrace off

pause

[1905305.1]

Master Note: Plan

Stability Features

(Including SQL Plan

Management (SPM))

[1359841.1]

Init.ora Parameter

"DB_UNIQUE_NAME"

Reference Note

[567064.1]

Creating a Physical

Standby using RMAN

Duplicate (RAC or Non-

RAC) [1617946.1]

ORA-32000 not able to

modify the rdbms spfile

which is there in ASM

[1498307.1]

显示更多

2019/6/27 文档 456518.1

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=445178166272384&parent=DOCUMENT&sourceId=1905305.1&id=456518.

…

1/5

PowerView 已关闭

(0)

tong (可用) 与我们联系帮助

APPLIES TO:

Oracle Database - Enterprise Edition - Version 11.1.0.6 and later

Information in this document applies to any platform.

GOAL

SQL Plan management (SPM) is a preventative mechanism that records and evaluates the execution plans of SQL

statements over time. SPM builds SQL plan baselines composed of a set of existing plans that are known to be

efficient. The SQL plan baselines are then used to preserve performance of corresponding SQL statements, regardless

of changes occurring in the system, providing "plan stability".

Common usage scenarios where SQL plan management can improve or preserve SQL performance include:

Database Upgrade

A database upgrade that installs a new optimizer version usually results in plan changes for a small percentage

of SQL statements, with most of the plan changes resulting in either no performance change or improvement.

However, certain plan changes may cause performance regressions. The use of SQL plan baselines significantly

minimizes potential performance regressions resulting from a database upgrade.

System/Data Changes

Ongoing system and data changes can impact plans for some SQL statements, potentially causing performance

regressions. The use of SQL plan baselines will help to minimize performance regressions and stabilize SQL

performance.

Application upgrade

Deployment of new application modules means introducing new SQL statements into the system. Application

software may use appropriate SQL execution plans developed under a standard test configuration for the new

SQL statements. If your system configuration is significantly different from the test configuration, the SQL plan

baselines can be evolved over time to produce better performance.

If you have a good plan in the cursor cache, then you can load these into SPM so that you can use this baseline to

preserve the performance. The following example command illustrates this:

set serveroutput on

var n number

begin

:n:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'&sql_id', plan_hash_value=>&plan_hash_value,

fixed =>'NO', enabled=>'YES');

end;

EXEC dbms_output.put_line('Number of plans loaded: ' || :n);

After enabling the SPM, for those SQL ID's that have SQL plan management baselines, the database will not collect

new access plans (even after SPM is disabled - assuming the baseline is active)

The purpose of this Document is to show an example of SQL PLAN MANAGEMENT.

NOTE: The steps to accept and un-accept the baseline have changed in 11gR2. For information on how to run

these steps in 11gR2, please see:

Document 1309799.1 How to Accept & Unaccept the plans in sql baselines in 11.2.0.1

SOLUTION

The following is a script that you can run to demonstrate how SPM operates and then apply the base principles to your

actual code. The script contains remarks to explain what is happening as it runs. The script was originally created and

tested on 11.1.0.6.0 in the SH schema. Output may be slightly different on other versions.

SCRIPT spm.sql

此文档是否有帮助?

文档详细信息

类型:

状态:

上次主更新:

上次更新:

HOWTO

PUBLISHED

2018-8-4

2018-9-26

最近查看

---------CUT HERE START------------------------------------------------

set pages 10000 lines 140 long 100000

set echo on

How to Use SQL Plan Management (SPM) - Plan Stability Worked Example (文档 ID 456518.1)

转到底部

是

否

Using SQL Plan

Management (SPM)

面板服务请求补丁程序和更新程序社区认证系统知识

提供反馈...

2019/6/27 文档 456518.1

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=445178166272384&parent=DOCUMENT&sourceId=1905305.1&id=456518.

…

2/5

spool spm.out

--Setting optimizer_capture_sql_plan_baselines=TRUE

--Automatically Captures the Plan for any

--Repeatable SQL statement.

--By Default optimizer_capture_sql_plan_baselines is False.

--A repeatable sql here means which is executed more then once.

--Now we will Capture various Optimizer plans in SPM Baseline.

--Plans are captured into the Baselines as New Plans are found.

pause

alter session set optimizer_capture_sql_plan_baselines = TRUE;

alter session set optimizer_use_sql_plan_baselines=FALSE;

alter session set optimizer_features_enable='11.1.0.6';

pause

set autotrace on

SELECT *

from sh.sales

where quantity_sold > 30

order by prod_id;

pause

SELECT *

from sh.sales

where quantity_sold > 30

order by prod_id;

set autotrace off

pause

select sql_handle,sql_text, plan_name,

origin, enabled, accepted

from dba_sql_plan_baselines

where sql_text like 'SELECT%sh.sales%';

pause

--SYS_SQL_PLAN_0f3e54d254bc8843 is the Very First Plan that

--is inserted in the Plan baseline.

--Note the Very First Plan is ENABLED=YES AND ACCEPTED=YES

--Note the ORIGIN is AUTO-CAPTURE WHICH MEANS The plan was captured

--Automatically when optimizer_capture_sql_plan_baselines = TRUE.

--Note The Plan hash value: 3803407550

pause

alter session set optimizer_features_enable='10.2.0.3';

alter session set optimizer_index_cost_adj=1;

pause

set autotrace on

SELECT *

from sh.sales

where quantity_sold > 30

order by prod_id;

set autotrace off

pause

select sql_handle,sql_text, plan_name,

origin, enabled, accepted

from dba_sql_plan_baselines

where sql_text like 'SELECT%sh.sales%';

pause

---A New Plan SYS_SQL_PLAN_0f3e54d211df68d0 was Found here

---And inserted in the Plan Baseline.

---Note the ACCEPTED IS NO,as This is second plan added to the base line.

---Note The Plan hash value: 899219946

pause

alter session set optimizer_features_enable='9.2.0';

alter session set optimizer_index_cost_adj=50;

alter session set optimizer_index_caching=100;

pause

set autotrace on

SELECT *

from sh.sales

where quantity_sold > 30

order by prod_id;

set autotrace off

pause

[1905305.1]

Master Note: Plan

Stability Features

(Including SQL Plan

Management (SPM))

[1359841.1]

Init.ora Parameter

"DB_UNIQUE_NAME"

Reference Note

[567064.1]

Creating a Physical

Standby using RMAN

Duplicate (RAC or Non-

RAC) [1617946.1]

ORA-32000 not able to

modify the rdbms spfile

which is there in ASM

[1498307.1]

显示更多

of 5

5墨值下载

oracle 安装部署

关注

评论