Re: The init.ora parameter CURSOR_SPACE_FOR_TIME

Hi,

from the Oracle server tuning manual - quote :-

"This parameter specifies when a shared SQL area can be deallocated from the library cache to make room for a new SQL statement. The default value of this parameter is FALSE, meaning that a shared SQL area can be deallocated from the library cache regardless of whether application cursors associated with its SQL statement are open. The value of TRUE means that a shared SQL area can only be deallocated when all application cursors associated with its statement are closed.

Depending on the value of CURSOR_SPACE_FOR_TIME, Oracle behaves differently when an application makes an execution call. If the value is FALSE, Oracle must take time to check that a shared SQL area containing the SQL statement is in the library cache. If the value is TRUE, Oracle need not make this check because the shared SQL area can never be deallocated while an application cursor associated with it is open. Setting the value of the parameter to TRUE saves Oracle a small amount of time and may slightly improve the performance of execution calls. This value also prevents the deallocation of private SQL areas until associated application cursors are closed.

Do not set the value of CURSOR_SPACE_FOR_TIME to TRUE if there are library cache misses on execution calls. Such library cache misses indicate that the shared pool is not large enough to hold the shared SQL areas of all concurrently open cursors. If the value is TRUE and there is no space in the shared pool for a new SQL statement, the statement cannot be parsed and Oracle returns an error saying that there is no more shared memory. If the value is FALSE and there is no space for a new statement, Oracle deallocates an existing shared SQL area. Although deallocating a shared SQL area results in a library cache miss later, it is preferable to an error halting your application because a SQL statement cannot be parsed.

Do not set the value of CURSOR_SPACE_FOR_TIME to TRUE if the amount of memory available to each user for private SQL areas is scarce. This value also prevents the deallocation of private SQL areas associated with open cursors. If the private SQL areas for all concurrently open cursors fills the user's available memory so that there is no space to allocate a private SQL area for a new SQL statement, the statement cannot be parsed and Oracle returns an error indicating that there is not enough memory."

Therefore I would set CURSOR_SPACE_FOR_TIME = false until your pins and reloads are sorted out. to do this I would increase the size of the shared_pool

Cheers

Neil C

Joseph Li wrote in message <37D763B5.1E76_at_netspace.net.au>...
>The platform is SUN Sparc Solaris 2.6, version of Oracle is 7.3.4.0.1 .
>I am running an application which demands CURSOR_SPACE_FOR_TIME be set
>to TRUE, or it could be set to TRUE by the previous DBA. The instance
>has been up since July 27 1999. I look at the pins and reloads values
>in V$LIBRARYCACHE; sum(pins) is 38855892, sum(reloads) is 486399, so
>sum(reloads) is more than 1% of sum(pins). The reloads came from the
>SQL AREA, TABLE/PROCEDURE, BODY namespaces, mostly from SQL AREA.
>Reading the tuning guide, I came to the conclusion that if
>CURSOR_SPACE_FOR_TIME is TRUE then there should be ZERO reloads, so I
>must be missing something. Can somebody offer a explanation ?
Received on Wed Sep 08 1999 - 08:32:45 CDT