| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Oracle Process Problem
Hi Bala,
Maybe those processes are just running some long-running jobs or servicing continuous feeds?
You can use my snapper.sql script for reporting the execution profile of
those sessions (e.g. see if they're doing lots of logical IOs, parses,
executes, sorts etc).
I've deliberately written the script the way that it doesn't require any
changes in the database - change management and approval can be slow in some
companies....
You can download it here:
http://www.tanelpoder.com/files/scripts/snapper.sql
An overview of it is here:
http://blog.tanelpoder.com/2007/12/06/oracle-session-snapper-v106-released
...and sample output is below (you need to replace &mysid with the SID you're interested in).
Good luck!
SQL> @snapper out 10 1 &mysid
HEAD, SID, SNAPSHOT START , SECONDS, TYPE, STATISTIC-- End of snap 1
, DELTA, D/SEC, HDELTA, HD/SEC
---------------------------------------------------------------------------- ----------------------------------------------------------------- DATA, 155, 20071213 21:15:26, 10, STAT, opened cursors cumulative
, 1, 0, 1, .1
DATA, 155, 20071213 21:15:26, 10, STAT, recursive calls
, 2, 0, 2, .2
DATA, 155, 20071213 21:15:26, 10, STAT, recursive cpu usage
, 2, 0, 2, .2
DATA, 155, 20071213 21:15:26, 10, STAT, CPU used by this session
, 2, 0, 2, .2
DATA, 155, 20071213 21:15:26, 10, STAT, session uga memory , -65464, -6546, -65.46k, -6.55k DATA, 155, 20071213 21:15:26, 10, STAT, session pga memory , -65536, -6554, -65.54k, -6.55k DATA, 155, 20071213 21:15:26, 10, STAT, calls to get snapshot scn: kcmgss , 1, 0, 1, .1 DATA, 155, 20071213 21:15:26, 10, STAT, session cursor cache hits
, 1, 0, 1, .1
DATA, 155, 20071213 21:15:26, 10, STAT, session cursor cache count
, 1, 0, 1, .1
DATA, 155, 20071213 21:15:26, 10, STAT, workarea executions - optimal , 8, 1, 8, .8 DATA, 155, 20071213 21:15:26, 10, STAT, execute count
, 1, 0, 1, .1
DATA, 155, 20071213 21:15:26, 10, STAT, sorts (memory)
, 5, 0, 5, .5
DATA, 155, 20071213 21:15:26, 10, STAT, sorts (rows)
, 1458, 146, 1.46k, 145.8
DATA, 155, 20071213 21:15:26, 10, TIME, PL/SQL execution elapsed time , 577, 58, 577us, 57.7us DATA, 155, 20071213 21:15:26, 10, TIME, DB CPU
, 31250, 3125, 31.25ms, 3.12ms
DATA, 155, 20071213 21:15:26, 10, TIME, sql execute elapsed time
, 19330, 1933, 19.33ms, 1.93ms
DATA, 155, 20071213 21:15:26, 10, TIME, DB time
, 19590, 1959, 19.59ms, 1.96ms
DATA, 155, 20071213 21:15:26, 10, WAIT, PL/SQL lock timer , 10010101, 1001010, 10.01s, 1s
PL/SQL procedure successfully completed.
-- Regards, Tanel Poder http://blog.tanelpoder.com <http://blog.tanelpoder.com/> _____ From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Anju Bala Sent: Thursday, December 13, 2007 18:20 To: oracle-l_at_freelists.org Subject: Oracle Process Problem hi list, We are running Oracle 9iR2 on Linux ES4. there are certain oracle process which is consuming almost 100% of the cpu. I tried finding if there is any latch issue but could not find any specific problem though I am not very good DBA but I tried all the possibilities I could. Could anyone please let me how to figure this problem out and resolve it?? System config : 4 CPU with 1GB RAM TIA Bala -- http://www.freelists.org/webpage/oracle-lReceived on Thu Dec 13 2007 - 07:21:17 CST
 |
 |