Update: Since this article was written I’ve released multiple updates to my Snapper script, so you should also read other Snapper related articles in my blog and my new website:
- http://tech.e2sn.com/oracle-scripts-and-tools/ (usage and download)
- http://blog.tanelpoder.com/?s=snapper (previous snapper articles)
—————————————
I have released the version 1.06 of Oracle Session Snapper.
If you don’t know what Snapper is, check out those blog entries first:
- http://blog.tanelpoder.com/2007/08/20/oracle-session-snapper-real-time-session-level-performance-stats-for-dbas/
- http://blog.tanelpoder.com/2007/08/26/oracle-session-snapper-part-2-getting-most-out-of-snapper/
Improvements:
- Ability to report instancewide Latch and Enqueue get deltas (useful for testing code efficiency in dev environments where there’s no other activity going on)
- Header format and header options have changed slightly – for making it simpler to quickly paste & graph the data in Excel
- Some bugs have been fixed (and probably new ones introduced)
How to read Snapper output?
Here are the column definitions (sorry for crappy formatting, I need to do some CSS tweaking once I get a chance):
| HEAD | Output row header. If row contains normal data then DATA, otherwise WARN or ERROR. |
| SID | SID of session this row belongs |
| SNAPSHOT START | Snapshot period start |
| SECONDS | Seconds of time in this snapshot (SNAPSHOT START + SECONDS = SNAPSHOT END |
| TYPE | Type of statistic reported. Can be WAIT for wait events, TIME for Time Model statistics (10g+), STAT for V$SESSTAT statistics, ENQ for enqueue gets, LAT for latch gets. Note that ENQ and LAT are instance wide statistics while others are session-specific. |
| STATISTIC | The name of statistic (or wait event or latch or enqueue) |
| DELTA | The delta of statistic values between snapshot end and begin (in other words, how much the counters were incremented during snapshot period) |
| D/SEC | Delta per Second |
| HDELTA | Human readable delta (e.g. using k instead of 1000, M instead of million, ms instead of milliseconds, us instead of microseconds etc. |
| HD/SEC | Human readable Delta per second |
Examples:
SQL> @snapper out,gather=stwel 10 1 &mysid
-- Session Snapper v1.06 by Tanel Poder ( http://www.tanelpoder.com )
---------------------------------------------------------------------------------------------------------------------------------------------
HEAD, SID, SNAPSHOT START , SECONDS, TYPE, STATISTIC , DELTA, D/SEC, HDELTA, HD/SEC
---------------------------------------------------------------------------------------------------------------------------------------------
DATA, 16, 20071206 20:11:31, 10, ENQ, CF , 3, 0, 3, .3
DATA, 16, 20071206 20:11:31, 10, ENQ, JD , 2, 0, 2, .2
DATA, 16, 20071206 20:11:31, 10, LAT, messages , 24, 2, 24, 2.4
DATA, 16, 20071206 20:11:31, 10, LAT, enqueues , 10, 1, 10, 1
DATA, 16, 20071206 20:11:31, 10, LAT, enqueue hash chains , 10, 1, 10, 1
DATA, 16, 20071206 20:11:31, 10, LAT, channel operations parent latch , 6, 1, 6, .6
DATA, 16, 20071206 20:11:31, 10, LAT, mostly latch-free SCN , 2, 0, 2, .2
DATA, 16, 20071206 20:11:31, 10, LAT, lgwr LWN SCN , 2, 0, 2, .2
DATA, 16, 20071206 20:11:31, 10, LAT, active checkpoint queue latch , 3, 0, 3, .3
DATA, 16, 20071206 20:11:31, 10, LAT, checkpoint queue latch , 144, 14, 144, 14.4
DATA, 16, 20071206 20:11:31, 10, LAT, cache buffers chains , 12, 1, 12, 1.2
DATA, 16, 20071206 20:11:31, 10, LAT, redo writing , 9, 1, 9, .9
DATA, 16, 20071206 20:11:31, 10, LAT, redo allocation , 2, 0, 2, .2
DATA, 16, 20071206 20:11:31, 10, LAT, library cache , 12, 1, 12, 1.2
DATA, 16, 20071206 20:11:31, 10, LAT, library cache pin , 12, 1, 12, 1.2
DATA, 16, 20071206 20:11:31, 10, LAT, session timer , 4, 0, 4, .4
DATA, 16, 20071206 20:11:31, 10, LAT, SQL memory manager latch , 3, 0, 3, .3
DATA, 16, 20071206 20:11:31, 10, LAT, SQL memory manager workarea list latch , 201, 20, 201, 20.1
DATA, 16, 20071206 20:11:31, 10, STAT, recursive calls , 1, 0, 1, .1
DATA, 16, 20071206 20:11:31, 10, STAT, recursive cpu usage , 4, 0, 4, .4
DATA, 16, 20071206 20:11:31, 10, STAT, session pga memory , 295704, 29570, 295.7k, 29.57k
DATA, 16, 20071206 20:11:31, 10, STAT, calls to get snapshot scn: kcmgss , 1, 0, 1, .1
DATA, 16, 20071206 20:11:31, 10, STAT, workarea executions - optimal , 30, 3, 30, 3
DATA, 16, 20071206 20:11:31, 10, STAT, execute count , 1, 0, 1, .1
DATA, 16, 20071206 20:11:31, 10, STAT, sorts (memory) , 15, 2, 15, 1.5
DATA, 16, 20071206 20:11:31, 10, STAT, sorts (rows) , 6783, 678, 6.78k, 678.3
DATA, 16, 20071206 20:11:31, 10, WAIT, PL/SQL lock timer , 9999793, 999979, 10s, 999.98ms
-- End of snap 1
PL/SQL procedure successfully completed.
Snapper Version 1.06 introduces two new flags for gather option, for reference, I list all of them:
- s - v$sesstat statistic counters
- t -v$sess_time_model times
- w - v$session_event waits
- e - v$enqueue_stat enqueue get counters
- l - v$latch latch get counters (gets + immediate_gets)
Note that the output above doesn’t have long enqueue names, only short abbreviations. This is because the script was run in an Oracle 9.2 database. If you run it on 10g+ then Snapper can use V$LOCK_TYPE view for fetching long enqueue names as seen below:
SQL> @snapper out,gather=e 10 1 &mysid
-- Session Snapper v1.06 by Tanel Poder ( http://www.tanelpoder.com )
---------------------------------------------------------------------------------------------------------------------------------------------
HEAD, SID, SNAPSHOT START , SECONDS, TYPE, STATISTIC , DELTA, D/SEC, HDELTA, HD/SEC
---------------------------------------------------------------------------------------------------------------------------------------------
DATA, 123, 20071206 20:48:47, 10, ENQ, CF - Controlfile Transaction , 4, 0, 4, .4
DATA, 123, 20071206 20:48:47, 10, ENQ, JD - Job Queue Date , 2, 0, 2, .2
DATA, 123, 20071206 20:48:47, 10, ENQ, JS - Job Scheduler , 76, 8, 76, 7.6
DATA, 123, 20071206 20:48:47, 10, ENQ, TM - DML , 18, 2, 18, 1.8
-- End of snap 1
PL/SQL procedure successfully completed.
Note once more that the latch and enqueue statistics come from instance-wide aggregated sourcee (V$ENQUEUE_STAT and V$LATCH) thus may not be meaningful for diagnosing a single session problem in a busy database. The other stats (S,T and W) come from session-specific sources. For this reason the E and L flags are disabled by default (if you omit gather parameter).
For further info and usage, take a look inside the snapper.sql script itself.
Thanks go out to Adrian Billington for improving my crappy PL/SQL code ;-)
There are quite a few things still in TODO list, so keep tuned for updates!
Kudos Tanel! BTW, how long did it take to create the fish/rod?
Thanks!
The fish thing went fast! I searched for “GIF to ASCII converter” and found this link:
http://www.degraeve.com/img2txt.php
:)
Very interesting, Tanel – so much that I’m considering writing similar zero-install versions of many of my package-based utilities.
It’s true, even when working with friendly production DBAs, asking them to install the most recent version is a nuisance … that can be easily avoided by using anonymous blocks.
I was thinking by the way, that if one writes the anonymous block using bind variables containing the script input parameters, the anonymous block is going to be found again in the shared pool, so avoiding the pl/sql reparsing – hence the anonymous block becomes a “runtime procedure” in a way.
Assuming the compiled version of the block is cached into the shared pool – I’m only 99% sure about that.
Alberto,
I also considered bind variables but I found them to actually limit the functionality of my script with providing no value (as hard parsing the script when executed manually every now an then should not be a problem). This might have a little bigger impact on RAC.. but again I normally run snapper few times per day only during my normal working day…
Not using a bind variable (and using a sqlplus variable instead) gives good flexibility for specifying the SIDs.
I use this in snapper script:
… WHERE SID IN (&snapper_sid) …
This allows me to set snapper_sid = 100 or set it to 100,101,102 or set it to “select sid from v$session where username = ‘BLAH’”
A plain bind variable would allow you to one and only one exact value for SID
Btw, both anonymous blocks and compiled PL/SQL are cached in shared pool. In fact when you call a stored proc from a client, this is done through an anonymous block as well ( e.g. BEGIN myproc(xyz); END; )
Cheers,
Tanel.
Tanel,
yes, agreed, I was just thinking at a an utility of mine, composed of about 80 stored procedures (contained in 5 packages) – I’m currently rewriting it as zero-install script, and in my case, it might make sense to have (at most) 80 anonymous mini-blocks called by a single small “main” anonymous block (that might change every time the script is run) instead of a giant main block containing 80 internal procedures.
In general, one could think about writing a whole suit of zero-install scripts, possibly reusing some of the mini-blocks. It would be a zero-install script with nearly the same performance (and shared pool footprint), and almost the same flexibility, of the original package-dependent script.
Thanks for the infos about caching – I said I was “99% sure” conservatively, since I never verified it myself. You have spared me some tedious work :)
Tanel,
its possible the output to be imported as CSV or TXT? I thought using your xls script is possible but at the moment doesnt work in Excel 2007
xls script should work with excel 2007 as well, but as snapper uses dbms_output for output (instead of returning a cursor resultset in table form) then the html wouldnt be generated correctly anyway.
Use snapper’s pagesize=-1 / pagesize=0 option and trace option (to trace into a file) and then load it to excel. I have deliberately formatted snappers output in a CSV fashion (commas separate columns) for exactly this reason.
thx Tanel,
I will follow your advice
Best Regards,
Kostas