Oracle-Soft Parse/Hard Parse/Soft Soft Parse解读

概述

在Oracle中存在两种类型的SQL语句:
一类为 DDL语句(数据定义语言)CREATE,DROP,ALTER，他们是从来不会共享使用的，也就是每次执行都需要进行硬解析。
一类就是DML语句(数据操纵语言)INSERT,UPDATE,DELETE,SELECT，他们会根据情况选择要么进行硬解析，要么进行软解析。

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

---

解析过程

硬/软解析过程

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

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

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

• d.对于存在相同哈希值的新命令行，其文本将与已存在的命令行的文本逐个进行比较。这些比较包括大小写，字符串是否一致，空格，注释等，如果一致，则对其进行软解析，转到步骤f.否则到d步骤。

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

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

---

软软解析过程

要完全理解软软解析先要理解游标的概念，当执行SQL时，首先要打开游标，执行完成后，要关闭游标，游标可以理解为SQL语句的一个句柄。

在执行软软解析之前，首先要进行软解析，MOS上说执行3次的SQL语句会把游标缓存到PGA，这个游标一直开着，当再有相同的SQL执行时，则跳过解析的所有过程直接去取执行计划。

实际上是当设置了session_cursor_cache这个参数之后，Cursor被直接Cache在当前Session的PGA中的，在解析的时候只需要对其语法分析、权限对象分析之后就可以转到PGA中查找了，如果发现完全相同的Cursor，就可以直接去取结果了，也就就是实现了 Soft Soft Parse.

---

解析过程分析

语法检测

判断一条SQL语句的语法是否符合SQL的规范，比如执行：

SQL> selet * from emp;

我们就可以看出由于Select关键字少了一个“c”，这条语句就无法通过语法检验的步骤了。

---

语义及权限检查

语法正确的SQL语句在解析的第二个步骤就是判断该SQL语句所访问的表及列是否准确？用户是否有权限访问或更改相应的表或列？ 比如如下语句：

SQL> select * from emp;

select * from emp

*

ERROR at line 1:

ORA-00942: table or view does not exist

由于查询用户没有可供访问的emp对象，因此该SQL语句无法通过语义检查。

---

解析的2个步骤

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语句就已经可以共享了。

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

SQL> select owner,table_name from dba_tables where table_name like 'EMP%';

OWNER                 TABLE_NAME
------------------------------ ------------------------------
ZMC                       EMP
CC                          EMP

EMP –两个对象的名字相同，当所有者不同。

zmc@entel> select * from tb_obj;

cc@entel> select * from tb_obj; 

由于查询的对象不同，是无法共享的,此时两者都需要使用硬解析以及走不同的执行计划.

可以进一步查询v$sql_shared_cursor以得知SQL为何不能共享的原因：

select address,
       auth_check_mismatch,
       translation_mismatch,
       optimizer_mismatch
  from v$sql_shared_cursor
  where address in (    
                   select address
                     from v$sql
                    where upper(sql_text) like 'SELECT * FROM EMP%');

ADDRESS     A T O

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

2769AE64     N N N

2769AE64     Y Y N

说明：

TRANSLATION_MISMATCH 表示SQL游标涉及到的数据对象是不同的；

AUTH_CHECK_MISMATCH 表示对同样一条SQL语句转换是不匹配的。

optimizer_mismatch 表示会话的优化器环境是不同的。

---

2. 验证SQL语句执行环境是否相同

比如同样一条SQL语句，一个查询会话加了/*+ first_rows */的HINT，另外一个用户加/*+ all_rows */的HINT，他们就会产生不同的执行计划，尽管他们是查询同样的数据。

通过如上检查以后，如果SQL语句是一致的，那么就会重用原有SQL语句的执行计划和优化方案，也就是我们通常所说的软解析。如果SQL语句没有找到同样的副本，那么就需要进行硬解析了。

