Optimize Oracle database performance

With the continuous development of network applications and e-commerce, the access traffic of various sites is growing. How can we make limited computer system resources serve more users? How can we ensure user response speed and service quality? These problems all fall into the scope of server performance optimization. As a successful database vendor, How Does Oracle optimize its database performance?

　　 Optimization Strategy

To ensure that the Oracle database runs in the optimal performance state, the database optimization strategy should be considered before the information system development. The optimization policies generally include Server OS parameter adjustment, database parameter adjustment, network performance adjustment, and application SQL statement analysis and design, the Analysis and Design of applications are based on the development of information systems.

Analysis and Evaluation of Oracle database performance mainly includes two indicators: Database throughput and database user response time. The database user response time can be divided into two types: system service time and user wait time:

Database User response time = system service time + User wait time

Therefore, there are two ways to get a satisfactory response time: one is to reduce the system service time, that is, to increase the database throughput; the other is to reduce the user wait time, this reduces the conflict rate of user access to the same database resource.

Database performance optimization includes the following parts:

1. The design of adjusting the data structure is completed before the information system is developed. The programmer needs to consider whether to use the Partition Function of the Oracle database and whether to create indexes for frequently accessed database tables.

2. Adjusting the application structure design is completed before the information system is developed. In this step, programmers need to consider what kind of architecture the application uses, whether to use the traditional Client/Server two-tier architecture or Browser/Web/database three-tier architecture. Database resources required by different application Architectures

3. Adjusting the execution of Database SQL statement applications will eventually be attributed to the execution of SQL statements in the database. Therefore, the execution efficiency of SQL statements ultimately determines the performance of the Oracle database. Oracle recommends using Oracle optimizer and row-level manager to adjust and optimize SQL statements.

4. Adjusting the memory allocation of the server is optimized during the information system operation. The database administrator can adjust the size of the data buffer, log buffer, and shared pool in the global zone (SGA) of the database system based on the database running status, you can also adjust the size of the global area (PGA) of the program.

5. The hard disk I/O adjustment step is completed before the information system development. The database administrator can store data files in the same tablespace on different hard disks to achieve I/O load balancing between hard disks.

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

In fact, the above database optimization measures are interrelated. The deterioration of Oracle database performance is basically because the user response time is relatively long and the user needs to wait for a long time. However, the causes of performance deterioration are diverse. Sometimes multiple factors contribute to performance deterioration. This requires database administrators to have comprehensive computer knowledge, the primary cause of the impact on database performance is sensitive.

  Performance optimization tools

Common Oracle database performance optimization tools include:

1. Oracle Database Online Data Dictionary Oracle online data dictionary can reflect the dynamic operation of Oracle, which is helpful for adjusting database performance.

2. operating system tools such as vmstat and iostat of UNIX operating systems can be used to view the usage of system-level memory and hard disk I/O, these tools help administrators figure out where the system bottleneck is.

3. SQL trace facility)

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

4. Oracle Enterprise Manager (OEM) is a graphical user management interface, which allows users to conveniently manage databases without having to remember complicated commands for Oracle database management.

5. Explain plan ?? The SQL language optimization command can help programmers write efficient

　　System Performance Evaluation

Different types of information systems require different database parameters. The database administrator needs

1. Online Transaction Processing Information System (OLTP) this type of information system generally requires a large number of insert and update operations. Typical systems include the air ticket sales system of civil aviation and the bank savings system. The OLTP system must ensure database concurrency, reliability, and end user speed. The Oracle databases used by such systems must mainly consider the following parameters:

Is the database rollback segment sufficient?

Do you need to create an Oracle database index, clustering, or hash?

Is the size of the system global partition (SGA) sufficient?

Is SQL statements efficient?

2. Data Warehousing is the main task of a Data Warehousing Information System, which is to query massive data in Oracle to obtain certain rules between data. The database administrator needs

Is B * used *? Index or bitmap index?

Is parallel SQL queries used to improve query efficiency?

Are stored procedures written using PL/SQL functions?

If necessary, you need to establish a parallel database to improve the query efficiency of the database.

Parameter Adjustment

1. CPU Parameters

CPU is an important resource of the server. The server is in good working status and the CPU usage exceeds 90% during peak hours. If the CPU usage exceeds 90% during idle time, it indicates that the server lacks CPU resources. If the CPU usage is still low during peak hours, it indicates that the server has sufficient CPU resources.

You can run the command to view CPU usage. Generally, for UNIX operating system servers, you can run the SAR-u command to view CPU usage. for NT operating system servers, you can use the Performance Manager of NT to view CPU usage

The database administrator can view the CPU usage time of the Oracle database by checking the "CPU used by this session" statistical item in the V $ sysstat data dictionary; view the "OS user level CPU time" statistical item to learn the CPU time in the operating system user State; view the "OS system call CPU time" statistical item to know the CPU time in the operating system state, the total CPU time of the operating system is the sum of the user status and system status time. If the CPU time used by the Oracle database accounts for more than 90% of the total CPU time of the operating system, it indicates that the server CPU is basically used by the Oracle database, which is reasonable. Otherwise, it indicates that the server CPU is too occupied by other programs, and the Oracle database cannot

2. Memory Parameters

The adjustment of memory parameters mainly refers to the adjustment of the system global zone (SGA) of the Oracle database. SGA consists of three parts: the Shared Pool, data buffer, and log buffer.

The Shared Pool consists of two parts: the shared SQL zone and the data dictionary buffer zone. The shared SQL zone stores user SQL commands.

　　Conclusion

The performance optimization and adjustment of Oracle databases is a system project that involves many aspects. The database administrator needs to make comprehensive use of the rules described above to carefully analyze various problems arising during Oracle operation, so as to ensure the high efficiency of Oracle database operation. It should also be pointed out that the preceding statement only measures a certain time point in the Oracle running process. The database administrator cannot determine the database running performance simply based on a specific point, you can make a comprehensive evaluation of the database running status only when the database is running more time points.

The monitoring of a single time point is very troublesome, and it is even more cumbersome to monitor multiple time points. Therefore, the author developed the Oracle database performance monitoring software oratune. This software not only regularly reads various parameters from the database and automatically calculates various proportions, but also automatically recommends that the database administrator modify the proportions.

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