在Oracle 9中伪造存储概要
目前还没有人发表评论 | 赶紧发表评论吧 | 作者：jametong
译者注: 本文翻译自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 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多出来一张表. 这些表为:
| ol$ | SQL语句 |
| ol$hints | 提示表 |
| ol$nodes | 查询块 |
第三张表是一张新表,被用来将提示列表与这条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
Oracle 8i/9i中的执行计划稳定性
目前还没有人发表评论 | 赶紧发表评论吧 | 作者：jametong
本文翻译自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按照我们想要的去做需要分三个阶段:
必须不断的停止/开始新的会话以确保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语句,降低额外开销,并为你提供更大的灵活性来进行测试,操作以及安装存储概要.
化整为零访问大表的三种方式
有4人发表了评论 | 赶紧发表评论吧 | 作者：奶妈来了
业务场景：
表xngul 大小大于 100G。
上面有(id)是number型自增字段，且是pk。
现在有需求要对这个表进行全表扫描，如果直接 select * from xngul, 则至少要半个小时，
而且一次性返回数据过多，应用程序无法处理。
所以想了办法化整为零，将这个表分段，分段读取。
有以下三种方式。
*******I.两个步骤，一个取分段的头尾，一个按头尾取分段内数据。*********
–取分段的头尾
select min(id) as min_id,
max(id) as max_id
from
(select /*+index (xngul xngul_pk)*/id
from xngul
where id > :last_max_id
order by id)
where rownum <= :page_size;
–按头尾取中间的数据
select *
from xngul
where id between :min_id and :max_id;
**********************************************************************
————————————————————————————-
**************II.一个步骤，完成分段和取数据。*************************
–在一个sql中完成分段和取数据
select /*+ordered use_nl(b)*/
b.*
from
(select id
from
(select /*+index (xngul xngul_pk)*/id
from xngul
where id > :last_max_id
order by id) a
where rownum <= :page_size) a, xngul b
where a.id=b.id;
**********************************************************************
————————————————————————————-
********III.借助一个表，实现多个进程并发处理。************************
–将分段数据记录到表中，并给每个段赋予一个 batch_id 和 batch_status
insert into batch
(batch_id, batch_status, ceiling_id, floor_id)
select
seq_batch.nextval as batch_id,
’not dealed’ as batch_status,
max(id) as ceiling_id,
min(id) as floor_id
from
(select /*+index (xngul xngul_pk)*/id
from xngul
where id > :last_max_id
order by id)
where rownum <= :page_size;
–多进程并发取未处理的batch_id
select batch_id, ceiling_id, floor_id
from batch for update nowait
where batch_status=’not dealed’
and rownum<=1;
–取该batch_id的明细数据
select *
from xngul
where id between :min_id and :max_id;
–处理完毕后，更新该batch_status
update batch
set batch_status=’has dealed’
where batch_id=:batch_id;
**********************************************************************
该方式还可以再扩展：
1，如果对数据实时性要求不高，可以在standby上按rowid来分段读取，效率会更高。
2，如果要做表连接，则可以对其中的大表做这个分段，分段的结果再来跟其他小表做连接，同样可以达到化整为零的目的。
Alibaba DBA 全家福
有5人发表了评论 | 赶紧发表评论吧 | 作者：grassbell
DBA团队汇集了来自全国各地志同道合的22名兄弟姐妹，我们有幸把兴趣变为了自己的工作，同时承担了阿里巴巴最核心的数据库维护的重任。
09年已经过去，我们经历了太多的酸甜苦辣，面对风雨，一起成长，有问题一起解决，互相帮助，没有猜疑，没有心机。
10年是个新的开始，把收获放入历史，把遗憾甚至痛 转换为动力，给自己勇气走出“舒适区”，努力突破自我！
AIX reserve_lock的问题
有3人发表了评论 | 赶紧发表评论吧 | 作者：八神
最近在AIX的reserve_lock上连续搞出了两个故障，教训是惨痛的，记录一下
我们有套双机AIX+Oracle 11G +ASM的环境,存储是两套EMC DMX3,一主一备,非RAC,正常情况下每台主机拉起自己的ASM DG,做成data guard,ASM DISK是直接基于/dev/hdiskpower的,没有使用AIX的VG,为了方便故障切换,我们让小机同时认到了主备存储的所有LUN,同时asm_diskstring也包含了两套存储所有的数据lun,在上线前,专门修改了PV的属性,reserve_lock=no
参考 http://chj733.spaces.live.com/blog/cns!C59D3364AD37617B!161.entry
上周光纤交换机调整,导致备机的hba通道出现问题,我们将备库的powerdisk删除了重认了一次，AIX里面默认认出的pv的reserve_lock=yes，这样两边的reserve_lock信息不一致了，主库是no，备库是yes，这个时候犯了个低级错误,头脑一热 ,在备机上做了次chdev -l hdiskpower reserve_lock=no(包括主库在用的lun)，马上主库的asm实例crash了,alert日志信息显示:device is busy,当时认为chdev是针对本机的ODM库的,不会修改盘头信息,后来IBM工程师解释,这个动作确实只修改ODM库,
但你备库的当前属性是reserve_lock=yes的,他会将磁盘资源lock住,而主库的是以reserve_lock=no打开的磁盘,自然就被抢了
第2次故障,种种原因,系统的状态又回到了原点,主库no,备库yes,这时不敢再修改reserve_lock了,但备库ASM还是要起来，standby还要继续使用的，在备库做完alter diskgroup dg2 mount后，主库开始出现大量的磁盘IO报警，很快DG就被强行dismount，数据库又挂了，不过这次ASM实例没挂，从理论上讲，ASM加载DG，会扫描asm_diskstring的所有磁盘，他是根据盘头信息来判断那些盘是要加载的，就算盘的顺序乱掉也没关系，为什么备库挂DG会把主库的DG搞当？后来线下做过测试，在YES的那边，做个简单的select * from v$asm_diskgroup也会把NO的那边弄挂，这个只能说明，ASM配reserve_lock=yes一起使用的时候，会对所有被检查的盘加锁，哪怕这个盘不属于你正要加载的DG,如果两边的lock属性不一致，就会存在抢断的现象，这个是比较危险的
对于这种共享磁盘的使用，建议：
保持两边的reserve_lock=no，严格控制主备磁盘不会重叠使用
出现HBA卡损坏，磁盘需要重认，最好先在powerpath中踢出磁盘，在OS上删除设备，尽量保持设备define，这样重认会重用现有的属性
可以考虑使用AIX VG,用lv作为asm disk，将控制权限交给AIX管理，这样是比较保险的，但估计很多人会觉得多包了层LVM不大爽
共享磁盘的lock属性,主要是为了控制多台主机对同一块磁盘设备的并发访问，每个厂商的设备基本都有这个设置，EMC的是reserve_lock，其他的也叫reserve_policy,大致意义是相同的，在访问这个设备的时候，会对设备打个标签，表示我已经持有了锁定，其他请求要访问时就会等待或失败，这个锁定的策略也是分好几种的
No Reserve reservation policy
这个表示不锁定
Exclusive Host Access single-path reservation policy
在san环境里面，一个LUN一般是由多条路径组成的，每条路径其实是个disk设备，他的Initiator就是光纤卡，
这些磁盘设备的锁定标志位就是以HBA卡来打的，所以HBA1打的标签，你通过HBA2是不能访问的，这个就叫single-path,
只有当HBA1失败后，切换到HBA2,标志位重新打过后，才能被访问
Persistent Reserve Exclusive Host Access reservation policy
我们上面说的hdiskpower应该就是这个策略，他是在HOST级别的，一般每个主机都会生成一个唯一的key，
标签在lun上打上后，后面只要是来自这个主机的请求，都可以访问这个lun，而其他主机由于生成的key不一样，
是没办法访问的
Oracle 11.1.0.7 ASMB进程存在内存泄露
目前还没有人发表评论 | 赶紧发表评论吧 | 作者：八神
今天在产品环境无意中通过topas发现，11G的asmb进程占用内存达到319M，对比了其他11G库的情况，都存在这个问题
ops$admin@omg>select PROGRAM,PGA_ALLOC_MEM / 1024 / 1024 as mb from v$process;
PROGRAM MB
———————– ———-
oracle@omg1 (ASMB) 319.256914
…
…
metalink上Doc ID: 6851110.8描述了这个问题，是BUG导致的内存泄露，使用11G R1 + ASM的同学可要注意了哈
这个系统是最近刚升级成11G的，运行了20天，算算下来每天泄露15M？有点恐怖了，所幸oracle提供了相关补丁，p6851110_111070_AIX5L.zip,后面找个时间应用上去看看
一个使用PC服务器的高可用性方案介绍
有9人发表了评论 | 赶紧发表评论吧 | 作者：osdba
以Intel Nehalem CPU的强劲性能和SSD盘的高iops为使用高性能pc服务器加SSD硬盘取代传统小型机加存储的方案成为可能。现在2颗4核的Intel Nelhalem cpu的性能已经达到或超过了一般的小型机的性能。单块SLC SSD硬盘的iops就可以达到10000以上，所以使用多块SSD硬盘的iops将超过或达到高端存储的IOPS的性能。
然而在pc服务器中缺乏与小型机系统上相应的成熟高可用方案，让大家对如何实现使用Intel Nehalem+SSD盘取带小型机成为困难，在这里我介绍一种目前在我们的一个应用场景中使用的一个高可用方案：
两台可插24块盘的宝德的PC服务器（Nelhalem CPU），可以混插SAS硬盘和SSD盘，我们这边大部分插了SSD盘，也插了少量SAS硬盘，混插的目的是数据库中有一些空间不是需要高IOPS的，如归档文件的空间，这样使用SAS硬盘既可以获得大空间，又可以减少成本（因为SSD盘还是比SAS硬盘贵很多），操作系统安装了Linux，数据库使用了oracle11g。
把两台机器的硬盘通过iscsi互相输出到对端机器上，iscsi是走的专用的网卡通道，然后在oracle asm中把本机的硬盘与另一台机器通过iscsi输过来的硬盘之间做镜像。正常工作时，数据库运行在一个节点上，为描述方便，本节点称为A机器，如果A机器的硬盘出现问题了，由于与另一台机器B机器的硬盘是在ASM中的做的镜像，所以数据库仍然能正常工作。如果A机器出问题了，则通过heartbeat做切换，数据库切换到B机器上运行，当A机器修好后，使用了oracle11g的快速同步功能，重新同步把A机器硬盘与B机器上硬盘进行同步，由于使用了oracle11g的快速同步功能，不需要做全量同步，只需要做一个增量同步就可以了，也就是说即使现在的数据库有2T大小，当A机器坏后到A机器修好后，数据库只发生了100G的变化量的话，重新同步只需要同步这100G的变化量的数据。这样就大大减少了恢复时间。
出现各种异常的解决办法：
假设数据库运行在A机器上：
- A机器的cpu、内存、主板坏，使用heartbeat做切换，数据库切换到B机器上运行。 等A机器修好了后，重新在asm中做磁盘同步就可以了。数据库不需要再切换回A机器运行。
- A机器操作系统出问题，使用heartbeat做切换，数据库切换到B机器上运行。
- A机器的硬盘坏，由于硬盘做了跨机器的镜像，同时Linux也支持在线换硬盘，可以在线换硬盘，换完后，再在asm中做磁盘同步。
- B机器坏，修好B机器后，在asm做快速增量同步。
11G数据库进程介绍
有2人发表了评论 | 赶紧发表评论吧 | 作者：vogts
最近DB升级到了11G，多了好多新的进程。这几天看了下，每个进程的作用。
oracle 29689 1 0 Oct29 ? 00:00:00 ora_q000_crmg
oracle 29691 1 0 Oct29 ? 00:00:14 ora_q001_crmg
queue monitor processes,可选的后台进程，主要是为流复制用的。最多能设置10个。
oracle 27720 1 0 Oct29 ? 00:02:35 ora_pmon_crmg
pmon进程
oracle 27740 1 0 Oct29 ? 00:34:14 ora_lgwr_crmg
写日志进程
oracle 27736 1 0 Oct29 ? 00:22:57 ora_dbw0_crmg
oracle 27738 1 0 Oct29 ? 00:02:43 ora_dbw1_crmg
Db writer进程
oracle 27742 1 0 Oct29 ? 00:06:23 ora_ckpt_crmg
CHECKPOINT进程
oracle 27744 1 0 Oct29 ? 00:00:58 ora_smon_crmg
smon进程
oracle 27746 1 0 Oct29 ? 00:00:00 ora_reco_crmg
Recoverer Process。这个进程主要处理分布式事务的，从rec0可以增长到recN。没有个数限制。
当和另外DB进行分布式事务的时候，就会自动增长。
oracle 27728 1 0 Oct29 ? 00:00:05 ora_dbrm_crmg
DATABASE RESOURCE manager。主要是做资源控制的，比如I/O,CPU。
我们设置了某个用户或某个session能使用多少CPU，等等，都是通过该进程来控制的。
oracle 27732 1 1 Oct29 ? 01:40:07 ora_dia0_crmg
DIA0 (diagnosability process 0) (only 0 is currently being used) is responsible for hang detection and deadlock resolution.
oracle 27726 1 0 Oct29 ? 00:00:00 ora_diag_crmg
DIAG (diagnosability) process performs diagnostic dumps and executes global oradebug commands.
oracle 28242 1 0 Oct29 ? 00:00:05 ora_fbda_crmg
flashback data archiver process。主要用于数据库回闪。保留前镜像，然后对数据进行归档用。
oracle 27734 1 0 Oct29 ? 00:00:00 ora_mman_crmg
MMAN is used for internal database tasks.
oracle 27748 1 0 Oct29 ? 00:03:58 ora_mmon_crmg
mmon进程
oracle 27730 1 0 Oct29 ? 00:00:01 ora_psp0_crmg
PSP0 (process spawner) spawns Oracle processes.
我的理解，主要是由它来产生其他processes。
oracle 29695 1 0 Oct29 ? 00:00:01 ora_smco_crmg
oracle 17037 1 0 11:04 ? 00:00:00 ora_w000_crmg
space management coordinator。主要用于空间管理，比如空间的收集，释放等等。Wnnn进程，也是通过该进程参数的。
oracle 27722 1 0 Oct29 ? 00:00:00 ora_vktm_crmg
virtual keeper of time。这个上次有篇文章说过，主要是11G的改进，以前是通过OS获取时间的，现在是通过ORACLE自己获得时间的。
每20MS重新刷一次。
oracle 27750 1 0 Oct29 ? 00:21:25 ora_mmnl_crmg
该进程主要负责性能相关数据分析，收集。比如 V$SESSION_WAIT_HISTORY 里的数据，都是通过该进程收集的。
