session_cached_cursor

[jdorlon]

Hi Pando,

Your guesses are as good as mine at this point. I'd suggest
you open a tar at this point. let me know what they say if
you do...I'd like to know.

Are you executing this stuff from SQL Plus? I know that
when going through the OCI as a lot of apps (including EZSQL)
do, there are different ways to re-execute a query. Some
make the parse count go up and some don't. I don't know
what does on at a low level though. Then again, I don't
know if SQLPlus uses the OCI or some other interface either

-John

[pando]

Hi I am running from SQL*PLUS since my knowledge on programming (therefore OCI) is limited

Regarding a TAR I will have to see coz I am changing company this Friday so I cant use my current support number

[sambavan]

I don't know how relevent this information to this topic is, the way the oracle handles its open_cursor parameter is the count keeps increasing as long as the session is alive. Once you close that session, the count would get reset to the old value. The reson for that is every time you open a cursor, oracle allocates resources to that cursor. Once you are done with that cursor operation, oracle wouldn't release the resources, instead it would hold on to those resourese to the life time of the session. You might wonder why, it was b'cos the aquring the resources is one of the expensive operations. As a result if you keep acquiring and releasing the resources like memory and etc, it would degrade the performance, instead, when another cursor gets called, oracle would try to use the already acquired resources instead of acquiring new set, but again if the resources are insufficient, it would acquire from the system.

This is how the cursor manipulation goes on in the system level.

Hope I'm in relevence to the subject of discussion.

Sam

[pando]

Hi Sambavan

I understand that concept and that's why I mentioned CLOSE_CACHED_OPEN_CURSORS and SESSION_CACHED_CURSOR should be related to each other, anyway if session_cached_cursor is used to cache the cursor I dont get it since from your theory the cursors are cached anyway, then if the cursor is cached why there is always soft parses with same queries? (If you check the link jdorlon mentioned you can see what I mean)

cheers