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

If you already downloaded snapper v4, then better re-download it again as the v4.03 also runs in SQL Developer!

Snapper 4 inside SQL Developer

You might also want to check how to enable the DBMS_OUTPUT display in SQL Developer post by “That” Jeff Smith :-)

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 …

This entry was posted in Oracle. Bookmark the permalink.

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

  1. GregG says:

    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

    • Tanel Poder says:

      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.

  2. Tanel Poder says:

    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!

  3. Helmars says:

    INST_ID missing in “select addr from gv$process where spid in…”.

    • Tanel Poder says:

      OK, fixed in v4.06 (just uploaded).

      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…

  4. Max says:

    Tanel, Awesome Job. I love the the extra output we get (NUM_WAITS, WAITS/SEC, AVERAGES)….really dont even have to turn on tracing …your tool can tell us all…

    But question for you. Dose snapper work inside of TOAD? As i work more with Toad. I tried using the same syntax as you would in sql developer with dbms_output on i get below error….

    SET SERVEROUTPUT ON
    @C:\oracle\sql\snapper4 all 5 1 lgwr

    Error at line 511
    ORA-06550: line 614, column 8:
    PL/SQL: ORA-00936: missing expression
    ORA-06550: line 604, column 8:
    PL/SQL: SQL Statement ignored
    ORA-06550: line 642, column 17:
    PL/SQL: ORA-00936: missing expression
    ORA-06550: line 634, column 8:
    PL/SQL: SQL Statement ignored
    ORA-06550: line 798, column 42:
    PL/SQL: ORA-00936: missing expression
    ORA-06550: line 792, column 9:
    PL/SQL: SQL Statement ignored

    • Tanel Poder says:

      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?

      • Dalibor says:

        I have same error message when command @snapper all 5 1 lgwr is started,
        on Oracle 11.2 it is ok, but on Oracle 9.2 is same error message, what to do?

        • Tanel Poder says:

          Snapper v4 needs at leat Oracle 10g. Snapper v3 works on Oracel 9.2 too, try that: https://blog.tanelpoder.com/files/scripts/snapper3.sql

          • Dalibor says:

            Great, thanks for quick response.
            Let me ask where to go using snapper to see what hapenned during event log file sync?
            Thanks.
            Regards.

          • Tanel Poder says:

            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?)

  5. SQL Newbie says:

    Could you please consider including the version # within the script ? Thanks.

  6. Andrey Goryunov says:

    Thank you very much Tanel!
    It also works in SQL Developer through http!

  7. Max says:

    Hi Tanel,

    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.

  8. Dani says:

    You can set DEFAULT DIRECTORY for scripts by : Tools menu > Preferences > Database > Worksheet > Select default path to look for scripts.

    in that way one needs not to type full path every time.

  9. 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?

    Thanks for a great utility :-)

    • 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?

      • Tanel Poder says:

        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 :-)

          http://allthingsoracle.com/end-to-end-metrics-building-a-performance-bridge-between-the-developer-and-the-dba/

          Regards Lasse

  10. jack says:

    Hi Tanel,

    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.

    Thanks
    Jack

    • Tanel Poder says:

      Hi Jack,

      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.

      Try: @snapper4 all 5 1 lgwr

      Does it still give an error?

  11. Jack says:

    Hi Tanel,

    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

    The script is prompting for SID_FILTER and INST_FILTER.

    Thanks
    Jack

  12. Jamey Johnston says:

    Cool, Tanel!

  13. Paul says:

    Hi Tanel,

    I’m trying Session Snapper v4.12 BETA and I get this:

    SQL> @snapper ash 5 1 “select sid from gv$session where username=’HCA’”
    Sampling SID select sid from gv$session where username=’HCA’ with interval 5 seconds, taking 1 snapshots…
    /* inst_filter */ s.inst_id=USERENV(‘Instance’) and (s.inst_id,s.sid) in (select sid from gv$session where username=’HCA’)
    *
    ERROR at line 628:
    ORA-06550: line 628, column 86:
    PL/SQL: ORA-00947: not enough values
    ORA-06550: line 619, column 8:
    PL/SQL: SQL Statement ignored
    ORA-06550: line 657, column 87:
    PL/SQL: ORA-00947: not enough values
    ORA-06550: line 649, column 8:
    PL/SQL: SQL Statement ignored
    ORA-06550: line 812, column 122:
    PL/SQL: ORA-00947: not enough values
    ORA-06550: line 807, column 9:
    PL/SQL: SQL Statement ignored

    Thanks
    Paul

    • Tanel Poder says:

      Hi, snapper 4 requires inst_id too in your select: “select inst_id,sid from gv$session where …”

      or better, use @snapper ash 5 1 user=HCA@* to get all instances.

      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$

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>