数据库编程总结

SQL语句优化的原则： 
　　◆1、使用索引来更快地遍历表 
　　缺省情况下建立的索引是非群集索引，但有时它并不是最佳的。在非群集索引下，数据在物理上随机存放在数据页上。合理的索引设计要建立在对各种查询的分析和预测上。一般来说：①.有大量重复值、且经常有范围查询（between, > ,< ，> =,< =）和order by、group by发生的列，可考虑建立群集索引；②.经常同时存取多列，且每列都含有重复值可考虑建立组合索引；③.组合索引要尽量使关键查询形成索引覆盖，其前导列一定是使用最频繁的列。索引虽有助于提高性能但不是索引越多越好，恰好相反过多的索引会导致系统低效。用户在表中每加进一个索引，维护索引集合就要做相应的更新工作。 
　　◆2、IS NULL 与 IS NOT NULL 
　　不能用null作索引，任何包含null值的列都将不会被包含在索引中。即使索引有多列这样的情况下，只要这些列中有一列含有null，该列就会从索引中排除。也就是说如果某列存在空值，即使对该列建索引也不会提高性能。任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。 
　　◆3、IN和EXISTS 
　　EXISTS要远比IN的效率高。里面关系到full table scan和range scan。几乎将所有的IN操作符子查询改写为使用EXISTS的子查询。 
　　◆4、在海量查询时尽量少用格式转换。 
　　◆5、当在SQL SERVER 2000中，如果存储过程只有一个参数，并且是OUTPUT类型的，必须在调用这个存储过程的时候给这个参数一个初始的值，否则会出现调用错误。 
　　◆6、ORDER BY和GROPU BY 
　　使用ORDER BY和GROUP BY短语，任何一种索引都有助于SELECT的性能提高。注意如果索引列里面有NULL值，Optimizer将无法优化。 
　　◆7、任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边。 
　　◆8、IN、OR子句常会使用工作表，使索引失效。如果不产生大量重复值，可以考虑把子句拆开。拆开的子句中应该包含索引。 
　　◆9、SET SHOWPLAN_ALL ON 查看执行方案。DBCC检查数据库数据完整性。 
　　DBCC(DataBase Consistency Checker)是一组用于验证 SQL Server 数据库完整性的程序。 
　　◆10、慎用游标 
　　在某些必须使用游标的场合，可考虑将符合条件的数据行转入临时表中，再对临时表定义游标进行操作，这样可使性能得到明显提高。

 

一些常用的SQL语句供大家参考，希望对大家有所帮助。

   说明：存储过程的使用,CREATE PROC 创建存储过程,SQL2000中用sp_xxx和xp_xxx存储过程;一般来说,sp_xxx是一般的存储过程,而xp_xxx是扩展的存储过程。使用这些系统存储过程时，一般使用USE MASTER然后在使用sp_xxx或者xp_xxx。

 

　　说明：复制表(只复制结构,源表名：a 新表名：b)　　

 

　　SQL: select * into b from a where 1<>1

 

　　说明：拷贝表(拷贝数据,源表名：a 目标表名：b)　　

 

　　SQL: insert into b(a, b, c) select d,e,f from b;

 

　　说明：显示文章、提交人和最后回复时间　　

 

　　SQL: select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b

 

　　说明：外连接查询(表名1：a 表名2：b)　　

 

　　SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c

 

　　说明：日程安排提前五分钟提醒　　

 

　　SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5　　

 

　　说明：两张关联表，删除主表中已经在副表中没有的信息

 

　　SQL: 　　

 

