Snapper 3.52 – With Oracle 9.2 support!

Tanel Poder

2011-01-09

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.

</code>
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...

`


  1. I am finally close to launching the completely rebuilt 2024 versions of my Linux & AOT classes in my Learning Platform! (Updates to SQL Tuning class in H2 2024):
    Advanced Oracle SQL Tuning training. Advanced Oracle Troubleshooting training, Linux Performance & Troubleshooting training. Check them out!
  2. Get randomly timed updates by email or follow Social/RSS