I have fixed most of the bugs that showed up during the Snapper launch party session and uploaded the new version (v4) of Snapper here:
I have uploaded some of my videos to https://www.youtube.com/tanelpoder:
I have not updated the snapper documentation yet, but here are the main improvements:
- RAC support (query remote instance’s sessions performance data via GV$)
- Manually specified “before and after” snapshot support. No need to use DBMS_LOCK to sleep for a certain number of seconds between snapshots
- Useful averages in the right side of the output (e.g. average bytes sent/received per roundtrip etc). I will keep adding new averages over the coming weeks.
RAC support
I have kept the syntax backwards-compatible, so when you run Snapper like this …
@snapper ash 5 1 user=tanel
… it will still query only local instance’s sessions.
But if you want to measure all tanel’s sessions in let’s say instance #2 only, you can just add @2 to the session filter condition:
@snapper ash 5 1 user=tanel@2
Or if you want all instances then use @*:
@snapper ash 5 1 user=tanel@*
If you want to measure a single session with sid 1234 in instance #2, you can do it in two ways:
@snapper ash 5 1 1234@2
Or use the inst_id,sid tuple syntax:
@snapper ash 5 1 (2,1234)
Note that the brackets are important, as otherwise snapper thinks that you want to measure SID 2 and SID 1234 in the local instance. You can pass multiple inst_id,SID tuples to the snapper though:
@snapper ash 5 1 (2,1234),(4,5678),(3,999)
Snapper still supports the qc= syntax too, to list all sessions started by a PX query coordinator session:
@snapper ash 5 1 qc=1234@*
Note that the qc= syntax is a bit broken/inconsistent right now – what the qc=1234@* means right now is that Snapper looks up the SID 1234 from every instance and lists slave sessions from all instances. If you use qc=1234@2 for example, then Snapper would only look for QC in the instance #2 and any slaves that are also in instance #2 (but with inter-instance parallelism you may have slaves in other instances too). I will fix it in a near-future version.
I will write about the other major features in separate blog entries, but if you want to learn about it now, then grab a beer (or coffe) and watch the hackig session that I once recorded in my Oracle performance & troubleshooting Youtube channel.
P.S. Some queries (like the qc=) one may take a while to run, running dynamic queries with subqueries against GV$ views efficiently is tricky business. Reducing the number of sessions or statistics reported will likely make Snapper run faster. I have kept the older and simpler Snapper v3 (no RAC-support) in the standard (old) snapper address.
Update: There’s an older document with Snapper (v3) capabilities on my old Oracle performance & troubleshooting site too.
Update 2: There’s a higher level overview video about my ashtop.sql and Snapper tool in my Youtube channel too (Advanced Oracle Troubleshooting preparation session).
