Optimization of Oracle Database performance

With the continuous development of network applications and E-commerce, the number of visits to various sites is increasing, how to make limited computer system resources for more users. How to ensure the user's response speed and quality of service. These problems are within the scope of server performance optimization. As a more successful database vendor, how is the performance optimization of Oracle company database performed

　　 Optimization Strategy

In order to ensure the Oracle database running in the best performance state, the database optimization strategy should be considered before the information system development. The optimization strategy generally includes the server operating system parameter adjustment, the database parameter adjustment, the network performance adjustment, the application SQL statement analysis and the design and so on several aspects, in which the application analysis and the design is in the information system development

The performance of Oracle database has two indexes, database throughput and database user response time. Database user response time can be divided into system service time and user wait time two items, namely:

Database user Response time = System service time + user wait time

Therefore, there are two ways to obtain satisfactory user response time: One is to reduce the system service time, that is to improve the throughput of the database, and the other is to reduce the user's waiting time, that is, to reduce the user access to the same database resources conflict rate.

Database performance optimization includes the following sections:

1. Adjusting the design of the data structure this part is completed before the information system is developed, and programmers need to consider whether to use the partitioning capabilities of the Oracle database, whether or not to index frequently accessed database tables.

2. Adjust the application structure design This part is also done before the information system is developed. In this step, programmers need to consider what architecture the application uses, whether it uses a traditional client/server two-tier architecture, or a three-tier architecture with Browser/web/database. Database resources required by different application architectures

3. Adjusting the execution of the database SQL statement application ultimately boils down to the execution of SQL statements in the database, so the efficiency of the execution of the SQL statement ultimately determines the performance of the Oracle database. Oracle Company recommends using the Oracle Statement Optimizer (Oracle Optimizer) and the row lock manager (Row-level Manager) to adjust the optimized SQL statements.

4. Adjust server memory allocation memory allocation is optimized in the process of information system operation. Depending on the health of the database, the database administrator can not only adjust the data buffers, log buffers, and shared pools of the database system global area (SGA area), but also adjust the size of the program global Area (PGA area).

5. Adjust hard disk I/O This step is completed prior to the development of information systems. The database administrator can put the data files that make up the same table space on a different hard disk and I/O load balance between the hard disks.

6. Adjust operating system parameters such as the Oracle database running on UNIX operating systems, you can adjust the size of the UNIX data buffer, the size of the memory that each process can use, and so on.

In fact, the database optimization measures mentioned above are interrelated. Oracle database performance degradation is basically the user response time is relatively long, users need to wait for a long time. The reason for the deterioration of performance is a variety of factors, and sometimes the results of the performance degradation caused by multiple elements, this requires the database administrator has a more comprehensive computer knowledge, can be sensitive to the impact of the database performance of the main reason. In addition, good database management tools for optimizing performance optimization Tools

Database performance optimization tools commonly used in Oracle databases are:

1. Oracle Database online data dictionary Oracle's online data dictionary can reflect the dynamic performance of Oracle, which is helpful for tuning the database.

2. Operating system tools such as the Vmstat, Iostat, and so on Unix operating systems can see the use of system-level memory and hard disk I/O, which can help administrators figure out where the system bottlenecks appear.

3. SQL language Tracking tool (SQL Trace Facility)

The SQL language Tracking tool can record the execution of SQL statements, and administrators can use virtual tables to adjust instances and use SQL statements to track files to adjust application performance. The SQL language Tracking tool outputs the results into an operating system

4. Oracle Enterprise Manager (OEM) This is a graphical user management interface that users can use to facilitate database management without having to remember commands for complex Oracle database management.

5. Explain plan?? SQL language tuning commands use this command to help programmers write efficient

　　System Performance Evaluation

The types of information systems are different and the database parameters to be concerned are different. Database administrators need to base their own

1. Online transaction processing Information System (OLTP) This type of information system generally requires a large number of inserts, update operations, the typical system includes the air ticket sale system, Bank savings system. OLTP systems need to ensure the concurrency, reliability, and end-user speed of the database, and the Oracle databases used by such systems should primarily consider the following parameters:

Whether the database rollback segment is sufficient.

Whether you need to establish an Oracle database index, aggregation, hash.

The system global Area (SGA) size is sufficient.

Whether the SQL statement is efficient.

2. Data warehousing System (warehousing) The main task of this information system is to query from Oracle's massive data to get some regularity between data. Database administrators need to have this type of Oracle data

Whether to use b* index or bitmap index.

Whether to use parallel SQL queries to improve query efficiency.

Whether to write stored procedures using the Pl/sql function.

If necessary, a parallel database needs to be established to improve the query efficiency of the database.

Adjustment of Parameters

1. CPU Parameters

The CPU is an important resource of the server, and the server's good working state is that the CPU usage rate is higher than 90% during the peak working time. If the idle time CPU usage is above 90%, the server lacks CPU resources; If the CPU usage is still low at peak hours, the server CPU resources are still quite sufficient.

Use the action command to see the CPU usage, the general UNIX operating system server, you can use the Sar-u command to view the CPU usage; NT operating system servers that can use NT's performance manager to view the CPU's

The database administrator can learn about the CPU time used by the Oracle database by looking at the CPU used by-session statistics in the V$sysstat data dictionary; View OS User Level CPU times Statistics for the operating system user state of the CPU time; View the OS system call CPU time statistics the CPU time in OS state is known, and the total CPU times of the operating system are the sum of the user state and System state time. If the Oracle database uses more than 90% of the total CPU time of the operating system, the server CPU is basically used by the Oracle database, which is reasonable, and conversely, the server CPU is too much occupied by other programs, the Oracle database cannot

2. Memory parameters

The adjustment of memory parameter mainly refers to the system global Area (SGA) Adjustment of Oracle database. The SGA is composed mainly of 3 parts: Shared pool, data buffer, log buffer.

A shared pool consists of two parts: a shared SQL area and a data dictionary buffer. The shared SQL area is a user SQL command

　　Concluding remarks

The performance tuning of Oracle database is a systematic project, which involves many aspects. The database administrator needs to use the rules described above to carefully analyze the various problems Oracle is running, to ensure the efficiency of Oracle database operation. Also need to point out that the statement given above is only to measure the Oracle running process at a point in time, the database administrator can not only according to a point of the situation of the database performance of the good or bad, only a few more time to run the database to make a comprehensive assessment of the state of operation.

As a single point of time monitoring is very troublesome, and for multiple time point monitoring is a cumbersome work, for this reason, the author developed the Oracle database performance monitoring software Oratune. This software not only can read all kinds of parameters from the database periodically and automatically calculate the various proportions, but also automatically according to these proportions to recommend the database administrator to modify

Oratune has been applied in Tsinghua University, North China Power Group and other units, and has played a good role in optimizing the performance of Oracle database.