Database optimization technology-dynamic variable binding in Oracle databases

We know that the shared pool is an extremely important shared memory structure in the System Global Area and SGA. HoweverOracle DatabaseStore parsed and compiled SQL statements together with other content here. However, parsed SQL statements must be reused if compiled SQL statements are to be used in most cases.Bind Variable. This document describes how to bind a variable to an Oracle database.

Bind variable) is a placeholder in the query.

For example, compare the following SQL statements:

 
 

  
  
Select * from table where id = 1 and
  
  
 
  
  
My_id: = 1
  
  
 
  
  
Select * from table where id = my_id
 
 

For the first SQL statement, using a direct constant in the query), then each query will be a brand new query, which is never seen in the database in the past, certainly, the query will be resolved, limited name resolution), security inspection, optimization, and so on. It is easy to say that, even if every different statement you execute must be compiled at the time of the statement. The parsing includes statements with hard-coded variables calledHard Parsing)

The second query uses a Bind Variable my_id, which is provided when the query condition is strict. This query is compiled sequentially, and then the query plan will be stored in a shared pool library cache), so that the query plan can be obtained and reused later. Reuse the resolved query plan is calledSoft resolution).

Differences between soft resolution and hard resolution are mainly reflected in the following aspects:

1. Compared with soft parsing, hard parsing requires longer time and requires more resources. Hard parsing will contract the number of users supported by the system.

2. When a hard-parsing query is performed, the database will be replaced by a low-level serial wearable device, which is called a latch ), these latches prevent data structures in Oracle shared memory from being modified by two processes at the same time. In case someone is correcting the data structures, they are not allowed to read the data again. The longer and more frequent the construction of latches for these data, the longer the waiting queue will process and the longer the waiting queue. You may start to monopolize valuable resources. Sometimes your computer is underutilized, but all usage in the database is extremely slow. This illusion may be caused by the fact that someone is cutting down a serial wearable device, and other people looking forward to serial wearable devices are queuing up, so you cannot run at full speed. In the database, if one condition is not used properly, it will seriously affect all other functions used. If only one thin utilization is not conducive to variable binding, the resolved SQL statements can be properly stored in the sharing pool for reuse even if the other exploitation is well designed, however, because of the existence of this thin utilization, the stored SQL statements will be removed from the sharing pool in a short time. This makes the appropriate use of these designs will surely make a hard Analysis of SQL again.

Example of code termination:

1. variables bound to oracle by default:

 
 

  
  
for i in 1..1000 loop  
  
  
 
  
  
select count(*) into my_count from table where my_type = i;  
  
  
 
  
  
ne.nexuscenter.com.cn<end loop; 
 
 

In the preceding case, Oracle binds the variable itself. That is, if the parameter is retained in an array, the select statement is placed in a loop and the select statement is compiled in sequence.

2. dynamically bind variables

 
 

  
  
my_type:='type1';  
  
  
 
  
  
my_count := 0;  
  
  
 
  
  
my_sql:='select count(*) into :x from table where type = :y' 
  
  
 
  
  
Execute Immediate my_sql into my_count using my_type; 
 
 

However, this code includes additional strings, which are not fully required.

Here is an introduction to Oracle Database optimization. If you want to learn more about Oracle databases, read this article: http://database.51cto.com/oracle/, and I believe it will surely help you reap the benefits!