# 关于软解析与硬解析,以及session cached cursors (翻译,ref asktom) (摘自CNOUG)

quote from difference between soft parse and hard parse

-- Thanks for the question regarding "difference between soft parse and hard parse", version 8.1.7.2
originally submitted on 26-Jan-2002 19:17 Eastern US time, last updated 5-Aug-2004 13:07

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:

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:

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语句的所有规则.

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的值,以及类似的一系列问题.

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:

Code: [Copy to clipboard]
 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:

Code: [Copy to clipboard]
 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 ? 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 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:

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 and session_cashed_cursor parameter  January 28, 2002

can you explain what means the default value (0) of session_ casched_cursor parameter? Oracle always has a cache of sqls in sga and size of this cache is determinated through init.ora parameter shared_pool_size.

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

Followup:
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.  Consider this example that shows the difference spent when soft parsing without session cached cursors vs soft parsing WITH session cached cursors:

Code: [Copy to clipboard]
 ops$[email]tkyte@ORA817DEV.US.ORACLE.COM[/email]> create table emp as select * from scott.emp;Table created.ops$[email]tkyte@ORA817DEV.US.ORACLE.COM[/email]> create table run_stats ( runid varchar2(15), name varchar2(80), value int );Table created.ops$[email]tkyte@ORA817DEV.US.ORACLE.COM[/email]> 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$[email]tkyte@ORA817DEV.US.ORACLE.COM[/email]> column name format a40ops$[email]tkyte@ORA817DEV.US.ORACLE.COM[/email]> 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 .. 100010      loop11            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 .. 100020      loop21            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 hsecs35 hsecsPL/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:

Code: [Copy to clipboard]
 ops$[email]tkyte@ORA817DEV.US.ORACLE.COM[/email]> 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) > 010 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 1LATCH.redo allocation 30 31 1STAT...consistent gets 5088 5089 1STAT...deferred (CURRENT) block cleanout 2 3 1applicationsSTAT...calls to get snapshot scn: kcmgss 5019 5018 -1STAT...enqueue releases 10 9 -1STAT...execute count 1015 1014 -1STAT...opened cursors cumulative 1015 1014 -1STAT...parse count (total) 1015 1014 -1STAT...session cursor cache count 0 1 1STAT...redo entries 28 27 -1STAT...recursive calls 1180 1179 -1STAT...physical reads 1 0 -1LATCH.direct msg latch 2 0 -2LATCH.session queue latch 2 0 -2LATCH.done queue latch 2 0 -2STAT...free buffer requested 8 6 -2STAT...enqueue requests 11 9 -2LATCH.messages 3 0 -3STAT...db block changes 47 44 -3LATCH.redo writing 3 0 -3LATCH.ksfv messages 4 0 -4STAT...session logical reads 17128 17123 -5LATCH.row cache objects 184 178 -6STAT...db block gets 12040 12034 -6STAT...parse time elapsed 9 3 -6STAT...parse time cpu 13 4 -9STAT...recursive cpu usage 51 38 -13LATCH.cache buffers chains 34315 34335 20STAT...redo size 23900 24000 100STAT...session cursor cache hits 3 1002 999LATCH.shared pool 2142 1097 -1045LATCH.library cache 17361 2388 -1497334 rows selected.ops$[email]tkyte@ORA817DEV.US.ORACLE.COM[/email]>

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...

• 本文已收录于以下专栏：

## PLSQL_解析过程及硬解析和软解析的区别（案例）

• qq_24369113
• 2016年08月05日 16:33
• 765

## Oracle硬解析，软解析，软软解析介绍

Oracle数据库中的CURSOR分为两种类型：Shared Cursor 和 Session Cursor 1,Shared Cursor Oracle里的第一种类型的Cursor就是Shared ...
• waterxcfg304
• 2016年06月25日 14:03
• 1572

## 彻底弄懂oracle硬解析、软解析、软软解析

• guogang83
• 2013年12月11日 17:47
• 5537

## oracle学习笔记 SQL语句解析类型_硬解析_软解析

oracle学习笔记 SQL语句解析类型_硬解析_软解析 shared pool在讲oracle体系结构概述时讲了大体是干什么的， 这节把一些细节讲一下。 一）软件出问题 在...
• xinzhan0
• 2016年07月28日 17:29
• 778

## Oracle 硬解析和软解析

• IndexMan
• 2013年04月12日 11:47
• 1431

## Oracle 硬解析与软解析

--======================= -- Oracle 硬解析与软解析 --======================= Oracle 硬解析与软解析是我们经常遇到的问题，什么情况会...
• fuzhangpeng
• 2013年09月08日 17:40
• 434

## SQL语句解析类型---硬解析和软解析

Oracle的硬解析和软解析 提到软解析(soft prase)和硬解析(hard prase)，就不能不说一下Oracle对sql的处理过程。当你发出一条sql语句交付Oracle，在执...
• holandstone
• 2015年11月30日 10:04
• 3544

## 第三讲--SQL语句硬解析和软解析

• chengonghao
• 2016年03月21日 19:12
• 481

## 共享池之八：软解析、硬解析、软软解析 详解一条SQL在library cache中解析涉及的锁

• q947817003
• 2014年03月17日 18:56
• 2200

## 【相克军】SQL语句解析类型_硬解析_软解析

• Jerry_mg
• 2017年12月02日 15:31
• 58

举报原因： 您举报文章：关于软解析与硬解析,以及session cached cursors (翻译,ref asktom) (摘自CNOUG) 色情 政治 抄袭 广告 招聘 骂人 其他 (最多只允许输入30个字)