• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 419
  • Last Modified:

Process taking cpu

Hi Gurus,

How to identified a db process is a ran away one from unix ,will there be an active session in DB for this process and what could be the value v$session.last_call_et for it?

How can we prevented a db process from running away?

Thanks
0
monto1
Asked:
monto1
6 Solutions
 
tindavidCommented:
I assume the definition of a runaway oracle process is a "oracle" owned process But unable to find a record in v$session table.  The situation is quite unusual as Oracle will always rollback if a session is disconnected from the DB session.

It will be impossible of a process doing Oracle work but not connecting to Oracle as active session.

The value of LAST_CALL_ET indicates the elapse time since last SQL command ended (in ms ?)
0
 
Geert GOracle dbaCommented:
a runaway process can be many things:
> an infinite loop
  this is usually a programming problem and requires some knowledge of the actual process

> an orphaned process
not sure if you actually have orphaned processes
check support.oracle.com for orphaned processes

a public link describing orphaned processes:
http://troubleshootingappsdba.blogspot.be/2008/02/orphan-processes-in-oracle-databases.html
0
 
johnsoneSenior Oracle DBACommented:
I think you need to be a little more specific in what you are asking, because I read this a little differently.  What are you trying to do?

Do you have a process on the OS side that you have identified as an Oracle session and you want to figure out what it is?  If so, then you need to look in V$PROCESS.  The SPID column will match to the OS process id, then you can join that back to V$SESSION on V$PROCESS.ADDR = V$SESSION.PADDR
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
monto1Author Commented:
I see an OS process which is using lot of  thousands of cpu secs and its an oracle process and when i map it to v$session i see the last_call_et very high and  gets incremented and the session state is active. so my question is ,is it a ran away process?will ran away process have a db session?

How can i make sure that it is not ran away and how to prevent any ran away process in oracle.

Thanks
0
 
johnsoneSenior Oracle DBACommented:
Which oracle process?  What session is it attached to?  What is the last query that it ran?

If it is a background process and your database has been up for a very long time, the fact that the process has a large number of accumulated cpu seconds is not all that out of normal.
0
 
monto1Author Commented:
It was a user process and was still executing with last_call_et very high along .It was running for more than a day.

 AFAIK  there should not be a db session attached for a ran away process but in this case the session was active in db.

Thanks
Syed
0
 
johnsoneSenior Oracle DBACommented:
What query is running in that process?  What is the plan for that query?  I have seen queries that run for a day that are not a problem.
0
 
monto1Author Commented:
That was an report query which generally runs for 10 mins but was running for more than a day ,the reason i suspected it to be ran away one was TOAD was not showing up anything in sessions tab but from sqlplus i could see a session(active) attached to the process.The plan for the query didn't change(PHV).

Regards
0
 
johnsoneSenior Oracle DBACommented:
If it is the same query with the same plan, then what is the session waiting for?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now