Oracle的硬解析和软解析

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

语法检查（syntax check）

检查此sql的拼写是否语法

语义检查（semantic check）

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

对sql语句进行解析（prase）

利用内部算法对sql进行解析生成解析树（parse tree）及执行计划（execution plan）

执行sql返回结果（execute and return）

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

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

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

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

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

创建解析树生成执行计划对于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主要分为三种

Hard Parse （硬解析）

Soft Parse （软解析）

Soft Soft Parse（好像有些资料中并没有将这个算在其中）

Hard Parse就是上面提到的对提交的Sql完全重新从头进行解析（当在Shared Pool中找不到时候将会进行此操作）总共有一下个执行步骤

语法分析

权限与对象检查

在共享池中检查是否有完全相同的之前完全解析好的—如果存在直接跳过和运行Sql（此时算soft parse）

选择执行计划

产生执行计划

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（*（hprs/prse）） [hprs硬解析次数prse解析次数] Parse比率计算 Round（*（prse/exec） ） [exec执行次数]

/*****************************************************/

quote from difference between soft parse and hard parse

Thanks for the question regarding difference between soft parse and hard parse version

originally submitted on Jan : Eastern US time last updated Aug :

Hi Tom

Can you explain briefly the difference between soft and hard parse?

你能不能给我详细介绍一下软解析与硬解析的区别啊？

and we said……

Here is a long winded answer it is extracted in part from a new book oming out soon beginning Oracle programming that I collaborated on这是一个很长的解释是从我与几个同事一起合着的新书<<开始学习Oracle编程>>中提取出来的

Parsing解析

This is the first step in the processing of any statement in Oracle Parsing is the act of breaking the submitted statement down into its component parts ？ determining what type of statement it is （query DML DDL） and performing various checks on it这是Oracle处理所有语句的第一步解析就是将用户提交的语句（根据一定的规则）分成不同的组件来确定这条语句是什么类型的语句（query查询DML数据操纵语言DDL还是数据定义语言）以对这条语句执行各种不同的语法检测

The parsing process performs two main functions解析过程主要有一下两个功能

Syntax Check is the statement a valid one？ Does it make sense given the SQL grammar documented in the SQL Reference Manual Does it follow all of the rules for SQL 语法检查这条语句是否合理？ 它是否符合SQL语法文档<>的相关语法是不是符合SQL语句的所有规则

o Semantic Analysis Going beyond the syntax ？ is the statement valid in light of the objects in the database （do the tables and columns referenced exist） Do you have access to the objects ？ are the proper privileges in place？ Are there ambiguities in the statement ？ for example if there are two tables T and T and both have a column X the query ？select X from T T where ？？ is ambiguous we don？t know which table to get X from And so on 语义分析 已经处理完语法检查了吗？ 这条语句是否正确的引用了数据库中对象（是否语句中所有的表和字段都存在） 是否有访问这些对象的权限？ 是不是对应的权限都有效（主要指role对应的权限吧）？ 比如是否有如下的两个表TT而这两个表有相同的字段名column X而查询语句 ？ select X from TT where ？？（没有明确指定列名）我们无法知道从哪个表去取出字段X的值以及类似的一系列问题

So you can think of parsing as basically a two step process that of a syntax check to check the validity of the statement and that of a semantic check ？ to ensure the statement can execute properly The difference between the two types of checks are hard for you to see ？ Oracle does not come back and say ？it failed the syntax check？ rather it returns the statement with a error code and message So for example this statement fails with a syntax error因此你可以认为解析基本上是由这两个步骤组成的 检查语句有效性的语法检查和确保语句能够正常运行的语义检查这两种检查的区别就是你所要说的硬解析了Oracle不会特别的指出这条语句没有通过语法检查它给你返回这条语句对应的错误代码和错误信息 比如 下面这条语句没有通过语法检查

Code

SQL> select from where ;

select from where

*

ERROR at line :

ORA: missing expression

While this statement failed with a semantic error ？ if the table NOT_A_TABLE existed and we had permission to access it this statement would have succeeded

而这条语句没有通过语义检查？ 如果表NOT_A_TABLE存在并且我们具有访问这个表的权限这条语句就会正确执行了

Code

SQL> select * from not_a_table;

select * from not_a_table

*

ERROR at line :

ORA: table or view does not exist

That is the only way to really tell the difference between a semantic and syntactic error ？ if the statement COULD have executed given the proper objects and privileges you had a semantic error otherwise if the statement could not execute under any circumstances you have a syntax error Regardless ？ Oracle will not execute the statement for you！

只是我能用来解释语法错误和语义错误的唯一的办法了如果在赋予合适的对象和权限的情况下这条语句确实能够执行的话我们称之为语义错误否则如果这条语句在任何条件下都不能执行的话我们就称之为语法错误无论如何Oracle都不会为你运行这条语句

The next step in the parse operation is to see if the statement we are currently parsing has already in fact been processed by some other session If it has ？ we may be in luck here we can skip the next two steps in the process that of optimization and row source generation If we can skip these next two steps in the process we have done what is known as a Soft Parse ？ a shorter process to getting our query going If we cannot if we must do all of the steps we are performing what is known as a Hard Parse ？ we must parse optimize generate the plan for the query This distinction is very important When developing our applications we want a very high percentage of our queries to be Soft Parsed ？ to be able to skip the optimize/generate phases ？ as they are very CPU intensive as well as a point of contention （serialization） If we have to Hard Parse a large percentage of our queries our system will function slowly and in some cases ？ not at all

