Session Snapper v4 – The World’s Most Advanced Oracle Troubleshooting Script!

Snapper V4 Webinar

Snapper V4 is out!

The major new features include:

  1. RAC support – ability to query stats from remote instances
  2. Manual Before and After snapshot support – no need to use DBMS_LOCK sleeps anymore
  3. Show useful averages and ratios *in addition to* raw metrics for faster troubleshooting
  4. And more! :)

Snapper is still a free-to-use tool and it still does NOT require any object creation nor changes in your databases for use. Now even DBMS_LOCK access isn’t needed, although it’s still useful for convenience.

I have posted the hacking session video to here:

Oh, wait, there’s more! You’re going to love this, especially if you love reverse engineering code puzzles! ;-) 

New Kind of Snapper Logo

Update: If you’re too lazy to run the code below, here’s what the logo looks like :-)

snapper_logo

I have also done major updates to Snapper logo which you should check out. If you have ever looked into the Snapper source code you know the old logo looks like this:

--    +-----=====O=== Welcome to The Session Snapper! (Yes, you are looking at a cheap ASCII
--   /                                                 imitation of a fish and a fishing rod.
--   |                                                 Nevertheless the PL/SQL code below the
--   |                                                 fish itself should be helpful for quick
--   |                                                 catching of relevant Oracle performance
--   |                                                 information.
--   |                                                 So I wish you happy... um... snapping?
--   |                                                )
--   |                       ......
--   |                       iittii,,....
--   ¿                    iiffffjjjjtttt,,
--                ..;;ttffLLLLffLLLLLLffjjtt;;..
--            ..ttLLGGGGGGLLffLLLLLLLLLLLLLLffjjii,,                        ..ii,,
--            ffGGffLLLLLLjjttjjjjjjjjffLLLLLLLLLLjjii..                ..iijj;;....
--          ffGGLLiittjjttttttiittttttttttffLLLLLLGGffii..            ;;LLLLii;;;;..
--        ffEEGGffiittiittttttttttiiiiiiiittjjjjffLLGGLLii..      iiLLLLLLttiiii,,
--      ;;ffDDLLiiiitt,,ttttttttttttiiiiiiiijjjjjjffLLLLffttiiiiffLLGGLLjjtttt;;..
--    ..ttttjjiitt,,iiiiiittttttttjjjjttttttttjjjjttttjjttttjjjjffLLDDGGLLttii..
--    iittiitttt,   ;;iittttttttjjjjjjjjjjttjjjjjjffffffjjjjjjjjjjLLDDGGLLtt;;..
--    jjjjttttii:. ..iiiiffLLGGLLLLLLLLffffffLLLLLLLLLLLLLLLLffffffLLLLLLfftt,,
--    iittttii,,;;,,ttiiiiLLLLffffffjjffffLLLLLLLLffLLffjjttttttttttjjjjffjjii..
--    ,,iiiiiiiiiittttttiiiiiiiiiijjffffLLLLLLLLffLLffttttttii;;;;iiiitttttttt;;..
--    ..iittttttffffttttiiiiiiiiiittttffjjjjffffffffttiittii::    ....,,;;iittii;;
--      ..;;iittttttttttttttttiiiiiittttttttttjjjjjjtttttt;;              ..;;ii;;..
--          ..;;;;iittttttjjttiittttttttttttttjjttttttttii..                  ....
--                ....;;;;ttjjttttiiiiii;;;;;;iittttiiii..
--                      ..;;ttttii;;....      ..;;;;....
--                        ..iiii;;..
--                          ..;;,,
--                            ....
--

As the Snapper script allows you to easily take performance snapshots of Oracle sessions and fish for solutions to your performance problems from the ocean of possible root causes, it makes sense to use a fish in the logo somewhere. I have also added sea and a cruise boat (symbolizing longer vacations thanks to Snapper!) into the new Snapper script file and and have pasted it below.

Scroll down and check it out – I’m sure you see it’s special. Does it ring a bell – what should you do with this special kind of a logo now? Of course – copy&paste it into sqlplus and run the logo! ;-) Note that you should run this on Oracle 11.2 or later.

There are actually two different logos below. The first one (cruise boat) is a low-tech one, suitable for use with Windows CMD.EXE and GUI tools like SQL*Developer.

