Oracle硬解析和软解析的区别分析

一、摘要

Oracle硬解析和软解析是我们经常遇到的问题，所以需要考虑何时产生软解析何时产生硬解析，如何判断

SQL的执行过程

当发布一条SQL或PL/SQL命令时，Oracle会自动寻找该命令是否存在于共享池中来决定对当前的语句使用硬解析或软解析。

通常情况下，SQL语句的执行过程如下：

Step1.SQL代码的语法(语法的正确性)及语义检查(对象的存在性与权限)。

Step2.将SQL代码的文本进行哈希得到哈希值。

Step3.如果共享池中存在相同的哈希值，则对这个命令进一步判断是否进行软解析，否则到e步骤。

Step4.对于存在相同哈希值的新命令行，其文本将与已存在的命令行的文本逐个进行比较。

   这些比较包括大小写，字符串是否一致，空格，注释等，如果一致，则对其进行软解析，转到步骤Step6，无需再次硬解析。

   否则到步骤Step5。

Step5.硬解析，生成执行计划。

Step6.执行SQL代码，返回结果。

二、软解析

1.下面的三个查询语句，不能使用相同的共享SQL区。尽管查询的表对象使用了大小写，但Oracle为其生成了不同的执行计划

select*fromemp;

select*fromEmp;

select*fromEMP;

2.类似的情况，下面的查询中，尽管其where子句empno的值不同，Oracle同样为其生成了不同的执行计划      

select*fromempwhereempno=7369

select*fromempwhereempno=7788

3.在判断是否使用硬解析时，所参照的对象及schema应该是相同的，如果对象相同，而schema不同，则需要使用硬解析,生成不同的执行计划

sys@ASMDB>selectowner,table_namefromdba_tableswheretable_namelike"TB_OBJ%";
OWNERTABLE_NAME
------------------------------------------------------------
USR1TB_OBJ--两个对象的名字相同，当所有者不同
SCOTTTB_OBJ
usr1@ASMDB>select*fromtb_obj;
scott@ASMDB>select*fromtb_obj;--此时两者都需要使用硬解析以及走不同的执行计划

三、硬解析

硬解析即整个SQL语句的执行需要完完全全的解析，生成执行计划。而硬解析，生成执行计划需要耗用CPU资源，以及SGA资源。在此不得不提的是对库缓存中闩的使用。闩是锁的细化，可以理解为是一种轻量级的串行化设备。当进程申请到闩后，则这些闩用于保护共享内存的数在同一时刻不会被两个以上的进程修改。在硬解析时，需要申请闩的使用，而闩的数量在有限的情况下需要等待。大量的闩的使用由此造成需要使用闩的进程排队越频繁，性能则逾低下。

1.下面对上面的两种情形进行演示

在两个不同的session中完成，一个为sys帐户的session，一个为scott账户的session，不同的session，其SQL命令行以不同的帐户名开头

如"sys@ASMDB>" 表示使用时sys帐户的session，"scott@ASMDB>"表示scott帐户的session

sys@ASMDB>selectname,class,valuefromv$sysstatwherestatistic#=331;
NAMECLASSVALUE
------------------------------------------当前的硬解析值为569
parsecount(hard)64569
scott@ASMDB>select*fromemp;
sys@ASMDB>selectname,class,valuefromv$sysstatwherestatistic#=331;
NAMECLASSVALUE
------------------------------------------执行上一个查询后硬解析值为570，解析次数增加了一次
parsecount(hard)64570
scott@ASMDB>select*fromEmp;
sys@ASMDB>selectname,class,valuefromv$sysstatwherestatistic#=331;
NAMECLASSVALUE
------------------------------------------执行上一个查询后硬解析值为571
parsecount(hard)64571
scott@ASMDB>select*fromEMP;
sys@ASMDB>selectname,class,valuefromv$sysstatwherestatistic#=331;
NAMECLASSVALUE
------------------------------------------执行上一个查询后硬解析值为572
parsecount(hard)64572
scott@ASMDB>select*fromempwhereempno=7369;
sys@ASMDB>selectname,class,valuefromv$sysstatwherestatistic#=331;
NAMECLASSVALUE
------------------------------------------执行上一个查询后硬解析值为573
parsecount(hard)64573
scott@ASMDB>select*fromempwhereempno=7788;--此处原来empno=7369，复制错误所致，现已更正为7788@20130905
sys@ASMDB>selectname,class,valuefromv$sysstatwherestatistic#=331;
NAMECLASSVALUE
------------------------------------------执行上一个查询后硬解析值为574
parsecount(hard)64574

