医院信息系统数据库性能优化研究
摘要
医院信息系统(HIS)数据库具有高吞吐量，数据删除／更新／插入操作频繁，数据繁杂宠大，且要求能高效、及时、准确进行数据处理的特点。故如何对数据库性能在进行优化设计，即提高数据库的吞吐量、减少用户等待时间具有重大意义。
传统的数据库性能优化主要从操作系统、客户端应用软件程序设计、网络及其它硬件设备等方面来考虑，这种方法只是调整数据库的周边环境，只能暂时缓解问题，而不能从根本上解决问题。实际应用中，更多情况是信息系统(包括数据库系统)都已设计好，只是在运行的过程中随着数据规模的增大，使得系统出现周期性性能问题。本文以医院信息系统中的ORACLE数据库系统为平台，提出在已有的硬件设施及数据库系统上从数据体系结构，存储结构及对象结构等方面“三位一体”解决问题的方法。
本文从目前的医院信息系统现状分析，提出系统性能评价指标，并从ORACLE体系结构出发，对内存区中的系统全局区及程序全局区进行合理的组织与分配，确定合理的命中率，这是性能优化的关键；ORACLE存储结构采用的互为关系的逻辑存储结构与物理存储结构，故确定其中的表空间、数据文件、控制文件的大小是性能优化的基础；数据库对象结构中的索引、簇、视图、存储过程等对其进行合理的设计，对大型数据库的性能有着重要的影响；最后便是在SQL／PLUS境下，通过与基于代价与基于规则的方法进行比较，对合理高效的SQL语句进行组织，并列出优化后系统性能的情况。
The database of Hospital Information Sysytem(HIS) is specialized by its large input/output datas , frequency data delete/update/insert and it requires the database to process the datas with efficiency , timely and accuracy. So it is very important to tune the database's performance, i.e. improve the input/output mount of database and decrease the waiting time of system to respond.
We only consider the operation system, the applied software on client machine, net and other hardware etc. , When tuning the database performance with traditional way. If we only tune the database's round environment, it just respited the situation, and could not resolve the problem at all. In practice, the informaton system (including database) was well designed and the database's performance is turn up with increasing database.
This paper is based on database of Hospital Information System .The paper considers to tune database's performance from system structure, store structure and object structure etc. From ORACLE system structure, the paper organizes and allocates memory with SGA (system global area) and PGA (program global area). The key of performance tuning is to confirm the hit ratio of system. The ORACLE system's store structure includes physicial structure and logical structure. So , it is the basis to decide the size of tablespace, data file and control file. The database object includes index, cluster, views and store procedure etc., the object have important effect on database. Lastly, it contrasts the ways of Based on Cost and Based on Rule and organizes the SQL under SQL/PLUS system environment.
传统的数据库性能优化主要从操作系统、客户端应用软件程序设计、网络及其它硬件设备等方面来考虑，这种方法只是调整数据库的周边环境，只能暂时缓解问题，而不能从根本上解决问题。实际应用中，更多情况是信息系统(包括数据库系统)都已设计好，只是在运行的过程中随着数据规模的增大，使得系统出现周期性性能问题。本文以医院信息系统中的ORACLE数据库系统为平台，提出在已有的硬件设施及数据库系统上从数据体系结构，存储结构及对象结构等方面“三位一体”解决问题的方法。
本文从目前的医院信息系统现状分析，提出系统性能评价指标，并从ORACLE体系结构出发，对内存区中的系统全局区及程序全局区进行合理的组织与分配，确定合理的命中率，这是性能优化的关键；ORACLE存储结构采用的互为关系的逻辑存储结构与物理存储结构，故确定其中的表空间、数据文件、控制文件的大小是性能优化的基础；数据库对象结构中的索引、簇、视图、存储过程等对其进行合理的设计，对大型数据库的性能有着重要的影响；最后便是在SQL／PLUS境下，通过与基于代价与基于规则的方法进行比较，对合理高效的SQL语句进行组织，并列出优化后系统性能的情况。
The database of Hospital Information Sysytem(HIS) is specialized by its large input/output datas , frequency data delete/update/insert and it requires the database to process the datas with efficiency , timely and accuracy. So it is very important to tune the database's performance, i.e. improve the input/output mount of database and decrease the waiting time of system to respond.
We only consider the operation system, the applied software on client machine, net and other hardware etc. , When tuning the database performance with traditional way. If we only tune the database's round environment, it just respited the situation, and could not resolve the problem at all. In practice, the informaton system (including database) was well designed and the database's performance is turn up with increasing database.
This paper is based on database of Hospital Information System .The paper considers to tune database's performance from system structure, store structure and object structure etc. From ORACLE system structure, the paper organizes and allocates memory with SGA (system global area) and PGA (program global area). The key of performance tuning is to confirm the hit ratio of system. The ORACLE system's store structure includes physicial structure and logical structure. So , it is the basis to decide the size of tablespace, data file and control file. The database object includes index, cluster, views and store procedure etc., the object have important effect on database. Lastly, it contrasts the ways of Based on Cost and Based on Rule and organizes the SQL under SQL/PLUS system environment.
引文
1.Mullner, Ross M.,The Internet and Healthcare&colon: Opportunities and Challenges. Journal of medical Systems. 2002(6)
2.Lee Junyoung, Kim Jeonghoon, Lee Jeongwhan, Hwang Seoncheol, Lee Byungchae, Jeong Keesam, Cho Chongkwan, Lee Myoungho. The Study on the Web-Based Clinical database Management System.Journal of Medical Systems. 2001(5)
3.Gaja krishnavaidyanatha, kirtikumar deshpande john kostelac, Oracle performance tuning 101.Mcgraw-hill education(asia) publication 2001(1)
4.Donald k. burleson, Oracle High-performance Tuning with Statspack. Oracle press 2001(1)
5.Eyal aronoff, evin loney, noorali sonawalla. Oracle advanced tuning & administrator, oracle press 2000(1)
6.George roch, Kevin Loney. Oracle:The complete Reference. Oracle press. 1998(1)
7.Josephe Johnson, Matthew weishau. OCP:Oracle8i DBA Performance tuning and Network administrator Study Guide. SYBEX InCo. 2001(1)
8.Doug Stuas, Biju Thomas. Oracle8i DBA Architecture & Administrator and Backup & Recovery Study Guide. SYBEX InCo.2001(1)
9．王燚，王磊，蒋蕊等．ORACLE体系结构．北京：机械工业出版社．2000(1)
10．刘晓霞，王联华，徐海琛等．Oracle8优化技术．北京：机械工业出版社．1998(1)
11．捷智开发团体．Oracle8起步与进阶．北京：人民邮电出版社．2001(3)
12．顾诚．Oracle数据库系统应用开发．北京：电子工业出版社，1998(1)
13．乐嘉锦，王兰成等．Oracle8初学者指南．北京：机械工业出版社．2002(1)
14．刘刚，曲建华等．医院信息系统oracle服务器端数据库维护例析．医疗设备信息．2002(3)
15．金宇红．医院信息系统数据库性能调整．计算机管理．2002(2)
16．何泽恒，吕建波，李建军．基于ORACLE数据库的信息系统优化设计 哈尔滨商业大学学报．2002(2)
17．张俊红．ORACLE数据库性能优化的主要方法．计算机系统应用．2001(08)
18．郭旭升，金宇红．医院信息系统数据库性能调整．计算机系统应用．2000(08)
19．吴先旺，杨永强，李伟华．ORACLE数据库性能优化．微电子学与计算机．1994(04)
20．刘伟，杜薇．关于Oracle关系数据库性能调整的研究．微型机与应用2001(10)
21．丁杜秋．探讨医院信息系统建设的几个问题．江苏卫生事业管理1997(06)
22．马金明，王洪涛．医院信息系统建设的科学规划信息技术．2002(05)
23．李之芳．如何构建高性能的医院信息系统．医学临床研究．2002(05)
2.Lee Junyoung, Kim Jeonghoon, Lee Jeongwhan, Hwang Seoncheol, Lee Byungchae, Jeong Keesam, Cho Chongkwan, Lee Myoungho. The Study on the Web-Based Clinical database Management System.Journal of Medical Systems. 2001(5)
3.Gaja krishnavaidyanatha, kirtikumar deshpande john kostelac, Oracle performance tuning 101.Mcgraw-hill education(asia) publication 2001(1)
4.Donald k. burleson, Oracle High-performance Tuning with Statspack. Oracle press 2001(1)
5.Eyal aronoff, evin loney, noorali sonawalla. Oracle advanced tuning & administrator, oracle press 2000(1)
6.George roch, Kevin Loney. Oracle:The complete Reference. Oracle press. 1998(1)
7.Josephe Johnson, Matthew weishau. OCP:Oracle8i DBA Performance tuning and Network administrator Study Guide. SYBEX InCo. 2001(1)
8.Doug Stuas, Biju Thomas. Oracle8i DBA Architecture & Administrator and Backup & Recovery Study Guide. SYBEX InCo.2001(1)
9．王燚，王磊，蒋蕊等．ORACLE体系结构．北京：机械工业出版社．2000(1)
10．刘晓霞，王联华，徐海琛等．Oracle8优化技术．北京：机械工业出版社．1998(1)
11．捷智开发团体．Oracle8起步与进阶．北京：人民邮电出版社．2001(3)
12．顾诚．Oracle数据库系统应用开发．北京：电子工业出版社，1998(1)
13．乐嘉锦，王兰成等．Oracle8初学者指南．北京：机械工业出版社．2002(1)
14．刘刚，曲建华等．医院信息系统oracle服务器端数据库维护例析．医疗设备信息．2002(3)
15．金宇红．医院信息系统数据库性能调整．计算机管理．2002(2)
16．何泽恒，吕建波，李建军．基于ORACLE数据库的信息系统优化设计 哈尔滨商业大学学报．2002(2)
17．张俊红．ORACLE数据库性能优化的主要方法．计算机系统应用．2001(08)
18．郭旭升，金宇红．医院信息系统数据库性能调整．计算机系统应用．2000(08)
19．吴先旺，杨永强，李伟华．ORACLE数据库性能优化．微电子学与计算机．1994(04)
20．刘伟，杜薇．关于Oracle关系数据库性能调整的研究．微型机与应用2001(10)
21．丁杜秋．探讨医院信息系统建设的几个问题．江苏卫生事业管理1997(06)
22．马金明，王洪涛．医院信息系统建设的科学规划信息技术．2002(05)
23．李之芳．如何构建高性能的医院信息系统．医学临床研究．2002(05)