Oracle：软解析与硬解析+绑定窥视

说到软解析(soft prase)和硬解析(hard prase)，就不能不说一下Oracle对sql的处理过程。当你发出一条sql语句交付Oracle，在执行和获取结果前，Oracle对此sql将进行几个步骤的处理过程：

　　1、语法检查(syntax check)

　　检查此sql的拼写是否语法。

　　2、语义检查(semantic check)

　　诸如检查sql语句中的访问对象是否存在及该用户是否具备相应的权限。

　　3、对sql语句进行解析(prase)

　　利用内部算法对sql进行解析，生成解析树(parse tree)及执行计划(execution plan)。

　　4、执行sql，返回结果(execute and return)

　　其中，软、硬解析就发生在第三个过程里。

　　Oracle利用内部的hash算法来取得该sql的hash值，然后在library cache里查找是否存在该hash值；

　　假设存在，则将此sql与cache中sql语句的进行比较；

　　假设“相同”，就将利用已有的解析树与执行计划，而省略了优化器的相关工作。这也就是软解析的过程。

　　诚然，如果上面的2个假设中任有一个不成立，那么优化器都将进行创建解析树、生成执行计划的动作。这个过程就叫硬解析。

　　创建解析树、生成执行计划对于sql的执行来说是开销昂贵的动作，所以，应当极力避免硬解析，尽量使用软解析。

　　这就是在很多项目中，倡导开发设计人员对功能相同的代码要努力保持代码的一致性，以及要在程序中多使用绑定变量的原因

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

大家都在说在Sql中使用了Bind Var（绑定变量）会提高不少性能，那他到底是如何提高性能的呢？

使用了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的时间可能不会有多少感觉，但是当量大了以后就会有比较明显的感觉了。

上面说到了硬解析（Hard Parse），那这个Hard Parse到底是个啥呢？
Parse主要分为三种：
1、Hard Parse （硬解析）
2、Soft Parse （软解析）
3、Soft Soft Parse（好像有些资料中并没有将这个算在其中）

Hard Parse就是上面提到的对提交的Sql完全重新从头进行解析（当在Shared Pool中找不到时候将会进行此操作），总共有一下5个执行步骤：
1：语法分析
2：权限与对象检查
3：在共享池中检查是否有完全相同的之前完全解析好的—如果存在,直接跳过4和5,运行Sql（此时算soft parse）
4：选择执行计划
5：产生执行计划

Soft Parse就如果是在Shared Pool中找到了与之完全相同的Sql解析好的结果后会跳过Hard Parse中的后面的两个步骤。

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

不过在计算解析次数的时候是只计算Hard Parse和Soft Parse的（其实Soft Soft Parse好像也并不能算是做了Parse  ）：
Soft Parse百分比计算：Round(100*(1-:hprs/:prse),2) [hprs：硬解析次数；prse：解析次数]
Parse比率计算： Round(100*(1-prse/exec) ,2) [exec：执行次数]

 

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/bruce_sky/archive/2010/10/12/5935009.aspx

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

cursor_sharing这个参数有三个取值：

FORCE
Forces statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect the meaning of the statement.

SIMILAR
Causes statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect either the meaning of the statement or the degree to which the plan is optimized.

EXACT
Only allows statements with identical text to share the same cursor.

有时候，很可能是在OLTP的系统中，为了最大限度的减少SQL PARSE的消耗，让类似的SQL可以尽可能的重用，我们会考虑设置把cursor_sharing设置为force。当cursor_sharing被设置为force的时候，优化器会用系统指定的绑定变量来替代SQL里面所有的literal constants，然后以此为基础判断我们的shared pool里面是不是有可以重用的cursor。按照我们上面的讨论，设置cursor_sharing为force对histograms影响最大的。

