Oracle 8i/9i中的执行计划稳定性

本文翻译自Jonathan Lewis的文章. Plan Stability in Oracle 8i/9i, 可以到此下载这篇文章的word版本. Stored Outlines in Oracle 8

Oracle 8i/9i中的执行计划稳定性
找出如何使用”存储概要”来提高应用的性能的方法,即使你无法修改源代码,调整索引或者胡乱摆弄配置..
by Jonathan Lewis (jonathan@jlcomp.demon.co.uk)

工具箱: 为了测试需要,这篇文章将仅仅涉及可在一个SQL*Plus会话中运行的简单SQL与PL/SQL代码. 读者将需要拥有一些特别的权限(典型的终端用户一般没有这些权限),另外,还需要熟悉一些基本SQL的技能. 本文从Oracle8i开始讲起, 接着讨论Oracle9i, 在9i部分将提供多个针对存储概要的生成与处理的改进.

针对黑盒的后门
如果你是这样一个DBA,在你负责维护的Oracle数据库上运行的是一个第三方应用程序, 你一定曾经遇到这样的困惑, 库缓存(Library Cache)中有一些运行的异常缓慢并且代价高昂的SQL,但是只能在源代码中添加提示才能解决问题.
从Oracle8.1开始,你不再需要重写SQL以添加提示了,可以在不调整代码的情况下使得提示生效.这个特性就是存储概要,也即执行计划稳定性,它的原理也很简单:存储信息到数据库中,告诉数据库”如果发现一条SQL语句与XXX长得像,就将这些提示放到如下这些位置”.
实际上,这将给你如下三种可能的好处.首先,可以优化那部分代价昂贵的语句.其次,如果存在一些Oracle需要花费很长时间进行优化(而不是执行)的其他语句,可以利用它来节约时间并降低优化阶段的争用.最后,它为了提供了一个使用新的cusror_sharing参数的机会同时又不用付出失去最优执行路径的损失.
在Oracle8中让它起作用有几个问题需要处理(在Oracle9中大部分哦都去掉了), 但是,通常都可以很容易的利用这个特性.

背景/概述
为了展示如何善用存储概要,我们将从一个包含无法修改源代码的存储过程开始, 显然, 这个存储过程会包含一个运行异常无效的SQL.
我们将看到,我们可以如何捕捉SQL语句以及它在数据库中的当前执行路径的细节,发现一些提示来提高这个SQL的性能,然后, 并且使得这条语句在将来的任何时候运行的时候都使用我们指定的提示.
在下面的展示中, 我们将创建一个用户,在这个用户的Schema中一张表,并且创建一个存储过程来访问这张表-但是仅仅为了有趣-我们将在这个存储过程上使用工具wrap, 以致我们无法对这段代码做逆向工程.
这个演示将默认存储概要框架已经在数据库创建是自动安装了.

初步配置
创建一个用户并使其拥有如下权限:create session,create table,create procedure,create any outline,以及alter session. 以此用户登录并运行以下脚本来创建表.

create table so_demo (
	n1	number,
	n2	number,
	v1	varchar2(10)
)
;

insert into so_demo values (1,1,'One');

create index sd_i1 on so_demo(n1);
create index sd_i2 on so_demo(n2);

analyze table so_demo compute statistics;

接着,需要如下代码来创建存储过程以访问这个表. 创建一个名为c_proc.sql的脚本并包含以下内容:

create or replace procedure get_value (
	i_n1	in	number,
	i_n2	in	number,
	io_v1	out	varchar2
)
as
begin
	select	v1
	into	io_v1
	from	so_demo
	where	n1 = i_n1
	and	n2 = i_n2
	;
end;
/

当然可以直接运行c_proc.sql脚本来创建这个存储过程,但是,为了测试效果,到操作系统中运行如下命令:

wrap iname=c_proc.sql

返回的信息应该是:

Processing c_proc.sql to c_proc.plb

运行难以理解的脚本c_proc.plb而不是c_proc.sql来创建存储过程, 你将会发现, 在user_source视图中再也找不到目标SQL 语句的任何一点蛛丝马迹.

这个应用到底想做什么?
现在, 我们已经有了想要假装运行的应用了,或许还可以考虑将sql_trace打开来看看它到底在做什么. 毋须吃惊, 这个SQL语句运行一个全表扫描来得到想要的数据.
在这个小小的测试中, 全表扫描可能是最有效的选择了,但是,我们假定当Oracle使用and-equal选项将我们的单列索引组合起来的执行路径可以获得最佳性能.
使用存储概要来解决这个问题,答案就很简单了. 实际上,有多种方法来实现我们想要达到的目的,因此, 不要将这个例子当作终极策略. Oracle一直在不断推出新特性以使得工作更加轻松,在这儿描述的方法在将来的版本中将无可避免的变得过时.

