重庆某医院Oracle调整及升级报告

xxx第三人民医院是一所集医疗、科研、教学为一体的综合性三级甲等医院。医院现有病床650张，日门诊量1000人次左右，住院人数500人左右。有26个临床科室、11个医技科室，并开设有60多个专科专病门诊。

医院目前使用了医院信息系统标准版、门诊医生工作站系统、病案管理系统、LIS系统（LIS接口程序从ZLHIS中提取病人信息和并完成计费功能），没有使用住院医生工作站。高峰期用户连接数160个左右，HIS系统工作站150台左右。

医院的软硬件环境如下：

 

2006年3月8日，医院系统管理人员报告，系统在业务高峰期的9:00:~11:00时段，门诊收费时有停顿的现象，有时一张单据保存需要1分钟左右的时间。LIS系统提取病人信息的速度非常慢，等待时间以分钟计。由于整个系统的运行速度较慢，已影响到医院正常业务的开展，医院各科室的意见比较大。服务器CPU占用率近期从原来的15%增长到现在的40%左右。

市二院与市三院在业务规模和应用规模上比较接近，我们希望从两家医院的应用环境对比分析中找到一些解决问题的线索。

 

 

从上表中，我们看到市二院启用了住院医生工作站系统，没有使用门诊医生工作站系统，二家医院在用户连接数和硬件环境上都比较接近。市二院使用ZLHIS的时间要比市三院长，数据量也比市三院大，但市二院在使用过程中没有反映速度慢的问题，系统运行速度比较理想。

通过以上的对比分析，我们发现以下的主要差异：

两个版本的性能差异对ZLHIS而言，主要体现以下两个方面：

   1)、10.1.3使用的本地管理的表空间（LMT），而816默认情况下使用的是字典管理的表空间(DMT)。LMT很好的解决了DMT的递归SQL与热块争用问题。可以带来数据写入和空间管理性能很大的提升。

   2)、10.1.3中本地管理的表空间，可以有效避免数据文件碎片，自动合并未使用的数据块。

   3)、Oracle 10g新增的回退段自动管理功能，很好地解决了8i下的回退段设置问题，在8i下如果回退段设置不当，会严重地影响数据库的性能。

   进一步分析数据库参数，发现两家医院的cursor_sharing参数设置不相同。三院的设置为exact，也就是精确匹配。由于ZLHIS中绑定变量使用得不是很好，大量的SQL语句需要做硬分析(Hard Parse)。

   二院设置为similar，这是10g新增加的选项。简单的讲，为了解决仅仅因为列值不同而导致SQL语句不能共享的问题，引入了CURSOR_SHARING参数，使这类SQL也可以使用共享SQL，从而提高效率，降低耗费在SQL语句解析上的CPU等各种资源。但使用similar选项也可以带来一些问题，比如把空格也解析成绑定变量 ；市二院的一日费用清单的数据源就出现过这种问题。最好的方式还是在编程时就避免硬编码的SQL语句，使用绑定变量。从二院的实际应用效果，使用simimar选项，效果比较明显。

3、市二院没有使用LIS系统。

市三院的LIS系统与ZLHIS进行了连接，LIS系统接口从ZLHIS中读取视图(VIEW)提取病人信息，并调用ZLHIS数据库的存储过程(PROCEDURE)完成计费功能。

首先，我想到是否因为系统问题导致性能下降，所以到达现场后，依次进行了如下检查：

1)、使用Windows 2000任务管理器查看CPU及内存使用情况，CPU利用率偏高，且波动较大,符合系统管理员的描述，内存利用率较为平稳。

2)、使用Windows 2000的事件查看器查看操作系统日志，没有发现异常。

3)、查看Oracle的后台进程文件(alert_orcl.log)，没有发现异常。初步排除是Oracle错误引起的性能下降

4)、检查磁盘阵列，没有发现磁盘损坏，磁盘阵列工作正常。

5)、医院管理人员配合检查了网络环境，确认网络设备工作正常，没有出现不稳定现象。

经过以上的检查分析，排除了硬件环境和操作系统方面的故障引起的性能下降，接下来，主要从应用和数据库方面进行分析与调整。

 