　　delete from info where not exists ( select * from infobz where info.infid=infobz.infid

 

　　说明：--

 

　　SQL: 　　

 

　　SELECT A.NUM, A.NAME, B.UPD_DATE, B.PREV_UPD_DATE

 

　　 FROM TABLE1,

 

　　 (SELECT X.NUM, X.UPD_DATE, Y.UPD_DATE PREV_UPD_DATE

 

　　 FROM (SELECT NUM, UPD_DATE, INBOUND_QTY, STOCK_ONHAND

 

　　 FROM TABLE2

 

　　 WHERE TO_CHAR(UPD_DATE,'YYYY/MM') = TO_CHAR(SYSDATE, 'YYYY/MM')) X,

 

　　 (SELECT NUM, UPD_DATE, STOCK_ONHAND

 

　　 FROM TABLE2

 

　　 WHERE TO_CHAR(UPD_DATE,'YYYY/MM') =

 

　　 TO_CHAR(TO_DATE(TO_CHAR(SYSDATE, 'YYYY/MM') || '/01','YYYY/MM/DD') - 1, 'YYYY/MM') Y,

 

　　 WHERE X.NUM = Y.NUM （+）

 

　　 AND X.INBOUND_QTY + NVL(Y.STOCK_ONHAND,0) <> X.STOCK_ONHAND B

 

　　WHERE A.NUM = B.NUM

 

　　说明：--

 

　　SQL: 　　

 

　　select * from studentinfo where not exists(select * from student where studentinfo.id=student.id) and 系名称='"&strdepartmentname&"' and 专业名称='"&strprofessionname&"' order by 性别,生源地,高考总成绩

 

　　说明：

 

　　从数据库中去一年的各单位电话费统计(电话费定额贺电化肥清单两个表来源）

 

　　SQL: 　

 

　　SELECT a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, 'yyyy') AS telyear,

 

　　 SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '01', a.factration)) AS JAN,

 

　　 SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '02', a.factration)) AS FRI,

 

　　 SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '03', a.factration)) AS MAR,

 

　　 SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '04', a.factration)) AS APR,

 

　　 SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '05', a.factration)) AS MAY,

 

　　 SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '06', a.factration)) AS JUE,

 

　　 SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '07', a.factration)) AS JUL,

 

　　 SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '08', a.factration)) AS AGU,

 

　　 SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '09', a.factration)) AS SEP,

 

　　 SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '10', a.factration)) AS OCT,

 

　　 SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '11', a.factration)) AS NOV,

 

　　 SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '12', a.factration)) AS DEC

 

　　FROM (SELECT a.userper, a.tel, a.standfee, b.telfeedate, b.factration

 

　　 FROM TELFEESTAND a, TELFEE b

 

　　 WHERE a.tel = b.telfax) a

 

　　GROUP BY a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, 'yyyy')

 

　　说明：四表联查问题：　　

 

　　SQL: select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where .....

 

　　说明：得到表中最小的未使用的ID号

 

　　SQL:　

 

　　SELECT (CASE WHEN EXISTS(SELECT * FROM Handle b WHERE b.HandleID = 1) THEN MIN(HandleID) + 1 ELSE 1 END) as HandleID

 

　　 FROM Handle

 

　　 WHERE NOT HandleID IN (SELECT a.HandleID - 1 FROM Handle a)

 

 

三、数据库优化

1、索引问题

在做性能跟踪分析过程中，经常发现有不少后台程序的性能问题是因为缺少合适索引造成的，有些表甚至一个索引都没有。这种情况往往都是因为在设计表时，没去定义索引，而开发初期，由于表记录很少，索引创建与否，可能对性能没啥影响，开发人员因此也未多加重视。然一旦程序发布到生产环境，随着时间的推移，表记录越来越多，这时缺少索引，对性能的影响便会越来越大了。

这个问题需要数据库设计人员和开发人员共同关注

法则：不要在建立的索引的数据列上进行下列操作:

◆避免对索引字段进行计算操作

◆避免在索引字段上使用not，<>，!=

◆避免在索引列上使用IS NULL和IS NOT NULL

◆避免在索引列上出现数据类型转换

◆避免在索引字段上使用函数

◆避免建立索引的列中使用空值。

2、在可以使用UNION ALL的语句里，使用了UNION

UNION 因为会将各查询子集的记录做比较，故比起UNION ALL ，通常速度都会慢上许多。一般来说，如果使用UNION ALL能满足要求的话，务必使用UNION ALL。还有一种情况大家可能会忽略掉，就是虽然要求几个子集的并集需要过滤掉重复记录，但由于脚本的特殊性，不可能存在重复记录，这时便应该使用UNION ALL，如xx模块的某个查询程序就曾经存在这种情况，见，由于语句的特殊性，在这个脚本中几个子集的记录绝对不可能重复，故可以改用UNION ALL）

 