Low-tech version:

 
SET                                                                     LiNeSiZe 10000         PageSize 5000
SET                                                                                   TrimOut ON           Head Off
                                                                  /**/
                                                                SELECT
                                                               LISTAGG
                                                             (SUBSTR(s,
                                        MOD(r2-1,115)+1,1)) WITHIN GROUP(ORDER BY r) 
                                     FROM(SELECT rownum r,RPAD(RPAD(RPAD(RPAD(LPAD(LPAD
                               ('(',x,'('),20,' '),x+20,')'),40,' '),40+y,' '),50,'W')s FROM(
                                  SELECT CEIL(ABS(SIN(rownum/30)*13))x,CEIL(ABS(COS(rownum/9)*5))y
                                     FROM dual CONNECT BY LEVEL<=115)), (SELECT rownum r2 FROM (dual)
--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                          CONNECT BY LEVEL <= 50) GROUP BY MOD(r2-1, 115)    +1;

Hi-tech version:

This version requires a modern xterm emulator like Putty (on Windows) or iTerm2 or Terminator on Mac OSX, if your current terminal ends up showing garbage. Go ahead, make your terminal window wide enough, paste and enjoy :-)

First change the sqlplus settings if you didn’t do so already:

SET LINESIZE 10000 TRIMOUT ON TRIMSPOOL OFF HEAD OFF PAGESIZE 5000

And now paste this in:

 
                                                      SELECT                      
                                                CHR(POWER(3,3))             
                                          ||'[38;5;0m'||LISTAGG(SUBSTR(
                                    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(s ,')',
                            CHR(POWER(3,3))||'[48;5;'||TRIM(TO_CHAR((POWER(2,4)+CEIL((SIN(r2/5)                                                 +
                    SIN(ROWNUM/150)+1)*3-1)+CEIL((-SIN(ROWNUM/150)+1)*3-1)*6+CEIL((COS(ROWNUM/150)+1)*3-1)                                 *6*6),
              '099'))||'m)'),'(',CHR(POWER(3,3))||'[48;5;'||TRIM(TO_CHAR((POWER(2,4)+CEIL((SIN(r2/5)+SIN(ROWNUM/150)                   +1)*3-1)+
       CEIL((-SIN(ROWNUM   /150)+1)*3-1)*6+CEIL((COS(ROWNUM/150)+1)*3-1)*6*6),'099'))||'m('),' ',CHR(POWER(3,3))||'[48;5;'||TRIM      (TO_CHAR(
(POWER(2,4)+CEIL(4)+CEIL(4)*6+CEIL(5-(r2/8))*6*6),'099'))||'m '),'.',CHR(POWER(3,3))||'[48;5;'||TRIM(TO_CHAR((POWER(2,4)+CEIL(5)+CEIL(4)*6+CEIL
       (3)*6*6),'099'))||'m~'),'W',CHR(POWER(3,3))||'[48;5;'||TRIM(TO_CHAR((POWER(2,4)+CEIL(4+ABS(SIN(r/25)))+CEIL(2-ABS(SIN          (r/5))*2)
           *6+CEIL(2-ABS(SIN(r/5))*2)*6*6),'099'))||'m '),(MOD(r2-1,115)+1)*12+1,12))WITHIN GROUP(ORDER BY r)||CHR(POWER                  (3,3))                        
                ||'[0m' v FROM(SELECT ROWNUM r, RPAD(RPAD(RPAD(RPAD(LPAD(LPAD('(',x,'('),20,' '), x+20,')'),40,' '),                        40+y,
                      '.'),50,'W')s FROM (SELECT CEIL(ABS(SIN(ROWNUM/30)*13))x,CEIL(ABS(COS(ROWNUM                                              *
                            1/DBMS_RANDOM.VALUE(7,7.5))*7))y FROM dual CONNECT BY LEVEL<=115)),
                                    (SELECT ROWNUM r2 FROM dual CONNECT BY LEVEL<=50)
                                               GROUP BY MOD(r2-1,115)+1;

 

So, how do you like the new Snapper logo? :)

Feel free to leave a comment – and if you like this, please share it – all the billions of people of the world deserve to know how cool sqlplus is! :-)

Also, check out my upcoming Advanced Oracle Troubleshooting and Advanced Oracle SQL Tuning seminars and see you all on Wednesday at Snapper V4 Launch Party ;-)

This entry was posted in Cool stuff, Oracle. Bookmark the permalink.

