Hard Parse就是膳缦沔提到的对提交的Sql完全年夜头年夜头进行解析(当在Shared Pool中找不到时辰将会进行此操作),总共有一下5个执行轨范:
* E ?+ X* O, `9 I 1:语法剖析
; z% C8 v6 I# B3 |7 o* T 2:权限与对象搜检" k$ S8 M4 L x A8 w( I+ R
3:在共享池中搜检是否有完全不异的之前完全解析好的—如不美观存在,直接跳过4和5,运行Sql(此时算soft parse)* `* k, _$ i' @1 `
4:选择执行打算 T# W9 _9 h) |! M( B1 }1 s
5:发生执行打算0 b# m! D7 B7 u3 D' F' d
Soft Parse就如不美观是在Shared Pool中找到了与之完全不异的Sql解析好的结不美观后会跳过Hard Parse中的后面的两个轨范。
; {! K" K8 |) `9 } Soft Soft Parse现实上是当设置了session_cursor_cache这个参数之后,Cursor被直接Cache在当前Session的PGA中的,在解析的时辰只需要对其语法剖析、权限对象剖析之后就可以转到PGA中查找了,如不美观发现完全不异的Cursor,就可以直接去取结不美观了,也就就是实现了 Soft Soft Parse.: @' ?2 A' I' A; A3 @, ^
不外在计较解析次数的时辰是只计较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:执行次数]+ X* P1 M# b9 ?! T- T
/*****************************************************/& n& } F' i. M. t) U3 ^
quote from difference between soft parse and hard parse - T0 I. t7 ^5 M& g
-- Thanks for the question regarding "difference between soft parse and hard parse", version 8.1.7.24 G- O: X! V, T& Q) J
originally submitted on 26-Jan-2002 19:17 Eastern US time, last updated 5-Aug-2004 13:07- h7 E, }7 H E4 N1 k
Hi Tom$ f0 }' f' \- i
Can you explain briefly the difference between soft and hard parse?' n! U& f/ |, o4 m& l# A0 ?2 k) B8 A
你能不能给我具体介绍一下软解析与硬解析的区别啊?. [- j5 `& v! V; ` s, _! `# k
and we said……" V2 R% Y$ C" o* a) l0 p
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:这是一个很长的诠释,是年夜我与几个同事一路合著的新书中提掏出来的。
9 b: |4 R" W3 m$ l& M3 B. c( O, ~ Parsing解析6 a- s+ o$ \$ j: Z2 U$ E* z
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仍是数据界说说话),以对这条语句执行各类分歧的语法检测。2 e$ W1 V; r9 ?* N) ^$ ?
The parsing process performs two main functions:解析过程首要有一下两个功能。
# t0 K8 e7 U* R$ [' e 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语句的所有轨则。/ j% A: v+ w2 O( Z
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的值,以及近似的一系列问题。3 w9 p3 t- j; @( x6 B- V; L$ W
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不会出格的指出这条语句没有经由过程语法搜检,它给你返回这条语句对应的错误代码和错误信息。 好比: 下面这条语句没有经由过程语法搜检。 |