3、对Where 语句的法则

3.1 避免在WHERE子句中使用in，not  in，or 或者having。

可以使用 exist 和not exist代替 in和not in。

可以使用表链接代替 exist。Having可以用where代替，如果无法代替可以分两步处理。

例子

SELECT *  FROM ORDERS WHERE CUSTOMER_NAME NOT IN
(SELECT CUSTOMER_NAME FROM CUSTOMER)

优化

SELECT *  FROM ORDERS WHERE CUSTOMER_NAME not exist
(SELECT CUSTOMER_NAME FROM CUSTOMER)

3.2 不要以字符格式声明数字，要以数字格式声明字符值。（日期同样）否则会使索引无效，产生全表扫描。

例子使用：

SELECT emp.ename, emp.job FROM emp WHERE emp.empno = 7369;
不要使用：SELECT emp.ename, emp.job FROM emp WHERE emp.empno = ‘7369’

 

4、对Select语句的法则

在应用程序、包和过程中限制使用select * from table这种方式。看下面例子

使用SELECT empno,ename,category FROM emp WHERE empno = '7369‘
而不要使用SELECT * FROM emp WHERE empno = '7369'

 

5、排序

避免使用耗费资源的操作，带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL语句会启动SQL引擎执行，耗费资源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要执行两次排序。

 

优化SQL Server数据库方法：

查询速度慢的原因很多，常见如下几种：
　　
　　1、没有索引或者没有用到索引(这是查询慢最常见的问题，是程序设计的缺陷)
　　
　　2、I/O吞吐量小，形成了瓶颈效应。
　　
　　3、没有创建计算列导致查询不优化。
　　
　　4、内存不足
　　
　　5、网络速度慢
　　
　　6、查询出的数据量过大（可以采用多次查询，其他的方法降低数据量）
　　
　　7、锁或者死锁(这也是查询慢最常见的问题，是程序设计的缺陷)
　　
　　8、sp_lock,sp_who,活动的用户查看,原因是读写竞争资源。
　　
　　9、返回了不必要的行和列
　　
　　10、查询语句不好，没有优化

　　可以通过如下方法来优化查询 :

1、把数据、日志、索引放到不同的I/O设备上，增加读取速度，以前可以将Tempdb应放在RAID0上，SQL2000不在支持。数据量（尺寸）越大，提高I/O越重要.

2、纵向、横向分割表，减少表的尺寸(sp_spaceuse)

3、升级硬件

4、根据查询条件,建立索引,优化索引、优化访问方式，限制结果集的数据量。注意填充因子要适当（最好是使用默认值0）。索引应该尽量小，使用字节数小的列建索引好（参照索引的创建）,不要对有限的几个值的字段建单一索引如性别字段。

5、提高网速;

6、扩大服务器的内存,Windows 2000和SQL server 2000能支持4-8G的内存。配置虚拟内存：虚拟内存大小应基于计算机上并发运行的服务进行配置。运行 Microsoft SQL Server2000 时，可考虑将虚拟内存大小设置为计算机中安装的物理内存的 1.5 倍。如果另外安装了全文检索功能，并打算运行 Microsoft 搜索服务以便执行全文索引和查询，可考虑：将虚拟内存大小配置为至少是计算机中安装的物理内存的 3 倍。将 SQL Server max server memory 服务器配置选项配置为物理内存的 1.5 倍（虚拟内存大小设置的一半）。

7、增加服务器 CPU个数;但是必须明白并行处理串行处理更需要资源例如内存。使用并行还是串行程是MsSQL自动评估选择的。单个任务分解成多个任务，就可以在处理器上运行。例如耽搁查询的排序、连接、扫描和GROUP BY字句同时执行，SQL SERVER根据系统的负载情况决定最优的并行等级，复杂的需要消耗大量的CPU的查询最适合并行处理。但是更新操作Update,Insert， Delete还不能并行处理。

