SESSION_CACHED_CURSORS真的能避免soft parse吗？

clevernby

在《maclean讲调优精要》中关于“SQL解析的流程”描述中有：

1. 
   
2. 是否SESSION_CACHED_CURSORS>0-------------------------是---------------V        游标已解析过无需再解析
   
3. 且游标在server端的会话游标缓存中？                                                     |
   

复制代码

该观点与DIS404e中存在出入
Chapter 3 Page 29

1. 
   
2. Four Application Types
   
3. Type 1 : Bind variables not used
   
4. Type 2 : (Open-Parse-Bind-Execute-Fetch-Close) on each execution
   
5. Type 3 : Open once + (Parse-Bind-Execute-Fetch) on each execution + Close once
   
6. Type 4 : (Open-Parse-Bind) once + (Execute-Fetch) on each execution + Close once
   

复制代码

Chapter 3 Page 31

1. 
   
2. Type 2
   
3. Setting SESSION_CACHED_CURSORS sufficiently high will  improve response time and scalability because the server will keep cursors cached after they are closed.
   

复制代码

Type 2存在的问题是(1)游标重复的open/close；(2)重复的soft parse；限制了性能扩展
Oracle建议通过设置SESSION_CACHED_CURSORS来阻止游标Close，避免反复Open。但是！它可以避免soft parse吗？

Chapter 3 Page 32

1. 
   
2. Type 3
   
3. Such applications have relatively good res ponse time, but scalability is still limited due to the soft parsing. This case is slightly better than type 2 due to the avoidance of repeated cursor open/close.
   
4. Setting SESSION_CACHED_CURSORS sufficiently high will not generally bring any further improvements  because cursors are not being closed.
   

复制代码

Type3类应用，游标的Open/Close均只发生一次，但Parse-Bind-Execute-Fetch在每次执行语句时依然会发生，也就是Type3无法避免重复的parse，即使设置更大的SESSION_CACHED_CURSORS也不行，因为这个参数只能避免游标的反复Open。

那SESSION_CACHED_CURSORS参数到底是干什么的呢？

repentance

Type 2存在的问题是(1)游标重复的open/close；(2)重复的soft parse；限制了性能扩展
Oracle建议通过设置SESSION_CACHED_CURSORS来阻止游标Close，避免反复Open。但是！它可以避免soft parse吗？
---------------------->
你在这个地方的理解可能有些偏差，设置SESSION_CACHED_CURSORS不能阻止游标Close。应该是设置SESSION_CACHED_CURSORS后，在游标关闭后，仍然能够快速soft soft  parse并execute，提升了响应时间。

Type3类应用，游标的Open/Close均只发生一次，但Parse-Bind-Execute-Fetch在每次执行语句时依然会发生，也就是Type3无法避免重复的parse，即使设置更大的SESSION_CACHED_CURSORS也不行，因为这个参数只能避免游标的反复Open。
----------->
游标只OPEN一次，后续每次执行无需在Open，所以可以直接execute。这个场景下设置SESSION_CACHED_CURSORS没有意义。

Stone

repentance的解释很好，我再适当找点儿资料补充下 :)

http://www.orafaq.com/node/758

Session cached cursors
There are two main initialization parameters that affect cursors, and many folks get them confused. One is OPEN_CURSORS, and the other is SESSION_CACHED_CURSORS.

SESSION_CACHED_CURSORS sets the number of cached closed cursors each session can have. You can set SESSION_CACHED_CURSORS to higher than OPEN_CURSORS, lower than OPEN_CURSORS, or anywhere in between. This parameter has no effect on ora-1000's or on the number of cursors a session will have open. Conversely, OPEN_CURSORS has no effect on the number of cursors cached. There's no relationship between the two parameters.

If SESSION_CACHED_CURSORS is not set, it defaults to 0 and no cursors will be cached for your session. (Your cursors will still be cached in the shared pool, but your session will have to find them there.) If it is set, then when a parse request is issued, Oracle checks the library cache to see whether more than 3 parse requests have been issued for that statement. If so, Oracle moves the session cursor associated with that statement into the session cursor cache. Subsequent parse requests for that statement by the same session are then filled from the session cursor cache, thus avoiding even a soft parse. (Technically, a parse can't be completely avoided; a "softer" soft parse is done that's faster and requires less CPU.)

In the session cursor cache, Oracle manages the cached cursors using a LRU list. Once more than SESSION_CACHED_CURSORS closed cursors are cached, Oracle starts dropping cached cursors off the LRU end of the list whenever it needs to make room to cache a new cursor.

另外AskTom的一篇相关解答可以帮助理解更多的信息：

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:865497961356

Good luck,
Stone

clevernby

