[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Oracle session

Posted on 2006-05-11
9
Medium Priority
?
442 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:ramziabk
  • 5
  • 2
  • 2
9 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 16660592
0
 
LVL 3

Expert Comment

by:haidersyed
ID: 16665004
Please mention Operating system you are using.
I will explain you all about this.
0
 
LVL 1

Author Comment

by:ramziabk
ID: 16665291
Database is installed on Windows NT4.End users are using Windows XP.

Thanks
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 3

Expert Comment

by:haidersyed
ID: 16666249
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
0
 
LVL 3

Expert Comment

by:haidersyed
ID: 16666345
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
0
 
LVL 1

Author Comment

by:ramziabk
ID: 16666459
haidersyed,

From the query you gave, how can i know which session is taking most procesing power so i can kill it.

Thanks
0
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1000 total points
ID: 16666956
Did you look at the link I posted?  Is says it shows CPU utilization (I haven't personally checked it out).

I just wanted to comment on your last statement:  "so i can kill it".  Are you sure you want to do this?  Killing a process just because you don't like what it's doing could cause data loss.

I've only ever seen true runaway processes in Oracle on an older shared server environment.
0
 
LVL 3

Expert Comment

by:haidersyed
ID: 16666989
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#
0
 
LVL 3

Expert Comment

by:haidersyed
ID: 16666994
please note you can not kill background processes.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, we’ll look at how to deploy ProxySQL.
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question