1 Introduction
With the arrival of the information age, people began to use database technology to conduct scientific and efficient management of large and complex information. Various applications in the database are emerging, with Visual FoxPro, Power Builder, etc., but Oracle is the best in this field is also the most popular large database application software. This article has been studied in how to optimize databases and their applications in ORACAL.
2. Optimize the database
2.1 Optimizing Settings Control File Number, Location and Its Backup Suggestions Control_File Initialization Parameters Specifying multiple files should be greater than 2, and the control file is mirrored to different locations, and these control files are backed up to a secure disk.
2.2 Optimize design tablespace, size and location of data files
2.2.1 Design Table Space
Try to disperse the read and write operation of the disk at the same time. If you update the data in a table, the database will simultaneously read the data in the table and the index information on the table, if the data information and index information of the table Both are placed in the same data file, the speed of the database will slow down. It is best to put data information and index information in two data files of different disks, at which time the database is dispersed on two disks, the speed will be significantly improved. Therefore, when designing the tablespace and data files of the database, first create two tablespaces (stored a table space for data table spaces for user data, and indexing table index). In addition, according to the size of the data amount of the system and the nature of the data in the system, it is considered to create several data tablespaces or add several data files to the data tablespace.
2.2.2 Design data file size
Put the record size in the same table space, at this time, the storage parameter setting of a table space can ensure that the records in the table are placed in a range, avoiding a record cross-range storage, which can clear the performance of the database. .
2.2.3 Designing the location of the data file
In order to avoid the I / O operational conflict of the disk, the data file should be created in different locations.
2.3 Optimized Design Reuters Log Files
2.3.1 Repeat the size of the log file
Since the database is cycled with the reputation log file, and when the LGWR process is switched in two log files, a detection point will automatically generate, so the size of the reunition log file will directly affect the frequency of detection points. Due to the database detection point, the speed of the database will be affected, so the frequency of detection points is large, or the detection point exactly in the peak period of the database processing data will greatly affect the performance of the database. Therefore, the size of the replay log file should consider the frequency of detection points and the detection point should avoid the peak period of the database processing data.
2.3.2 Renat of Reversing the number of log file groups
In the ArchiveLog mode, the number of reprintable log file groups is appropriately increased, and the frequency of the data stock log file can be reduced.
2.3.3 Arunning Archive of the Log File
The archive of the repel the log file should be set on the disk read and write faster physical devices. This reduces the archive time of the log file.
2.4 Optimization Design of Returning Segments
2.4.1 Design of the number of rollback segments
To consider the number of concurrent transactions that need to be supported by the database, because each returning segment has the maximum number of transactions that can support, if the number of rolls is not enough, some new transactions are forced to wait and will affect the performance of the database.
2.4.2 Design of Returning Segment Size
It is necessary to consider the number of rollback items that can accommodate the possible largest transactions of the database.
2.5 Optimize the memory configuration used by the database
To adjust the size balance of the database's cache, shared pool, repeat the log buffer, and process global zones.
3. Optimize applications
3.1 Take a unified SQL program
Because Oracle is in executing SQL text, first compare the SQL text to be executed with the previously executed SQL text saved in the public zone, the SQL text will skip the resolution processing of the SQL text to be executed. This speeds up the execution speed of SQL text by reducing the number of parses.
3.2 Select the appropriate SQL code
When the database operation is performed, the same result can be implemented in many ways. We should choose a suitable SQL code to make it easier to clear and run faster.
3.3 Clear column name
The column name obtained using SELECT must be explicitly specified, and the records should be made as little. Use sort, because Oracle puts the value of the necessary columns to the WORK field, so the number of columns will save I / O return.
3.4 Note the order in combination index
The effect of indexing is different due to the order of the column. For example: (AGE, Address) combined index.
WHERE AGE = 28 and address = "bei jing" & combined index Valid WHERE AGE = 28 & combined index Valid WHERE Address = "bei jing" & junction index invalid
3.5 Note where WHERE
3.5.1 Index Columns Do not use functions
Since the index column uses a function, the index will be invalid, so the program is used to use the index as possible with the index. Such as:
WHERE SUBSTR (Telno, 2, 2) = "88" changed to WHERE Telno Like "88%" better.
3.5.2 Index Columns Don't use NOT
Since the index column is not equal, the index will become invalid, and finally the speed is slow.
3.5.3 Do not use NULL
If NULL is used, the index will be invalid, become a full table search, affecting the processing speed.
3.5.4 Do not calculate the index column
If the index column is calculated, the index will be invalid, and then the speed is slower.
4 Conclusion
The optimization method proposed in this article can enhance the role of Oracle database performance and improve the efficiency of Oracle applications. This is only the experience summary of individuals in actual work, hoping to have a certain help for the majority of peers.
