Update:Niall Litchfield pointed out that the “set serverout on” command was ignored on SQL Developer because I was using the short syntax (serverout instead of serveroutput). Once I changed this, the SQL Developer doesn’t print the warning anymore and there’s no need to explicitly open up the separate DBMS_OUTPUT. I have uploaded Snapper v4.04 with this fix included. Thanks Niall!

Also, thanks go to:

Hans-Peter Sloot – for building the first cut of RAC support into a previous Snapper version

Ronald Rood – for adding the customizations for SQL Developer support into a previous Snapper version

Note that the manual before/after snapshots don’t work inside SQL Developer right now, will try to fix this later …

NB! If you want to move to the "New World" - offload your data and workloads to Hadoop, without having to re-write your existing applications - check out Gluent. We are making history! ;-)

42 Responses to Even more Snapper – v4.03 now works in SQL Developer too!

Really love snapper since the beginning, are You planning to deal with:
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind
ORA-06512: at line 599
ORA-06512: at line 1557

Its issue when You have upgraded 32->64bit and v$session is broken .
Regards
GregG

Yep it’s also on my list. Do you know about the temporary workaround yet?

1) CREATE TABLE tmp_gv_session AS SELECT * FROM gv$session WHERE 1=0
2) and then modify snapper code to use tmp_gv_session%ROWTYPE instead of the real gv$session

But this is not too convenient, especially as snapper’s philosophy is to not require any DB changes. So, I think I’ll manually define the type with RAW(8) datatypes where needed, dependent on the DB version as opposed to using gv$view%ROWTYPE.

Update: Niall Litchfield pointed out that the “set serverout on” command was ignored on SQL Developer because I was using the short syntax (serverout instead of serveroutput). Once I changed this, the SQL Developer doesn’t print the warning anymore and there’s no need to explicitly open up the separate DBMS_OUTPUT. I have uploaded Snapper v4.04 with this fix included. Thanks Niall!

Btw, there’s one more fairly important bug to fix (not related to this one) .. when snapper samples remote sessions via gv$, it should ignore the remote gv$ parallel slave SIDs too as these sessions are active just because snapper is running itself … anyway will do this in a few days, got to find an efficient way for doing this.. a NOT IN subquery might end up doing ping-pong between instances…

I’d love to get snapper working inside TOAD as well, but I am too busy right now for this :(

Based on the line numbers in the error message it’s the &sid_filter variable which doesn’t get initialized … it’s populated in an earlier query block… does TOAD support the sqlplus tricks like the “column x new_value v” at all?

See what’s the average duration of log file sync wait event (and how many commits per second your session is doing).
Run snapper on LGWR itself to see how busy it is and whether “log file parallel write” wait events take most of its time (or is it CPU instead). And what’s the average duration of the write wait events (and is it roughly the same as you’d expect from your storage subsystem?)

Yes Toad can do what you are asking “column x new_value v”…there are 2 ways to execute a statement in toad as you know. One is just “execute statement” and other one being “run as script”…you can only use what you are asking above(column x new_value v) when you run it as script…my toad version is 11 and also tried it on 9.6…

Again AWESOME job….May God Bless you and your family for helping the oracle community by saving the so much troubleshooting time.

I know you can run the snapper by using a query against your v$session and then selecting some number of SIDs.
I really would like to start snapper , for instance for 60 seconds, collecting stats on sessions taged with the end-to-end metrics (module, action and/or client_identifier).
Could this be possible?

I’m sure you could just do (select sid from v$session where module=’demo’). But this would probably only be ran at the start of the snapper. The session with tag module=’demo’ could change during the time snapper runs, and I want the snapper to catch these changes. Possible?

Yeah, the sampling is dynamic only for the snapper’s ASH data, but for v$sesstat and other stat metrics, it’s captured during snapper start. I don’t remember anymore, perhaps for every snapshot instead, so if you run 60 x 5 sec snapshots you might see changes.. but it’s not exactly what you want.

I would suggest you to look into V$SERV_MOD_ACT_STATS and DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE, this should do exactly what you want (but doesn’t capture as much v$sesstat stats though).

Its great to hear that people area actually using the end to end instrumentation out there :-)

Ok Thanks! I’ll test this out. Yes. I’m allready using the v$serv_mod_act_stats ,and both stat and trace enabled by module and action.
Yes. The problem is that java developers don’t know about it. So it’s up to us Oracle geaks to show them ;-)

When I show this for our devleopers (and DBAs) they usually are very thrilled about the feature.
It really makes a bridge between the code and the database. It makes it very easy for the developer and DBA to identify, trace, and monitor different applications, features, webservices … what ever .. inside the database (trace, stats, v$session, v$sql, v$serv_mod_act_stats, dba_hist_sqlstat, v$active_session_history and dba_hist_active_sess_history).

I’m doing a webinar on end-to-end metrics at allthingsoracle.com the 29th of may (tomorrow).
It would be great to get more people start using this feature.
If you don’t mind, I would appreciate a quote en twitter, linkedin etc :-)

When i executed snapper4, it is prompting for Enter Bind Values for inst_filter, sid_filter, x,v,snapper, appreciate, if you could update the script with the
correct information or bind values to be given as input to the script.

How did you run snapper? With proper parameters? If you run snapper with correct parameters (look into my blog articles or the hacking session videos) and you have the required privileges (read rights on v$ views and exec right on dbms_lock), it works ok.

Thanks for your immediate reply. I am sorry, I was out of town and hence the delay.
Well I executed snapper4 in sqldeveloper as demonstrated in your blog. see below:
I logged in as system
@h:\dba_scripts\snapper4 all 5 1 lgwr

Note that the poor-mans-ash sampling over GV$ is slow, so you’d better off either using the real ASH (and keep snapper for v$sesstat etc) or use user=HCA without the instance qualifier to sample only the local V$

Interesting – it works in my 11.2.0.4 DB. How did you download the file? If you did a copy&paste from a browser to an editor, can you try the “Save as…” approach or wget/curl instead for downloading the file. I’ve had cases before where the copy&paste&editor mess up the script text

Good one! I assume you logged in to the DB server and ran sqlplus user/pass from there? (connecting directly, without the listener). If you connect directly like this (the bequeath protocol), then it’s your sqlplus who spawns the oracle server process, inheriting any ulimits of the sqlplus process – so a different user with different ulimits (or connecting via the listener) would help.

If I recall correctly, this was actually an issue with early SQL developer (?). Have you tried with the latest SQL Developer version, it works ok for me with SQL Developer 4.1. I just published a newer version of Snapper – please try with that: http://blog.tanelpoder.com/files/scripts/snapper.sql