Oracle variable binding

In the Java source Program Bind Variable)
In writing SQL statements in Java, Oracle is not used to bind variables, which greatly reduces the performance of the database:
1. Hard Analysis of SQL statements (hard parse) is too much, which seriously consumes CPU resources and prolongs the overall execution time of SQL statements. The execution process of SQL statements is divided into several steps: syntax check, analysis, execution, and returned results. The analysis is divided into hard parse and soft parse ).
After an SQL statement passes the syntax check, Oracle will first go to the shared pool to find whether there is the same SQL statement. If it finds it, it will be called soft analysis and then execute the SQL statement.
Hard analysis mainly checks whether all objects involved in the SQL are valid and their permissions. Then, the execution plan is generated based on the RBO or CBO mode before the SQL statement is executed.
It can be seen that hard analysis has a lot more actions than soft analysis, and the key here is "whether the same SQL exists in the Shared Pool", which depends on whether to use the bound variable.
2. The number of SQL statements in the shared pool is too large, and the reusability is extremely low. This accelerates the aging of SQL statements, resulting in excessive fragments 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 leads to the hit rate in shared_pool_size.
Decreased, the shared pool fragments increased, and the available memory space was insufficient. 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 number
CPU resources, resulting in a sharp decline in performance. The absence of binding variables violates the design principles of the Oracle shared pool and the idea of sharing the design.
When writing Java programs, we used to define Java program variables and put them into SQL statements, as shown in
String v_id = 'xxxxx ';
String v_ SQL = 'select name from table_a where id = '+ v_id;
Above Code It seems that the variable v_id is used, but this is a Java program variable, instead of the Oracle binding variable. After the statement is passed to the database, the Java program variable
It has been replaced with a specific constant value and becomes:
Select * From table_a where name = 'xxxxx ';
If this statement is executed for the first time, a hard analysis is performed. Later, the v_id value in the same Java code was changed (v_id = 'yyyyyy'), and the database received the following statement:
Select * From table_a where name = 'yyyy ';
Oracle does not consider the preceding two statements to be the same. Therefore, it performs a hard analysis on the second statement. The execution plans of these two statements are the same!
In fact, you only need to change the above Java code to the following to use the Oracle binding variable:
String v_id = 'xxxxx ';
String v_ SQL = 'select name from table_a where id =? '; // Embed the bound variable
Stmt = con. preparestatement (v_ SQL );
Stmt. setstring (1, v_id); // assign a value to the bound variable
Stmt.exe cutequery ();
In Java, the setxxx series methods can be used in combination to assign values to variable bindings of different data types, thus greatly optimizing the performance of SQL statements.