The Oracle tutorial being looked at is Oracle database and application Optimization Developer Network Oracle.
Introduction: Fine Place to start, skillfully place diligently. The difference between Master and rookie is: Master know everything, rookie know some. Computer tips to collect the most novel tricks, so that you easily set foot on the Master road.
Absrtact: In this paper, Oracle database and Oracle Application optimization, a comprehensive analysis and research, and put forward some of their own suggestions.
Tags: ORACLE, optimization, database, SQL
1. Introduction
With the advent of the information age, people began to use the database technology widely to carry on the scientific and efficient management to the massive and complex information. Various applications in the database field have sprung up, with visual Foxpro,power Builder and so on, but Oracle is one of the best and most popular large database applications in the field. In this paper, we study how to optimize the database and its application program in Oracal.
2. Optimizing the Database
2.1 Optimization Settings control the number of files, location and backup
It is recommended that Control_file initialization parameters specify that multiple files should be greater than 2 and that the control files be mirrored to different locations and that the control files be backed up to a secure disk.
2.2 Optimize the design of the table space, data file size and location
2.2.1 Design Table Space
Try to spread the disk's read and write operations at the same time. If the data in one table is updated, the database will read both the data in the table and the index information on the table, and the database will slow down if both the data and index information of the table are placed in the same data file. It is best to put the data information and index information in the two data files of different disks, when the reading and writing of the database to the disk will be dispersed on two disks, the speed will be significantly improved. Therefore, when you design the table space and data files for your database, you first create two tablespaces of table and table indexes, which hold the data table space of the user's data and the index table space that holds the table index. In addition, according to the size of the system data and the nature of the data in the system, we consider creating several data table spaces or adding several data files to the data table space.
Size of the 2.2.2 design data file
Put the record size of the table in the same table space, when a table space storage parameter settings, you can ensure that the records in the table are placed in a range, to avoid a record across the range of storage, you can obviously database performance.
2.2.3 the location of the design data file
To avoid conflicts with disk I/O operations, you should create a data file in a different location.
[NextPage]
2.3 Optimization Design Replay log files
2.3.1 Replay the size of the log file
Since databases are recycled using replay log files, and when the LGWR process switches in two log files, a checkpoint is automatically generated, so the size of the replay log file directly affects the frequency at which the detection point appears. And because in the database checkpoint, to the user, the speed of the database will be affected, so the frequency of detection points, or detection points just appear in the database processing data peak, will greatly affect the performance of the database. Therefore, the replay log file size design, should consider the frequency of detection points and detection points should avoid the peak of database processing data.
2.3.2 Replay number of log file groups
In Archivelog mode, it is possible to reduce the frequency of database archive log files by appropriately increasing the number of replay log file groups.
2.3.3 Replay archive of log files
The archive of the replay log file should be set on the physical device where the disk reads and writes faster. This reduces the archive time for log files.
Optimization design of 2.4 Rolling back section
The design of the number of 2.4.1 rollback segment
To consider the number of concurrent transactions that the database needs to support, because each rollback segment has the maximum number of transactions it can support, and if the number of rollback segments is not large enough, some new transactions are forced to wait, affecting the performance of the database.
The design of the 2.4.2 rollback segment size
Consider the number of rollback entries that the rollback segment can hold for the maximum transaction possible for the database.
2.5 Optimizing the memory configuration used by the database
To adjust the cache size of the database, shared pools, replay log buffers, and the process global area balance issues.
3. Optimizing Applications
3.1 Adopting a unified SQL program
Because Oracle is executing SQL, the current SQL text to be executed is compared to the previously executed SQL text saved in the public area, and the SQL text skips the parsing of the currently executing SQL, which speeds up the execution of the SQL text by reducing the number of resolutions.
3.2 Select the appropriate SQL code
The same results can be implemented in a number of ways while database operations are in progress. We should choose the right SQL code to make it easier to understand and run faster.
3.3 Clear Column Name
The column names obtained by using select must be explicitly specified, and the extracted records should be made as small as possible. In situations like sort, because Oracle places the necessary column values in the work realm, reducing the number of columns also saves the I/O number.
3.4 Note the order in which the index is combined
Indexes have different effects because of the order of the columns. For example: (age,address) The associative index of the order.
WHERE age=28 and address= ' BEI JING ' & Associative index valid
WHERE age=28 & Associative Index valid
WHERE address= ' BEI JING ' & junction full index is invalid
3.5 Note the use of where
3.5.1 Indexed columns do not use functions
The index becomes invalid as soon as the function is used in the indexed column, so change the program to take advantage of the index, when indexed. Such as:
where SUBSTR (telno,2,2) = ' 88 ' is changed to where Telno like ' 88% ' is better.
3.5.2 Indexed columns do not use not
Because the index column is not equal, the index becomes invalid and eventually slows down.
3.5.3 do not use null
If NULL is used, the index is invalidated, becomes a full table retrieval, and affects the processing speed.
3.5.4 do not calculate on an indexed column
If you evaluate an indexed column, the index is invalidated and the speed slows down.
4. Concluding remarks
The optimization method presented in this paper can improve the performance of Oracle database and improve the efficiency of Oracle application execution. This is only a personal experience in the actual work of summary, hoping to be able to play a certain role in the vast number of peers.
Previous page