你想要应用如何去做?
让Oracle按照我们想要的去做需要分三个阶段:

登录一个新的会话并重新运行这个存储过程,首先告诉Oracle我们需要捕捉它运行的每一条SQL语句,以及这些SQL相关的执行路径的详细信息. 这些”路径”存储概要的第一个例子.

为任何有问题的SQL语句创建更好的存储概要,并且将糟糕的存储概要与这个更好的存储概要进行”交换”.

登录一个新的会话, 让Oracle在下次遇上匹配的SQL的时候开始使用这个新的存储概要而不是使用常规的优化方法;接着再次运行这个存储过程.

必须不断的停止/开始新的会话以确保pl/sql 没有缓存这个游标. 存储概要仅仅在游标被解析的时候才能被创建与应用, 因此必须确保已经存在的相关游标已经关闭.
因此创建一个会话,并执行以下命令:

alter session set create_stored_outlines = demo;

接着运行一小段匿名块来执行这个存储过程,例如:

declare
	m_value	varchar2(10);
begin
	get_value(1, 1, m_value);
end;
/

然后停止收集执行路径(不然接着要运行的部分SQL也将出现在存储概要的表中,使得使得事情变得更加复杂).

alter session set create_stored_outlines = false;

为了看到我们活动的结果,查询以下视图可以让我们看到Oracle为我们创建并保存的存储概要的细节.

select name, category, used, sql_text
from user_outines
where category = 'DEMO';

NAME                           CATEGORY                       USED
------------------------------ ------------------------------ ---------
SQL_TEXT
--------------------------------------------------------------------------------
SYS_OUTLINE_020503165427311    DEMO                           UNUSED
SELECT V1   FROM SO_DEMO  WHERE N1 = :b1  AND N2 = :b2

select	name, stage, hint
from	user_outline_hints
where	name = ' SYS_OUTLINE_020503165427311';

NAME                                STAGE HINT
------------------------------ ---------- --------------------------------
SYS_OUTLINE_020503165427311             3 NO_EXPAND
SYS_OUTLINE_020503165427311             3 ORDERED
SYS_OUTLINE_020503165427311             3 NO_FACT(SO_DEMO)
SYS_OUTLINE_020503165427311             3 FULL(SO_DEMO)
SYS_OUTLINE_020503165427311             2 NOREWRITE
SYS_OUTLINE_020503165427311             1 NOREWRITE

我们可以看到,有一个类目为demo有且仅有一个存储概要,研究这个存储概要的sql_text将发现它与我们原来的PL/SQL源中的SQL类似但又不完全一致. 这一点非常重要, 只有在将要运行的sql_text与保存在存储概要中的sql_text非常接近的时候, Oracle才会使用这个存储概要. 实际上, 在Oracle8i中, 这个SQL必须完全匹配, 这也是最初使用存储概要时的一个致命的问题.
从列表中可以看出,存储概要仅仅是一组提示, 这些提示描述Oracle运行特定SQL将要采取的行动. 这个执行计划使用了一个全表扫描操作-哪怕是简单到只是一个全表扫描, Oracle也使用了大量的提示来确保它的执行.
注意,存储概要总是归属于一个类目;在这个例子中是demo类目,这是我们在最初的alter session命令中指定的. 如果我们最初的命令中仅仅指定True而不是demo的话,将会发现存储概要虽在的类目为default.
存储概要也拥有名字, 并且这个名字必须在整个数据库中保持唯一.任何两个存储概要都不能拥有同一个名字, 即使它们是由不同的用户产生的.实际上,存储概要没有属主,它们只有创建者. 如果你创建一个存储概要并且刚刚好与我将要运行的一段SQL语句匹配,接下来Oracle将应用你的这一组提示到我的文本上-即使这些提示在我的Schema中是完全无意义的. (这给我们提供了一种完全不同的伪装存储概要的选项,这将在另外一篇文章中介绍). 你可能已经注意到,当Oracle自动生成存储概要的时候,它的名字有一个简单的格式-包含一个到最近的微秒的时间戳.
接着调优我们的问题SQL, 我们判断, 如果我们能够注入提示/*+ and_equal(so_demo, sd_i1, sd_i2) */,Oracle将使用我们期望的执行路径,因此我们明确的创建如下存储概要:

create or replace outline so_fix
for category demo on
select /*+ and_equal(so_demo, sd_i1, sd_i2) */ v1
from	so_demo
where	n1 = 1
and	n2 = 2;

