SQL -- 判断测试SQL为软/硬解析(Oracle)

前言：本人能力有限，本文部分参考。

执行SQL是软解析、硬解析？

 

理论知识：（参考博客leshami）

Oracle 硬解析与软解析是我们经常遇到的问题，什么情况会产生硬解析，什么情况产生软解析，又当如何避免硬解析？下面的描述将给出软硬解析的产生，以及硬解析的弊端和如何避免硬解析的产生。（其实理论说起来不难，难的是开发让你证明。头皮发麻）

一、SQL语句的执行过程

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

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

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

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

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

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

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

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

      
二、硬解析的弊端

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

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

    1.更改参数cursor_sharing 参数

        cursor_sharing决定了何种类型的SQL能够使用相同的SQL area (PS:不建议调整)

        CURSOR_SHARING = { SIMILAR | EXACT | FORCE } 

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

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

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

                               --语句进行分析来制定最佳执行计划。可以基于不同的级别来设定该参数，

                                      如ALTER SESSION, ALTER SYSTEM

 

    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

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

 

 绑定变量优点

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

 绑定变量缺点

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

六、总结

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

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

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

---

---

 

理论知识补充结束，下面记录一下头皮发麻的证明过程。

截图 1-1

注释：一般我们都是希望开饭是使用绑定变量的。

        简要解释一下下面的图片的含义。图中1-3行为使用绑定绑定变量，但是递归次数少于总执行数。主要因为可能多次相同的语句且在共享游标还未失效的情况下，也是不需要硬解析的。

        图中第四行使用了绑定变量但是仍然有79次的硬解析

--以下是查询SQL  
 SELECT SQL_TEXT,--执行的SQL文本
       sql_id,
       last_active_time, --可以大概过滤一下
       EXECUTIONS 总执行次数,
       PARSE_CALLS AS "递归调用/硬解析" --这个是递归调用解析总和，不能一概而论需要自己分析
                    FROM v$SQLstats 
                    WHERE SQL_TEXT LIKE 'SELECT PERMISSION_ID%FROM UI_Permission%WHERE RESOURCE_ID =%UNION%SELECT PERMISSION_ID%FROM UI_Permission%WHERE UI_Permission.RESOURCE_ID IN%'
                     and last_active_time >to_date('25/06/2019 8:50','dd/mm/yyyy HH24:MI') 
                     order by 4 desc;

另一个角度（游标）分析  参考博客

1,Shared Cursor
Oracle里的第一种类型的Cursor就是Shared Cursor。 它是存在Shared Pool里，Shared Pool 是SGA里的里的一块内存区域，Shared Pool（Library Cache & Data Dict Cache）。 Library Cache缓存的是刚刚执行过的SQL语句和PL/SQL语句（procedure,function, package and trigger）所对应的执行计划，解析数(parse tree).Pcode,Mcode等对象。缓存在库缓存中的对象我们称它为库缓存对象（Library Cache Object）. Oracle数据库中的Shared Cursor又细分为Parent Cursor(父游标)和Child Cursor(子游标). 这两种类型。我们可以通过查询V$SQLAREA和 V$SQL 来查看当前缓存中的Parent Cursor和 Child Cursor。其中V$SQLAREA用于查看Parent Cursor， V$SQL用于查看Child Cursor. 在ORACLE数据库里，任意一个目标SQL 一定会同时对应两个Shared Cursor,其中一个是Parent Cursor，另外一个是Child Cursor. Parent Cursor会存储该SQL 的SQL 文本，而该SQL 真正的可以被重用的解析数和执行计划则存储在Child Cursor中。
2，Session Cursor
Oracle里的第二种类型的Cursor就是Session Cursor。它是当前Session解析和执行SQL的载体，换句话说，Session Cursor用于在当前Session中的解析和执行SQL，它和Shared Cursor的区别是，它是缓存在PGA中，而不是像Shared Cursor那样缓存在SGA的库缓存里。
Session Cursor与Session是一一对应的，不同的session的Session Cursor直接没有办法共享，这个和Shared Cursor有本质的区别。Session Cursor是有生命周期的,每个session都在使用的过程中都至少会经历一场（open,parse,bind,execute,fetch and Close）中的一个或者多个阶段。用过的Session Cursor不一定缓存在对应的Session 的PGA中，这取决于Session_cached_cursor的值是否大于0.Oracle在解析和执行目标SQL时，会先去当前的SESSION的PGA中找是否有匹配的缓存的Session Cursor.当第一次解析和执行目标SQL时(显然是硬解析)，当前SESSION的PGA中肯定不存在匹配的SESSION CURSOR,这个时候ORACLE 会新生成一个Session Cursor和一对Shared Cursor(即Parent Cursor 和 Child Cursor)，这其中的Shared Cursor会存储能被所有Session共享，重用的内容（比如目标SQL的解析树，执行计划等），而Session Cursor则会经历一次open,parse,bind,execute,fetch和close中的一个或多个阶段。显然一个Session Cursor 只能对应一个Shared Cursor, 而一个Shared Cursor却可以同时对应多个Session Cursor.
 

 

========================================================================================