让Oracle跑得更快 - Oracle10g性能分析与优化思路

babyboy147

贡献于2011-12-13

字数:0 关键词: Oracle 数据库服务器

1 / 90 2 / 90 第1章 引起数据库性能问题的因素 1.1 软件设计对数据库的影响 1.1.1 软件架构设计对数据库性能的影响 软件系统的架构对数据库的影响是非常直接的。例如一套系统,如果并发数非常大,比如 是超过 3000 个并发,通常这种情况下,我们会考虑采用一套软件来搭建一个中间层,这就是 通常讲到的 3 层或是多层结构。使用这一套软件的目的是用来构建一个缓冲池,在数据库之 前对大量的并发进行处理,以便于每次只有少数的用户连接到数据库中,其他的用户在缓冲 池的队列中等待。当然,这只是一个动态的过程,程序会尽可能快地去响应所有用户的请 求,这种提前对大量并发用户进行处理的方式,会比让这 3000 个用户直接连接到数据库中效 果要好得多,同时数据库也可以使用更多的资源来处理用户的操作请求而不是去开 3000 个进 程来处理每个用户的请求,这个开销是非常大的。所以对于大量并发的系统来讲,在数据库 之前建一个缓冲用户请求的中间件服务,显得至关重要。同时,很多这种中间件软件还提供 了负载均衡的功能。 当然,Oracle 数据库自身也提供了一种 MTS 的技术,作用和这种中间件服务是一样的, 但目前看来,采用商业中间件软件或是开发商自己开发一套中间件服务的做法更多一些(参 见图 1-1)。 图 1-1 多层架构体系 1.1.2 软件代码的编写对数据库性能的影响 软件代码对数据库的影响,通常指的是应用程序代码中对数据库操作的代码部分对数据库 产生的影响。具体来讲就是 SQL语句或是 PL/SQL包。SQL语句造成的影响,一种是 SQL语 句本身在逻辑上就是效率低下的,另一种就是 SQL语句没有绑定变量。 性能低下的 SQL语句,比如使用 Hint,不合适的外连接,谓词的隐含转换,优化器的选 择等,会对 SQL的执行产生非常大的影响,特别是多表关联的情况下,影响更是显著。它主 要体现为 SQL语句的执行受到了人为的约束,比如数据的访问方式(索引还是全表扫描), 以及表关联方式的选择上(Hah Join,Nested Loops)。 1.1.2.1 人为地在 SQL代码中加入 Hint 来约束 SQL的执行计划 我曾经遇到的一个例子就是这样,开发人员为了要求每次对一个表做操作的时候都使用索 引,于是在代码中强行加入了 Hint 约束 SQL的执行计划,它的样子大概是这样: 3 / 90 Select /*+ index(t1 ind_t1) */ col1,col2 from t1 where col1>...... and col1 <...... 我猜测他们在系统上线之前测试的阶段,发现这条 SQL选择索引比全表扫描效率高得 多,为了保证以后执行计划能够始终选择索引,他们在代码中的 SQL里加入了这个 Hint。 系统上线后,没有出现过问题,直到有一天用户抱怨查询非常慢,我从数据库里得到了用 户端发出的 SQL,才知道这个 SQL在代码里加入了 Hint。问题是,为什么之前操作都没有问 题呢?我登录这个数据库,查看了一下这个表的信息,惊奇地发现,这个表每天仍然定时在 做分析操作,这是一个奇怪的现象,人为地对表进行定时分析,却不允许数据库来选择执行 计划,这显然是不合理的事情。但这种现象在开发人员当中又是比较普遍的,大家了解一些 数据库的技术,却无法将这些知识整合起来运用,系统设计及开发阶段,没有 DBA 参与进 来,直到系统进入运行维护阶段,才有 DBA 来充当救火队员的角色,这在当前中国软件开发 中是一个很普遍的现象。 接着说这个案例。这是一个 Oracle 10gr2的数据库,CBO(基于成本的优化器)的技术已 经比较成熟,所以此时应该选择由 Oracle 数据库来决定 SQL的执行计划。我分别执行了这条 原始 SQL和去掉了 Hint 的 SQL,并获得了各自的执行计划,执行计划显示出,去掉 Hint 的 S QL 选择了全表扫描(Full Table Scan),执行中扫过的数据块远远小于通过索引访问数据的 S QL,于是原因找到了。 可是为什么之前没有出现过这个问题?我对比了一下最近的数据和之前的数据,发现近期 的数据在创建索引的列上的列值重复率要远远高于从前,因此 Oracle 在选择索引之后,比以 前读取了更多的索引块和数据块,造成了大量的 I/O 操作。 因此,对于高版本的数据库(10g 以上),我们还是应该让数据库自己根据表、索引的统 计分析信息来决定 SQL的执行计划,因为表中的数据是会变化的,这种人为的强行干预,必 然会在某个时候出现问题。 1.1.2.2 不必要的外连接操作 外连接是一个代价非常昂贵的执行过程。如果业务需要,这种操作是必要的,但是有时候 会出现人为地在 SQL中使用不必要的外连接的情形,这实际上是因为有的开发人员担心遗漏 一些数据而刻意使用它,这就非常有可能留下隐患,让数据库选择昂贵的执行计划而不是最 优的那一个。 下面的这个例子说明了一个不必要的外连接使用。 我们创建两个简单的表,并插入一些数据,同时给其中的一个表 T2的 C列上插入一些空 值: SQL> create table t1 as select rownum a,rownum+100 b from dba_users where rownum< 10; 表已创建。 SQL> create table t2 as select decode(mod(rownum,2),0,rownum) c,rownum+1000 d fro m dba_users where rownum<10; 表已创建。 4 / 90 SQL> select * from t1; A B ---------- ---------- 1 101 2 102 3 103 4 104 5 105 6 106 7 107 8 108 9 109 已选择 9 行。 SQL> select * from t2; C D ---------- ---------- 1001 2 1002 1003 4 1004 1005 6 1006 1007 8 1008 1009 已选择 9 行。 通过下面这条语句,通过使用 A 字段和 T2表 C字段关联,我们获取了 T1表上所有的 行,以及 T2表上符合条件的行: SQL> select a,b,c,d from t1,t2 where t1.a=t2.c(+) ; A B C D ---------- ---------- ---------- ---------- 2 102 2 1002 4 104 4 1004 6 106 6 1006 8 108 8 1008 1 101 3 103 5 105 7 107 5 / 90 9 109 请看下面这条 SQL,它是什么意思呢? SQL> select a,b,c,d from t1,t2 where t1.a=t2.c(+) and t2.d>1000; A B C D ---------- ---------- ---------- ---------- 2 102 2 1002 4 104 4 1004 6 106 6 1006 8 108 8 1008 这条 SQL的意思是告诉数据库,我要得到 T1表上所有的行,并且用 A 列和 T2表 C做关 联,同时要求 T2表 C列的值大于 1000。 让我们再看看另一条结果集完全一样的 SQL: SQL> select a,b,c,d from t1,t2 where t1.a=t2.c and t2.d>1000; A B C D ---------- ---------- ---------- ---------- 2 102 2 1002 4 104 4 1004 6 106 6 1006 8 108 8 1008 从结果集上来看,这是两条等价的 SQL语句,就是说,在这种情况下,外连接其实是没 有用的,是人为地在 SQL里设定的限制!如果仔细看一下第一条语句,我们不难发现,条件 中 T2.C>1000已经明确地指出,在结果集中,T2表在任何一行,C列都应该有值的,也就是 在这种情况下,根本就不需要使用外连接,业务逻辑上讲,外连接在这里是多余的。这种情 况在开发人员的代码中有时候会遇到,对他们来讲,只要保证结果集正确就好,但对数据库 来讲,在执行时可能会引起极大的性能差别。 1.1.2.3 CBO 下优化器模式的选择 通常对于一种功能单一的数据库来讲,在实例级设置一个优化器模式就可以了,比如对于 OLAP 系统,绝大多数时候数据库上运行着的是报表作业,执行基本上是聚合类的 SQL操 作,比如 GROUP BY,这时候,把优化器模式设置成 all_rows 是恰当的。 而对于一些分页操作比较多的网站类数据库,设置成 first_rows 会更好一些。 我却遇到了另外的一件事情。我们的数据库上运行着的基本上是一个 OLAP 系统,所以优 化器模式设置为 ALL_ROWS,这有利于报表 SQL的快速完成。但数据库上还运行着一些用户 查询的业务,查询的方式可以说成是分页的。有时候就会出现用户抱怨查询慢的问题,尽管 我知道问题所在,却比较难解决,因为这些 SQL已经被开发人员写到代码里面了。 针对这种情况,如果能在开发阶段就考虑到这个问题,针对需要分页操作的 SQL,开发人 员在 SQL里通过 Hint 的方式来将优化模式转换成 FIRST_ROWS,这样就可以大大地提高数据 的处理速度。 比如这样一个每次取出 10 条记录的分页查询: 6 / 90 Select * from (SELECT /*+ first_rows(10) */ a.*,rownum rnum from (SELECT /*+ first_rows(10) */ id,name from t1 order by id) a Where rownum<=10) Where rnum>=1; 可以在每个子查询中重复使用 FIRST_ROWS(n)来提高查询效率。 尽管说在 SQL中人为地加入 Hint 操作不是一个好主意,但是有些时候,比如需要兼顾其 他的用户操作时,可以考虑做这样的设定。但前期需要做一些测试工作,以确保这样的设定 能够带来性能上的提高,同时不会对数据库造成其他方面的影响。这是系统设计阶段应该仔 细考虑好的一个问题。 1.1.2.4 没有绑定变量的 SQL 对于这个话题,其实很多人存在着一个误区。记得有一次在广州出差,我和一个同样做数 据库的同事,有这样一段对话: 同事:“我们的系统有没有绑定变量?” 我:“不知道.....” 同事:“我发现没有绑定。” 他的表情很凝重,仿佛发现了数据库的一个致命隐患一般。 我:“无所谓吧?” 他立即反驳我说:“谁说无所谓,SQL 没有绑定变量,数据库每次执行就会发生硬分析 (Hard parse,喜欢读 Statspack Report 的朋友对这个词应该很敏感吧,我的同事就是一个 Statspack fans,那时候他正在研究 Statspack,觉得如果硬分析太多了,天就要塌下来,仿 佛把这些硬分析变为软分析之后,数据库性能会提高成百上千倍一样),这样性能肯定会大 受影响,有时候用户反映查询慢,会不会是这个原因导致的?” 我说不是这个原因导致的,我可以保证,因为我们是这样的一个系统:数据库的用户连 接数很少,大概不会超过 50 个,每个用户每天发出的查询操作不会超过 50 个,这对于一个 运行在内存 8 个 GB,10 几个 CPU 的系统上的数据库来说,硬分析对数据库性能的影响微乎其 微,完全可以忽略掉,因为我们是一个 OLAP 系统。 他想了一下,认同了我的观点。 我想说的绑定变量的误区就和上面这个案例一样,有时候它对性能的影响被夸大化了。我 在 ITPUB上总看到很多这样的帖子,大家在谈及 SQL时必定要求绑定变量,仿佛不这样系统 就要出问题了一样。实际上,至少对于 OLAP 系统(在线分析系统,通常指的是这样的一个 系统,数据库存放着海量的数据,连接的用户少,SQL语句基本上都是用户产生报表的大查 询)来说,未绑定变量对数据库的影响是很有限的,甚至是完全没有必要的,因为只有少量 的用户和少量的 SQL操作,数据库不需要花多少资源在 SQL分析上面。这个话题我们会在后 续的章节中讨论到。 绑定变量的真正用途是在一个 OLTP 系统中,这个系统通常有这样的特点,用户并发数很 大,用户的请求十分密集,并且这些请求的 SQL大多数是可以重复使用的,我们试想,当这 些成千上万的 SQL被数据库一遍又一遍地进行语法分析、语义分析,生成执行计划时,这对 数据库的压力该有多大?如果一条 SQL执行一遍之后就被缓存到数据库的内存当中(实际上 7 / 90 是在共享池里),以后的成百上千的用户请求都使用这个 SQL解析后的结果,那效率将有多 么大的提高! 所以,我的观点是,当你要考察绑定变量对你的数据库的影响有多大时,先确定你的系统 是 OLTP 系统或是 OLAP 系统;当然,现在很多数据库同时担负这两种角色,那么你需要分 析数据库的性能情况,比如,做一个 Statspack Report 来帮助你确定变量是否绑定,以及是否 已经对系统的性能构成严重的影响。 1.1.2.5 PL/SQL包 如果你的程序里面有 PL/SQL 包,请考虑使用存储过程来代替它,存储过程是经过成功编译后存放在 数据库中的代码,执行起来的效率要比程序代码中 PL/SQL 包的效率高很多,因为它不再需要做语法和语 义的分析(语法的分析指的是数据库对代码进行检查,看它是否存在语法上的错误;而语义分析是查看语 句执行的对象是否存在,比如需要操作的表、列等,以及是否有执行这些操作的权限)。 1.2 数据库的设计 数据库的设计在系统设计当中是一个非常重要的环节,但目前看来,很多开发商忽略了它应 有的重要性,大多数的数据库设计基本上等同于创建业务所需要的所有对象,仅此而已。这 是作者从事了 10 年 DBA 工作的切身体会,也许这也不能全怪开发商,比如他们有工期的压 力,有人员成本的压力,那应该是另外一个话题。 对于数据库的设计,我认为除了一些必需的对象创建之外,应该还要更多地考虑在整个系统 运行的生命周期中,按照系统的实际情况及可能的变化做一些前瞻性的设计,以基本满足系 统生命周期里的各方面需求,不至于发生大的修改或是升级。 说起系统的升级,这是一个有趣的话题,可能很多开发人员,特别是做项目开发的人员,应 该会深有体会。比如我身边的一些案例,明明是最初设计上存在着缺陷或者疏漏,导致后来 系统出了问题,却成了开发商项目的二期、三期的理由,也成为软件 1.0 版、2.0 版的理由。 基本上看来,前期数据库设计的一个根基就是要弄清楚数据库的类型。通常来说,我们把业 务分为两类,在线事务处理系统(OLTP)和在线分析系统(OLAP)或者 DSS(决策支持系 统),这两类系统在数据库的设计上是如此不同,甚至有些地方的设计是貌似相悖的。比如 OLTP 系统强调数据库的内存效率,强调内存各种指标的命中率,强调绑定变量,强调并发操 作;而 OLAP 系统则强调数据分析,强调 SQL 执行时长,强调磁盘 I/O,强调分区等。因为这 些区别,在数据库设计的阶段,弄清楚数据库类型是至关重要的,只有在这个前提之下,才 能够讨论数据库的具体设计,否则设计必然是盲目的,“皮之不存毛将焉附”。 1.2.1 OLTP 数据库 OLAP 和 OLTP 是两类完全不同的系统,对数据库的要求也截然不同。通常来讲,OLTP(在线事 务处理系统)的用户并发数都很多,但他们只对数据库做很小的操作,数据库侧重于对用户 8 / 90 操作的快速响应,这是对数据库最重要的性能要求。我清楚地记得在 2008 年的时候,某个门 票在线销售系统允许人们通过网络购买门票,这是一个典型的 OLTP 系统。我当时还想尝试去 买一张,结果是还没等到我去买,就听说系统瘫痪了。我想,应该是在线购票的用户数太多 吧,导致数据库(我不太确定,也可能是中间件系统)没有办法处理大量的连接,从而导致 了系统崩溃。这真是一个惨痛的教训,它用事实告诉我们,对于一个系统,特别是非常重要 的系统,一些前瞻性的预测和系统的压力测试有多么的重要。 对于一个 OLTP 系统来说,数据库内存设计显得很重要,如果数据都可以在内存中处理,那么 数据库的性能无疑会提高很多。我知道有些对处理速度要求很高的系统,已经采用了一些内 存数据库,比如 Oracle 的 Times Ten。 内存的设计通常是通过调整 Oracle 和内存相关的初始化参数来实现的,比较重要的几个是内 存相关的参数,包括 SGA 的大小(Data Buffer,Shared Pool)、PGA 大小(排序区,Hash 区等)等,这些参数在一个 OLTP 系统里显得至关重要,OLTP 系统是一个数据块变化非常频 繁、SQL 语句提交非常频繁的系统。对于数据块来说,应尽可能让数据块保存在内存当中,对 于 SQL 来说,尽可能使用变量绑定技术来达到 SQL 的重用,减少物理 I/O 和重复的 SQL 解 析,能极大地改善数据库的性能。 关于一些初始化参数的设定的问题,我认为,这里绝没有一个确定的标准,这和每个数据库 上运行的业务直接相关,能够确定这些参数值的唯一方法就是测试,先给这些参数设定一个 经验值,然后通过搭建测试环境对数据库进行测试,通过一些性能报告(比如 AWR 或者 Staspack 报告)作为依据,不断地调整这些参数值,以达到最佳的性能。 除了内存、没有绑定变量的 SQL 会对 OLTP 数据库造成极大的性能影响之外,还有一些因素也 会导致数据库的性能下降,比如热块(hot block)的问题,当一个块被多个用户同时读取的 时候,Oracle 为了维护数据的一致性,需要使用一种称为 Latch 的东西来串行化用户的操 作。当一个用户获得了这个 Latch 后,其他的用户就只能被迫等待。获取这个数据块的用户 越多,等待就越明显,就造成了这种热块问题。这种热块可能是数据块,也可能是回滚段 块。对于数据块来讲,通常是数据块上的数据分布不均匀导致,如果是索引的数据块,可以 考虑创建反向索引来达到重新分布数据的目的,对于回滚段数据块,可以适当多增加几个回 滚段来避免这种争用(热块部分在后面有专门的章节讨论)。 1.2.2 OLAP 数据库 我一直认为 OLAP 数据库在内存上可优化的余地很小,甚至觉得增加 CPU 处理速度和磁盘 I/O 速度是最直接的提高数据库性能的方式,但这将意味着系统成本的增加。实际上,用户对 OLAP 系统性能的期望远远没有对 OLTP 性能的期望那么高。 内存的优化,对 OLAP 来讲影响很小,比如我曾经遇到的一个数据库,每天晚上运行的报表程 序,基本上都是对几亿条或者几十亿条数据进行聚合处理,这种海量的数据,全部在内存中 操作是很难的,同时也完全没有必要,因为这些数据块很少重用,缓存起来没有实际意义, 倒是物理 I/O 相当大,这种系统的瓶颈往往是在磁盘 I/O 上面。 对于 OLAP 系统,SQL 的优化显得非常重要,试想,如果一张表中只有几千条数据,无论执行 全表扫描或是使用索引,对我们来说差异都很小,几乎感觉不出来,但是当数据量提升到几 9 / 90 亿或者几十亿甚至更多的时候,全表扫描、索引可能导致极大的性能差异,因此 SQL 的优化 显得重要起来。 看下面的一个例子,它对比了索引和全表扫描的效率: ********************************************************************** select * from t where object_id<100 call count cpu elapsed disk query current rows ---- ------ ---- -------- ----- ----- ------- ----- Parse 1 0.01 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 8 0.00 0.00 0 17 0 98 ---- ------ ---- -------- ----- ----- ------- ----- total 10 0.01 0.00 0 17 0 98 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 55 Rows Row Source Operation ------- --------------------------------------------------- 98 TABLE ACCESS BY INDEX ROWID T (cr=17 pr=0 pw=0 time=95 us) 10 / 90 98 INDEX RANGE SCAN T_INX (cr=9 pr=0 pw=0 time=2383 us)(object id 51627) ********************************************************************** select /*+ full(t) */ * from t where object_id<100 call count cpu elapsed disk query current rows ---- ------ ------ -------- ------ ----- ------- ----- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 8 0.01 0.00 0 695 0 98 ---- ------ ------ -------- ------ ----- ------- ----- total 10 0.01 0.01 0 695 0 98 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 55 Rows Row Source Operation ------- --------------------------------------------------- 98 TABLE ACCESS FULL T (cr=695 pr=0 pw=0 time=116 us) 11 / 90 ********************************************************************** 我们看到,在这个只有几万条记录的表中,相同的 SQL 语句,全表扫描扫过的数据块(一致 性读)是 695 个,而索引只扫过了 17 个,差别还是非常大的。 分区技术在 OLAP 数据库中很重要,这种重要主要体现在数据管理上,比如数据加载,可以通 过分区交换的方式实现,备份可以通过备份分区表空间实现,删除数据可以通过分区进行删 除;至于分区在性能上的影响,不能一概而论,认为分区的性能将始终好于非分区,这个结 论是不成立的,至少是片面的,我们通过以下几种情况来分析它。 1. 当查询的范围正好落在某个分区的时候 这时候分区的效率自然是高于没有分区的,因为 SQL 在有分区的表上只扫过一个分区的数 据,而对于没有分区,需要扫描整个表,这也是大多数人认为分区会提高性能的一个原因 吧,比如下面的例子: ********************************************************************** select count(*) from t where x<1000 call count cpu elapsed disk query current rows ---- ------ ------- -------- ---- ----- ------- ----- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.00 0 23 0 1 ---- ------ ------- -------- ---- ----- ------- ----- 12 / 90 total 4 0.00 0.00 0 23 0 1 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 55 Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=23 pr=0 pw=0 time=2495 us) 999 PARTITION RANGE SINGLE PARTITION: 1 1 (cr=23 pr=0 pw=0 time=9085 us) 999 TABLE ACCESS FULL T PARTITION: 1 1 (cr=23 pr=0 pw=0 time=4077 us) ********************************************************************** select count(*) from t1 where x<1000 call count cpu elapsed disk query current rows ---- ------ ------- -------- ---- ----- ------- ----- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.01 0.00 0 84 0 1 13 / 90 ---- ------ ------- -------- ---- ----- ------- ----- total 4 0.01 0.01 0 84 0 1 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 55 Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=84 pr=0 pw=0 time=9015 us) 999 TABLE ACCESS FULL T1 (cr=84 pr=0 pw=0 time=4077 us) 第一个 SQL 只扫过了一个分区的数据,扫过的数据块为 23 个;第二个 SQL 做了全表扫描,扫 过的数据块为 84 个,这种情况下肯定是分区表的效率要高一些。 2. 当查询的范围跨越几个分区时 这时候分区可能并不绝对是最优的,比如下面的例子,我们把查询的范围扩大到分区表的 13 个分区,让 CBO 使用 FAST INDEX FULL SCAN 的方式扫描索引,另外我们创建另一张非分区 表,表结果和数据同分区表完全一样,我们使用同一条 SQL,并且也让 CBO 强制使用 FAST INDEX FULL SCAN 的方式访问非分区表上的全局索引。我们要验证的一个观点是,分区索引并 不一定比全局索引在任何时候都快,有时候它反而会慢。下面是输入的结果: Select /*+ index_ffs(t t_ind) */ count(*) from t where x<13000 call count cpu elapsed disk query current rows 14 / 90 ---- ------ ------- -------- ---- ----- ------- ----- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.03 0.02 0 164 0 1 ---- ------ ------- -------- ---- ----- ------- ----- total 4 0.03 0.03 0 164 0 1 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 55 Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=164 pr=0 pw=0 time=29234 us) 12999 PARTITION RANGE ALL PARTITION: 1 13 (cr=164 pr=0 pw=0 time=117074 us) 12999 INDEX FAST FULL SCAN T_IND PARTITION: 1 13 (cr=164 pr=0 pw=0 time=52408 us)(object id 51774) select /*+ index_ffs(t1 t1_ind) */ count(*) from t1 where x<13000 call count cpu elapsed disk query current rows 15 / 90 ---- ------ ------- -------- ---- ----- ------- ----- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.03 0.02 0 117 0 1 ---- ------ ------- -------- ---- ----- ------- ----- total 4 0.03 0.02 0 117 0 1 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 55 Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=117 pr=0 pw=0 time=24755 us) 12999 INDEX FAST FULL SCAN T1_IND (cr=117 pr=0 pw=0 time=52082 us)(object id 51788) ********************************************************************** 在这个例子里面,分区索引之所以扫过了更多的数据块,是因为分区索引在做 FFS(INDEX FAST FULL SCAN)的时候只能够在本地索引上进行,如果涉及其他的分区,还需要按照访问索 引的方式去访问其他索引(比如先找到其他分区索引的根数据块,再找到最左边的叶块,然 后执行 FFS 操作),这样,查询跨过的分区越多,这种额外的代价就越大;而在这种情况 下,全局索引只需要定位到一个叶块,然后执行一次 FFS 就能够扫过所有的索引叶块,这样 性能就会好于分区索引。 上面的例子是想说明,OLAP 环境中,分区主要的功能是管理上的方便性,它并不能绝对保证 查询性能的提高,有时候分区会带来性能上的提高,有时候甚至会降低,就像我们在例子中 16 / 90 看到的一样。 1.3 数据库的硬件设计 数据库的硬件设计在性能上主要体现在: l CPU l I/O l 负载情况 这些指标需要对业务进行综合评估和系统测试之后,做出一个合理的硬件配置清单。 数据库的硬件设计包含了数据库服务器的架构和数据存储。这些因素在数据库设计阶段将作 17 / 90 为重点的考虑因素。如果当系统上线之后,出现冗余或者空间不足的问题,将是一件非常麻 烦的事情。 数据的存储和安全应该主要考虑以下几个问题。 1.3.1 存储容量 如果一个系统的生命周期可以确定,或者说数据库中的数据保存时间可以确定,那么我们就 可以通过一个简单的计算,大致估算出数据库所存放的数据量的大小,以作为存储设备采购 的一个依据。 可以通过估算占有存储空间的所有数据库对象(其实主要是估算业务用户下的所有对象)的 容量,来计算数据的容量。 占用空间的对象都可以在 DBA_SEGMENTS 视图里面找到,数据库的空间的分配是以段的形式分 配的,凡是段对象,都是要占用空间的,它包括表、索引、物化视图、其他的一些大对象 (比如全文索引对象)。 如果在开发阶段能够预测每个表的记录数,然后我们取得这个表的字段总长度,于是表的容 量=记录数*字段长度。 一个表中索引的大小和索引的类型,以及索引键值的重复率有很大的关系,开发人员可以通 过模拟一些实际数据来估算出索引和表数据的一个比例,然后做出索引所占空间的估算。 一个计算容量的例子如下。 我们创建一个表,然后在表上创建索引,之后对表和索引进行分析,然后查询视图 user_tables 就可以得到表的大致容量。 SQL> create table t as select * from dba_objects; Table created. SQL> create index t_ind on t(object_id); Index created. SQL> exec dbms_stats.gather_table_stats(user,'t',cascade=>true); 18 / 90 PL/SQL procedure successfully completed. SQL> select avg_row_len from user_tables where table_name='T'; AVG_ROW_LEN ----------- 93 这个数值就是表的平均行长,如果我们能够估算出预期表的记录数 N,那么最终表占用的空间 就是 93 bytes*N: SQL> select segment_name,segment_type,bytes from user_segments where segment_name in ('T','T_IND'); SEGMENT_NAME SEGMENT_TYPE BYTES ------------- ------------------ ---------- T TABLE 6291456 T_IND INDEX 1048576 SQL> select trunc((1048576/6291456)*100) ind_pct from dual; IND_PCT ---------- 16 19 / 90 我们取得了测试表中表和索引的大小,计算出索引和表大小的百分比,这样,我们就获得了 最终这个表的空间使用量为: 表的总使用量 = 93 bytes*N(1+16) 这就是预期这个表的容量,如果能够预测出未来数据库的数据量,我们就可以比较客观地估 算出数据库预期的容量大小。 另外一个容易被开发商忽略的问题是对系统备份数据占用空间的考虑。我遇到的一些系统就 有这个问题,系统上线之后才发现,设计人员根本就没有考虑过系统备份的问题,也没有预 留出足够的空间来做数据备份,给 DBA 带来的压力相当大。为了保证数据的安全,我设计了 一套备份方案,由于磁盘的空间有限,在备份新的数据的同时,还需要及时删除一些旧的备 份数据,在这种磁盘空间捉襟见肘的空隙下工作,DBA 有时候觉都不能睡安稳。 1.3.2 存储的物理设计 现在越来越多的大数据量数据库选择了 SAN 存储结构(参见图 1-2),这是一个扩展性非常好 的存储设计,它可以非常方便地将存储设备增加到存储网络当中,但成本和故障点相应地就 会变多,对维护人员的技术要求就很高,它不但要求维护人员懂得磁盘阵列的技术,还要掌 握 SAN 交换机的相关技术。 图 1-2 SAN 存储架构 1.3.3 数据的安全 20 / 90 数据的安全是系统设计阶段应该充分考虑好的一个问题,要按照用户对数据安全级别要求的 高低,以及运行业务停止的时长来设计数据库的安全解决方案。 大致来讲,用户对数据安全的要求有如下几个层次。 1.3.3.1 Data Guard 结构 如果用户对数据的安全性要求非常高,并且对系统的宕机时间要求很高,可以考虑 Data Guard 设计结构(参见图 1-3),当主数据库出现故障时,维护人员可以用最短的时间启用备 用数据库,保证业务的正常进行。 1.3.3.2 RAC 结构 RAC 结构(参见图 1-4)和 Data Guard 结构分属于不同级别的安全设计,Data Guard 能够保 证数据不丢失或者尽可能少丢失(注:Data Guard 有三种保护模式,具体细节请参考 Oracle 官方文档),它是数据库级别的一个冗余结构。而 RAC 则是实例级的一个冗余结构,它能够 保证数据库在一个实例出现故障之后,用户操作可以无缝地由另外一个实例接管,现在很多 对业务连续性要求很高的系统都采用了 RAC+Data Guard 的数据库结构设计。 图 1-3 Data Guard 结构 21 / 90 图 1-4 RAC 结构 1.3.3.3 Rman+归档的方式 Rman+归档的备份方式相对 RAC+Data Guard 来看,它的优势在于成本上要廉价,并且能够保 证数据的完整,当数据库损坏时,如果我们保留有完整的备份集和归档日志,就可以将数据 库恢复到最后出现故障时的时间点。缺点是需要较长的宕机时间。 1.3.3.4 数据导出方式(exp/imp,exdp/imdp) 这两种数据导入导出方式更像是一种数据传递或者数据保存,它不能保证数据的安全。 1.4 小结 1. 系统的数据库类型,OLAP 还是 OLTP 弄清楚系统是 OLAP 或是 OLTP 是一件非常重要的事情,它将影响到数据库所有的相关设置, 不论是内存参数、存储参数还是性能参数,可以说是系统设计阶段数据库设计最优先考虑的 事情。 2. 系统并发量 如果是一个 OLTP 系统,并发将作为非常重要的一个因素考虑,如果设计阶段没有对系统预期 的并发数做出准确的估算,将会出现非常严重的后果,比如前面说的那个门票销售系统的例 子。 22 / 90 高的并发数可能导致这样两个严重的后果: ● 系统资源严重被使用,系统过负荷运行。 ● 严重的等待事件,比如前面讲到的热块以及锁定等情况。 3. SQL 代码的编写 性能低劣的 SQL 对数据库的杀伤力是巨大的。开发人员,特别是数据库接口的开发人员,应 该好好学习一下 SQL 的开发技巧,它不但包括 SQL 功能的实现,还有 SQL 语句的优化性。可 是很遗憾的是,很多开发人员忽略了 SQL 的开发技巧。 4. 数据库的设计 数据库本身的设计在系统设计中也是至关重要的,因此我更推崇让 DBA 来参与或者执行这个 工作,因为 DBA 会更关注数据库自身的问题,会让数据库变得更可用。 5. 存储的设计 在系统设计阶段,一定要预测系统预期的数据使用空间,否则以后数据库扩容将是一件非常 麻烦的事情,特别是那些不允许宕机的系统,尤其应该将各种因素考虑清楚,最大可能地消 除将来由于系统自身设计的问题导致的隐患。 第 2 章 锁和阻塞 2.1 关于锁 锁这种东西,对于初学者来说就像是一个深不可测、遥不可及的概念。我的经验告诉我,如 果初学者冲上来就去纠结 Oracle 各种锁的概念、锁的类型、锁的级别,试图用最快的方式来 理解锁的含义,基本上都会感到痛不欲生,至少我当时的心情大致如此。 随着工作时间的变长,我慢慢地养成了一种和从前不太一样的思维方式。比如现在我们在谈 锁,以前的方法是,首先会去找文档来看什么是锁,费力不说,到底也没有搞清楚;而现在 的思维方式转变了,开始学会用一种轻松、主动的方式来思考问题,于是我首先会去想,为 23 / 90 什么会有锁?没有它会怎么样? 我必须要向大家推荐后面一种思考方式的好处。这是一种启发式的思考模式,是主动的,所 以它是快乐的。而前一种是消极的,被动的,同时也是痛苦的。我就曾经在这样的痛苦中摸 索了很久,希望大家不要重蹈我的覆辙。 为了说明这样的一种思维方式,我举一个例子,想必大家都切身感受过。 中学时候学万有引力定律时,老师上来就告诉我们,世间万物是相吸的,并由牛顿发现了 它,下面是万有引力定律的公式„„于是大家机械地听着,记着,背着。坦白地说,这样被 动接受的感觉很一般,但至少还不太痛苦,因为我们还能听懂。大家试想一下,万有引力是 多么美好的东西啊?如果我们托着下巴,呆呆地坐在窗户前面想,为什么地球就要绕着太阳 转呢?为什么不会飞走呢?如果有一天飞走了,我们怎么办呢?这看起来是在胡思乱想,比 起课堂老师上来就捞干的说要低效得多,但是往往就是在这种低效中,我们获得了任何人都 不能给予的东西,我们通过自己的思维推理,得出了这个结论:太阳对地球是有吸引力的。 这是我们主动获得的东西,我们充满了成就感和自豪感。 前面扯了一些看似不着边际的话,在我看来非常重要,这也是我想通过这本书想表达的另一 个目的,就是学会思考。它比我直接告诉你锁是什么要重要得多,它希望你能够像思考地球 为什么围绕着太阳转一样,托着下巴来思考 Oracle 数据库为什么会发明一个锁,这不是扯 淡。 让我们一起来思考这个问题吧,如果你还不太习惯这种思考方式的话。为什么 Oracle 数据库 中会有锁呢?设想,如果世界上只有你一个人,你有一箱子金币,你会弄把锁头来把它锁起 来吗?不会,因为如果世界上只有你一个人,根本就不会有锁头这种东西出现,因为没有其 他高等生物会去打开它,除了你。你可能会问,举一个如此粗鄙的例子给我们描述锁,太不 把 Oracle 放在眼里了吧?其实不是这样的。Oracle 的锁和这个锁头一样吗?答案是一样的, 就是因为受现实生活中的锁头的启发才取了这个名字(lock),所以你看,它神秘吗?一点 都不神秘,那不过是一层窗户纸,只需要我们的轻轻一捅。 继续我们的推理,现实生活中因为存在着其他想得到箱子里金币的人,你为了防止他们来 拿,所以上了锁。在数据库中也存在着类似的事情,如果一个数据库只有你一个人用,我绝 对不相信 Oracle 会费这么大的劲开发出一个锁来捣乱,所以,我们的答案找到了,因为还有 其他的人在使用这个数据,你为了防止他们把你做的事情弄糟,所以在你对数据操作的时 候,拒绝他们操作!简单吧?是不是还有点高兴?我们就在说说笑笑的过程中了解了锁的渊 源。 可能有人跳出来说,你说了半天只是说了一个锁,锁分很多种啊,表级锁、行级锁、共享 锁、排他锁„„你再来用你的快乐法推啊 ! 那好,我们再推,我们这回从哲学上来推。哲学真是一个非常高深莫测的学科,我一直觉得 只有一些绝顶聪明的人才能学懂哲学,我是学不懂的,只能引用他们发明的一些话。比如现 在我引用这条“存在即合理”。就是说,Oracle 的老板不是笨蛋,没必要花高昂的费用雇人 写一些没有意义的代码。那么,对于一个公司来说什么叫有意义?能满足用户的需求即为有 意义,好了,答案找到了,用户 A 的系统需要用到表级锁,Oracle 公司为了把数据库卖给 24 / 90 他,所以开发了表级锁,用户 B 需要用到行级锁,所以 Oracle 为他们开发了行级锁„„ 做技术的人,总想把所有的技术都学会,之前我也曾这么学过,后来发现学完之后很快就忘 了。比如最开始 Oracle 推出 RAC 的时候,我们费尽千辛万苦搭好了一个环境,开始很激动, 整天在上面打命令。可是没持续多久,就没人用了,原因是大家不知道该做什么了,该做的 都做完了。看起来是该会的都会了,其实是什么都还不会,比如说 RAC 对系统的影响究竟有 多大?什么样的系统需要用 RAC?RAC 在资源分配上需要怎样的配置才合理?一堆的疑问摆在 我的面前,我发现我什么都没学会,因为我没有认真思考 Oracle 为什么会发明一个 RAC。 一年后,每个人几乎把所做的测试统统忘光了,直到我们的系统中真正用到了 RAC,才开始有 目的地研究起来,那时候故障不断,一个月的时间锻炼要强过以前好几个月的自学。 说这个例子是什么意思呢?就是要说,很多概念或者技术,看起来很深奥,那是因为你没有 用到,当你用到的时候,你会觉得它无非是满足用户需求的一个产品,仅此而已。上面讨论 的锁也一样,它对于你很陌生,说明你现在没有用到它,还不需要它,当你需要它的时候, 你会发现它正好能满足你的需求,那时候不要说理解,基本上就是拿来就可以用了。很多人 学了无数年的经济,却比不上一个几乎没有读过书的人懂得做生意,因为前者在漫无目的地 机械地学,后者在为了生存下去而不断地思考。这就是区别。 2.2 锁和阻塞 关于并发(concurrency)这个词,请注意区别另外一个词,并行(parallel),在英语中这 本是两个完全不同的单词,但汉语却把它们弄得有点相似。在数据库中,并发的意思是说有 超过两个以上的用户对同样的数据做修改(可能包括插入、删除和修改),而并行的意思是 说将一件事情分成很多小的部分,让每一部分同时执行,最后将执行结果汇总成最终结果 (关于并行后面有专门的一章来介绍)。 你如果还记得前面说到锁的问题的话,应该已经猜到我想说什么了。是的,没有并发,就没 有锁,锁的产生是因为并发,并发的产生是因为系统需要,系统需要是因为用户需要,这 样,又回到我们之前引用的哲学观点了—存在即合理。 下面是一个最简单的并发导致锁定的例子。 我们开一个会话 session 1,注意,以后我们再举例子的时候大致都会这么来说,它的实际过 程是,你要和数据库建立起一个连接,方式是可以使用第三方软件,或者直接使用 SQLPLUS 来连接。 SQL> select sid from v$mystat where rownum=1; 25 / 90 SID ---------- 158 Sql>create table t(x int primary key); Table created. SQL>insert into t values(1); Commit; SQL> update t1 set x=10 where x=1; 已更新 1 行。 我们创建了一张只有一个字段 X 的表 T,这个字段上有个一个主键(primary key),它的意思 是要求这个字段的值唯一。 我们再开一个会话,session 2: SQL> select sid from v$mystat where rownum=1; SID ---------- 26 / 90 157 SQL> update t1 set x=10 where x=1; 这时候 session 2 就被“卡”在那里了。我常听开发人员称之为锁表。其实在数据库中对这 个过程的具体描述是:session 2 被 session 1 阻塞(session 2 was blocked by session 1)。 为什么会有这个现象呢?我们在 T 表 x 列上创建了一个主键以保证这个列值的唯一性,然后 我们在 session 1 中插入了 1 条数据,并没有提交,此时数据库会认为你还没有决定是否将 这条数据 1 插入到表中,它在等待你做出决定(提交或是回滚)。正当数据库在等待你的决 定时,另一个用户也插入了同样的一条记录,为了保证数据不重复,数据库只能让另一个用 户(session 2)等待,直到你做出决定。 我们可以从一个视图中看到这些信息。 SQL> select sid,type,id1,id2,lmode,request,block from v$lock where sid in (157,1 58) order by sid; SID TY ID1 ID2 LMODE REQUEST BLOCK ----- ----- -- ------ ------- ---------- ---------- 157 TM 51349 0 3 0 0 157 TX 327699 292 0 6 0 158 TX 327699 292 6 0 1 158 TM 51349 0 3 0 0 这里 SID=158 是第一个会话 session 1,SID=157 是第二个会话 session 2,BLOCK=1 表示这 个会话正在阻塞其他的会话,REQUEST=6 表示当前会话正在等待一个 LMODE=6 的锁,意思是, 这个会话正在被阻塞。 关于这个视图每个列的详细含义,可以参考 Oracle 官方文档的 Refrence 部分。 27 / 90 现在我们是通过正向的方式模拟了阻塞的情况,在现实的例子中,情况通常是用户(或者也 有开发人员)告诉我们,系统卡住了,不动了,那么我们就应该逆着这个过程来寻根溯源。 首先要下手的就是查看这张视图 V$LOCK,通常来讲,系统如果平时运行正常,突然会停止不 动,多半是被阻塞(blocked)住了,我通常第一个动作就是先看看 V$LOCK 这张视图,看看 是不是有像上面一样的阻塞信息。 这张视图最有意思的两列就是右边数第一列和第二列,也就是 request 列和 block。按照 Oracle 官方文档的说明,如果某个 SID 的 request 列是一个非 0 的值,那么它就是在等待一 个锁,如果 block 列是 1,这个 SID 就持有了一个锁,并且阻塞别人获得这个锁。这个锁的类 型由 TYPE 字段定义,锁的模式由 LMODE 字段定义,ID1 和 ID2 字段定义了这个锁的相关信 息。 现在这个 V$LOCK 视图给出的信息中,我们可以看到 SID 是 157 的会话有一个 IMODE 为 6 的请 求,这只是一个请求,它没有没有获得它,而是在等待;SID 是 158 的会话持有了一个 LMODE 为 6(其实这是一个行级排他锁)的锁,并且阻塞了 SID=157 会话的请求(block=1)。 如果你足够仔细,就能够发现这两列的 ID1 和 ID2 的值完全相同。这并非偶然,而是必然, 因为它们本来就是指向同一个资源,只不过一个是持有者(sid =158),一个是请求等待者 (sid=158)。 通过这个视图,我们很快就发现了问题所在(实际上这是一个最简单的阻塞关系,生产环境 中通常要复杂一些,不过当你掌握了这些技术之后,通过推理,就可以慢慢地确定问题的所 在)。 session 2 卡住了的原因是由于 session 1 上有一个事物没有提交,而在这张表上恰好有要 求列值唯一性的约束(表建有主键),我们通过 SID 号,去查询视图 V$SESSION 视图就可以 确定用户的信息了。 比如可以这样: SQL> select machine from v$session where sid in (158,157); MACHINE ---------------------------------------------------------------- WORKGROUP\ALAN WORKGROUP\ALAN 因为这是我自己的试验机器,所有两个 session 的 machine 名字是一样的,否则应该是每个 28 / 90 用户所使用的机器名称。 这里面还有一种情况,就是如果你的系统是一个多层结构,用户是通过一个中间件共享池来 连接到数据库中的,那么这里面的机器名字将是你的中间件服务器的名字,这种情况就有一 点小麻烦,你可能要借助一些其他的办法来确定用户的具体信息(比如借助中间件服务提供 的连接信息)。 这样我们通过这种方式反推出系统出现问题的环节,当然,这个过程并不能叫做性能优化, 充其量只是一个故障定位(trouble shooting)。性能优化是一个系统的工程,我们在上一 章已经做了比较详细的论述,通常来讲,一个系统设计完毕后,它的性能优化的余地已经非 常小了,因此我想再次强调系统设计阶段对性能考虑的重要性,以及 DBA 尽早介入系统开发 的重要性。 我在这里并没有打算将锁的类型和模式做非常深入的解释,那些东西可以很容易地从 Oracle 的官方文档中找到(tahiti.oracle.com)。就像开头说的那样,我们尽可能地不把事情弄得 太复杂,那样会让人望而却步,几乎把自己要做什么都忘记了。 不知道大家注意到没有,在刚才查看的 V$LOCK 视图当中,每个 session 还各自持有一个 type 为 TM 的锁,在这个锁上面,没有阻塞和等待,因为它是个表级共享锁,就是说,每个用户都 可以以共享的方式(lmode 为 3)持有它。 其实 TM 锁更像是一个段级的锁,通常我们叫它表锁,是因为我们把它这个表看做一个段,当 某个表有几个段的时候,每个段上都会分别加上 TM 锁。 看下面的例子: SQL> select sid from v$mystat where rownum=1; SID ---------- 143 SQL> create table t(x int) partition by range(x)(partition p1 values less than(10), partition p2 values less than(maxvalue)); 表已创建。 29 / 90 SQL> insert into t values (1); 已创建 1 行。 SQL> select * from t partition(p1); ---这条记录落在了 p1 分区中。 X ---------- 1 SQL> select sid,type,id1,id2,lmode,request,block from v$lock where sid=143; SID TY ID1 ID2 LMODE REQUEST BLOCK -------- -- ------ ----- ------ -------- ------ 143 TM 51358 0 3 0 0 143 TM 51359 0 3 0 0 143 TX 458769 306 6 0 0 SQL> select object_Name,subobject_name from dba_objects where object_id in(51358,51359); OBJECT_NAME SUBOBJECT_NAME ------------------------------ ------------------------------ T T P1 30 / 90 我们创建了一个分区表 T,它包含了 2 个分区,P1 和 P2。当我们向表中插入一条记录,而这 条记录落在了分区 P1 上,此时 P2 里面并没有数据,我们通过查看 V$LOCK 视图发现,数据库 只在表 T 和分区 P1 上设置了 TM 共享锁,却没有在分区 P2 上设置(当锁的类型为 TM 时,字 段 ID1 表示加锁的对象 ID,我们可以关联 DBA_OBJECTS 视图来获得对象的名称,这里 51358 和 51359 分别对应的是 T 表和 T 表的第一个分区 P1)。 如果我们分别对三个对象做 DDL 操作: SQL> truncate table t; truncate table t * 第 1 行出现错误: ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源 SQL> alter table t truncate partition p1; alter table t truncate partition p1 * 第 1 行出现错误: ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源 SQL> alter table t truncate partition p2; 表被截断。 因为 P2 分区上没有加 TM 锁,所以 P2 上可以进行 DDL 操作。TM 是一个段级的共享锁,它允许 同级别(或更低级别)的锁同时设置,但拒绝高级别的锁定请求(在这里,DDL 操作需要一个 更高级的排他表级锁)。Oracle 尽可能地减少锁影响的范围,在这里,由于事务涉及的数据 31 / 90 没有影响到 P2 分区 ,因此,在 P2 分区上做 DDL 操作是被允许的。 SQL> insert into t values(11); 已创建 1 行。 SQL> select * from t partition(p2); ---新插入的行落在 p2 分区中。 X ---------- 11 SQL> SQL>SQL> select sid,type,id1,id2,lmode,request,block from v$lock where sid=143; SID TY ID1 ID2 LMODE REQUEST BLOCK --- -- ---------- ---------- ---------- ---------- ---------- 143 TM 51358 0 3 0 0 143 TM 51359 0 3 0 0 143 TM 51360 0 3 0 0 143 TX 458769 306 6 0 0 SQL> 当向 P2 分区中插入一条数据时候,我们发现,P2 分区也加上了一个共享锁(ID1=51360), 因为在这个分区上有事务,所有 Oracle 需要在这个对象上加一个共享锁,以最小代价来保护 这个事务所涉及的对象不会被改变,而 DDL 操作是需要获得这个分区上的一个排他锁,因此 是不被允许的,因为这个对象上还有尚未结束的事务,为了保证事务的完整性,DDL 操作(在 32 / 90 例子里是 truncate)被拒绝。 很多人一看到 TX 锁,就不由自主地想到是一个行级排他锁,这样理解有时候是会误导你的, 它的真正目的其实只是维护一个事务的完整性,在多数情况下,这种事务是更新表的数据, 所以给人一种印象,它就是加在数据行上的一个排他锁。 在 Oracle 里面,并不存在真正意义上的属于某个对象或者数据的锁,那需要一个锁管理器, 无形中锁管理器自身又产生了另一个申请锁的等待,就是等待锁管理器来分配和释放锁。在 Oracle 数据库中,它并不会对某个表上加个锁或者某几行上加上锁,锁是以数据块的一个属 性存在的。也就是说,每个数据块本身就存储着自己数据块中数据的信息,这个地方叫 ITL (Interested Transaction List),凡是在这个数据块上有活动的事务,它的信息就会记录 在这里面供后续的操作查询,以保证事务的一致性。 看下面的例子: SQL> create table t(x int); 表已创建。 SQL> insert into t select object_id from dba_objects; 已创建 49749 行。 SQL> commit; 提交完成。 SQL> update t set x=x+1 where x<5000; 已更新 4916 行。 33 / 90 SQL> select sid,type,id1,id2,lmode,block from v$lock where sid=138; SID TY ID1 ID2 LMODE BLOCK ---------- -- ---------- ---------- ---------- ---------- 138 TM 51366 0 3 0 138 TX 65581 300 6 0 我们首先更新了表的一部分数据,这里是 4916 行,可以看到 TX 锁的 id1 和 id2 分别是 65581 和 300。 SQL> update t set x=x+1 where x>5000; 已更新 44832 行。 SQL> select sid,type,id1,id2,lmode,block from v$lock where sid=138; SID TY ID1 ID2 LMODE BLOCK ---------- -- ---------- ---------- ---------- ---------- 138 TM 51366 0 3 0 138 TX 65581 300 6 0 我们继续更新了表剩余部分数据的值,会发现事务的 id1 和 id2 的值和上面依然一样,这说 明这个事务锁并非属于某些行,实际上它只属于一个事务,无论这个事务做了多少操作,都 只是这一个事务锁,和它所操作的数据多少无关。 这个行级锁的称谓有误导人的嫌疑,其实它的真正意思是,这个事务锁会在行级对数据产生 34 / 90 影响,比如说阻塞。还记得前面的例子吗?在一个主键表中,当我更新一个主键的键值时, 就会产生一个 TX 事务锁,它阻止其他人对这一行的更新。因为它体现在对表的具体行的操 作,因此称为行级锁,但我更喜欢称它为事务锁,这样理解起来要容易得多。 另外,TX 并非都是在对数据修改的时候才会产生,只要需要维护事务一致性的时候,就需要 用到这个锁。比如分布式事务,一般来说就是通过 dblink 在几个数据库中处理数据,因为是 分布式的操作方式,所以需要一个事务的总协调者,这时候即使都是读取操作,依然需要一 个 TX 锁来维护事务的一致性。 同样,TM 锁也并非是加在表上的一个锁,它会在表的级别上产生影响,比如它不允许其他用 户对表做 DDL 操作,其实这不过是一个意向的问题,而并非是锁存在的方式。再重复一遍, Oracle 中锁的信息是数据块的一个属性,是物理的,并不是逻辑上属于某个表或者某个行。 可能有些读者还注意到了一个细节,就是我没有介绍 V$LOCK 视图中锁类型为 TX 时 id1 列和 id2 列的含义。这里面是有个原因的,因为我想让大家的思想始终围绕性能这个话题,而不要 跑题,要知道,Oracle 的每一个技术都可以弄出一长篇的东西来做研究,若是每一个细节都 要拿出来讨论的话,那么这本书就基本上和一个技术手册没有区别了。 顺便带一句,在 TX 锁中,这两个字段构成了这个事务在回滚段中的位置。这有什么用呢?它 的用处是,当其他的操作需要读取这个数据块时,它会发现这个块上要读取的数据上是有活 动事务的,因此需要到回滚段中去找它之前的内容,那么这些内容在回滚段的什么地方呢, 这两个值就告诉了你。 而对于 TM 锁,ID1 值就是加锁的段对象,可以是一个表,也可以是表的一个分区,此时 ID2 一般为 0。 在实际生产环境中,这种由于唯一性约束导致数据阻塞的情况还是比较多见的,上面的例子 提供了一种方式来定位这种故障。 2.3 引起阻塞的其他情况 除了上面讨论的由于唯一约束引起的阻塞之外,在生产环境中还经常会遇到下面两种情况引 起的阻塞: ● select for update 35 / 90 ● 外键没有创建索引 2.3.1 select for update 这个语法是有相当大的现实意义的,比如说有这样的一个例子,某公司人事部的某个员工在 修改公司雇员的工资,雇员的工资表的结构,我们可以借用 Oracle 自带的 SCOTT 用户下的表 EMP,它的内容如下: SQL> select * fromemp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ------ ------ ---- ----- --------- ------ ----- --------- 7369 SMITH LERK 7902 17-12 月-80 800 20 7499 ALLEN SALESMAN 7698 20-2 月 -81 1600 300 30 7521 WARD SALESMAN 7698 22-2 月 -81 1250 500 30 7566 JONES MANAGER 7839 02-4 月 -81 2975 20 7654 MARTIN SALESMAN 7698 28-9 月 -81 1250 1400 30 7698 BLAKE MANAGER 7839 01-5 月 -81 2850 30 7782 CLARK MANAGER 839 09-6 月- 81 2450 10 7788 SCOTT ANALYST 7566 19-4 月 -87 3000 20 7839 KING PRESIDENT 17-11 月-81 5000 10 7844 TURNER SALESMAN 7698 08-9 月 -81 1500 0 30 7876 ADAMS CLERK 7788 23-5 月 -87 1100 20 7900 JAMES CLERK 7698 03-12 月-81 950 30 7902 FORD ANALYST 7566 03-12 月-81 3000 20 7934 MILLER CLERK 7782 23-1 月 -82 1300 10 已选择 14 行。 36 / 90 SQL> 这个人事部的员工 A 将当月雇员 Scott 的工资修改为原来的 110%,它在自己的机器上发出了 这样的操作执行命令(实际上他操作的是一个 GUI 程序,由这个程序向数据库发出了这样的 指令): Sql>select * from emp where ename='SCOTT'; 这个操作将雇员 Scott 的信息从表 EMP 中提取到 A 的操作界面上,用户 A 可以修改这个用户 的工资,在用户的界面上有一个按钮,让用户决定是否让修改生效。但是这个 A 员工在确认 修改之前,由于其他的事情走开了。 此时人事部的 B 员工也做着同样的事情(让我们假设他们之间的工作协调有了问题,这在实 际工作中应该不少见吧),他也发出了这样的操作命令: SQL> select sal from emp where ename='SCOTT'; SAL ---------- 3000 B 员工按了保存按钮,修改生效,实际上是程序向数据库发出了这样的 SQL 指令: Sql> update emp set sal=sal*1.1 where emp ename='SCOTT'; SQL>commit; 用户 B 提交了修改,操作生效,现在员工 Scott 的工资变成了以前的 1.1 倍。 37 / 90 SQL> select sal from emp where ename='SCOTT'; SAL ---------- 3300 现在我们的用户 A 又回到了自己的座位上继续他的工作,他将在操作界面上将 Scott 用户的 工资修改成原来的 1.1 倍,然后保存。 数据库依然执行了上面的 update 操作,并执行了 commit,这时候,如果 A 用户再次查询 Scott 用户的信息,就会惊奇地发现,这个用户的钱竟然不是自己修改的数据值,而是另外一 个数: SQL> select sal from emp where ename='SCOTT'; SAL ---------- 3630 幸亏这个 A 员工即时发现了这个问题,否则雇员 Scott 就变成了这样一个存在 bug 的系统的 “最大受益者”。 让我们再来从时间的顺序上回顾一下这个操作: A 员工 B 员工 时间 1 select * from emp where enam e='SCOTT'; 时间 2 select * from emp where ename='SCOTT'; 时间 3 update emp set sal=sal*1.1 38 / 90 where ename='SCOTT'; 时间 4 commit; 时间 5 update emp set sal=sal*1.1 where ename='SCOTT'; 时间 6 commit; 在时间 1 的那一刻,Scott 用户的工资是 3000,A 员工仅仅是将数据从数据库中提取出来,并 没有对数据做处理(至少没有对数据库发出处理数据的请求)。 时间 2,3,4 由用户 B 完成了对 Scott 员工工资的修改,并提交,此时 Scott 的工资为 3000*1.1=3300 时间 5,6 由用户 A 继续之前的操作,而此时 Scott 用户的工资已经更新,但用户 A 浑然不 觉,所以他继续在当前工资的基础上,又增加为 1.1 倍,所以: 3300*1.1=3630 这是程序编写时的一个 bug,当用户 A 需要修改 Scott 员工的工资时,并没有保证这个数据不 能被其他人修改,这是一个非常危险的程序 bug。 对于这个例子,程序开发人员应该考虑避免这种现象的出现,一个比较简单的方式就是使用 select„ for update 的方式,以排他的方式获得这些需要修改行的数据,并且保证在修改完 成之前,其他用户无法对这些数据进行修改。 下面是这样的一个例子。 创建一个会话 session 1: SQL> create table t as select rownum id from dba_users; 表已创建。 SQL> select * from t; 39 / 90 ID ---------- 1 2 3 4 5 6 7 8 9 10 11 ID ---------- 12 13 14 15 16 17 18 40 / 90 19 20 21 22 已选择 22 行。 希望修改 id>10 的记录: SQL> select * from t where id>10 for update; ID ---------- 11 12 13 14 15 16 17 18 19 20 21 41 / 90 ID ---------- 22 已选择 12 行。 另外开一个会话 session 2 尝试做同样的操作: SQL> select * from t where id>10 for update; 这时候 session2 就会被 session1 阻塞,因为 session 2 需要等待 session 1 的操作结果才 能确定 id=20 的记录有哪些。 从 V$LOCK 视图中可以看到这种阻塞关系: SQL> select sid,type,lmode,request,block from v$lock where type in ('TM','TX'); SID TY LMODE REQUEST BLOCK ---------- -- ---------- ---------- ---------- 142 TX 0 6 0 143 TM 3 0 0 142 TM 3 0 0 143 TX 6 0 1 SQL> sid=143 是例子中的 session 1,sid=142 是 session 2,session 1 持有一个 lmode 是 6 的事 务锁(TX),而 session 2 正好是等待这个事务锁,block 项中的 1 表示当前回话阻塞了其他 42 / 90 的会话。 如果程序设置成这样,通常可以保护 session 1 用户的数据一致性,但对于 session 2 用户 来说,如果 session 1 迟迟没有提交,将会一直等待下去,对于用户来说,它很可能不知道 发生了什么事情,也不知道要等多久。 如果 session 2 不愿意等待,可以通过这样的操作来获得一个错误通知而无须等待。 ----Session 1 SQL> select * from t where id>10 for update nowait; ID ---------- 11 12 13 14 15 16 17 18 19 20 21 ID ---------- 43 / 90 22 已选择 12 行。 SQL> ----Session 2 SQL> select * from t where id>10 for update nowait; select * from t where id>10 for update nowait * 第 1 行出现错误: ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源 在 SQL 语句中通过使用 nowait 选项,可以让后续操作的用户不必等待而直接收到一个错误信 息,在有人为干预的情况下,这种错误提示无疑会更有助于故障的判断。 但对于一个自动运行的系统来讲,等待,似乎更合理一些,尽管要等待,但它能够保证系统 正常运行而不会中断。 2.3.2 外键和索引 如果你的系统有主、外键引用关系,并且满足以下三个条件中的任意一个,那么你应该考虑 给外键字段创建索引,否则系统的性能可能会下降甚至阻塞。 ● 主表上有频繁的删除操作。 ● 主键上有频繁的修改操作。 ● 业务上经常会出现主表和从表做关联查询的情况。 44 / 90 第一个条件和第二个条件操作的时候,主表会在从表上创建一个锁定,以保证主表主键的修 改不会导致从表的数据在引用上出现问题,这是一个数据引用完整性的要求,如果主表上经 常出现这样的删除或是对主键列进行修改的操作,或者每次操作的记录数很多,都将造成从 表长时间被锁定,而影响其他用户的正常操作。比如主表每次删除 1000 行数据,它就需要扫 描从表 1000 次,以确定每一行记录的改变都不会造成从表数据在引用上的不完整。 下面的例子说明了删除主表中一条记录和多条记录在性能上的差别,这种差别主要表现在对 从表数据的读取次数。 SQL> create table t(x int primary key); Table created. SQL> create table t1 (x references t); Table created. SQL> insert into t select rownum rm from dba_users; 32 rows created. 向从表中插入多一些的数据。 SQL> begin for i in 1..22 2 loop 3 for j in 1..1000 loop 4 insert into t values(i); 5 end loop; 6 end loop; 45 / 90 7 end; 8 / 开启对 SQL 执行过程的 trace 开关。 SQL> alter session set sql_trace=true; 会话已更改。 SQL> delete t where x=1; delete t where x=1 * 第 1 行出现错误: ORA-02292: 违反完整约束条件 (TEST.SYS_C005163) - 已找到子记录 对生成的 trace 文件做下面的操作: Tkprof tracefile.trc tracefile.log 关于 SQL trace 和 TKPROF 工具,将在第 10 章进行详细的介绍。 在生成的文件中找到了 T1 表操作的信息: delete from t where x=1 46 / 90 call count cpu elapsed disk query current rows ----- ------ ----- ------- ----- ----- ------- ----- Parse 1 0.01 0.06 0 0 0 0 Execute 1 0.02 0.11 0 1 7 0 Fetch 0 0.00 0.00 0 0 0 0 ----- ------ ----- ------- ----- ----- ------ ----- total 2 0.03 0.18 0 1 7 0 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 55 Rows Row Source Operation ------- --------------------------------------------------- 0 DELETE T (cr=0 pr=0 pw=0 time=18 us) 1 INDEX UNIQUE SCAN SYS_C005162 (cr=1 pr=0 pw=0 time=32 us)(object id 51387) ********************************************************************** select /*+ all_rows */ count(1) from "TEST"."T1" where "X" = :1 call count cpu elapsed disk query current rows 47 / 90 ----- ------ ----- ------- ----- ----- ------- ----- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.01 0.00 0 38 0 1 ----- ------ ----- ------- ----- ----- ------- ----- total 3 0.01 0.00 0 38 0 1 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=38 pr=0 pw=0 time=4901 us) 1000 TABLE ACCESS FULL T1 (cr=38 pr=0 pw=0 time=6689 us) 从这个结果上看,当主表 T 删除了 1 行记录的时候,从表 T1 执行了一次全表扫描的操作 (Execute =1),这主要的原因就是主表在删除数据时,要对从表做一次全表扫描以确定要 删除的数据在从表中是否存在。 下面我们要看看当主表中删除多条数据时,从表上的数据的访问情况。为了模拟主表能完成 删除多行而不因报错而终止的动作,我们向主表中插入一些从表中没有的数据,以便于删除 能够进行下去: SQL> insert into t select object_Id from dba_objects where object_id>22 and obj ect_id<100; 48 / 90 已创建 77 行。 SQL> commit; 提交完成。 SQL> alter session set sql_trace=true; 会话已更改。 SQL> delete t where x>22; 已删除 77 行。 SQL> commit; 提交完成。 SQL> alter session set sql_trace=false; 会话已更改。 下面是 trace 文件中的信息: delete t 49 / 90 where x>22 call count cpu elapsed disk query current rows ----- ------ ----- ------- ----- ----- ------- ----- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.04 0.28 0 1 391 77 Fetch 0 0.00 0.00 0 0 0 0 ----- ------ ----- ------- ----- ----- ------- ----- total 2 0.04 0.28 0 1 391 77 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 55 Rows Row Source Operation ------- --------------------------------------------------- 0 DELETE T (cr=2927 pr=0 pw=0 time=448550 us) 77 INDEX RANGE SCAN SYS_C005162 (cr=1 pr=0 pw=0 time=811 us)(object id 51387) ********************************************************************** select /*+ all_rows */ count(1) 50 / 90 from "TEST"."T1" where "X" = :1 all count cpu elapsed disk query current rows ----- ------ ----- ------- ----- ----- ------- ----- Parse 1 0.00 0.00 0 0 0 0 Execute 77 0.00 0.00 0 0 0 0 Fetch 77 0.11 0.15 0 2926 0 77 ----- ------ ----- ------- ----- ----- ------- ----- total 155 0.11 0.15 0 2926 0 77 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 77 SORT AGGREGATE (cr=2926 pr=0 pw=0 time=155894 us) TABLE ACCESS FULL T1 (cr=2926 pr=0 pw=0 time=154296 us) 从这个结果可以清楚地看到,当主表删除了 77 条数据时,从表执行了 77 次这条 SQL 语句, 也就是对从表扫描了 77 遍!这对性能的影响是相当大的。 这条 SQL 语句: 51 / 90 select /*+ all_rows */ count(1) from "TEST"."T1" where "X" = :1 在从表上一共扫描了 2926 个数据块。试想在生产环境中,如果从表更大一些,这种代价是相 当大的。 如果此时在从表的外键上创建了索引,那么通过访问从表索引的方式在外键上查找相关的记 录将会极大地提升查询性能,同时能够避免从表被长时间锁定。 对于主从表上经常有关联查询的情况,从表外键上创建索引也是非常必要的,因为访问索引 的效率要比全表扫描高很多,性能会有很大的提高。 下面的例子演示 SQL 在执行全表扫描和通过索引方式访问数据时消耗资源的比较: SQL> create table t (id int primary key,name varchar2(2000)); Table created. SQL> create table t1(id int references t,name varchar2(2000)); Table created. 我们创建了一个主表 T 和一个从表 T1。 SQL> insert into t select rownum,object_name from dba_objects; 50871 rows created. SQL> commit; 52 / 90 Commit complete. SQL> insert into t1 select rownum,object_name from dba_objects; 50871 rows created. SQL> commit; Commit complete. 然后分别向两张表中插入相同的数据: SQL> create index t1_ind on t1(id); Index created. SQL> exec dbms_stats.gather_table_stats(user,'t',cascade=>true); PL/SQL procedure successfully completed. SQL> exec dbms_stats.gather_table_stats(user,'t1',cascade=>true); PL/SQL procedure successfully completed. 53 / 90 在 T1 表 ID 字段上创建索引,并分别对两张表进行分析。 SQL> set autotrace trace stat; SQL> select /*+ full(a) */ a.id,a.name from t1 a,t b where a.id=b.id and b.id<100; 99 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 3814514657 ------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU) |Time | ------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 66 | 2178 | 57 (4) | 00:00:01 | | 1 | NESTED LOOPS | | 66 | 2178 | 57 (4) | 00:00:01 | |* 2 | TABLE ACCESS FULL | T1 | 67 | 1876 | 57 (4) | 00:00:01 | |* 3 | INDEX UNIQUE SCAN | SYS_C0010063 | 1 | 5 | 0 (0) | 00:00:01 | ---------------------------------------------------------------------- Predicate Information (identified by operation id): 54 / 90 --------------------------------------------------- 2 - filter("A"."ID"<100) 3 - access("A"."ID"="B"."ID") filter("B"."ID"<100) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 362 consistent gets 0 physical reads 0 redo size 2591 bytes sent via SQL*Net to client 466 bytes received via SQL*Net from client 8 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 99 rows processed 通过使用 Hint /*+ full(a) */来强制两表关联查询时,对 T1 表采取全表扫描的方式进行关 联查询,从执行计划里我们看到 T1 表的确是进行了全表扫描。 SQL> select a.id,a.name from t1 a,t b where a.id=b.id and b.id<100; 55 / 90 99 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 3374743317 ---------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost(%CPU)| Time ---------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 66 | 2178 | 3 (0)| 00:00:0 | 1 | NESTED LOOPS | | 66 | 2178 | 3 (0)| 00:00:0 | 2 | TABLE ACCESS BY INDEX ROWID | T1|67 | 1876 | 3 (0)| 00:00:0 |* 3 | INDEX RANGE SCAN | T1_IND | 67 | | 2 (0)| 00:00:0 |* 4 | INDEX UNIQUE SCAN | SYS_C0010063| 1 | 5 | 0 (0)| 00:00:0 ---------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("A"."ID"<100) 4 - access("A"."ID"="B"."ID") filter("B"."ID"<100) 56 / 90 Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 124 consistent gets 0 physical reads 0 redo size 2591 bytes sent via SQL*Net to client 466 bytes received via SQL*Net from client 8 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 99 rows processed SQL> 第二次关联是让 Oracle 自己选择执行计划,我们看到此时 Oracle 选择了使用 T1 表上的索引 进行关联查询。 从两次关联查询的执行计划结果可以看到,当从表 T1 执行全表扫描时(第一条 SQL), Oracle 扫过的数据块(这里是一致性读 consistent gets)是 362 个,而 T1 表走索引时, Oarcle 扫过的数据块是 124 个,所以,此时 T1 表选择使用索引的代价更小一些。 从表执行全表扫描时(第一条 SQL 语句),Oracle 扫过的数据块(这里是一致性读 consistent gets)是 362,而走索引时,Oracle 扫过的数据块是 124 个,索引的代价更小一 些。 在 OLAP 系统中,从表经常会是非常巨大的表,在这种情况下,如果从表没有索引,那么查询 57 / 90 几乎是不可想象的。 第 3 章 Latch 和等待 3.1 共享池中的 Latch 争用 共享池中如果存在大量的 SQL 被反复分析,就会造成很大的 Latch 争用和长时间的等待,最 常见到的现象是没有绑定变量。最常见的几种共享池里的 Latch 是: select * from v$latchname where name like 'library cache%' LATCH# NAME ---------- ------------------------------ 157 library cache 158 library cache pin 159 library cache pin allocation 160 library cache load lock 在分析系统性能时,如果看到有 library cache 这样的 Latch 争用,就可以断定是共享池中 出现了问题,这种问题基本上是由 SQL 语句导致的,比如没有绑定变量或者一些存储过程被 反复分析。 下面是来自 asktom.oracle.com 网站上给出的一个在有绑定变量和不绑定变量情况下,latch 资源争用的对比测试,测试是让一条 SQL 语句执行 10 000 次,然后给出各自的执行过程中产 生的资源使用情况。 SQL>create user test identified by test default tablespace users ; User created. 58 / 90 SQL>grant dba to test; Grant succeeded. SQL> grant select on sys.v_$statname to test; Grant succeeded. SQL> grant select on sys.v_$mystat to test; Grant succeeded. SQL> grant select on sys.v_$latch to test; Grant succeeded. SQL> grant select on sys.v_$timer to test; Grant succeeded. SQL>conn test/test Connected. 59 / 90 SQL> create global temporary table run_stats 2 ( runid varchar2(15), 3 name varchar2(80), 4 value int ) 5 on commit preserve rows; Table created. SQL> create or replace view stats 2 as select 'STAT...' || a.name name, b.value 3 from v$statname a, v$mystat b 4 where a.statistic# = b.statistic# 5 union all 6 select 'LATCH.' || name, gets 7 from v$latch 8 union all 9 select 'STAT...Elapsed Time', hsecs from v$timer; View created. SQL> create or replace package runstats_pkg 2 as 60 / 90 3 procedure rs_start; 4 procedure rs_middle; 5 procedure rs_stop( p_difference_threshold in number default 0 ); 6 end; 7 / Package created. SQL> create or replace package body runstats_pkg 2 as 3 4 g_start number; 5 g_run1 number; 6 g_run2 number; 7 8 procedure rs_start 9 is 10 begin 11 delete from run_stats; 12 13 insert into run_stats 14 select 'before', stats.* from stats; 15 61 / 90 16 g_start := dbms_utility.get_time; 17 end; 18 19 procedure rs_middle 20 is 21 begin 22 g_run1 := (dbms_utility.get_time-g_start); 23 24 insert into run_stats 25 select 'after 1', stats.* from stats; 26 g_start := dbms_utility.get_time; 27 28 end; 29 30 procedure rs_stop(p_difference_threshold in number default 0) 31 is 32 begin 33 g_run2 := (dbms_utility.get_time-g_start); 34 35 dbms_output.put_line 36 ( 'Run1 ran in ' || g_run1 || ' hsecs' ); 37 dbms_output.put_line 38 ( 'Run2 ran in ' || g_run2 || ' hsecs' ); 62 / 90 39 if ( g_run2 <> 0 ) 40 then 41 dbms_output.put_line 42 ( 'run 1 ran in ' || round(g_run1/g_run2*100,2) || 43 '% of the time' ); 44 end if; 45 dbms_output.put_line( chr(9) ); 46 47 insert into run_stats 48 select 'after 2', stats.* from stats; 49 50 dbms_output.put_line 51 ( rpad( 'Name', 30 ) || lpad( 'Run1', 12 ) || 52 lpad( 'Run2', 12 ) || lpad( 'Diff', 12 ) ); 53 54 for x in 55 ( select rpad( a.name, 30 ) || 56 to_char( b.value-a.value, '999,999,999' ) || 57 to_char( c.value-b.value, '999,999,999' ) || 58 to_char( ( (c.value-b.value)-(b.value-a.value)), '999,999,999' ) data 59 from run_stats a, run_stats b, run_stats c 60 where a.name = b.name 61 and b.name = c.name 63 / 90 62 and a.runid = 'before' 63 and b.runid = 'after 1' 64 and c.runid = 'after 2' 65 -- and (c.value-a.value) > 0 66 and abs( (c.value-b.value) - (b.value-a.value) ) 67 > p_difference_threshold 68 order by abs( (c.value-b.value)-(b.value-a.value)) 69 ) loop 70 dbms_output.put_line( x.data ); 71 end loop; 72 73 dbms_output.put_line( chr(9) ); 74 dbms_output.put_line 75 ( 'Run1 latches total versus runs -- difference and pct' ); 76 dbms_output.put_line 77 ( lpad( 'Run1', 12 ) || lpad( 'Run2', 12 ) || 78 lpad( 'Diff', 12 ) || lpad( 'Pct', 10 ) ); 79 80 for x in 81 ( select to_char( run1, '999,999,999' ) || 82 to_char( run2, '999,999,999' ) || 83 to_char( diff, '999,999,999' ) || 84 to_char( round( run1/decode( run2, 0, to_number(0), run2) *100,2 ), '99,999.99' ) || '%' data 64 / 90 85 from ( select sum(b.value-a.value) run1, sum(c.value-b.value) run2, 86 sum( (c.value-b.value)-(b.value-a.value)) diff 87 from run_stats a, run_stats b, run_stats c 88 where a.name = b.name 89 and b.name = c.name 90 and a.runid = 'before' 91 and b.runid = 'after 1' 92 and c.runid = 'after 2' 93 and a.name like 'LATCH%' 94 ) 95 ) loop 96 dbms_output.put_line( x.data ); 97 end loop; 98 end; 99 100 end; 101 / Package body created. SQL> 上面的操作是创建一个测试环境,包括创建用户、相关表,以及一个捕获 SQL 执行中的统计 数据的存储过程。 65 / 90 下面开始做测试对比,先创建一个表 T 用于插入数据: ops$tkyte@ORA9IR2> create table t ( x int ); Table created. 创建第一个存储过程 p1,不使用变量绑定方式执行 SQL 10 000 次: ops$tkyte@ORA9IR2> create or replace procedure p1 2 as 3 l_cnt number; 4 begin 5 for i in 1 .. 10000 6 loop 7 execute immediate 'select count(*) from t where x = ' || i into l_cnt; 8 end loop; 9 end; 10 / Procedure created. 创建第二个存储过程 p2,使用变量绑定方式执行 SQL 10000 次: ops$tkyte@ORA9IR2> create or replace procedure p2 2 as 3 l_cnt number; 66 / 90 4 begin 5 for i in 1 .. 10000 6 loop 7 select count(*) into l_cnt from t where x = i; 8 end loop; 9 end; 10 / Procedure created. ops$tkyte@ORA9IR2> ops$tkyte@ORA9IR2> exec runStats_pkg.rs_start PL/SQL procedure successfully completed. ops$tkyte@ORA9IR2> exec p1 PL/SQL procedure successfully completed. ops$tkyte@ORA9IR2> exec runStats_pkg.rs_middle PL/SQL procedure successfully completed. 67 / 90 ops$tkyte@ORA9IR2> exec p2 PL/SQL procedure successfully completed. ops$tkyte@ORA9IR2> exec runStats_pkg.rs_stop(1000) Run1 ran in 527 hsecs Run2 ran in 53 hsecs run 1 ran in 994.34% of the time Name Run1 Run2 Diff STAT...parse count (hard) 10,001 3 -9,998 STAT...opened cursors cumulati 10,004 5 -9,999 STAT...parse count (total) 10,004 5 -9,999 LATCH.row cache enqueue latch 40,000 4 -39,996 LATCH.row cache objects 40,000 4 -39,996 LATCH.child cursor hash table 80,008 26 -79,982 LATCH.library cache pin alloca 80,027 37 -79,990 LATCH.library cache pin 110,061 20,073 -89,988 LATCH.shared pool 151,968 10,173 -141,795 LATCH.library cache 210,116 20,156 -189,960 Run1 latches total versus runs -- difference and pct Run1 Run2 Diff Pct 68 / 90 775,553 113,629 -661,924 682.53% PL/SQL procedure successfully completed. 测试创建的性能采集包 runStats_pkg 分别在测试开始、存储过程 p1 运行结束、存储过程 p2 运行结束的 3 个时间点采集了性能数据,最后在报告中给出了 2 个存储过程各自的 latch 资 源使用情况及对比情况。从这个结果中可以清楚地看到不绑定变量的 Latch 争用是非常严重 的,请大家注意输出结果中几个 library cache 的 Latch 在数值的差异,可以看到,绑定变 量时要比不绑定变量时这些 Latch 争用小得多。 如果你的数据库存在这几种 Latch 争用,大多数时候要考察你的系统的 SQL 变量绑定情况。 关于如何确定系统中是否存在绑定变量的情况,ASKTOM 网站也提供了一个不错的函数 remove_constans()来检查共享池中的 SQL 的运行情况。 首先创建一个表,用于存放整理过的数据: create table t1 as select sql_text from v$sqlarea; 给表增加一个字段: alter table t1 add sql_text_wo_constants varchar2(1000); 创建函数 remove_constants: create or replace function remove_constants( p_query in varchar2 ) return varchar2 as l_query long; l_char varchar2(1); l_in_quotes boolean default FALSE; 69 / 90 begin for i in 1 .. length( p_query ) loop l_char := substr(p_query,i,1); if ( l_char = '''' and l_in_quotes ) then l_in_quotes := FALSE; elsif ( l_char = '''' and NOT l_in_quotes ) then l_in_quotes := TRUE; l_query := l_query || '''#'; end if; if ( NOT l_in_quotes ) then l_query := l_query || l_char; end if; end loop; l_query := translate( l_query, '0123456789', '@@@@@@@@@@' ); for i in 0 .. 8 loop l_query := replace( l_query, lpad('@',10-i,'@'), '@' ); l_query := replace( l_query, lpad(' ',10-i,' '), ' ' ); end loop; return upper(l_query); end; 70 / 90 / 下面是如何使用这个函数。 将 v$sql 视图中的数据用 remove_constants 处理后,更新到 t1 表中: update t1 set sql_text_wo_constants = remove_constants(sql_text); 查出除了谓词条件不同的 SQL 语句和它们的执行次数,在这里是查询 SQL 没有被重用超过 100 次的 SQL 语句: select sql_text_wo_constants, count(*) from t1 group by sql_text_wo_ constants having count(*) > 100 order by 2 / 以下是一个测试的例子,我们使用一个循环执行 1000 次某条 SQL,每次执行时只有谓词不 同: SQL> ed 1 begin 2 for i in 1..1000 loop 3 execute immediate 'select * from t where rm='||i; 4 end loop; 5* end; SQL> / PL/SQL procedure successfully completed. SQL> select sql_text_wo_constants, count(*) 71 / 90 2 from t1 3 group by sql_text_wo_constants 4 having count(*) > 100 ---可以修改成你希望的次数 5 order by 2 6 / SQL_TEXT_WO_CONSTANTS --------------------------------------------------------------------- COUNT(*) ---------- SELECT * FROM T WHERE RM=@ 1000 可以看到输出结果中,这条语句被执行了 1000 次,其中谓词条件被“@”代替,这样通过这 个函数,可以很容易地找到共享池中哪些 SQL 没有绑定变量。 3.2 数据缓冲池 Latch 争用 访问频率非常高的数据块被称为热块(Hot Block),当很多用户一起去访问某几个数据块 时,就会导致一些 Latch 争用。最常见 Latch 争用是: ● buffer busy waits ● cache buffer chain 72 / 90 这两个 Latch 的争用分别发生在访问数据块的不同时刻。 当一个会话需要去访问一个内存块时,它首先要去一个像链表一样的结构中去搜索这个数据 块是否在内存中,当会话访问这个链表时需要获得一个 Latch,如果获取失败,将会产生 Latch cache buffer chain 等待,导致这个等待的原因是访问相同数据块的会话太多或者这 个列表太长(如果读到内存中的数据块太多,需要管理数据块的 hash 列表就会很长,这样会 话扫描列表的时间就会增加,持有 cache buffer chain Latch 的时间就会变长,其他会话获 得这个 Latch 的机会就会降低,等待就会增加)。 当一个会话需要访问一个数据块,而这个数据块正在被另外一个用户从磁盘读取到内存中或 者这个数据块正在被另一个会话修改时,当前的会话就需要等待,就会产生一个 buffer busy waits 等待。 产生这些 Latch 争用的直接原因是太多的会话去访问相同的数据块导致热块问题,造成热块 的原因可能是数据库设置导致或者是重复执行的 SQL 频繁访问一些相同数据块导致。 热块产生的原因不尽相同,按照数据块的类型,可以分成以下几种热块类型,不同热块类型 处理的方式都是不同的: ● 表数据块 ● 索引数据块 ● 索引根数据块 ● 文件头数据块 3.2.1 表数据块 比如在 OLTP 系统中,对于一些小表,会出现某些数据块被频繁查询或者修改的操作,这时候 这些被频繁访问的数据块就会变成热块,导致内存中 Latch 的争用。 如果出现这样的热块情况,并且表不太大,一个方法是可以考虑将表数据分布在更多的数据 块上,减少数据块被多数会话同时访问的频率。 可以通过下面的命令将每个数据块存放记录的数量减到最少: alter table minimize records_per_block; 请看下面的例子,创建两个数据量相同的表,让一个表上每个数据块存放记录数尽可能地 小,另一个表保持默认的情况,来看数据块访问情况: 73 / 90 SQL> create table t as select * from dba_objects; Table created. SQL> create index t_inx on t(object_id); Index created. SQL> exec dbms_stats.gather_table_stats(user,'t',cascade=>true); PL/SQL procedure successfully completed. SQL> select 'T' tbl_name, rows_per_block, count(*) number_of_such_blocks from ( 2 select dbms_rowid.ROWID_BLOCK_NUMBER( rowid ), count(*) rows_per_block 3 from t 4 group by dbms_rowid.ROWID_BLOCK_NUMBER( rowid )) 5 group by 'T', rows_per_block; SQL> / TB ROWS_PER_BLOCK NUMBER_OF_SUCH_BLOCKS -- -------------- --------------------- T0 77 20 T0 71 104 74 / 90 T0 74 14 T0 60 1 T0 86 2 T0 88 1 T0 82 19 T0 81 21 T0 94 1 T0 68 52 T0 72 63 TB ROWS_PER_BLOCK NUMBER_OF_SUCH_BLOCKS -- -------------- --------------------- T0 92 2 T0 84 7 T0 93 1 T0 69 84 T0 80 19 T0 73 35 T0 67 11 T0 64 1 T0 91 1 T0 38 1 T0 79 20 75 / 90 TB ROWS_PER_BLOCK NUMBER_OF_SUCH_BLOCKS -- -------------- --------------------- T0 70 146 T0 75 14 T0 83 13 T0 87 2 T0 65 1 T0 78 19 T0 76 22 T0 85 1 T0 55 1 31 rows selected. SQL> 可以看到 T 表上每个数据块平均存放行数为 70 行左右: SQL> create table t1 as select * from dba_objects where rownum<3; Table created. SQL> select 'T' tbl_name, rows_per_block, count(*) number_of_such_blocks from ( 2 select dbms_rowid.ROWID_BLOCK_NUMBER( rowid ), count(*) rows_per_block 76 / 90 3 from t 4 group by dbms_rowid.ROWID_BLOCK_NUMBER( rowid )) 5 group by 'T', rows_per_block; 6 / TB ROWS_PER_BLOCK NUMBER_OF_SUCH_BLOCKS -- -------------- --------------------- T0 2 1 SQL> alter table t1 minimize records_per_block; Table altered. SQL> insert into t1 select * from dba_objects; 50833 rows created. SQL> commit; Commit complete. SQL> create index t1_inx on t1(object_id); 77 / 90 Index created. SQL> exec dbms_stats.gather_table_stats(user,'t',cascade=>true); PL/SQL procedure successfully completed. SQL> select 'T' tbl_name, rows_per_block, count(*) number_of_such_blocks from ( 2 select dbms_rowid.ROWID_BLOCK_NUMBER( rowid ), count(*) rows_per_block 3 from t 4 group by dbms_rowid.ROWID_BLOCK_NUMBER( rowid )) 5 group by 'T', rows_per_block; 6 / TB ROWS_PER_BLOCK NUMBER_OF_SUCH_BLOCKS -- -------------- --------------------- T1 2 25297 T1 表上将每个数据块上存放的记录数缩小为 2 条,这样 T1 表上有数据的数据块数量要远远多 于 T 表。 SQL> set autotrace traceonly; SQL> select * from t where object_id<1000; 已选择 953 行。 执行计划 78 / 90 ---------------------------------------------------------- Plan hash value: 1579008347 ---------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost(%CPU) | Time | ---------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 961 | 89373 | 18 (0) | 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID | T | 961 | 89373|18 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | T_INX | 961 | | 4 (0) | 00:00:01 | ---------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"<1000) 统计信息 ---------------------------------------------------------- 1 recursive calls 0 db block gets 143 consistent gets 0 physical reads 0 redo size 91544 bytes sent via SQL*Net to client 79 / 90 1078 bytes received via SQL*Net from client 65 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 953 rows processed SQL> SQL> select * from t where object_id<1000; 已选择 955 行。 执行计划 ---------------------------------------------------------- Plan hash value: 1311207630 ----------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost(%CPU) | Time | ---------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 955 | 165K | 506 (1) | 00:00:07 | | 1 | TABLE ACCESS BY INDEX ROWID|T1 | 955 |165K| 506(1) | 00:00:07 | |* 2 | INDEX RANGE SCAN | T1_INX | 955 | | 4 (0) | 00:00:01 | ---------------------------------------------------------------------- 80 / 90 Predicate Information (identified by operation id): ---------------------------------------------------------- 2 - access("OBJECT_ID"<1000) 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 620 consistent gets 0 physical reads 0 redo size 91698 bytes sent via SQL*Net to client 1078 bytes received via SQL*Net from client 65 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 955 rows processed SQL> 当我们分别从 T,T1 表中选出 1000 条数据时,可以看到两个表的执行计划是相同的,返回的 结果集是相同的,但 T1 表的一致性读的数量是 620,要比 T 表 143 大出很多,原因就是 T1 表 中的行分布在更多的数据块上,导致 Oracle 需要读取更多的数据块来完成查询。从另一个角 度看,我们把数据分布到了更多的数据块上,大大降低了一个数据块被重复读取的概率。 81 / 90 但是这种方式的缺点是显而易见的,就是降低了数据的性能,在这种情况下,访问相同的数 据意味着需要读取更多的数据块,性能会有所降低。 不过如果存在这样的情况,比如很多用户只修改 1 条记录并且各自更新的数据不同,那么这 样做的好处就非常明显。在 T 表中,一个数据块上大约有 70 条记录,就是说很可能 70 个用 户会同时访问一个数据块,这必然导致热块的出现;当我们让每个数据块只存放 2 条记录 时,最多只会有 2 个用户会同时访问一个数据块,这样热块的概率将会被极大地降低。 3.2.2 索引数据块 这样的情况通常发生在一个 RAC 架构里,某个表的索引键值呈现出典型的“右倾”现象,比 如一个表的主键使用一个序列来生成键值,那么这个主键在索引数据块上的键值就是以一种 顺序递增的方式排列的,比如 1,2,3,4,5,„,由于这些键值分布得非常接近,当许多用户在 RAC 的不同实例来向表中插入主键时,就会出现相同的索引数据块在不同实例的内存中被调 用,形成一种数据块的争用,对于这样的情况,使用反向索引可以缓解这种争用。反向索引 是将从前的索引键值按照反向的方式排列,在正常的主键 B-TREE 索引中,键值会按照大小顺 序的排列,比如这样: 1234 1235 1236 1237 而反向索引之后,键值变成下面的样子: 4321 5321 6321 7321 这样,本来是放在相同的索引数据块上的键值,现在分布到不同的数据块上,这样用户在 RAC 不同的实例上插入的主键值因为分布在不同的数据块上,所以不会导致热块的产生,这基本 上是反向索引被使用的唯一情况。 反向索引使用场合之所以如此受限,是因为它丢弃了 B-TREE 索引的一个最重要的功能: 批注 [U1]: 看到此 82 / 90 INDEX RANGE SCAN 索引访问方式中,这个方式最为常见,但是反向索引却不能使用这个功能,究其原因就是, 反向索引已经把键值的排列顺序打乱,当按照键值顺序地查找一个范围时,在反向索引中, 由于键值被反向存储,这些值已经不是连续存放的了。所以 INDEX RANGE SCAN 的方式在反向 索引中没有任何意义。看下面的例子: SQL> create table t as select rownum id from dba_objects; 表已创建。 SQL> create index t_inx on t(id); 索引已创建。 SQL> exec dbms_stats.gather_table_stats(user,'t',cascade=>true); PL/SQL 过程已成功完成。 SQL> create table t1 as select rownum id from dba_objects; 表已创建。 SQL> create index t1_inx on t1(id) reverse; 83 / 90 索引已创建。 SQL> exec dbms_stats.gather_table_stats(user,'t1',cascade=>true); PL/SQL 过程已成功完成。 SQL>SQL> set autotrace trace exp; SQL> select * from t where id >12340 and id<12350; 执行计划 ---------------------------------------------------------- Plan hash value: 2152055767 ---------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost(%CPU) | Time | ---------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10 | 40 | 2 (0) |00:00:01 | |* 1 | INDEX RANGE SCAN | T_INX | 10 | 40 | 2 (0) |00:00:01 | ---------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("ID">12340 AND "ID"<12350) 84 / 90 SQL> select * from t1 where id >12340 and id<12350; 执行计划 ---------------------------------------------------------- Plan hash value: 3617692013 ---------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost(%CPU) |Time | ---------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10 | 40 | 22 (10) |00:00:01 | |* 1 | TABLE ACCESS FULL | T1 | 10 | 40 | 22 (10) |00:00:01 | ---------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("ID"<12350 AND "ID">12340) SQL> select /*+ index(t1,t1_inx) */ * from t1 where id >12340 and id<12350; 执行计划 ---------------------------------------------------------- Plan hash value: 3995001570 ---------------------------------------------------------------------- 85 / 90 | Id | Operation | Name | Rows | Bytes | Cost(%CPU) |Time | ---------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10 | 40 | 114 (3) |00:00:02 | |* 1 | INDEX FULL SCAN | T1_INX | 10 | 40 | 114 (3) |00:00:02 | ---------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("ID"<12350 AND "ID">12340) 我们看到,对于一个在实际应用中非常常见的条件范围的查询,在反向索引中却只能够通过 全表扫描或者全索引扫描的方式来实现,这就是反向索引的一个非常严重的缺陷,所以在生 产环境下使用它时要十分慎重。 3.2.3 索引根数据块 热块也可能发生在索引的根数据块上。我们知道在 B-TREE 索引里,当 Oracle 访问一个索引 键值时,首选访问索引的根,然后是索引的分支,最后才是索引的叶块,索引的键值就是存 储在叶块上面,图 3-1 是一个 B-TREE 索引示意图。 图 3-1 B-TREE 索引示意图 当索引的根、枝数据都集中在几个数据块上时,比如 D,G 所在的枝数据块,当用户访问的范 围从 A~L 时,都会访问这个数据块,如果很多用户频繁地访问这个范围的索引键值,有可能 86 / 90 导致这个枝数据块变成热块。 当出现这种现象时,可以考虑对索引做分区,以便于使这些根、枝数据块分布到不同的数据 段(分区)上,减少数据块的并行访问的密度。 SQL> select partition_name from user_tab_partitions where table_name='T'; PARTITION_NAME ------------------------------ P1 P10 P11 P12 P2 P3 P4 P5 P6 P7 P8 P9 PM 已选择 13 行。 SQL> select partition_name from user_tab_partitions where table_name='T1'; 87 / 90 未选定行 SQL>alter session set sql_trace=true; 会话已更改。 SQL> select /*+ index(t1 t1_ind) */ * from t1 where x<10000; ...... SQL> select /*+ index(t t_ind) */ * from t where x<10000; ...... SQL>alter session set sql_trace=false; 会话已更改。 我们创建两个数据完全相同的表,其中一个为包含 13 个分区的分区表,另一个为非分区表, 然后指定一个相同的查询范围,并强制 CBO 使用索引,然后比较对于访问相同的索引键值, 扫过的数据块的多少。 下面是 SQL_TRACE 的输出结果。 ********************************************************************** select /*+ index(t1 t1_ind) */ * from t1 where x<10000 88 / 90 call count cpu elapsed disk query current rows ----- ----- ---- ------- ----- ------- ------- ----- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 668 0.02 0.01 0 687 0 9999 ----- ----- ---- ------- ----- ------- ------- ----- total 670 0.02 0.01 0 687 0 9999 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 55 Rows Row Source Operation ------- --------------------------------------------------- 9999 INDEX RANGE SCAN T1_IND (cr=687 pr=0 pw=0 time=30069 us)(object id 51797) ********************************************************************** select /*+ index(t t_ind) */ * from t where x<10000 89 / 90 call count cpu elapsed disk query current rows ----- ----- ---- ------- ----- ------- ------- ------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 668 0.02 0.01 0 707 0 9999 ----- ----- ---- ------- ----- ------- ------- ------- total 670 0.02 0.01 0 707 0 9999 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 55 Rows Row Source Operation ------- --------------------------------------------------- 9999 PARTITION RANGE ITERATOR PARTITION: 1 10 (cr=707 pr=0 pw=0 time=60106 us) 9999 INDEX RANGE SCAN T_IND PARTITION: 1 10 (cr=707 pr=0 pw=0 time=30339 us)(object id 51774) 从结果中可以看到,T1 表上的 SQL 扫过了 687 个索引数据块,而 T 表扫过了 707 个索引块,T 表索引的访问方式为分区索引的范围扫描。尽管两个 SQL 扫过相同的索引键值,但是 T 表却 比 T1 表多扫过 20 个数据块,原因就是 T 的索引分布在不同的分区上,Oracle 需要多次通过 索引根数据块才能访问到键值,所以就会多出一些数据块来。 上面的例子说明了当把一个索引创建为分区索引时,索引的根数据块会分布到更多的数据块 90 / 90 上,通过这种方式,可以避免由于索引根、枝数据块太集中导致热块产生。 3.2.4 段头数据块 在 Oracle 9i 之前,数据块的空间使用情况需要手工来管理,在每个数据段的段头有个(或 者几个)Free List 列表,用于存放段中哪些数据块可以使用。当数据块里面的数据达到数据 块总容量的一个比例时,数据块就会从 Free List 列表中剔除,这个数据块就不能够再插入 数据。这个比例由参数 PCT_FREE 来确定。当数据块的空间减少到一个比例时,数据块就会被 放到 Free List 列表中,这些数据块可以被用来插入数据。这个比例由参数 PCT_USED 确定。 在 OLTP 系统数据库中,一些数据段的 Free List 可能会是一个访问很频繁的数据块,比如这 个数据库上有些表有很多删除、插入的动作,很多会话需要不断访问这些数据块的 Free List 列表,以便获得需要的数据块信息。此时这个数据块(称作段头数据块)就会变成一个热 块,此时内存中就会出现比如 cache buffer chain 这样的 Latch 等待事件;当出现这个问题 时,一个常见的解决方法是增加 Free List 的数量,以便于分散会话访问数据块的密度,比 如有 10 个用户并行地向一个表中插入数据,我们设置 Free Lists 的值为 10,就可以避免出 现等待,这在一定程度上能够缓解段头的热块问题。 从 Oracle 9i 开始,引入了一个自动段管理的技术 ASSM(Automatic Segment Space Management,ASSM),它让 Oracle 自动管理“Free Lists”。实际上在 ASSM 里面,已经没 有 Free List 这样的列表结构,Oracle 使用位图方式来标记数据块是否可用,这种数据块的 空间管理方式比用一个列表来管理更加高效。关于位图的概念,可以参考 Oracle 的官方文档 Concept。 对于一个 OLTP 系统,表的 DML 操作非常密集,对于这些表,使用 ASSM 方式来管理会比人工 管理更加方便和准确,能有效地避免段头变成热块;而对于 OLAP 系统数据库,这个参数并没 有太大的实际意义,因为在这样的数据库中,很少有表发生频繁修改,OLAP 系统数据库主要 的工作是报表和海量数据的批量加载。

下载文档,方便阅读与编辑

文档的实际排版效果,会与网站的显示效果略有不同!!

需要 15 金币 [ 分享文档获得金币 ] 27 人已下载

下载文档

相关文档