从上面的示例中可以看出，尽管执行的语句存在细微的差别，但Oracle还是为其进行了硬解析，生成了不同的执行计划。即便是同样的SQL语句，而两条语句中空格的多少不一样，Oracle同样会进行硬解析。

四、硬解析改进-使用动态语句

1.更改参数cursor_sharing

       参数cursor_sharing决定了何种类型的SQL能够使用相同的SQLarea

       CURSOR_SHARING={SIMILAR|EXACT|FORCE}   

           EXACT     --只有当发布的SQL语句与缓存中的语句完全相同时才用已有的执行计划。

           FORCE     --如果SQL语句是字面量，则迫使Optimizer始终使用已有的执行计划,无论已有的执行计划是不是最佳的。

           SIMILAR  --如果SQL语句是字面量，则只有当已有的执行计划是最佳时才使用它，如果已有执行计划不是最佳则重新对这个SQL

                           --语句进行分析来制定最佳执行计划。

       可以基于不同的级别来设定该参数，如ALTERSESSION,ALTERSYSTEM

sys@ASMDB>showparametercursor_shar--查看参数cursor_sharing
NAMETYPEVALUE
-----------------------------------------------------------------------------
cursor_sharingstringEXACT
sys@ASMDB>altersystemsetcursor_sharing="similar";--将参数cursor_sharing的值更改为similar
sys@ASMDB>selectname,class,valuefromv$sysstatwherestatistic#=331;
NAMECLASSVALUE
------------------------------------------当前硬解析的值为865
parsecount(hard)64865
scott@ASMDB>select*fromdeptwheredeptno=10;
sys@ASMDB>selectname,class,valuefromv$sysstatwherestatistic#=331;
NAMECLASSVALUE
------------------------------------------执行上一条SQL查询后，硬解析的值变为866
parsecount(hard)64866
scott@ASMDB>select*fromdeptwheredeptno=20;
sys@ASMDB>selectname,class,valuefromv$sysstatwherestatistic#=331;
NAMECLASSVALUE
------------------------------------------执行上一条SQL查询后，硬解析的值没有发生变化还是866
parsecount(hard)64866
sys@ASMDB>selectsql_text,child_numberfromv$sql--在下面的结果中可以看到SQL_TEXT列中使用了绑定变量:"SYS_B_0"
wheresql_textlike"select*fromdeptwheredeptno%";
SQL_TEXTCHILD_NUMBE
--------------------------------------------------------------
select*fromdeptwheredeptno=:"SYS_B_0"0
sys@ASMDB>altersystemsetcursor_sharing="exact";--将cursor_sharing改回为exact
--接下来在scott的session中执行deptno=40和的查询后再查看sql_text，当cursor_sharing改为exact后，每执行那个一次
--也会在v$sql中增加一条语句
sys@ASMDB>selectsql_text,child_numberfromv$sql
wheresql_textlike"select*fromdeptwheredeptno%";
SQL_TEXTCHILD_NUMBER
--------------------------------------------------------------
select*fromdeptwheredeptno=500
select*fromdeptwheredeptno=400
select*fromdeptwheredeptno=:"SYS_B_0"0

绑定变量要求变量名称，数据类型以及长度是一致，否则无法使用软解析

(1).绑定变量（bindvariable）是指在DML语句中使用一个占位符，即使用冒号后面紧跟变量名的形式，如下

           select*fromempwhereempno=7788   --未使用绑定变量

           select*fromempwhereempono=:eno  --:eno即为绑定变量

           在第二个查询中，变量值在查询执行时被提供。该查询只编译一次，随后会把查询计划存储在一个共享池（库缓存）中，以便以后获取和重用这个查询计划。