8、如果是使用like进行查询的话，简单的使用index是不行的，但是全文索引，耗空间。 like 'a%' 使用索引 like '%a' 不使用索引用 like '%a%' 查询时，查询耗时和字段值总长度成正比,所以不能用CHAR类型，而是VARCHAR。对于字段的值很长的建全文索引。

9、DB Server 和APPLication Server 分离；OLTP和OLAP分离

10、分布式分区视图可用于实现数据库服务器联合体。联合体是一组分开管理的服务器，但它们相互协作分担系统的处理负荷。这种通过分区数据形成数据库服务器联合体的机制能够扩大一组服务器，以支持大型的多层Web 站点的处理需要。有关更多信息，参见设计联合数据库服务器。（参照SQL帮助文件'分区视图'）

a、在实现分区视图之前，必须先水平分区表

b、在创建成员表后，在每个成员服务器上定义一个分布式分区视图，并且每个视图具有相同的名称。这样，引用分布式分区视图名的查询可以在任何一个成员服务器上运行。系统操作如同每个成员服务器上都有一个原始表的复本一样，但其实每个服务器上只有一个成员表和一个分布式分区视图。数据的位置对应用程序是透明的。

11、重建索引 DBCC REINDEX ,DBCC INDEXDEFRAG,收缩数据和日志 DBCC SHRINKDB,DBCC SHRINKFILE. 设置自动收缩日志.对于大的数据库不要设置数据库自动增长，它会降低服务器的性能。在T-sql的写法上有很大的讲究，下面列出常见的要点：首先，DBMS处理查询计划的过程是这样的：

1、 查询语句的词法、语法检查

2、 将语句提交给DBMS的查询优化器

3、 优化器做代数优化和存取路径的优化

4、 由预编译模块生成查询规划

5、 然后在合适的时间提交给系统处理执行

6、 最后将执行结果返回给用户其次，看一下SQL SERVER的数据存放的结构：一个页面的大小为8K(8060)字节，8个页面为一个盘区，按照B树存放。

12、Commit和rollback的区别 Rollback:回滚所有的事物。 Commit:提交当前的事物. 没有必要在动态SQL里写事物，如果要写请写在外面如： begin tran exec(@s) commit trans 或者将动态SQL 写成函数或者存储过程。

13、在查询Select语句中用Where字句限制返回的行数,避免表扫描,如果返回不必要的数据，浪费了服务器的I/O资源，加重了网络的负担降低性能。如果表很大，在表扫描的期间将表锁住，禁止其他的联接访问表,后果严重。

14、SQL的注释申明对执行没有任何影响

