Oracle Database Performance Optimization Technology

Absrtact: Oracle database is one of the most widely used large-scale databases, and its optimization is directly related to the operation efficiency of the system. Based on the basic principle of database performance optimization, this paper expounds how to avoid competition and how to optimize data access in the database design stage, how to adjust the memory and I/O from the operating system and the database instance level in the database runtime to achieve the optimization of database performance.

Keywords: Oracle database; performance optimization; memory; I/O

1. Introduction

With the continuous development of network applications and E-commerce, the number of visits to each site is increasing, the size of the database has been expanding, the performance of the database system has become more and more prominent, so how to tune the database is essential: how to use limited computer 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 the world's largest database manufacturer, Oracle database at home and abroad to obtain a lot of successful applications, according to statistics, the global 93% of the listing. COM companies, 65 "Fortune Global 100" Enterprises invariably use Oracle database to carry out e-commerce. Many enterprises, government units and E-commerce websites in China have also adopted Oracle as the database server. Oracle database server is a highly optimized software product, and regular adjustments can optimize the performance of the application system and prevent system bottlenecks.

The basic principle of database performance optimization is to get the data you need with as little disk access as possible. In order to evaluate the performance of database, it is necessary to compare the evaluation indexes before and after the database adjustment: The tradeoff between response time and throughput, the availability of database, the hit rate of database and the efficiency of memory usage, so as to measure the effect of adjusting measures and the direction of guiding adjustment.

Performance tuning of an Oracle database should be done in a certain order, as the system's adjustments in the previous steps can avoid some unnecessary adjustments or costly adjustments. In general, you can start with two phases:

1, design phase: the logical structure and physical structure of the optimization design, so that in order to meet the demand conditions, the system performance to achieve the best, the system cost to achieve the smallest;

2, the database operation stage: takes the operating system level, the database level some optimization measures to make the system performance best;

2. In the system design development phase to adjust the database

In order to make full use of Oracle database functional characteristics, in the design of information systems, database designers need to be based on the business situation (such as access or number of clients) and existing resource conditions (such as the configuration of the database server) to consider the structure of the system and the logical structure of the database design:

2.1 Adjust the application structure design.

That is, the application is a traditional C/s two-tier architecture, or b/w/d three-tier architecture. Different application architectures require different database resources.

2.2 Proper use of partitioning, indexing, and archiving functions.

If the amount of data in a business grows very fast, consider whether the database table hosting the business uses the partitioning capabilities of the Oracle database, and whether the database tables that are accessed frequently need to be indexed For frequently accessed data that is not changed after the business process is completed, the data can be accessed as little as possible in the application system by putting in a historical file.

2.3 Properly write SQL statements that access data.

A good SQL statement can be reused by the database to reduce the analysis time, and proper use of the index can greatly reduce the access data block and thus reduce the response time. The execution of the application ultimately boils down to the execution of SQL statements in the database, so the efficiency of the execution of the SQL statement 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.

2.4 Adjust hard disk I/O

This step was completed prior to the development of the information system. 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. The following guidelines should also be followed in cases where the disk is more affluent:

Separate the table from the index;

Create user table space, separate disk from system table space.

Specify a different table space when creating tables and indexes;

Create a table space dedicated to the rollback segment to prevent space competition from affecting the completion of the transaction;

Create temporary table spaces for sorting operations, as much as possible to prevent database fragmentation from being present in multiple tablespace spaces.