Oracle根据提交的SQL语句再查询相应的数据对象是否有统计信息。如果有统计信息的话，那么CBO将会使用这些统计信息产生所有可能的执行计划(可能多达成千上万个)和相应的Cost，最终选择Cost最低的那个执行计划。如果查询的数据对象无统计信息，则按RBO的默认规则选择相应的执行计划。这个步骤也是解析中最耗费资源的，因此我们应该极力避免硬解析的产生。至此，解析的步骤已经全部完成，Oracle将会根据解析产生的执行计划执行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不同，则需要使用硬解析，生成不同的执行计划

---

硬解析的弊端

硬解析即整个SQL语句的执行需要完完全全的解析，生成执行计划。而硬解析，生成执行计划需要耗用CPU资源，以及SGA资源。

在此不得不提的是对库缓存中闩（latch）的使用。闩是锁的细化，可以理解为是一种轻量级的串行化设备。当进程申请到闩后，则这些闩用于保护共享内存的数在同一时刻不会被两个以上的进程修改。

在硬解析时，需要申请闩的使用，而闩的数量在有限的情况下需要等待。大量的闩的使用由此造成需要使用闩的进程排队越频繁，性能则逾低下。

---

硬解析的改进方法

1 .更改参数cursor_sharing

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

　　CURSOR_SHARING = { SIMILAR | EXACT | FORCE }

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

　　FORCE –如果SQL语句是字面量，则迫使Optimizer(优化器)始终使用已有的执行计划，无论已有的执行计划是不是最佳的。

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

可以基于不同的级别来设定该参数，如ALTER SESSION, ALTER SYSTEM

查询当前的CURSOR_SHARING的值

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 
SQL> show parameter CURSOR_SHARING 

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------
cursor_sharing                       string      EXACT

相当于

select * from v$parameter a where a.NAME like '%cursor_sharing%';

SQL>alter system set cursor_sharing=’similar’; –将参数cursor_sharing的值更改为similar 　　注意当该参数设置为similar,会产生不利的影响

---

2.使用绑定变量

使用了Bind Var能提高性能主要是因为这样做可以尽量避免不必要的硬分析(Hard Parse)而节约了时间，同时节约了大量的CPU资源。

当一个Client提交一条Sql给Oracle后，Oracle 首先会对其进行解析(Parse)，然后将解析结果提交给优化器(Optimiser)来进行优化而取得Oracle认为的最优的Query Plan，然后再按照这个最优的Plan来执行这个Sql语句(当然在这之中如果只需要软解析的话会少部分步骤)。

但是，当Oracle接到 Client提交的Sql后会首先在共享池(Shared Pool)里面去查找是否有之前已经解析好的与刚接到的这一个Sql完全相同的Sql(注意这里说的是完全相同，既要求语句上的字符级别的完全相同，又要求涉及的对象也必须完全相同)。当发现有相同的以后解析器就不再对新的Sql在此解析而直接用之前解析好的结果了。这里就节约了解析时间以及解析时候消耗的CPU资源。尤其是在OLTP中运行着的大量的短小Sql，效果就会比较明显了。因为一条两条Sql的时间可能不会有多少感觉，但是当量大了以后就会有比较明显的感觉了。

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

绑定变量(bindvariable)是指在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

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

---

使用绑定变量的栗子 (软解析/软软解析)

测试数据：

create table xgj_test(x_id int );
insert into xgj_test(x_id) values (1);
insert into xgj_test(x_id) values (2);
insert into xgj_test(x_id) values (3);
insert into xgj_test(x_id) values (4);
insert into xgj_test(x_id) values (5);
commit ;

软解析：

sql command窗口：

SQL> var xid number;
SQL>  exec :xid:=1;

PL/SQL procedure successfully completed
xid
---------
1

SQL> select * from xgj_test  where x_id=:xid;

                                   X_ID
---------------------------------------
                                      1
xid
---------
1

SQL>  exec :xid:=2;

PL/SQL procedure successfully completed
xid
---------
2

SQL> select * from xgj_test  where x_id=:xid;

                                   X_ID
---------------------------------------
                                      2
xid
---------
2

SQL>  exec :xid:=3;

PL/SQL procedure successfully completed
xid
---------
3

SQL> select * from xgj_test  where x_id=:xid;

                                   X_ID
---------------------------------------
                                      3
