--=======================
-- Oracle 硬解析与软解析
--=======================
Oracle 硬解析与软解析是我们经常遇到的问题，什么情况会产生硬解析，什么情况产生软解析，又当如何避免硬解析？下面的描述将给出
软硬解析的产生，以及硬解析的弊端和如何避免硬解析的产生。
一、SQL语句的执行过程
当发布一条SQL或PL/SQL命令时，Oracle会自动寻找该命令是否存在于共享池中来决定对当前的语句使用硬解析或软解析。
通常情况下，SQL语句的执行过程如下：
a.SQL代码的语法(语法的正确性)及语义检查(对象的存在性与权限)。
b.将SQL代码的文本进行哈希得到哈希值。
c.如果共享池中存在相同的哈希值，则对这个命令进一步判断是否进行软解析，否则到e步骤。
d.对于存在相同哈希值的新命令行，其文本将与已存在的命令行的文本逐个进行比较。这些比较包括大小写，字符串是否一致，空格，注释
等，如果一致，则对其进行软解析，转到步骤f。否则到d步骤。红色字体描述有误应该转到步骤e(更正@20130905，谢网友keaihuilang指出)
e.硬解析，生成执行计划。
f.执行SQL代码，返回结果。
二、不能使用软解析的情形
1.下面的三个查询语句，不能使用相同的共享SQL区。尽管查询的表对象使用了大小写，但Oracle为其生成了不同的执行计划
select * from emp;
select * from Emp;
select * from EMP;
2.类似的情况，下面的查询中，尽管其where子句empno的值不同，Oracle同样为其生成了不同的执行计划
select * from emp where empno=7369
select * from emp where empno=7788
3.在判断是否使用硬解析时，所参照的对象及schema应该是相同的，如果对象相同，而schema不同，则需要使用硬解析,生成不同的执行计划
sys@ASMDB> select owner,table_name from dba_tables where table_name like 'TB_OBJ%';
OWNER TABLE_NAME
------------------------------ ------------------------------
USR1 TB_OBJ --两个对象的名字相同，当所有者不同
SCOTT TB_OBJ
usr1@ASMDB> select * from tb_obj;
scott@ASMDB> select * from tb_obj; --此时两者都需要使用硬解析以及走不同的执行计划
三、硬解析的弊端
硬解析即整个SQL语句的执行需要完完全全的解析，生成执行计划。而硬解析，生成执行计划需要耗用CPU资源，以及SGA资源。在此不
得不提的是对库缓存中闩的使用。闩是锁的细化，可以理解为是一种轻量级的串行化设备。当进程申请到闩后，则这些闩用于保护共享内存
的数在同一时刻不会被两个以上的进程修改。在硬解析时，需要申请闩的使用，而闩的数量在有限的情况下需要等待。大量的闩的使用由此
造成需要使用闩的进程排队越频繁，性能则逾低下。
四、硬解析的演示
下面对上面的两种情形进行演示
在两个不同的session中完成，一个为sys帐户的session，一个为scott账户的session，不同的session，其SQL命令行以不同的帐户名开头
如" sys@ASMDB> " 表示使用时sys帐户的session，" scott@ASMDB> "表示scott帐户的session
sys@ASMDB> select name,class,value from v$sysstat where statistic#=331;
NAME CLASS VALUE
-------------------- ---------- ---------- --当前的硬解析值为569
parse count (hard) 64 569
scott@ASMDB> select * from emp;
sys@ASMDB> select name,class,value from v$sysstat where statistic#=331;
NAME CLASS VALUE
-------------------- ---------- ---------- --执行上一个查询后硬解析值为570，解析次数增加了一次
parse count (hard) 64 570
scott@ASMDB> select * from Emp;
sys@ASMDB> select name,class,value from v$sysstat where statistic#=331;
NAME CLASS VALUE
-------------------- ---------- ---------- --执行上一个查询后硬解析值为571
parse count (hard) 64 571
scott@ASMDB> select * from EMP;
sys@ASMDB> select name,class,value from v$sysstat where statistic#=331;
NAME CLASS VALUE
-------------------- ---------- ---------- --执行上一个查询后硬解析值为572
parse count (hard) 64 572
scott@ASMDB> select * from emp where empno=7369;
sys@ASMDB> select name,class,value from v$sysstat where statistic#=331;
NAME CLASS VALUE
-------------------- ---------- ---------- --执行上一个查询后硬解析值为573
parse count (hard) 64 573
scott@ASMDB> select * from emp where empno=7788; --此处原来empno=7369，复制错误所致，现已更正为7788@20130905
sys@ASMDB> select name,class,value from v$sysstat where statistic#=331;
NAME CLASS VALUE
-------------------- ---------- ---------- --执行上一个查询后硬解析值为574
parse count (hard) 64 574
从上面的示例中可以看出，尽管执行的语句存在细微的差别，但Oracle还是为其进行了硬解析，生成了不同的执行计划。即便是同样的SQL
语句，而两条语句中空格的多少不一样，Oracle同样会进行硬解析。
五、编码硬解析的改进方法
1.更改参数cursor_sharing
参数cursor_sharing决定了何种类型的SQL能够使用相同的SQL area
CURSOR_SHARING = { SIMILAR | EXACT | FORCE }
EXACT --只有当发布的SQL语句与缓存中的语句完全相同时才用已有的执行计划。
FORCE --如果SQL语句是字面量，则迫使Optimizer始终使用已有的执行计划,无论已有的执行计划是不是最佳的。
SIMILAR --如果SQL语句是字面量，则只有当已有的执行计划是最佳时才使用它，如果已有执行计划不是最佳则重新对这个SQL
--语句进行分析来制定最佳执行计划。
可以基于不同的级别来设定该参数，如ALTER SESSION, ALTER SYSTEM
sys@ASMDB> show parameter cursor_shar --查看参数cursor_sharing
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string EXACT
sys@ASMDB> alter system set cursor_sharing='similar'; --将参数cursor_sharing的值更改为similar
sys@ASMDB> select name,class,value from v$sysstat where statistic#=331;
NAME CLASS VALUE
-------------------- ---------- ---------- --当前硬解析的值为865
parse count (hard) 64 865
scott@ASMDB> select * from dept where deptno=10;
sys@ASMDB> select name,class,value from v$sysstat where statistic#=331;
NAME CLASS VALUE
-------------------- ---------- ---------- --执行上一条SQL查询后，硬解析的值变为866
parse count (hard) 64 866
scott@ASMDB> select * from dept where deptno=20;
sys@ASMDB> select name,class,value from v$sysstat where statistic#=331;
NAME CLASS VALUE
-------------------- ---------- ---------- --执行上一条SQL查询后，硬解析的值没有发生变化还是866
parse count (hard) 64 866
sys@ASMDB> select sql_text,child_number from v$sql -- 在下面的结果中可以看到SQL_TEXT列中使用了绑定变量:"SYS_B_0"
2 where sql_text like 'select * from dept where deptno%';
SQL_TEXT CHILD_NUMBER
-------------------------------------------------- ------------
select * from dept where deptno=:"SYS_B_0" 0
sys@ASMDB> alter system set cursor_sharing='exact'; --将cursor_sharing改回为exact
--接下来在scott的session 中执行deptno=40 和的查询后再查看sql_text，当cursor_sharing改为exact后，每执行那个一次
--也会在v$sql中增加一条语句
sys@ASMDB> select sql_text,child_number from v$sql
2 where sql_text like 'select * from dept where deptno%';
SQL_TEXT CHILD_NUMBER
-------------------------------------------------- ------------
select * from dept where deptno=50 0
select * from dept where deptno=40 0
select * from dept where deptno=:"SYS_B_0" 0
1708
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
