Oracle Database and Application Optimizer developer network Oracle
- 2020-06-03 08:39:23
- OfStack
Introduction: Work hard and carefully. The difference between a master and a rookie is that a master knows everything and a rookie knows something. Computer small skill collects the most novel move clever move, let you set foot on the road of ace easily.
Abstract: In this paper, ORACLE database and ORACLE application optimization, a comprehensive analysis and research, and put forward their own 1 Suggestions.
Key words :ORACLE, optimization, database, SQL
1. The introduction
With the advent of the information age, people begin to widely use the database technology for scientific and efficient management of a large number of complex information. There are a large number of applications in the database field, including Visual Foxpro, Power Builder and so on, but ORACLE is the best and most popular large database application in this field. This paper studies how to optimize the database and its applications in ORACAL.
2. Optimize database
2.1 Optimize the number, location and backup of control files
It is recommended that the CONTROL_FILE initialization parameter specify that multiple files should be greater than 2 and that the control files be mirrored to different locations and backed up to a secure disk.
2.2 Optimize the design of table space, size and location of data files
2.2.1 Design table Spaces
Try to spread out the read and write operation on the disk at the same time. For example, when updating the data in a table, the database will read the data in the table and the index information on the table at the same time. If the data information and index information of the table are put in the same data file, the speed of the database will be slow. It is better to put the data information and index information in two data files on different disks, at this point the database reads and writes to the disk will be spread across the two disks, the speed will be significantly improved. So in the design of the database table space and data file, first to the table and table index to create two table space (data table space for user data and index table space for table index). In addition, according to the size of the data volume of the system and the nature of the data in the system, it is also considered to create a few data table Spaces or to add a few data files to the data table space.
2.2.2 Size of design data file
The table with the same record size is placed in the same table space. At this time, the storage parameter setting of the table space can ensure that the records in the table are placed in a range, avoiding the storage of one record across a range, which can clearly show the performance of the database.
2.2.3 Location of design data file
To avoid I/O operation conflicts on disks, data files should be created in different locations.
[NextPage]
2.3 Optimize the design to replay the log file
2.3.1 Replay the log file size
Because the database USES the replay log files in a loop and the LGWR process automatically produces a checkpoint when switching between two log files, the size of the replay log file directly affects the frequency of the checkpoint. As the speed of the database will be affected for users when detecting points in the database, the occurrence frequency of detecting points is large, or detecting points just appear at the peak of database processing data, which will greatly affect the performance of the database. Therefore, replaying the log file size design should take into account the frequency of checkpoint occurrence and the fact that checkpoint should avoid the peak of data processing in the database.
2.3.2 Number of reenactment log file groups
In ARCHIVELOG mode, increasing the number of replay log file groups can reduce the frequency of database archive log files.
2.3.3 Archive of replay log files
You should archive the replay log file on a physical device that reads and writes faster on disk. This reduces the archiving time of the log files.
2.4 Optimal design of rollback segment
2.4.1 Design of the number of rollback segments
Consider the number of concurrent transactions the database needs to support, because each rollback segment has the maximum number of transactions it can support, and if the rollback segment is not large enough, some new transactions are forced to wait, affecting database performance.
2.4.2 Design of rollback segment size
Consider the number of rollback items that this rollback segment can hold the largest possible transaction for the database.
2.5 Optimize the memory configuration used by database
To adjust the cache buffer for the database, share pools, replay log buffers, and process global size balancing issues.
3. Optimize your application
3.1 Adopt SQL program of Unified 1
Because when ORACLE executes an SQL document, it first compares the currently executed SQL document with the previously executed SQL document saved in the common area, the same SQL document skips the parsing of the currently executed SQL document, thus speeding up the execution of SQL document by reducing the number of parses.
3.2 Select the appropriate SQL code
When performing database operations, the same 1 result can be achieved in many ways. We should pick the appropriate SQL code to make it simpler and faster.
3.3 Clear column names
Column names 1 obtained with SELECT should be clearly specified and should be drawn with as few records as possible. When using SORT, etc., ORACLE places the necessary column values in the WORK field, so reducing the number of columns saves the number of I/O cycles.
3.4 Note the order of the combined indexes
Indexes work differently because of the order of the columns. For example :(AGE, ADDRESS) sequential combined index.
WHERE AGE=28 AND ADDRESS='BEI JING' & Combined index valid
WHERE AGE=28 & Combined index valid
WHERE ADDRESS='BEI JING' & The total index is invalid
3.5 Note the use of WHERE
3.5.1 Do not use functions for indexed columns
Since the index column 1 becomes invalid once a function is used, change the program to use the index whenever possible. Such as:
WHERE SUBSTR(TELNO, 2,2)='88' is better as WHERE TELNO LIKE '88%'.
3.5.2 Do not use NOT for index columns
Since index column 1 is used differently, the index becomes invalid, resulting in slower speed.
3.5.3 Avoid NULL
If NULL is used, the index becomes invalid and becomes full table retrieval, affecting processing speed.
3.5.4 Do not calculate index columns
If an index column is evaluated, the index becomes invalid and then slows down.
4. The conclusion
The optimization method proposed in this paper can improve the performance of ORACLE database and the execution efficiency of ORACLE application. This is only a summary of personal experience in the actual work, I hope to be able to play a definite help to the majority of peers.
On 1 page