这个问题可以有两个work around,一是在我们认为影响会很到的SQL里面加上hint /*+ cursor_sharing_exact */，这回告诉CBO对于这个SQL采用cursor_sharing=exact的策略。
另一个解决方法是设置cursor_sharing=similar，按照上面Oracle文档的说法，设置cursor_sharing为similar也会首先把SQL里的literals替换为绑定变量，并且也会在第一次分析SQL的时候做bind variable peeking，但是当以后重新运行类似的SQL的时候，CBO会查看如果发现新的绑定变量会影响到执行计划（当然，之所以会产生不同的执行计划往往是因为存在histograms），就会重新生成执行计划。经过一些实验，我们可以发现，当设置cursor_sharing=similar的时候，如果我们的条件是range scan或等于的条件，并且条件涉及的列上有histograms信息的时候，CBO会在分析SQL的时候对绑定变量做检查，如果发现新的绑定变量有可能影响SQL的执行计划，则会重新评估并生成新的计划。

但是往往我们在优化系统的一个方面的时候会导致其他方面的问题，cursor_sharing=similar就是一个很典型的例子，当我们这样的设置的时候，首先优化器的压力会变大，因为CBO要做很多的重新优化。
更严重的问题在于cursor_sharing=similar会导致同样的SQL（除了绑定变量的值不一样之外）在library cache里面拥有很多不同的执行计划，因为我们知道一个SQL下面的所有执行计划都是被一个latch保护的，所以cursor_sharing=similar会导致更严重的latch 争用。

因此当我们使用cursor_sharing=similar的时候，除非必要，无需统计histograms信息，因为我们要保证我们为了解决一个问题不会导致其他的更严重的问题。

最后，当你设置CURSOR_SHARING为similar和force的时候，使用OUTLINES和EXPLAIN PLAN会遇到一些问题。
当你使用explain plan for {sql statement}, 或者create outline for {sql statement} 这样的命令时，Oracle不会把里面的常量替换为绑定变量，而是使用常量来生成执行计划。对于explain plan来说，我们看到的计划很可能不是真正执行时使用的计划，而outline则更糟，因为即使你运行了相同的SQL，Oracle会在处理SQL之前把里面的常量先替换为绑定变量，这样你执行的SQL和outline里面的SQL是不匹配的，从而导致outline不会被使用。

 

若不存在 histograms ，则不产生硬解析 。换句话说，当表的字段被分析过存在histograms的时候，similar 的表现和exact一样，当表的字段没被分析不存在histograms的时候,similar的表现和force一样。这样避免了一味地如force一样转换成变量形式，因为有hostograms的情况下转换成变量之后就容易产生错误的执行计划，没有利用上统计信息。而exact呢，在没有hostograms的情况下也要分别产生硬解析，这样的话，由于执行计划不会受到数据分布的影响（因为没有统计信息）重新解析是没有实质意义的。而similar则综合了两者的优点。

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

  

绑定窥视：

      优化程序在只有在硬解析的时候会进行窥视，软解析的时候不会。也就是说优化程序不会每回都根据你传递的值去选择正确的执行计划 执行，只在做硬解析的时候，也就是第一次执行语句才会。 这样会存在绑定变量选择错误执行计划的可能。

BIND PEEKING:如果收集了柱状图统计信息，并且使用了绑定变量，在第一次硬解析的时候CBO就会执行

BIND PEEKING，以便选择最佳的执行计划，再次执行的时候就不会bind peeking了，始终要记住bind peeking发生的条件:硬解析，存在柱状图，使用了绑定变量或者设置cursor_sharing=similar，下一篇就专门讨论 (cursor_sharing=similar)这个问题。

 

使用情形：

