關於遊標的一些理解

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/，如需轉載，請註明出處，否則將追究法律責任。