Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Process taking cpu

Posted on 2013-06-13
9
Medium Priority
?
413 Views
Last Modified: 2013-06-20
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
Comment
Question by:monto1
9 Comments
 

Assisted Solution

by:tindavid
tindavid earned 252 total points
ID: 39246871
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
 
LVL 38

Assisted Solution

by:Geert Gruwez
Geert Gruwez earned 249 total points
ID: 39246949
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
 
LVL 35

Assisted Solution

by:johnsone
johnsone earned 999 total points
ID: 39247376
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:monto1
ID: 39248875
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
 
LVL 35

Assisted Solution

by:johnsone
johnsone earned 999 total points
ID: 39252023
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
 

Author Comment

by:monto1
ID: 39252094
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
 
LVL 35

Assisted Solution

by:johnsone
johnsone earned 999 total points
ID: 39253020
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
 

Author Comment

by:monto1
ID: 39253443
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
 
LVL 35

Accepted Solution

by:
johnsone earned 999 total points
ID: 39253522
If it is the same query with the same plan, then what is the session waiting for?
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

581 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