First of all, i'm new to SAP so please forgive me if I post something wrong :)
In the SAP documentation (Note 912389 - Cursor parameters on Oracle) i read the following about SESSION_CACHED_CURSORS:
"Use in the SAP environment:
Since each SQL statement is opened only once and the cursor is kept open in the standard system, you do not need to cache cursors at session level for a repeated parsing. There is no point, therefore, in setting SESSION_CACHED_CURSORS to a value greater than 0."
After reading other documentations i understand that the cursors are cached by Oracle in the shared area even without session_cached_cursors set, and because SAP works with a fixed pool of connections (1 SAP session uses a free oracle connection from the pool whenever it needs one, so you can be using all the sessions in a mixed way) there is no benefit in setting session_cached_cursors.
But i'm not sure if I understand correctly because i would think that, even with a connection pool, there would be a whole lot of cursors which can be cached at session level afterall because the same oracle sessions are going to be used more than once so it could be that the cached cursors in the fixed oracle session are beneficial for the "SAP request" at that time.
We would probably have the same cursor cached at multiple sessions then, but regardless of the "wasted" memory wouldn't that be faster than setting session_cached_cursors to 0 and using the shared cursor cache ??
We are working with 10.2.0.4 and 18.104.22.168 databases.
Thanks in advance!
Oracle provides some parameters that control the behavior of cursor activities. In the regular SAP environment, SQL statements with bind variables are transferred to the database for parsing. Consequently, the CURSOR_SHARING parameter is irrelevant and that is why it should not be set explicitly.
and in sap this is pointless so you have to cache your cursor to avoid any error .
The parameter session-cached-cursor is basically meant for softer soft parse , a little more better way to optimize the query than even the soft parsing. But , this would kick in if the query is parsed for more than 4 times else not. So if SAP says that in its system any query won't get parsed for more than once than may be its okay to set the parameter to 0 but I am not really sure that this would truly be the case . But I have never used SAP so I may be wrong too.
Indeed statements are always sent to the db with bind variables.
So cursor_sharing = similar would have no influence in an SAP system...
I understand that. And i also understand that having session_cached_cursors > 0 would be more benificial, in a non-SAP environment... (because of the softer soft parse aman talks about..)
But then the note you posted (http://www.stechno.net/sap-notes.html?view=sapnote&id=912389) also says that in the SAP system any query won't get parsed for more than once, so session_cached_cursors have no use...
Why you say then that having it set to value 0 is no good?
I can't follow...
Edited by: mbr8500 on 4-dec-2012 4:29
also in the same document
"Since each SQL statement is opened only once and the cursor is kept open in the standard system, you do not need to cache cursors at session level for a repeated parsing. There is no point, therefore, in setting SESSION_CACHED_CURSORS to a value greater than 0. " ;)
Yes indeed, but you said "Please open the link that i provide it with my post , Set the parameter to 0 is not good solution"
And the document says the opposite...
I think we're misunderstanding each other :)
What is the right thing to do now?
I already knew that information. Setting session_cached_cursors > 0 is indeed better in a normal system.
However, SAP says that all cursors are kept open. So setting session_cached_cursors > 0 has no use...
But how can SAP keep all the cursors open?
And why do i see lots of "session cursor cache hits" then?
Thanks for your reply.
Let me repeat everything i learned until now.
"Since each SQL statement is opened only once and the cursor is kept open in the standard system, you do not need to cache cursors at
session level for a repeated parsing. There is no point, therefore, in setting SESSION_CACHED_CURSORS to a value greater than 0"
So we now have 2 options:
A) Maybe, when SAP says "any SQL statement is opened only once and the cursor is kept open in the standard system", they mean that there is only 1 "parse call" per query to the database (which would be a hard parse because it's always the first time) and all cursors are kept open in the database system, then it would be true that session_cached_cursors > 0 has no use.
However, if this is true how can you explain all the open and cached cursors for every session, and lots of 'session cursor cache hits' in my database? Also if SAP keeps ALL the cursors open, this would take a huge amount of memory i think, right?
B) Maybe, when SAP says "any SQL statement is opened only once and the cursor is kept open in the standard system", they mean that no hard parse is ever needed more than once (true because bind variables are always used) and that the parsed cursors are kept "open" in the SAP system's cursor cache (by caching them and letting similar query's use the same parsed statement with bind variables). So all parse calls to the database would be "soft parses", except for the hard parse during the first time a query is launched. If that is the case then session_cached_cursors > 0 would be beneficial, because a "softer soft parse" is better than the "soft parses".
Option B would clarify my questions from option A, but the note says 'There is no point, therefore, in setting SESSION_CACHED_CURSORS to a value greater than 0' ...
Please can anyone explain this?
Edited by: mbr8500 on 4-dec-2012 8:43
The parameter works for the UGA that's within the PGA. Within UGA, the cursors would be kept in an "open cursor cache" and from there , would be moved to a "closed cursor cache" . The parameter comes into the play that if the cursor is in the "closed cursor cache" as well, it would be having a pointer stored within it that would point to the hash chain of the library cache. This would save you from doing the "soft parse" and would be considered as a "hit" for the "session cursor cache hit" . Now, as I said before too, I don't know how SAP works but it would be massive thing if they are keeping the cursor opened all teh time. If they are really doing it, may be than the parameter is of no use but then, it would be very interesting to know that how they are doing it.
Thanks for your answer. I follow you completely. I have raised the question on SAP community network and will keep you informed of what the outcome is.
But if it would be true that every cursor is kept open there are some things that i don't understand then.
On our databasse, session_cached_cursors is now set to 100.
When i query the usage of these cached cursors i see that every session uses them, some sessions even use the maximum of 100.
Output can be seen in previous post.