session_cached_cursors..

[Sureshy]

Hi Friends..

Cab anyone tell me the size in bytes of a session cached cursor ?

Would this depend on the size opf he cursor when explicitly created in pl/sql or is there a set size ?

Am I right in thinkng session cached cursors are stored in pga of session ?

Thanks

Suresh

[ggnanaraj]

They are cached in the sessions PGA. I would presume that there is no fixed size for each number of session_cached_cursors defined. It should depend on the size of the cursor declared in PL/SQL. So, it is a variable size.


SESSION_CACHED_CURSORS can be set in situations where a user repeatedly parses the same statements. This can occur in FORMS based application if users often switch between forms. Every time a user switches to a new form all the sql statements opened for the old form will be closed. The session_cached_cursors parameter will cause closed cursors to be cached within the session so that a subsequent call to parse the statement will bypass the parse phase. One thing to be careful about is that if this parameter is set to a high value, the amount of fragmentation in the shared pool may be increased.

[Edited by ggnanaraj on 10-01-2001 at 09:01 AM]

[Sureshy]

Hi ggnanara...

Thanks for your help.

I am setting to 100 due to repeated parsing of cursors identified in utlbstat/estat.

I will increase shared pool size to accomodate.

Thanks Again

Suresh

[Sureshy]

Hi ggnanaraj..

If session_cached_cursors are stored in sessions PGA, how does this affect the shared pool ?

Also.. After Setting this parameter I started receiving ora-04031 (shared pool size inadequate),Can you explain this.

Many Thanks

Suresh

[julian]

Originally posted by Sureshy
Hi ggnanaraj..

If session_cached_cursors are stored in sessions PGA, how does this affect the shared pool ?

Also.. After Setting this parameter I started receiving ora-04031 (shared pool size inadequate),Can you explain this.

Many Thanks

Suresh

The parameter can increase fragmentation in the shared pool, that's how :-)

About the error: avoid it by increaseing the shared pool size or apply the latest patch. There are several bugs related to the 4031 error, spinning, etc.

Do you use 8i?

[Sureshy]

Hi Julian..

The question I was asking was how could shared pool be affected when according to documenttion cached cursors are held in PGA not shared pool.

Answered my own question by researching metalink..

In reference to SESSION_CACHED_CURSORS causing shared pool fragmentation. Actually, what is stored in the PGA is a cache of references to cursors which themselves are in the Library Cache. There are documents you might have read concerning fragmentation can be caused if the parameter is set "too high".

This is how shared pool can become fragmented because of this parameter and this also answers why ora 04031 errors were received when this parameter was set.

Suresh

[julian]

What value do you have for SESSION_CACHED_CURSORS?
More than 40-50?

[Sureshy]

50 session_cached_cursors

120 users

Increased shared pool from 60Mb to 100Mb
Seems to have cured ora-04031
average parse calls/shared_cursors_cache_hits is around 55%

Suresh