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...
`
