今天跟一个网友讨论了这个话题，加上以前的笔记大概整理出了一个脉络，其间参考了不少资料，看了半天的外文，这里跟大家分享，省得大家看E文头晕了 呵呵
嗯，嗯 今天没有白忙啊……
什么叫soft parse？ hard parse？
You Asked (Jump to Tom’s latest followup)
Hi Tom
Can you explain briefly the difference between soft and hard parse?
cheers
and we said…
Here is a long winded answer, it is extracted in part from a new book coming out soon “beginning Oracle programming” that I collaborated on:
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.
The parsing process performs two main functions:（parse 包含语义，语法分析）
o 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.
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.
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:
SQL> select from where 2;
select from where 2
*
ERROR at line 1:
ORA-00936: 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:
SQL> select * from not_a_table;
select * from not_a_table
*
ERROR at line 1:
ORA-00942: 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!
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 （也就是说soft parse 也要进行前面的parse过程–语法语义分析，但是不进行优化和执行计划的生成，这就是soft parse ；而hard 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.
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 5 but will revisit is 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:
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………
从上面可以看出来，soft parse与hard parse的区别，hard parse比soft parse多了两个步骤，而且是两个很耗资源的步骤。所以要尽量避免hard parse，采用soft parse
但是Tom 有句话：”Using bind variables is crucial to the performance; avoiding the soft parse of a query is equally as important”.
用绑定变量是重要的，而避免soft parse 也是同样重要的。为什么这么说呢？乍一看不是与上面矛盾了么？
先看一个例子：
在sqlplus 里面执行一个查询，通过v$sqlarea 视图得知，我们执行了多少次查询就有多少次soft parse，这是为什么呢？
查询的语句如下：
select /*tag*/ * from v$log; //在sql语句里做一个标志
多执行几次
然后查询这条sql语句
select sql_text, executions, parse_calls from v$sqlarea where sql_text like ‘%tag%’;
下面就可以看出这条语句执行了多少次，soft parse了多少次。
这是为什么呢？
how you avoid a soft parse varies by language.
In PLSQL — you get it for free. If you have code that does:
for i in 1 .. 100
loop
for x in ( selct * from t where zzz = i )
loop
…..
the query “select * from t where zzz = :BIND” is parsed ONCE per session — not per execution.
In Java — you would use a prepared/callable statement and prepare it ONCE per session — not once per execution.
In VB — same, don’t be in such a hurry to close those statments, reuse them over and over and over.
In SQLPlus — being a very generic tool — it canot cache open cursors for us like that. You can play with session_cached_cursors to achieve some caching by the server. 在sqlplus 这个参数默认是0，也就是说不缓存cursor
PLSQL being very very specific — can cache the (static) cursors for us. (but not ref cursors)
也就是说 session_cached_cursors 这个参数对 soft parse 产生了一定影响。这个参数The default value of ZERO means that by default, ZERO cursors will be cached for your 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.
下面这个例子将要演示没有缓存cursor的session与缓存了cursor session的区别
Consider this example that shows the difference spent when soft parsing without session cached cursors vs soft parsing WITH session cached cursors:
ops$tkyte@ORA817DEV.US.ORACLE.COM> create table emp as select * from scott.emp;
Table created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> create table run_stats ( runid varchar2(15),
name varchar2(80), value int );
Table created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace view stats
2 as select ‘STAT…’ || a.name name, b.value
3 from v$statname a, v$mystat b
4 where a.statistic# = b.statistic#
5 union all
6 select ‘LATCH.’ || name, gets
7 from v$latch;
View created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> column name format a40
ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
2 l_start number;
3 l_cnt number;
4 begin
5 execute immediate ‘alter session set session_cached_cursors=0′;
6 insert into run_stats select ‘before’, stats.* from stats;
7
8 l_start := dbms_utility.get_time;
9 for i in 1 .. 1000
10 loop
11 execute immediate ’select count(*) from emp’ into l_cnt;
12 end loop;
13 dbms_output.put_line( (dbms_utility.get_time-l_start) || ‘ hsecs’ );
14
15 execute immediate ‘alter session set session_cached_cursors=100′;
16 insert into run_stats select ‘after 1′, stats.* from stats;
17
18 l_start := dbms_utility.get_time;
19 for i in 1 .. 1000
20 loop
21 execute immediate ’select count(*) from emp’ into l_cnt;
22 end loop;
23 dbms_output.put_line( (dbms_utility.get_time-l_start) || ‘ hsecs’ );
24
25 insert into run_stats select ‘after 2′, stats.* from stats;
26 end;
27 /
45 hsecs
35 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:
ops$tkyte@ORA817DEV.US.ORACLE.COM> select a.name, b.value-a.value run1,
c.value-b.value run2,
2 ( (c.value-b.value)-(b.value-a.value)) diff
3 from run_stats a, run_stats b, run_stats c
4 where a.name = b.name
5 and b.name = c.name
6 and a.runid = ‘before’
7 and b.runid = ‘after 1′
8 and c.runid = ‘after 2′
9 and (c.value-a.value) > 0
10 and (c.value-b.value) <> (b.value-a.value)
11 order by abs( (c.value-b.value)-(b.value-a.value))
12 /
NAME RUN1 RUN2 DIFF
—————————————- ———- ———- ———-
LATCH.checkpoint queue latch 3 4 1
LATCH.redo allocation 30 31 1
STAT…consistent gets 5088 5089 1
STAT…deferred (CURRENT) block cleanout 2 3 1
applications
STAT…calls to get snapshot scn: kcmgss 5019 5018 -1
STAT…enqueue releases 10 9 -1
STAT…execute count 1015 1014 -1
STAT…opened cursors cumulative 1015 1014 -1
STAT…parse count (total) 1015 1014 -1
STAT…session cursor cache count 0 1 1
STAT…redo entries 28 27 -1
STAT…recursive calls 1180 1179 -1
STAT…physical reads 1 0 -1
LATCH.direct msg latch 2 0 -2
LATCH.session queue latch 2 0 -2
LATCH.done queue latch 2 0 -2
STAT…free buffer requested 8 6 -2
STAT…enqueue requests 11 9 -2
LATCH.messages 3 0 -3
STAT…db block changes 47 44 -3
LATCH.redo writing 3 0 -3
LATCH.ksfv messages 4 0 -4
STAT…session logical reads 17128 17123 -5
LATCH.row cache objects 184 178 -6
STAT…db block gets 12040 12034 -6
STAT…parse time elapsed 9 3 -6
STAT…parse time cpu 13 4 -9
STAT…recursive cpu usage 51 38 -13
LATCH.cache buffers chains 34315 34335 20
STAT…redo size 23900 24000 100
STAT…session cursor cache hits 3 1002 999
LATCH.shared pool 2142 1097 -1045
LATCH.library cache 17361 2388 -14973
34 rows selected.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
see the significantly REDUCED number of LATCH counts on the library and shared pool. Since a latch is a lock, a lock is a serialization device, serialization implies WAITS — using the session cached cursors will increase scalability and performance as you add more and more users. Its not only FASTER, but more scalable as well…
看上面的红色标记，有缓存cursor的session，对latch的使用比没有缓存cursor的session 少了很多。而latch又是一个需要等待的serialization device，所以很多的soft parse会对系统性能造成较大的影响。
我们要减少soft parse不是相对于hard parse 而言，相对于hard parse，我们当然要选择使用soft parse。这里说到减少使用soft parse是从应用的角度而言，比如，shared pool里面已经缓存了对该语句的执行计划等等信息，由于会话没有缓存cursor，造成需要再一次soft parse，这样就需要避免了。也就是说在应用方面，soft parse 一次，需要执行多次，这样才是性能好的表现。
Tom是这么说的：
Ok, at the SYSTEM LEVEL (eg: what you see with statspack) we want our ratio of SOFT parses to HARD parses to be very very large. We want to be SOFT parsing at the system level (that means our applications as a whole make use of shared sql). That means a given query that was hard parsed once was soft parsed (reused) over and over.
Within an application — we want our PARSE to EXECUTE ratio to be very very LOW. We want to parse a statement once, execute it over and over. It is best if all of our queries in our application are soft parsed (meaning someone else did the hard parse) and I would be suspicious if all queries were hard parsed in an application unless the database had just been restarted.
So, system wide — the ratio of SOFT to HARD should be high (1000 soft parses vs 10 hard parses means each statement was reused 100 times).
In an application — the ratio of PARSE to EXECUTE should be low (1 parse to 100
executes means our app parsed — be it soft or hard — ONCE and executed it 100 times)
为什么session_cached_cursors默认为零？
为什么cursor 一定要是session一级？ 这是一个网友的质疑
要理解cursor的概念请查看，
http://spaces.msn.com/members/ls98zqw/Blog/cns!1p1smsgyqcbBaOubCh6HaahQ!143.entry
或者E:\ora_doc\综合资料\ORACLE非官方教材\细类\cursor\cursor.doc
cursor说白了就是一个用户到SGA的指针。A private sql area 叫做cursor，cursor位于UGA区。Tom说 A cursor points to some shared sql. Shared sql is used by all. A cursor is more then just the plan and such from the shared pool, a cursor is a “state” as well. A cursor is your data — YOUR data. A cursor has your bind variable values — YOUR values. 红色的部分说明 a private sql area的作用，标示state，存放数据，存放执行计划，存放绑定变量。
Just remember - a cursor is a SESSION thing, its your state, your values, your result set. the plan — thats a SHARED thing, its the part of the shared pool that all sessions can share. 但是在dedicated 模式下，怎么shared？这个时候cursor位于uga，uga位于pga，不是共享的。我个人理解是这个时候我们cursor内的东西都是从sga里拿到的，这些内容原来都是共享的。
了解了cursor的内容之后，可能跟据上面的内容，我们会问？既然要减少soft parse的使用，我们为什么不在会话里设置session_cached_cursors这个参数为非零，是否这个参数有什么负面影响呢?
Tom 回答说：cursor caching increases the amount of memory your session will consume. It is a tuning feature that trades increased RAM for significantly increased performance. I personally see no downside to it. 的确这个参数设置非零对性能有提升，但是要损失内存。要想消除soft parse，使用pl/sql。
需要注意的是使用session_cached_cursors并不能消除soft parse，在tkprof仍然可以看出softparse 增加了1，但是这次执行的parse 是softer soft parse，
从我们上面比较session cache cursor 与session 不 cache cursor的例子中有一行可以看到
STAT…parse count (total) 1015 1014 -1
这两个并没有什么区别，也就是说并没有通过修改session_cached_cursors参数来来达到消除soft parse的目的。只是减少了latch的征用。
而对于soft parse和softer soft parse而言，这两个步骤并不相同。
in a soft parse you have
1. syntax check
2. semantics check
3. go to shared pool and look if the SQL is there
in a softer soft parse you have
3. go to shared pool and look if the SQL is there
====================================
v$open_cursor与session_cached_cursors
v$open_cursor是oracle提供的一张查看每个session打开过的cursor的视图,它的表结构如下
SQL> desc v$open_cursor
Name Null? Type
—————————————————– ——– ————————————
SADDR RAW(4)
SID NUMBER
USER_NAME VARCHAR2(30)
ADDRESS RAW(4)
HASH_VALUE NUMBER
SQL_TEXT VARCHAR2(60)
当我们执行一条sql语句的时候,我们将会在shared pool产生一个library cache object,cursor就是其中针对于sql语句的一种library cache object.另外我们会在pga有一个cursor的拷贝,同时在客户端会有一个statement handle,这些都被称为cursor,在v$open_cursor里面我们可以看到当前打开的cursor和pga内cached cursor.
session_cached_cursor
这个参数限制了在pga内session cursor cache list的长度,session cursor cache list是一条双向的lru链表,当一个session打算关闭一个cursor时,如果这个cursor的parse count超过3次,那么这个cursor将会被加到session cursor cache list的MRU端.当一个session打算parse一个sql时,它会先去pga内搜索session cursor cache list,如果找到那么会把这个cursor脱离list,然后当关闭的时候再把这个cursor加到MRU端.session_cached_cursor提供了快速软分析的功能,提供了比soft parse更高的性能.
