This discussion is archived
1 2 Previous Next 21 Replies Latest reply: Dec 11, 2012 4:22 AM by 847540 RSS

SAP and session_cached_cursors

847540 Newbie
Currently Being Moderated
Hi all,

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 11.2.0.3 databases.

Thanks in advance!
  • 1. Re: SAP and session_cached_cursors
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Check
    http://osamamustafa.blogspot.com/2012/11/opencursor-in-oracle.html
  • 2. Re: SAP and session_cached_cursors
    847540 Newbie
    Currently Being Moderated
    Hi,

    Ok i understand now that the cursors will never be in that cache because they remain open. Session_cached_cursors is only for the closed cache.

    The Note says: "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... ".
    So the "standard system" (by which they mean SAP itself i think?) keeps every cursor always open?
    How is that possible, wouldn't that use too much memory?

    And how do you explain that the cached_cursors are all used then by some sessions (see query output)?:

    Thanks for helping me clarify this!

    SQL> show parameter cursor

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    cursorfeatures_enabled integer 10
    cursor_sharing string EXACT
    cursor_space_for_time boolean FALSE
    open_cursors integer 800
    session_cached_cursors integer 100

    SQL> /

    OPENED_CURSORS CACHED_CURSORS USERNAME SID SERIAL#
    -------------- -------------- ------------------------------ ---------- ----------
    266 100 SAPSR3 483 14
    263 68 SAPSR3 481 6
    262 100 SAPSR3 491 3
    187 100 SAPSR3 489 3
    105 99 SAPSR3 479 13
    70 99 SAPSR3 486 7
    65 72 SAPSR3 492 6
    47 100 SAPSR3 461 5
    35 99 SAPSR3 471 3
    31 30 SAPSR3 480 3
    29 87 SAPSR3 490 18
    27 72 SAPSR3 487 3
    23 61 506 1
    23 48 SAPSR3 484 8
    12 97 SAPSR3 466 4
    11 68 SAPSR3 476 11
    11 49 SAPSR3 493 10
    7 13 SAPSR3 488 5
    7 13 SAPSR3 477 10
    7 13 SAPSR3 482 3
    7 13 SAPSR3 475 5
    7 13 SAPSR3 478 2
    7 13 SAPSR3 485 5
    6 32 SAPSR3 473 27
    5 13 SAPSR3 462 5
    5 13 SAPSR3 465 1
    5 13 SAPSR3 467 3
    5 13 SAPSR3 470 6
    5 13 SAPSR3 472 2
    5 13 SAPSR3 474 2
    4 33 507 1
    1 2 503 38
    1 5 SYS 469 9821
    0 0 511 1
    0 0 512 1
    0 0 464 12
    0 0 513 1
    0 0 515 1
    0 0 514 1
    0 0 516 1
    0 1 505 1
    0 0 501 1
    0 0 500 1
    0 0 499 1
    0 7 510 1
    0 61 509 1
    0 0 495 1
  • 3. Re: SAP and session_cached_cursors
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    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 .

    http://www.stechno.net/sap-notes.html?view=sapnote&id=912389
  • 4. Re: SAP and session_cached_cursors
    Aman.... Oracle ACE
    Currently Being Moderated
    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.

    Aman....
  • 5. Re: SAP and session_cached_cursors
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Hi Aman

    Please open the link that i provide it with my post , Set the parameter to 0 is not good solution
  • 6. Re: SAP and session_cached_cursors
    847540 Newbie
    Currently Being Moderated
    Hi,

    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...

    Regards

    Edited by: mbr8500 on 4-dec-2012 4:29
  • 7. Re: SAP and session_cached_cursors
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    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. " ;)
  • 8. Re: SAP and session_cached_cursors
    847540 Newbie
    Currently Being Moderated
    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?

    Regards
  • 10. Re: SAP and session_cached_cursors
    847540 Newbie
    Currently Being Moderated
    Hi,

    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?

    Regards
  • 11. Re: SAP and session_cached_cursors
    847540 Newbie
    Currently Being Moderated
    Hi,

    Thanks for your reply.

    Let me repeat everything i learned until now.

    SAP says:
    "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?

    Regards

    Edited by: mbr8500 on 4-dec-2012 8:43
  • 12. Re: SAP and session_cached_cursors
    Aman.... Oracle ACE
    Currently Being Moderated
    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.

    Aman....
  • 13. Re: SAP and session_cached_cursors
    847540 Newbie
    Currently Being Moderated
    Hi Aman,

    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.

    Regards,
    Mathieu
  • 14. Re: SAP and session_cached_cursors
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    we need SAP expert to told us what is the purpose of keeping cursor is open !!! i will check some document on google regarding to that
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points