稳定执行计划操作手册--oracle库

稳定执行计划操作手册--oracle库。稳定执行计划操作手册。

一、 概述

本文档列举了不同版本间稳定执行计划的方法，给出了详细的操作步骤。考虑到可阅读性及可操作性，未做过多的原理介绍，需要使用者在操作前已经具备相关技术知识，以便操作起来得心应手。

Outline 的使用前提

Outline的功能是为了保存sql执行计划，以保证sql在db配置或者数据变化时每次执行时都使用该执行计划。Outline的实现是通过保存生成sql 执行计划的输入信息(包括hint， 初时化参数等)。

Outline使用需要注意的事项：

1) Outline的计划只有在outline内所有hint都有效的情况下才会被使用。如：索引没有创建的前提下，索引的hint是失效的，导致整个outline计划不会被使用。

2) 参数Cursor_sharing=force时不能使用outline. 当前环境cursor_sharing =exact

3) literial sql的共享程度不高，Outline针对绑定变量的sql较好。针对literial sql的情况，需要每条sql都生成outline。或是在cursor_sharing=similar 情况下生成。但在这种情况下，

生成outline 的环境与生产环境应具有相同的cursor_sharing 参数值。

4) 创建outline需要有create any outline的权限。

二、 9i 使用方法

简述

这里描述的是在Oracle 数据库生产环境中由于未知的原因，使得单个sql出现严重的性能问题，在判断出是执行计划不正确引起的时，采用outlines 技术，从其它具有正确的执行计划的环境，

如测试库，开发库等获取正确的执行计划，并导入到生产库中的方法。

以下步骤可用SYS用户或者实名用户。

步骤一、 授权

可预先在各个数据库中以 sys 执行以下步骤，以便节省后续的时间：(这一步一般不需要处理)

-- 为 outln 用户授权

grant create any outline to outln;

grant drop any outline to outln;

grant all on plan_table to outln;

-- 创建 plan_table 并创建同义词

@?/rdbms/admin/utlxplan.sql;

create public synonym plan_table for plan_table;

grant select ,insert, update, delete on plan_table to public;

步骤二、 获取执行计划

在生产库获得存在性能问题的SQL的 sql_hash_value ，并且获取当前执行计划。

步骤三、 获取SQL文本

从v$sqltext 中获得相应的sql语句片段，通过UE替换成完整SQL。

除最后一行外，其它行尾部的数字一定会是64，表示该行除该数字外有64个字符，最后一行的数字也为指示该行有多少字符。通过UE替换64^p为空，再将最后的字符数替换为分号即可。

set linesize 80

set head off

set feedback off

spool /tmp/sql1.txt

select sql_text || length(sql_text) sql_text from v$sqltext where hash_value=&hash_value

order by piece;

spool off

步骤四、 生成outline

1) 找到有好的执行计划其它环境，如COW库、测试库、开发库或其它同类型的生产库。

2) 如果没有能够找到好的执行计划，需要想办法在其它环境制造出好的执行计划，如临时删除一个不合适的索引，从而强制其使用期望的索引。

通过如下SQL生成outline，红色部分需要替换:

create outline for category special on

<步骤三获取的问题SQL文本>;

步骤五、 导出outline

使用如下命令导出outline，parfile内容如下，红色部分注意替换：

exp parfile=exp_outl.par

--exp_outl.par

userid="outln/outln"

tables=(outln.ol$,outln.ol$hints,outln.ol$nodes)

file=outln_20120627.dmp

query="WHERE ol_name in ('','','')"

STATISTICS=NONE

步骤六、 传输dmp文件

通过主机命令将dmp文件传输至目标库。

步骤七、 导入outline

在目标库导入outline，命令如下，红色部分需要替换：

imp outln/outln file=outln_20120627.dmp full=y ignore=y log=ol_imp.log

步骤八、 启用outline

导入outline后，执行如下命令启用outline

exec dbms_outln.update_signatures;

alter system set use_stored_outlines=special;

步骤九、 验证outline

在生产库kill还在使用原执行计划的session，以便其重新登录后使用新计划，检查如下确认outline生效：

01. 检查dba_outlines：

select name,

to_char(TIMESTAMP, 'yyyy-mm-dd hh24:mi:ss') TIMESTAMP,

USED,

SQL_TEXT

from dba_outlines

where name like 'KXN%';

02. 检查新进的SQL是否用上好的执行计划

三、 10g 使用方法

简述

10g做outline比9i简单，不需要用UE处理SQL文本，直接拿sql_hash_value和child_number即可，实际中可以使用SYS用户和实名用户，如果用10g的方法抓到SQL是错误的，可以使用9i的方法。

步骤一、 获取SQL信息

查出问题SQL的hash_value和child_number

select hash_value,child_number from v$sql where hash_value in('1030675442',

'1165145440',

'2506807260');

步骤二、 生成 outline

alter session set create_stored_outlines=true;

exec dbms_outln.create_outline(2452139749, 0, 'SPECIAL');

