The ORACLE tutorial is: Oracle Database and application optimization Developer Network Oracle.
Introduction: work in detail. The difference between a master and a cainiao is that the master knows everything and the cainiao knows something. Computer tips collect the latest tricks and tricks, so that you can easily embark on the road to master.
Abstract: This article comprehensively analyzes and studies the optimization of ORACLE databases and ORACLE applications, and puts forward some suggestions.
Key words: ORACLE, optimization, database, SQL
1. Introduction
With the arrival of the information age, people began to use database technology extensively to scientifically and efficiently manage a large amount of complicated information. A large number of applications have emerged in the database field, including Visual Foxpro and Power Builder. However, ORACLE is the most outstanding and popular large-scale database application software in this field. This article studies how to optimize the database and its Applications in marshal.
2. Optimize the database
2.1 optimize the number, location, and backup of Control Files
We recommend that you specify more than 2 files in the CONTROL_FILE initialization parameter, mirror the control files to different locations, and back up these control files to a secure disk.
2.2 optimize the design of tablespaces and the size and location of data files
2.2.1 design tablespaces
Try to distribute disk read/write operations at the same time. For example, when updating data in a table, the database will read the data in the table and the index information of the table at the same time, if both the table data information and index information are stored in the same data file, the database will be slower. It is best to place the data information and index information in two data files on different disks. In this case, the database's read and write operations on the disk will be dispersed on two disks, and the speed will be significantly improved. Therefore, when designing the tablespace and data files of a database, create two tablespaces for the table and the table index respectively (the table space for storing user data and the index tablespace for storing table indexes ). In addition, based on the data size of the system and the nature of the data in the system, consider creating several data table spaces or adding several data files to the data table space.
2.2.2 design data file size
When a table with the same record size is placed in the same tablespace, the storage parameter settings of a tablespace can ensure that all records in the table are placed in the same range, avoiding the possibility of storing a record across a range, the database performance can be obvious.
2.2.3 Design the data file location
To avoid disk I/O operation conflicts, data files should be created in different locations.
[NextPage]
2.3 optimize the design and repeat log files
2.3.1 size of repeat log files
Because the database uses them cyclically when replaying log files, and when the LGWR process switches between two log files, a detection point is automatically generated, therefore, the size of the repeat log file will directly affect the occurrence frequency of the detection site. However, when detecting database points, the speed of the database may be affected. Therefore, the detection points may frequently appear, or the detection points may appear at the peak of data processing in the database, this will greatly affect the performance of the database. Therefore, the design of the size of repeat log files should consider the frequency of Detection Points and the peak period of data processing in the database.
2.3.2 number of repeat Log File groups
In ARCHIVELOG mode, you can increase the number of repeat Log File groups to reduce the frequency of archiving database log files.
2.3.3 repeat log file archiving
The archiving of repeat log files should be set on physical devices with faster disk read/write speed. This reduces the archiving time of log files.
2.4 Optimization Design of rollback segments
2.4.1 design of the number of rollback segments
Consider the number of concurrent transactions that the database needs to support, because each rollback segment has the maximum number of transactions that it can support. If the number of rollback segments is insufficient, some new transactions are forced to wait, which will affect the database performance.
2.4.2 Design of rollback segment size
Consider the maximum number of rollback items that the rollback segment can accommodate in the database.
2.5 optimize the memory configuration used by the database
You need to adjust the high-speed buffer zone of the database, share the pool, repeat the log buffer, and balance the size of the global zone of the process.
3. Optimize applications
3.1 adopt a unified SQL Program
This is because ORACLE first compares the SQL text to be executed with the previously executed SQL text saved in the public area when executing the SQL text. The SQL text is the same, the parsing process of the SQL text to be executed will be skipped. This will speed up the execution of the SQL text by reducing the number of resolutions.
3.2 select appropriate SQL code
The same result can be implemented in many ways during database operations. We should select appropriate SQL code to make it clearer and run faster.
3.3 clear column names
The names of the columns obtained using the SELECT statement must be explicitly specified, and as few records as possible should be extracted. When using SORT and so on, because ORACLE places the necessary column values in the WORK field, reducing the number of columns saves I/O return.
3.4 Note the order of combining Indexes
The indexing effect varies depending on the column order. For example: (AGE, ADDRESS) combine the order of the index.
Where age = 28 and address = 'bei jing' & effective with indexes
Where age = 28 & effective with indexes
Where address = 'bei jing' & the full index is invalid.
3.5 pay attention to the use of WHERE
3.5.1 do not use functions for index Columns
Once a function is used for an index column, the index will become invalid. Therefore, you can change the program to use the index whenever possible. For example:
Where substr (TELNO, 88%) = '88 'should be changed to where telno like '123.
3.5.2 do NOT use NOT for index Columns
Once an index column is not equal to or used, the index becomes invalid, resulting in slow speed.
3.5.3 do not use NULL
If NULL is used, the index will be invalid and become full table search, affecting the processing speed.
3.5.4 do not calculate index Columns
If the index column is calculated, the index will be invalid, and the speed will be slow.
4. Conclusion
The optimization method proposed in this article can improve the performance of ORACLE databases and improve the execution efficiency of ORACLE Applications. This is only a summary of my personal experience in practical work, hoping to help the majority of colleagues.
Previous Page
