| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Process field on v$session
Dennis,
The query you sent is going to be helpful. I noticed one thing from you example though: 2800 is not a session pid (spid) on the db server. 2800 is Apache running on the application server, which pid gets logged in v$session.process when a connection is established between the two boxes (the app server and the db server.)=20
Now, I understand that by using this query I will be able to see what SQL code is being executed given a spid. However, after playing with this for a while, I found out that for the any given spid, there can be several users executing the SQL code. Remember the v$sqlarea view that I mentioned before? When I run the following query, the users_executing field shows numbers up in the 50s and 60s. If one spid has as many as 60 users executing the same SQL statement, then it will be even harder to point out what web client is pounding the database. I know that the SQL statements run by users connecting through our app server have not given us performance problems in the past. Now, whether we had these many users executing this SQL in the past I don't know. Maybe there has been an increase in the number of users. In the past we didn't care about it because there was no sluggish effect on our db server. Now that we are having this problem, we are monitoring our systems more closely.
The following query is what I use (which is very similar to your code):
SELECT
all_users.username, executions, users_executing, command_type, disk_reads, sql_text
As of right now, the problem seems to have gone away, but I'll keep monitoring our db server. Thanks for you help!
Julio
-----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 5:23 PM
To: 'oracle-l_at_freelists.org'
Subject: 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 =3D 19633
and a.addr =3D b.paddr
and b.sql_address =3D c.address
order by address, hash_value, piece
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 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=3D20
-----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=3D20
-----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,
=3D20
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.=3D20
=3D20
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.=3D20
=3D20
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!
=3D20
Julio
=3D20
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
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
 |
 |