Hard Parse就是膳缦沔提到的对提交的Sql完全年夜头年夜头进行解析(当在Shared Pool中找不到时辰将会进行此操作),总共有一下5个执行轨范:
$ ^4 s; b+ R6 S7 p" r 1:语法剖析
. D# Y5 r. e4 y2 t 2:权限与对象搜检
' H8 u' c. U* s6 b8 m! ~6 D7 X 3:在共享池中搜检是否有完全不异的之前完全解析好的—如不美观存在,直接跳过4和5,运行Sql(此时算soft parse), ~. k1 ]% q, ]! X- r" A
4:选择执行打算6 }" k2 g G% C' g
5:发生执行打算
+ z9 l, @% R7 F6 ] Soft Parse就如不美观是在Shared Pool中找到了与之完全不异的Sql解析好的结不美观后会跳过Hard Parse中的后面的两个轨范。3 a b( h4 ]$ b, R# _4 a- |) a
Soft Soft Parse现实上是当设置了session_cursor_cache这个参数之后,Cursor被直接Cache在当前Session的PGA中的,在解析的时辰只需要对其语法剖析、权限对象剖析之后就可以转到PGA中查找了,如不美观发现完全不异的Cursor,就可以直接去取结不美观了,也就就是实现了 Soft Soft Parse.
3 I2 u( Q; r6 M$ U$ L, S/ S 不外在计较解析次数的时辰是只计较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:执行次数]
/ Y0 H" e2 U' U% Z: ? /*****************************************************/
$ v8 ~) j/ p. d' J( ^+ o quote from difference between soft parse and hard parse
2 i* ?$ W$ g$ }1 P8 ?) y9 _ -- Thanks for the question regarding "difference between soft parse and hard parse", version 8.1.7.2
' a, i) v" m" K1 ]8 j originally submitted on 26-Jan-2002 19:17 Eastern US time, last updated 5-Aug-2004 13:07
/ ^4 C8 A3 R$ c7 {# U: e Hi Tom0 Z" l; F2 _6 D
Can you explain briefly the difference between soft and hard parse?* R6 x1 l8 \1 C' \9 F3 Q
你能不能给我具体介绍一下软解析与硬解析的区别啊?- w' @5 F- y7 w7 i! \% I: N
and we said……% U% }5 a9 M) s$ V
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:这是一个很长的诠释,是年夜我与几个同事一路合著的新书中提掏出来的。
+ f) C' v& j( z5 Q- P! a! w Parsing解析
! T; v6 M0 a3 O6 R 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仍是数据界说说话),以对这条语句执行各类分歧的语法检测。5 S- o8 _$ t/ d# p+ E1 j6 C
The parsing process performs two main functions:解析过程首要有一下两个功能。) n& L4 \) r. e1 g5 L
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. 1. 语法搜检,这条语句是否合理? 它是否合适SQL语法文档的相关语法,是不是合适SQL语句的所有轨则。
& ^" K/ m) W) y 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 T1 and T2 and both have a column X, the query ?select X from T1, T2 where ?? is ambiguous, we don?t know which table to get X from. And so on. 2.语义剖析: 已经措置完语法搜检了吗? 这条语句是否正确的引用了数据库中对象(是否语句中所有的表和字段都存在)。 是否有访谒这些对象的权限? 是不是对应的权限都有用(首要指role对应的权限吧,)? 好比是否有如下的两个表T1,T2,而这两个表有不异的字段名column X,而发芽语句 ? select X from T1,T2 where ??,(没有明晰指定列名),我们无法知道年夜哪个表去掏出字段X的值,以及近似的一系列问题。
6 r/ T$ z- B3 k% P: Z 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不会出格的指出这条语句没有经由过程语法搜检,它给你返回这条语句对应的错误代码和错误信息。 好比: 下面这条语句没有经由过程语法搜检。 |