Use Oracle to bind variables when writing SQL statements in the Java source program

When writing SQL statements in the Java source program, Oracle-bound variables are used for compiling SQL statements in Java. Oracle-bound variables are not used, which greatly reduces the database performance, it is manifested in two aspects: 1. Too many hard parse statements consume CPU resources, which 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 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. 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; the above Code seems to use the variable v_id, but this is a Java program variable, rather than the Oracle binding variable, after the statement is passed to the database, the Java program variable has been replaced with a specific constant value and changed:
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 as the same statement. Therefore, a hard analysis is performed 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, combined with the setxxx series methods, you can assign values to bind variables of different data types, thus greatly optimizing the performance of SQL statements.
From: http://154850.blog.51cto.com/144850/41536