[20120803]11G SPM的学习1.txt

[20120803]11G SPM的学习1.txt

    开始学习SQL Plan Management(SPM) ,11G开始提供SPM，在10g下我经常使用sql profile看一些bad sql语句，sql profile我觉得已经做的很好，

有时候能够提供很好的建议。我开始学习SPM的时候感觉不习惯（也许是因为在toad下使用sql profile很简单）为什么oracle还有搞出SPM来，慢

慢看资料，才明白其中一些细节。

    我看过别人在从8i升级到9i的时候，出现性能波动，里面的一些sql语句执行计划发生了变化。我自己也遇到从8i升级到10g时出现sql语句性能问

题，10g下的自动分析导致建立了一些字段建立不必要的直方图。

    SPM对于sql执行计划的稳定起到很好的作用，对于升级和稳定sql语句起到很好的作用。

    通过一些例子来学习，我的测试环境如下：

1.SPM设置与这两个参数有关：

以上两个参数是缺省设置，如果设置optimizer_capture_sql_plan_baselines=true,可以让oracle自动捕获与建立sql plan baseline。

一般缺省设置是false。

optimizer_use_sql_plan_baselines=true,当查询sql分析时，oracle产生几个执行计划,对比sql plan baseline是否存在，如果存在

，oracle直接选取该计划。

建立测试表：

2.建立sql plan baseline:

也许有许多方法，一般方法如下：

方法1:

设置optimizer_capture_sql_plan_baselines＝true

alter session set optimizer_capture_sql_plan_baselines＝true;

执行sql语句两次以上！

alter session set optimizer_capture_sql_plan_baselines＝false;

方法2:

使用dbms_spm包的load_plans_from_cursor_cache函数导入，我测试使用该方法。

--知道sql_id=3yxwagyspybax,plan_hash_value=1601196873

--我一直有一个疑问？如果通过这个查询到sql_id. 直接查询sql_text当然可以。

--可以发现EXACT_MATCHING_SIGNATURE，FORCE_MATCHING_SIGNATURE与SIGNATURE相等。而SQL_PLAN_BASELINE为NULL。

--执行select * from t where id=:x;2次，每次后访问v$sql视图看看！

--可以发现sql_id=3yxwagyspybax产生了子光标,v$sql.SQL_PLAN_BASELINE与dba_sql_plan_baselines.PLAN_NAME相等。

--可以通过这些信息,查询相关信息。

3.改动sql语句看看。（把select改为Select看看);

这样可以通过v$sql的SQL_PLAN_BASELINE与EXACT_MATCHING_SIGNATURE与dba_sql_plan_baselines的PLAN_NAME和SIGNATURE建立联系。

再改动sql语句，使用文字变量看看。

--大家可以通过这些例子理解EXACT_MATCHING_SIGNATURE，FORCE_MATCHING_SIGNATURE。有点离题了,^_^。

--FORCE_MATCHING_SIGNATURE视乎是转成了绑定变量来对比，并且忽略sql语句大小写。这些知识自己不是很懂，跳过.

4.建立索引:

--可以发现由于sql plan baseline存在，执行计划依旧使用全表扫描.

--查询dba_sql_plan_baselines可以发现多了1条记录,即使我们设置optimizer_capture_sql_plan_baselines=FALSE,

--oracle可以捕获新的计划和baseline,只不过accepted=no,也就是执行不采用该计划。

--通过origin字段，也可以看到增加的记录是AUTO-CAPTURE。

--查看SQL_HANDLE=SYS_SQL_a45a9e109f85e5a4, PLAN_NAME=SQL_PLAN_a8qny22gsbtd40893a4b2的执行计划可以使用

--为了后面能操作比较快，我定义如下变量：

5.修改sql plan baseline:

--奇怪，没有ACCEPTED这个属性。

--   attribute_name   - One of the following possible attribute names:

--                      'ENABLED',

--                      'FIXED',

--                      'AUTOPURGE',

--                      'PLAN_NAME',

--                      'DESCRIPTION'

--   attribute_value  - If the attribute name denotes a plan status then

--                      the legal values are: 'YES', 'NO'.

--                      If the attribute name denotes a plan name then the

--                      supplied value should not conflict with already

--                      stored plan name.

--                      If the attribute name denotes plan description then

--                      any character string is allowed.

-- 也就是不能通过DBMS_SPM.ALTER_SQL_PLAN_BASELINE来修改ACCEPTED属性？

--我看一些blog，属性ACCEPTED修改为NO,这样操作是可以的。不知道是否是版本的问题。

链接如下:https://forums.oracle.com/forums/thread.jspa?messageID=9470036

From 11R2 onwards to accept the plan the script. has changed but to unaccept we have to change the enable status to 'NO'

so that the plan is not in use and therefore we have suggested the script. which will make the plan enable as 'NO' and

thus the plan will become unusable.

If the ACCEPTED=YES and ENABLE=NO,then the plan will not be used as for the plan to be used the ENABLE has to 'YES',in

11R1 we were able to change the status of ACCEPTED to 'NO' and 'YES' but from 11R2 we can change the plan status of

ACCEPTED from 'NO' to 'YES'.

    Therefore we requested you to change the enable status to 'NO' for which we have provided the script.

    Here "verify" indicates that it will not do any performance review and plan will be accepted forcefully,if we set this

as 'YES' then it will do the performance review and then only will accept the plan.

    For accepting the plan the "commit" has to be 'YES'.

    var pbsts varcahr2(1000)

    exec :pbsts := dbms_spm.evolve_sql_plan_baseline('SYS_SQL_7de69bb90f3e54d2','SQL_PLAN_7vtnvr47mwp6k54bc8843',verify=> 'NO', commit=>'YES');

要将属性ACCEPTED修改为YES,仅仅这样操作。

--可以发现oracle使用新的执行计划，plan_name=SQL_PLAN_a8qny22gsbtd40893a4b2 .

--还可以使用另外的方法：

--exec :v_basenum :=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_handle => :v_sql_handle,plan_name => :v_plan_name_index,attribute_name => 'ACCEPTED',attribute_value => 'NO');

--使用这个执行计划

--可以发现plan_name=SQL_PLAN_a8qny22gsbtd40893a4b2,ORIGIN=MANUAL-LOAD.

--可以发现执行计划使用索引扫描。