xid
---------
3

SQL>  exec :xid:=4;

PL/SQL procedure successfully completed
xid
---------
4

SQL> select * from xgj_test  where x_id=:xid;

                                   X_ID
---------------------------------------
                                      4
xid
---------
4

SQL>  exec :xid:=5;

PL/SQL procedure successfully completed
xid
---------
5

SQL> select * from xgj_test  where x_id=:xid;

                                   X_ID
---------------------------------------
                                      5
xid
---------
5

SQL> 

查看解析次数

---

软软解析：

 begin
         for i in 1..5 loop
         execute immediate ' select * from xgj_test  where x_id=:i' using i;
         end loop;
end;

比较软解析和软软解析的解析次数

---

使用绑定变量的栗子 (软软解析)

create table xiaogongjiang(col int); --创建表txiaogongjiang

create or replace procedure proc1 as  --创建存储过程proc1使用绑定变量来插入新记录
 begin
       for i in 1 .. 10000
       loop
            execute immediate  'insert into xiaogongjiang values(:n)' using i;
       end loop;
       --提交
       commit;
 end proc1;

SQL>  create table xiaogongjiang(col int); --创建表txiaogongjiang

Table created

SQL> 
SQL> create or replace procedure proc1 --创建存储过程proc1使用绑定变量来插入新记录
  .........
Warning: Procedure created with compilation errors

当有错误时，可以通过show error来显示错误
SQL> show error
Errors for PROCEDURE ZMC.PROC1:

LINE/COL ERROR
-------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2/3      PLS-00103: Encountered the symbol "" when expecting one of the following............ 

SQL> 
SQL> create or replace procedure proc1 as  --创建存储过程proc1使用绑定变量来插入新记录
  2   begin
  3         for i in 1 .. 10000
  4         loop
  5              execute immediate  'insert into xiaogongjiang values(:n)' using i;
  6         end loop;
         --提交
           commit;
  7   end proc1;
  8  /

Procedure created

SQL> 

执行存过之前，我们先查询下v$sql中的内容：

select *  from v$sql a where a.SQL_TEXT like 'insert into xiaogongjiang%';

执行存过

SQL> exec proc1

PL/SQL procedure successfully completed

时长：

再次查询

select *  from v$sql a where a.SQL_TEXT like 'insert into xiaogongjiang%';

未使用绑定变量的栗子 （硬解析）

create table xiaogongjiang2(col int); --创建表xiaogongjiang2

create or replace procedure proc2 --创建存储过程proc2,未使用绑定变量，因此每一个SQL插入语句都会硬解析
 as
begin
  for i in 1 .. 10000  
   loop 
    execute immediate 'insert into xiaogongjiang2 values('||i||')'; 
 end loop;
--提交
  commit;
end proc2;

执行存过：

SQL> exec proc2

PL/SQL procedure successfully completed

时长：

重新查询

select *  from v$sql a where a.SQL_TEXT like 'insert into xiaogongjiang2%';

每一条都是一个硬解析，也耗时了3.7S , 使用绑定变量耗时0.4S…..

在未使用绑定变量的情形下，不论是解析次数，闩使用的数量，队列，分配的内存，库缓存，行缓存远远高于绑定变量的情况。因此尽可能的使用绑定变量避免硬解析产生所需的额外的系统资源。

---

查看解析次数

select sql_text, s.PARSE_CALLS, loads, executions
  from v$sql s
 where sql_text like 'insert into xiaogongjiang%'
 order by 1, 2, 3, 4;

结合栗子

create table xgj(col int); --xgj

create or replace procedure proc1 as  --创建存储过程proc1使用绑定变量来插入新记录
 begin
       for i in 1 ..  100
       loop
            execute immediate  'insert into xgj values(:n)' using i;
       end loop;
          --提交
       commit;
 end proc1;

>exec proc1 

PL/SQL procedure successfully completed

上面的栗子

字段解释：

PARSE_CALLS 解析的次数

LOADS 硬解析的次数

EXECUTIONS 执行的次数

---

绑定变量的优点

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

绑定变量的缺点

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

---

总结

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

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

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