Operating System - HP-UX
1748156 Members
3922 Online
108758 Solutions
New Discussion юеВ

Re: 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 13
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