(2).下面使用了绑定变量，但两个变量其实质是不相同的，对这种情形，同样使用硬解析

           select*fromempwhereempno=:eno;

           select*fromempwhereempno=:emp_no

          使用绑定变量时要求不同的会话中使用了相同的回话环境，以及优化器的规则等

scott@ASMDB>createtabletb_test(colint);--创建表tb_test
scott@ASMDB>createorreplaceprocedureproc1--创建存储过程proc1使用绑定变量来插入新记录
as
begin
foriin1..10000
loop
executeimmediate"insertintotb_testvalues(:n)"usingi;
endloop;
end;
/
Procedurecreated.
scott@ASMDB>createorreplaceprocedureproc2--创建存储过程proc2，未使用绑定变量，因此每一个SQL插入语句都会硬解析
as
begin
foriin1..10000
loop
executeimmediate"insertintotb_testvalues("||i||")";
endloop;
end;
/
Procedurecreated.
scott@ASMDB>execrunstats_pkg.rs_start
PL/SQLproceduresuccessfullycompleted.
scott@ASMDB>execproc1;
PL/SQLproceduresuccessfullycompleted.
scott@ASMDB>execrunstats_pkg.rs_middle;
PL/SQLproceduresuccessfullycompleted.
scott@ASMDB>execproc2;
PL/SQLproceduresuccessfullycompleted.
scott@ASMDB>execrunstats_pkg.rs_stop(1000);
Run1ranin1769hsecs
Run2ranin12243hsecs--run2运行的时间是run1的/1769≈倍
run1ranin14.45%ofthetime
NameRun1Run2Diff
LATCH.SQLmemorymanagerworka4102,6942,284
LATCH.sessionallocation5328,9128,380
LATCH.simulatorlrulatch339,3719,338
LATCH.simulatorhashlatch519,3989,347
STAT...enqueuerequests3110,0309,999
STAT...enqueuereleases2910,03010,001
STAT...parsecount(hard)410,01110,007--硬解析的次数，前者只有四次
STAT...callstogetsnapshots5510,08710,032
STAT...parsecount(total)3310,06710,034
STAT...consistentgets24710,35310,106
STAT...consistentgetsfromca24710,35310,106
STAT...recursivecalls10,47420,88510,411
STAT...dbblockgetsfromcach10,40830,37119,963
STAT...dbblockgets10,40830,37119,963
LATCH.enqueues32221,82021,498--闩的队列数比较
LATCH.enqueuehashchains35121,90421,553
STAT...sessionlogicalreads10,65540,72430,069
LATCH.librarycachepin40,34872,41032,062--库缓存pin
LATCH.kksstats840,06140,053
LATCH.librarycachelock31861,29460,976
LATCH.cachebufferschains51,851118,34066,489
LATCH.rowcacheobjects351123,512123,161
LATCH.librarycache40,710234,653193,943
LATCH.sharedpool20,357243,376223,019
Run1latchestotalversusruns--differenceandpct
Run1Run2DiffPct
157,159974,086816,92716.13%--proc2使用闩的数量也远远多于proc1，其比值是.13%
PL/SQLproceduresuccessfullycompleted.

 (3).使用绑定变量的好处

由上面的示例可知，在未使用绑定变量的情形下，不论是解析次数，闩使用的数量，队列，分配的内存，库缓存，行缓存远远高于绑定

变量的情况。因此尽可能的使用绑定变量避免硬解析产生所需的额外的系统资源。

绑定变量的优点

减少SQL语句的硬解析，从而减少因硬解析产生的额外开销(CPU,Sharedpool,latch)。其次提高编程效率，减少数据库的访问次数。

绑定变量的缺点

 优化器就会忽略直方图的信息，在生成执行计划的时候可能不够优化。SQL优化相对比较困难

五、总结

 1.尽可能的避免硬解析，因为硬解析需要更多的CPU资源，闩等。

 2.cursor_sharing参数应权衡利弊，需要考虑使用similar与force带来的影响。

 3.尽可能的使用绑定变量来避免硬解析。