This discussion is archived
1 2 Previous Next 21 Replies Latest reply: Dec 11, 2012 4:22 AM by 847540 Go to original post RSS
  • 15. Re: SAP and session_cached_cursors
    jgarry Guru
    Currently Being Moderated
    I believe the answer is that SAP uses an application server with processes attached to the database, so it can just keep them open. Then it has its own data cache to serve the data it gets from the queries. You might want to go through a tracing exerise to see what is really going on. There is some learning curve to that, but there is plenty of help on the net for the basics. Then you start to think like this: http://blog.tanelpoder.com/2009/07/09/identify-the-sql-statement-causing-those-wait-x-lines-in-a-top-truncated-sql-tracefile/ Edit: oops, meant http://blog.tanelpoder.com/2009/07/09/how-to-detect-when-a-cursor-was-closed-from-sql-trace-output/

    But I wouldn't know anything about it. ;)

    Edited by: jgarry on Dec 5, 2012 10:02 AM
  • 16. Re: SAP and session_cached_cursors
    847540 Newbie
    Currently Being Moderated
    Hi,

    Yes indeed there is an application server with a fixed set of connections.
    Below is the output of a production system.
    You see that most sessions have exactly 301 cursors open, that can't be coincidence right?
    But also you see that the 'session cursor cache count' is the maximum value of 50...

    301 open cursors would be very little if SAP really keeps all the cursors open, don't you think?
    + how would you explain all the cached cursors then.

    By the way, i see the same behavior (with also exactly 301 open connections) in some other systems too.

    I'm really thinking of not following the SAP recommendation and setting session_cached_cursors to a high enough value....

    Regards

    SQL> show parameter session_cac

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    session_cached_cursors integer 50
    SQL> select a.value opened_cursors, c.value cached_cursors, s.username, s.sid, s.serial#, s.process
    2 from v$sesstat a,v$statname b, v$sesstat c,v$statname d, v$session s
    3 where a.statistic# = b.statistic# and c.statistic# = d.statistic# and s.sid=a.sid and s.sid=c.sid
    4 and b.name = 'opened cursors current'
    5 and d.name = 'session cursor cache count'
    6 order by 1 desc;

    OPENED_CURSORS CACHED_CURSORS USERNAME SID SERIAL# PROCESS
    -------------- -------------- ------------------------------ ---------- ---------- ------------------------
    301 50 SAPSR3 834 15 29246
    301 3 SAPSR3 1473 7 29252
    301 50 SAPSR3 1377 7 29234
    301 50 SAPSR3 1313 7 29254
    301 50 SAPSR3 1281 7 29247
    301 50 SAPSR3 1249 9 29250
    301 50 SAPSR3 1185 7 29253
    301 50 SAPSR3 1121 7 29228
    301 50 SAPSR3 1057 7 29249
    301 50 SAPSR3 993 7 29248
    301 50 SAPSR3 897 7 29238
    301 50 SAPSR3 803 3 29245
    301 50 SAPSR3 771 3 29251
    301 50 SAPSR3 710 3547 7297
    301 50 SAPSR3 643 3 29241
    301 50 SAPSR3 610 9 29235
    301 12 SAPSR3 482 13 29230
    301 50 SAPSR3 165 28509 21587
    300 50 SAPSR3 644 141 22641
    300 50 SAPSR3 2 41 26484
    300 50 SAPSR3 1218 11 26477
    300 50 SAPSR3 739 57 22648
    300 50 SAPSR3 1443 11 22653
    300 7 SAPSR3 772 15 3492
    300 50 SAPSR3 1251 61 22652
    300 50 SAPSR3 1480 25405 21553
    300 24 SAPSR3 1379 11 22637
    300 50 SAPSR3 930 27 3209
    300 36 SAPSR3 1282 29 26492
    300 50 SAPSR3 1411 11 22647
    300 50 SAPSR3 1059 11 22650
    300 9 SAPSR3 1314 11 26489
    300 50 SAPSR3 1122 21 26475
    300 16 SAPSR3 1346 31 26490
    300 22 SAPSR3 579 61 26491
    300 50 SAPSR3 548 7 31019
    300 50 SAPSR3 1155 2037 22645
    300 50 SAPSR3 452 3 26487
    300 50 SAPSR3 356 11 22643
    300 50 SAPSR3 355 3 26482
    300 50 SAPSR3 292 7 19542
    300 50 SAPSR3 261 11 22639
    300 50 SAPSR3 260 7 460
    300 50 SAPSR3 229 11 22644
    300 50 SAPSR3 196 11 22636
    300 50 SAPSR3 1154 11 26474
    300 10 SAPSR3 163 11 26476
    300 50 SAPSR3 100 11 22642
    300 50 SAPSR3 68 11 22649
    300 50 SAPSR3 34 11 26473
    299 50 SAPSR3 1187 15 22638
    298 3 SAPSR3 1410 17 13270
    298 35 SAPSR3 1507 11 22646
    294 50 SAPSR3 7 54691 31975
    292 50 SAPSR3 33 15 19102
    291 4 SAPSR3 423 6305 16883
    291 50 SAPSR3 1478 59853 20562
    276 50 SAPSR3 66 11 18809
    271 4 SAPSR3 1315 29 31466
    269 1 SAPSR3 678 42165 26638
    268 50 SAPSR3 516 4881 17963
    267 1 SAPSR3 836 52673 545
    265 8 SAPSR3 1442 19 22670
    264 50 SAPSR3 866 545 18897
  • 17. Re: SAP and session_cached_cursors
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Check this article which talk about SAP Cursor
    http://help.sap.com/saphelp_nw04/helpdata/en/c4/3a6e64505211d189550000e829fbbd/content.htm
  • 18. Re: SAP and session_cached_cursors
    jgarry Guru
    Currently Being Moderated
    mbr8500 wrote:
    Hi,

    Yes indeed there is an application server with a fixed set of connections.
    Below is the output of a production system.
    You see that most sessions have exactly 301 cursors open, that can't be coincidence right?
    But also you see that the 'session cursor cache count' is the maximum value of 50...

    301 open cursors would be very little if SAP really keeps all the cursors open, don't you think?
    + how would you explain all the cached cursors then.

    By the way, i see the same behavior (with also exactly 301 open connections) in some other systems too.

    I'm really thinking of not following the SAP recommendation and setting session_cached_cursors to a high enough value....

    Regards
    It's a lot easier to read if you use{noformat}
    {noformat} tags.
    
    Kind of hard to say if that is really meaningful, since if the sessions never close, even sporadic caching would eventually go up to 50.  Since SAP is doing its own caching, this might mean the session caching is just useless extra buffering, or perhaps a side effect of app startup operations.  We would have to know the rate of caching to decide, I think.  I really wouldn't know.  I do know there have been other things done completely wrong.  At least part of the testing would involve watching the rate of growth and then change, from a cold start to a stable system.  And you have to specify what actual performance payback you expect for making a change where they expect you to pay for one of their expensive consultants.
    
    
    SQL> show parameter session_cac
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    session_cached_cursors               integer     50
    SQL> select a.value opened_cursors, c.value cached_cursors, s.username, s.sid, s.serial#, s.process
    2  from v$sesstat a,v$statname b, v$sesstat c,v$statname d,  v$session s
    3  where a.statistic# = b.statistic#  and c.statistic# = d.statistic# and s.sid=a.sid and s.sid=c.sid
    4  and b.name = 'opened cursors current'
    5  and d.name = 'session cursor cache count'
    6  order by 1 desc;
    
    OPENED_CURSORS CACHED_CURSORS USERNAME                              SID    SERIAL# PROCESS
    -------------- -------------- ------------------------------ ---------- ---------- ------------------------
    301             50 SAPSR3                                834         15 29246
    301              3 SAPSR3                               1473          7 29252
    301             50 SAPSR3                               1377          7 29234
    301             50 SAPSR3                               1313          7 29254
    301             50 SAPSR3                               1281          7 29247
    301             50 SAPSR3                               1249          9 29250
    301             50 SAPSR3                               1185          7 29253
    301             50 SAPSR3                               1121          7 29228
    301             50 SAPSR3                               1057          7 29249
    301             50 SAPSR3                                993          7 29248
    301             50 SAPSR3                                897          7 29238
    301             50 SAPSR3                                803          3 29245
    301             50 SAPSR3                                771          3 29251
    301             50 SAPSR3                                710       3547 7297
    301             50 SAPSR3                                643          3 29241
    301             50 SAPSR3                                610          9 29235
    301             12 SAPSR3                                482         13 29230
    301             50 SAPSR3                                165      28509 21587
    300             50 SAPSR3                                644        141 22641
    300             50 SAPSR3                                  2         41 26484
    300             50 SAPSR3                               1218         11 26477
    300             50 SAPSR3                                739         57 22648
    300             50 SAPSR3                               1443         11 22653
    300              7 SAPSR3                                772         15 3492
    300             50 SAPSR3                               1251         61 22652
    300             50 SAPSR3                               1480      25405 21553
    300             24 SAPSR3                               1379         11 22637
    300             50 SAPSR3                                930         27 3209
    300             36 SAPSR3                               1282         29 26492
    300             50 SAPSR3                               1411         11 22647
    300             50 SAPSR3                               1059         11 22650
    300              9 SAPSR3                               1314         11 26489
    300             50 SAPSR3                               1122         21 26475
    300             16 SAPSR3                               1346         31 26490
    300             22 SAPSR3                                579         61 26491
    300             50 SAPSR3                                548          7 31019
    300             50 SAPSR3                               1155       2037 22645
    300             50 SAPSR3                                452          3 26487
    300             50 SAPSR3                                356         11 22643
    300             50 SAPSR3                                355          3 26482
    300             50 SAPSR3                                292          7 19542
    300             50 SAPSR3                                261         11 22639
    300             50 SAPSR3                                260          7 460
    300             50 SAPSR3                                229         11 22644
    300             50 SAPSR3                                196         11 22636
    300             50 SAPSR3                               1154         11 26474
    300             10 SAPSR3                                163         11 26476
    300             50 SAPSR3                                100         11 22642
    300             50 SAPSR3                                 68         11 22649
    300             50 SAPSR3                                 34         11 26473
    299             50 SAPSR3                               1187         15 22638
    298              3 SAPSR3                               1410         17 13270
    298             35 SAPSR3                               1507         11 22646
    294             50 SAPSR3                                  7      54691 31975
    292             50 SAPSR3                                 33         15 19102
    291              4 SAPSR3                                423       6305 16883
    291             50 SAPSR3                               1478      59853 20562
    276             50 SAPSR3                                 66         11 18809
    271              4 SAPSR3                               1315         29 31466
    269              1 SAPSR3                                678      42165 26638
    268             50 SAPSR3                                516       4881 17963
    267              1 SAPSR3                                836      52673 545
    265              8 SAPSR3                               1442         19 22670
    264             50 SAPSR3                                866        545 18897
  • 19. Re: SAP and session_cached_cursors
    847540 Newbie
    Currently Being Moderated
    Hi,

    Thanks for the "code tag" hint ;)

    Well actually I've got the task to optimize the database parameters in some SAP systems.
    There are no complaints about slow systems but that is probably due to the good hardware. So i don't know what the actual performance gain would be... I'm just trying to optimize the system. So i started reading a lot of notes and suddenly encountered the SAP note i was talking about.
    Then i first did some measurements and tests but those confused me, and therefore i decided to open this thread...

    To be more complete about the behavior:
    After starting SAP you can see that there are a few sessions that have about 30 open cursors and about as much cached ones.
    After using the system for a while (few users log in and do some work) you just keep seeing both numbers increase rapidly.
    But the open_cursors never goes above 301, and of course cached_cursors never above the parameter setting.
    What i should have showed in my previous note is that there are a lot of cursor cache hits too. So i think i may assume that the caches are of use then, or not?

    For example:
    SQL> SELECT sum(cach.value) cache_hits, sum(prs.value) all_parses
      2    FROM   v$sesstat cach, v$sesstat prs, v$statname nm1, v$statname nm2
      3   WHERE  cach.statistic# = nm1.statistic#
      4    AND    nm1.name = 'session cursor cache hits'
      AND    prs.statistic#=nm2.statistic#
     AND    nm2.name= 'parse count (total)'
      5    6    7   AND    prs.sid= cach.sid;
    
    CACHE_HITS ALL_PARSES
    ---------- ----------
        523165     294537
    Regards
  • 20. Re: SAP and session_cached_cursors
    jgarry Guru
    Currently Being Moderated
    Wellllllll, if you have no performance problem, a lot of people say doing tuning is Compulsive Tuning Disorder.

    The thing about adjusting tuning parameters in a non-bottleneck situation is you don't know what will happen when you start seeing bottlenecks, and worse, you don't know if you might cause bottlenecks.

    I don't think the change will cause any harm, but I sure wouldn't want to make work for myself by violating setup instructions willy-nilly, because you know when you do need help, their support is going to point at any non-standard settings before they even try to solve whatever problem you have. All vendors do this to some degree or another. However, I've found that some vendors have people (not necessarily on front line support) who are happy to be able to work with someone who actually seems to know what they are doing and wants to understand what is going on. I don't know anything about SAP, but perhaps you'd get lucky asking, especially if others have asked and they have some internal docs about it. People feel good when they can help.

    Sometimes I'm so optimistic I should be selling real estate.
  • 21. Re: SAP and session_cached_cursors
    847540 Newbie
    Currently Being Moderated
    Hi,

    Yes I'll probably do that to figure it out completely. I hope you weren't too optimistic ;)
    But i still have a question for an oracle expert, and that would be you... (assuming that you haven't switched careers to selling real estate :) )

    Am I right when I assume that the caches are of use (and not a little bit) when i see all those "cursor cache hits" ?

    Regards
1 2 Previous Next

Legend

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