|
让Oracle跑得更快——Oracle 10g性能分析与优化思路 引起数据库性能问题的因素 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的执行计划，它的样子大概是这样： 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的SQL选择了全表扫描（Full Table Scan），执行中扫过的数据块远远小于通过索引访问数据的SQL，于是原因找到了。 可是为什么之前没有出现过这个问题？我对比了一下最近的数据和之前的数据，发现近期的数据在创建索引的列上的列值重复率要远远高于从前，因此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 from dba_users where rownum<10;
表已创建。
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 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条记录的分页查询： 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执行一遍之后就被缓存到数据库的内存当中（实际上是在共享池里），以后的成百上千的用户请求都使用这个SQL解析后的结果，那效率将有多么大的提高！ 所以，我的观点是，当你要考察绑定变量对你的数据库的影响有多大时，先确定你的系统是OLTP系统或是OLAP系统；当然，现在很多数据库同时担负这两种角色，那么你需要分析数据库的性能情况，比如，做一个Statspack Report来帮助你确定变量是否绑定，以及是否已经对系统的性能构成严重的影响。 1.1.2.5 PL/SQL包 如果你的程序里面有PL/SQL包，请考虑使用存储过程来代替它，存储过程是经过成功编译后存放在数据库中的代码，执行起来的效率要比程序代码中PL/SQL包的效率高很多，因为它不再需要做语法和语义的分析（语法的分析指的是数据库对代码进行检查，看它是否存在语法上的错误；而语义分析是查看语句执行的对象是否存在，比如需要操作的表、列等，以及是否有执行这些操作的权限）。 |
|||||
|
|||||
|
数据库的设计在系统设计当中是一个非常重要的环节，但目前看来，很多开发商忽略了它应有的重要性，大多数的数据库设计基本上等同于创建业务所需要的所有对象，仅此而已。这是作者从事了10年DBA工作的切身体会，也许这也不能全怪开发商，比如他们有工期的压力，有人员成本的压力，那应该是另外一个话题。 对于数据库的设计，我认为除了一些必需的对象创建之外，应该还要更多地考虑在整个系统运行的生命周期中，按照系统的实际情况及可能的变化做一些前瞻性的设计，以基本满足系统生命周期里的各方面需求，不至于发生大的修改或是升级。 说起系统的升级，这是一个有趣的话题，可能很多开发人员，特别是做项目开发的人员，应该会深有体会。比如我身边的一些案例，明明是最初设计上存在着缺陷或者疏漏，导致后来系统出了问题，却成了开发商项目的二期、三期的理由，也成为软件1.0版、2.0版的理由。 基本上看来，前期数据库设计的一个根基就是要弄清楚数据库的类型。通常来说，我们把业务分为两类，在线事务处理系统（OLTP）和在线分析系统（OLAP）或者DSS（决策支持系统），这两类系统在数据库的设计上是如此不同，甚至有些地方的设计是貌似相悖的。比如OLTP系统强调数据库的内存效率，强调内存各种指标的命中率，强调绑定变量，强调并发操作；而OLAP系统则强调数据分析，强调SQL执行时长，强调磁盘I/O，强调分区等。因为这些区别，在数据库设计的阶段，弄清楚数据库类型是至关重要的，只有在这个前提之下，才能够讨论数据库的具体设计，否则设计必然是盲目的，“皮之不存毛将焉附”。 1.2.1 OLTP数据库 OLAP和OLTP是两类完全不同的系统，对数据库的要求也截然不同。通常来讲，OLTP（在线事务处理系统）的用户并发数都很多，但他们只对数据库做很小的操作，数据库侧重于对用户操作的快速响应，这是对数据库最重要的性能要求。我清楚地记得在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的优化显得非常重要，试想，如果一张表中只有几千条数据，无论执行全表扫描或是使用索引，对我们来说差异都很小，几乎感觉不出来，但是当数据量提升到几亿或者几十亿甚至更多的时候，全表扫描、索引可能导致极大的性能差异，因此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) 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)
**********************************************************************
我们看到，在这个只有几万条记录的表中，相同的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 —- —— ———————————— 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 —- —— ———————————— 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 —- —— ———————————— 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 —- —— ———————————— 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环境中，分区主要的功能是管理上的方便性，它并不能绝对保证查询性能的提高，有时候分区会带来性能上的提高，有时候甚至会降低，就像我们在例子中看到的一样。 |
|||||
|
|||||
|
数据库的硬件设计在性能上主要体现在： lCPU lI/O l 负载情况
这些指标需要对业务进行综合评估和系统测试之后，做出一个合理的硬件配置清单。 数据库的硬件设计包含了数据库服务器的架构和数据存储。这些因素在数据库设计阶段将作为重点的考虑因素。如果当系统上线之后，出现冗余或者空间不足的问题，将是一件非常麻烦的事情。 数据的存储和安全应该主要考虑以下几个问题。 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);
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
我们取得了测试表中表和索引的大小，计算出索引和表大小的百分比，这样，我们就获得了最终这个表的空间使用量为： 表的总使用量 = 93 bytes*N(1+16)
这就是预期这个表的容量，如果能够预测出未来数据库的数据量，我们就可以比较客观地估算出数据库预期的容量大小。 另外一个容易被开发商忽略的问题是对系统备份数据占用空间的考虑。我遇到的一些系统就有这个问题，系统上线之后才发现，设计人员根本就没有考虑过系统备份的问题，也没有预留出足够的空间来做数据备份，给DBA带来的压力相当大。为了保证数据的安全，我设计了一套备份方案，由于磁盘的空间有限，在备份新的数据的同时，还需要及时删除一些旧的备份数据，在这种磁盘空间捉襟见肘的空隙下工作，DBA有时候觉都不能睡安稳。 1.3.2 存储的物理设计 现在越来越多的大数据量数据库选择了SAN存储结构（参见图1-2），这是一个扩展性非常好的存储设计，它可以非常方便地将存储设备增加到存储网络当中，但成本和故障点相应地就会变多，对维护人员的技术要求就很高，它不但要求维护人员懂得磁盘阵列的技术，还要掌握SAN交换机的相关技术。
图1-2 SAN存储架构 1.3.3 数据的安全 数据的安全是系统设计阶段应该充分考虑好的一个问题，要按照用户对数据安全级别要求的高低，以及运行业务停止的时长来设计数据库的安全解决方案。 大致来讲，用户对数据安全的要求有如下几个层次。 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结构
图1-4 RAC结构 1.3.3.3 Rman+归档的方式 Rman+归档的备份方式相对RAC+Data Guard来看，它的优势在于成本上要廉价，并且能够保证数据的完整，当数据库损坏时，如果我们保留有完整的备份集和归档日志，就可以将数据库恢复到最后出现故障时的时间点。缺点是需要较长的宕机时间。 1.3.3.4 数据导出方式（exp/imp,exdp/imdp） 这两种数据导入导出方式更像是一种数据传递或者数据保存，它不能保证数据的安全。 |
|||||
|
|||||
|
1. 系统的数据库类型，OLAP还是OLTP 弄清楚系统是OLAP或是OLTP是一件非常重要的事情，它将影响到数据库所有的相关设置，不论是内存参数、存储参数还是性能参数，可以说是系统设计阶段数据库设计最优先考虑的事情。 2. 系统并发量 如果是一个OLTP系统，并发将作为非常重要的一个因素考虑，如果设计阶段没有对系统预期的并发数做出准确的估算，将会出现非常严重的后果，比如前面说的那个门票销售系统的例子。 高的并发数可能导致这样两个严重的后果： ●系统资源严重被使用，系统过负荷运行。 ●严重的等待事件，比如前面讲到的热块以及锁定等情况。 3. SQL代码的编写 性能低劣的SQL对数据库的杀伤力是巨大的。开发人员，特别是数据库接口的开发人员，应该好好学习一下SQL的开发技巧，它不但包括SQL功能的实现，还有SQL语句的优化性。可是很遗憾的是，很多开发人员忽略了SQL的开发技巧。 4. 数据库的设计 数据库本身的设计在系统设计中也是至关重要的，因此我更推崇让DBA来参与或者执行这个工作，因为DBA会更关注数据库自身的问题，会让数据库变得更可用。 5. 存储的设计 在系统设计阶段，一定要预测系统预期的数据使用空间，否则以后数据库扩容将是一件非常麻烦的事情，特别是那些不允许宕机的系统，尤其应该将各种因素考虑清楚，最大可能地消除将来由于系统自身设计的问题导致的隐患。 |
|||||
|
|||||
|
锁这种东西，对于初学者来说就像是一个深不可测、遥不可及的概念。我的经验告诉我，如果初学者冲上来就去纠结Oracle各种锁的概念、锁的类型、锁的级别，试图用最快的方式来理解锁的含义，基本上都会感到痛不欲生，至少我当时的心情大致如此。 随着工作时间的变长，我慢慢地养成了一种和从前不太一样的思维方式。比如现在我们在谈锁，以前的方法是，首先会去找文档来看什么是锁，费力不说，到底也没有搞清楚；而现在的思维方式转变了，开始学会用一种轻松、主动的方式来思考问题，于是我首先会去想，为什么会有锁？没有它会怎么样？ 我必须要向大家推荐后面一种思考方式的好处。这是一种启发式的思考模式，是主动的，所以它是快乐的。而前一种是消极的，被动的，同时也是痛苦的。我就曾经在这样的痛苦中摸索了很久，希望大家不要重蹈我的覆辙。 为了说明这样的一种思维方式，我举一个例子，想必大家都切身感受过。 中学时候学万有引力定律时，老师上来就告诉我们，世间万物是相吸的，并由牛顿发现了它，下面是万有引力定律的公式……于是大家机械地听着，记着，背着。坦白地说，这样被动接受的感觉很一般，但至少还不太痛苦，因为我们还能听懂。大家试想一下，万有引力是多么美好的东西啊？如果我们托着下巴，呆呆地坐在窗户前面想，为什么地球就要绕着太阳转呢？为什么不会飞走呢？如果有一天飞走了，我们怎么办呢？这看起来是在胡思乱想，比起课堂老师上来就捞干的说要低效得多，但是往往就是在这种低效中，我们获得了任何人都不能给予的东西，我们通过自己的思维推理，得出了这个结论：太阳对地球是有吸引力的。这是我们主动获得的东西，我们充满了成就感和自豪感。 前面扯了一些看似不着边际的话，在我看来非常重要，这也是我想通过这本书想表达的另一个目的，就是学会思考。它比我直接告诉你锁是什么要重要得多，它希望你能够像思考地球为什么围绕着太阳转一样，托着下巴来思考Oracle数据库为什么会发明一个锁，这不是扯淡。 让我们一起来思考这个问题吧，如果你还不太习惯这种思考方式的话。为什么Oracle数据库中会有锁呢？设想，如果世界上只有你一个人，你有一箱子金币，你会弄把锁头来把它锁起来吗？不会，因为如果世界上只有你一个人，根本就不会有锁头这种东西出现，因为没有其他高等生物会去打开它，除了你。你可能会问，举一个如此粗鄙的例子给我们描述锁，太不把Oracle放在眼里了吧？其实不是这样的。Oracle的锁和这个锁头一样吗？答案是一样的，就是因为受现实生活中的锁头的启发才取了这个名字（lock），所以你看，它神秘吗？一点都不神秘，那不过是一层窗户纸，只需要我们的轻轻一捅。 继续我们的推理，现实生活中因为存在着其他想得到箱子里金币的人，你为了防止他们来拿，所以上了锁。在数据库中也存在着类似的事情，如果一个数据库只有你一个人用，我绝对不相信Oracle会费这么大的劲开发出一个锁来捣乱，所以，我们的答案找到了，因为还有其他的人在使用这个数据，你为了防止他们把你做的事情弄糟，所以在你对数据操作的时候，拒绝他们操作！简单吧？是不是还有点高兴？我们就在说说笑笑的过程中了解了锁的渊源。 可能有人跳出来说，你说了半天只是说了一个锁，锁分很多种啊，表级锁、行级锁、共享锁、排他锁……你再来用你的快乐法推啊！ 那好，我们再推，我们这回从哲学上来推。哲学真是一个非常高深莫测的学科，我一直觉得只有一些绝顶聪明的人才能学懂哲学，我是学不懂的，只能引用他们发明的一些话。比如现在我引用这条“存在即合理”。就是说，Oracle的老板不是笨蛋，没必要花高昂的费用雇人写一些没有意义的代码。那么，对于一个公司来说什么叫有意义？能满足用户的需求即为有意义，好了，答案找到了，用户A的系统需要用到表级锁，Oracle公司为了把数据库卖给他，所以开发了表级锁，用户B需要用到行级锁，所以Oracle为他们开发了行级锁…… 做技术的人，总想把所有的技术都学会，之前我也曾这么学过，后来发现学完之后很快就忘了。比如最开始Oracle推出RAC的时候，我们费尽千辛万苦搭好了一个环境，开始很激动，整天在上面打命令。可是没持续多久，就没人用了，原因是大家不知道该做什么了，该做的都做完了。看起来是该会的都会了，其实是什么都还不会，比如说RAC对系统的影响究竟有多大？什么样的系统需要用RAC？RAC在资源分配上需要怎样的配置才合理？一堆的疑问摆在我的面前，我发现我什么都没学会，因为我没有认真思考Oracle为什么会发明一个RAC。 一年后，每个人几乎把所做的测试统统忘光了，直到我们的系统中真正用到了RAC，才开始有目的地研究起来，那时候故障不断，一个月的时间锻炼要强过以前好几个月的自学。 说这个例子是什么意思呢？就是要说，很多概念或者技术，看起来很深奥，那是因为你没有用到，当你用到的时候，你会觉得它无非是满足用户需求的一个产品，仅此而已。上面讨论的锁也一样，它对于你很陌生，说明你现在没有用到它，还不需要它，当你需要它的时候，你会发现它正好能满足你的需求，那时候不要说理解，基本上就是拿来就可以用了。很多人学了无数年的经济，却比不上一个几乎没有读过书的人懂得做生意，因为前者在漫无目的地机械地学，后者在为了生存下去而不断地思考。这就是区别。 |
|||||
|
|||||
|
关于并发（concurrency）这个词，请注意区别另外一个词，并行（parallel），在英语中这本是两个完全不同的单词，但汉语却把它们弄得有点相似。在数据库中，并发的意思是说有超过两个以上的用户对同样的数据做修改（可能包括插入、删除和修改），而并行的意思是说将一件事情分成很多小的部分，让每一部分同时执行，最后将执行结果汇总成最终结果（关于并行后面有专门的一章来介绍）。 你如果还记得前面说到锁的问题的话，应该已经猜到我想说什么了。是的，没有并发，就没有锁，锁的产生是因为并发，并发的产生是因为系统需要，系统需要是因为用户需要，这样，又回到我们之前引用的哲学观点了—存在即合理。 下面是一个最简单的并发导致锁定的例子。 我们开一个会话session 1，注意，以后我们再举例子的时候大致都会这么来说，它的实际过程是，你要和数据库建立起一个连接，方式是可以使用第三方软件，或者直接使用SQLPLUS来连接。 SQL> select sid from v$mystat where rownum=1;
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 ———- 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部分。 现在我们是通过正向的方式模拟了阻塞的情况，在现实的例子中，情况通常是用户（或者也有开发人员）告诉我们，系统卡住了，不动了，那么我们就应该逆着这个过程来寻根溯源。 首先要下手的就是查看这张视图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 —————————————————————- WORKGROUPALAN WORKGROUPALAN
因为这是我自己的试验机器，所有两个session的machine名字是一样的，否则应该是每个用户所使用的机器名称。 这里面还有一种情况，就是如果你的系统是一个多层结构，用户是通过一个中间件共享池来连接到数据库中的，那么这里面的机器名字将是你的中间件服务器的名字，这种情况就有一点小麻烦，你可能要借助一些其他的办法来确定用户的具体信息（比如借助中间件服务提供的连接信息）。 这样我们通过这种方式反推出系统出现问题的环节，当然，这个过程并不能叫做性能优化，充其量只是一个故障定位（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));
表已创建。
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
我们创建了一个分区表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尽可能地减少锁影响的范围，在这里，由于事务涉及的数据没有影响到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操作（在例子里是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行。
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的值和上面依然一样，这说明这个事务锁并非属于某些行，实际上它只属于一个事务，无论这个事务做了多少操作，都只是这一个事务锁，和它所操作的数据多少无关。 这个行级锁的称谓有误导人的嫌疑，其实它的真正意思是，这个事务锁会在行级对数据产生影响，比如说阻塞。还记得前面的例子吗？在一个主键表中，当我更新一个主键的键值时，就会产生一个TX事务锁，它阻止其他人对这一行的更新。因为它体现在对表的具体行的操作，因此称为行级锁，但我更喜欢称它为事务锁，这样理解起来要容易得多。 另外，TX并非都是在对数据修改的时候才会产生，只要需要维护事务一致性的时候，就需要用到这个锁。比如分布式事务，一般来说就是通过dblink在几个数据库中处理数据，因为是分布式的操作方式，所以需要一个事务的总协调者，这时候即使都是读取操作，依然需要一个TX锁来维护事务的一致性。 同样，TM锁也并非是加在表上的一个锁，它会在表的级别上产生影响，比如它不允许其他用户对表做DDL操作，其实这不过是一个意向的问题，而并非是锁存在的方式。再重复一遍，Oracle中锁的信息是数据块的一个属性，是物理的，并不是逻辑上属于某个表或者某个行。 可能有些读者还注意到了一个细节，就是我没有介绍V$LOCK视图中锁类型为TX时id1列和id2列的含义。这里面是有个原因的，因为我想让大家的思想始终围绕性能这个话题，而不要跑题，要知道，Oracle的每一个技术都可以弄出一长篇的东西来做研究，若是每一个细节都要拿出来讨论的话，那么这本书就基本上和一个技术手册没有区别了。 顺便带一句，在TX锁中，这两个字段构成了这个事务在回滚段中的位置。这有什么用呢？它的用处是，当其他的操作需要读取这个数据块时，它会发现这个块上要读取的数据上是有活动事务的，因此需要到回滚段中去找它之前的内容，那么这些内容在回滚段的什么地方呢，这两个值就告诉了你。 而对于TM锁，ID1值就是加锁的段对象，可以是一个表，也可以是表的一个分区，此时ID2一般为0。 在实际生产环境中，这种由于唯一性约束导致数据阻塞的情况还是比较多见的，上面的例子提供了一种方式来定位这种故障。 |
|||||
|
|||||
|
除了上面讨论的由于唯一约束引起的阻塞之外，在生产环境中还经常会遇到下面两种情况引起的阻塞： ●select for update ●外键没有创建索引 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 TURNERSALESMAN 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行。
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倍。
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 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;
ID ———- 1 2 3 4 5 6 7 8 9 10 11
ID ———- 12 13 14 15 16 17 18 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
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表示当前回话阻塞了其他的会话。 如果程序设置成这样，通常可以保护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 ———- 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 外键和索引 如果你的系统有主、外键引用关系，并且满足以下三个条件中的任意一个，那么你应该考虑给外键字段创建索引，否则系统的性能可能会下降甚至阻塞。 ●主表上有频繁的删除操作。 ●主键上有频繁的修改操作。 ●业务上经常会出现主表和从表做关联查询的情况。
第一个条件和第二个条件操作的时候，主表会在从表上创建一个锁定，以保证主表主键的修改不会导致从表的数据在引用上出现问题，这是一个数据引用完整性的要求，如果主表上经常出现这样的删除或是对主键列进行修改的操作，或者每次操作的记录数很多，都将造成从表长时间被锁定，而影响其他用户的正常操作。比如主表每次删除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 2loop 3for j in 1..1000 loop 4insert into t values(i); 5end loop; 6end loop; 7end; 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
callcount 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
callcount 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 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;
已创建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 where x>22
callcount 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) from “TEST”.”T1″ where “X” = :1
allcount 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语句： 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;
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.
在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): —————————————————
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;
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)
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系统中，从表经常会是非常巨大的表，在这种情况下，如果从表没有索引，那么查询几乎是不可想象的。 |
|||||
|
|||||
|
共享池中如果存在大量的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.
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.
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 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 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’ ); 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 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 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执行中的统计数据的存储过程。 下面开始做测试对比，先创建一个表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; 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.
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 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; 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; /
下面是如何使用这个函数。 将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(*) 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没有绑定变量。 |
|||||
|
|||||
|
访问频率非常高的数据块被称为热块（Hot Block），当很多用户一起去访问某几个数据块时，就会导致一些Latch争用。最常见Latch争用是： ●buffer busy waits ●cache buffer chain
这两个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;
请看下面的例子，创建两个数据量相同的表，让一个表上每个数据块存放记录数尽可能地小，另一个表保持默认的情况，来看数据块访问情况： 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 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
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 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);
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行。
执行计划 ———————————————————- 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 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 | ———————————————————————-
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需要读取更多的数据块来完成查询。从另一个角度看，我们把数据分布到了更多的数据块上，大大降低了一个数据块被重复读取的概率。 但是这种方式的缺点是显而易见的，就是降低了数据的性能，在这种情况下，访问相同的数据意味着需要读取更多的数据块，性能会有所降低。 不过如果存在这样的情况，比如很多用户只修改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索引的一个最重要的功能： 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;
索引已创建。
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)
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
———————————————————————- | 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时，都会访问这个数据块，如果很多用户频繁地访问这个范围的索引键值，有可能导致这个枝数据块变成热块。 当出现这种现象时，可以考虑对索引做分区，以便于使这些根、枝数据块分布到不同的数据段（分区）上，减少数据块的并行访问的密度。 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′;
未选定行
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
call count cpu elapsed disk query current rows —– —– —- ——- —– ——- ——- —– Parse 10.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
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需要多次通过索引根数据块才能访问到键值，所以就会多出一些数据块来。 上面的例子说明了当把一个索引创建为分区索引时，索引的根数据块会分布到更多的数据块上，通过这种方式，可以避免由于索引根、枝数据块太集中导致热块产生。 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系统数据库主要的工作是报表和海量数据的批量加载。
|