Stone 发表于 2013-7-19 12:05
repentance的解释很好，我再适当找点儿资料补充下 :)

http://www.orafaq.com/node/758

非常感谢，之前理解确实有误。

Maclean Liu(刘相兵

It turns out that in several cases, working on tuning issue, the high value
for soft parse statistic is due to a call of the OCIHandleAlloc and
OCIHandelPrepare inside the execution loop. For example change the
order of the OCI call in the test_trace.c as follows
ALTER_SESSION("..set session_cached_cursors=0....
....
for(Idx<0;....){ /* Execution Loop */
STMT_ALLOC(...) ==> OCIHandleAlloc
PREPARE(...) ==> OCIStmtPrepare
.... Here comes the bind section
and the define section OCIBindByName
OCIDefineByPos....
STMT_EXECUTE ==> OCIStmtExecute
... Here comes the fetch section
OCIStmtFetch..........
STMT_FREE(...) ==> OCIHandleFree
} /* End of execution loop */
In this case the trace produce by the event 10049 will look as follow




KGLTRCLCK kglget hd = 0x0x2a5cc754 KGL Lock addr = 0x0x28764a4c mode = N
KGLTRCPIN kglpin hd = 0x0x2a5cc754 KGL Pin addr = 0x0x287a0608 mode = S
KGLTRCPIN kglpndl hd = 0x0x2a5cc754 KGL Pin addr = 0x0x287a0608 mode = S
KGLTRCLCK kgllkdl hd = 0x0x2a5cc754 KGL Lock addr = 0x0x28764a4c mode = N
KGLTRCLCK kgllkdl hd = 0x0x2a4cc2f0 KGL Lock addr = 0x0x2871af70 mode = N
KGLTRCLCK kglget hd = 0x0x2a4cc2f0 KGL Lock addr = 0x0x287b7414 mode = N
KGLTRCLCK kglget hd = 0x0x2a5cc754 KGL Lock addr = 0x0x287168e8 mode = N
KGLTRCPIN kglpin hd = 0x0x2a5cc754 KGL Pin addr = 0x0x28753d70 mode = S
KGLTRCPIN kglpndl hd = 0x0x2a5cc754 KGL Pin addr = 0x0x28753d70 mode = S
KGLTRCLCK kgllkdl hd = 0x0x2a5cc754 KGL Lock addr = 0x0x287168e8 mode = N
KGLTRCLCK kgllkdl hd = 0x0x2a4cc2f0 KGL Lock addr = 0x0x287b7414 mode = N
KGLTRCLCK kglget hd = 0x0x2a4cc2f0 KGL Lock addr = 0x0x2877b270 mode = N
KGLTRCLCK kglget hd = 0x0x2a5cc754 KGL Lock addr = 0x0x287d9fa4 mode = N
....
This represents a continuous opening and closing the same cursor. Running
several programs like this one usually leads to a rise in the Parse CPU to
Parse Elapsed statistic.
Turning on the session_cached_cursors
...
ALTER_SESSION("alter session set session_cached_cursors=100",....
...
will reduce the number of kgl calls; In that case the trace file generated
by the event 10049 will look as it would be generated by the program written
calling the statement allocation, the statement prepare and the statement
free outside the execution loop.
....
KGLTRCLCK kglget hd = 0x0x2a4cc2f0 KGL Lock addr = 0x0x2871b2b0 mode = N
KGLTRCLCK kglget hd = 0x0x2a5cc754 KGL Lock addr = 0x0x287236c8 mode = N
KGLTRCPIN kglpin hd = 0x0x2a5cc754 KGL Pin addr = 0x0x28734448 mode = S
KGLTRCPIN kglpndl hd = 0x0x2a5cc754 KGL Pin addr = 0x0x28734448 mode = S
KGLTRCPIN kglpin hd = 0x0x2a5cc754 KGL Pin addr = 0x0x28767548 mode = S
KGLTRCPIN kglpndl hd = 0x0x2a5cc754 KGL Pin addr = 0x0x28767548 mode = S
KGLTRCPIN kglpin hd = 0x0x2a5cc754 KGL Pin addr = 0x0x2876816c mode = S
KGLTRCPIN kglpndl hd = 0x0x2a5cc754 KGL Pin addr = 0x0x2876816c mode = S
KGLTRCPIN kglpin hd = 0x0x2a5cc754 KGL Pin addr = 0x0x28767740 mode = S
KGLTRCPIN kglpndl hd = 0x0x2a5cc754 KGL Pin addr = 0x0x28767740 mode = S
KGLTRCPIN kglpin hd = 0x0x2a5cc754 KGL Pin addr = 0x0x28734448 mode = S
KGLTRCPIN kglpndl hd = 0x0x2a5cc754 KGL Pin addr = 0x0x28734448 mode = S