1 2 Previous Next 21 Replies Latest reply: Dec 11, 2012 6:22 AM by 847540 Go to original post RSS
      • 15. Re: SAP and session_cached_cursors
        jgarry
        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
          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
            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
              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
                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
                  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
                    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