15、尽可能不使用光标，它占用大量的资源。如果需要row-by-row地执行，尽量采用非光标技术,如：在客户端循环，用临时表，Table变量，用子查询，用Case语句等等。游标可以按照它所支持的提取选项进行分类： 只进 必须按照从第一行到最后一行的顺序提取行。FETCH NEXT 是唯一允许的提取操作,也是默认方式。可滚动性可以在游标中任何地方随机提取任意行。游标的技术在SQL2000下变得功能很强大，他的目的是支持循环。有四个并发选项 READ_ONLY：不允许通过游标定位更新(Update)，且在组成结果集的行中没有锁。 OPTIMISTIC WITH valueS:乐观并发控制是事务控制理论的一个标准部分。乐观并发控制用于这样的情形，即在打开游标及更新行的间隔中，只有很小的机会让第二个用户更新某一行。当某个游标以此选项打开时，没有锁控制其中的行，这将有助于最大化其处理能力。如果用户试图修改某一行，则此行的当前值会与最后一次提取此行时获取的值进行比较。如果任何值发生改变，则服务器就会知道其他人已更新了此行，并会返回一个错误。如果值是一样的，服务器就执行修改。选择这个并发选项OPTIMISTIC WITH ROW VERSIONING:此乐观并发控制选项基于行版本控制。使用行版本控制，其中的表必须具有某种版本标识符，服务器可用它来确定该行在读入游标后是否有所更改。在SQL Server 中，这个性能由 timestamp 数据类型提供，它是一个二进制数字，表示数据库中更改的相对顺序。每个数据库都有一个全局当前时间戳值：@@DBTS。每次以任何方式更改带有 timestamp 列的行时，SQL Server先在时间戳列中存储当前的 @@DBTS 值，然后增加 @@DBTS 的值。如果某 个表具有 timestamp 列，则时间戳会被记到行级。服务器就可以比较某行的当前时间戳值和上次提取时所存储的时间戳值，从而确定该行是否已更新。服务器不必比较所有列的值，只需比较 timestamp 列即可。如果应用程序对没有 timestamp 列的表要求基于行版本控制的乐观并发，则游标默认为基于数值的乐观并发控制。 SCROLL LOCKS 这个选项实现悲观并发控制。在悲观并发控制中，在把数据库的行读入游标结果集时，应用程序将试图锁定数据库行。在使用服务器游标时，将行读入游标时会在其上放置一个更新锁。如果在事务内打开游标，则该事务更新锁将一直保持到事务被提交或回滚；当提取下一行时，将除去游标锁。如果在事务外打开游标，则提取下一行时，锁就被丢弃。因此，每当用户需要完全的悲观并发控制时，游标都应在事务内打开。更新锁将阻止任何其它任务获取更新锁或排它锁，从而阻止其它任务更新该行。然而，更新锁并不阻止共享锁，所以它不会阻止其它任务读取行，除非第二个任务也在要求带更新锁的读取。滚动锁根据在游标定义的 Select 语句中指定的锁提示，这些游标并发选项可以生成滚动锁。滚动锁在提取时在每行上获取，并保持到下次提取或者游标关闭，以先发生者为准。下次提取时，服务器为新提取中的行获取滚动锁，并释放上次提取中行的滚动锁。滚动锁独立于事务锁，并可以保持到一个提交或回滚操作之后。如果提交时关闭游标的选项为关，则 COMMIT 语句并不关闭任何打开的游标，而且滚动锁被保留到提交之后，以维护对所提取数据的隔离。所获取滚动锁的类型取决于游标并发选项和游标 Select 语句中的锁提示。锁提示 只读 乐观数值 乐观行版本控制 锁定无提示 未锁定 未锁定 未锁定 更新 NOLOCK 未锁定 未锁定未锁定 未锁定 HOLDLOCK 共享 共享 共享 更新 UPDLOCK 错误 更新 更新 更新 TABLOCKX 错误 未锁定 未锁定更新其它 未锁定 未锁定 未锁定 更新 *指定 NOLOCK 提示将使指定了该提示的表在游标内是只读的。

16、用Profiler来跟踪查询，得到查询所需的时间，找出SQL的问题所在;用索引优化器优化索引

17、注意UNion和UNion all 的区别。UNION all好

18、注意使用DISTINCT，在没有必要时不要用，它同UNION一样会使查询变慢。重复的记录在查询里是没有问题的

19、查询时不要返回不需要的行、列

20、用sp_configure 'query governor cost limit'或者SET QUERY_GOVERNOR_COST_LIMIT来限制查询消耗的资源。当评估查询消耗的资源超出限制时，服务器自动取消查询,在查询之前就扼杀掉。 SET LOCKTIME设置锁的时间

21、用select top 100 / 10 Percent 来限制用户返回的行数或者SET ROWCOUNT来限制操作的行

22、在SQL2000以前，一般不要用如下的字句: "IS NULL", "<>", "!=", "!>", "!<", "NOT", "NOT EXISTS", "NOT IN", "NOT LIKE", and "LIKE '%500'"，因为他们不走索引全是表扫描。也不要在Where字句中的列名加函数，如Convert，substring等,如果必须用函数的时候，创建计算列再创建索引来替代.还可以变通写法：Where SUBSTRING(firstname,1,1) = 'm'改为Where firstname like 'm%'（索引扫描），一定要将函数和列名分开。并且索引不能建得太多和太大。NOT IN会多次扫描表，使用EXISTS、NOT EXISTS ，IN , LEFT OUTER JOIN 来替代，特别是左连接,而Exists比IN更快，最慢的是NOT操作.如果列的值含有空，以前它的索引不起作用，现在2000的优化器能够处理了。相同的是IS NULL，"NOT", "NOT EXISTS", "NOT IN"能优化她，而"<>"等还是不能优化，用不到索引。

