Oracle implements the system resource management allocation plan and optimizes the connection Scheme

OracleImplementation SystemResource Management allocation planAndSQL OptimizationThe connection scheme is what we will introduce in this article. Next we will introduce this part one by one.

Implement system resource management allocation plan

ORACLE provides Database Resource ManagerDRM and Database Resource manager) to control user Resource allocation. DBA can use it to allocate system Resource percentages for user and job classes. In an OLDP system, 75% of CPU resources can be allocated to online users, and the remaining 25% is reserved for batch users. In addition, you can perform multi-level CPU allocation. In addition to CPU resource allocation, DRM can also limit the parallel operations on resource user groups.

Use the most SQL-optimized database connection Solution

1. Use the direct ole db database connection method.

You can use ADO to connect to a database in two ways. One is the traditional ODBC method and the other is the ole db method. ADO is based on the ole db technology. To support ODBC, you must establish the call conversion from the corresponding ole db to ODBC. However, you do not need to convert the data using the direct ole db method, this increases the processing speed.

2. Use the Connection Pool mechanism

In database processing, the biggest resource cost is to establish a database connection, and the user still has a long connection wait time. The solution is to reuse the existing Connection, that is, use the Connection Pool object mechanism.

The Connection Pool principle is: a Connection buffer Pool is maintained in the IIS + ASP system, so that when the next user accesses, a database Connection is directly obtained in the Connection buffer Pool, instead of reconnecting to the database, the system response speed can be greatly improved.

3. Efficient SQL statement Design

Generally, the following methods can be used to optimize the performance of SQL statements on data operations:

1) Reduce the number of queries to the database, that is, reduce the number of queries to the database by using distributed database objects such as snapshots and graphs.

2) try to use the same or very similar SQL statements for queries. This not only makes full use of the analyzed syntax tree in the SQL sharing pool, the possibility of hitting the data to be queried in SGA is also greatly increased.

3) restrict the use of dynamic SQL statements. Although dynamic SQL statements are useful, dynamic SQL performs syntax analysis again even if there is a completely identical query value in the SQL sharing pool.

4) Avoid executing SQL statements without any conditions. When an SQL statement without any conditions is executed, it is usually required to perform the FTS. The database first locates a data block and then searches for other data in sequence, this is a long process for large tables.

5) If you have constraints on the data in some tables, it is best to use the description integrity of the SQL statements in the table to be created, rather than in the SQL program.

6) The automatic COMMIT mode can be canceled to merge SQL statements into a group for execution and then submit them in a centralized manner. The program can also use COMMIT and ROLLBACL to submit and roll back the transaction explicitly.

7) It takes a long time to retrieve a large amount of data. setting the number of row prefetch can improve the system performance and set a maximum value. When the SQL statement returns a row that exceeds this value, the numeric database temporarily stops running, unless a user sends a new instruction, the user starts to organize and display data, rather than waiting for the user to continue.

Make full use of the data background processing solution to reduce network traffic

1. Create a temporary table or view reasonably

Creating a temporary table or view is to create a new table or view on the basis of the database as needed. A new table can be created for querying information after multi-Table Association. For a single-Table query, a view can be created, in this way, we can take full advantage of the large capacity and strong scalability of the Data Base. All condition judgment and numerical calculation statistics can be processed in the database server background and then appended to the temporary table, the process of forming data results can be implemented by the database process or function.

2. full use of database Packaging Technology

Use the database Description Language to compile the database process or function, and pack the process or function into a uniform running package in the database background.

3. Use of data replication, snapshots, views, and Remote Procedure Call Technology

Data Replication: Copies data to the local database at a time, so that local data will be used for future queries, but it is only suitable for those data with little changes. Snapshots can also be used to dynamically copy data between distributed databases, define the automatic snapshot refresh time or manual refresh, to ensure the integrity of reference data. The Remote Call process also greatly reduces network congestion caused by frequent SQL statement calls.

In short:There is no uniform solution to all performance problems, but ORACLE provides a rich selection environment, the architecture, software structure, model object, and specific business and technical implementation of the ORACLE database can be considered as a whole. To improve the system performance, a system-wide approach is required. During database optimization, applications, I/O subsystems, and operating system OS should be optimized accordingly. Optimization is the process of purposefully changing one or more components of the system to meet one or more goals. For Oracle, optimization aims to adjust the component level to improve performance, that is, to increase throughput and reduce response time. If DBA can comprehensively consider the optimization solution from the nine aspects above, it is believed that most ORACLE Applications can achieve optimal data access.

Here is an introduction to the Oracle Resource Management allocation plan and the optimization of the connection solution. I hope this introduction will be helpful to you!