这个语句创建了一个显示命名为so_fix的存储概要到我们的demo类目.通过指定谓词name = ‘SO_FIX’来查询user_outlines和user_outline_hints,可以看到这个存储概要到底长啥样.

NAME                           CATEGORY                      USED
------------------------------ ------------------------------ ---------
SQL_TEXT
--------------------------------------------------------------------------------
SO_FIX                         DEMO                           UNUSED
select /*+ and_equal(so_demo, sd_i1, sd_i2) */ v1
from so_demo
where n1 = 1
and n2 = 2

NAME                                STAGE HINT
------------------------------ ---------- --------------------------------
SO_FIX                                  3 NO_EXPAND
SO_FIX                                  3 ORDERED
SO_FIX                                  3 NO_FACT(SO_DEMO)
SO_FIX                                  3 AND_EQUAL(SO_DEMO SD_I1 SD_I2)
SO_FIX                                  2 NOREWRITE
SO_FIX                                  1 NOREWRITE

请特别注意, FULL(SO_DEMO)所在的行已经被替换成了AND_EQUAL(SO_DEMO SD_I1 SD_I2),这也正是我们想要看到的.
现在,我们必须交换这两个存储概要.我们希望每次Oracle看到原来的文本的时候都使用我们的新的提示列表,我们必须欺骗Oracle(来达到这一点).视图user_outlines与user_outline_hints是由schemaoutln中的两个表(分别为ol$和ol$hints)生成的,我们将不得不直接修改这两张表; 这意味着要使用outln登录到数据库或者使用包含更新这两张表的权限的账户登录.
幸运的是,outln schema里面的这两张表不包含任何引用完整性约束.方便的是,表ol$(outline)与表ol$hints(hint)之间的关系是通过存储概要的名称(存储在列ol_name中)来定义的.因此,通过非常仔细地检查存储概要的名称,我们可以通过在ol$hints表上替换两个存储概要的名称来交换它们的提示,就像下面的SQL这样:

update outln.ol$hints
set ol_name =
	decode(
		ol_name,
			'SO_FIX','SYS_OUTLINE_020503165427311',
			'SYS_OUTLINE_020503165427311','SO_FIX'
	)
where ol_name in ('SYS_OUTLINE_020503165427311','SO_FIX')
;

侵入一个如此接近Oracle内核的内容,特别是在一个手册中给出注释,你可能会感觉到一点的不自在-但是这个更新操作实际上是经过Oracle官方认可的:参见Metalink Note: 92202.1 Dated 5th June 2000.然而,这个注释还有一点没有提及, 还需要做第二个更新操作来确保与每个存储概要相关的提示的数量保持一致.如果没有做这件事,你将发现部分存储概要将在export/import的时候被破坏或毁坏.

update outln.ol$ ol1
set hintcount = (
	select	hintcount
	from	ol$ ol2
	where	ol2.ol_name in ('SYS_OUTLINE_020503165427311',' SO_FIX')
	and	ol2.ol_name != ol1.ol_name
	)
where
	ol1.ol_name in ('SYS_OUTLINE_020503165427311','SO_FIX')
;

一旦交换操作完成, 你就可以登录一个新的会话,告诉Oracle开始使用存储概要,重新运行这个存储过程并退出;并再次使用sql_trace来检查Oracle具体如何操作这条SQL语句的.告诉Oracle使用这个(侵入)的存储概要的方法是执行以下命令:

alter session set use_stored_outline = demo;

通过检查跟踪稳健,你将发现这条SQL现在使用的是and_equal路径. (如果你使用tkprof来处理并explain这个跟踪稳健,你将发现输出结果显示了两个相互矛盾的路径. 第一条路径显示实际发生and_equal路径,这是正确的结果;第二条路径可能显示为一个全表扫描,因为存储概要在tkprof在针对跟踪到的SQL语句运行explain plan的时候可能不会被启用).

从开发环境到生产环境
到目前为止,我们已经创建好一个outline,我们还需要将其迁移到生产环境中去.还有很多关于存储概要的小功能可以帮助实现这一点. 例如,可以重命名这个存储概要,将其从开发环境导出,并导入到生产系统中,检查它是否能在生产环境的一个’test’类目中正常工作,接着将其迁移到生产类目.可用的命令如下:

alter outline SYS_OUTLINE_020503165427311 rename to AND_EQUAL_SAMPLE;
alter outline AND_EQUAL_SAMPLE change category to PROD_CAT;

