Snapper used to require access to DBMS_LOCK, so it could sleep for X seconds between the “before” and “after” performance data snapshots. Now it is possible to get away without using DBMS_LOCK. Instead you will run Snapper twice, once for taking the “before” snapshot, then run your workload and then run Snapper again for taking the “after” snapshot and print the output.
So, the usual way of running snapper is this:
@snapper4 all 5 1 152
This would take 1 5-second performance snapshot SID 152’s V$ views.
With Snapper4 you can use the old way or just add BEGIN or END keywords to the 1st parameter, like this:
@snapper4 all,begin 5 1 152
Whenever using the begin/end syntax, the 2nd and 3rd parameter specifying snapshot duration and count (“5 1”) are just ignored, but they still have to be there right now as snapper requires 4 parameters when called (I’m thinking about changing this in V4.1).
So, let’s try it:
SQL> @snapper4 all,begin 5 1 152 Sampling SID 152 with interval 5 seconds, taking 1 snapshots... SP2-0552: Bind variable "SNAPPER" not declared.
Oops! There’s some error!
In this version of Snapper, if you want to use the before/after manual snapshot mode, you need to run one more command in your session before starting (you need to do it only once when logging in, not every time you run snapper in your session):
SQL> VAR SNAPPER REFCURSOR SQL> SQL> @snapper4 all,begin 5 1 152 Sampling SID 152 with interval 5 seconds, taking 1 snapshots... -- Session Snapper v4.02 BETA - by Tanel Poder ( ) - Enjoy the Most Advanced Oracle Troubleshooting Script on the Planet! :) Taking BEGIN sample ... PL/SQL procedure successfully completed.
The REFCURSOR trick is how I manage to store the “before” snapshot data even though the Snapper anonymous block exits and gives you back control. See the code for more details. I’ve figured out a way to do this in the snapper script itself (it’s not trivial), but haven’t gotten to this yet. Coming soon :)
Anyway, now as we have taken the before snapshot, we can run whatever commands in the session(s) we are measuring (it doesn’t have to be a single session, you can use the usual snapper syntax to pick which sessions to measure).
Once you are ready to take the end snapshot and print the deltas, run this:
SQL> @snapper4 all,end 5 1 152
Sampling SID 152 with interval 5 seconds, taking 1 snapshots...
-- Session Snapper v4.02 BETA - by Tanel Poder ( ) - Enjoy the Most Advanced Oracle Troubleshooting Script on the Planet! :)
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SID, USERNAME , TYPE, STATISTIC , DELTA, HDELTA/SEC, %TIME, GRAPH , NUM_WAITS, WAITS/SEC, AVERAGES
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
152, SYS , STAT, opened cursors cumulative , 13690, 1.35k, , , , , .7 per execution
152, SYS , STAT, opened cursors current , 1, .1, , , , , .05*10^-3 per execution
152, SYS , STAT, user commits , 18, 1.78, , , , , .93*10^-3 per execution
152, SYS , STAT, recursive calls , 234274, 23.11k, , , , , 12.05 per execution
152, SYS , STAT, recursive cpu usage , 848, 83.64, , , , , .04 per execution
152, SYS , STAT, session logical reads , 267980, 26.43k, , , , , 13.78 per execution
... some output removed for brevity ...
152, SYS , WAIT, control file sequential read , 1078, 106.33us, .0%, [ ], 130, 12.82, 8.29us average wait
152, SYS , WAIT, log file sync , 10969, 1.08ms, .1%, [ ], 15, 1.48, 731.27us average wait
152, SYS , WAIT, db file sequential read , 444277, 43.82ms, 4.4%, [W ], 1900, 187.4, 233.83us average wait
152, SYS , WAIT, db file scattered read , 812924, 80.18ms, 8.0%, [W ], 1077, 106.23, 754.8us average wait
152, SYS , WAIT, direct path write temp , 1758, 173.4us, .0%, [ ], 20, 1.97, 87.9us average wait
-- End of Stats snap 1, end=2013-02-18 23:46:25, seconds=10.1
-- End of ASH snap 1, end=, seconds=, samples_taken=0
And that’s it. Note that there’s no ASH output as if snapper returns control back to you between before & after snapshots, it can not sample the V$SESSION view for printing you the “ASH” top.
With the exception of ASH output and ignoring the snapshot_time and snapshot_count parameter values, Snapper allows you to use the full range of syntax for picking which stats do display (gather,sinclude,winclude,tinclude options) and which sessions to measure (sid@inst, user=joe@2, program=sqlplus%@* and even subqueries). You may want to sample some other session(s) this way or you may want to take before and after snapshots of your own session where snapper runs, there are no restrictions, just remember that when taking snapshots of your own session, there’s some snapper’s own activity which shows up in the session’s metrics. If you want a clean snapshot like autotrace is doing, then use one session for running snapper and another session for running the actual test cases.
Note that I just wrote a blog entry about Snapper v4 RAC GV$ view syntax too.
Enjoy! (and remember that Snapper v4 is still beta and likely has some issues)
