数据库应用系统的性能分析与优化方法研究
|
摘要
数据库系统的优化对于整个系统的正常运行起着至关重要的作用，但是，它却是一项非常复杂的工作。
本文从数据库服务器、应用程序、操作系统及网络等几方面对数据库应用系统在运行过程中可能出现的各种性能问题作出了分析，同时以Oracle数据库为例给出通常情况下的解决方法。在对数据库服务器的优化中本文主要提到对内存和系统I/O的优化。内存的优化主要是通过如何提高内存的共享来实现的；系统I/O的优化，则主要通过对数据库物理和逻辑结构的合理分布、数据的合理存储、以及对回滚段和重做日志调整的阐述，说明如何达到减少I/O争用的目的；应用系统的优化则主要是SQL语句的优化，本文通过说明如何执行SQL跟踪和解释计划从而获得对SQL语句实际执行效果的评价，同时还给出SQL语句优化的一些常用技巧；在讨论对操作系统和网络的优化时，本文着重介绍了如何减少其它应用所占用的资源，从而增大为数据库服务的资源，以及如何通过减少网络流量的方法来提高网络性能等方面加以阐述。
最后，本文通过对一个实例的优化，阐述了数据库应用系统性能优化的总的原则和一般方法。
The tuning for database system is vital to the normal running of the whole system, but it is a complicated work.
This paper analyzes all kinds of performance problems that may occur on Database Server, application programs, OS and network during the running of Database application system. Then put forward some usual methods to settle them based on Oracle databse. Tuning for Databse Server includes the tuning for memory and I/O. In order to tune memory we describe how to improve the sharing for memory; And the tuning for I/O mainly includes decreasing the contention of I/O by reasonable distribution of physical and logical strcture, adjusting the storing of data, and the optimizing for rollback segment and redo log. The tuning of application programs mainly includes the optimizing of SQL, this paper describes how to excute SQL trace and explain plan to get the estimate for the effect of the excution of SQL, and then put forward some tips for tuning SQL. While discussing the tuning of OS and network, this paper emphasizes how to increase the resource serving for database by reducing the resource taking up by other applications, and how to
improve the performance of network by reducing the flow on network.
At last,this paper introduces the general rules and methods to tune Database application system by
tuning an example.
本文从数据库服务器、应用程序、操作系统及网络等几方面对数据库应用系统在运行过程中可能出现的各种性能问题作出了分析，同时以Oracle数据库为例给出通常情况下的解决方法。在对数据库服务器的优化中本文主要提到对内存和系统I/O的优化。内存的优化主要是通过如何提高内存的共享来实现的；系统I/O的优化，则主要通过对数据库物理和逻辑结构的合理分布、数据的合理存储、以及对回滚段和重做日志调整的阐述，说明如何达到减少I/O争用的目的；应用系统的优化则主要是SQL语句的优化，本文通过说明如何执行SQL跟踪和解释计划从而获得对SQL语句实际执行效果的评价，同时还给出SQL语句优化的一些常用技巧；在讨论对操作系统和网络的优化时，本文着重介绍了如何减少其它应用所占用的资源，从而增大为数据库服务的资源，以及如何通过减少网络流量的方法来提高网络性能等方面加以阐述。
最后，本文通过对一个实例的优化，阐述了数据库应用系统性能优化的总的原则和一般方法。
The tuning for database system is vital to the normal running of the whole system, but it is a complicated work.
This paper analyzes all kinds of performance problems that may occur on Database Server, application programs, OS and network during the running of Database application system. Then put forward some usual methods to settle them based on Oracle databse. Tuning for Databse Server includes the tuning for memory and I/O. In order to tune memory we describe how to improve the sharing for memory; And the tuning for I/O mainly includes decreasing the contention of I/O by reasonable distribution of physical and logical strcture, adjusting the storing of data, and the optimizing for rollback segment and redo log. The tuning of application programs mainly includes the optimizing of SQL, this paper describes how to excute SQL trace and explain plan to get the estimate for the effect of the excution of SQL, and then put forward some tips for tuning SQL. While discussing the tuning of OS and network, this paper emphasizes how to increase the resource serving for database by reducing the resource taking up by other applications, and how to
improve the performance of network by reducing the flow on network.
At last,this paper introduces the general rules and methods to tune Database application system by
tuning an example.
引文
1．Michael J.Corny等著，刘晓霞等译，Oracle 8优化技术，机械工业出版社，1998
2．Eyal Aronoff等著，李逸波等译，Oracle 8性能优化和管理手册，机械工业出版社，2000
3．Kevin Loney等著，李纪松等译，Oracle 8i数据库管理员手册，机械工业出版社，2000
4．William G.Page.Jr等著，王磊等译，Oracle 8/8i开发使用手册，机械工业出版社，2000
5．Andy Tremayne著，刘谦等译，Oracle Applications性能调整手册，机械工业出版社，2001
6．Sumit Sarin著，钟鸣等译，Oracle数据库管理员技术指南，机械工业出版社，2001
7．王能斌编著，数据库系统原理，电子工业出版社，2000年
8. Oracle 8i Designing and Tuning for Performance Release 2 (8.1.6)
9. www.oracle.com
10. D.J. DeWitt, R. Katz, F. Olken, L. Shapior, M. Stonebraker, and D. Wood, "Implementation techniques for main memory database systems," Proc. 1984 SIGMOD Conf., pp. 1-8, Boston, June 1984
11. GM. Sacco, "Fragmentation: A techniques for efficient query processing," ACM Trans.Database Systems, vol. 11, no. 2, pp. 113-133, 1986
12. M. Kitsuregawa, H. Tanaka, and T. Moto-oka, "Application of hash to database machine and its architecture," New Generation Computing, vol. 1, no. 1, pp. 66-74, 1983
13. C. Turbyfill, "Comparative benchmark of relational database systems," PhD thesis, Cornell Univ., Sept. 1987
14. nCUBE 2 Supercomputers: Technical Overview. Beaverton, Ore.: nCUBE, 1990.
15. Guy Harrison, "A Guide to Oracle Performance Tuning", www. oraview, com
16. Donald Bufieson, Oracle tuning: Start by looking at the big picture, www. dba-oracle.com,2001
17. Donald Burleson, "Oracle tuning: Examining the Oracle instance", www. dba-oracle.com,2001
18. Stephen Rea, "Oracle Database Tuning", www. uaex.edu
19．郭卫华等，已有ORACLE系统的性能调整优化，计算机工程与应用，2001-4
20．陆晨音，Oracle8i数据库系统的优化与调整，计算机工程，2001-6
2．Eyal Aronoff等著，李逸波等译，Oracle 8性能优化和管理手册，机械工业出版社，2000
3．Kevin Loney等著，李纪松等译，Oracle 8i数据库管理员手册，机械工业出版社，2000
4．William G.Page.Jr等著，王磊等译，Oracle 8/8i开发使用手册，机械工业出版社，2000
5．Andy Tremayne著，刘谦等译，Oracle Applications性能调整手册，机械工业出版社，2001
6．Sumit Sarin著，钟鸣等译，Oracle数据库管理员技术指南，机械工业出版社，2001
7．王能斌编著，数据库系统原理，电子工业出版社，2000年
8. Oracle 8i Designing and Tuning for Performance Release 2 (8.1.6)
9. www.oracle.com
10. D.J. DeWitt, R. Katz, F. Olken, L. Shapior, M. Stonebraker, and D. Wood, "Implementation techniques for main memory database systems," Proc. 1984 SIGMOD Conf., pp. 1-8, Boston, June 1984
11. GM. Sacco, "Fragmentation: A techniques for efficient query processing," ACM Trans.Database Systems, vol. 11, no. 2, pp. 113-133, 1986
12. M. Kitsuregawa, H. Tanaka, and T. Moto-oka, "Application of hash to database machine and its architecture," New Generation Computing, vol. 1, no. 1, pp. 66-74, 1983
13. C. Turbyfill, "Comparative benchmark of relational database systems," PhD thesis, Cornell Univ., Sept. 1987
14. nCUBE 2 Supercomputers: Technical Overview. Beaverton, Ore.: nCUBE, 1990.
15. Guy Harrison, "A Guide to Oracle Performance Tuning", www. oraview, com
16. Donald Bufieson, Oracle tuning: Start by looking at the big picture, www. dba-oracle.com,2001
17. Donald Burleson, "Oracle tuning: Examining the Oracle instance", www. dba-oracle.com,2001
18. Stephen Rea, "Oracle Database Tuning", www. uaex.edu
19．郭卫华等，已有ORACLE系统的性能调整优化，计算机工程与应用，2001-4
20．陆晨音，Oracle8i数据库系统的优化与调整，计算机工程，2001-6