在诊断和调整过程中，主要使用Statspack和SQL_TRACE工具。

Oracle Statspack工具从816中开始引入Oracle中，已成为DBA用来诊断数据库性能的强有力工具。它通过收集一组数据库运行数据，记录数据库的运行状况，生成Statspack报告。通过对Statspack报告的分析确定性能的瓶颈所在。目前，Statspack工具已经在Oracle性能调整中得到了广泛应用。

SQL_TRACE是Oracle提供的用于进行SQL跟踪的手段。生成的Trace文件进行格式化后，提供了包括SQL文本，执行计划，执行效率等重要信息；是强有力的辅助诊断工具.在日常的数据库问题诊断和解决中，SQL_TRACE是非常常用的方法。

本次调整使用Statspack工具收集业务高峰期的系统运行情况，并以此作为调整的基础。Sql_Trace主要用于跟踪分析Statspack报告中的Top SQL部分的SQL语句，还用于定位引起个别功能运行速度较慢的SQL语句。

  经过对Statspack报告的分析，我们初判断影响性能的主要原因有：

1、“病人费用汇总”表的行级锁定。医保事务需要更新当天的病人费用汇总表中的一行，获得一个行级锁定，当普通业务也需要更新相同的行时，必须等待医院业务事务完成，这种等待就造成了系统速度慢甚至是死机的现象。

2、从高峰期生成的Statspack报告中关于SQL共享使用的指标都不理想，软解析比率为：75.71%。原因是ZLHIS没有很好的使用绑定变量，造成了语句大量的硬解析，耗费了大量的系统资源，这也是医院CPU利用率增高的原因之一。

3、从报告的TOP SQL部分发现LIS接口提取病人信息的SQL逻辑读很高，进一步使用SQL_Trace功能分析语句，发现这段SQL对“病人挂号记录”和”病人信息”表都做了全表扫描，运行效率低下。

详细的Statspack报告分析请参见附录A和附录B。

   基于上述分析，于2006年3月12日将Oracle数据库系统升级到了10.2.0，升级后主要做了以下调整：

1、使用cursor_sharing=similar参数,以使ZLHIS的大量硬编码的SQL语句能够共享使用，解决绑定变量的问题，降低SQL硬解析的系统消耗。(关于使用绑定变量效率提升的实验请参见附录C)。

2、建立基于函数to_char的索引，改善LIS提取病人信息的SQL语句的执行效率。

3、使用定时刷新的物化视图代替“病人费用汇总”表，并创建一个“病人费用汇总”视图，然后在视图上建立替代触发器，屏蔽掉程序的DML语句。

4、表空间全部采用ASSM方式的本地管理表空间(LMT)，减少维护表空间的递归SQL消耗，避免Freelist的争用。

经过升级调整后，观察CPU占用率从原来的平均45%左右，降到了现在的20%左右。

 

 

经过升级调整后，LIS提取病人信息的速度有了明显提高，检验科使用人员比较满意，医院各业务窗口部门普遍反映速度有了提高。

 

 

在Oracle8i下优化器默认为Choose方式(optimizer_mode=choose)，Oracle 10g下优化器默认为All_rows(CBO的一种)，为保证语句的执行计划与在8i下的一致性，并考虑到程序有的地方强行使用了RBO，将优化器模式修改为choose。Oracle 10g对CBO有了更好的支持，但是由于优化器和其他环境的变化导致了部分SQL语句在10g下运行速度较慢。 主要体现在以下两个方面：
    1)、升级后部分药品相关的模块运行速度有了下降，对这部分SQL进行了调整，运行速度恢复正常。

2)、部分报表执行速度较慢，接下来对系统中的报表SQL进行了集中优化和调整。

调整SQL时主要使用了以下几种方法：

1)、对相关表作分析(Analyze)，收集准确的统计信息。

2)、禁用部分不合理的索引，消除其对执行计划的影响，如：药品收发记录_IX_库房ID。

3)、使用提示字(Hint)，稳定SQL语句的执行计划。

