存储过程编写经验和优化措施 　　一）、适合读者对象：数据库开发程序员，数据库的数据量很多，涉及到对SP（存储过程）的优化的项目开发人员，对数据库有浓厚兴趣的人。 　 　　二）、介绍：在数据库的开发过程中，经常会遇到复杂的业务逻辑和对数据库的操作，这个时候就会用SP来封装数据库操作。如果项目的SP较多，书写又没有一定的规范，将会影响以后的系统维护困难和大SP逻辑的难以理解，另外如果数据库的数据量大或者项目对SP的性能要求很，就会遇到优化的问题，否则速度有可能很慢，经过亲身经验，一个经过优化过的SP要比一个性能差的SP的效率甚至高几百倍。 　 　　三）、内容： 　 　　1、开发人员如果用到其他库的Table或View，务必在当前库中建立View来实现跨库操作，最好不要直接使用“databse.dbo.table_name”，因为sp_depends不能显示出该SP所使用的跨库table或view，不方便校验。　　 　　2、开发人员在提交SP前，必须已经使用set showplan on分析过查询计划，做过自身的查询优化检查。 3、高程序运行效率，优化应用程序，在SP编写过程中应该注意以下几点： 　　 　　a)SQL的使用规范： 　　　i.　尽量避免大事务操作，慎用holdlock子句，提高系统并发能力。 　　　ii.　尽量避免反复访问同一张或几张表，尤其是数据量较大的表，可以考虑先根据条件提取数据到临时表中，然后再做连接。 　　　iii.　尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该改写；如果使用了游标，就要尽量避免在游标循环中再进行表连接的操作。 　　　iv.　注意where字句写法，必须考虑语句顺序，应该根据索引顺序、范围大小来确定条件子句的前后顺序，尽可能的让字段顺序与索引顺序相一致，范围从大到小。 　　　v.　不要在where子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。 　　　vi.　尽量使用exists代替select count(1)来判断是否存在记录，count函数只有在统计表中所有行数时使用，而且count(1)比count(*)更有效率。 　　　vii.　尽量使用“>=”，不要使用“>”。 　　　viii.　注意一些or子句和union子句之间的替换 　　　ix.　注意表之间连接的数据类型，避免不同类型数据之间的连接。 　　　x.　注意存储过程中参数和数据类型的关系。 　　　xi.　注意insert、update操作的数据量，防止与其他应用冲突。如果数据量超过200个数据页面（400k），那么系统将会进行锁升级，页级锁会升级成表级锁。 　　 　　b)索引的使用规范： 　　　i.　索引的创建要与应用结合考虑，建议大的OLTP表不要超过6个索引。 　　　ii.　尽可能的使用索引字段作为查询条件，尤其是聚簇索引，必要时可以通过index index_name来强制指定索引 　　　iii.　避免对大表查询时进行table scan，必要时考虑新建索引。 　　　iv.　在使用索引字段作为条件时，如果该索引是联合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用。 　　　v.　要注意索引的维护，周期性重建索引，重新编译存储过程。　　 　　c)tempdb的使用规范： 　　　i.　尽量避免使用distinct、order by、group by、having、join、cumpute，因为这些语句会加重tempdb的负担。 　　　ii.　避免频繁创建和删除临时表，减少系统表资源的消耗。 　　　iii.　在新建临时表时，如果一次性插入数据量很大，那么可以使用select into代替create table，避免log，提高速度；如果数据量不大，为了缓和系统表的资源，建议先create table，然后insert。 　　　iv.　如果临时表的数据量较大，需要建立索引，那么应该将创建临时表和建立索引的过程放在单独一个子存储过程中，这样才能保证系统能够很好的使用到该临时表的索引。 　　　 v.　如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先truncate table，然后drop table，这样可以避免系统表的较长时间锁定。 　　　 vi.　慎用大的临时表与其他大表的连接查询和修改，减低系统表负担，因为这种操作会在一条语句中多次使用tempdb的系统表。　　 　　d)合理的算法使用： 　　 　　根据上面已提到的SQL优化技术和ASE Tuning手册中的SQL优化内容,结合实际应用,采用多种算法进行比较,以获得消耗资源最少、效率最高的方法。具体可用ASE调优命令：set statistics io on, set statistics time on , set showplan on 等。 51、SET SHOWPLAN_ALL ON 查看执行方案。DBCC检查数据库数据完整性。DBCC(DataBase Consistency Checker)是一组用于验证SQL Server数据库完整性的程序。 52、谨慎使用游标 在某些必须使用游标的场合，可考虑将符合条件的数据行转入临时表中，再对临时表定义游标进行操作，这样可使性能得到明显提高。 Oracle SQL 性能优化： 1.选用适合的ORACLE优化器 ORACLE的优化器共有3种 A、RULE (基于规则) b、COST (基于成本) c、CHOOSE (选择性) 设置缺省的优化器，可以通过对init.ora文件中OPTIMIZER_MODE参数的各种声明，如RULE，COST，CHOOSE，ALL_ROWS，FIRST_ROWS 。 你当然也在SQL句级或是会话(session)级对其进行覆盖。 为了使用基于成本的优化器(CBO， Cost-Based Optimizer) ， 你必须经常运行analyze 命令，以增加数据库中的对象统计信息(object statistics)的准确性。 如果数据库的优化器模式设置为选择性(CHOOSE)，那么实际的优化器模式将和是否运行过analyze命令有关。 如果table已经被analyze过， 优化器模式将自动成为CBO ， 反之，数据库将采用RULE形式的优化器。 在缺省情况下，ORACLE采用CHOOSE优化器， 为了避免那些不必要的全表扫描(full table scan) ， 你必须尽量避免使用CHOOSE优化器，而直接采用基于规则或者基于成本的优化器。 2.访问Table的方式 ORACLE 采用两种访问表中记录的方式： A、 全表扫描 全表扫描就是顺序地访问表中每条记录。ORACLE采用一次读入多个数据块(database block)的方式优化全表扫描。 B、 通过ROWID访问表 你可以采用基于ROWID的访问方式情况，提高访问表的效率， ROWID包含了表中记录的物理位置信息。ORACLE采用索引(INDEX)实现了数据和存放数据的物理位置(ROWID)之间的联系。通常索引提供了快速访问ROWID的方法，因此那些基于索引列的查询就可以得到性能上的提高。 3.共享SQL语句 为了不重复解析相同的SQL语句，在第一次解析之后，ORACLE将SQL语句存放在内存中。这块位于系统全局区域SGA(system global area)的共享池(shared buffer pool)中的内存可以被所有的数据库用户共享。 因此，当你执行一个SQL语句(有时被称为一个游标)时，如果它和之前的执行过的语句完全相同， ORACLE就能很快获得已经被解析的语句以及最好的执行路径。ORACLE的这个功能大大地提高了SQL的执行性能并节省了内存的使用。 可惜的是ORACLE只对简单的表提供高速缓冲(cache buffering)，这个功能并不适用于多表连接查询。 数据库管理员必须在init.ora中为这个区域设置合适的参数，当这个内存区域越大，就可以保留更多的语句，当然被共享的可能性也就越大了。 当你向ORACLE提交一个SQL语句，ORACLE会首先在这块内存中查找相同的语句。这里需要注明的是，ORACLE对两者采取的是一种严格匹配，要达成共享，SQL语句必须完全相同(包括空格，换行等)。 数据库管理员必须在init.ora中为这个区域设置合适的参数，当这个内存区域越大，就可以保留更多的语句，当然被共享的可能性也就越大了。 共享的语句必须满足三个条件： A、 字符级的比较： 当前被执行的语句和共享池中的语句必须完全相同。 B、 两个语句所指的对象必须完全相同： C、 两个SQL语句中必须使用相同的名字的绑定变量(bind variables)。 4.选择最有效率的表名顺序(只在基于规则的优化器中有效) ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，因此FROM子句中写在最后的表(基础表 driving table)将被最先处理。在FROM子句中包含多个表的情况下，你必须选择记录条数最少的表作为基础表。当ORACLE处理多个表时， 会运用排序及合并的方式连接它们。首先，扫描第一个表(FROM子句中最后的那个表)并对记录进行派序，然后扫描第二个表(FROM子句中最后第二个表)，最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并。 如果有3个以上的表连接查询， 那就需要选择交叉表(intersection table)作为基础表， 交叉表是指那个被其他表所引用的表。 5.WHERE子句中的连接顺序 ORACLE采用自下而上的顺序解析WHERE子句，根据这个原理，表之间的连接必须写在其他WHERE条件之前， 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。 6.SELECT子句中避免使用 ' * ' 当你想在SELECT子句中列出所有的COLUMN时，使用动态SQL列引用 '*' 是一个方便的方法。不幸的是，这是一个非常低效的方法。实际上，ORACLE在解析的过程中， 会将'*' 依次转换成所有的列名， 这个工作是通过查询数据字典完成的， 这意味着将耗费更多的时间。 7.减少访问数据库的次数 当执行每条SQL语句时，ORACLE在内部执行了许多工作：解析SQL语句，估算索引的利用率，绑定变量，读数据块等等。由此可见，减少访问数据库的次数，就能实际上减少ORACLE的工作量。 8.使用DECODE函数来减少处理时间 使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表。 9.整合简单，无关联的数据库访问 如果你有几个简单的数据库查询语句，你可以把它们整合到一个查询中(即使它们之间没有关系) 10.删除重复记录 11.用TRUNCATE替代DELETE 当删除表中的记录时，在通常情况下， 回滚段(rollback segments ) 用来存放可以被恢复的信息。 如果你没有COMMIT事务，ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况)。 而当运用TRUNCATE时， 回滚段不再存放任何可被恢复的信息。当命令运行后，数据不能被恢复。因此很少的资源被调用，执行时间也会很短。 12.尽量多使用COMMIT 只要有可能，在程序中尽量多使用COMMIT，这样程序的性能得到提高，需求也会因为COMMIT所释放的资源而减少 COMMIT所释放的资源： A、 回滚段上用于恢复数据的信息。 B、被程序语句获得的锁。 C、 redo log buffer 中的空间。 D、ORACLE为管理上述3种资源中的内部花费。 13.计算记录条数 和一般的观点相反，count(*) 比count(1)稍快，当然如果可以通过索引检索，对索引列的计数仍旧是最快的。例如 COUNT(EMPNO) 14.用Where子句替换HAVING子句 避免使用HAVING子句，HAVING 只会在检索出所有记录之后才对结果集进行过滤。 这个处理需要排序，总计等操作。如果能通过WHERE子句限制记录的数目，那就能减少这方面的开销。 15.减少对表的查询 在含有子查询的SQL语句中，要特别注意减少对表的查询。 16.通过内部函数提高SQL效率。 17.使用表的别名(Alias) 当在SQL语句中连接多个表时， 请使用表的别名并把别名前缀于每个Column上。这样一来，就可以减少解析的时间并减少那些由Column歧义引起的语法错误。 18.用EXISTS替代IN 在许多基于基础表的查询中，为了满足一个条件，往往需要对另一个表进行联接。在这种情况下，使用EXISTS(或NOT EXISTS)通常将提高查询的效率。 19.用NOT EXISTS替代NOT IN 在子查询中，NOT IN子句将执行一个内部的排序和合并。 无论在哪种情况下，NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历)。为了避免使用NOT IN ，我们可以把它改写成外连接(Outer Joins)或NOT EXISTS。 20.用表连接替换EXISTS 通常来说 ， 采用表连接的方式比EXISTS更有效率 。 21.用EXISTS替换DISTINCT 当提交一个包含一对多表信息(比如部门表和雇员表)的查询时，避免在SELECT子句中使用DISTINCT。 一般可以考虑用EXIST替换 。 DB2数据库优化 为了帮助 DB2 DBA 避免性能灾难并获得高性能，我为我们的客户、用户和 DB2 专家同行总结了一套故障诊断流程。以下详细说明在 Unix、Windows 和 OS/2 环境下使用 DB2 UDB 的电子商务 OLTP 应用程序的 10 条最重要的性能改善技巧 - 并在本文的结束部分作出 总结。 　　每隔大约几个星期，我们就会接到苦恼的 DBA 们的电话，抱怨有关性能的问题。“我们 Web 站点速度慢得像蜗牛一样”，他们叫苦道，“我们正在失去客户，情况严重。你能帮忙吗?”为了回答这些问题，我为我的咨询公司开发了一个分析流程，它能让我们很快找到性能问题的原因，开发出补救措施并提出调整意见。这些打电话的人极少询问费用和成本 - 他们只关心制止损失。当 DB2 或电子商务应用程序的运行不能达到预期的性能时，组织和财务的收益将遭受极大的损失。 　　1. 监视开关 　　确保已经打开监视开关。如果它们没有打开，您将无法获取您需要的性能信息。要打开该监视开关，请发出以下命令： 　　db2 "update monitor switches using 　　lock ON sort ON bufferpool ON uow ON 　　table ON statement ON" 　　2. 代理程序 　　确保有足够的 DB2 代理程序来处理工作负载。要找出代理程序的信息，请发出命令： 　　db2 "get snapshot for database manager" 　　并查找以下行： 　　High water mark for agents registered = 7 　　High water mark for agents waiting for a token = 0 　　Agents registered= 7 　　Agents waiting for a token= 0 　　Idle agents= 5 　　Agents assigned from pool= 158 　　Agents created from empty Pool = 7 　　Agents stolen from another application= 0 　　High water mark for coordinating agents= 7 　　Max agents overflow= 0 　　如果您发现Agents waiting for a token或Agents stolen from another application不为 0，那么请增加对数据库管理器可用的代理程序数(MAXAGENTS 和/或 MAX_COORDAGENTS取适用者)。 　　3. 最大打开的文件数 　　DB2 在操作系统资源的约束下尽量做一个“优秀公民”。它的一个“优秀公民”的行动就是给在任何时刻打开文件的最大数设置一个上限。数据库配置参数 MAXFILOP约束 DB2 能够同时打开的文件最大数量。当打开的文件数达到此数量时，DB2 将开始不断地关闭和打开它的表空间文件(包括裸设备)。不断地打开和关闭文件减缓了 SQL 响应时间并耗费了 CPU 周期。要查明 DB2 是否正在关闭文件，请发出以下命令： 　　db2 "get snapshot for database on DBNAME" 　　并查找以下的行： 　　Database files closed = 0 　　如果上述参数的值不为 0，那么增加MAXFILOP的值直到不断打开和关闭文件的状态停埂Ｊ褂靡韵旅睿?/P> 　　db2 "update db cfg for DBNAME using MAXFILOP N" 　　4. 锁 　　LOCKTIMEOUT的缺省值是 -1，这意味着将没有锁超时(对 OLTP 应用程序，这种情况可能会是灾难性的)。尽管如此，我还是经常发现许多 DB2 用户用LOCKTIMEOUT= -1。将LOCKTIMEOUT设置为很短的时间值，例如 10 或 15 秒。在锁上等待过长时间会在锁上产生雪崩效应。 　　首先，用以下命令检查LOCKTIMEOUT 的值： 　　db2 "get db cfg for DBNAME" 　　并查找包含以下文本的行： 　　Lock timeout (sec) (LOCKTIMEOUT) = -1 　　如果值是 -1，考虑使用以下命令将它更改为 15 秒(一定要首先询问应用程序开发者或您的供应商以确保应用程序能够处理锁超时)： 　　db2 "update db cfg for DBNAME using LOCKTIMEOUT 15" 　　您同时应该监视锁等待的数量、锁等待时间和正在使用锁列表内存(lock list memory)的量。请发出以下命令： 　　db2 "get snapshot for database on DBNAME" 　　查找以下行： 　　Locks held currently= 0 　　Lock waits= 0 　　Time database waited on locks (ms)= 0 　　Lock list memory in use (Bytes)= 576 　　Deadlocks detected= 0 　　Lock escalations= 0 　　Exclusive lock escalations= 0 　　Agents currently waiting on locks= 0 　　Lock Timeouts= 0 　　如果Lock list memory in use (Bytes)超过所定义LOCKLIST大小的 50%，那么在LOCKLIST数据库配置中增加 4k 页的数量。 　　5. 临时表空间 　　为了改善 DB2 执行并行 I/O 和提高使用TEMPSPACE的排序、散列连接(hash join)和其它数据库操作的性能，临时表空间至少应该在三个不同的磁盘驱动器上拥有三个容器。 　　要想知道您的临时表空间具有多少容器，请发出以下命令： 　　db2 "list tablespaces show detail" 　　查找与以下示例类似的TEMPSPACE 表空间定义： 　　Tablespace ID= 1 　　Name= TEMPSPACE1 　　Type= System managed space 　　Contents= Temporary data 　　State= 0x0000 　　Detailed explanation: Normal 　　Total pages= 1 　　Useable pages= 1 　　Used pages= 1 　　Free pages= Not applicable 　　High water mark (pages)= Not applicable 　　Page size (bytes)= 4096 　　Extent size (pages)= 32 　　Prefetch size (pages)= 96 　　Number of containers= 3 　　注意Number of containers的值是 3，而且Prefetch size是Extent size的三倍。为了得到最佳的并行 I/O 性能，重要的是Prefetch size为Extent size的倍数。这个倍数应该等于容器的个数。 　　要查找容器的定义，请发出以下命令： 　　db2 "list tablespace containers for 1 show detail" 　　1 指的是tablespace ID #1，它是刚才所给出的示例中的TEMPSPACE1。 　　6. 内存排序 　　OLTP 应用程序不应该执行大的排序。它们在 CPU、I/O 和所用时间方面的成本极高，而且将使任何 OLTP 应用程序慢下来。因此，256 个 4K 页(1MB)的缺省SORTHEAP大小(1MB)应该是足够了。您也应该知道排序溢出的数量和每个事务的排序数。 　　请发出以下命令： 　　Db2 "get snapshot for database on DBNAME" 　　并查找以下行： 　　Total sort heap allocated= 0 　　Total sorts = 1 　　Total sort time (ms)= 8 　　Sort overflows = 0 　　Active sorts = 0 　　Commit statements attempted = 3 　　Rollback statements attempted = 0 　　Let transactions = Commit statements attempted + Rollback 　　statements attempted 　　Let SortsPerTX= Total sorts / transactions 　　Let PercentSortOverflows = Sort overflows * 100 / Total sorts 　　如果PercentSortOverflows ((Sort overflows * 100) / Total sorts )大于 3 个百分点，那么在应用程序 SQL 中会出现严重的或意外的排序问题。因为正是溢出的存在表明发生了大的排序，所以理想的情况是发现没有排序溢出或至少其百分比小于一个百分点。 　　如果出现过多的排序溢出，那么“应急”解决方案是增加SORTHEAP的大小。然而，这样做只是掩盖了真实的性能问题。相反，您应该确定引起排序的 SQL 并更改该 SQL、索引或群集来避免或减少排序开销。 　　如果SortsPerTX 大于 5 (作为一种经验之谈)，那么每个事务的排序数可能很大。虽然某些应用程序事务执行许多小的组合排序(它们不会溢出并且执行时间很短)，但是它消耗了过多的 CPU。当SortsPerTX很大时，按我的经验，这些机器通常会受到 CPU 的限制。确定引起排序的 SQL 并改进存取方案(通过索引、群集或更改 SQL)对提高事务吞吐率是极为重要的。 　　7. 表访问 　　对于每个表，确定 DB2 为每个事务读取的行数。您必须发出两个命令： 　　db2 "get snapshot for database on DBNAME" 　　db2 "get snapshot for tables on DBNAME" 　　在发出第一个命令以后，确定发生了多少个事务(通过取Commit statements attempted和Rollback statements attempted之和 - 请参阅技巧 3)。 　　在发出第二个命令以后，将读取的行数除以事务数(RowsPerTX)。在每个事务中，OLTP 应用程序通常应该从每个表读取 1 到 20 行。如果您发现对每个事务有成百上千的行正被读取，那么发生了扫描操作，也许需要创建索引。(有时以分布和详细的索引来运行 runstats 也可提供了一个解决的办法。) 　　“get snapshot for tables on DBNAME”的样本输出如下： 　　Snapshot timestamp = 09-25-2000 　　4:47:09.970811 　　Database name= DGIDB 　　Database path= /fs/inst1/inst1/NODE0000/SQL00001/ 　　Input database alias= DGIDB 　　Number of accessed tables= 8 　　Table List 　　Table Schema= INST1 　　Table Name= DGI_ 　　SALES_ LOGS_TB 　　Table Type= User 　　Rows Written= 0 　　Rows Read= 98857 　　Overflows= 0 　　Page Reorgs= 0 　　Overflows 的数量很大就可能意味着您需要重组表。当由于更改了行的宽度从而 DB2 必须在一个不够理想的页上定位一个行时就会发生溢出。 　　8. 表空间分析 　　表空间快照对理解访问什么数据以及如何访问是极其有价值的。要得到一个表空间快照，请发出以下命令： 　　db2 "get snapshot for tablespaces on DBNAME" 　　对每个表空间，回答以下问题： 　　平均读取时间(ms)是多少? 　　平均写入时间(ms)是多少? 　　异步(预取)相对于同步 (随机)所占的物理 I/O 的百分比是多少? 　　每个表空间的缓冲池命中率是多少? 　　每分钟读取多少物理页面? 　　对于每个事务要读取多少物理和逻辑页面? 　　对于所有表空间，回答以下问题： 　　哪个表空间的读取和写入的时间最慢?为什么?是因为其容器在慢速的磁盘上吗?容器大小是否相等?对比异步访问和同步访问，访问属性是否和期望的一致?随机读取的表应该有随机读取的表空间，这是为了得到高的同步读取百分比、通常较高的缓冲池命中率和更低的物理 I/O 率。 　　对每个表空间，确保预取大小等于数据块大小乘以容器数。请发出以下命令： 　　db2 "list tablespaces show detail" 　　如果需要，可以为一个给定表空间改变预取大小。可以使用以下命令来检查容器定义： 　　db2 "list tablespace containers for N show detail" 　　在此，N 是表空间标识号。 　　9. 缓冲池优化 　　我时常发现一些 DB2 UDB 站点，虽然机器具有 2、4 或 8GB 内存，但是 DB2 数据库却只有一个缓冲池(IBMDEFAULTBP)，其大小只有 16MB! 　　如果在您的站点上也是这种情况，请为 SYSCATSPACE 目录表空间创建一个缓冲池、为TEMPSPACE表空间创建一个缓冲池以及另外创建至少两个缓冲池：BP_RAND和BP_SEQ。随机访问的表空间应该分配给用于随机访问的缓冲池(BP_RAND)。顺序访问(使用异步预取 I/O)的表空间应该分配给用于顺序访问的缓冲池(BP_SEQ)。根据某些事务的性能目标，您可以创建附加的缓冲池;例如，您可以使一个缓冲池足够大以存储整个“热”(或者说访问非常频繁的)表。当涉及到大的表时，某些 DB2 用户将重要表的索引放入一个索引(BP_IX)缓冲池取得了很大成功。 　　太小的缓冲池会产生过多的、不必要的物理 I/O。太大的缓冲池使系统处在操作系统页面调度的风险中并消耗不必要的 CPU 周期来管理过度分配的内存。正好合适的缓冲池大小就在“太小”和“太大”之间的某个平衡点上。适当的大小存在于回报将要开始减少的点上。如果您没有使用工具来自动进行回报减少分析，那么您应该在不断增加缓冲池大小上科学地测试缓冲池性能(命中率、I/O 时间和物理 I/O 读取率)，直到达到最佳的缓冲池大小。因为业务一直在变动和增长，所以应该定期重新评估“最佳大小”决策。 　　10. SQL 成本分析 　　一条糟糕的 SQL 语句会彻底破坏您的一整天。我不止一次地看到一个相对简单的 SQL 语句搞糟了一个调整得很好的数据库和机器。对于很多这些语句，天底下(或在文件中)没有 DB2 UDB 配置参数能够纠正因错误的 SQL 语句导致的高成本的情况。 　　更糟糕的是，DBA 常常受到种种束缚：不能更改 SQL(可能是因为它是应用程序供应商提供的，例如 SAP、 PeopleSoft或 Siebel)。这给 DBA 只留下三条路可走： 　　1. 更改或添加索引 　　2. 更改群集 　　3. 更改目录统计信息 　　另外，如今健壮的应用程序由成千上万条不同的 SQL 语句组成。这些语句执行的频率随应用程序的功能和日常的业务需要的不同而不同。SQL 语句的实际成本是它执行一次的成本乘以它执行的次数。 　　每个 DBA 所面临的重大的任务是，识别具有最高“实际成本”的语句的挑战，并且减少这些语句的成本。 　　通过本机 DB2 Explain 实用程序、一些第三方供应商提供的工具或 DB2 UDB SQL Event Monitor 数据，您可以计算出执行一次 SQL 语句所用的资源成本。但是语句执行频率只能通过仔细和耗时地分析 DB2 UDB SQL Event Monitor 的数据来了解。 　　在研究 SQL 语句问题时，DBA 使用的标准流程是： 　　1. 创建一个 SQL Event Monitor，写入文件： 　　$> db2 "create event monitor SQLCOST for statements write to ..." 　　2. 激活事件监视器(确保有充足的可用磁盘空间)： 　　$> db2 "set event monitor SQLCOST state = 1" 　　3. 让应用程序运行。 　　4. 取消激活事件监视器： 　　$> db2 "set event monitor SQLCOST state = 0" 　　5. 使用 DB2 提供的 db2evmon 工具来格式化 SQL Event Monitor 原始数据(根据 SQL 吞吐率可能需要数百兆字节的可用磁盘空间)： 　　$> db2evmon -db DBNAME -evm SQLCOST 　　> sqltrace.txt 　　6. 浏览整个已格式化的文件，寻找显著大的成本数(一个耗时的过程)： 　　$> more sqltrace.txt 　　7. 对已格式化的文件进行更完整的分析，该文件试图标识唯一的语句(独立于文字值)、每个唯一语句的频率(它出现的次数)和其总 CPU、排序以及其它资源成本的总计。如此彻底的分析在 30 分钟的应用程序 SQL 活动样本上可能要花一周或更多的时间。 　　要减少确定高成本 SQL 语句所花的时间，您可以考虑许多可用的信息来源： 　　从 技巧 4，务必要计算在每个事务中从每个表中读取的行数。如果产生的数字看上去很大，那么 DBA 可以在 SQL Event Monitor 格式化输出中搜索有关的表名称(这将缩小搜索范围而且节省一些时间)，这样也许能够找出有问题的语句。从 技巧 3，务必计算每个表空间的异步读取百分比和物理 I/O 读取率。如果一个表空间的异步读取百分比很高并远远超过平均的物理 I/O 读取率，那么在此表空间中的一个或更多的表正在被扫描。查询目录并找出哪些表被分配到可疑的表空间(每个表空间分配一个表提供最佳性能检测)，然后在 SQL Event Monitor 格式化输出中搜索这些表。这些也可能有助于缩小对高成本 SQL 语句的搜索范围。 尝试观察应用程序执行的每条 SQL 语句的 DB2 Explain 信息。然而，我发现高频率、低成本语句经常争用机器容量和能力来提供期望的性能。 如果分析时间很短而且最大性能是关键的，那么请考虑使用供应商提供的工具(它们能够快速自动化识别资源密集的 SQL 语句的过程)。 Database-GUYS Inc.的 SQL-GUY 工具提供精确、实时且均衡的 SQL 语句的成本等级分析。 　　继续调节 　　最佳性能不仅需要排除高成本 SQL 语句，而且需要确保相应的物理基础结构是适当的。当所有的调节旋钮都设置得恰到好处、内存被有效地分配到池和堆而且 I/O 均匀地分配到各个磁盘时，才可得到最佳性能。虽然量度和调整需要时间，但是执行这 10 个建议的 DBA 将非常成功地满足内部和外部的 DB2 客户。因为电子商务的变化和增长，即使是管理得最好的数据库也需要定期的微调。DBA 的工作永远都做不完! 快速回顾最棒的 10 个技巧 对工作负载使用足够的代理程序。 不允许 DB2 不必要地关闭和打开文件。 不允许长期的锁等待。 确保数据库的 TEMPSPACE 表空间的并行 I/O 能力。 保守地管理 DB2 排序内存并不要以大的 SORTHEAP 来掩盖排序问题。 分析表的访问活动并确定具有特别高的每个事务读取行数或溢出数的表。 分析每个表空间的性能特性，并寻求改善读取时间最慢、等待时间最长、物理 I/O 读取率最高、命中率最差的表空间性能以及与所期望的不一致的访问属性。 创建多个缓冲池，有目的地将表空间分配到缓冲池以便于共享访问属性。 检查 DB2 UDB SQL Event Monitor 信息以找到哪个 SQL 语句消耗计算资源最多并采取正确的措施。 四、冷备份与热备份、双机热备与容错 冷备份与热备份 一、 冷备份 　　 　　冷备份发生在数据库已经正常关闭的情况下，当正常关闭时会提供给我们一个完整的数据库。冷备份时将关键性文件拷贝到另外的位置的一种说法。对于备份Oracle信息而言，冷备份时最快和最安全的方法。冷备份的优点是： 　　 　　１、 是非常快速的备份方法（只需拷文件） 　　２、 容易归档（简单拷贝即可） 　　３、 容易恢复到某个时间点上（只需将文件再拷贝回去） 　　４、 能与归档方法相结合，做数据库“最佳状态”的恢复。 　　５、 低度维护，高度安全。 　　 　　但冷备份也有如下不足： 　　 　　１、 单独使用时，只能提供到“某一时间点上”的恢复。 　　２、 再实施备份的全过程中，数据库必须要作备份而不能作其他工作。也就是说，在冷备份过程中，数据库必须是关闭状态。 　　３、 若磁盘空间有限，只能拷贝到磁带等其他外部存储设备上，速度会很慢。 　　４、 不能按表或按用户恢复。 　　 　　如果可能的话（主要看效率），应将信息备份到磁盘上，然后启动数据库（使用户可以工作）并将备份的信息拷贝到磁带上（拷贝的同时，数据库也可以工作）。冷备份中必须拷贝的文件包括： 　　 　　１、 所有数据文件 　　２、 所有控制文件 　　３、所有联机REDO LOG文件 　　４、 Init.ora文件（可选） 　　 　　值得注意的使冷备份必须在数据库关闭的情况下进行，当数据库处于打开状态时，执行数据库文件系统备份是无效的。 　　 　　下面是作冷备份的完整例子。 　　 　　（1） 关闭数据库 　　sqlplus /nolog 　　sql>connect /as sysdba 　　sql>shutdown normal; 　　 　　（2） 用拷贝命令备份全部的时间文件、重做日志文件、控制文件、初始化参数文件 　　sql>cp 　　 　　（3） 重启Oracle数据库 　　sql>startup 　　 　　二、 热备份 　　 　　热备份是在数据库运行的情况下，采用archivelog mode方式备份数据库的方法。所以，如果你有昨天夜里的一个冷备份而且又有今天的热备份文件，在发生问题时，就可以利用这些资料恢复更多的信息。热备份要求数据库在Archivelog方式下操作，并需要大量的档案空间。一旦数据库运行在archivelog状态下，就可以做备份了。热备份的命令文件由三部分组成： 　　 　　1． 数据文件一个表空间一个表空间的备份。 　　 　　（1） 设置表空间为备份状态 　　（2） 备份表空间的数据文件 　　（3） 恢复表空间为正常状态 　　 　　2． 备份归档log文件 　　 　　（1） 临时停止归档进程 　　（2） log下那些在archive rede log目标目录中的文件 　　（3） 重新启动archive进程 　　（4） 备份归档的redo log文件 　　 　　3． 用alter database bachup controlfile命令来备份控制文件热备份的优点是： 　　 　　1． 可在表空间或数据库文件级备份，备份的时间短。 　　2． 备份时数据库仍可使用。 　　3． 可达到秒级恢复（恢复到某一时间点上）。 　　4． 可对几乎所有数据库实体做恢复 　　5． 恢复是快速的，在大多数情况下爱数据库仍工作时恢复。 　　 　　热备份的不足是： 　　 　　1． 不能出错，否则后果严重 　　2． 若热备份不成功，所得结果不可用于时间点的恢复 　　3． 因难于维护，所以要特别仔细小心，不允许“以失败告终”。 双机热备的实现模式 双机热备有两种实现模式，一种是基于共享的存储设备的方式，另一种是没有共享的存储设备的方式，一般称为纯软件方式。 　基于存储共享的双机热备是双机热备的最标准方案。 　对于这种方式，采用两台（或多台，参见：双机与集群的异同）服务器，使用共享的存储设备（磁盘阵列柜或存储区域网SAN）。两台服务器可以采用互备、主从、并行等不同的方式。在工作过程中，两台服务器将以一个虚拟的IP地址对外提供服务，依工作方式的不同，将服务请求发送给其中一台服务器承担。同时，服务器通过心跳线(目前往往采用建立私有网络的方式)侦测另一台服务器的工作状况。当一台服务器出现故障时，另一台服务器根据心跳侦测的情况做出判断，并进行切换，接管服务。对于用户而言，这一过程是全自动的，在很短时间内完成，从而对业务不会造成影响。由于使用共享的存储设备，因此两台服务器使用的实际上是一样的数据，由双机或集群软件对其进行管理。 　对于纯软件的方式，则是通过镜像软件，将数据可以实时复制到另一台服务器上，这样同样的数据就在两台服务器上各存在一份，如果一台服务器出现故障，可以及时切换到另一台服务器。 　对于这种方式的深入分析，请参见：纯软件方式的双机热备方案深入分析 　纯软件方式还有另外一种情况，即服务器只是提供应用服务，而并不保存数据（比如只进行某些计算，做为应用服务器使用）。这种情况下同样也不需要使用共享的存储设备，而可以直接使用双机或集群软件即可。但这种情况其实与镜像软件无关，只不过是标准的双机热备的一种小的变化。 双机容错的工作原理 1、双机容错的两种方式 双机容错从工作原理上可以分为共享磁盘阵列柜方式和扩展镜像纯软件方式两种。这两种方式的共同特点都是围绕关键数据的可靠性，对操作系统、电源、CPU和主机主板进行容错。 双机共享磁盘阵列柜方式是以磁盘阵列柜为中心的双机容错方神机妙算，磁盘柜通过SCSI线连接到两个系统上，并能被两个系统所访问。关键数据放在共享磁盘柜中，在正常运行时，控制友在主用系统上，当主用系统发生故障或主用系统检查到某种故障后，系统控制权就切换到备用主机。主用系统修复后，主备角色互换，双机系统进入正常工作模式。 双机扩展镜像酏软件方式是纯软件方式的双机容错方案，两个系统之间通过以太网连接，关键数据在两个系统之间呈镜像存在。在正常运行时，控制权在主用系统上，数据实时地镜像到备用系统上。当主用系统发生故障或主用系统检查到某种故障后，系统控制权切换到备用主机。由于采用以太网作为系统的数据链路，主用系统可不干扰备用系统工作，自动脱离并在一个孤立的环境中进行故障的诊断和维修，主用系统修复后，控制权需要切回到主用系统，数据需要从备用系统恢复到主用系统，这个工作在后台自动完成，应用读取数据仍从备用系统上进行而不会中断。数据恢复完成后，双机系统进入正常工作模式。 以上两种双机容错的方式已经能很好地保证数据可靠，如果在主、备机上各运行一种应用还可实现相互备份。 2．共享磁盘阵列柜方式的工作原理 使用共享磁盘阵列柜方式的两台（或多台）服务器的数据同时存放在一个磁盘阵列柜里，因此，不需要进行数据复制，只需在其中一台服务器停机时将此服务器的工作转移到另外一台服务器，工作较为简单。由于数据存储在同一磁盘阵列柜里，一是磁盘阵列柜的数据捐赠 坏则数据全部丢失，有单点崩溃的可能性，而且由于服务器与磁盘阵列柜之间通常使用SCSI线连接，因此受到距离的了限制。 共享磁盘阵列车柜方式一般由监控系统与切换系统两部分组成。 （1） 监控系统 A、SCSI侦测。共享磁盘阵列柜方式内部含有侦测心跳通信线路，侦测结果置于共享磁盘阵列柜上的一个5MB的小区，用于监控，此小区一般在机柜逻辑盘的起始段，对于某一台服务器而言，将侦测信自己人以类似于记录方式写在该小区内，其中每一条记录包括如下内容。 系统对本服务器的监测状态信息 另一台服务器是滞看到本服务器状态信息，同时修改记录区内容。 B、网络侦测。当一台服务器有问题或出现故障时，对等服务器的可调变心跳频率不断提高。在最小心跳时间内发现记录内容没有更新，即会调用网络心跳侦测两次确认系统状态。当峡谷线心路都判断系统故障时，共享磁盘阵列柜方式将故障服务器的交易业务在最小安全切换时间内切换到对等服务器上继续运行。 C、切换系统 网络服务器。双服务器后台，对于用户一羰，由监控软件共享磁盘阵列柜方式提供一个逻辑的IP地址，如192.192.192.1,任一用户上网可以直接使用这一地址，当后台其中一台服务器出现故障时，另外一台服务器会自己将其网卡的IP地址替换为192.192.192.1，这样，用户一端的网络不会因为一台服务器出现故障而断掉。 数据库服务。当其中一台服务器出现故障时，另外一台服务器会自动接管数据库，同时启动数据库和应用程序，使用户数据库可以继续操作，对用户而言不受影响。 应用系统。当有一台服务器出现故障时，另外一台服务器会自动接管各类应用程序，同时启动应用程序，使用户可以继续操作，对用户而言不受影响。 3、扩展镜像纯软件方式的工作原理 使用纯软件方式的软件不需要共享磁盘阵列柜，它将数据存储于各自服务器内，通过镜相引擎将数据进行实时复制。当其中一台服务器停机时，设定的服务器接管停机服务器的工作。由于数据存储于不同服务器内，因此避免了单点崩溃的可能性，增加了数据的安全性。服务器之间通过网络连接，所以服务器之间的连接受距离的限制较小。由于数据存储在各自己服务器硬盘内，因此服务器之间有应用各不影响，提高了服务器正常使用时的效率。 4、热备份 热备份其实是计算机容错技术的一个概念，是实现计算机系统高可用性的主要方式。热备份采用磁盘镜相技术，将运行着的计算机系统数据和应用数据同时保存在不同的硬盘上，镜像在不同的磁盘上的数据在发生变化时同时刷新，从而保证数据一致性。当系统中的一个硬盘发生故障时，计算机可以使用镜像数据，避免因系统单点故障（如硬盘故障）导致整个计算机系统无法运行，从而实现计算机系统的高可用性。 现在的计算机系统在系统建设时都普遍采用了热备份方式，最典型的实现方式是双机热备份，即双机容错系统。双机容错系统在建设时选用两台同样服务器，运行相同的操作系统、应用软件（如数据库软件），两台服务器共享一个磁盘阵列，采用磁盘镜像，将应用数据建立在磁盘阵列车上，实现双机容错。其中一台服务器被指定为工作机，由它处理当前运行的业务，另一台为备份服务器。一旦工作机发生故障，运行的业务请求将被人工（或自动）地切换到备份服务器，使运行着的业务不至于因为系统的单点故障中断，实现系统的高可用性。 热备份实现了计算机系统的高可用性，使一些对实时性要求很强的业务（如银行信用卡业务）得以保障。然而，热备份方式并不能解决所有计算机系统数据管理问题，举一个最简单的例子，如果操作人员误删除了一个文件，热备系统为保证数据的一致性，会同时将这个文件的镜像文件删除，造成数据丢失。为防止有用的数据因系统故障和人为误操作而损坏或丢失，实行数据存储管理必不可少，数据存储管理的核心是数据备份。 双机容错环境下Oracle数据库的具体应用 目前许多建立和应用信息系统的企业，在系统应用不断改进的同时，开始注意提高企业信息系统的可用性和可靠性。通过双机容错系统为企业提供系统高可用性保障是目前企业普遍采用的方法。 医疗机构工作性质的特殊性要求其信息系统7天×24小时不间断运行，采用双机容错方案为系统提供了高可用解决方案。本文将对医疗信息系统的双机容错环境下Oracle数据库应用做详细介绍。 　　 　　系统配置 　　 　　　　该系统的硬件配置如下: 　　 　　　　主数据库服务器: 富士通Primergy MS-610服务器(双Xeon 700MHz CPU，1GB内存)。 　　　　数据库备份服务器: 富士通Team Server C870ie GP5C875(双PentiumⅢ 700MHz CPU，1GB内存)。 　　　　容错软件: 天地公司的SLHA 3.0软件包。 　　　　磁盘阵列: IQstore R1500(带2个SCSI接口)。 　　　　线路连接: 2台服务器用RS-232串口线和RJ-45网络线相连(如图1所示)。 　　 　　 　　 　　　　软件配置如下。 　　　　操作系统: Windows NT Server 4.0 　　　　服务器软件配置: Windows NT 4.0 Service Pack 5、Internet Explorer 5.0、Microsoft Data Access Component 2.0，Oracle数据库为7.3.3企业版。 　　 　　　　双机容错的实现 　　 　　　　1. 操作系统的安装 　　 　　　　我们用A机表示数据库服务器，用B机表示备份数据库服务器。首先在物理上将所需硬件设备连接好，分别在各自服务器上安装Windows NT Server 4.0操作系统及补丁包等。然后，进入磁盘管理器，将磁盘阵列划分为2个逻辑盘D和E，此时2台服务器都可访问磁盘阵列。 　　 　　　　2.Oracle数据库的安装 　　 　　　　先关闭B机，在A机上安装Oracle数据库，安装路径默认为D盘，归档日志放在E盘。安装完毕后，将Oracle的3个服务(此处SID为ORCL,所以3个服务就是OracleServiceORCL、OracleStartORCL和OracleTNSlistener)的启动方式改为手动并将此3个服务停止。注意: 改为手动的目的是为了让这3个服务由双机容错软件来启动,而不是由操作系统启动。 　　 　　　　然后，关闭A机，启动B机，格式化D盘，将刚刚由A机建立在磁盘阵列上的Oracle目录也格式化掉; 在B机上安装Oracle数据库，安装路径默认为D盘，安装完毕，同样将Oracle的3个服务的启动方式改为手动并停止3个服务。 　　 　　　　双机上安装Oracle的实质就是将Oracle系统分别装在2台服务器上，而数据只存储在磁盘阵列上。 　　 　　　　3.双机容错软件的安装及双机容错环境的建立 　　 　　　　双机容错软件的安装非常简单，只需启动A机和B机，在2台服务器上分别安装该软件即可。建立双机容错环境是将磁盘阵列上的D盘和E盘以及Oracle 的3个服务交由双机容错软件控制，并由双机容错软件进行切换。 　　 　　　　在双机容错软件SLHA的"Configuration"选项中将数据库服务器设为Active状态，即平时正常工作状态时，此时数据库服务器工作，备份服务器等待。当A机Active时，只有A机可以访问磁盘阵列，B机不能访问磁盘阵列。此时，Oracle数据库服务器实际上是A机，A机的IP地址就是Active IP Address，同时A机的主机名为Active Host Name; 当A机因故不能工作时，A机的状态会被"心跳线"侦测到，这时B机开始切换到Active状态，接管磁盘阵列，此时的Oracle数据库服务器改为B机，B机的IP地址就是Active IP Address，同时B机的主机名为Active Host Name。上述操作均由系统自动完成，实践证明切换所需的时间很快，对客户端的影响很小。 　　 　　　　需要注意的问题 　　 　　　　1．当在A机安装完Oracle数据库后在B机安装Oracle数据库时，一定要先将磁盘阵列D盘格式化，而不是只将D盘中已由A机安装的Oracle数据库删除，否则可能会出现意想不到的错误，例如Oracle侦听服务失败等; 　　 　　　　2．最终安装好Oracle数据库后，要对D:\Oracle\Orant\network\Admin\ Listener.ora文件进行修改，其中Server名称一定要改为Active host name Alias,如不进行修改将使客户端的Oracle数据库用户无法连接到Oracle数据库中。 　　 　　　　3．在Hosts文件中增加一条记录，使Active IP Address和Active Host Name相互对应，这样系统就会自动起到解析作用。Hosts文件位于c:\Winnt\ system32\drivers\etc目录下。 　　 　　　　4．要注意不到万不得已，不要强行切换，避免产生数据错误。如必须对双机进行切换，可先进入Svrmgr Oracle服务器控制台,用Shutdown命令关闭Oracle数据库，再进行切换。 　　　　双机容错的原理 　　 　　　　Oracle数据库安装在磁盘阵列上(即图2中Public Drives)，2台服务器都可以访问它，但不能同时访问。Oracle Server for NT主要提供3个服务：OracleServiceSID、OracleStartSID和OracleTNSlistener。在数据库服务器正常工作时，由数据库服务器控制磁盘阵列柜，此时只有该服务器可以访问磁盘阵列，该服务器上的Oracle服务处于启动(Active)状态，此时该服务器就扮演图2中Active Server的角色，备份服务器处于等待(Standby)状态，即图2中Backup Server。 　　 　　　　当数据库服务器发生故障不能工作时，双机容错系统会检测到数据库服务器的状态，从而使备份服务器自动激活，接管磁盘阵列并自动启动Oracle的3个服务，而对于客户端来说，只经历一个短暂的服务器重启过程，访问的数据仍是磁盘阵列中的数据。 　　 　　　　注意: 是双机容错软件而不是操作系统来控制Oracle 的启动和停止，即由双机容错软件来控制这3个Oracle服务的启动和停止，实现Oracle数据库在双机之间的切换 .

评论