在前面的文章中我讨论到存储概要并且描述了一种通过滥用系统来生成你所需要存储概要的方法我同时也指出在Oracle 中使用这种方法存在一些风险因为存储在数据库中的细节信息已经变得非常复杂在接下来的文章中我将介绍一种合法的操作存储概要的方法这种方法可以应用在Oracle 与Oracle 中这篇文章的细节都是基于实验得出的实验环境是Oracle 与Oracle 的默认安装环境
回顾
当你知道如何通过给一段DML语句添加提示就可以让它运行的快很多但是你却没有访问源代码并将提示放到适当位置的途径 你会怎么做?
在上一篇文章中我展示了你可以如何用存储概要(也被称为执行计划稳定性)来驱使数据库引擎为你做这种工作
一个存储概要由两个组件组成(宽泛地讲)一个你希望控制的SQL语句一组每当Oracle发现这条SQL被优化都将在它上面应用的提示这两个组件都被保存在一个被称为outln的数据库schema中
我们可以使用一组如图中类似的查询语句来检查保存在其中的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 Examining stored outlines
在前面的文章中我介绍了这样一种想法来欺骗系统 使用合法的方法创建一个存储概要 接着使用一个文本相似的但已经添加过提示的语句来创建一个存储概要最后使用一组SQL语句来交换这两个存储概要的实际结果来修复存储概要
当时我曾提到这种方法对Oracle 来讲或许是安全的但是由于在新版本中引入的变化 在Oracle 中可能会导致问题
这篇文章将对这些变化进行考查 介绍一种合法的方法来得到你想要的一组存储到outln中的提示用来解决你的那些问题语句
相关变化
如果你登录到outln schema(在Oracle 中它默认是锁住的)查看可用的表清单你将发现Oracle 比Oracle 多出来一张表 这些表为:
ol$ SQL语句
ol$hints 提示表
ol$nodes 查询块
第三张表是一张新表被用来将提示列表与这条SQL语句(一份内部重写的版本)的多个不同查询块你还将发现提示列表(ol$hints)也被加强了其中还包括文本长度与偏移量的细节信息
图为这三张表的详细描述用星号标注了Oracle 中出现的新字段
ol$
OL_NAME VARCHAR()
SQL_TEXT LONG
TEXTLEN NUMBER
SIGNATURE RAW()
HASH_VALUE NUMBER
HASH_VALUE NUMBER ***
CATEGORY VARCHAR()
VERSION VARCHAR()
CREATOR VARCHAR()
TIMESTAMP DATE
FLAGS NUMBER
HINTCOUNT NUMBER
SPARE NUMBER ***
SPARE VARCHAR() ***
Ol$hints
OL_NAME VARCHAR()
HINT# NUMBER
CATEGORY VARCHAR()
HINT_TYPE NUMBER
HINT_TEXT VARCHAR()
STAGE# NUMBER
NODE# NUMBER
TABLE_NAME VARCHAR()
TABLE_TIN NUMBER
TABLE_POS NUMBER
REF_ID NUMBER ***
USER_TABLE_NAME VARCHAR() ***
COST FLOAT() ***
CARDINALITY FLOAT() ***
BYTES FLOAT() ***
HINT_TEXTOFF NUMBER ***
HINT_TEXTLEN NUMBER ***
JOIN_PRED VARCHAR() ***
SPARE NUMBER ***
SPARE NUMBER ***
ol$nodes (completely new in )
OL_NAME VARCHAR()
CATEGORY VARCHAR()
NODE_ID NUMBER
PARENT_ID NUMBER
NODE_TYPE NUMBER
NODE_TEXTLEN NUMBER
NODE_TEXTOFF NUMBER
Figure The outln tables
你可能很快会注意到多处细节有大量信息被基于这些表的视图排除在外了视图user_outline_hints的视图定义完全没有改变尽管表ol$hints上新增加了个字段实际上这个视图在Oracle 的时候就极度不足因为它遗漏了相当有用的hint#字段
你还会注意到Oracle 现在有两个hash_value字段如果你在Oracle 与Oracle 中对同样的SQL语句创建存储概要你将发现它们拥有同样的hash_value但是Oracle 中对应的hash_value可能完全不同
你可以也会发现Oracle 中的signature(签名)字段的值与Oracle 中的值是不同的 这是由于Oracle这两个版本之间策略上的最主要的调整就是为了提高存储概要的重复利用在Oracle 中只有在你的SQL语句与存储的SQL语句完全匹配(包含空格符/大小写以及换行符)的时候才可以使用到Oracle 之后这个限制放宽了只要在去除掉重复的空字符并且将文本都转换成同样的大小写之后SQL语句能够匹配就可以使用存储概要了例如下面的两条SQL语句将使用同一个存储概要
select * from t where id = ;
SELECT *
FROM T
WHERE ID = ;
策略上的这个调整导致了第一次创建这个执行计划的SQL语句的签名的调整;如果你的数据库从Oracle 升级到Oracle 就必须更新存储概要或者必须确认它们不再被使用(事实上别名为dbms_outln包outln_pkg包含一个特别的存储过程update_signatures来处理这个问题
不过关于Oracle 中这些表的最意义重大的事情却是对查询语句中涉及到的文本与对象的极度详细描述创建图中显示的例子并在继续阅读之前详细查看ol$hints表中的内容
drop table t;
create table t
nologging
as
select
rownum id
rownum n
object_name
rpad(x) padding
from
all_objects
where
rownum <=
;
alter table t
add constraint t_pk primary key (id);
create index t_i on t(n);
analyze table t compute statistics;
create or replace outline demo_ on
select * from t
where id =
and n =
;
Figure Sample code
这个例子立足于一个简单的小表包含两组相近的列其中一个列为逐渐(从而也创建了索引)另外包含一个简单的非唯一索引我们为一个典型的查询创建一个存储概要来查看我们可以如何对待它
如果针对由这个例子创建的存储概要demo_运行图中的示例查询我们将发现这个查询将附带个提示
STAGE NODE HINT
NO_EXPAND
ORDERED
NO_FACT(T)
INDEX(T T_PK)
NOREWRITE
NOREWRITE
不出意外其中的第四行显示我们将使用主键索引来访问这张表如果我们实际上想要Oracle使用这个非唯一索引T_I访问表我们该对存储概要做什么呢?理论上讲我们可以调整这个存储概要以使得
INDEX(T T_PK)
变成
INDEX(T T_I)
新特性
我们可以做的第一件事是查看包dbms_outln_edit这个包在Oracle 中引入正如它的名字提示的那样它的目标是编辑存储概要这看上去令人充满希望
然而查看包的方法列表检查文档手册我们发现这个包只包含如下几个编辑相关的方法
CREATE_EDIT_TABLES
DROP_EDIT_TABLES
CHANGE_JOIN_POS
前两个方法允许我们创建或删除outln用户拥有的表的本地拷贝第三个方法允许我们交换一个存储概要计划中的表连接顺序 哪怕仅仅是帮助我们修改一个简单的提示的方法也是没有的目前这个包看上去实际上一无是处但是它们注定会越来越完善
当然B方案就是去侵入它了!如果我们登录到outln用户并自己诊察ol$hints表(也就是支撑视图user_outline_hints的表)的内容我们可以尝试下面的这个更新操作:
update ol$hints
set
hint_text = INDEX(T T_I)
where
ol_name = demo_
and hint# =
;
登录回到我们的测试Schema清空共享池并且打开存储概要:
connect test_user/test
alter system flush shared_pool;
alter session set use_stored_outlines = true;
实际上我们将发现侵入的存储概要确实如你所愿了但是这是一个让人不爽的解决方案
因为我们一直会给一个关于更改数据字典表的严厉的警告
旧方法()
接着我们的目标就是寻找一种迂回但又看似无害的方法来改变存储概要表的内容并且不需要直接的侵入存储概要表
从前(在Oracle 以前)我们有多种实现办法它们都是基于这样一个事实存储概要的效果仅仅取决于进来的SQL语句的文本而完全不关心对对象类型或者对象的所有者
将表替换成添加过提示的视图是一种有效的方法(我相信这种方法最初是由Tom Kyte在它的《Expert One on One: Oracle》这本书中介绍的)
连接到另外一个拥有表T的访问权限的Schema按照下面的定义创建一个添加过提示的视图视图与表的名称保持一致
Create or replace view t as
Select /*+ index(tt_i) */
*
from test_usert;
一旦视图创建完成就在这个schema下使用下面的这个命令重编译这个已存在的存储概要
alter outline demo_ rebuild;
注意:必须拥有权限alter any outline才可以执行这个命令
如果登录回到原来的schema清空缓存(flush shared pool)并且启用存储概要我们将会发现原来的查询语句现在如愿以偿的使用上了索引T_I
INDEX(T T_I)
这样为什么可行?因为存储概要并不属于任何一个schema 当我们在另外一个schema中重编译这个称为demo_的存储概要的时候名称T应用到了一个本地的包含提示的视图上了因此Oracle将这个提示包装进了真实的执行计划中从而也进入了这个存储概要通过查看视图user_outline_hints将会发现关键的那一行已经变成了
INDEX(T T_I)
很不幸我们还将注意到它现在包含行如下形式的提示:
NOREWRITE
NOREWRITE
NOREWRITE
而原来我们只有两行:
NOREWRITE
NOREWRITE
我们引入了一个新的提示也就是Stage Node 我不敢说我确切的知道这是什么意思但是它一定与这样一个事实有关为了在另外一个Schema解析优化这个查询Oracle执行了一个额外的步骤来将视图引用转换成基础表的引用
虽然目前这不会导致存储概要无法正确使用(或者如同它在这个简单的例子中这样)谁又能说Oracle在将来的版本又会有多挑剔呢
旧方法()
因为视图引入了一个可能在将来版本变成错误的异常我们不得不更加挑剔 让我们试试下面的这种方法:
Create a new schema
Create table T in that schema
Create ONLY the index T_I
Rebuild the outline in that schema
如果比较存储概要重建前后user_outline_hints的详细内容(必须重新登录到原来的Schema来做这件事)我们将发现除了我们想要改变的那一行它们是完全一样的重新登录回原来的Schema通过清空共享池以及打开存储概要做一个常规检查我们将会发现修改后的存储概要已经被使用了
然而还有一个潜在的威胁不过这一次更加隐蔽再回去看图中出现在Oracle 中的新字段的定义你认为字段user_table_name中保存的值将会是什么啊?它应该是有限制的表名称例如:
{User_name}{table_name}
在我们的例子中这将告诉Oracle表T实际上是一个属于新的Schema的表而不是原来的Schema下面的表即使Oracle确实在使用这个存储概要这个表里的信息也充分说明Oracle是在错误的对象上面应用这个存储概要
另外它现在现在有效但是为什么有这个信息在这儿呢可能是为了将来的版本增强做准备呢
可靠的赌注
看来要生成存储概要而又不面临将来的风险就只有一种方法了那就是尽可能的真实
在这个示例中你需要删除主键索引生成执行计划然后替换掉主键
当然你可能不想在生产环境做这件事即使你在生产环境做了存储概要也有可能选择走全表扫描(而不是走你想要的那个索引)
底线是你必须至少在另一个数据库中有一个这个Schema的空闲拷贝接着需要非常小心的操作这个拷贝以得到需要的存储概要一旦得到这个存储概要你就可以从一个数据库导出它并将其导入另外一个数据库
例如:在这个空闲的数据库上删除主键以避免PK唯一扫描就是可行的如果Oracle并没有自动的采用另外一个索引你可以对系统说各种谎言诸如:
将optimizer_mode改成first_rows_
构造数据使得列N上的数据是唯一的(不过不要将其改成唯一索引这样生成出来的存储概要将是unique scan而不是range scan了)
使用dbms_stats来使这个索引获得一个难以置信的clustering_factor
调整参数optimiser_index_caching来告诉系统这个索引已经完全被缓存
调整optimiser_index_cost_adj来告诉系统多块读要比单块读要慢倍
使用dbms_stats修改aux_stats$表来达到上一条同样的宣称效果并且添加这样一个事实一次多块读的典型大小为个块
重建这个索引以包含where从句中的所有字段
给定存储概要表中的内容假使表的所有者不变对象类型不变以及不改变索引的唯一度几乎任何事情都可以做 如果你可以构造一个数据集与环境来生成一份与生产系统没有内部不一致的存储概要那么你就可以以任何方式来欺骗系统
结论
相对于Oracle 来讲在Oracle 中进入存储概要的信息变更更加精细了之前可以非常容易也很明显无风险的调整存储概要的方法现在还仍然可以工作但是Oracle 中收集的巨量的附加信息表明之前的那种方法现在可能会给将来留下隐患
虽然Oracle 中引入了一个编辑存储概要的包但它当前还只是局限在交换表的连接顺序除了使用第二套系统来调整索引(通过改变环境参数以及人造的统计信息)外 看似不存在安全的干预存储概要的方法