更为详细SQL调整技巧请参见相关文档。

 

 此次数据库分析调整印证了“60%的性能问题都是应用问题”这句名言。使用cursor_sharing=similar只是权宜之际，并可能会带来一定的副作用，我们的程序在绑定变量的使用上还有很长的路要走。“病人费用汇总”这种在当初看起来不错的设计，由于医保业务的发展，在今天却成为了一个严重影响性能的缺陷。我们在数据库结构设计中不能默守陈规，必须适应不断变化的业务需要。

 有时，一条不良的SQL语句可能会导致整个系统的性能下降，影响客户对整个系统的评价，最好的方式是在程序开发阶段就把这些劣质SQL扼杀在摇篮里，有时甚至为了性能，牺牲部分不实用的功能也是值得的，我们需要在性能与功能之间做出平衡。出现性能问题时我们需要用科学的方法分析解决问题，做出正确的判断，而不是依据经验和主观意识进行判断。

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

我们利用Oracle 的Statspack工具生成了上午10:15至11:00的报告，用以作为分析和调整的基础。

 

 1)、Redo size:   16,906.18: 每秒钟产生16,906.18字节的重做日志。每分钟产生约1M左右的重做日志(16,906.18*60/1024)，系统压力相对还是比较大。

2)、Physical reads 92.41物理读为92.41，很少基本可以忽略，初步判断，高整缓冲的设置没有太大的问题。

3)、Transactions: 1.91 每秒钟2个事务左右，也证明系统压力较大。

 

1)、Buffer Hit   Ratio  99.72 从这部分看高速缓存命中率为99.72，比较理想，不需要再加大高速缓存。

2)、Soft Parse Ratio 75.71 。这项值低于90%，一般证明系统没有很好的使用绑定变量。ZLHIS在绑定变量的使用上还有很长的路要走。建议升级到Oracle 9i/10g 使用cursor_sharing=similar参数，可以取得较好的效果。

3)、In-memory Sort Ratio:       99.90，体现出排序基本上都在内存中进行，不需要调整排序区。

 

 

1) db file sequential read 为最主要的等待事件(87.46%)，这通常是指单一的数据块读操作（例如，索引的读取）。该值过大说明表的连接顺序很糟糕。或者使用了非选择性的索引。数据库的主要调整方向为：优化SQL，使用正确的索引，改善表连接效果。

2)enqueue闩锁是底层的队列机制，用于保护系统的全局区（SGA）的内存结构。使用本地管理的表空间(LMT)可以有效降低这部分等待事件,目前ZLHIS使用的是字典管理的表空间（DMT），会造成Fet$、Uet$系统表的争用（字典管理表空间使用这两个表记录字典管理表空间的使用情况，LMT使用了位图(BITMAP)管理表空间，显著地提高了效率。

3) SQL*Net more data from dblink、SQL*Net message from dblink，为系统空闲事件，可不必关注，造成这两者较高的原因可能是ZLHIS通过网络向医保前置机大量取数产生的。

4)enqueue 进而查看报告的另一部分

(1)、唯一索引中的重复索引，需要提交(commit)或回段(rollback)事务。

(2)、位图索引的更新，由于ZLHIS中没有使用位图索引，所以这应该不是主要原因。

(3)、多个用户同时更新同一个块，即热点块争用。典型的就是“病人费用汇总”表的块争用问题，可以判定这是Enqueue等待事件较高的原因。

从statspack报告的磁盘IO部分来看，发现ZLEXPENSE和ZLPATIENT的磁盘IO最大。ZLEXPENSE上面存储了费用相关的表，IO大是正常的。ZL9PATIENT的IO偏大是不正常的，可以判定是由于LIS系统提取病人信息的的全表扫描引起大量的磁盘IO。可以看到，解决LIS系统提取病人信息的全表扫描，是系统调整的一个关键点。

 

 