解析中的下一个步骤就是检查我们现在正在解析的语句是否已经被其他的session执行过在这一步我们可能很幸运我们可以跳过下面的两个步骤 语句的优化（生成执行计划） 和 生成执行编码 如果我们能够跳过这两个步骤我们就实现了一般意义上的软解析 我们必须解析优化并为这条语句生成执行计划这个特性（distinction）非常重要当我们开发应用程序的时候我们会希望大部分的语句都是软解析的以跳过优化和生成编码的步骤因为他们和争用（串行化）（contention）一样都是非常消耗CPU的操作如果我们必须硬解析我们语句的大部分的话在某些情况下我们的系统就会运行的很慢并不是任何时候（在olapdss系统应该是个例外吧）

The way this sharing of SQL in Oracle is accomplished is via the shared pool a piece of memory in the SGA maintained by Oracle We covered this topic in chapter but will revisit it again in the context of processing a query After Oracle parses the query and it passes the syntax and semantic checks ？ it will look in the shared pool component of the SGA to see if that same exact query has already been processed by another session Since it has performed the semantic check it has already figured out

在Oracle中共享SQL的方式是通过共享池——SGA中的一块内存（由Oracle系统来维护）——实现的我们在第五章中讨论了这个主题并且会在查询处理的章节再次讨论这个主题当Oracle已经解析了这条语句并且已经通过语法和语义检查的时候Oracle就会在SGA的shared pool（共享池）组件中查询看看是否已经有一个完全一样的语句已经被另外一个session执行过因为语句已经通过了oracle的语义检查并且oracle已经计算出

o Exactly what tables are involved具体涉及到哪些表

o That we have access to the tables （the proper privileges are there）

我们拥有访问对应表的个相应权限等等……

And so on Now it can look at all of the queries in the shared pool that have already been parsed/optimized and generated to see if the work has already been done现在可以在所有已经经过解析和优化并且生成的可执行编码的SQL语句搜索来看看对应的语句是否已经经过解析

Soft Parse and session_cashed_cursor parameter January Reviewer A reader软解析与session_cashed_cursor参数

can you explain what means the default value （） of session_ casched_cursor parameter？ Oracle always has a cache of sqls in sga and size of this cache is determinated through initora parameter shared_pool_size

tom 你能不能给我解释一下session_cashed_cursor（默认值为）参数的含义Oracle在SGA中始终保持一个sql语句的缓存这个缓存的大小是由初始化参数shared_pool_size的大小决定的

FollowupThe default value of ZERO means that by default ZERO cursors will be cached for your session

默认值为表示在默认情况下Oracle不会给你的session缓存游标

They will be cached in the shared pool —— but your session will have to find them there Session Cached Cursors can remove the need to have to find them Consider this example that shows the difference spent when soft parsing without session cached cursors vs soft parsing WITH session cached cursors他们会被缓存在共享池（Shared Pool）中但是你的session必须在共享池中去查找他们session cached cursors可以省略掉去共享池中查找这一步下面这个例子给你演示了没有缓存游标和缓存了游标之后软解析之间的差别和系统消耗资源的情况

Code

ops$tkyte@ORADEVUSORACLECOM> create table emp as select * from scottemp;

Table created

ops$tkyte@ORADEVUSORACLECOM> create table run_stats ( runid varchar()

name varchar() value int );

Table created

ops$tkyte@ORADEVUSORACLECOM> create or replace view stats

as select STAT || aname name bvalue

from v$statname a v$mystat b

where astatistic# = bstatistic#

union all

select LATCH || name gets

from v$latch;

View created

ops$tkyte@ORADEVUSORACLECOM> column name format a

ops$tkyte@ORADEVUSORACLECOM> declare

l_start number;

l_cnt number;

begin

execute immediate alter session set session_cached_cursors=;

insert into run_stats select before stats* from stats;

l_start := dbms_utilityget_time;

for i in

loop

execute immediate select count(*) from emp into l_cnt;

end loop;

dbms_outputput_line( (dbms_utilityget_timel_start) || hsecs );

execute immediate alter session set session_cached_cursors=;

insert into run_stats select after stats* from stats;

l_start := dbms_utilityget_time;

for i in

loop

execute immediate select count(*) from emp into l_cnt;

end loop;

dbms_outputput_line( (dbms_utilityget_timel_start) || hsecs );

insert into run_stats select after stats* from stats;

end;

/

hsecs

hsecs

PL/SQL procedure successfully completed

so session cached cursors RAN faster （i ran this a couple of times there were no hard parses going on But the real good news is因此在session中缓存游标可以运行的更快（我运行了好几次没有硬解析的发生）真正可喜的消息是

Code

ops$tkyte@ORADEVUSORACLECOM> select aname bvalueavalue run

cvaluebvalue run

( (cvaluebvalue)(bvalueavalue)) diff

from run_stats a run_stats b run_stats c

where aname = bname

and bname = cname

and arunid = before

and brunid = after

and crunid = after

and (cvalueavalue) >

and (cvaluebvalue) <> (bvalueavalue)

order by abs( (cvaluebvalue)(bvalueavalue))

/

NAME RUN RUN DIFF

LATCHcheckpoint queue latch

LATCHredo allocation

nsistent gets

STATdeferred (CURRENT) block cleanout

applications

STATcalls to get snapshot scn: kcmgss

STATenqueue releases

STATexecute count

STATopened cursors cumulative

STATparse count (total)

STATsession cursor cache count

STATredo entries &nb