为了处理将存储概要从开发环境导出并导入到生产系统,可以通过利用在exp的参数文件中添加一个where从句来实现,因为,我们可能会有一个如下的导出参数文件:

userid=outln/outln
tables=(ol$, ol$hints, ol$nodes)	# ol$nodes exists in v9 only
file=so.dmp
consistent=y				# very important
rows=yes
query='where ol_name  = ''AND_EQUAL_SAMPLE'''

Oracle9中的改进
当开始深入使用存储概要的时候, 还有很多细节需要注意,在Oracle8中,在使用存储概要可以做什么以及它如何工作方面有一些使人烦恼的充满限制的特性.幸运的是,很多这种问题在Oracle 9中都已经解决.

最琐碎也最明显的缺陷是,在Oracle 8中,只有当存储概要中的SQL语句与将要运行的语句完全匹配的时候才会被使用. 在Oracle 9中,有一个”标准化”的操作来缓解这个匹配需求;在进行比较之前,SQL文本会被转换成大写,并去掉其中的空白字符(一般包含空格,换行,回车以及tab 分隔符). 在不同的SQL语句之间存在微弱的差别的情况下,这个功能提高了使用同样的存储概要的机会.

当存储概要需要处理一个更加复杂的涉及到多个查询块的执行路径时,还有一些问题-在Oracle 9中这些也已经通过引入第三张表存在于outln schema的表ol$nodes解决掉了.它可以帮助Oracle突破ol$hints中的提示列表,并在输入的SQL的合适的子项之间实现交叉引用. 这当然是个好事情. 然而,这将给在两个存储概要之间交换提示这种策略带来一些负面效应, 因为ol$hints这张表还获取了文本长度与偏移量的多方面的细节.当升级到Oracle 9的时,有必要使用另外一种方法来大量生成存储概要,例如,包含经过手工特别处理的数据集的第二schema,或缺失的索引,或包含嵌入正在使用的提示的存储视图来代替文本中引用的表.

Oracle 9中引入的另外一个特性是,新增了大量的管理存储概要的支持,其中包含一个可以允许直接修改存储概要的包的初始版本.然而更重大的改进是,新增了一个选项,为在生产系统的操作执行计划提供了更好程度的安全性.虽然没有人喜欢在生产环境做实验,但有时生产环境是唯一的场所-包含合适的数据分布以及数据规模-来让你确定一段特定SQL语句的最优执行计划. 在Oracle 9中,可以创建一份outln表的虽有拷贝,并提取”公共”存储概要到里面以做”私有”的实验,而不会出现不小心将私有的存储概要对最终用户代码可见的风险.就我个人来讲,我会考虑将它作为杀手锏,但我也可以想象有时它可能成为一种必需. 在一个不那么危险的级别上,如果你有一个大规模的UAT或者测试系统,这就是一个可被用来独立测试的特性了.

限制
这篇文章给你提供了足够多的信息来开始测试存储概要;但是在你开始将这项技术应用到生产环境之前,必须明白以下几点.

首先,在Oracle8i中,outln的用户的默认密码是一个众所周知的密码,并且这个帐户有一些非常危险的权限.你必须修改这个帐户的密码.在oracle 9中,你将发现这个帐户的状态是locked.

其次,用来保存存储概要信息的表是创建在system表空间中的.对于一个生产系统来讲当你开始创建存储概要的时候你将发现你在system表空间中使用了大量的空间.将这些表迁移到一个属于它们自己的表空间,这是个好主意.很不幸,这其中有一个表包含一个long类型的列,因此你可能不得不使用exp/imp来将这些表移动到一个新的表空间.

最后,当使用存储概要对于解决致命的性能问题异常有效时,还会涉及到另外的代价.如果有存储概要被激活,针对每一个新解析的SQL语句Oracle都将检查是否存在一个相关的存储概要. 如果系统中有大量的SQL语句没有对应的存储概要,那么就需要权衡从少量的包含存储概要的SQL语句得到的好处是否能够抵消这部分额外代价.不过, 这是一个仅仅在存在更加严重的性能问题的系统才可能出现的问题.

结论.
存储概要可能会非常有用.当你无法通过修改源代码或者改变索引策略时,存储概要可能是仅有的可以让一个第三方应用有效运行的方法.
将这个想法推至极限,如果你仍然必须面对将一个系统从基于规则的优化方法到基于成本的优化方法的话,存储概要可能是是成本最低的并且无风险的选项.
如果要最大程度的受益于存储概要,那么Oracle 9提供了多项改进以允许支持更多类型的SQL语句,降低额外开销,并为你提供更大的灵活性来进行测试,操作以及安装存储概要.