ramziabk
asked on
Oracle session
Hello all,
Guys i have Oracle 8i on dual processor machine.Out of a sudden,CPU PROCESSING JUMBS TO 100% without performing any extraordinary tasks.Please advice and if i can trace or find the session causing that to occur.
Thanks
Ramzi
Guys i have Oracle 8i on dual processor machine.Out of a sudden,CPU PROCESSING JUMBS TO 100% without performing any extraordinary tasks.Please advice and if i can trace or find the session causing that to occur.
Thanks
Ramzi
Please mention Operating system you are using.
I will explain you all about this.
I will explain you all about this.
ASKER
Database is installed on Windows NT4.End users are using Windows XP.
Thanks
Thanks
Hi,
On windows flavours you can user enterprise manager which will show you sessions and their memory and CPU utilization.
Also you can use query to find resource utilization
SELECT s.name TYPE ,SUBSTR(A.SID,1,5) "SID", A.serial#,
SUBSTR(A.PROCESS,1,7) "PROCESS",
SUBSTR(A.USERNAME,1,20) "USERID",
V.VALUE "RESOURCE USED BY THIS SESSION",A.TERMINAL,A.PROG RAM,SPID
FROM v$statname s, v$sesstat v, v$session a, V$PROCESS
WHERE s.name in ('CPU used by this session','DBWR buffers scanned','free buffer requested','physical reads direct','sorts (memory)','physical writes','session uga memory','session pga memory') and
v.statistic#=s.statistic#
AND v.sid = a.sid
and v.value > 100
AND ADDR=PADDR
On windows flavours you can user enterprise manager which will show you sessions and their memory and CPU utilization.
Also you can use query to find resource utilization
SELECT s.name TYPE ,SUBSTR(A.SID,1,5) "SID", A.serial#,
SUBSTR(A.PROCESS,1,7) "PROCESS",
SUBSTR(A.USERNAME,1,20) "USERID",
V.VALUE "RESOURCE USED BY THIS SESSION",A.TERMINAL,A.PROG
FROM v$statname s, v$sesstat v, v$session a, V$PROCESS
WHERE s.name in ('CPU used by this session','DBWR buffers scanned','free buffer requested','physical reads direct','sorts (memory)','physical writes','session uga memory','session pga memory') and
v.statistic#=s.statistic#
AND v.sid = a.sid
and v.value > 100
AND ADDR=PADDR
For unix solaris you can use top command to get os id of oracle proecesses and then you can identfy
on windows you find only one oracle.exe running
on windows you find only one oracle.exe running
ASKER
haidersyed,
From the query you gave, how can i know which session is taking most procesing power so i can kill it.
Thanks
From the query you gave, how can i know which session is taking most procesing power so i can kill it.
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
it gives you both sid and serial#
you can kill session using
alter system kill session 'sid,serial#';
place the value of sid and serial#
you can kill session using
alter system kill session 'sid,serial#';
place the value of sid and serial#
please note you can not kill background processes.
http://www.oracle.com/technology/oramag/code/tips2005/112805.html