在JAVA 源程序中编写SQL语句时使用ORACLE 绑定变量

在JAVA 源程序中编写SQL语句时使用ORACLE 绑定变量

在JAVA 源程序中编写SQL语句时使用ORACLE 绑定变量( bind variable )

在JAVA中的SQL 语句的编写方面，没有使用ORACLE 绑定变量，很大程度上降低了数据库的性能，表现在两个方面：

1、SQL语句硬分析(Hard Parse)太多，严重消耗CPU资源，延长了SQL语句总的执行时间

SQL语句的执行过程分几个步骤：语法检查、分析、执行、返回结果。其中分析又分为硬分析(Hard Parse)和软分析(Soft Parse)。
一条SQL语句通过语法检查后，Oracle 会先去shared pool 中找是否有相同的sql，如果找着了，就叫软分析，然后执行SQL语句。
硬分析主要是检查该sql所涉及到的所有对象是否有效以及权限等关系，然后根据RBO或CBO模式生成执行计划，然后才执行SQL语句。
可以看出，硬分析比软分析多了很多动作，而这里面的关键是“在shared pool 中是否有相同的sql”，而这就取决于是否使用绑定变量。

2、共享池中SQL语句数量太多，重用性极低，加速了SQL语句的老化，导致共享池碎片过多。
共享池中不同的SQL语句数量巨大，根据LRU原则，一些语句逐渐老化，最终被清理出共享池；这样就导致shared_pool_size 里面命中率
下降，共享池碎片增多，可用内存空间不足。而为了维护共享池内部结构，需要使用latch，一种内部生命周期很短的lock，这将使用大量
的cpu 资源，使得性能急剧下降。
不使用绑定变量违背了oracle 的shared pool 的设计的原则，违背了这个设计用来共享的思想。

编写java 程序时，我们习惯都是定义JAVA 的程序变量，放入SQL 语句中，如
String v_id = 'xxxxx';
String v_sql = 'select name from table_a where id = ' + v_id ;

以上代码，看起来是使用了变量v_id ，但这却是java 的程序变量，而不是oracle 的绑定变量，语句传递到数据库后，此java 的程序变量
已经被替换成具体的常量值，变成：
select * from table_a where name = 'xxxxx' ;

假定这个语句第一次执行，会进行硬分析。后来，同一段java 代码中v_id 值发现变化（v_id = 'yyyyyy'），数据库又接收到这样的语句：
select * from table_a where name = 'yyyyyy' ;

ORACLE 并不认为以上两条语句是相同的语句，因此对第二条语句会又做一次硬分析。这两条语句的执行计划可是一样的！

其实，只需将以上java 代码改成以下这样，就使用了oracle 的绑定变量：
String v_id = 'xxxxx';
String v_sql = 'select name from table_a where id = ? ';//嵌入绑定变量
stmt = con.prepareStatement( v_sql );
stmt.setString(1, v_id ); //为绑定变量赋值
stmt.executeQuery();

在Java中，结合使用setXXX 系列方法，可以为不同数据类型的绑定变量进行赋值，从而大大优化了SQL 语句的性能。

没有使用绑定变量是使用Oracle数据库的应用系统性能问题主要原因和可伸缩性的主要障碍，Oracle的共享池的操作方法就决定开发人员应该使用绑定变量，如果想要Oracle运行速度减慢，甚至完全中止，就可以拒绝使用绑定变量。

在SQL语句中，绑定变量是一个占位符。例如，为了查询员工号为123的员工的信息，可以查询：1）select * from emp where empno=123;另外，也可以查询：2）select * from emp where empno=:empno。

在一个典型的OLTP系统中，查询员工123一次，可能再也不会查询，以后将查询员工456，员工789等。如果像语句1）中那样使用硬编码量（常量），那么每次查询都是一个新查询，即在数据库共享池中以前没有过的查询。每次查询必须经过分析、限定（名称解析）、安全检查、优化等等，简单地说，执行的每条语句在每次执行时都将必须经过编译。

在第二个查询2）中使用了绑定变量:empno，它的值在查询执行时提供。查询经过一次编译后，查询方案将存储在共享池中，可以用来检索和重用。在性能和可伸缩性方面，这两者的差异是巨大的，甚至是惊人的。

从上所述，很明显看出，分析一个带有硬编码量的语句将比重用一条已分析过的查询方案花费更长的时间和消耗更多的资源，不明显的是前者将减少系统所能支持的用户数量。很明显，部分原因是由于增加资源消耗量，但更主要的因素是在解析sql语句的过程中对共享池中锁存器（latch）的争用。

通过下面的两个小程序我们可以看出其中的差别，其中程序NoBind.java没有使用绑定变量，程序UseBind.java使用了绑定变量。

程序NoBind.java：

// You need to import the java.sql package to use JDBC

import java.sql.*;

import oracle.jdbc.*;

// We import java.io to be able to use the i/o Class

import java.io.*;

class NoBind

{

public static void main(String args[])

throws SQLException, IOException

{

// Load the Oracle JDBC driver

DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521: demo","scott","tiger");

// Create a statement

Statement stmt = conn.createStatement();

for(int i=1;i<=5;i++)

{

ResultSet rset = stmt.executeQuery("select hisal from salgrade where grade="+i);

while (rset.next())

{

System.out.println(rset.getString(1));

}

// close the result set

rset.close();

}

// close the statement and connect

stmt.close();

conn.close();

}

}

程序UseBind.java：

// You need to import the java.sql package to use JDBC

import java.sql.*;

import oracle.jdbc.*;

// We import java.io to be able to use the i/o Class

import java.io.*;

class UseBind

{

public static void main(String args[])

throws SQLException, IOException

{

// Load the Oracle JDBC driver

DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521: demo","scott","tiger");

// Create a PreparedStatement

PreparedStatement pstmt = conn.prepareStatement("select hisal from salgrade where grade=?");

for (int i=1;i<=5;i++)

{

// use the setXX() method

pstmt.setInt (1,i);

ResultSet rset = pstmt.executeQuery();

while (rset.next())

{

System.out.println(rset.getString(1));// print the first column

}

// close the result set

rset.close();

}

// close the statement and connect

pstmt.close();

conn.close();

}

}

上面两个程序都是通过scott/tiger登录，从salgrade表中检索5条数据，然后在终端上打印出来，不同之处在于第一个程序使用硬编码量，通过拼字符串的方式来构造sql语句，第二个程序使用了绑定变量，先使用一个占位符代替实际数值，然后再通过setInt()方法给占位符赋值。

执行程序NoBind.java后，通过查询v$sql视图可以发现，Oracle解析了5条不同的sql语句，如下：

SQL> select sql_text from v$sql where sql_text like 'select hisal from%';

SQL_TEXT

-------------------------------------------------------------------------------

select hisal from salgrade where grade=1

select hisal from salgrade where grade=4

select hisal from salgrade where grade=2

select hisal from salgrade where grade=5

select hisal from salgrade where grade=3

刷新共享池，然后再执行程序UseBind.java后，通过查询v$sql视图可以发现，Oracle只解析了1条sql语句，如下：

SQL> select sql_text from v$sql where sql_text like 'select hisal from%';

SQL_TEXT

-------------------------------------------------------------------------------

select hisal from salgrade where grade=:1

通过上述测试