23、使用Query Analyzer，查看SQL语句的查询计划和评估分析是否是优化的SQL。一般的20%的代码占据了80%的资源，我们优化的重点是这些慢的地方。

24、如果使用了IN或者OR等时发现查询没有走索引，使用显示申明指定索引： Select * FROM PersonMember (INDEX = IX_Title) Where processid IN ('男'，'女')

25、将需要查询的结果预先计算好放在表中，查询的时候再Select。这在SQL7.0以前是最重要的手段。例如医院的住院费计算。

26、MIN() 和 MAX()能使用到合适的索引。

27、数据库有一个原则是代码离数据越近越好，所以优先选择Default,依次为Rules,Triggers, Constraint（约束如外健主健CheckUNIQUE……,数据类型的最大长度等等都是约束）,Procedure.这样不仅维护工作小，编写程序质量高，并且执行的速度快。

28、如果要插入大的二进制值到Image列，使用存储过程，千万不要用内嵌Insert来插入(不知JAVA是否)。因为这样应用程序首先将二进制值转换成字符串（尺寸是它的两倍），服务器受到字符后又将他转换成二进制值.存储过程就没有这些动作: 方法：Create procedure p_insert as insert into table(Fimage) values (@image), 在前台调用这个存储过程传入二进制参数，这样处理速度明显改善。

29、Between在某些时候比IN 速度更快,Between能够更快地根据索引找到范围。用查询优化器可见到差别。select * from chineseresume where title in ('男','女') Select * from chineseresume where between '男' and '女' 是一样的。由于in会在比较多次，所以有时会慢些。

30、在必要是对全局或者局部临时表创建索引，有时能够提高速度，但不是一定会这样，因为索引也耗费大量的资源。他的创建同是实际表一样。

31、不要建没有作用的事物例如产生报表时，浪费资源。只有在必要使用事物时使用它。

32、用OR的字句可以分解成多个查询，并且通过UNION 连接多个查询。他们的速度只同是否使用索引有关,如果查询需要用到联合索引，用UNION all执行的效率更高.多个OR的字句没有用到索引，改写成UNION的形式再试图与索引匹配。一个关键的问题是否用到索引。

 33、尽量少用视图，它的效率低。对视图操作比直接对表操作慢,可以用stored procedure来代替她。特别的是不要用视图嵌套,嵌套视图增加了寻找原始资料的难度。我们看视图的本质：它是存放在服务器上的被优化好了的已经产生了查询规划的SQL。对单个表检索数据时，不要使用指向多个表的视图，直接从表检索或者仅仅包含这个表的视图上读，否则增加了不必要的开销,查询受到干扰.为了加快视图的查询，MsSQL增加了视图索引的功能。

34、没有必要时不要用DISTINCT和ORDER BY，这些动作可以改在客户端执行。它们增加了额外的开销。这同UNION 和UNION ALL一样的道理。

select top 20 ad.companyname,comid,position,ad.referenceid,worklocation, convert(varchar(10),ad.postDate,120) as postDate1,workyear,degreedescription FROM jobcn_query.dbo.COMPANYAD_query ad where referenceID in('JCNAD00329667','JCNAD132168','JCNAD00337748','JCNAD00338345',
'JCNAD00333138','JCNAD00303570','JCNAD00303569',
'JCNAD00303568','JCNAD00306698','JCNAD00231935','JCNAD00231933',
'JCNAD00254567','JCNAD00254585','JCNAD00254608',
'JCNAD00254607','JCNAD00258524','JCNAD00332133','JCNAD00268618',
'JCNAD00279196','JCNAD00268613') order by postdate desc

