Application and significance of Oracle variable binding in C #. NET

1. What is variable binding?
Bind variable ):
Select * from emp where empno =: empno;
It is a placeholder placed in the query by the user. It will tell Oracle that "I will then provide a value for this variable. Now we need to generate a scheme, but when I actually execute the statement, will provide the actual value that should be used ".
In essence, it is used to replace the substitution variables of constants in SQL statements. Variable binding makes the SQL statements submitted each time identical.

Ii. Why bind variables?
Variable binding can reduce hard syntax analysis and optimize sharing pool usage. In oracle, there are two optional parsing processes for a submitted SQL statement: Hard parsing and soft parsing.
After an SQL statement is submitted, oracle first checks whether there are identical statements in the shared buffer pool. If yes, it only needs to perform soft analysis; otherwise, it must perform hard analysis.
A hard parsing requires many steps such as analysis, resolution, security check, execution path formulation, and access plan optimization. It consumes a lot of cpu and resources.
For example, to query children numbered 001, the following two write methods can be implemented:
Select * from t_child where childid = '001'; // do not use Bind Variable
Select * from t_child where childid =: childid; // use the Bind Variable
However, in actual applications, it is often the case that a query of a child with the serial number 001 may not be used again. Then you may query the child '002 'and then query '003. In this way, each query is a new query and requires hard parsing;
The second query statement provides the Bind Variable childid, which is provided during query execution. After the query is compiled once, the query solution is stored in the Shared Pool and can be used for retrieval and reuse; in terms of performance and scalability, the difference between the two is enormous, or even astonishing;
Without binding variables, different query conditions lead to too many SQL statements in the Shared Pool and extremely low reusability. This accelerates the aging of SQL statements and leads to excessive fragmentation in the shared pool. The number of different SQL statements in the shared pool is huge. According to the LRU principle, some statements gradually ages and are eventually cleared out of the shared pool. This causes the hit rate in shared_pool_size to decrease and the number of fragments in the shared pool to increase, insufficient memory space. To maintain the internal structure of the shared pool, you need to use latch, a lock with a short internal lifecycle, which will use a large amount of cpu resources, resulting in a sharp reduction in performance.
The absence of binding variables violates the design principles of the oracle shared pool and the idea of sharing the design.

3. How to bind variables

= = = = SqlHelper(sql, dbName, valuelist, parmlist, 

  DataSet SqlHelper( query,  dataBaseName, ArrayList bllist, ArrayList pamlist,  = ==  (adoHelper._dbFactory.DatabaseType ===  ( i = ; i < bllist.Count; i++ paramvalue =  (bllist[i] == =  OracleParameter(pamlist[i].ToString(), = (bllist[i].GetType().ToString() == = == =  ==  + 

 

Iv. Restrictions on variable binding
When you bind a variable to an indexed field (including a field set) with a very large set of fields (sets), you may not be able to select the optimal query plan, therefore, the query efficiency is very low.
The trend of the set is very large (that is, the skew is very serious). For example, for an index field, there are 1000 records in total, 900 records in A value, and 50 records in B value; if the value of C is 50, we can say that the set of this field is very large. Of course, the potential algorithms of this set are complex. They are related to query conditions and whether to create indexes. For example, if full table scan: Set potential = number of records; index scan, query condition "=": Set potential = number of records/number of unique values on the field;
So why is the query efficiency lower when the bound variable is used when the set is large?
When executing SQL statements, Oracle calculates the cost of each access path and uses the access path with the minimum cost as the statement execution plan. If the condition for the first query happens to be the most recorded value in the index field, the index scan cost is very high. According to the analysis, the full table scan is used, the query plan is saved to the shared pool. When other values are queried, the cost is very low. However, due to the use of variable binding, hard Parsing is not performed, that is, the execution plan used is not analyzed, instead, you can directly use the previous query plan, which will lead to the failure to select the optimal query plan.
The benefits of binding variables to SQL statements that are executed once after a considerable period of time will be offset by the inability to effectively use the optimizer.