oracle绑定变量
在oracle 中，对于一个提交的sql语句,存在两种可选的解析过程, 一种叫做硬解析,一种叫做软解析.
一个硬解析需要经解析,制定执行路径,优化访问计划等许多的步骤.硬解释不仅仅耗费大量的cpu，更重要的是会占据重要的门闩（latch）资源，严重的影响系统的规模的扩大（即限制了系统的并发行），而且引起的问题不能通过增加内存条和cpu的数量来解决。之所以这样是因为门闩是为了顺序访问以及修改一些内存区域而设置的，这些内存区域是不能被同时修改。当一个sql语句提交后，oracle会首先检查一下共享缓冲池（shared pool）里有没有与之完全相同的语句，如果有的话只须执行软分析即可，否则就得进行硬分析。
而唯一使得oracle 能够重复利用执行计划的方法就是采用绑定变量。绑定变量的实质就是用于替代sql语句中的常量的替代变量。绑定变量能够使得每次提交的sql语句都完全一样。
普通sql语句：
SELECT fname, lname, pcode FROM cust WHERE id = 674;
SELECT fname, lname, pcode FROM cust WHERE id = 234;
SELECT fname, lname, pcode FROM cust WHERE id = 332;
含绑定变量的sql 语句：
SELECT fname, lname, pcode FROM cust WHERE id = :cust_no;
Sql*plus 中使用绑定变量：
sql> variable x number;
sql> exec :x := 123;
sql> SELECT fname, lname, pcode FROM cust WHERE id =:x;
pl/sql
pl/sql很多时候都会自动绑定变量而无需编程人员操心，即很多你写得sql语句都会自动利用绑定变量，如下例所示：
create or replace procedure dsal(p_empno in number)
as
/
也许此时你会想要利用绑定变量来替代p_empno,但是这是完全没有必要的，因为在pl/sql中，引用变量即是引用绑定变量。
但是在pl/sql中动态sql并不是这样。
在vb，java以及其他应用程序中都得显式地利用绑定变量。
对于绑定变量的支持不仅仅限于oracle,其他RDBMS如SQL SERVER支持这一特性。
但并不是任何情况下都需要使用绑定变量，下面是两种例外情况：
１．对于隔相当一段时间才执行一次的sql语句，这时利用绑定变量的好处会被不能有效利用优化器而抵消
２．数据仓库的情况下。
********************************************************************************************************************************
经常可以遇到这样的情况，由于程序员的sql语句写的非常不合理，导致Oracle数据库查询速度缓慢，严重的情况会导致数据库的故障。
举个例子，我以前就遇到过Oracle数据库定期（一周左右）出现 ORA-4031错误（shared pool内存不够，无法连接Oracle数据库），此时数据库已经无法再使用了。
必须关闭重起，来释放那些占在shared pool里面无法释放的对象。
所以对于一个Oracle DBA来说，需要定期的检查shared pool中的sql语句占用的内存空间，对于严重占用shared pool，无法释放的sql语句，必须要求程序员修改或优化sql语句。
select sql_text ,sharable_mem from v$sql where sharable_mem > '100000' order by sharable_mem ;
上面的sql语句是查询shared pool中占用内存超过100K的sql语句。
这个sql可以非常有效的检查出Oracle shared pool中那些严重占用内存的sql，
根据我的经验，绝大多数有问题的sql语句都会在这里留下痕迹，通过在这里找出有问题的sql语句并进行修改，再反复运行这个sql脚本，直到所以有问题的sql都处理完毕，
这就是对Oracle数据库在sql上面的最好的优化，可以保证不会因为程序员的sql语句问题导致Oracle数据库的性能问题。
共享池主要由库缓冲区（共享SQL区和PL/SQL区）和数据字典缓冲区组成。
select * from v$sgastat; --显式SGA的状态信息。
有的人写的SQL语句非常复杂，嵌套了好几层，SQL语句本身写的很差，就有可能会占用很大的SQL区域。
这是我从Oracle Metalink里面摘下来的：
引用
If the SHARED_POOL_SIZE is large enough, most ORA-04031 errors are a result of
其实现实的很多情况是本来不需要那么复杂的sql的，由于程序员水平不够，写了那种很糟糕很复杂的sql，造成的数据库性能问题。
另外还有一个常识问题，却很容易被忽略。比如：
select * from table_name where id = 1;
select * from table_name where id = 2; 对于这种带参数的sql，id = ? 这个地方叫做占位符(Placeholder)。
拿PHP为例，很多人喜欢这样写代码
$sql = "select * from table_name where id = ";
$id=1;
$stmt = ociparse($conn,$sql.$id);;
ociexecute($stmt);;
......
$id = 2;
$stmt = ociparse($conn,$sql.$id);;
ociexecute($stmt);;
拿Java为例，是这样的：
Java代码
String sql = "select * from table_name where id = ";
Statement stmt = conn.createStatement();;
rset = stmt.executeQuery(sql "1");;
......
rset = stmt.executeQuery(sql "2");;
这种写法，对于Oracle数据库来说，完全就是两条不同的sql语句，
select * from table_name where id = 1;
select * from table_name where id = 2; 每次查询都要进行sql语句的执行解析，并且每个sql都会分配一个区域来存放sql解析后的二进制可执行代码。
试想，要是id不同的10万个sql呢？Oracle就会分配10万个sql区域来分别存放10万个这样的id不同的sql语句。对于一个数据库驱动的Web网站这样情况下，SGA开的再大，也会很快被耗尽share pool的，最后报一个ORA-4031错误。数据库就连接不上了，只好重起。
正确的写法应该是：
Java代码
$stmt = ociparse($conn,"select * from table_name where id = :id" );;
ocibindbyname($stmt,":id" ,&$id, 12 );;
$id =1 ;
ociexecute($stmt);;
...
$id = 2 ;
ociexecute($stmt);;
Java代码
PreparedStatement pstmt = conn.prepareStatement("select * from table_name where id = ?");;
pstmt.setInt(1,1);;
rset = pstmt.executeQuery();;
...
pstmt.setInt(1,2);;
rset = pstmt.executeQuery();;
这样Oracle数据库就知道你实际上用的都是同一条sql语句，会以这样的形式：
select * from table_name where id = :1
解析执行后存放在sql区域里面，当以后再有一样的sql的时候，把参数替换一下，就立刻执行，不需要再解析sql了。既加快了sql执行速度，也不会占有过多SGA的share pool。
可惜的是，很多程序员明知道这个问题，却意识不到问题的严重性，因为上面那种写法，编程的时候很灵活，sql语句可以动态构造，实现起来很容易，后面那种写法，sql语句是写死的，参数不能再变了，编程经常会非常麻烦。
很多数据库的性能问题都是这样造成的。
有兴趣在一个生产系统中，用上面sql检查一下，看看是否选择出来的有很多都是一样的sql语句，只是参数不同，如果是这样的话，就说明程序员的代码写的有问题。
呵呵，所以sun才从statement中再继承出来一个PreparedStatement.
************************************************************************************************************************************
我们都知道在Oracle中每条SQL语句在执行之前都需要经过解析，这里面又分为软解析和硬解析。那么这两种解析有何不同之处呢？它们又分别是如何进行解析呢？Oracle内部解析的步骤又是如何进行的呢？下面我们就这些话题进行共同探讨。
在Oracle中存在两种类型的SQL语句，一类为DDL语句，他们是从来不会共享使用的，也就是每次执行都需要进行硬解析。还有一类就是DML语句，他们会根据情况选择要么进行硬解析，要么进行软解析。
在Oracle 8i OCP教材的023中1-12有说明SQL语句的解析步骤，当一条SQL语句从客户端进程传递到服务器端进程后，需要执行如下步骤：
* 在共享池中搜索 SQL 语句的现有副本
* 验证 SQL 语句的语法是否准确
* 执行数据字典查找来验证表和列的定义
* 获取对象的分析锁以便在语句的分析过程中对象的定义不会改变
* 检查用户访问引用方案对象的权限
* 确定语句的最佳执行计划
* 将语句和执行计划载入共享的 SQL 区
这个先入为主的概念一直占据着我的脑海，我认为硬解析就是上面几个步骤。相对于硬解析，软解析的步骤就是上面第一步找到现有SQL语句的副本后，只需要验证用户是否有权限执行就是了，这样省略上面好几个步骤，相对硬解析来说性能开销就非常小了。即使是在论坛上和大家讨论时，我也一直坚持这个看法。直到前一天看了Tom的《Effective Oracle By Design》中关于语句处理的章节后，我才知道这个自己一直坚持的观点事实上是错误的。
事实上，在Oracle中SQL语句的解析步骤如下：
1、 语法检测。判断一条SQL语句的语法是否符合SQL的规范，比如执行：
SQL> selet * from emp；
我们就可以看出由于Select关键字少了一个“c”，这条语句就无法通过语法检验的步骤了。
2、 语义检查。语法正确的SQL语句在解析的第二个步骤就是判断该SQL语句所访问的表及列是否准确？用户是否有权限访问或更改相应的表或列？比如如下语句：
SQL> select * from emp;
select * from emp
*
ERROR at line 1:
ORA-00942: table or view does not exist
由于查询用户没有可供访问的emp对象，因此该SQL语句无法通过语义检查。
3、检查共享池中是否有相同的语句存在。假如执行的SQL语句已经在共享池中存在同样的副本，那么该SQL语句将会被软解析，也就是可以重用已解析过的语句的执行计划和优化方案，可以忽略语句解析过程中最耗费资源的步骤，这也是我们为什么一直强调避免硬解析的原因。这个步骤又可以分为两个步骤：
（1）验证SQL语句是否完全一致。在这个步骤中，Oracle将会对传递进来的SQL语句使用HASH函数运算得出HASH值，再与共享池中现有语句的HASH值进行比较，看是否一一对应。
现有数据库中SQL语句的HASH值我们可以通过访问v$sql、v$sqlarea、v$sqltext等数据字典中的HASH_VALUE列查询得出。如果SQL语句的HASH值一致，那么ORACLE事实上还需要对SQL语句的语义进行再次检测，以决定是否一致。
那么为什么Oracle需要再次对语句文本进行检测呢？不是SQL语句的HASH值已经对应上了？事实上就算是SQL语句的HASH值已经对应上了，并不能说明这两条SQL语句就已经可以共享了。
我们首先参考如下一个例子：假如用户A有自己的一张表EMP，他要执行查询语句：select * from emp；用户B也有一张EMP表，同样要查询select * from emp；这样他们两条语句在文本上是一模一样的，他们的HASH值也会一样，但是由于涉及到查询的相关表不一样，他们事实上是无法共享的。假如这时候用户 C又要查询同样一条语句，他查询的表为scott下的公有同义词，还有就是SCOTT也查询同样一张自己的表emp，情况会是如何呢？
SQL> connect a/a
Connected.
SQL> create table emp ( x int );
Table created.
SQL> select * from emp;
no rows selected
SQL> connect b/b
Connected.
SQL> create table emp ( x int );
Table created.
SQL> select * from emp;
no rows selected
SQL> conn scott/tiger
Connected.
SQL> select * from emp;
SQL> conn c/c
Connected.
SQL> select * from emp;
SQL> conn/as sysdba
Connected.
SQL> select address,hash_value, executions, sql_text
2 from v$sql
3 where upper(sql_text) like 'SELECT * FROM EMP%'
4 /
ADDRESS HASH_VALUE EXECUTIONS SQL_TEXT
-------- ---------- ---------- ------------------------
78B89E9C 3011704998 1 select * from emp
78B89E9C 3011704998 1 select * from emp
78B89E9C 3011704998 2 select * from emp
我们可以看到这四个查询的语句文本和HASH值都是一样的，但是由于查询的对象不同，只有后面两个语句是可以共享的，不同情况的语句还是需要硬解析的。因此在检查共享池共同SQL语句的时候，是需要根据具体情况而定的。
我们可以进一步查询v$sql_shared_cursor以得知SQL为何不能共享的原因：
SQL> select kglhdpar, address,
2 auth_check_mismatch, translation_mismatch
3 from v$sql_shared_cursor
4 where kglhdpar in
5 ( select address
6 from v$sql
7 where upper(sql_text) like 'SELECT * FROM EMP%' )
8 /
KGLHDPAR ADDRESS A T
-------- -------- - -
78B89E9C 786C9D78 N N
78B89E9C 786AC810 Y Y
78B89E9C 786A11A4 Y Y
TRANSLATION_MISMATCH表示SQL游标涉及到的数据对象是不同的；AUTH_CHECK_MISMATCH表示对同样一条SQL语句转换是不匹配的。
（2）验证SQL语句执行环境是否相同。比如同样一条SQL语句，一个查询会话加了的HINT，另外一个用户加的HINT，
他们就会产生不同的执行计划，尽管他们是查询同样的数据。
我们下面就一个实例来说明SQL执行环境对解析的影响，我们通过将会话的 workarea_size_policy变更来查看对同样一条SQL语句执行的影响：
SQL> alter system flush shared_pool;
System altered.
SQL> show parameter workarea_size_policy
NAME
------------------------------------ ----------- --------------
workarea_size_policy
SQL> select count(*) from t;
COUNT(*)
----------
5736
SQL> alter session set workarea_size_policy=manual;
Session altered.
SQL> select count(*) from t;
COUNT(*)
----------
5736
SQL> select sql_text, child_number, hash_value, address
2 from v$sql
3 where upper(sql_text) = 'SELECT COUNT(*) FROM T'
4 /
SQL_TEXT
------------------------------ ------------ ---------- --------
select count(*) from t 0 2199322426 78717328
select count(*) from t 1 2199322426 78717328
可以看到由于不同会话workarea_size_policy设置得不同，即使是同样一条SQL语句还是无法共享的。通过进一步查询v$sql_shared_cursor我们可以发现两个会话的优化器环境是不同的：
SQL> select optimizer_mismatch
2 from v$sql_shared_cursor
3 where kglhdpar in
4 ( select address
5 from v$sql
6 where upper(sql_text) = 'SELECT COUNT(*) FROM T' );
O
-
N
Y
通过如上三个步骤检查以后，如果SQL语句是一致的，那么就会重用原有SQL语句的执行计划和优化方案，也就是我们通常所说的软解析。如果SQL语句没有找到同样的副本，那么就需要进行硬解析了。
4、Oracle根据提交的SQL语句再查询相应的数据对象是否有统计信息。如果有统计信息的话，那么CBO将会使用这些统计信息产生所有可能的执行计划（可能多达成千上万个）和相应的Cost，最终选择Cost最低的那个执行计划。如果查询的数据对象无统计信息，则按RBO的默认规则选择相应的执行计划。这个步骤也是解析中最耗费资源的，因此我们应该极力避免硬解析的产生。至此，解析的步骤已经全部完成，Oracle将会根据解析产生的执行计划执行SQL语句和提取相应的数据。
转自：http://blog.csdn.net/cnham/article/details/5999272
在oracle 中，对于一个提交的sql语句,存在两种可选的解析过程, 一种叫做硬解析,一种叫做软解析.
一个硬解析需要经解析,制定执行路径,优化访问计划等许多的步骤.硬解释不仅仅耗费大量的cpu，更重要的是会占据重要的门闩（latch）资源，严重的影响系统的规模的扩大（即限制了系统的并发行），而且引起的问题不能通过增加内存条和cpu的数量来解决。之所以这样是因为门闩是为了顺序访问以及修改一些内存区域而设置的，这些内存区域是不能被同时修改。当一个sql语句提交后，oracle会首先检查一下共享缓冲池（shared pool）里有没有与之完全相同的语句，如果有的话只须执行软分析即可，否则就得进行硬分析。
而唯一使得oracle 能够重复利用执行计划的方法就是采用绑定变量。绑定变量的实质就是用于替代sql语句中的常量的替代变量。绑定变量能够使得每次提交的sql语句都完全一样。
普通sql语句：
SELECT fname, lname, pcode FROM cust WHERE id = 674;
SELECT fname, lname, pcode FROM cust WHERE id = 234;
SELECT fname, lname, pcode FROM cust WHERE id = 332;
含绑定变量的sql 语句：
SELECT fname, lname, pcode FROM cust WHERE id = :cust_no;
Sql*plus 中使用绑定变量：
sql> variable x number;
sql> exec :x := 123;
sql> SELECT fname, lname, pcode FROM cust WHERE id =:x;
pl/sql
pl/sql很多时候都会自动绑定变量而无需编程人员操心，即很多你写得sql语句都会自动利用绑定变量，如下例所示：
create or replace procedure dsal(p_empno in number)
as
/
也许此时你会想要利用绑定变量来替代p_empno,但是这是完全没有必要的，因为在pl/sql中，引用变量即是引用绑定变量。
但是在pl/sql中动态sql并不是这样。
在vb，java以及其他应用程序中都得显式地利用绑定变量。
对于绑定变量的支持不仅仅限于oracle,其他RDBMS如SQL SERVER支持这一特性。
但并不是任何情况下都需要使用绑定变量，下面是两种例外情况：
１．对于隔相当一段时间才执行一次的sql语句，这时利用绑定变量的好处会被不能有效利用优化器而抵消
２．数据仓库的情况下。
********************************************************************************************************************************
经常可以遇到这样的情况，由于程序员的sql语句写的非常不合理，导致Oracle数据库查询速度缓慢，严重的情况会导致数据库的故障。
举个例子，我以前就遇到过Oracle数据库定期（一周左右）出现 ORA-4031错误（shared pool内存不够，无法连接Oracle数据库），此时数据库已经无法再使用了。
必须关闭重起，来释放那些占在shared pool里面无法释放的对象。
所以对于一个Oracle DBA来说，需要定期的检查shared pool中的sql语句占用的内存空间，对于严重占用shared pool，无法释放的sql语句，必须要求程序员修改或优化sql语句。
select sql_text ,sharable_mem from v$sql where sharable_mem > '100000' order by sharable_mem ;
上面的sql语句是查询shared pool中占用内存超过100K的sql语句。
这个sql可以非常有效的检查出Oracle shared pool中那些严重占用内存的sql，
根据我的经验，绝大多数有问题的sql语句都会在这里留下痕迹，通过在这里找出有问题的sql语句并进行修改，再反复运行这个sql脚本，直到所以有问题的sql都处理完毕，
这就是对Oracle数据库在sql上面的最好的优化，可以保证不会因为程序员的sql语句问题导致Oracle数据库的性能问题。
共享池主要由库缓冲区（共享SQL区和PL/SQL区）和数据字典缓冲区组成。
select * from v$sgastat; --显式SGA的状态信息。
有的人写的SQL语句非常复杂，嵌套了好几层，SQL语句本身写的很差，就有可能会占用很大的SQL区域。
这是我从Oracle Metalink里面摘下来的：
引用
If the SHARED_POOL_SIZE is large enough, most ORA-04031 errors are a result of
其实现实的很多情况是本来不需要那么复杂的sql的，由于程序员水平不够，写了那种很糟糕很复杂的sql，造成的数据库性能问题。
另外还有一个常识问题，却很容易被忽略。比如：
select * from table_name where id = 1;
select * from table_name where id = 2; 对于这种带参数的sql，id = ? 这个地方叫做占位符(Placeholder)。
拿PHP为例，很多人喜欢这样写代码
$sql = "select * from table_name where id = ";
$id=1;
$stmt = ociparse($conn,$sql.$id);;
ociexecute($stmt);;
......
$id = 2;
$stmt = ociparse($conn,$sql.$id);;
ociexecute($stmt);;
拿Java为例，是这样的：
Java代码
String sql = "select * from table_name where id = ";
Statement stmt = conn.createStatement();;
rset = stmt.executeQuery(sql "1");;
......
rset = stmt.executeQuery(sql "2");;
这种写法，对于Oracle数据库来说，完全就是两条不同的sql语句，
select * from table_name where id = 1;
select * from table_name where id = 2; 每次查询都要进行sql语句的执行解析，并且每个sql都会分配一个区域来存放sql解析后的二进制可执行代码。
试想，要是id不同的10万个sql呢？Oracle就会分配10万个sql区域来分别存放10万个这样的id不同的sql语句。对于一个数据库驱动的Web网站这样情况下，SGA开的再大，也会很快被耗尽share pool的，最后报一个ORA-4031错误。数据库就连接不上了，只好重起。
正确的写法应该是：
Java代码
$stmt = ociparse($conn,"select * from table_name where id = :id" );;
ocibindbyname($stmt,":id" ,&$id, 12 );;
$id =1 ;
ociexecute($stmt);;
...
$id = 2 ;
ociexecute($stmt);;
Java代码
PreparedStatement pstmt = conn.prepareStatement("select * from table_name where id = ?");;
pstmt.setInt(1,1);;
rset = pstmt.executeQuery();;
...
pstmt.setInt(1,2);;
rset = pstmt.executeQuery();;
这样Oracle数据库就知道你实际上用的都是同一条sql语句，会以这样的形式：
select * from table_name where id = :1
解析执行后存放在sql区域里面，当以后再有一样的sql的时候，把参数替换一下，就立刻执行，不需要再解析sql了。既加快了sql执行速度，也不会占有过多SGA的share pool。
可惜的是，很多程序员明知道这个问题，却意识不到问题的严重性，因为上面那种写法，编程的时候很灵活，sql语句可以动态构造，实现起来很容易，后面那种写法，sql语句是写死的，参数不能再变了，编程经常会非常麻烦。
很多数据库的性能问题都是这样造成的。
有兴趣在一个生产系统中，用上面sql检查一下，看看是否选择出来的有很多都是一样的sql语句，只是参数不同，如果是这样的话，就说明程序员的代码写的有问题。
呵呵，所以sun才从statement中再继承出来一个PreparedStatement.
************************************************************************************************************************************
我们都知道在Oracle中每条SQL语句在执行之前都需要经过解析，这里面又分为软解析和硬解析。那么这两种解析有何不同之处呢？它们又分别是如何进行解析呢？Oracle内部解析的步骤又是如何进行的呢？下面我们就这些话题进行共同探讨。
在Oracle中存在两种类型的SQL语句，一类为DDL语句，他们是从来不会共享使用的，也就是每次执行都需要进行硬解析。还有一类就是DML语句，他们会根据情况选择要么进行硬解析，要么进行软解析。
在Oracle 8i OCP教材的023中1-12有说明SQL语句的解析步骤，当一条SQL语句从客户端进程传递到服务器端进程后，需要执行如下步骤：
* 在共享池中搜索 SQL 语句的现有副本
* 验证 SQL 语句的语法是否准确
* 执行数据字典查找来验证表和列的定义
* 获取对象的分析锁以便在语句的分析过程中对象的定义不会改变
* 检查用户访问引用方案对象的权限
* 确定语句的最佳执行计划
* 将语句和执行计划载入共享的 SQL 区
这个先入为主的概念一直占据着我的脑海，我认为硬解析就是上面几个步骤。相对于硬解析，软解析的步骤就是上面第一步找到现有SQL语句的副本后，只需要验证用户是否有权限执行就是了，这样省略上面好几个步骤，相对硬解析来说性能开销就非常小了。即使是在论坛上和大家讨论时，我也一直坚持这个看法。直到前一天看了Tom的《Effective Oracle By Design》中关于语句处理的章节后，我才知道这个自己一直坚持的观点事实上是错误的。
事实上，在Oracle中SQL语句的解析步骤如下：
1、 语法检测。判断一条SQL语句的语法是否符合SQL的规范，比如执行：
SQL> selet * from emp；
我们就可以看出由于Select关键字少了一个“c”，这条语句就无法通过语法检验的步骤了。
2、 语义检查。语法正确的SQL语句在解析的第二个步骤就是判断该SQL语句所访问的表及列是否准确？用户是否有权限访问或更改相应的表或列？比如如下语句：
SQL> select * from emp;
select * from emp
*
ERROR at line 1:
ORA-00942: table or view does not exist
由于查询用户没有可供访问的emp对象，因此该SQL语句无法通过语义检查。
3、检查共享池中是否有相同的语句存在。假如执行的SQL语句已经在共享池中存在同样的副本，那么该SQL语句将会被软解析，也就是可以重用已解析过的语句的执行计划和优化方案，可以忽略语句解析过程中最耗费资源的步骤，这也是我们为什么一直强调避免硬解析的原因。这个步骤又可以分为两个步骤：
（1）验证SQL语句是否完全一致。在这个步骤中，Oracle将会对传递进来的SQL语句使用HASH函数运算得出HASH值，再与共享池中现有语句的HASH值进行比较，看是否一一对应。
现有数据库中SQL语句的HASH值我们可以通过访问v$sql、v$sqlarea、v$sqltext等数据字典中的HASH_VALUE列查询得出。如果SQL语句的HASH值一致，那么ORACLE事实上还需要对SQL语句的语义进行再次检测，以决定是否一致。
那么为什么Oracle需要再次对语句文本进行检测呢？不是SQL语句的HASH值已经对应上了？事实上就算是SQL语句的HASH值已经对应上了，并不能说明这两条SQL语句就已经可以共享了。
我们首先参考如下一个例子：假如用户A有自己的一张表EMP，他要执行查询语句：select * from emp；用户B也有一张EMP表，同样要查询select * from emp；这样他们两条语句在文本上是一模一样的，他们的HASH值也会一样，但是由于涉及到查询的相关表不一样，他们事实上是无法共享的。假如这时候用户 C又要查询同样一条语句，他查询的表为scott下的公有同义词，还有就是SCOTT也查询同样一张自己的表emp，情况会是如何呢？
SQL> connect a/a
Connected.
SQL> create table emp ( x int );
Table created.
SQL> select * from emp;
no rows selected
SQL> connect b/b
Connected.
SQL> create table emp ( x int );
Table created.
SQL> select * from emp;
no rows selected
SQL> conn scott/tiger
Connected.
SQL> select * from emp;
SQL> conn c/c
Connected.
SQL> select * from emp;
SQL> conn/as sysdba
Connected.
SQL> select address,hash_value, executions, sql_text
2 from v$sql
3 where upper(sql_text) like 'SELECT * FROM EMP%'
4 /
ADDRESS HASH_VALUE EXECUTIONS SQL_TEXT
-------- ---------- ---------- ------------------------
78B89E9C 3011704998 1 select * from emp
78B89E9C 3011704998 1 select * from emp
78B89E9C 3011704998 2 select * from emp
我们可以看到这四个查询的语句文本和HASH值都是一样的，但是由于查询的对象不同，只有后面两个语句是可以共享的，不同情况的语句还是需要硬解析的。因此在检查共享池共同SQL语句的时候，是需要根据具体情况而定的。
我们可以进一步查询v$sql_shared_cursor以得知SQL为何不能共享的原因：
SQL> select kglhdpar, address,
2 auth_check_mismatch, translation_mismatch
3 from v$sql_shared_cursor
4 where kglhdpar in
5 ( select address
6 from v$sql
7 where upper(sql_text) like 'SELECT * FROM EMP%' )
8 /
KGLHDPAR ADDRESS A T
-------- -------- - -
78B89E9C 786C9D78 N N
78B89E9C 786AC810 Y Y
78B89E9C 786A11A4 Y Y
TRANSLATION_MISMATCH表示SQL游标涉及到的数据对象是不同的；AUTH_CHECK_MISMATCH表示对同样一条SQL语句转换是不匹配的。
（2）验证SQL语句执行环境是否相同。比如同样一条SQL语句，一个查询会话加了的HINT，另外一个用户加的HINT，
他们就会产生不同的执行计划，尽管他们是查询同样的数据。
我们下面就一个实例来说明SQL执行环境对解析的影响，我们通过将会话的 workarea_size_policy变更来查看对同样一条SQL语句执行的影响：
SQL> alter system flush shared_pool;
System altered.
SQL> show parameter workarea_size_policy
NAME
------------------------------------ ----------- --------------
workarea_size_policy
SQL> select count(*) from t;
COUNT(*)
----------
5736
SQL> alter session set workarea_size_policy=manual;
Session altered.
SQL> select count(*) from t;
COUNT(*)
----------
5736
SQL> select sql_text, child_number, hash_value, address
2 from v$sql
3 where upper(sql_text) = 'SELECT COUNT(*) FROM T'
4 /
SQL_TEXT
------------------------------ ------------ ---------- --------
select count(*) from t 0 2199322426 78717328
select count(*) from t 1 2199322426 78717328
可以看到由于不同会话workarea_size_policy设置得不同，即使是同样一条SQL语句还是无法共享的。通过进一步查询v$sql_shared_cursor我们可以发现两个会话的优化器环境是不同的：
SQL> select optimizer_mismatch
2 from v$sql_shared_cursor
3 where kglhdpar in
4 ( select address
5 from v$sql
6 where upper(sql_text) = 'SELECT COUNT(*) FROM T' );
O
-
N
Y
通过如上三个步骤检查以后，如果SQL语句是一致的，那么就会重用原有SQL语句的执行计划和优化方案，也就是我们通常所说的软解析。如果SQL语句没有找到同样的副本，那么就需要进行硬解析了。
4、Oracle根据提交的SQL语句再查询相应的数据对象是否有统计信息。如果有统计信息的话，那么CBO将会使用这些统计信息产生所有可能的执行计划（可能多达成千上万个）和相应的Cost，最终选择Cost最低的那个执行计划。如果查询的数据对象无统计信息，则按RBO的默认规则选择相应的执行计划。这个步骤也是解析中最耗费资源的，因此我们应该极力避免硬解析的产生。至此，解析的步骤已经全部完成，Oracle将会根据解析产生的执行计划执行SQL语句和提取相应的数据。
转自：http://blog.csdn.net/cnham/article/details/5999272
1168
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
