Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Process field on v$session
Julio
Here is the SQL query I use, where the spid is the unix process I.D. that you mentioned that you already have (replace 19633 with your 2800). This isn't the best query for this, but I use it a lot and I'm on my way out the door for the day. Good luck.
select sql_text
from v$process a, v$session b, v$sqltext c
where spid = 19633
and a.addr = b.paddr
and b.sql_address = c.address
order by address, hash_value, piece
Dennis Williams
DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of QuijadaReina, Julio C
Sent: Tuesday, April 13, 2004 3:24 PM
To: oracle-l_at_freelists.org
Subject: RE: Process field on v$session
Dennis,
Thanks for pointing me to a different direction - I was starting to get frustrated with this. I hope you pardon my ignorance since I am new to the Oracle views. But, is this what v$session.command will show me? And since you mentioned SQL, I've seen a view called v$sqlarea. Is that a good starting point?
Julio=20
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of DENNIS WILLIAMS
Sent: Tuesday, April 13, 2004 2:23 PM
To: 'oracle-l_at_freelists.org'
Subject: RE: Process field on v$session
Julio
Consider working from the other direction. Try to find the SQL that
is
being executed.
Dennis Williams
DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com=20
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of QuijadaReina, Julio C
Sent: Tuesday, April 13, 2004 12:24 PM
To: oracle-l_at_freelists.org
Subject: Process field on v$session
All,
=20
For a good chunk of the day now, I've been trying to get all information I can about this one session that is consuming up to 70 per cent of CPU on our db server.=20
=20
Let me give you a little background: We have an application server that
runs a web-based system for class registration. This app server runs
Apache. The task mingler on this app server shows several Apache PIDs
-each db instance has its own separate web service and in turn, each
Apache PID can have several child processes.=20
=20
Now, in my efforts to track down this CPU-hogging session; on our db server I see this session as coming from the app server [get this from v$session.machine.] I can also get some other goodies by joining on v$process to get the db server PID. I've done pretty good up to this point, but there is one field on v$session called 'process' of which I am uncertain. This field shows two numbers separated by a colon (e.g. 2800:2168). A little bit of research indicates that the first number corresponds to the app server Apache's PID. I have used netstat on my app server to see if the second number would be a port number. But, netstat does not show any clients connecting to that port number. Has anyone figured out what that second number stands for? Is it a client's identification number of some sort or is it just a random number generated by Oracle upon establishing a connection? I'll appreciate any input you may have on this. Thanks in advance!
=20
Julio
=20
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.htmlput 'unsubscribe' in the subject line.
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.htmlput 'unsubscribe' in the subject line.
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.htmlput 'unsubscribe' in the subject line.
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org
![]() |
![]() |