译者注: 本文翻译自Jonathan Lewis的文章Faking Stored Outlines in Oracle 9, 可以从此处下载原文的word版本: Stored Outlines in Oracle 9.
本文与前一篇Oracle 8i/9i中的执行计划稳定性是Jonathan Lewis先生写的关于stored outline具体使用以及其中可能涉及到的风险系列文章,也是我所见到的关于stored outline介绍的最详细的文档了. 关于stored outline还有以下相关资料可以对照阅读下:
Oracle Outlines - aka Plan Stability By Kerry Osborne
Plan stability in 10g - using existing cursors to create Stored Outlines and SQL profiles By Randolf Geist
Stored Outlines and Plan Stability By Tim Hall
Tuning Third-party Vendor Oracle Systems :Tuning when you can’t touch the code By Mark Ault

原文为在Oracle 9中伪造存储概要

在Oracle 9中伪造存储概要

在前面的文章中,我讨论到存储概要,并且描述了一种通过滥用系统来生成你所需要存储概要的方法.我同时也指出,在Oracle 9中使用这种方法存在一些风险,因为存储在数据库中的细节信息已经变得非常复杂.在接下来的文章中,我将介绍一种合法的操作存储概要的方法,这种方法可以应用在Oracle 8与Oracle 9中.这篇文章的细节都是基于实验得出的,实验环境是Oracle 8.1.7.0与Oracle 9.2.0.1的默认安装环境.

回顾

当你知道如何通过给一段DML语句添加提示就可以让它运行的快很多,但是你却没有访问源代码并将提示放到适当位置的途径, 你会怎么做?

在上一篇文章中,我展示了你可以如何用存储概要(也被称为执行计划稳定性)来驱使数据库引擎为你做这种工作.
一个存储概要由两个组件组成(宽泛地讲)-一个你希望控制的SQL语句,一组每当Oracle发现这条SQL被优化都将在它上面应用的提示.这两个组件都被保存在一个被称为outln的数据库schema中.

我们可以使用一组如图-1中类似的查询语句来检查保存在其中的SQL语句,以及附着在这条SQL语句上的提示.

select	name, used, sql_text
from	user_outlines
where	category = 'DEFAULT'
;

select	stage, node, hint
from	user_outline_hints
where	name = '{one of the names}'
;

Figure 1 Examining stored outlines.
在前面的文章中,我介绍了这样一种想法来欺骗系统, 使用合法的方法创建一个存储概要, 接着,使用一个文本相似的但已经添加过提示的语句来创建一个存储概要,最后,使用一组SQL语句来交换这两个存储概要的实际结果来修复存储概要.

当时,我曾提到这种方法对Oracle 8来讲或许是安全的,但是由于在新版本中引入的变化, 在Oracle 9中可能会导致问题.
这篇文章将对这些变化进行考查, 介绍一种合法的方法来得到你想要的一组存储到outln中的提示,用来解决你的那些问题语句.

相关变化

如果你登录到outln schema(在Oracle 9中它默认是锁住的)查看可用的表清单,你将发现Oracle 9比Oracle 8多出来一张表. 这些表为:

第三张表是一张新表,被用来将提示列表与这条SQL语句(一份内部重写的版本)的多个不同查询块.你还将发现,提示列表(ol$hints)也被加强了,其中还包括文本长度与偏移量的细节信息.
图2为这三张表的详细描述,用星号标注了Oracle 9中出现的新字段.

ol$

OL_NAME          VARCHAR2(30)
SQL_TEXT         LONG
TEXTLEN          NUMBER
SIGNATURE        RAW(16)
HASH_VALUE       NUMBER
HASH_VALUE2      NUMBER           ***
CATEGORY         VARCHAR2(30)
VERSION          VARCHAR2(64)
CREATOR          VARCHAR2(30)
TIMESTAMP        DATE
FLAGS            NUMBER
HINTCOUNT        NUMBER
SPARE1           NUMBER           ***
SPARE2           VARCHAR2(1000)   ***

Ol$hints

