{"id":292,"date":"2021-01-18T22:58:53","date_gmt":"2021-01-18T22:58:53","guid":{"rendered":"https:\/\/maboc.nl\/?p=292"},"modified":"2021-01-18T22:58:53","modified_gmt":"2021-01-18T22:58:53","slug":"session-statistics","status":"publish","type":"post","link":"https:\/\/maboc.nl\/?p=292","title":{"rendered":"Session statistics"},"content":{"rendered":"

You can have a look at v$sesstat (joined with v$staname) to see the value of some statistics at this moment. What is a little more difficult to see is how statistics change over time. So I wrote a script for it.<\/p>\n

What it does:<\/p>\n

    \n
  1. Gather all statistics, of one or all sessions, and put them in an array.<\/li>\n
  2. Sleep the desired amount of seconds<\/li>\n
  3. Gather all statistics again anput them in another array<\/li>\n
  4. Create one big array of the data<\/li>\n
  5. Sort the data<\/li>\n
  6. Display the data<\/li>\n<\/ol>\n

    First show some output.
    \nFirst example I would like to see the change of statistics of session 460 (sid) over 1 minute (60 seconds):<\/p>\n

    (2021-01-18 23:03:26) SYS@CDB01_BLOEMKOOL > @stats 460 60\r\n460       non-idle wait count                               User                            555            564         9\r\n460       non-idle wait time                                User                            703            744        41\r\n460       scheduler wait time                               User                            592            634        42\r\n460       session logical reads                             User                           6422           6568       146\r\n460       consistent gets                                   Cache                          6419           6565       146\r\n460       consistent gets from cache                        Cache                          4620           4766       146\r\n460       consistent gets pin                               Cache                          3817           3963       146\r\n460       consistent gets pin (fastpath)                    Cache                          3697           3843       146\r\n460       no work - consistent read gets                    Debug                          5464           5610       146\r\n460       table scan blocks gotten                          SQL                            4366           4512       146\r\n460       buffer is pinned count                            Cache SQL                    167753         192019     24266\r\n460       table scan rows gotten                            SQL                          560149         584684     24535\r\n460       table scan disk non-IMC rows gotten               SQL                          560149         584684     24535\r\n460       logical read bytes from cache                     Cache                      37871616       39067648   1196032\r\n(2021-01-18 23:04:40) SYS@CDB01_BLOEMKOOL ><\/pre>\n
    A second example: Show the change of statistics of all sessions over a period of 1 second:<\/p>\n
    (2021-01-18 23:11:11) SYS@CDB01_BLOEMKOOL > @stats % 1\r\n49        opened cursors cumulative                         User                           1001           1002         1\r\n403       recursive calls                                   User                         271229         271230         1\r\n49        non-idle wait time                                User                            118            119         1\r\n396       background timeouts                               Debug                        268428         268429         1\r\n399       background timeouts                               Debug                        788510         788511         1\r\n403       background timeouts                               Debug                        268385         268386         1\r\n409       background timeouts                               Debug                        268505         268506         1\r\n410       background timeouts                               Debug                        268290         268291         1\r\n412       background timeouts                               Debug                        268428         268429         1\r\n419       background timeouts                               Debug                        268428         268429         1\r\n428       background timeouts                               Debug                        268415         268416         1\r\n429       background timeouts                               Debug                        268412         268413         1\r\n410       enqueue conversions                               Enqueue                      273803         273804         1\r\n49        calls to get snapshot scn: kcmgss                 RAC                             520            521         1\r\n...\r\n...\r\n...\r\n409       physical write total bytes                        Cache                    9487723520     9487726592      3072\r\n409       cell physical IO interconnect bytes               SQL                      9607496704     9607499776      3072\r\n409       redo write finish time                            Debug                    4294994669     4294998879      4210\r\n409       redo write time (usec)                            Debug                    4296653531     4296657744      4213\r\n409       redo write total time                             Debug                    4305361173     4305365398      4225\r\n410       physical write total bytes                        Cache                   13430349824    13430398976     49152\r\n407       session pga memory                                User                        4778872        4844408     65536\r\n410       physical read total bytes                         Cache                   25358295040    25358360576     65536\r\n410       cell physical IO interconnect bytes               SQL                     38788644864    38788759552    114688\r\n49        session uga memory                                User                        2810200        2991720    181520\r\n49        session uga memory max                            User                        2875688       13222264  10346576\r\n49        session pga memory                                User                        5006336       50291712  45285376\r\n49        session pga memory max                            User                        7103488       60580864  53477376\r\n(2021-01-18 23:18:43) SYS@CDB01_BLOEMKOOL ><\/pre>\n
    Actually the usage is pretty straight forward:
    \nThe first argument is the session to want to see the statistics off. Or a % for all sessions. The second argument is the number of seconds of the interval.<\/p>\n
    You can find the script here<\/a>.<\/p>\n
    Take some care with the usage of this script. In my RDBMS 19 there are 2036 named statistics. If you use % for the sid selection, and you have a lot of sessions, the script will get very busy.<\/p>\n
    \r\n(2021\/01\/18 23:55:11) SYS@CDB01_BLOEMKOOL > select count(*) from v$statname;\r\n\r\n  COUNT(*)\r\n----------\r\n      2036\r\n\r\n(2021\/01\/18 23:55:24) SYS@CDB01_BLOEMKOOL >\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
    You can have a look at v$sesstat (joined with v$staname) to see the value of some statistics at this moment. What is a little more difficult to see is how statistics change over time. So I wrote a script for it. What it does: Gather all statistics, of one or all sessions, and put them […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5,49],"tags":[24,50],"class_list":["post-292","post","type-post","status-publish","format-standard","hentry","category-oracle","category-sql","tag-oracle","tag-statistics"],"_links":{"self":[{"href":"https:\/\/maboc.nl\/index.php?rest_route=\/wp\/v2\/posts\/292","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/maboc.nl\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/maboc.nl\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/maboc.nl\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/maboc.nl\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=292"}],"version-history":[{"count":11,"href":"https:\/\/maboc.nl\/index.php?rest_route=\/wp\/v2\/posts\/292\/revisions"}],"predecessor-version":[{"id":303,"href":"https:\/\/maboc.nl\/index.php?rest_route=\/wp\/v2\/posts\/292\/revisions\/303"}],"wp:attachment":[{"href":"https:\/\/maboc.nl\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=292"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/maboc.nl\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=292"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/maboc.nl\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=292"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}