Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

How to trace which user slow down oracle performance

ng_7
Regular Advisor

How to trace which user slow down oracle performance

hi,

I wonder if someone could help me : I am using Oracle Application 11.5.7 and Database 8.1.7. How do i trace PID from TOP is belongs to whom ?
13 REPLIES
Steven E. Protter
Exalted Contributor

Re: How to trace which user slow down oracle performance

Shalom,

A PID in top does normally display the user.

ps -ef | grep

See what information you get.

Oracle Application and database on the same system may be the cause of the issue. That requires a large amount of memory to run efficiently.

http://www.hpux.ws/system.perf.sh

SEP
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
spex
Honored Contributor

Re: How to trace which user slow down oracle performance

Hello,

Replace with the pid from top:

SELECT s.osuser
FROM v$process p, v$session s
WHERE p.addr = s.paddr
AND p.spid='';

PCS
ng_7
Regular Advisor

Re: How to trace which user slow down oracle performance

hi, PCS,
i can't really know who are the user from the output, perhaps we need something like login name ?
Hein van den Heuvel
Honored Contributor

Re: How to trace which user slow down oracle performance

Well, the osuser is just an example to get you going.

Looks at other 'intersting' columns v$process

http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2022.htm

And specifically, check out

MACHINE, TERMINAL, and PROGRAM in
v$session

http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2088.htm

Hth,
Hein.
ng_7
Regular Advisor

Re: How to trace which user slow down oracle performance

hi, I still can't see information i need from v$process and v$session. i can see in username column is all oraprod for v$process and username in v$session is null.
i suspect something wrong with my settings.

thanks
Yogeeraj_1
Honored Contributor

Re: How to trace which user slow down oracle performance

Hi,

What you see from v$process and v$session is correct information.


I usually use the following query to find user information:
select b.sid SID,b.serial# "Serial#", c.spid "srvPID", b.osuser, b.username, b.status, b.client_info, machine
from v$session b, v$process c
where b.paddr = c.addr
and c.sPID = &OSPID

from the snap that you have uploaded, we find that both sessions have connected to the database using USERNAME: oraprod


if you need any specific help please let us know.


hope this helps!

kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
ng_7
Regular Advisor

Re: How to trace which user slow down oracle performance

hi, I am using your scripts and put in PID, but my username column is all APPS, is it something wrong with my settings ?

thanks

Yogeeraj_1
Honored Contributor

Re: How to trace which user slow down oracle performance

hi again,

break this query into 2 parts and display the results:

select b.sid SID,b.serial# "Serial#", c.spid "srvPID", b.osuser, b.username, b.status, b.client_info, machine
from v$session b, v$process c where b.paddr = c.addr and c.sPID = &OSPID

ie.
select c.spid "srvPID", c.addr
from v$process c
where c.sPID = &OSPID;

and

select b.sid SID,b.serial# "Serial#", b.osuser, b.username, b.status, b.client_info, machine
from v$session b
where b.paddr =


kind regards
yogeeraj

No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
ng_7
Regular Advisor

Re: How to trace which user slow down oracle performance

eg: i take 13264, and the result as per attached. please advise

thanks
Yogeeraj_1
Honored Contributor

Re: How to trace which user slow down oracle performance

hi,

It seems that the application using a specific username to run the programs/forms.

to find what SQL statement the "culprit" session is using, run:

SELECT sql_text
FROM v$sqltext_with_newlines
WHERE address = (SELECT prev_sql_addr
FROM v$session
WHERE username = &uname AND SID = &snum)
ORDER BY piece

If you take the uploading data, it would be something like:
e.g.
SELECT sql_text
FROM v$sqltext_with_newlines
WHERE address = (SELECT prev_sql_addr
FROM v$session
WHERE username = 'APPS' AND SID = '116')
ORDER BY piece

It will be quite difficult to trace back the user. You may also wish to verify at the application server level as well.

There should be a monitoring tool at the Oracle Application Level also.


hope this helps!

kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
David_616
Occasional Visitor

Re: How to trace which user slow down oracle performance

Hi,

If the PID belongs to a concurrent request you can use the sql below to know the user who submitted it.

SELECT DISTINCT fu.user_name
FROM v$process vp,
v$session vs,
v$sess_io vio,
v$transaction vt,
fnd_concurrent_requests fcr,
fnd_user fu
WHERE vp.addr = vs.paddr
AND vs.SID = vio.SID
AND vs.taddr = vt.addr(+)
AND fcr.os_process_id = vs.process
AND fu.user_id = fcr.requested_by
and vp.spid='13264' --DATABASE PID

Regards, David.
Leopoldo González
Regular Visitor

Re: How to trace which user slow down oracle performance

I do these 3 steps:
1. Run top to get the heavyest process's PID,
2. then use ps -ef|grep to get related processes,
3. finally use Oracle's Enterprise Manager Console to find the session that matches one of the PIDs gotten in step 2.

You can edit the session (double click on it) to see the SQL text, execution plan, cost, etc...

Another thing to check: Usually the poor performance is caused by disk I/O contention, use "iostat" to check that condition.

Good luck.
(excuse me for my English hehe)
skt_skt
Honored Contributor

Re: How to trace which user slow down oracle performance

use extract command to get the CPU/DISK/Mem deataisll. THis really helps to fidn out the slow ness from DB level. ALso refer the statspack data and look for any wait events.

extract -p -xp -b 06/04/07 23:00 -e 06/05/07 05:00 -r /var/opt/perf/reptproc_io -f extract_0604_11PM_5AM.out