Snapper 3.52 – With Oracle 9.2 support!

As I promised last year, I have 2 christmas gifts for you. I have already forgotten what the other one was supposed to be :), but the first one is Snapper v3.52 which has (the much requested) Oracle 9.2 support!

The syntax is the same, with Snapper you can now sample ASH-like data on Oracle 9.2 too. Instead of SQL_IDs it will display you SQL hash values:

SQL> @snapper ash,ash1=user+sql_id,ash2=sid+event 5 1 all
Sampling SID all with interval 5 seconds, taking 1 snapshots...

-- Session Snapper v3.52 by Tanel Poder @ E2SN ( http://tech.e2sn.com )

--------------------------------------------------
Active% | SQL_HASH_VAL | EVENT
--------------------------------------------------
    19% | 867131449    | db file scattered read
    19% | 1458866975   | db file scattered read
    13% | 1267657304   | db file scattered read
     6% | 884811952    | ON CPU
     6% | 581825634    | db file scattered read
     3% | 867131449    | ON CPU
     3% | 1267657304   | ON CPU
     3% | 1866659945   | ON CPU
     3% | 1671194465   | db file scattered read
     3% | 3021169464   | ON CPU

------------------------------------------------
Active% | USERNAME             | SQL_ID
------------------------------------------------
    23% | SYSTEM               | 867131449
    19% | SYSTEM               | 1458866975
    16% | SYSTEM               | 1267657304
     6% | SYSTEM               | 581825634
     6% | SYSTEM               | 884811952
     3% | SYSTEM               | 1558333473
     3% | SYSTEM               | 1671194465
     3% | SYSTEM               | 1866659945
     3% | SYSTEM               | 1927486197
     3% | SYSTEM               | 2700565926

--------------------------------------------
Active% |    SID | EVENT
--------------------------------------------
    77% |     18 | db file scattered read
    19% |     18 | ON CPU
     3% |     18 | db file sequential read

--  End of ASH snap 1, end=2011-01-10 03:02:58, seconds=5, samples_taken=31

PL/SQL procedure successfully completed.


Other than the 9i change, the rest of the snapper is pretty much the same as earlier, with some minor bugfixes and additions.

You can download it from here.

If you want to get the most out of snapper, read this article here (and make sure you look inside the script!)

Note: Big thanks to Marcus Mönnig who who wrote the additional 9i support code for Snapper first and Jamey Johnston for his additions (and myself for some final polishing fixes ;-)

Note2: I've agreed with Marcus that he could add Snapper into his free performance tool distribution in unchanged form, check out his Mumbai tool which could be useful for Oracle performance monitoring...

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 Uncategorized. Bookmark the permalink.

24 Responses to Snapper 3.52 – With Oracle 9.2 support!

  1. GregG says:

    Perfect! Thanks Guys.
    GregG

  2. Pingback: Tweets that mention Snapper 3.52 – With Oracle 9.2 support! | Tanel Poder's blog: IT & Mobile for Geeks and Pros -- Topsy.com

  3. Greg says:

    Tanel,
    do You have an idea why I’m getting:
    declare
    *
    ERROR at line 1:
    ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind
    ORA-06512: at line 453
    ORA-06512: at line 1523

    when running snapper as
    snapper ash 5 1 all

    actually older versions got same issue .
    Regards.
    Greg

  4. Tanel Poder says:

    @Greg
    Hi Greg,

    Yeah, that’s a common issue – it’s actually a problem with Oracle.

    Try this:

    1) Decribe V$SESSION
    2) CREATE TABLE tmp AS SELECT * FROM v$session;
    3) Describe tmp

    See if the RAW column lengths are different between these describes? (one has RAW(4) other RAW(8))

    This happens for some reasons in databases migrated from 32-bit platforms to 64-bit.

    One solution is to modify snapper so instead of V$SESSION%ROWTYPE it would use tmp%ROWTYPE (this means a change in the server side) … but now I’m starting to think that perhaps we can avoid referencing V$SESSION%ROWTYPE at all, Jamey has already created a type manually for 9i support…

  5. Simon says:

    Excellent. Nothing like a late Christmas pressie… or is that an early one for 2011? ;)

  6. Tanel Poder says:

    @Simon
    It’s more like a late one from 2007 or something ;-P

  7. Pingback: Snapper 3.52 – With Oracle 9.2 support! « Oracle Mine….

  8. Simon says:

    @Tanel Poder
    LOL… Better late than never! :-)

    Just been studying for the Oracle Performance exam actually… (part of the oracle partnership network thingy…) I never see “snapper” as the answer for some of the multiple guess Q’s though?! :-s

  9. Tanel Poder says:

    @Simon
    Well, consider it a competitive advantage that you know Snapper and most of the people at Oracle don’t ;-)

  10. Simon says:

    @Tanel Poder

    Indeed… The poor things! :-D

  11. ANDREJS says:

    Hi Tanel,

    good PL/SQL

    I can use pid(OSPID, SPID) when searching, no problem.
    @snapper ash,ash1=user+sql_id,ash2=sid+event 5 1 pid=18557

    How can I get PID in report?

    @snapper ash=pid 5 1 all it doesn’t work for me

    SQL> @snapper ash=ospid 5 1 all
    Sampling SID all with interval 5 seconds, taking 1 snapshots…

    – Session Snapper v3.52 by Tanel Poder @ E2SN ( http://tech.e2sn.com )

    ——-
    Active%
    ——-
    769% |

    – End of ASH snap 1, end=2011-01-13 15:48:47, seconds=5, samples_taken=36

    PL/SQL procedure successfully completed.

    thanks.

  12. Tanel Poder says:

    @ANDREJS
    Hi Andrejs,

    Snapper doesn’t show SPIDs (yet). So far I haven’t had a need for this, as within Oracle it’s more “rewarding” to see the SID, but it might be useful in some cases, so I’ll keep it in mind…

  13. ANDREJS says:

    Hi Tanel,

    How fast you are in process investigation from ‘linux top’. So i.e. you have 3 ORACLE_HOMES(9,10,11g) installed under one oracle user. Executing top below, how fastly you determine ‘/USER /PID or /COMMAND oracle’ to which ORACLE_HOME does it belong?

    top – 09:57:10 up 4 days, 10:06, 1 user, load average: 3.64, 2.61, 2.17
    Tasks: 441 total, 3 running, 438 sleeping, 0 stopped, 0 zombie
    Cpu(s): 12.1% us, 3.0% sy, 0.0% ni, 70.6% id, 13.6% wa, 0.1% hi, 0.7% si
    Mem: 6111268k total, 6069884k used, 41384k free, 10244k buffers
    Swap: 16386292k total, 215680k used, 16170612k free, 4079680k cached

    PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
    6505 oracle 16 0 2710m 740m 735m R 99.0 12.4 2:26.43 oracle
    7428 oracle 15 0 2708m 804m 802m R 24.8 13.5 0:33.68 oracle

  14. Tanel Poder says:

    @ANDREJS

    Andrejs, I don’t know whether top shows CWD or EXE fields, but I wrote a shell script for doing it manually:

    http://blog.tanelpoder.com/2011/02/28/finding-oracle-homes-with/

    Otherwise, top -c (or hit “c” in top UI) will at least show you the process aliases (ora_pmon_SID instead of just oracle)

  15. ANDREJS says:

    @Tanel Poder

    I have also comment about V$SESSION%ROWTYPE.

    After migration on ORACLE 9i from Linux 32bit to 64bit, script below is not working only under user SYS. All other users executing without problem. When doing desc v$sessions Raw is RAW(8), creating table from v$sessions the same RAW(8).

    declare
    sesrow v$session%rowtype;
    begin

    select sid into sesrow.sid
    from v$mystat
    where rownum=1;

    select * into sesrow
    from v$session
    where sid = sesrow.sid and rownum=1;

    end;
    Error at line 1
    ORA-06502: PL/SQL: numeric or value error: raw variable length too long
    ORA-06512: at line 9

    Script Terminated on line 1.

  16. Pingback: » Best Oracle Peformance Tools?

  17. Pingback: Why is my Exadata smart scan not offloading? « Martin's Blog

  18. Pingback: Essential tools for Exadata performance experiments « Martin's Blog

  19. Pingback: Advanced Oracle Troubleshooting Guide – Part 10: Index unique scan doing multiblock reads?! | Tanel Poder's blog: IT & Mobile for Geeks and Pros

  20. Andrea says:

    Hi.
    I have this error. Any idea?

    cdrdbs70.CDRs(SYS)> @snapper ash 1 10 323
    Sampling SID 323 with interval 1 seconds, taking 10 snapshots…
    SP2-0552: Bind variable “REDO” not declared.

  21. Pingback: Kyle Hailey » Best Oracle Performance Tools

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>