---- With the continuous development of network applications and e-commerce, the number of visitors at all sites is getting bigger and bigger, how to make limited computer system resources for more users? How to ensure the user's response speed and quality of service? These issues belong to the category of server performance optimization. As a successful database manufacturer, how is the performance optimization of ORACLE Database? Optimization Strategy - To ensure that the Oracle Database is running in the best performance, you should consider the database optimization policy. Optimization strategies generally include several aspects of server operating system parameter adjustment, database parameter adjustment, network performance adjustment, application SQL statement analysis, and design, where application analysis and design is done before the information system development. ---- Analysis Evaluation Oracle Database Properties There are mainly database throughput, database user response time, two indicators. Database user response time can be divided into two system service time and user waiting time, namely: ---- Database User Response Time = System Service Time User Waiting Time ---- So satisfactory user response time has two A way: First, reduce system service time, improve the throughput of the database; the second is to reduce user waiting time, which reduces the conflict rate of users to access the same database resource. ---- Database performance optimization includes the following parts: ---- 1. Adjusting the design of the data structure This section is completed before the development information system, the programmer needs to consider whether the partition function of the Oracle database is used, for the regularly accessed database Whether the table needs to establish an index. ---- 2. Adjusting the application structure design is also done before the development of the information system. Programmers need to consider what kind of architecture used in this step is the use of traditional Client / Server two-layer architecture or use Browser / Web / Database's three-layer architecture. Different application architecture requirements have different database resources. ---- 3. Adjusting the execution of the database SQL statement application will eventually be attributed to the SQL statement in the database execution, so the execution efficiency of the SQL statement finally determines the performance of the Oracle database. Oracle recommends adjusting Optimizing SQL statements using Oracle Optimizer and Row-Level Manager. ---- 4. Adjusting server memory allocation memory allocation is optimized in the information system operation. The database administrator can adjust the data buffer, log buffer, and shared pool of the database system, but also adjust the size of the program global zone (PGA area). ---- 5. Adjusting the hard disk I / O step is done before the information system development. Database administrators can put data files that make up the same table space on different hard drives, and do I / O load balancing between hard drives. ---- 6. Adjusting the operating system parameters For example: Oracle database running on the UNIX operating system can adjust the size of the UNIX data buffer, and the memory size of each process can use. ---- In fact, the above database optimization measures are interrelated. The performance of the Oracle database performance is basically the user's response time, and the user needs to wait for a long time. The reason why performance deterioration is a variety of, sometimes multiple factors create a result of deterioration in performance, which requires database administrators to have a relatively comprehensive computer knowledge, and can sense the main cause of database performance. . In addition, good database management tools are also important to optimize database performance.
Performance Optimization Tools ---- Oracle Database User Optimization Tools are: ---- 1. Oracle Database Online Data Dictionary Oracle Online Data Dictionary can reflect the dynamic operation of Oracle, it is very helpful for adjusting database performance. . ---- 2. Operating system tools, for example, using the UNIX operating system VMSTAT, IOSTAT, etc. can check the usage of system-level memory and hard disk I / O, which helps administrators find out where the system bottleneck appears. ---- 3. SQL Language Tracking Tool (SQL TRACE FACLITY) ---- SQL Language Tracking Tool can record the execution of the SQL statement, the administrator can use the virtual table to adjust the instance, and use the SQL statement to track file adjustment applications performance. The SQL Language Tracking tool outputs the result into an operating system file, and the administrator can use the TKPROF tool to view these files. ---- 4. Oracle Enterprise Manager (OEM) This is a graphic user management interface that allows users to easily perform database management without having to remember the complex Oracle database management commands. ---- 5. Explain Plan - SQL Language Optimization Command Use this command to help programmers write an efficient SQL language. System Performance Assessment ---- Different types of information systems are different, and database parameters that need to be concerned are also different. Database administrators need to focus on different database parameters based on their own information system types. ---- 1. Online Transaction Information System (OLTP) This type of information system generally requires a lot of INSERT, UPDATE operations, typical systems include civil aviation ticket distribution systems, bank savings systems, etc. The OLTP system needs to ensure the concurrency, reliability, and end users of the database. The Oracle database used by such systems must mainly consider the following parameters: Is the database return segment sufficient? Do you need to establish an Oracle database index, gather, have a hash? Is the system global zone (SGA) size sufficient? Is the SQL statement efficient? ---- 2. Data Warehousing This information system is the main task of the information system is to query from the massive data of Oracle to obtain certain laws between data. The database administrator needs to focus on the following parameters for this type of Oracle database: Do you use a B * index or bitmap index? Do you use a parallel SQL query to improve query efficiency? Do you use a PL / SQL function to write a stored procedure? If necessary, you need to establish a parallel database to increase the query efficiency of the database. Adjustment of the parameters ---- 1. CPU parameter ---- CPU is an important resource for the server, the server has a good working state as at the peak of the working peak, higher than 90% of the CPU. If the free time CPU usage is over 90%, the server lacks CPU resources; if the CPU usage is still low if the peak work peak, the server CPU resource is still sufficient. ---- Use the operation command to see the use of the CPU, the server of the general UNIX operating system, you can use the SAR-U command to view the usage rate of the CPU; the server of the NT operating system, you can use the NT performance manager to view the CPU. The usage rate.
