Snapper V4 Webinar
Snapper V4 is out!
The major new features include:
- RAC support – ability to query stats from remote instances
- Manual Before and After snapshot support – no need to use DBMS_LOCK sleeps anymore
- Show useful averages and ratios *in addition to* raw metrics for faster troubleshooting
- 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 :-)
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 ;-)
When I see the ascii art, I always think about perl JAPH’s – http://en.wikipedia.org/wiki/Just_another_Perl_hacker
:)
My avatar is also japh :)
Did you run the SQL (especially the lower one? ;-)
Yep, it is great and colorful :)
Cool, thanks! Just wanted to confirm whether it works for others too. I was using Terminator (the other one) and had initially some flakeyness when line width was too big. Then found a way to reduce the actual bytes per line …
Tanel.
News are great but I really wonder which one was more time consuming logo or actual snapper release ?
I managed to catch a little cold recently and didn’t feel like doing anything serious today, but wanted to do something – thus the new logo that had been in my mind for a long time :)
But snapper code itself has taken longer time than logo :)
Nice to begin the week with so much colors :-)
The fish is well colored, your are an artist Tanel ;-)
Yeah, that was the ide – to bring some color into the dull Oracle performance metrics ;-)
very nice trick !!! :)
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.
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).
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.
You have too much time on your hands! Kerry isn’t giving you enough to do, Tanel! :-)
I think it was time well spent! :-)
If you read the source code, the whole image is programmatically generated, so wasn’t too tedious, nevertheless challenging for the brain. Expect more in the future ;-)
Great job as usual.
I prefer the low-tech version… A cat near here run away looking at the second version…
Maybe Snapper v5 should have a colorful cat as a logo :-)
Beautifully done.
I missed the webinar. So I look forward eagerly to the uploaded session.
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
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. :)
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”.
Thanks Tanel ..
Great job as usual Tanel Poder ,waiting for new snapper script :)
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.
I have uploaded snapper v4 to here already:
http://blog.tanelpoder.com/files/scripts/snapper4.sql (note it’s called snapper4 right now, not just snapper).
It’s beta and has some flaws I plan to fix this week …
Thanks !
But how do I use it do that it does not give dbms_lock error?
It is still having the old command line with 4 parameters.
Thanks,
Rachit
Check this: http://blog.tanelpoder.com/2013/02/18/manual-before-and-after-snapshot-support-in-snapper-v4/
Thanks !
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
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
Ah, thanks for the quick reply and the explanation.
It’s meaning is more clear now to me.
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é
Hmm…
Seems a little fishy to me.
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.
Hi Jared, snapper only supports “static” field names, no functions/expressions. Snapper v3 (snapper.sql) supports P1RAW columns (easy to add to snapper4 too), which would help. As the hex output allows to split the value to 2 parts easier.
Btw, snapper already filters out any inactive sessions from the ASH output, so you can say “where type=’USER'” in the parameter..
Thanks for the updates Tanel.
Hi Tanel,
In snapper.sql script there’s a remark:
— set the noprint’s value to “noprint” if you don’t want these temporary variables to show up in a sqlplus spool file
I need to spool the results of snapper into a spool file, but even if I set:
DEF noprint=”noprint”
there are still three columns which mess up the spool file.
The solution was to also add these:
col snapper_ora10lower &noprint on
col snapper_ora9 &noprint on
col snapper_ora10higher &noprint on
HTH.
Should be fixed in the latest version :)