SQL Plan Management介绍

基于成本的优化法则基于统计信息找到最优执行计划,但是一些环境的变化可能影响执行计划的改变如:

·         New optimizer version

·         Changes to optimizer statistics and optimizer parameters

·         Changes to schema and metadata definitions

·         Changes to system settings

·         SQL profile creating

执行计划的变化有两个方向,更好的性能和更差的性能, 11g以前为了保证执行计划的稳定性通过Stored Outlines和锁定统计信息,但是这样屏蔽了优化的一些新的特性和更优的执行计划,11g SPM很好的解决了始终保证执行计划朝着更好的性能发展。SPM自动管理执行计划,当一个新的执行计划被发现,不是立即被使用,而是被确保被验证该计划比历史执行计划有着更好的性能才被使用。

1. SQL Plan Baselines 捕获

自动捕获

设置参数OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES true

被开启后系统自动创建并维护执行计划历史记录,执行计划历史包含sql语句,绑定变量,编译环境等,第一次生成的执行计划被标记为accepted状态,代表执行计划历史和sql plan baseline。后来生成的执行计划被加入执行计划历史被标记为non-accepted状态,直到被验证不会带来性能下降。

手动捕获

SQL Tuning Sets AWR Snapshots加载

DECLARE

my_plans pls_integer;

BEGIN

  my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(

    sqlset_name => 'tset1');

END;

/

 Cursor Cache加载

DECLARE

my_plans pls_integer;

BEGIN

  my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(

    sql_id => '99twu5t2dn5xd');

END;

/

2. SQL Plan Baselines选择

1 OPTIMIZER_USE_SQL_PLAN_BASELINES 设置为 TRUE

2 sql编译过程中,优化器基于成本的优化法则首先构建一个最优执行计划,然后去sql plan baseline找到匹配的计划,如果能找到就使用该执行计划,如果找不到优化器将要评估sql plan baseline中状态为accepted状态的计划,找到一个成本最低的来执行,并新生成的执行计划加入到sql plan baseline中标记为non-accepted状态,不能被使用,直到被验证不会引起性能下降后被标记为accepted后才可以使用。
SQL Plan Management介绍

3. SQL Plan Baselines演化

Sql plannon-acceptedaccepted的过程,手动演化的语句如下:

SET SERVEROUTPUT ON

SET LONG 10000

DECLARE

    report clob;

BEGIN

    report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(

                  sql_handle => 'SYS_SQL_593bc74fca8e6738');

    DBMS_OUTPUT.PUT_LINE(report);

END;

/

12c引入了SYS_AUTO_SPM_EVOLVE_TASK自动作业verification non-accepted状态的sql plan

4. SQL Plan Baselines the SQL Tuning Advisor

当与SQL Tuning Advisor优化SQL语句,如果找到一个调整计划,并验证其性能要比从相应的SQL plan baseline选择了一个更好的计划,它使一个建议,接受SQL profile。当SQL profile被接受,调整计划被添加到相应的SQL plan baseline

5. 查看SQL Plan Baselines

select sql_handle, sql_text, plan_name, origin,

enabled, accepted, fixed, autopurge

from dba_sql_plan_baselines;

select * from table(

    dbms_xplan.display_sql_plan_baseline(

        sql_handle=>'SYS_SQL_209d10fabbedc741',

        format=>'basic'));

6. Database upgradeSQL Plan Management

方式一

使用SQL Tuning Sets

可以选择(cursor cache, workload repository或者其他SQL Tuning Sets)创建一个新的SQL Tuning Sets

BEGIN

SYS.DBMS_SQLTUNE.CREATE_SQLSET (

sqlset_name => 'SPM_STS',

description => '10g plans');

END;

\

cursor cache填充SQL Tuning Sets

 

DECLARE

stscur dbms_sqltune.sqlset_cursor;

BEGIN

OPEN stscur FOR

SELECT VALUE(P)

FROM TABLE(dbms_sqltune.select_cursor_cache(

‘parsing_schema_name <> ‘‘SYS’’’,

null, null, null, null, 1, null, 'ALL')) P;

-- populate the sqlset

dbms_sqltune.load_sqlset(sqlset_name => 'SPM_STS',

populate_cursor => stscur);

END;

/

加载到SPM

SQL> Variable cnt number

SQL> execute :cnt := DBMS_SPM.LOAD_PLANS_FROM_SQLSET( -

sqlset_name => 'SPM_STS');

 

方式二

Stored Outlines导入SPM

1  alter system set CREATE_STORED_OUTLINES=OLDPLAN;

2 执行应用sql或者开启应用

3 alter system set CREATE_STORED_OUTLINES=false;

4 导出exp outln/outln file=soutline.dmp owner=outln rows=y

5 导入SPM

variable report clob;

exec :report:=DBMS_SPM.MIGRATE_STORED_OUTLINE( -

attribute_name=>'OUTLINE_NAME', attribute_value =>

'OLDPLAN');

 方式三

如果升级前没有捕获执行计划,可以选择此方式

首先设置参数OPTIMIZER_FEATURES_ENABLE10g,然后捕获10g sql plan,然后再加载到SPM,最后再设置OPTIMIZER_FEATURES_ENABLE11g

注意:捕获的执行计划需要使用10g的统计信息 详细流程如下图

SQL Plan Management介绍

7. SQL 管理库策略

Sql plan base 相关信息存储在SQL Management Base,是数据库字典的一部分,存储在sysaux表空间,默认最大限制是sysaux表空间的10%可以通过以下方式更改限制值

BEGIN

  DBMS_SPM.CONFIGURE(

    'space_budget_percent',30);

END;

/

也可以设置没有使用的sql plan保留时间,默认是53周,plan_retention_weeks的范围值是5-523

可以通过以下方式进行调整

BEGIN

  DBMS_SPM.CONFIGURE(

    'plan_retention_weeks',105);

END;

/

配置参数查询

select parameter_name, parameter_value from dba_sql_management_config;

 

PARAMETER_NAME                 PARAMETER_VALUE

------------------------------ ---------------

SPACE_BUDGET_PERCENT                        30

PLAN_RETENTION_WEEKS                       105

 

上一篇:maven多继承关系


下一篇:CentOS7安装phpMyAdmin