Oracle Session Snapper v3.10

Hi all, long time no see!  =8-)

Now as I’m done with the awesome Hotsos Symposium (and the training day which I delivered) and have got some rest, I’ll start publishing some of the cool things I’ve been working on over the past half a year or so.

The first is Oracle Session Snapper version 3!

There are some major improvements in Snapper 3, like ASH style session activity sampling!

When you troubleshoot a session’s performance (or instance performance) then the main things you want to know first are very very simple:

  1. Which SQL statements are being executed
  2. What are they doing, are they working on CPU or waiting.
  3. If waiting, then for what

Often this is enough for troubleshooting what’s wrong. For example, if a session is waiting for a lock, then wait interface will show you that. If a single SQL statement is taking 99% of total response time, the V$SESSION (ASH style) samples will point out the problem SQL and so on. Simple stuff.

However there are cases where you need to go beyond wait interface and use V$SESSTAT (and other) counters and even take a “screwdriver” and open Oracle up from outside by stack tracing :-)

When I wrote the first version of Snapper for my own use some 4-5 years ago I wrote it mainly having the “beyond wait interface” part in mind. So I focused on V$SESSTAT and various other counters and left the basic troubleshooting to other tools. I used to manually sample V$SESSION/V$SESSION_WAIT a few times in a row to get a rough overview of what a session was doing or some other special-purpose scripts.

However after Snapper got more popular and I started getting some feedback about it I saw the need for covering more with Snapper, not just the “beyond wait interface” part, but also the “wait interface” and “which SQL” part too.

So, now I’m presenting Snapper v3 which does all the 3 points above using ASH style V$SESSION sampling and it still has the first step to “beyond wait interface” part in it, which is very useful for advanced performance troubleshooting and diagnosis – I’m talking about the V$SESSTAT counters above.

I’ve made some syntax changes in Snapper too and right now the v3 doesn’t work on Oracle 9.2 (it will work some day :)

To give you an idea of the new ASH style sampling capabilities, heres some example output:

SQL> @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id,ash2=program+module+action 5 1 all
Sampling...
-- Session Snapper v3.10 by Tanel Poder @ E2SN ( http://tech.e2sn.com ) --------------------------------------------------------------
Active% |    SID | EVENT                     | WAIT_CLASS
-------------------------------------------------------------- 100% |    133 | db file scattered read    | User I/O 5% |    165 | control file parallel wri | System I/O 2% |    162 | ON CPU                    | ON CPU 2% |    167 | db file parallel write    | System I/O 2% |    166 | log file parallel write   | System I/O
--------------------------------------------------- Active% | PLSQL_OBJE | PLSQL_SUBP | SQL_ID --------------------------------------------------- 77% |            |            | a5xyjp9gt796s 23% |            |            | 4g4u44bk830ms 12% |            |            |
------------------------------------------------------------------------------------------- Active% | PROGRAM                   | MODULE                    | ACTION ------------------------------------------------------------------------------------------- 100% | sqlplus@mac01 (TNS V1-V3) | sqlplus@mac01 (TNS V1-V3) | 5% | oracle@solaris02 (CKPT)   |                           | 2% | oracle@solaris02 (DBW0)   |                           | 2% | oracle@solaris02 (CJQ0)   |                           | 2% | oracle@solaris02 (LGWR)   |                           | --  End of ASH snap 1, end=2010-03-22 17:35:06, seconds=5, samples_taken=43

You can read some usage examples and download it here:

P.S. People who attended Hotsos Symposium Training Day where I demoed the initial version of Snapper v3 – download the new version from above link (v3.10), it’s much more flexible than the one I demoed couple of weeks ago!


Note that this year’s only Advanced Oracle Troubleshooting class takes place in the end of April/May 2014, so sign up now if you plan to attend this year!

This entry was posted in Oracle and tagged , , , . Bookmark the permalink.

11 Responses to Oracle Session Snapper v3.10

  1. Christoph says:

    I can’t wait to give Snapper 3 a try.
    Your training day at the Hotsos Symposium was pure gold. I learned a lot from your logical approach. Snapper, and the rest of your helpful scripts, will receive a permanent spot in my Oracle tool bag.
    Thanks a bunch for making all this available!

  2. Tanel Poder says:

    @Christoph
    Thanks Christoph! Glad to hear that other people also like this stuff as much as I do :-)

    About Snapper v3.10, I haven’t thoroughly tested it on Oracle 10.1 yet, so there may be some glitches, but I’m setting up a 10.1 env in a day or two for further testing… Anyway if there are any issues, please let me know.

  3. Coskan says:

    Thank you for this wonderful tool Tanel. Although your tools are getting popular there are also tiny failures because of script library like this below :)

    http://oracleshare.wordpress.com/2010/02/27/self-inflicted-unable-to-startup-or-shutdown-database-from-oem

  4. mfullerton says:

    Man, since I have become a contractor in 2007 I have been so bogged down I haven’t been able to make a single Hotsos symposium! I’m so bummed to have missed it! Any chance of putting your presentation up for download? I think I am going to request off for it next year. with this much notice I’ve got to be able to take the time off!

  5. Mark says:

    Thank you! I’ll be testing the new version of snapper in a 10.2.0.4 environment today (already working on a production problem). Also thank you for your training day at Hotsos. I really learned a lot about how to approach Oracle performance issues which I tend to forget from time to time and the scripts you provided are excellent. I’m sure my sql and plsql programming will also get better after learning the code in your scripts.

  6. Neil says:

    Brilliant! Thank you Tanel for taking the time to improve snapper.
    I have on quick question – are there any important considerations when using snapper
    on a RAC environment ?

  7. Aswath Rao says:

    Tanel,

    Thanks for sharing the snapper.sql script.

    Thanks
    Aswath Rao

  8. Steve says:

    Thank you for this wonderful tool Tanel. Although your tools are getting popular there are also tiny failures because of script library like this below :)

    http://oracleshare.wordpress.com/2010/02/27/self-inflicted-unable-to-startup-or-shutdown-database-from-oem

  9. Tanel Poder says:

    @Steve
    You shouldn’t set SQLPATH on a server anyway, my scripts are meant as client side scripts. As far as failures go, anything you do may help you or cause trouble so it’s YOUR task to test and verify any new tool, script or technique you use, do not take anybody’s word for it!

  10. Kirt says:

    Just got back from the SOUG presentation. This was one of the most valuable session’s I have attended in a long time. I am looking forward to working with your scripts! Thanks for the great presentation!

  11. Pingback: E-Business Suite Performance Tuning « Anjul Sahu's Oracle Blog

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>