35、在IN后面值的列表中，将出现最频繁的值放在最前面，出现得最少的放在最后面，减少判断的次数。

36、当用Select INTO时，它会锁住系统表(sysobjects，sysindexes等等)，阻塞其他的连接的存取。创建临时表时用显示申明语句，而不是 select INTO. drop table t_lxh begin tran select * into t_lxh from chineseresume where name = 'XYZ' --commit 在另一个连接中Select * from sysobjects可以看到 Select INTO 会锁住系统表，Create table 也会锁系统表(不管是临时表还是系统表)。所以千万不要在事物内使用它！！！这样的话如果是经常要用的临时表请使用实表，或者临时表变量。

37、一般在GROUP BY 个HAVING字句之前就能剔除多余的行，所以尽量不要用它们来做剔除行的工作。他们的执行顺序应该如下最优：select 的Where字句选择所有合适的行，Group By用来分组个统计行，Having字句用来剔除多余的分组。这样Group By 个Having的开销小，查询快.对于大的数据行进行分组和Having十分消耗资源。如果Group BY的目的不包括计算，只是分组，那么用Distinct更快

38、一次更新多条记录比分多次更新每次一条快,就是说批处理好

39、少用临时表，尽量用结果集和Table类性的变量来代替它,Table 类型的变量比临时表好

40、在SQL2000下，计算字段是可以索引的，需要满足的条件如下：

a、计算字段的表达是确定的

b、不能用在TEXT,Ntext，Image数据类型

c、必须配制如下选项 ANSI_NULLS = ON, ANSI_PADDINGS = ON, …….

41、尽量将数据的处理工作放在服务器上，减少网络的开销，如使用存储过程。存储过程是编译好、优化过、并且被组织到一个执行规划里、且存储在数据库中的SQL语句，是控制流语言的集合，速度当然快。反复执行的动态SQL,可以使用临时存储过程，该过程（临时表）被放在Tempdb中。以前由于SQL SERVER对复杂的数学计算不支持，所以不得不将这个工作放在其他的层上而增加网络的开销。SQL2000支持UDFs,现在支持复杂的数学计算，函数的返回值不要太大，这样的开销很大。用户自定义函数象光标一样执行的消耗大量的资源，如果返回大的结果采用存储过程

42、不要在一句话里再三的使用相同的函数，浪费资源,将结果放在变量里再调用更快

43、Select COUNT(*)的效率教低，尽量变通他的写法，而EXISTS快.同时请注意区别： select count(Field of null) from Table 和 select count(Field of NOT null) from Table 的返回值是不同的！！！

44、当服务器的内存够多时，配制线程数量 = 最大连接数+5，这样能发挥最大的效率；否则使用 配制线程数量<最大连接数启用SQL SERVER的线程池来解决,如果还是数量 = 最大连接数+5，严重的损害服务器的性能。

45、按照一定的次序来访问你的表。如果你先锁住表A，再锁住表B，那么在所有的存储过程中都要按照这个顺序来锁定它们。如果你（不经意的）某个存储过程中先锁定表B，再锁定表A，这可能就会导致一个死锁。如果锁定顺序没有被预先详细的设计好，死锁很难被发现

46、通过SQL Server Performance Monitor监视相应硬件的负载 Memory: Page Faults / sec计数器如果该值偶尔走高，表明当时有线程竞争内存。如果持续很高，则内存可能是瓶颈。

Process:

1、% DPC Time 指在范例间隔期间处理器用在缓延程序调用(DPC)接收和提供服务的百分比。(DPC 正在运行的为比标准间隔优先权低的间隔)。 由于 DPC 是以特权模式执行的，DPC 时间的百分比为特权时间百分比的一部分。这些时间单独计算并且不属于间隔计算总数的一部 分。这个总数显示了作为实例时间百分比的平均忙时。

2、%Processor Time计数器　如果该参数值持续超过95%，表明瓶颈是CPU。可以考虑增加一个处理器或换一个更快的处理器。

