Link to home
Start Free TrialLog in
Avatar of ramziabk
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
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Please mention Operating system you are using.
I will explain you all about this.
Avatar of ramziabk

ASKER

Database is installed on Windows NT4.End users are using Windows XP.

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.PROGRAM,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
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
haidersyed,

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
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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#
please note you can not kill background processes.