Oracle Session Snapper, part 2: Getting most out of Snapper

Tanel Poder

2007-08-26

The main design goal of Session Snapper was that it should not require any changes to be made into database.

And to achieve this goal, I was even willing to sacrifice some functionality.

So, for example there is no sorting capability in Snapper output. It would have been easy to create an SQL Type to database, use that as session statistics storage and query results out using an order by on statistics delta column – giving you (probably) most significant resource consumers first. But I didn’t do this as it would have violated the no-database-change-whatsoever design goal. (This problem could however be solved using manual sorting in PL/SQL code as done in Adrian Billington’s variation of runstats utility: http://www.oracle-developer.net/utilities.php )

The second design goal was that snapper should work with as little privileges as possible. So far it requires execute access only on DBMS_LOCK, DBMS_OUTPUT and read access on few V$ views.

However if you run the snapper using “out” option then it will use DBMS_OUTPUT.PUT_LINE for sending data back to the client. Unfortunately the client (SQLPlus in this case) calls DBMS_OUTPUT.GET_LINES to retrieve the output only when the previous database call has finished. Of course this makes sense, because otherwise some asynchronous call capability to Oracle server would be needed, which either has to interrupt the server process somehow for processing the GET_LINES, or another server process should be started for output feed.

Anyway, we don’t have such functionality in Oracle, so this means that if you run snapper in a loop, you will get the output only when the loop finishes and returns control to sqlplus. This is not good enough for having continuous real-time view of your session(s) performance.

Those who have looked into the header or sourcecode of my Session Snapper script have seen that if you use “trace” option and tail -f on that tracefile, you can get continuous real-time Snapper output to your screen (in addition to having that info saved in logfile). This functionality uses DBMS_SYSTEM.KSDWRT() procedure though. This means again that in order to get real-time output, you need to either grant execute priv on DBMS_SYSTEM to your monitoring user or run Snapper as SYS. Both those options violate the design goals of Snapper and may be unacceptable in real life production systems.

So, to combat that, I wrote a script which still gives us real-time Snapper output without need to access DBMS_SYSTEM.

The snapperloop.sql script is actually very simple. It will just run snapper in 1-iteration loop many times in row:

The code looks like this:

set feedback off
prompt
prompt -- Running Snapper 42,000 times in a row, press CTRL+C to cancel...
prompt
@snapper "&1" "&2" "&3" "&4"
@snapper "&1" "&2" "&3" "&4"
@snapper "&1" "&2" "&3" "&4"
@snapper "&1" "&2" "&3" "&4"
@snapper "&1" "&2" "&3" "&4"
@snapper "&1" "&2" "&3" "&4"
(repeat the above line for thousands of times)

...
...
...

So you can just run SnapperLoop exactly how you would run Snapper, just with 1-iteration per run (the third parameter = 1) and let SnapperLoop re-execute Snapper for you. (By the way, note how you can select SIDs dynamically based on a custom query in last parameter):

Sys@Lin11g> @snapperloop out 1 1 "select sid from v$mystat where rownum = 1"

-- Running Snapper 42,000 times in a row, press CTRL+C to cancel...

-- Session Snapper v1.03 by Tanel Poder (  )

--------------------------------------------------------------------------------------------------------------------------------------------
--        SID, SNAPSHOT START   , SECONDS  , TYPE, STATISTIC                               ,         DELTA,      D/SEC,     HDELTA,   HD/SEC
--------------------------------------------------------------------------------------------------------------------------------------------
DATA,     120, 20070826 11:53:17,         4, STAT, opened cursors cumulative               ,             1,          0,          1       .25
DATA,     120, 20070826 11:53:17,         4, STAT, recursive calls                         ,             3,          1,          3       .75
DATA,     120, 20070826 11:53:17,         4, STAT, recursive cpu usage                     ,           285,         71,        285     71.25
DATA,     120, 20070826 11:53:17,         4, STAT, CPU used by this session                ,           285,         71,        285     71.25
DATA,     120, 20070826 11:53:17,         4, STAT, session pga memory                      ,        131072,      32768,    131.07k    32.77k
DATA,     120, 20070826 11:53:17,         4, STAT, calls to get snapshot scn: kcmgss       ,             1,          0,          1       .25
DATA,     120, 20070826 11:53:17,         4, STAT, workarea executions - optimal           ,             4,          1,          4         1
DATA,     120, 20070826 11:53:17,         4, STAT, parse count (total)                     ,             1,          0,          1       .25
DATA,     120, 20070826 11:53:17,         4, STAT, execute count                           ,             1,          0,          1       .25
DATA,     120, 20070826 11:53:17,         4, STAT, sorts (memory)                          ,             2,          1,          2        .5
DATA,     120, 20070826 11:53:17,         4, STAT, sorts (rows)                            ,           610,        153,        610     152.5
DATA,     120, 20070826 11:53:17,         4, TIME, repeated bind elapsed time              ,            14,          4,       14us     3.5us
DATA,     120, 20070826 11:53:17,         4, TIME, parse time elapsed                      ,            37,          9,       37us    9.25us
DATA,     120, 20070826 11:53:17,         4, TIME, PL/SQL execution elapsed time           ,           171,         43,      171us   42.75us
DATA,     120, 20070826 11:53:17,         4, TIME, DB CPU                                  ,       3046537,     761634,      3.05s  761.63ms
DATA,     120, 20070826 11:53:17,         4, TIME, sql execute elapsed time                ,       3047255,     761814,      3.05s  761.81ms
DATA,     120, 20070826 11:53:17,         4, TIME, DB time                                 ,       3047257,     761814,      3.05s  761.81ms
DATA,     120, 20070826 11:53:17,         4, WAIT, PL/SQL lock timer                       ,       1000122,     250031,         1s  250.03ms
--  End of snap 1

-- Session Snapper v1.03 by Tanel Poder (  )

--------------------------------------------------------------------------------------------------------------------------------------------
--        SID, SNAPSHOT START   , SECONDS  , TYPE, STATISTIC                               ,         DELTA,      D/SEC,     HDELTA,   HD/SEC
--------------------------------------------------------------------------------------------------------------------------------------------
DATA,     120, 20070826 11:53:24,         3, STAT, opened cursors cumulative               ,             1,          0,          1       .33
DATA,     120, 20070826 11:53:24,         3, STAT, recursive calls                         ,             3,          1,          3         1
DATA,     120, 20070826 11:53:24,         3, STAT, recursive cpu usage                     ,           282,         94,        282        94
DATA,     120, 20070826 11:53:24,         3, STAT, CPU used by this session                ,           282,         94,        282        94
DATA,     120, 20070826 11:53:24,         3, STAT, calls to get snapshot scn: kcmgss       ,             1,          0,          1       .33
DATA,     120, 20070826 11:53:24,         3, STAT, workarea executions - optimal           ,             4,          1,          4      1.33
DATA,     120, 20070826 11:53:24,         3, STAT, parse count (total)                     ,             1,          0,          1       .33
DATA,     120, 20070826 11:53:24,         3, STAT, execute count                           ,             1,          0,          1       .33
DATA,     120, 20070826 11:53:24,         3, STAT, sorts (memory)                          ,             2,          1,          2       .67
DATA,     120, 20070826 11:53:24,         3, STAT, sorts (rows)                            ,           609,        203,        609       203
DATA,     120, 20070826 11:53:24,         3, TIME, repeated bind elapsed time              ,            12,          4,       12us       4us
DATA,     120, 20070826 11:53:24,         3, TIME, parse time elapsed                      ,            39,         13,       39us      13us
DATA,     120, 20070826 11:53:24,         3, TIME, PL/SQL execution elapsed time           ,           178,         59,      178us   59.33us
DATA,     120, 20070826 11:53:24,         3, TIME, DB CPU                                  ,       3061535,    1020512,      3.06s     1.02s
DATA,     120, 20070826 11:53:24,         3, TIME, sql execute elapsed time                ,       3064089,    1021363,      3.06s     1.02s
DATA,     120, 20070826 11:53:24,         3, TIME, DB time                                 ,       3064090,    1021363,      3.06s     1.02s
DATA,     120, 20070826 11:53:24,         3, WAIT, PL/SQL lock timer                       ,       1000295,     333432,         1s  333.43ms
--  End of snap 1

Or if you don’t want to see the headers (and the annoying tanelpoder.com banner ;) you can run:

Sys@Sol01> @snapperloop out,pagesize=0 1 1 "select sid from v$mystat where rownum = 1"

-- Running Snapper 42,000 times in a row, press CTRL+C to cancel...

DATA,     471, 20070826 12:32:30,         2, STAT, opened cursors cumulative               ,             1,          1,          1        .5
DATA,     471, 20070826 12:32:30,         2, STAT, opened cursors current                  ,             1,          1,          1        .5
DATA,     471, 20070826 12:32:30,         2, STAT, recursive calls                         ,             2,          1,          2         1
DATA,     471, 20070826 12:32:30,         2, STAT, recursive cpu usage                     ,            39,         20,         39      19.5
DATA,     471, 20070826 12:32:30,         2, STAT, CPU used by this session                ,            38,         19,         38        19
DATA,     471, 20070826 12:32:30,         2, STAT, session pga memory                      ,        131072,      65536,    131.07k    65.54k
DATA,     471, 20070826 12:32:30,         2, STAT, session pga memory max                  ,        131072,      65536,    131.07k    65.54k
DATA,     471, 20070826 12:32:30,         2, STAT, calls to get snapshot scn: kcmgss       ,             1,          1,          1        .5
DATA,     471, 20070826 12:32:30,         2, STAT, cursor authentications                  ,             1,          1,          1        .5
DATA,     471, 20070826 12:32:30,         2, STAT, workarea executions - optimal           ,             2,          1,          2         1
DATA,     471, 20070826 12:32:30,         2, STAT, parse count (total)                     ,             1,          1,          1        .5
DATA,     471, 20070826 12:32:30,         2, STAT, execute count                           ,             1,          1,          1        .5
DATA,     471, 20070826 12:32:30,         2, STAT, sorts (memory)                          ,             2,          1,          2         1
DATA,     471, 20070826 12:32:30,         2, STAT, sorts (rows)                            ,           460,        230,        460       230
DATA,     471, 20070826 12:32:30,         2, TIME, repeated bind elapsed time              ,             4,          2,        4us       2us
DATA,     471, 20070826 12:32:30,         2, TIME, parse time elapsed                      ,           678,        339,      678us     339us
DATA,     471, 20070826 12:32:30,         2, TIME, PL/SQL execution elapsed time           ,          1904,        952,      1.9ms     952us
DATA,     471, 20070826 12:32:32,         1, STAT, opened cursors cumulative               ,             1,          1,          1         1
DATA,     471, 20070826 12:32:32,         1, STAT, opened cursors current                  ,             1,          1,          1         1
DATA,     471, 20070826 12:32:32,         1, STAT, recursive calls                         ,             2,          2,          2         2
DATA,     471, 20070826 12:32:32,         1, STAT, recursive cpu usage                     ,            26,         26,         26        26
DATA,     471, 20070826 12:32:32,         1, STAT, CPU used by this session                ,            26,         26,         26        26
DATA,     471, 20070826 12:32:32,         1, STAT, session pga memory                      ,        131072,     131072,    131.07k   131.07k
DATA,     471, 20070826 12:32:32,         1, STAT, calls to get snapshot scn: kcmgss       ,             1,          1,          1         1
DATA,     471, 20070826 12:32:32,         1, STAT, workarea executions - optimal           ,             2,          2,          2         2
DATA,     471, 20070826 12:32:32,         1, STAT, parse count (total)                     ,             1,          1,          1         1
DATA,     471, 20070826 12:32:32,         1, STAT, execute count                           ,             1,          1,          1         1
DATA,     471, 20070826 12:32:32,         1, STAT, sorts (memory)                          ,             2,          2,          2         2
DATA,     471, 20070826 12:32:32,         1, STAT, sorts (rows)                            ,           465,        465,        465       465
DATA,     471, 20070826 12:32:32,         1, TIME, repeated bind elapsed time              ,             4,          4,        4us       4us
DATA,     471, 20070826 12:32:32,         1, TIME, parse time elapsed                      ,            39,         39,       39us      39us
DATA,     471, 20070826 12:32:32,         1, TIME, PL/SQL execution elapsed time           ,           148,        148,      148us     148us
DATA,     471, 20070826 12:32:32,         1, WAIT, PL/SQL lock timer                       ,        980231,     980231,   980.23ms  980.23ms
DATA,     471, 20070826 12:32:34,         1, STAT, opened cursors cumulative               ,             1,          1,          1         1
DATA,     471, 20070826 12:32:34,         1, STAT, opened cursors current                  ,             1,          1,          1         1
DATA,     471, 20070826 12:32:34,         1, STAT, recursive calls                         ,             2,          2,          2         2
DATA,     471, 20070826 12:32:34,         1, STAT, recursive cpu usage                     ,            25,         25,         25        25
DATA,     471, 20070826 12:32:34,         1, STAT, CPU used by this session                ,            25,         25,         25        25
DATA,     471, 20070826 12:32:34,         1, STAT, session pga memory                      ,        131072,     131072,    131.07k   131.07k
DATA,     471, 20070826 12:32:34,         1, STAT, calls to get snapshot scn: kcmgss       ,             1,          1,          1         1
DATA,     471, 20070826 12:32:34,         1, STAT, workarea executions - optimal           ,             2,          2,          2         2
DATA,     471, 20070826 12:32:34,         1, STAT, parse count (total)                     ,             1,          1,          1         1
DATA,     471, 20070826 12:32:34,         1, STAT, execute count                           ,             1,          1,          1         1
DATA,     471, 20070826 12:32:34,         1, STAT, sorts (memory)                          ,             2,          2,          2         2
DATA,     471, 20070826 12:32:34,         1, STAT, sorts (rows)                            ,           465,        465,        465       465
DATA,     471, 20070826 12:32:34,         1, TIME, repeated bind elapsed time              ,             4,          4,        4us       4us
DATA,     471, 20070826 12:32:34,         1, TIME, parse time elapsed                      ,            39,         39,       39us      39us
DATA,     471, 20070826 12:32:34,         1, TIME, PL/SQL execution elapsed time           ,           304,        304,      304us     304us
DATA,     471, 20070826 12:32:34,         1, WAIT, PL/SQL lock timer                       ,        983087,     983087,   983.09ms  983.09ms

You can even spool the output to a file for further analysis if you like. And thanks to formatting it should be fairly easy to post-process it.

You can download snapperloop.sql in zipped file from https://github.com/tanelpoder/tpt-oracle/blob/master/snapperloop.zip


  1. I am finally close to launching the completely rebuilt 2024 versions of my Linux & AOT classes in my Learning Platform! (Updates to SQL Tuning class in H2 2024):
    Advanced Oracle SQL Tuning training. Advanced Oracle Troubleshooting training, Linux Performance & Troubleshooting training. Check them out!
  2. Get randomly timed updates by email or follow Social/RSS