[Original] Oracle database performance optimization solution [deleted]

This solution has a good effect in actual problem handling. It only uses some of the measures in the solution and has improved the performance by several hundred times. In particular, the stored procedure of submitting 1.3 million rows of data into a single transaction after a Combined Query of three million data tables and business operations, cannot be completed in hours, the task is completed in 119 seconds. Therefore, I would like to share my experience with beginners and get further guidance from database experts.

1 problem
The xxxxxxx system is a centralized business data processing system. There are many millions of data operations, reads, writes, and queries in a single transaction.

At present, the performance is relatively low, and some operation steps need to run for a long time. More than 30 minutes, or even 1.5 hours of operation in some processes, no results.

 

2 Purpose
This article attempts to make some specific analyses and proposes targeted performance optimization and improvement solutions;

 

3. Analysis
3.1 Analysis Method
L use performance detection logs

L use the lock viewing mechanism on the Oracle Console

L SQL status tracking

L database scheme browsing

 

3.2 analysis results
L currently, the entire operation logic uses the Oracle stored procedure.

L use an explicit cursor. And try to use the batch operation method to improve performance.

L database index creation is not reasonable.

L At present, data in Phase I will be deleted from multiple single tables or 0.26 million to 1.05 million of data will be inserted.

L no data history table or background automatic operation mechanism.

 

4. Improvement Measures
4.1 priority
4.1.1 index creation
L The data volume of a dictionary-type table is small and used in queries. You need to index all the related fields during the query. Suitable for situations where the data volume is less than 5000.

L after testing, it is found that the actual running data is mostly string type. In addition, the content of the first half of the string is almost the same, and the latter half is significantly different. Therefore, the recommended index type is -- reverse B-tree index.

L large data tables often commit millions of data in one transaction. We recommend that you create a drop index before the insert operation and a new index after the insert operation. Although index creation and deletion also consume time. However, the elimination of blank blocks in the middle will benefit the performance improvement.

[Note: You must create as few indexes as possible for tables with large data volumes]

 

4.1.2 use of materialized views
Refresh view is added when data needs to be changed to save additional overhead for every dynamic query.

[Note: You can create indexes for materialized views as needed to further improve the retrieval speed]

 

4.1.3 use of temporary tables
Similar to materialized views, however, there are several special features.

L reduces the overhead of redo logs.

L does not need to be physically stored on the hard disk, reducing disk operation time.

L if you need to recreate data each time, you do not need to delete the time overhead of the data.

[Related measures: see 4.2.1]

 

4.1.4 economical field size design.
Avoid performance overhead caused by waste of database space.

 

4.1.5 avoid unnecessary Association queries after field functions are processed
Be sure to create a function index or cancel the association after function processing. Avoid full table scan.

 

4.2 Database Server Improvement Measures
4.2.1 create an Oracle database with version 9.20 or later
This can better support temporary tables and greatly reduce the overhead of redo logs by using temporary tables.

 

4.2.2 use RAID policies to speed up hard disk I/O operations
Different database hard disks can be written separately for parallel operations.

 

4.2.3 distribute large data tables on multiple hard disks to improve parallel capabilities
Partition Table creation.

 

4.2.4 create a separate data index space
Improve the Performance of Indexing large data volumes.

 

4.3 methods for further improvement
4.3.1 create historical data series tables to distribute the data pressure of a single table
With the development of the system time, the data volume will increase further. No table is supported. In addition, the business characteristics determine that the association of each phase is not very close, and it is suitable for creating historical data tables.

Changes to the database design will lead to changes in the processing logic, resulting in a certain amount of work for development and testing.

[Note: an additional benefit of a history table is that you can use truncate to clear the entire data period. The performance will be improved many times over the current delete operation, and the larger the data volume, the more indexes, the greater the effect difference]

 

4.3.2 it is recommended that the data table be redesigned with performance as the priority
Currently, all are insert, select, and delete. update should be used properly.

 

4.3.3 establish background Operation Mechanism
Implement the background Running Mechanism and give a general running Progress ratio.

 

4.3.4 Replace the Union Field identifier with a single meaningless field identifier
It is found that multiple large tables use six to eight fields as the unique identifier of the data. We recommend that you use a single meaningless field instead. This involves development and testing work, so it is put in further work suggestions.

 

5. Final supplement
From the long-term use of the system, it is recommended that the relevant personnel can gradually complete all the optimization work.