关于游标的一些理解

Open cursors
----------------------
Open cursors take up space in the shared pool, in the library cache. To keep a renegade session from filling up the library cache, or clogging the CPU with millions of parse requests, we set the parameter OPEN_CURSORS.

OPEN_CURSORS sets the maximum number of cursors each session can have open, per session. For example, if OPEN_CURSORS is set to 1000, then each session can have up to 1000 cursors open at one time. If a single session has OPEN_CURSORS # of cursors open, it will get an ora-1000 error when it tries to open one more cursor.

The default is value for OPEN_CURSORS is 50, but Oracle recommends that you set this to at least 500 for most applications. Some applications may need more, eg. web applications that have dozens to hundreds of users sharing a pool of sessions. Tom Kyte recommends setting it around 1000.

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.

Why cache cursors?
-------------------------------
The obvious advantage to caching cursors by session is reduced parse times, which leads to faster overall execution times. This is especially so for applications like Oracle Forms applications, where switching from one form. to another will close all the session cursors opened for the first form. Switching back then opens identical cursors. So caching cursors by session really cuts down on reparsing.

There's another advantage, though. Since a session doesn't have to go looking in the library cache for previously parsed SQL, caching cursors by session results in less use of the library cache and shared pool latches. These are often points of contention for busy OLTP systems. Cutting down on latch use cuts down on latch waits, providing not only an increase in speed but an increase in scalability.

Tuning SESSION_CACHED_CURSORS

If you choose to use SESSION_CACHED_CURSORS to help out an application that is continually closing and reopening cursors, you can monitor its effectiveness via two more statistics in v$sesstat. The statistic "session cursor cache hits" reflects the number of times that a statement the session sent for parsing was found in the session cursor cache, meaning it didn't have to be reparsed and your session didn't have to search through the library cache for it. You can compare this to the statistic "parse count (total)"; subtract "session cursor cache hits" from "parse count (total)" to see the number of parses that actually occurred.

20:02:11 SQL> select name,value from v$sysstat where name like '%parse%';

NAME                                  VALUE
-------------------------------- ----------
parse time cpu                   4254191346
parse time elapsed                 19063371
parse count (total)              4457467754
parse count (hard)                   801307
parse count (failures)               181169

5 rows selected.

20:02:34 SQL> select name,value from v$sysstat where name like '%cursor%';

NAME                                  VALUE
-------------------------------- ----------
opened cursors cumulative        4438485198
opened cursors current           4344831533
session cursor cache hits        4288360277
session cursor cache count         40387437
cursor authentications               360794

5 rows selected.

20:02:49 SQL> select 4288360277/4457467754 from dual;

4288360277/4457467754
---------------------
           .962061985

1 row selected.

同时又看了看http://www.dba-oracle.com/t_sess ... rs_optimal_size.htm

对于session_cached_cursors还有个解释：
The session_cached_cursors parameter is used to reduce the amount of parsing with SQL statements that use host variables. 

The session_cached_cursors parameter has a default value of 50, and increasing the value of session_cached_cursors will requires a larger shared_pool_size to cache the cursors.

那么我们都知道session_cached_cursors是存放在PGA里面，为什么我们还需要扩大shared_pool_size呢?

首先我们要知道设置了session_cached_cursors，那么在PGA中的session cursor cache可以缓存具有3次request并且已经close的cursor。因为游标是缓存在共享池Shared Pool中,但是session必须在共享池中去查找他们,session cached cursors可以省略掉去共享池中查找游标这一步.这样可以解决很多诸如shared pool latch,library cache latch的问题。那么设置了session_cached_cursors游标就可以cached到PGA里面，那么过程呢？其实首先这个游标是从shared pool中copy到PGA的，因为不仅仅一个session在使用这个游标，其他session可能也要使用到这个游标，因此通常情况下，shared pool也要把游标给cache住。那么shared pool就是copy的源。

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12361284/viewspace-160108/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12361284/viewspace-160108/