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...
Perfect! Thanks Guys.
GregG
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
@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…
Excellent. Nothing like a late Christmas pressie… or is that an early one for 2011? ;)
@Simon
It’s more like a late one from 2007 or something ;-P
@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
@Simon
Well, consider it a competitive advantage that you know Snapper and most of the people at Oracle don’t ;-)
@Tanel Poder
Indeed… The poor things! :-D
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.
@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…
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
@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)
@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.
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.
Andrea, did you “Save as…” the snapper script when downloaded or just copy & paste it into an editor? If you copy&paste, then sometimes the editors and terminal emulators corrupt the text …
Hi Tanel.
Saved and ran. Now it works.
Thank you very much for your support.
Great!