OL_NAME          VARCHAR2(30)
HINT#            NUMBER
CATEGORY         VARCHAR2(30)
HINT_TYPE        NUMBER
HINT_TEXT        VARCHAR2(512)
STAGE#           NUMBER
NODE#            NUMBER
TABLE_NAME       VARCHAR2(30)
TABLE_TIN        NUMBER
TABLE_POS        NUMBER
REF_ID           NUMBER           ***
USER_TABLE_NAME  VARCHAR2(64)     ***
COST             FLOAT(126)       ***
CARDINALITY      FLOAT(126)       ***
BYTES            FLOAT(126)       ***
HINT_TEXTOFF     NUMBER           ***
HINT_TEXTLEN     NUMBER           ***
JOIN_PRED        VARCHAR2(2000)   ***
SPARE1           NUMBER           ***
SPARE2           NUMBER           ***

ol$nodes  (completely new in 9)

OL_NAME          VARCHAR2(30)
CATEGORY         VARCHAR2(30)
NODE_ID          NUMBER
PARENT_ID        NUMBER
NODE_TYPE        NUMBER
NODE_TEXTLEN     NUMBER
NODE_TEXTOFF     NUMBER

Figure 2 The outln tables.
你可能很快会注意到多处细节-有大量信息被基于这些表的视图排除在外了.视图user_outline_hints的视图定义完全没有改变,尽管表ol$hints上新增加了10个字段.实际上,这个视图在Oracle 8的时候就极度不足,因为它遗漏了相当有用的hint#字段.
你还会注意到,Oracle 9现在有两个hash_value字段.如果你在Oracle 8与Oracle 9中对同样的SQL语句创建存储概要,你将发现它们拥有同样的hash_value,但是Oracle 9中对应的hash_value可能完全不同.
你可以也会发现,Oracle 9中的signature(签名)字段的值与Oracle 8中的值是不同的. 这是由于Oracle这两个版本之间策略上的最主要的调整就是为了提高存储概要的重复利用.在Oracle 8中,只有在你的SQL语句与存储的SQL语句完全匹配(包含空格符/大小写以及换行符)的时候才可以使用.到Oracle 9之后,这个限制放宽了,只要在去除掉重复的”空字符”并且将文本都转换成同样的大小写之后SQL语句能够匹配就可以使用存储概要了.例如,下面的两条SQL语句将使用同一个存储概要.

select * from t1 where id = 5;

SELECT  *
FROM	T1
WHERE   ID = 5;