步骤三、 修改outline名称

上一步生成的outline是系统自动命名的，可将outline名称修改为有意义的名字。

alter outline SYS_OUTLINE_12062711343918532 rename to kxn_1030675442;

步骤四、 导出outline

使用如下parfile导出outline，注意替换红色部分命令如下：

exp parfile=exp_outl.par

--exp_outl.par

userid="outln/outln"

tables=(outln.ol$,outln.ol$hints,outln.ol$nodes)

file=outln_20150927.dmp

query="WHERE ol_name in ('','','')"

STATISTICS=NONE

步骤五、 传输dmp文件

通过主机命令将dmp文件传输至目标库。

步骤六、 导入outline

在目标库导入outline，命令如下，红色部分需要替换：

imp outln/outln file=outln_20150927.dmp full=y ignore=y log=ol_imp0927.log

步骤七、 启用outline

导入outline后，执行如下命令启用outline

exec dbms_outln.update_signatures;

alter system set use_stored_outlines=special;

步骤八、 验证outline

在生产库kill还在使用原执行计划的session，以便其重新登录后使用新计划，检查如下确认outline生效：

01. 检查dba_outlines：

select name,

to_char(TIMESTAMP, 'yyyy-mm-dd hh24:mi:ss') TIMESTAMP,

USED,

SQL_TEXT

from dba_outlines

where name like ‘LIZY%’;

02. 检查新进的SQL是否用上好的执行计划

四、 outline 互换

简述

一般通过导出导入outline可解决紧急问题，但是有一些情况，比如需要导出导入的执行计划是经过修改后的SQL得到的(比如hint)，

修改后的SQL要保证和修改前的处理逻辑是一样的，返回结果是一样的，这种情况单纯的导出导入outline是不行的，需要用到outline互换。

这里前两步做outline的方法，可用9i的，如果是10g及以上，也可以用10g的方法，在互换名称的时候注意名字正确就可以。

步骤一、 优化前做outline

这一步是为原来的，性能差的执行计划生成outline。

CREATE OR REPLACE OUTLINE before for category special ON

<问题SQL文本>;

步骤二、 优化后做outline

这一步是为加Hint后的，好的执行计划生成outline。

CREATE OR REPLACE OUTLINE after for category special ON

<问题SQL文本>;

步骤三、 互换outline名字

这一步是关键，通过互换outline名字，达到交换执行计划的目的。

UPDATE OUTLN.OL$HINTS

SET OL_NAME=DECODE(OL_NAME,'after','before','before','after')

WHERE OL_NAME IN ('after','before');

步骤四、 启用outline

交换完成后，执行如下命令启用outline。

exec dbms_outln.update_signatures;

alter system set use_stored_outlines=special;

步骤五、 删除无用outline

通过前面交换后，加hint后的SQL执行执行计划变成原来的较差的执行计划了，为了不影响版本下发等，将这个outline删除掉。

drop outline after;

步骤六、 修改outline名称

将前面保留下来的before修改为有意义的名字。

alter outline before rename to kxn_1030675442;

步骤七、 验证outline

在生产库kill还在使用原执行计划的session，以便其重新登录后使用新计划，检查如下确认outline生效：

01. 检查dba_outlines：

select name,

to_char(TIMESTAMP, 'yyyy-mm-dd hh24:mi:ss') TIMESTAMP,

USED,

SQL_TEXT

from dba_outlines

where name like 'KXN%';

02. 检查新进的SQL是否用上好的执行计划

五、 11g SPM使用方法

简述

11g数据库，采用SPM管理执行计划，应该优先使用SPM解决SQL执行计划问题，下面介绍常用场景下的操作方法。

场景一. 加hint优化后替换原执行计划

当在同一个数据库，既有好的执行计划，又有坏的执行计划时，采用这种方法。

步骤一、 装载坏的执行计划

获得执行计划错误的SQL语句的SQL_ID,并当前将坏的执行计划装载到SPM里。

variable cnt number;

execute :cnt :=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(SQL_ID => '&SQL_ID', PLAN_HASH_VALUE => &HASH_VALUE) ;

步骤二、 确认被装载到SPM

检查SPM，确认相关的SQL计划已经被装载到SPM，LOAD进来的一般是最新的。

select SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, SQL_TEXT

from dba_SQL_PLAN_BASELINES

where ACCEPTED = 'YES'

order by LAST_MODIFIED;

步骤三、 优化SQL

调整SQL语句，如增加新的hint，确认获得理想的执行计划后，执行调整后的语句，取得SQL_ID和Plan hash value。

select sql_id, plan_hash_value

from v$sql

where sql_text like '%/*+ test2-nbh INDEX(demand_state_alias%';

注意

对于有绑定变量的SQL，最好也使用绑定变量的方式来获得正确的执行计划，如果使用字面量，执行计划虽然被装载，但可能无法被SQL语句使用。

同时可以在SQL语句增加一些特别的提示，以容易获得修改后的语句，如上面的查询增加test2-nbh这样一个标识。