oracle性能调优之--Oracle 硬解析与软解析

Oracle硬解析与软解析是我们经常遇到的问题，什么情况会产生硬解析，什么情况产生软解析，又当如何避免硬解析？下面的描述将给出

软硬解析的产生，以及硬解析的弊端和如何避免硬解析的产生。

一、SQL语句的执行过程

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

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

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

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

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

d.对于存在相同哈希值的新命令行，其文本将与已存在的命令行的文本逐个进行比较。这些比较包括大小写，字符串是否一致，空格，注释

等，如果一致，则对其进行软解析，转到步骤f。否则到d步骤。

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

f.执行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资源。在此不

得不提的是对库缓存中闩的使用。闩是锁的细化，可以理解为是一种轻量级的串行化设备。当进程申请到闩后，则这些闩用于保护共享内存

的数在同一时刻不会被两个以上的进程修改。在硬解析时，需要申请闩的使用，而闩的数量在有限的情况下需要等待。大量的闩的使用由此

造成需要使用闩的进程排队越频繁，性能则逾低下。

四、硬解析的演示

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

在两个不同的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=7369;

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>show parametercursor_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"

2wheresql_textlike'select * from dept where deptno%';

SQL_TEXTCHILD_NUMBER

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

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

2wheresql_textlike'select * from dept where deptno%';

SQL_TEXTCHILD_NUMBER

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

select*fromdeptwheredeptno=500

select*fromdeptwheredeptno=400

select*fromdeptwheredeptno=:"SYS_B_0"0

注意当该参数设置为similar，会产生不利的影响，可以参考这里：cursor_sharing参数对于expdp的性能影响

2.使用绑定变量

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

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

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

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

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

和重用这个查询计划。

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

select*fromempwhereempno=:eno;

select*fromempwhereempno=:emp_no

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

使用绑定变量的例子(参照了TOM大师的Oracle 9i&10g编程艺术)

scott@ASMDB>createtabletb_test(colint);--创建表tb_test

scott@ASMDB>createorreplaceprocedureproc1--创建存储过程proc1使用绑定变量来插入新记录

2as

3begin

4foriin1..10000

5loop

6executeimmediate'insert into tb_test values(:n)'using i;

7endloop;

8end;

9/

Procedurecreated.

scott@ASMDB>createorreplaceprocedureproc2--创建存储过程proc2，未使用绑定变量，因此每一个SQL插入语句都会硬解析

2as

3begin

4foriin1..10000

5loop

6executeimmediate'insert into tb_test values('||i||')';

7endloop;

8end;

9/

Procedurecreated.

scott@ASMDB>execrunstats_pkg.rs_start

PL/SQLproceduresuccessfully completed.

scott@ASMDB>execproc1;

PL/SQLproceduresuccessfully completed.

scott@ASMDB>execrunstats_pkg.rs_middle;

PL/SQLproceduresuccessfully completed.

scott@ASMDB>execproc2;

PL/SQLproceduresuccessfully completed.

scott@ASMDB>execrunstats_pkg.rs_stop(1000);

Run1ranin1769 hsecs

Run2ranin12243hsecs--run2运行的时间是run1的/1769≈倍

run1 ranin14.45%ofthetime

NameRun1Run2Diff

LATCH.SQL memory managerworka4102,6942,284

LATCH.sessionallocation5328,9128,380

LATCH.simulator lrulatch339,3719,338

LATCH.simulator hashlatch519,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...consistent getsfromca24710,35310,106

STAT...recursivecalls10,47420,88510,411

STAT...db block getsfromcach10,40830,37119,963

STAT...db blockgets10,40830,37119,963

LATCH.enqueues32221,82021,498--闩的队列数比较

LATCH.enqueue hashchains35121,90421,553

STAT...session logical reads10,65540,72430,069

LATCH.library cachepin40,34872,41032,062--库缓存pin

LATCH.kksstats840,06140,053

LATCH.library cachelock31861,29460,976

LATCH.cache bufferschains51,851118,34066,489

LATCH.row cacheobjects351123,512123,161

LATCH.librarycache40,710234,653193,943

LATCH.sharedpool20,357243,376223,019

Run1latches total versus runs--difference and pct

Run1Run2DiffPct

157,159974,086816,92716.13%--proc2使用闩的数量也远远多于proc1，其比值是.13%

PL/SQLproceduresuccessfully completed.

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

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

绑定变量的优点

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

绑定变量的缺点

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

六、总结

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

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

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