SQL 解析过程及效率提升

   一 、 sql 的执行过程

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

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

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

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

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

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

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

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

二、硬解析，软解析

   不能硬解析的情况：对象大小写不同，值不同。

三、硬解析的弊端

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

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

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

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

四、硬解析和软解析效率比较

 

1.创建一个测试表

create table efftable
   (
    idd integer
   );

2.创建无参数绑定存储过程

   create or replace procedure pro_notbind as
begin
 for i in 1..1000 loop
 execute immediate 'insert into efftable (idd) values ('
 || i || ')';
 end  loop;
 commit;
 end;

3.创建参数绑定存储过程

create or replace procedure pro_bind as
begin
 for i in 1..1000 loop
 execute immediate 'insert into efftable (idd) values (:x)'
 using i;
 end  loop;
 commit;
 end;

 

4.分别执行pro_notbind ，pro_bind

执行pro_notbind 花费的时间大约为11s

执行pro_bind 花费的时间大约为0.047s

另外也可以sql记录表v$sqlarea中查询sql的执行情况：
第一个在v$sqlarea有1000条记录。

 

五、编码硬解析的改进方法

 1.更改参数cursor_sharing

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

        CURSOR_SHARING = { SIMILAR | EXACT | FORCE }   

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

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

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

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

 

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

六、总结

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

 

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

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