从SQL ordered by Gets部分可以看出，大部分的低效SQL大多数都与“病人费用汇总”有关，而且多以update操作为主。在ZLHIS中很多存储过程都要update“病人费用汇总”表，且很多update语句没有很好用到索引。还有就是update语句需要获得Oracle行级排它锁，这也是造成Enqueue等待事件较高的原因。解决”病人费用汇总”行级锁定和访问效率是提高运行速度的又一关键点。

 在32位平台上，Oracle进程的内存寻址空间有2G的限制，实际上Oracle.exe进程能够使用的内存为1.75G左右。那么SGA+PGA只能有1.75G左右。市三院在高峰期会话数有180左右，最大时达到230个session。由于使用专用服务器，每个Session大概需要2M左右的合内存。所以整个 SGA不能大于1290M (1750M-460M)。

 在实例SGA设置时，正是遵循了上述的原则。要使用更大的SGA，有两种解决方案：

 1)、使用共享服务器模式。但由于ZLHIS系统有很多的大事务要运行，这种模式会带来，会话事务处理的等待。

 2)、将平台升级到64bit。这需要大量的硬件和软件投资。

 由于从Statspack的分析来看，SGA各个部分的使用效果都不错，所以没有调整SGA的必要。

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

升级后Oracle10g并进行调整后，我们在同一时间段生成Staspack报告，发现各项指标有了明显改善。报告的前半部分如下：

 

 

 

 

 

 

 

相比8i下的statspack主要有以下几点改善：

1)、Soft Parse %:   87.47

Sql语句软解析比率有了很大的提高，提高了10%左右，主要是在10g使用了cursor_sharing=similar参数。

2)、Enqueue等待事件不再是主要的等待事件，主要是得益于将“病人费用汇总”用物化视图代替，减少了表的行级锁定。

3)、最主要的等待事件变成了CPU time，此事件为Oracle空闲事件，空闲事件是不能避免的，在性能调整中，不必太关注此类事件。

4)、TOP 5 部分的几个等待事件等待时间都很小。对比8i下的top5中的几个等待事件的等待时间减少了很多。 如db file sequential read 由原来的138,851ms，减少到现在的718ms。可见改善的幅度还是很大的。

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

1)、表空间的处理。

由于10G与8I的数据文件默认存放路径不一致，不能直接导入表空间，需要先创建相关的表空间。我在8i下用SQL生成SQL的方式重新创建表空间的脚本，对生成的后稍加修改，就是创建表空间的脚本。

 

2)、IMP – 00020错误的解决。

在导入“病人信息”表时出现IMP-00020错误。

导入了                                                        263066 行

 

   病人信息不能导入，查询Oracle MetaLink此错误的解释为：

由于在”病人信息”上使用了基于to_char的函数索引，先怀疑是函数的问题。按照metaLink的解释，先试着加大导入时的buffer和使用commit=y、constrains=n、indexes=n参数，还是出现同样的错误。于是初步判断是由于导出时，使用了direct=y造成的，是遇上了816的一个bug。于是决定使用冷备份还原，重新导出“病人信息”表， 重新导入成功，至此问题解决。

主要参数说明：

1)、SGA使用手动管理。从实际市二院等医院的使用情况来看，自动管理SGA的效果不是很好，可能跟ZLHIS的应用有关。各SGA的大小参照8i下的SGA设置，因为从前面的分析来看，8i下的SGA没有什么大的问题，运行效果较好。

 

2)、Cursor_sharing = similar参数的使用，使用此参数的目的在于SQL语句软件解析的比率。此参数，可以让一些硬编码的SQL语句，使用绑定变量，这由Oracle自动完成，下面是Trace出来一段业务SQL。

   可以看到Oracle已经对SQL语句使用了绑定变量，也就节约了解析时间，但这个参数可能会带来一些副作用，比如把空格翻译成绑定变量等，但从目前使用的情况来看，可以带来一定的性能提升。最根本解决绑定变量问题的途径只有一个：修改软件,使用绑定变量。

   下面通过一个实验来看使用与不使用绑定变量之间的性能差别：

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 我们看到同样插入100000行数据，使用绑定变量用了5秒钟时间，而不使用绑定变量，共用了1分30秒钟。两者的性能差别居然有18倍的差距！可以说目前ZLHS最大的性能问题，主要是没有使用绑定变量。

3)、processes，默认为150，太小，加大到400。

   用VB写了个小程序，使用ADO循环连接Oracle,测试可以连接的最大用户数。发现只有200个SESSION左右。虽然医院的高峰期的会话数只有180左右，但为了保证有扩展性，决定调整Oracle.exe进程的堆栈大小。注意必须停止Oracle服务才能调整，且当前目录必须为Oracle.exe的当前目录。

 

 

 

 