3、% Privileged Time 指非闲置处理器时间用于特权模式的百分比。(特权模式是为操作系统组件和操纵硬件驱动程序而设计的一种处理模式。它允许直接访问硬件和所有内存。另一种模式为用户模式，它是一种为应用程序、环境分系统和整数分系统设计的一种有限处理模式。操作系统将应用程序线程转换成特权模式以访问操作系统服务)。特权时间的 % 包括为间断和 DPC 提供服务的时间。特权时间比率高可能是由于失败设备产生的大数量的间隔而引起的。这个计数器将平均忙时作为样本时间的一部分显示。

4、% User Time表示耗费CPU的数据库操作，如排序，执行aggregate functions等。如果该值很高，可考虑增加索引，尽量使用简单的表联接，水平分割大表格等方法来降低该值。 Physical Disk: Curretn Disk Queue Length计数器该值应不超过磁盘数的1.5~2倍。要提高性能，可增加磁盘。 SQLServer:Cache Hit Ratio计数器该值越高越好。如果持续低于80%，应考虑增加内存。 注意该参数值是从SQL Server启动后，就一直累加记数，所以运行经过一段时间后，该值将不能反映系统当前值。

47、分析select emp_name form employee where salary > 3000 在此语句中若salary是Float类型的，则优化器对其进行优化为Convert(float,3000)，因为3000是个整数，我们应在编程时使用3000.0而不要等运行时让DBMS进行转化。同样字符和整型数据的转换。

48、查询的关联同写的顺序

select a.personMemberID, * from chineseresume a,personmember b where personMemberID = b.referenceid and a.personMemberID = 'JCNPRH39681' （A = B ,B = '号码'）

select a.personMemberID, * from chineseresume a,personmember b where a.personMemberID = b.referenceid and a.personMemberID = 'JCNPRH39681' and b.referenceid = 'JCNPRH39681' （A = B ,B = '号码'， A = '号码'）

select a.personMemberID, * from chineseresume a,personmember b where b.referenceid = 'JCNPRH39681' and a.personMemberID = 'JCNPRH39681' （B = '号码'， A = '号码'）

49、

(1)IF 没有输入负责人代码 THEN code1=0 code2=9999 ELSE code1=code2=负责人代码 END IF 执行SQL语句为: Select 负责人名 FROM P2000 Where 负责人代码>=:code1 AND负责人代码 <=:code2

(2)IF 没有输入负责人代码 THEN Select 负责人名 FROM P2000 ELSE code= 负责人代码 Select 负责人代码 FROM P2000 Where 负责人代码=:code END IF 第一种方法只用了一条SQL语句,第二种方法用了两条SQL语句。在没有输入负责人代码时,第二种方法显然比第一种方法执行效率高,因为它没有限制条件; 在输入了负责人代码时,第二种方法仍然比第一种方法效率高,不仅是少了一个限制条件,还因相等运算是最快的查询运算。我们写程序不要怕麻烦

50、关于JOBCN现在查询分页的新方法（如下），用性能优化器分析性能的瓶颈，如果在I/O或者网络的速度上，如下的方法优化切实有效，如果在CPU或者内存上，用现在的方法更好。请区分如下的方法，说明索引越小越好。

begin

DECLARE @local_variable table (FID int identity(1,1),ReferenceID varchar(20))

insert into @local_variable (ReferenceID)

select top 100000 ReferenceID from chineseresume order by ReferenceID

select * from @local_variable where Fid > 40 and fid <= 60

end 和

begin

DECLARE @local_variable table (FID int identity(1,1),ReferenceID varchar(20))

insert into @local_variable (ReferenceID)

select top 100000 ReferenceID from chineseresume order by updatedate

select * from @local_variable where Fid > 40 and fid <= 60

end 的不同

begin

create table #temp (FID int identity(1,1),ReferenceID varchar(20))

insert into #temp (ReferenceID)

select top 100000 ReferenceID from chineseresume order by updatedate

select * from #temp where Fid > 40 and fid <= 60 drop table #temp

end

存储过程编写经验和优化措施 

　　一）、适合读者对象：数据库开发程序员，数据库的数据量很多，涉及到对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数据库在双机之间的切换 .