Re: session cursor cache

Brian wrote:
> I am aware of the v$ views relating to cursors. Is it possible to view
> the contents of the entire cache? The 3 paragraphs in the 10g
> Performance Tuning Guide does not go into much detail.
>
> I am looking for real life answers from people who tune and monitor the
> cursor cache.
>
> Sometimes web pages have a very slight hesitation. Sometime they
> don't. This is what tuning is all about. I am curious why sometimes
> the cache hit rate fluctuates from 30% to 100%. I would like to see
> which cursors from my application are in the cache and which system
> cursors are being cached. Possibly I might better understand why all
> my cursors are not being cached as a set of pooled sessions execute the
> same code over and over again.
>
> The slight hesitations I'm working on are hardly noticeable. The
> users are very happy with performance. Can it get better? Sure.

This page http://ixora.com.au/scripts/library.htm#session_cursor_cache and the short description in the script give some useful advice on when you have enough session_cached_cursors.

Unfortunately I don't know any way to find the SQLs inside session cursor cache. I thought v$sql_cursor or dumping UGA for a session would do but it didn't work for me. Anyway, if 'session cursor cache hits' statistic keeps going up, that cache is used. If it goes up in a particular session (through v$sesstat), that session is using it. If 'session cursor cache count' goes up, then new cursors are added to that cache. 220 for the cache size is not necessarily too large.

To find whether the database is the cause of your slight hesitation, keep watching v$session_wait for non-idle events. I assume your application is using bind variables. If cursors are still not shared, see if their version_count in v$sqlarea goes up and what column in v$sql_shared_cursor shows 'N'. Although this is a different issue than session cached cursors, it's worth spending time on.

Yong Huang Received on Wed Oct 19 2005 - 11:02:18 CDT