41 Responses to Session Snapper v4 – The World’s Most Advanced Oracle Troubleshooting Script!

  1. When I see the ascii art, I always think about perl JAPH’s – http://en.wikipedia.org/wiki/Just_another_Perl_hacker
    :)

  2. Coskan says:

    News are great but I really wonder which one was more time consuming logo or actual snapper release ?

  3. Bertrand says:

    Nice to begin the week with so much colors :-)
    The fish is well colored, your are an artist Tanel ;-)

  4. yves says:

    very nice trick !!! :)

  5. Hi Tanel,

    Couple of questions about the script logic :-
    ===================================

    1 . I couldn’t able to understand still the below one, which you have utilized in your code, what’s an advantage over pls_integer or other integer data types.
    — trick for holding 32bit UNSIGNED event and stat_ids in 32bit SIGNED PLS_INTEGER
    pls_adjust constant number(10,0) := power(2,31) – 1;

    2. Can you elaborate the below code
    — sleep timeout backoff depending on the duration sampled (for up to 10 seconds total sampling time will get max 100 Hz sampling)
    — for longer duration sampling the algorithm will back off and for long durations (over 100 sec) the sampling rate will stabilize
    — at 1Hz
    dbms_lock.sleep( greatest(0.1,(least(1,&snapper_sleep*&snapper_count/100))) );

    Why can’t I use the user specified refresh time instead of above logic. ?

    3. I have gone through code and compared with systematic approach (as always say )…with Oracle Architecture
    Query executes
    => v$statname get’s updated(STAT)
    => event get’s fired (instrumented at DB level)(WAIT)
    => sys modeltime (time invested on each phases (TIME) SWT — till here
    => Latches
    here’s the catchy, why you have opted for count(*) on stat and wait ?
    As per my understanding Each latch holder covers across the various stats and events system

    => BUFG Similarly the Buffer gets dealt with all levels
    => Enqueue On top of Buffers included

    If you find time, you can reply back in session or here, if you find not interesting, then you can skip it.

    • Tanel Poder says:

      Pavan, I will cover some of the “internals” during the webinar and if you still have questions left after that, feel free to ask! Note that some constructs come from the fact that when building snapper, it supported Oracle 9i and I planned to extend the support to 8i too (as it was still widely in use back then).

    • Tanel Poder says:

      Pavan, here are the answers:

      1) the array has to be indexed by PLS_INTEGER/BINARY_INTEGER in order to do bulk collects into it. And its range is roughly -2 billion to +2 billion -(2^31) to 2^31-1. But as the stat_id values range from zero to 4 billion (2^32) I used the pls_adjust hack to shift the statistic IDs from 0..4B to -2B..2B. Basically it’s a hack, which works.

      2) I use the backoff so that Snapper would still be accurate enough even when running it for 5 seconds (I don’t want to just sample 5 times per 5 seconds, I want more) and also if I choose to run snapper for hours, that then it wouldn’t sample 50 times per second anymore – I want to reduce the overhead. So it’s “adaptive sampling rate” so the users wouldn’t have to think about this themselves. It would be possible to add an optional parameter for this…

      3) Yes, while STAT, WAIT and TIME are session-level, the LATG, BUFG and ENQG are system-wide. That’s why I use -1 as the SID when showing the latter stats, to emphasize that they are not only generated by the measured sessions, but the whole instance. IIRC I use the count(*) somewhere just to keep track of unique statistic numbers (on top of the previous stats queried), it takes less memory to store stats by ID as opposed to their name. So, the count(*) is not summing up the actual stats values, it’s just a trick for keeping track of the stats ids of different statistics from different V$ views.

      • Hi Tanel,

        Very much thanks for sharing your replies to my questions (indeed with your busy schedule, I know it costs $$$ , knowledge is wealth after all). I was trying to know/understand how your “Visualization is with Oracle Database Instance/memory ” when you start explaining things more and more .. in your free hacking sessions, that is the important aspect and I just compare with code/scripts perspective.

  6. You have too much time on your hands! Kerry isn’t giving you enough to do, Tanel! :-)

  7. Marco V. says:

    Great job as usual.
    I prefer the low-tech version… A cat near here run away looking at the second version…

  8. Krish says:

    Beautifully done.

    I missed the webinar. So I look forward eagerly to the uploaded session.

  9. Sachin says:

    hi tanel,
    I was little late to join the webinar ( just 2 min) and it was full already.
    waiting eagerly for upload , when it will be available ?

    thanks

    • Tanel Poder says:

      I plan to make it available on the weekend. Sorry for the trouble – I realized I had a max limit when I logged in and changed my account to allow up to 500 attendees, so I had to restart the webinar and then everyone managed to log in.

      • sorry for suggestion about set autot trace stat, when i wrote about disabling output in example with arraysize 2 and 5000, i meant just that fetch size changing will be more noticeable. Though I understand that the example was just to show possibilities of the instrument. :)

        • Tanel Poder says:

          No probs, thanks for the comment! Yep I’ve used autotrace for this too, this time I wanted to make it deliberately slower at first, but this backfired later with higher arraysize. When there’s output displayed on the screen, then (at least on Windows) it’s possible to have fun with minimizing the window, the query will complete faster. You maximize the window, it’s the slowest. And if you make the window slightly smaller, it’s slightly slower. A very good way to explain and demo “application think time”.

      • Sachin says:

        Thanks Tanel ..

  10. shivanand says:

    Great job as usual Tanel Poder ,waiting for new snapper script :)

  11. Rachit says:

    You have very nice and informative stuff on you blogs !
    I see snapper V4 does not require DBMS_LOCK execute permission.I would really love to try it since our DBA does not agree to give execute permissons to individual users on this package.
    Please let us know when it will be available for download.

    Thanks,
    Rachit.

  12. Freek D'Hooge says:

    Hi,

    I’m trying to understand the meaning of the active% column in the ash output.
    From the code it looks like activity percentage = (number of records within this group (= output line) divided by the total number of samples) multiplied by 100.
    Is this correct?

    If so, can I thus say that in the example below, I had – within this 10 second periode – per sample on average 219 sessions waiting on “enq: TX – index contention” while executing the sql with sql_id 76bdhgn7nmyuq ?
    Or is it wrong to translate this to number of sessions?

    sys@QZHSTP10> @snapper ash 10 5 all
    Sampling SID all with interval 10 seconds, taking 5 snapshots…
    – Session Snapper v3.61 by Tanel Poder ( http://blog.tanelpoder.com )
    ———————————————————————————————
    Active% | SQL_ID | SQL_CHILD | EVENT | WAIT_CLASS
    ———————————————————————————————
    2738% | 76bdhgn7nmyuq | 8 | enq: TX – index contention | Concurrency
    2263% | | | log file sync | Commit
    975% | 76bdhgn7nmyuq | 8 | buffer busy waits | Concurrency
    550% | 76bdhgn7nmyuq | 7 | enq: TX – index contention | Concurrency
    450% | 8u5a32s6yk7m4 | 0 | enq: TX – index contention | Concurrency
    400% | bm954qru7svvz | 0 | gcs drm freeze in enter server mode | Other
    363% | 22hpq69u5md5w | 0 | gcs drm freeze in enter server mode | Other
    325% | bm954qru7svvz | 0 | enq: FB – contention | Other
    300% | 76bdhgn7nmyuq | 8 | latch: ges resource hash list | Other
    263% | d04yvurxcsnck | 0 | gcs drm freeze in enter server mode | Other

    – End of ASH snap 1, end=2013-03-11 15:47:20, seconds=10, samples_taken=8

    Kind regards,

    Freek

    • Tanel Poder says:

      It’s like average active sessions, but shown as a % (AAS x 100).

      So, 100% means that there was one session on average waiting for this event, running this SQL – all of its time. Or there may have been 2 sessions who both did this 50% of their time. Or 5 sessions who did this for 20% of their time… you get the point.

      So, 2263% percent waiting for log file sync / Commit means that during the snapper runtime, there was enough waiting for commits, so if you’d add all this time up, it would amount to 22.6 sessions full response time during the measurement. So as you measured for 10 seconds, the total time waited during that 10 seconds was 10 x 22.63 = 226.3 seconds.

      I deliberately use this approach, not the “statspack” way of aggregating ALL sessions numbers into 100% and showing some profile of that – as I don’t want the meaning of 100% to change across snapshots. In Statspack/AWR, the meaning of 100% changes depending on how many sessions you have logged on, in ASH and Snapper output 100% always means the same thing – full response time of a single session during the measurement period.

      I’ve written about it here too:

      http://tech.e2sn.com/oracle-scripts-and-tools/session-snapper

  13. José says:

    Hi Tanel!

    I tried both logs are they are amazing hehe I like Hi-tech version so colorfull I run it several times and waves changed as you said

    Thank you very much for such a touch of color!

    José

  14. Jared says:

    Hmm…
    Seems a little fishy to me.

  15. Jared says:

    Hi Tanel,

    I’ve been trying to decode P1 into to the mode for TX enqueue waits, so I can see if the waits are mode 4 or 6

    This will execute, but the mode does not appear:

    @snapper ash=sql_id+event+wait_class+bitand(p1,65535) 5 1 “select sid from v$session where status = ‘ACTIVE’ and state = ‘WAITING’ and type = ‘USER’”

    Is this even possible from the snapper command line?

    I can of course just use this:

    @snapper ash=sql_id+event+wait_class+p1 5 1 “select sid from v$session where status = ‘ACTIVE’ and state = ‘WAITING’ and type = ‘USER’”

    … and then decode the mode separately, but it would be nice to see it on the output.

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>