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?
Comment by TedC — December 8, 2007 @ 1:06 pm
Thanks!
The fish thing went fast! I searched for “GIF to ASCII converter” and found this link:
http://www.degraeve.com/img2txt.php
:)
Comment by tanelp — December 8, 2007 @ 3:03 pm
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.
Comment by Alberto Dell'Era — December 23, 2007 @ 3:28 am
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.
Comment by tanelp — December 26, 2007 @ 12:40 am
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 :)
Comment by Alberto Dell'Era — December 28, 2007 @ 12:36 am
[...] @ 11:44 pm Wolfgang Breitling has written a snapper.pl script – Perl version of my original snapper.sql one. Check it [...]
Pingback by Perl version of Snapper « Tanel Poder’s blog: Core IT for geeks and pros — December 28, 2007 @ 11:44 pm
[...] the SEQ# value changing fast, you can sample and calculate wait time deltas from V$SESSION_EVENT as Snapper [...]
Pingback by Systematic application troubleshooting in Unix « Tanel Poder’s blog: Core IT for geeks and pros — January 5, 2008 @ 8:06 pm
[...] know lot of senior DBAs and Developers have their own home grown monitoring and tuning scripts like Tanel Poder, so please feel free to comment on this post about [...]
Pingback by Performance Tuning Protocol « H.Tonguç YILMAZ Oracle Blog — January 26, 2008 @ 9:46 pm
[...] If you don’t want to install the sesstat package, you can run a lightweight version called the session-snapper. He wrote the tool because of an interesting blog about the lack of session level information. You can [download the script here|http://www.tanelpoder.com/files/scripts/snapper.sql]. He’s written a few blogs about snapper. [...]
Pingback by Hotsos Symposium 2008 Day Two « Seven Seconds — March 5, 2008 @ 12:18 pm
[...] the latch waited for the most. You still need to start your diagnosis from wait interface, using Snapper or WaitProf for example. Only if these tools show significant latch waits, you should drill down [...]
Pingback by Advanced Oracle Troubleshooting Guide, Part 7: Sampling latch holder statistics using LatchProf « Tanel Poder’s blog: Core IT for geeks and pros — July 8, 2008 @ 7:58 pm
[...] the troublemaking session, then what next? One way forward is looking into V$SESSTAT counters using Snapper tool. Depending on what latch is the problematic one, you would look for different stats like [...]
Pingback by Advanced Oracle Troubleshooting Guide, Part 8: Even more detailed latch troubleshooting using LatchProfX « Tanel Poder’s blog: Core IT for geeks and pros — July 23, 2008 @ 1:11 am
[...] Oracle Session Snapper v1.06 released [...]
Pingback by Advanced Oracle Troubleshooting @OOW 2008 presentation slides and scripts « Tanel Poder’s blog: Core IT for geeks and pros — September 26, 2008 @ 6:57 am
[...] Танела Подера для отслеживания работы сессии. А вот сам пост, описывающий Change Log и прочие [...]
Pingback by Пара ссыл по APEX’у и не только « Тетрадочка DBA — May 22, 2009 @ 1:32 pm