Summary of some practical Oracle Database optimization policies

To improve query efficiency, we often make some optimization strategies. This article mainly introduces someOracle DatabaseBut it is very useful.Optimization StrategyTo help you.

SQL statement Optimization

This is easy to handle. You can find SQL statements that are high in CPU usage and modify them based on indexes and SQL skills.

SELECT function is not used

When performing frequent query monopoly, try to directly select the field name, and then use the C language code to perform secondary processing on the query harvest, so as to avoid the possibility of making Oracle a mixed function for mathematical computing. Due to the versatility of Oracle, its function and mathematical computation speed is far slower than the computation speed after being directly compiled into machine code using C language.

Bind Variable

This can greatly reduce the "hard parse" of SQL, and we have ended variable binding in most partial processes. Some of the things that have not ended up yet can be quickly corrected.

Batch FETCH

In case of sequential select, more than a few hundred or thousands of records will be returned. The use of batch Fetch, for example, sequential fetch 1000 entries of registration, is much faster than a batch of fetch data, it can also reduce the pressure on oracle.

Batch submission

Sequential correction of many regulations such as less than 10000) registration, and then sequential submission, to be faster than each submission. Of course, the premise is that the business logic approves this.

Batch addition, deletion, and modification

In the event that multiple registrations may be excluded from the necessity of sequential correction, the measures for batch array binding can be approved, which is similar to the aforementioned "binding variable". The difference is that the former is bound to a variable, it is bound to the first pointer of a large array, which is much faster than one-by-one binding.

SQL pre-resolution

The large part above is batch monopoly, and a common scenario is small transaction monopoly, but the frequency is extremely high nextplas.com. In this scenario, the idle SQL statements are not mixed and there is almost no room for optimization. However, due to frequent monopoly, the CPU remains high. Most of our processes are using the following method:

 
 

  
  
loop  
  
  
 
  
  
parse sql;  
  
  
 
  
  
bind var;  
  
  
 
  
  
execute sql;  
  
  
 
  
  
end loop; 
 
 

Although we take advantage of variable binding, due to frequent monopoly, it also forms many "soft parse" and network communication. In the memory database, we are free to approve pre-resolution measures to improve efficiency. In fact, Oracle has provided developers with this form for a long time, but developers are too tedious and inefficient. Change the process to the following format:

 
 

  
  
parse sql;  
  
  
 
  
  
bind var;  
  
  
 
  
  
loop  
  
  
 
  
  
execute sql;  
  
  
 
  
  
end loop; 
 
 

This can reduce the pressure on Oracle and at least double the efficiency. However, this form affects the changes in the process structure, which is certainly done in the design phase. Otherwise, there will be a great deal of danger and workload if you try again later.

SQL statement Optimization Measures

1. SQL statements are written in uppercase, because Oracle always parses SQL statements first, converts lowercase letters into uppercase letters, and then implements SQL statements.

2. Avoid using NOT in the index column, and avoid using NOT in the index column. NOT will occur in the same way as using the function in the index column.

3. When Oracle "Encounters" NOT ", it will still use indexes to scan the entire table.

4. Avoid using computation on index columns. In the WHERE clause, it is assumed that the index column is a part of the function www.greatever-china.com. The optimizer uses full table scanning instead of indexing.

5. Use DISTINCT to minimize monopoly, and use EXISTS to rotate DISTINCTvalues shoshould never be negative.

These are all the optimization policies for Oracle databases. If you want to know