--=======================
-- Oracle 硬解析与软解析
--=======================
Oracle 硬解析与软解析是我们经常遇到的问题，什么情况会产生硬解析，什么情况产生软解析，又当如何避免硬解析？下面的描述将给出
软硬解析的产生，以及硬解析的弊端和如何避免硬解析的产生。
一、 SQL 语句的执行过程
当发布一条 SQL 或 PL / SQL 命令时， Oracle 会自动寻找该命令是否存在于共享池中来决定对当前的语句使用硬解析或软解析。
通常情况下， SQL 语句的执行过程如下：
a . SQL 代码的语法 ( 语法的正确性 ) 及语义检查 ( 对象的存在性与权限 ) 。
b . 将 SQL 代码的文本进行哈希得到哈希值。
c . 如果共享池中存在相同的哈希值，则对这个命令进一步判断是否进行软解析，否则到 e 步骤。
d . 对于存在相同哈希值的新命令行，其文本将与已存在的命令行的文本逐个进行比较。这些比较包括大小写，字符串是否一致，空格，注释
等，如果一致，则对其进行软解析，转到步骤 f 。否则到 d 步骤。
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 = 7369 ;
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
注意当该参数设置为 similar ，会产生不利的影响，可以参考这里： cursor_sharing 参 数 对 于expdp 的 性 能 影 响
2. 使用绑定变量
绑定变量要求变量名称，数据类型以及长度是一致，否则无法使用软解析
绑定变量（ bind variable ）是指在 DML 语句中使用一个占位符，即使用冒号后面紧跟变量名的形式，如下
select * from emp where empno = 7788 -- 未使用绑定变量
select * from emp where empono =: eno --:eno 即为绑定变量
在第二个查询中，变量值在查询执行时被提供。该查询只编译一次，随后会把查询计划存储在一个共享池（库缓存）中，以便以后获取
和重用这个查询计划。
下面使用了绑定变量，但两个变量其实质是不相同的，对这种情形，同样使用硬解析
select * from emp where empno =: eno ;
select * from emp where empno =: emp_no
使用绑定变量时要求不同的会话中使用了相同的回话环境，以及优化器的规则等。
使用绑定变量的例子 ( 参照了 TOM 大师的 Oracle 9i & 10g 编程艺术 )
scott@ASMDB > create table tb_test ( col int ); -- 创建表 tb_test
scott@ASMDB > create or replace procedure proc1 -- 创建存储过程 proc1 使用绑定变量来插入新记录
2 as
3 begin
4 for i in 1..10000
5 loop
6 execute immediate 'insert into tb_test values(:n)' using i ;
7 end loop ;
8 end ;
9 /
Procedure created .
scott@ASMDB > create or replace procedure proc2 -- 创建存储过程 proc2 ，未使用绑定变量，因此每一个 SQL 插入语句都会硬解析
2 as
3 begin
4 for i in 1..10000
5 loop
6 execute immediate 'insert into tb_test values(' || i|| ')' ;
7 end loop ;
8 end ;
9 /
Procedure created .
scott@ASMDB > exec runstats_pkg . rs_start
PL / SQL procedure successfully completed .
scott@ASMDB > exec proc1 ;
PL / SQL procedure successfully completed .
scott@ASMDB > exec runstats_pkg . rs_middle ;
PL / SQL procedure successfully completed .
scott@ASMDB > exec proc2 ;
PL / SQL procedure successfully completed .
scott@ASMDB > exec runstats_pkg . rs_stop ( 1000 );
Run1 ran in 1769 hsecs
Run2 ran in 12243 hsecs --run2 运行的时间是 run1 的 /1769≈ 倍
run 1 ran in 14.45 % of the time
Name Run1 Run2 Diff
LATCH . SQL memory manager worka 410 2 , 694 2 , 284
LATCH . session allocation 532 8 , 912 8 , 380
LATCH . simulator lru latch 33 9 , 371 9 , 338
LATCH . simulator hash latch 51 9 , 398 9 , 347
STAT ... enqueue requests 31 10 , 030 9 , 999
STAT ... enqueue releases 29 10 , 030 10 , 001
STAT ... parse count ( hard ) 4 10 , 011 10 , 007 -- 硬解析的次数，前者只有四次
STAT ... calls to get snapshot s 55 10 , 087 10 , 032
STAT ... parse count ( total ) 33 10 , 067 10 , 034
STAT ... consistent gets 247 10 , 353 10 , 106
STAT ... consistent gets from ca 247 10 , 353 10 , 106
STAT ... recursive calls 10 , 474 20 , 885 10 , 411
STAT ... db block gets from cach 10 , 408 30 , 371 19 , 963
STAT ... db block gets 10 , 408 30 , 371 19 , 963
LATCH . enqueues 322 21 , 820 21 , 498 -- 闩的队列数比较
LATCH . enqueue hash chains 351 21 , 904 21 , 553
STAT ... session logical reads 10 , 655 40 , 724 30 , 069
LATCH . library cache pin 40 , 348 72 , 410 32 , 062 -- 库缓存 pin
LATCH . kks stats 8 40 , 061 40 , 053
LATCH . library cache lock 318 61 , 294 60 , 976
LATCH . cache buffers chains 51 , 851 118 , 340 66 , 489
LATCH . row cache objects 351 123 , 512 123 , 161
LATCH . library cache 40 , 710 234 , 653 193 , 943
LATCH . shared pool 20 , 357 243 , 376 223 , 019
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
157 , 159 974 , 086 816 , 927 16.13 % --proc2 使用闩的数量也远远多于 proc1 ，其比值是 .13%
PL / SQL procedure successfully completed .
由上面的示例可知，在未使用绑定变量的情形下，不论是解析次数，闩使用的数量，队列，分配的内存，库缓存，行缓存远远高于绑定
变量的情况。因此尽可能的使用绑定变量避免硬解析产生所需的额外的系统资源。
绑定变量的优点
减少 SQL 语句的硬解析，从而减少因硬解析产生的额外开销 ( CPU , Shared pool , latch ) 。其次提高编程效率，减少数据库的访问次数。
绑定变量的缺点
优化器就会忽略直方图的信息，在生成执行计划的时候可能不够优化。 SQL 优化相对比较困难
六、总结
1. 尽可能的避免硬解析，因为硬解析需要更多的 CPU 资源，闩等。
2.cursor_sharing 参数应权衡利弊，需要考虑使用 similar 与 force 带来的影响。
3. 尽可能的使用绑定变量来避免硬解析。
七、更多参考
有关闪回特性请参考
Oracle 闪回特性(FLASHBACK DATABASE)
Oracle 闪回特性(FLASHBACK DROP & RECYCLEBIN)
Oracle 闪回特性(Flashback Query 、Flashback Table)
Oracle 闪回特性(Flashback Version 、Flashback Transaction)
有关基于用户管理的备份和备份恢复的概念请参考：
Oracle 基于用户管理恢复的处理 ( 详细描述了介质恢复及其处理 )
有关 RMAN 的恢复与管理请参考：
有关 Oracle 体系结构请参考：
Oracle 实例和Oracle 数据库(Oracle 体系结构)
Oracle 联机重做日志文件(ONLINE LOG FILE)