D:\oracle\product\10.2.0\db_1\BIN>

   调整后的连接数可以达到250左右。

 

 

 

是由于升级到10g后，默认使用CBO优化器，而CBO必须依赖准确的统计信息来确定SQL语句的执行计划。统计信息错误，将会影响SQL语句的执行计划，使Oracle使用错误的执行路径，从而影响执行速度。使用ANALYZE命令对表进行分析：

用SQL生成SQL的方式产生分析脚本对表进行全表分析。

病人费用记录和药品收发记录由于数据量太大，对其只进行5%的采样分析。

通过分析报表数据源，发现<发药时间>参数SQL对药品收发记录使用了全表扫描。

SQL语句：

select distinct A.审核日期,名称

是由于升级到10g后，使用了CBO优化器，而药品收发记录的统计信息还没有更新，导致Oracle使用了全表扫描，而不是使用审核日期上的建立的索引。前面做5%的采样分析获得的统计信息不准确。决定对药品收发记录表进行全表分析。

 

 

 

  使用SQL_TRACE功能产生Trace文件。分析产生的Trace文件发现如下一段：

**************************************************************************************************************************

 

 

发现这段提取病人信息的SQL使用“病人费用记录”表来实现只显示产生费用的病人信息。这段SQL的物理读非常大(16541，红色字体部分) 进一步分析下面的执行计划发现，使用的”病人费用记录_IX_病人ID”索引，由于当前在院的病人数较多，效率很低。一日清单一般只需要查询当天的费用，应该“登记时间”索引的效率。问题原因与7.1一样，都是对象的统计信息不准确造成的。解决办法同样是对表作全表分析。

 

使用服务器管理工具对导入的数据进行检查、修正。以非ZLHIS用户进行各种业务的测试。至此升级完成。

 

检验科LIS系统通过接口程序提取zlhis病人信息速度很慢，以分钟计算。

可能是提取病人信息的VIEW的SQL的执行效率低下，根据执行计划调整SQL语句。

查看原始的视图语句：

 

提取LIS接口程序读取病人信息的SQL语句，查看语句的执行计划：

分析语句的执行计划，发现病人信息、病人挂号记录表等为全表扫描。首先需要消除对“病人挂号记录“的全表扫描。

 

发现病人挂号记录有478299条记录，数据量还是比较大。进一步分析语句，发现条件中有A.门诊号=J.门诊号，其中A表为病人信息，J表为病人挂号记录。查看两个表的索引情况。

 

   发现病人挂号记录的门诊号上都没有建立索引,在表的“门诊号”字段上建立索引。

   改造原来的(select distinct (select 编码 from 部门表 where id=执行部门id) as 科室号,门诊号 from 病人挂号记录 )语句，将子查询的方式改为直接表连接。

改造后的SQL语句如下：

改造后的SQL语句性能有了很大的很高，提取一个病人的信息由原来的30秒钟左右，提高到现在的5秒钟左右。再次查看执行计划：

SELECT STATEMENT, GOAL = CHOOSE                                             

   发现病人费用记录已经使用索引，但病人信息表还是全表扫描。下一步就是消除病人信息的全表扫描。

通过住院号进行查询，应该是可以用到建立在住院号上的索引，但这里没有用到，原因是视图中对住院号使用了to_char()函数，即：to_char(A.住院号) as 住院号。经咨询医院和公司的其他人员，必须使用to_char进行转换，否则LIS的接口程序不能提取病人的信息。要彻底解决此问题，有两种方式，1、使用基本函数的索引。2、通知三晶公司修改LIS结口。但第二种可能性很小。于是决定采用基于函数的索引。

1、修改init.ora参数

     query_rewrite_enabled = TRUE

2、创建基于函数的索引

    create index 病人信息_IX_char住院号 on 病人信息(to_char(住院号)) tablespace zl9patient 

3、由于只有在CBO 下才能使用基于函数的索引，对”病人挂号记录”等作分析。

4、改造SQL语句，加入提示字(hints：/*+frist_rows*/强制使用CBO。

调整后的SQL语句执行计划：

经过调整，LIS系统提取病人信息的语句，速度得到了很大的提高，大概在1秒左右。此至问题解决。