策略上的这个调整导致了第一次创建这个执行计划的SQL语句的签名的调整;如果你的数据库从Oracle 8升级到Oracle 9,就必须更新存储概要或者必须确认它们不再被使用.(事实上,别名为dbms_outln包outln_pkg包含一个特别的存储过程update_signatures来处理这个问题.
不过,关于Oracle 9中这些表的最意义重大的事情却是对查询语句中涉及到的文本与对象的极度详细描述.创建图-3中显示的例子,并在继续阅读之前详细查看ol$hints表中的内容.

drop table t1;

create table t1
nologging
as
select
	rownum		id,
	rownum		n1,
	object_name,
	rpad('x',500)	padding
from
	all_objects
where
	rownum <= 100
;

alter table t1
add constraint t1_pk primary key (id);

create index t1_i1 on t1(n1);

analyze table t1 compute statistics;

create or replace outline demo_1 on
select	* from t1
where	id = 5
and	n1 = 10
;

Figure 3 Sample code.
这个例子立足于一个简单的小表,包含两组相近的列,其中一个列为逐渐(从而也创建了索引),另外包含一个简单的非唯一索引.我们为一个典型的查询创建一个存储概要来查看我们可以如何对待它.
如果针对由这个例子创建的存储概要demo_1运行图-1中的示例查询,我们将发现这个查询将附带6个提示.

  STAGE  NODE  HINT
	    3     1  NO_EXPAND
	    3     1  ORDERED
	    3     1  NO_FACT(T1)
	    3     1  INDEX(T1 T1_PK)
	    2     1  NOREWRITE
	    1     1  NOREWRITE

不出意外,其中的第四行显示我们将使用主键索引来访问这张表.如果我们实际上想要Oracle使用这个非唯一索引T1_I1访问表,我们该对存储概要做什么呢?理论上讲,我们可以调整这个存储概要以使得

	    3     1  INDEX(T1 T1_PK)

变成

	    3     1  INDEX(T1 T1_I1)

新特性

我们可以做的第一件事是查看包dbms_outln_edit.这个包在Oracle 9中引入,正如它的名字提示的那样,它的目标是编辑存储概要,这看上去令人充满希望.
然而,查看包的方法列表,检查文档手册,我们发现这个包只包含如下几个”编辑相关”的方法.

	CREATE_EDIT_TABLES
	DROP_EDIT_TABLES
	CHANGE_JOIN_POS

前两个方法允许我们创建或删除outln用户拥有的表的本地拷贝.第三个方法允许我们交换一个存储概要计划中的表连接顺序. 哪怕仅仅是帮助我们修改一个简单的提示的方法也是没有的.目前,这个包看上去实际上一无是处-但是它们注定会越来越完善.
当然B方案就是去侵入它了!如果我们登录到outln用户,并自己诊察ol$hints表(也就是支撑视图user_outline_hints的表)的内容,我们可以尝试下面的这个更新操作:

update ol$hints
set
	hint_text = 'INDEX(T1 T1_I1)'
where
	ol_name = 'demo_1'
and hint# = 4
;

登录回到我们的测试Schema,清空共享池,并且打开存储概要:

connect test_user/test
alter system flush shared_pool;
alter session set use_stored_outlines = true;

实际上,我们将发现侵入的存储概要确实如你所愿了.但是这是一个让人不爽的解决方案,
因为我们一直会给一个关于”更改数据字典表”的严厉的警告.

旧方法(1)

接着,我们的目标就是寻找一种迂回但又看似无害的方法来改变存储概要表的内容,并且不需要直接的侵入存储概要表.
从前(在Oracle 9以前),我们有多种实现办法,它们都是基于这样一个事实,存储概要的效果仅仅取决于进来的SQL语句的文本,而完全不关心对对象类型或者对象的所有者.
将表替换成添加过提示的视图是一种有效的方法.(我相信,这种方法最初是由Tom Kyte在它的《Expert One on One: Oracle》这本书中介绍的).
连接到另外一个拥有表T1的访问权限的Schema，按照下面的定义创建一个添加过提示的视图,视图与表的名称保持一致.

Create or replace view t1 as
Select /*+ index(t1,t1_i1) */
	*
from test_user.t1;

一旦视图创建完成,就在这个schema下使用下面的这个命令”重编译”这个已存在的存储概要.

	alter outline demo_1 rebuild;

注意:必须拥有权限alter any outline才可以执行这个命令.
如果登录回到原来的schema,清空缓存(flush shared pool),并且启用存储概要,我们将会发现原来的查询语句现在如愿以偿的使用上了索引T1_I1.

	    3      1  INDEX(T1 T1_I1)

这样为什么可行?因为存储概要并不属于任何一个schema. 当我们在另外一个schema中重编译这个称为demo_1的存储概要的时候,名称T1应用到了一个本地的包含提示的视图上了,因此Oracle将这个提示包装进了真实的执行计划中,从而也进入了这个存储概要.通过查看视图user_outline_hints,将会发现关键的那一行已经变成了
3 1 INDEX(T1 T1_I1)

很不幸,我们还将注意到它现在包含3行如下形式的提示:

	    2      1  NOREWRITE
	    1      2  NOREWRITE
	    1      1  NOREWRITE

而原来我们只有两行:

	    2     1  NOREWRITE
	    1     1  NOREWRITE

我们引入了一个新的提示,也就是”Stage 1,Node 2″.我不敢说我确切的知道这是什么意思,但是它一定与这样一个事实有关,为了在另外一个Schema解析优化这个查询,Oracle执行了一个额外的步骤来将视图引用转换成基础表的引用.
虽然目前这不会导致存储概要无法正确使用(或者如同它在这个简单的例子中这样),谁又能说Oracle在将来的版本又会有多挑剔呢.

旧方法(2)

因为视图引入了一个可能在将来版本变成错误的异常,我们不得不更加挑剔. 让我们试试下面的这种方法:

Create a new schema.
Create table T1 in that schema.
Create ONLY the index T1_I1.
Rebuild the outline in that schema

如果比较存储概要重建前后user_outline_hints的详细内容(必须重新登录到原来的Schema来做这件事),我们将发现除了我们想要改变的那一行,它们是完全一样的.重新登录回原来的Schema,通过清空共享池以及打开存储概要做一个常规检查,我们将会发现修改后的存储概要已经被使用了.

然而,还有一个潜在的威胁,不过这一次更加隐蔽.再回去看图-2中出现在Oracle 9中的新字段的定义-你认为字段user_table_name中保存的值将会是什么啊?它应该是有限制的表名称,例如:
{User_name}.{table_name}

在我们的例子中,这将告诉Oracle表T1实际上是一个属于新的Schema的表,而不是原来的Schema下面的表.即使Oracle确实在使用这个存储概要,这个表里的信息也充分说明Oracle是在错误的对象上面应用这个存储概要.
另外,它现在现在有效,但是为什么有这个信息在这儿呢-可能是为了将来的版本增强做准备呢.

可靠的赌注

看来,要生成存储概要,而又不面临将来的风险就只有一种方法了,那就是尽可能的真实.

在这个示例中,你需要删除主键索引,生成执行计划,然后替换掉主键.

当然,你可能不想在生产环境做这件事,即使你在生产环境做了,存储概要也有可能选择走全表扫描(而不是走你想要的那个索引).
底线是你必须至少在另一个数据库中有一个这个Schema的空闲拷贝,接着需要非常小心的操作这个拷贝以得到需要的存储概要.一旦得到这个存储概要,你就可以从一个数据库导出它并将其导入另外一个数据库.
例如:在这个空闲的数据库上,删除主键以避免PK唯一扫描就是可行的.如果Oracle并没有自动的采用另外一个索引,你可以对系统说各种谎言,诸如:

• 将optimizer_mode改成first_rows_1
• 构造数据使得列N1上的数据是唯一的.(不过,不要将其改成唯一索引,这样生成出来的存储概要将是unique scan而不是range scan了).
• 使用dbms_stats来使这个索引获得一个难以置信的clustering_factor.
• 调整参数optimiser_index_caching来告诉系统,这个索引已经完全被缓存.
• 调整optimiser_index_cost_adj来告诉系统,多块读要比单块读要慢100倍.
• 使用dbms_stats修改aux_stats$表来达到上一条同样的宣称效果,并且添加这样一个事实,一次多块读的典型大小为2个块.
• 重建这个索引以包含where从句中的所有字段.

给定存储概要表中的内容,假使表的所有者不变,对象类型不变以及不改变索引的唯一度,几乎任何事情都可以做. 如果你可以构造一个数据集与环境来生成一份与生产系统没有内部不一致的存储概要,那么你就可以以任何方式来欺骗系统.

结论
相对于Oracle 8来讲,在Oracle 9中进入存储概要的信息变更更加精细了.之前可以非常容易也很明显无风险的”调整”存储概要的方法,现在还仍然可以工作,但是Oracle 9中收集的巨量的附加信息表明,之前的那种方法现在可能会给将来留下隐患.

虽然Oracle 9中引入了一个编辑存储概要的包,但它当前还只是局限在交换表的连接顺序.除了使用第二套系统来调整索引(通过改变环境参数以及人造的统计信息)外, 看似不存在安全的干预存储概要的方法.

参考文献
Oracle 9i Release 2: Database Performance Tuning Guide and Reference - Chapter 7.
Oracle 9I Release 2: Supplied PL/SQL Packages and Types Reference - Chapters 41 - 42