Oracle绑定变量、硬解析、软解析、执行计划关系

xxtgzznigg

　　Oracle在执行SQL语句时，普遍存在以下几个步骤：

• 当SQL语句首次执行，Oracle将确认该句语句的语法是否正确（语法解析Syntax parse）并进一步确认语句相关表和列的存在性等因素(语义解析semantic parse)以及优化器决定执行计划等步骤。整个过程称之为硬解析，硬解析消耗大量的CPU时间和系统资源。硬解析过多会有效降低系统性能。
  

• 若之前已进行过硬解析，且解析后的分析树和执行计划仍存在于共享池中，则同样的SQL仅需要软解析。软解析将输入的SQL语句转换为哈希代码，同共享池内哈希链表上的已有记录进行对比，找出对应的游标信息，使用已有的执行计划执行。
  

• 绑定变量,将实际的变量值代入SQL语句中。
  

• 执行SQL语句，查询语句将返回结果集。
  

　　不使用绑定变量的SQL语句，Oracle无法将它们视为相同的，如以下两句语句：

select * from emp where   empno=1234　　select * from emp where   empno=5678

　　因为自由变量的不同，Oracle认为以上是2句不同的语句，则当第一条被硬解析后，第二条SQL执行时仍无法避免硬解析。实际在以上不使用绑定变量的情况中，只要自由变量有所改变则需要一次硬解析。这是强烈建议使用绑定变量的主要原因，使用绑定变量的语句变量的实际值仅在SQL执行的最后阶段被代入。如以下语句：

select * from emp where   empno=:x

　　该语句使用绑定值:x替代自由变量，在应用中语句可能以预编译或普通编译的方式存在，仅在执行阶段代入变量值，多次执行仅需要一次硬解析，较不使用绑定变量情况性能大大提升。
　　同时过多的硬解析还会引发共享池碎片过多的问题。因为每当需要硬解析一个SQL或者PLSQL语句时，都需要从shared pool中分配一块连续的空闲空间来存放解析结果。Oracle首先扫描shared pool查找空闲内存，如果没有发现大小正好合适的空闲chunk，就查找更大的chunk，如果找到比请求的大小更大的空闲chunk，则将它分裂，多余部分继续放到空闲列表中。因为过多的硬解析加剧了内存段分配的需求，这样就产生了碎片问题。系统经过长时间运行后，就会产生大量小的内存碎片。当请求分配一个较大的内存块时，尽管shared pool总空闲空间还很大，但是没有一个单独的连续空闲块能满足需要。这时，就可能产生 ORA-4031错误。
　　通常我们可以通过以下SQL语句将系统中非绑定变量的语句找出：

SELECT substr(sql_text,1,40)   “SQL”,　　count(*) , 　　sum(executions) “TotExecs” 　　FROM v$sqlarea 　　WHERE executions < 5 –-语句执行次数 　　GROUP BY substr(sql_text,1,40) 　　HAVING count(*) > 30 –-所有未共享的语句的总的执行次数 　　ORDER BY 2;

　　以上语句在实际使用中substr函数截取到的字符串长度需要视乎实际情况予以变化。
　　对于非绑定变量且短期内无法修改的应用，Oracle存在参数cursor_sharing可以改善其表现。cursor_sharing默认为exact,对使用自由变量的语句不做额外处理；当设为force时，非绑定变量的SQL语句被进一步处理以达到共享SQL的目的，但以上处理步骤同样要消耗一定的CPU时间；当设为similar时，若数据库存在语句相关统计信息则其表现如exact,若无统计信息则表现为force。cursor_sharing参数是Oracle针对无法修改的非绑定变量应用所提出的折中方案，但cursor_sharing为force值时存在一定SQL引发bug或语句无效的情况，且额外的处理操作同样需要消耗一定量的CPU时间和系统资源。故针对系统性能的最优方案往往是直接修改应用代码，使用绑定变量特性。
　　
　　附注英文资料
Oracle SQL is parsed before execution, and a hard parse includes 
these steps:

    Loading into shared pool - The SQL source code is loaded into RAM 
for parsing. (the "hard" parse step)

    Syntax parse - Oracle parses the syntax to check for misspelled
 SQL keywords.

    Semantic parse - Oracle verifies all table & column names
 from the dictionary and checks to see if you are authorized to
 see the data.

    Query Transformation - If enabled (query_rewrite=true), 
Oracle will transform complex SQL into simpler, equivalent forms and 
replace aggregations with materialized views, as appropriate.

    Optimization - Oracle then creates an execution plan, based on
 your schema statistics (or maybe with statistics from dynamic sampling in 10g).

    Create executable - Oracle builds an executable file with native 
file calls to service the SQL query.

Oracle gives us the shared_pool_size parm to cache SQL so that 
we don't have to parse, over-and-over again.  
However, SQL can age-out if the shared_pool_size is too small or
 if it is cluttered with non-reusable SQL (i.e. SQL that has literals "where name = "fred") in the source.

What the difference between a hard parse and a soft parse in Oracle? 
 Just the first step, step 1 as shown in red, above.  In other words,
 a soft parse does not require a shared pool reload (and the associated
 RAM memory allocation).

A general high "parse call" (> 10c.) indicates that your system has many incoming 
unique SQL statements, or that your SQL is not reentrant 
(i.e. not using bind variables).

A hard parse is when your SQL must be re-loaded into the shared pool.
  A hard parse is worse than a soft parse because of the overhead
 involved in shared pool RAM allocation and memory management. 
 Once loaded, the SQL must then be completely re-checked for syntax
 & semantics and an executable generated. 

Excessive hard parsing can occur when your shared_pool_size is too 
small (and reentrant SQL is paged out), 
or when you have non-reusable SQL statements without host variables.

See the cursor_sharing parameter for a easy way to make 
SQL reentrant and remember that you should
 always use host variables in you SQL so that they can be reentrant.