虽然变量绑定减少了硬编译次数，节省了cpu等资源，防止SQL注入。大多是情况下使用它可以显著的提高系统性能和系统的并发访问量，和安全性。
但是仍然有一些情况下不适合使用变量绑定：
1、对于隔相当长一段时间才执行一次的sql语句，利用绑定变量的好处会被不能有效利用优化器而抵消；
2、数据仓库的情况下；
3、在对建有索引的字段，且字段(集)的集非常大时，利用绑定变量可能会导致查询计划错误，因而导致查询效率非常低。

       不过可以得到2个提示:1绑定变量并不是在所有情况下都可以提高效率，当然绝大多数情况下会，在数据分布不均衡的情况下，还是别用绑定变量了，从上面的例子可以看出，使用了绑定变量，如果表记录达到百万级别，然后通过索引扫描，那将会对性能带来多大影响！，然而这个时候不使用绑定变量，只有三种情况 select owner from test where status='VALID'，select owner from test where status='INVALID',select owner from test where status='UNKONWN'，并不会给shared pool带来多大的问题，因为status值只有3个，重复解析最多3次。

2不要轻易相信autotrace(大多数情况下还是准确的)。

http://blog.sina.com.cn/s/blog_465a4a1e0100onhc.html

解决方法：解析计划应该更新的+不需更新的

    

      由于我们的系统是工作日（周一到周五）运行，每周系统都会重启。因此，在周一很多语句都会被硬解析。这样的话，很难避免在硬解析时，窥视的数据再次落入这些小分区内。要避免再次造成性能问题，可以考虑以下方法：

1. 相关语句上加HINT，强制使用索引。但是这样的修改涉及面太大，且如果将来Schema发生变化，代码维护更新困难；
2. 用Stored Outline为语句固定查询计划。其缺点和第一点差不多；
3. 禁用Bind Variable Peeking。因为我们的系统会每周重启，如果在db level禁用，风险较大，所以我们考虑在session level禁用。因为该模块的代码都是通过Package调用的，所以修改的代码量非常少：在入口函数上加上以下语句：execute immediate 'alter session set " _optim_peeking_user_binds" = false ' ;http://www.uml.org.cn/sjjm/201009145.asp

 

 

1.  首先对该字段分析柱状图。

2.  分析完毕立即执行如下代码：

1. ALTER SYSTEM FLUSH SHARED_POOL;  
2. VAR PV VARCHAR2(10);  
3. EXEC :PV:='A01P';  
4. SELECT ......WHERE STATUS=:PV; 

并且建议他们尽快修改应用程序，这个SQL不要使用绑定变量，因为今天的小手段只是一个临时性的措施，下次数据库重启或者其他情况下可能还会出问题。

http://book.51cto.com/art/201006/204572.htm

 

 

但是，很可惜的是，使用绑定变量从而共享游标与SQL优化是两个矛盾的目标。Oracle使用绑定变量的前提，是oracle认为大部分的列的数据都是分布比较均匀的。从而，使用第一次的绑定变量的值所得到的执行计划，大多数情况下都能适用于该绑定变量的其他的值。很明显，如果第一次传入的绑定变量的值恰好占整个数据量的百分比较高，从而导致全表扫描的执行计划。而后来传入的绑定变量的值都占整个数据量的百分比都很低，则应该走索引扫描会更好的，但是由于使用了绑定变量，从而oracle并不会再去看你的绑定变量的值，而是直接拿全表扫描的执行计划来用。这时，由于使用了绑定变量，虽然我们达到了游标共享，从而节省CPU的目的，但是SQL的执行计划却不够优化了。

 

那么我们如何在绑定变量和SQL优化之间进行取舍呢？在OLTP应用中，由于并发性较高，CPU上的争用会比较严重，同时SQL本身执行时间较短，涉及到的数据量较少，解析所占的时间在整个SQL执行时间中占的比例较高，而花在I/O上的时间占的比例较低。因此尽管绑定变量会有SQL不够优化的问题，还是建议使用绑定变量。但是在DSS应用和数据仓库应用中，由于并发性较低，CPU上的争用较轻，同时SQL语句的执行时间都很长，而且主要时间花在等待I/O上，而解析占的比重较低，这时优化SQL执行计划的重要性就体现出来了。因此，建议不要使用绑定变量，而直接使用字面值。但是大多数的情况都是混合应用，既有OLTP又有数据仓库，这时就很难完美的解决该问题了。